DIXWeb.git - Gitblit

			@@ -6,13 +6,17 @@
			using System.Threading.Tasks;
			using DIXWeb.Entity.DIX;
			using DIXWeb.Entity.GlobalM;
			using NPOI.HSSF.UserModel;
			using NPOI.SS.UserModel;
			using NPOI.SS.Util;

			namespace DIXWeb.Business.DIX
			{
			public class ExcelHelper
			{
			public static void CreateExcel(List<ExcelEntity> list, string excelRealPath,string sheetName) {
			private bool IsXlsx=false;
			public static void CreateExcel(List<ExcelEntity> list, string excelRealPath, string sheetName)
			{
			new ToExcelTemplate(excelRealPath, sheetName).ExportTemplate(list);
			}

			@@ -65,5 +69,418 @@
			}


			public void Create(string excelRealPath, string sheetName,bool IsXlsx)
			{
			this.IsXlsx = IsXlsx;
			HSSFWorkbook workbook = new HSSFWorkbook();
			ISheet sheet = workbook.CreateSheet("Sheet1");
			SetDefaultStyle(workbook, sheet);

			ToExcelTemplate data = new ToExcelTemplate();
			string[] orgArr = data.GetOrg();
			string[] iotArr = data.GetInspectObjectType();
			string[] iaArr = data.GetInspectArea();
			string[] itArr = data.GetInspectType();
			string[] ifArr = data.GetInspectFrequency();
			string[] isArr = data.GetInspectStatu();
			string InspectObjectIsNeedStr = System.Configuration.ConfigurationManager.AppSettings["InspectObjectIsNeedStr"].ToString();
			string[] iNArr = InspectObjectIsNeedStr.Split(',');

			SetCellDropdownList(workbook, sheet, "Org", 1, 0, 0, orgArr,1);
			SetCellDropdownList(workbook, sheet, "InspectObjectType", 1, 1, 1, iotArr,2);
			SetCellDropdownList(workbook, sheet, "InspectArea", 1, 4, 4, iaArr,3);
			SetCellDropdownList(workbook, sheet, "InspectType", 1, 6, 6, itArr,4);
			SetCellDropdownList(workbook, sheet, "InspectFrequency", 1, 7, 7, ifArr,5);
			SetCellDropdownList(workbook, sheet, "InspectStatu", 1, 8, 8, isArr,6);
			SetCellDropdownList(workbook, sheet, "IsNeed", 1, 9, 9, iNArr,7);

			WriteToFile(excelRealPath, workbook);
			}


			public static void SetCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string name,int firstRow, int firstcol, int lastcol, string[] vals, int sheetindex = 1)
			{
			//先创建一个Sheet专门用于存储下拉项的值
			ISheet sheet2 = workbook.CreateSheet(name);
			//隐藏
			workbook.SetSheetHidden(sheetindex, true);
			int index = 0;
			foreach (var item in vals)
			{
			sheet2.CreateRow(index).CreateCell(0).SetCellValue(item);
			index++;
			}
			//创建的下拉项的区域：
			var rangeName = name + "Range";
			IName range = workbook.CreateName();
			range.RefersToFormula = name + "!$A$1:$A$" + index;
			range.NameName = rangeName;
			CellRangeAddressList regions = new CellRangeAddressList(firstRow, 65535, firstcol, lastcol);
			DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
			HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
			dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
			dataValidate.ShowPromptBox = true;
			sheet.AddValidationData(dataValidate);
			}

			private string WriteToFile(string fileName, HSSFWorkbook workbook)
			{
			string result = string.Empty;
			try
			{
			using (System.IO.FileStream file = new System.IO.FileStream(fileName, System.IO.FileMode.Create))
			{
			workbook.Write(file);
			}
			result = "SUCCESS,";
			}
			catch (Exception ex)
			{
			result = "ERROR," + ex.Message;
			}
			return result;
			}
			private void SetDefaultStyle(IWorkbook workbook, ISheet sheet)
			{
			Dictionary<String, ICellStyle> styles = createCellStyles(workbook);
			if (!IsXlsx)
			sheet.TabColorIndex = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
			NPOI.SS.UserModel.IPrintSetup printSetup = sheet.PrintSetup;
			sheet.DisplayGridlines = false;
			SetDefaultRowHeight(sheet, 28.0F);

			sheet.FitToPage = (true);
			sheet.HorizontallyCenter = (true); //设置Sheet缩放
			sheet.SetZoom(82, 100); // 100 percent magnification
			SetColumnWidth(sheet, 00, 25.00);
			SetColumnWidth(sheet, 01, 25.00);
			SetColumnWidth(sheet, 02, 25.00);
			SetColumnWidth(sheet, 03, 25.00);
			SetColumnWidth(sheet, 04, 25.00);
			SetColumnWidth(sheet, 05, 25.00);
			SetColumnWidth(sheet, 06, 25.00);
			SetColumnWidth(sheet, 07, 25.00);
			SetColumnWidth(sheet, 08, 25.00);
			SetColumnWidth(sheet, 09, 25.00);
			SetColumnWidth(sheet, 10, 25.00);

			sheet.SetColumnHidden(24, true);

			IRow curRow = sheet.CreateRow(0);
			ICell curCel = null;
			SetRowHeight(curRow, 33);//第一行行高
			//第一行列头
			curCel = curRow.CreateCell(0);
			SetColumnWidth(sheet, 00, 15.00);
			curCel.SetCellValue("组织ID");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(1);
			SetColumnWidth(sheet, 01, 25.00);
			curCel.SetCellValue("设备类型ID");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(2);
			SetColumnWidth(sheet, 02, 15.00);
			curCel.SetCellValue("设备编号");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(3);
			SetColumnWidth(sheet, 03, 15.00);
			curCel.SetCellValue("设备名称");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(4);
			SetColumnWidth(sheet, 04, 25.00);
			curCel.SetCellValue("设备区域ID");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(5);
			SetColumnWidth(sheet, 05, 15.00);
			curCel.SetCellValue("设备位置");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(6);
			SetColumnWidth(sheet, 06, 25.00);
			curCel.SetCellValue("点检周期ID");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(7);
			SetColumnWidth(sheet, 07, 20.00);
			curCel.SetCellValue("点检频率ID");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(8);
			SetColumnWidth(sheet, 08, 20.00);
			curCel.SetCellValue("设备状态ID");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(9);
			SetColumnWidth(sheet, 09, 30.00);
			curCel.SetCellValue("是否需要点检");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(10);
			SetColumnWidth(sheet, 10, 25.00);
			curCel.SetCellValue("导入日期");
			curCel.CellStyle = styles["Bold_13_Black_Font"];

			curCel = curRow.CreateCell(11);
			SetColumnWidth(sheet, 11, 25.00);
			curCel.SetCellValue("有效期(年)");
			curCel.CellStyle = styles["Bold_13_Black_Font"];


			}
			protected void SetDefaultRowHeight(ISheet sheet, float rowHeight)
			{
			sheet.DefaultRowHeight = Convert.ToInt16(rowHeight * 20);
			}
			protected void SetColumnWidth(ISheet sheet, int columnIndex, double width)
			{
			sheet.SetColumnWidth(columnIndex, (int)((width + 0.72) * 256));
			}
			protected void SetRowHeight(IRow targetRow, float rowHeight)
			{
			targetRow.Height = Convert.ToInt16(rowHeight * 20);
			}
			protected void SetFontHeight(IFont targetFont, double fontHeight)
			{
			if (IsXlsx == true)
			{
			targetFont.FontHeightInPoints = Convert.ToInt16(fontHeight);
			}
			else
			{
			targetFont.FontHeight = fontHeight * 20;
			}
			}

			protected Dictionary<string, ICellStyle> createCellStyles(IWorkbook wb)
			{
			//return base.createCellStyles(wb);
			Dictionary<string, ICellStyle> styles = new Dictionary<string, ICellStyle>();
			IDataFormat df = wb.CreateDataFormat();
			NPOI.SS.UserModel.IFont MsFont_9_Black_Font = wb.CreateFont();
			MsFont_9_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
			SetFontHeight(MsFont_9_Black_Font, 9);
			MsFont_9_Black_Font.FontName = "微软雅黑";

			NPOI.SS.UserModel.IFont MsFont_9_Blue_Font = wb.CreateFont();
			MsFont_9_Blue_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
			MsFont_9_Blue_Font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
			SetFontHeight(MsFont_9_Blue_Font, 9);
			MsFont_9_Blue_Font.FontName = "微软雅黑";

			NPOI.SS.UserModel.IFont MsFont_9_Red_Font = wb.CreateFont();
			MsFont_9_Red_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
			MsFont_9_Red_Font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
			SetFontHeight(MsFont_9_Red_Font, 9);
			MsFont_9_Red_Font.FontName = "微软雅黑";


			NPOI.SS.UserModel.ICellStyle cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			styles.Add("微黑_9_V中H中_换行_全框细", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			styles.Add("微黑_9_V中H右_换行_全框细", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Blue_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			styles.Add("微黑_9_V中H左_换行_全框细_蓝字", cellStyle);


			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = true; //上锁
			styles.Add("微黑_9_V中H中_换行_全框细_上锁", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Red_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			styles.Add("微黑_红9_V中H中_换行_全框细", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
			styles.Add("微黑_9_V中H中_换行_全框细_蓝背景", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = true;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
			styles.Add("微黑_9_V中H中_换行_全框细_蓝背景_上锁", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Blue_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = true;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
			styles.Add("微黑_9蓝_V中H中_换行_全框细_蓝背景_上锁", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BrightGreen.Index;
			styles.Add("微黑_9_V中H右_换行_全框细_绿背景", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
			styles.Add("微黑_9_V中H中_换行_全框细_柠檬黄背景", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
			styles.Add("微黑_9_V中H右_换行_全框细_柠檬黄背景", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = true;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
			styles.Add("微黑_9_V中H中_换行_全框细_柠檬黄背景_上锁", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (false);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.DataFormat = (df.GetFormat("m-d h:mm"));
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
			styles.Add("微黑_9_V中H中_不换行_全框细_柠檬黄背景_日期", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (true);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
			styles.Add("微黑_9_V中H中_换行_全框细_淡蓝背景", cellStyle);


			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_9_Black_Font);
			cellStyle.WrapText = (false);
			cellStyle.IsLocked = false;
			cellStyle.FillPattern = FillPattern.SolidForeground;
			cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
			cellStyle.DataFormat = (df.GetFormat("m-d h:mm"));
			styles.Add("微黑_9_V中H中_不换行_全框细_淡蓝背景_日期", cellStyle);


			NPOI.SS.UserModel.IFont Bold_13_Black_Font = wb.CreateFont();
			Bold_13_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
			Bold_13_Black_Font.FontHeight = 13 * 20; //字体高度，XLSX下有字体大小问题
			//Bold_15_Black_Font.FontHeightInPoints = 10; //字体高度，XLS下有大小问题
			Bold_13_Black_Font.FontName = "黑体";

			NPOI.SS.UserModel.IFont Bold_15_Black_Font = wb.CreateFont();
			Bold_15_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
			Bold_15_Black_Font.FontHeight = 15 * 20; //字体高度，XLSX下有字体大小问题
			//Bold_15_Black_Font.FontHeightInPoints = 10; //字体高度，XLS下有大小问题
			Bold_15_Black_Font.FontName = "黑体";

			NPOI.SS.UserModel.IFont MsFont_12_Black_Font = wb.CreateFont();
			MsFont_12_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
			SetFontHeight(MsFont_12_Black_Font, 12);
			MsFont_12_Black_Font.FontName = "微软雅黑";

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(Bold_13_Black_Font);
			//cellStyle.WrapText = (false);//换行
			styles.Add("Bold_13_Black_Font", cellStyle);

			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(Bold_15_Black_Font);
			//cellStyle.WrapText = (false);
			styles.Add("Bold_15_Black_Font", cellStyle);


			cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
			cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
			cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
			cellStyle.SetFont(MsFont_12_Black_Font);
			//cellStyle.WrapText = (true);//换行
			cellStyle.IsLocked = true;
			styles.Add("微黑_12_V中H中_全框细_上锁", cellStyle);

			return styles;
			}

			protected NPOI.SS.UserModel.ICellStyle CreateBorderedStyle(NPOI.SS.UserModel.IWorkbook wb,
			NPOI.SS.UserModel.BorderStyle defaltBorderStyle = NPOI.SS.UserModel.BorderStyle.Thin)
			{
			NPOI.SS.UserModel.ICellStyle style = wb.CreateCellStyle();
			style.BorderRight = defaltBorderStyle;
			style.BorderBottom = defaltBorderStyle;
			style.BorderLeft = defaltBorderStyle;
			style.BorderTop = defaltBorderStyle;
			style.RightBorderColor = (NPOI.SS.UserModel.IndexedColors.Black.Index);
			style.BottomBorderColor = (NPOI.SS.UserModel.IndexedColors.Black.Index);
			style.LeftBorderColor = (NPOI.SS.UserModel.IndexedColors.Black.Index);
			style.TopBorderColor = (NPOI.SS.UserModel.IndexedColors.Black.Index);
			return style;
			}
			}
			}