// 由微信类生成Excel private static void ObjectExport(ExportExcelPageMaker configData, Dictionary<string, ICellStyle> contentStyles, ISheet sheet, ObjectListModel objectLM) { int rowIndex = 1; foreach (ObjectContainer container in objectLM.List) { IRow dataRow = sheet.CreateRow(rowIndex); int columnIndex = 0; object receiver = container.MainObject; string strValue = string.Empty; foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICell cell = dataRow.CreateCell(columnIndex); if (fieldInfo != null) { if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None) { strValue = receiver.MemberValue(fieldInfo.NickName).ConvertToString(); CellPadding(strValue, cell, fieldInfo); } else strValue = container.Decoder.GetNameString(fieldInfo.NickName); cell.CellStyle = contentStyles[fieldInfo.NickName]; } columnIndex++; } rowIndex++; } }
// 由微信类生成Excel private static void ObjectExport(ExportExcelPageMaker configData, Dictionary <string, ICellStyle> contentStyles, ISheet sheet, ObjectListModel objectLM) { int rowIndex = 1; foreach (ObjectContainer container in objectLM.List) { IRow dataRow = sheet.CreateRow(rowIndex); int columnIndex = 0; object receiver = container.MainObject; string strValue = string.Empty; foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICell cell = dataRow.CreateCell(columnIndex); if (fieldInfo != null) { if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None) { strValue = receiver.MemberValue(fieldInfo.NickName).ConvertToString(); CellPadding(strValue, cell, fieldInfo); } else { strValue = container.Decoder.GetNameString(fieldInfo.NickName); } cell.CellStyle = contentStyles[fieldInfo.NickName]; } columnIndex++; } rowIndex++; } }
// 模板 public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData) { MemoryStream ms = new MemoryStream(); using (ms) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc); HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); int index = 0; ExportExcelPageMaker headerConfigData = new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = true }; ExportExcelPageMaker contentConfigData = new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = false }; foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList) { int colWith = NPOIWrite.GetColWidth(fieldInfo); sheet.SetColumnWidth(index, colWith << 8); ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook, contentConfigData, fieldInfo, NPOIWrite.Model.Content); HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook); sheet.SetDefaultColumnStyle(index, styleContent); if (dataValidate != null) { ((HSSFSheet)sheet).AddValidationData(dataValidate); } ICell cell = dataRow.CreateCell(index); ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook, headerConfigData, fieldInfo, NPOIWrite.Model.Header); cell.SetCellValue(fieldInfo.DisplayName); cell.CellStyle = styleHeader; index++; } //string strFileName = @"D:\EmportTemplateTest.xls"; //using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) //{ // workbook.Write(fs); //} workbook.Write(ms); ms.Flush(); return(ms.ToArray()); } }
// 表格头部设置 private static void HeaderSetting(ExportExcelPageMaker configData, IWorkbook workbook, ISheet sheet) { IRow dataRow = sheet.CreateRow(0); int index = 0; foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICell cell = dataRow.CreateCell(index); ICellStyle styleHeader = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Header); cell.SetCellValue(fieldInfo.DisplayName); cell.CellStyle = styleHeader; int colWith = GetColWidth(fieldInfo); sheet.SetColumnWidth(index, colWith << 8); index++; } }
public static byte[] ExportExcel(OutputData dataSource, ExportExcelPageMaker configData) { MemoryStream ms = new MemoryStream(); using (ms) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(configData.fMetaData.Table.TableDesc); HeaderSetting(configData, workbook, sheet); Dictionary <string, ICellStyle> ContentStyles = GetContentStyles(workbook, configData); if (dataSource.Data is DataSet) { DataSet ds = (DataSet)dataSource.Data; TkDebug.AssertArgumentNull(ds, "ds", null); DataTable dt = ds.Tables[configData.fMetaData.Table.TableName]; if (dt != null) { DataTableExport(configData, ContentStyles, sheet, dt); } } if (dataSource.Data is ObjectListModel) { ObjectListModel objectLM = (ObjectListModel)dataSource.Data; if (objectLM.List != null) { ObjectExport(configData, ContentStyles, sheet, objectLM); } } workbook.Write(ms); ms.Flush(); return(ms.ToArray()); } }
public static byte[] ExportExcel(OutputData dataSource, ExportExcelPageMaker configData) { MemoryStream ms = new MemoryStream(); using (ms) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(configData.fMetaData.Table.TableDesc); HeaderSetting(configData, workbook, sheet); Dictionary<string, ICellStyle> ContentStyles = GetContentStyles(workbook, configData); if (dataSource.Data is DataSet) { DataSet ds = (DataSet)dataSource.Data; TkDebug.AssertArgumentNull(ds, "ds", null); DataTable dt = ds.Tables[configData.fMetaData.Table.TableName]; if (dt != null) { DataTableExport(configData, ContentStyles, sheet, dt); } } if (dataSource.Data is ObjectListModel) { ObjectListModel objectLM = (ObjectListModel)dataSource.Data; if (objectLM.List != null) { ObjectExport(configData, ContentStyles, sheet, objectLM); } } workbook.Write(ms); ms.Flush(); return ms.ToArray(); } }
internal static ICellStyle BorderAndFontSetting(IWorkbook workbook, ExportExcelPageMaker configData, Tk5FieldInfoEx metaInfo, Model model) { ICellStyle cellStyle = workbook.CreateCellStyle(); if (configData.UserBorder) { cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; } if (model == Model.Content) { IFont fontContent = FontSetting(workbook, configData.Content); cellStyle.SetFont(fontContent); if (metaInfo.Extension != null) { AlignSetting(metaInfo.Extension.Align, cellStyle); } else { AlignSetting(configData.Content.Align, cellStyle); } } else { AlignSetting(configData.Header.Align, cellStyle); IFont fontHeader = FontSetting(workbook, configData.Header); cellStyle.SetFont(fontHeader); } return(cellStyle); }
//生成Dictionary<NickName, ICellStyle>, 每一列对应每一个ICellStyle,通过NickName来获取ICellStyle private static Dictionary <string, ICellStyle> GetContentStyles(IWorkbook workbook, ExportExcelPageMaker configData) { Dictionary <string, ICellStyle> NameToStyle = new Dictionary <string, ICellStyle>(); IDataFormat format = workbook.CreateDataFormat(); foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICellStyle cellStyle = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Content); if (fieldInfo.Extension != null && !string.IsNullOrEmpty(fieldInfo.Extension.Format)) { cellStyle.DataFormat = format.GetFormat("@"); } else { switch (fieldInfo.DataType) { case TkDataType.Long: case TkDataType.Int: case TkDataType.Short: case TkDataType.Byte: case TkDataType.Double: case TkDataType.Decimal: case TkDataType.Money: if (fieldInfo.DataType == TkDataType.Money) { cellStyle.DataFormat = format.GetFormat("¥#,##0"); } else { cellStyle.DataFormat = format.GetFormat("0"); } break; case TkDataType.String: case TkDataType.Text: case TkDataType.Guid: case TkDataType.Xml: cellStyle.DataFormat = format.GetFormat("@"); break; case TkDataType.DateTime: case TkDataType.Date: if (fieldInfo.DataType == TkDataType.DateTime) { cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm"); } else { cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); } break; } } NameToStyle.Add(fieldInfo.NickName, cellStyle); } return(NameToStyle); }
// 有DataTable生成Excel private static void DataTableExport(ExportExcelPageMaker configData, Dictionary <string, ICellStyle> contentStyles, ISheet sheet, DataTable dataTable) { int rowIndex = 1; foreach (DataRow row in dataTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); int columnIndex = 0; foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICell cell = dataRow.CreateCell(columnIndex); if (fieldInfo != null) { string strValue = string.Empty; Tk5ExtensionConfig ex = fieldInfo.Extension; SimpleFieldControl sfctrl = fieldInfo.InternalControl; if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None) { strValue = (row[fieldInfo.NickName]).ToString(); if (!string.IsNullOrEmpty(strValue)) { if (sfctrl != null && sfctrl.SrcControl == ControlType.CheckBox) { if ((ex != null && strValue == ex.CheckValue) || (ex == null && strValue == "1")) { cell.SetCellValue("√"); } } else { CellPadding(strValue, cell, fieldInfo); } } } else { strValue = row[fieldInfo.NickName + "_Name"].ToString(); if (!string.IsNullOrEmpty(strValue)) { if (sfctrl != null && (sfctrl.SrcControl == ControlType.CheckBoxList || sfctrl.SrcControl == ControlType.MultipleEasySearch)) { MultipleDecoderData data = MultipleDecoderData.ReadFromString(strValue); cell.SetCellValue(string.Join(", ", data)); } else { cell.SetCellValue(strValue); } } } cell.CellStyle = contentStyles[fieldInfo.NickName]; } columnIndex++; } rowIndex++; } }
// 导出模板 public static byte[] CreateExcelTemplate(ExportExcelPageMaker configData) { MemoryStream ms = new MemoryStream(); using (ms) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(configData.fMetaData.Table.TableDesc); HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); int index = 0; foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICell cell = dataRow.CreateCell(index); ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook, configData, fieldInfo, NPOIWrite.Model.Header); cell.SetCellValue(fieldInfo.DisplayName); cell.CellStyle = styleHeader; int colWith = NPOIWrite.GetColWidth(fieldInfo); sheet.SetColumnWidth(index, colWith << 8); HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo); if (dataValidate != null) { ((HSSFSheet)sheet).AddValidationData(dataValidate); } } workbook.Write(ms); ms.Flush(); return ms.ToArray(); } }
//生成Dictionary<NickName, ICellStyle>, 每一列对应每一个ICellStyle,通过NickName来获取ICellStyle private static Dictionary<string, ICellStyle> GetContentStyles(IWorkbook workbook, ExportExcelPageMaker configData) { Dictionary<string, ICellStyle> NameToStyle = new Dictionary<string, ICellStyle>(); IDataFormat format = workbook.CreateDataFormat(); foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICellStyle cellStyle = BorderAndFontSetting(workbook, configData, fieldInfo, Model.Content); if (fieldInfo.Extension != null && !string.IsNullOrEmpty(fieldInfo.Extension.Format)) cellStyle.DataFormat = format.GetFormat("@"); else { switch (fieldInfo.DataType) { case TkDataType.Long: case TkDataType.Int: case TkDataType.Short: case TkDataType.Byte: case TkDataType.Double: case TkDataType.Decimal: case TkDataType.Money: if (fieldInfo.DataType == TkDataType.Money) cellStyle.DataFormat = format.GetFormat("¥#,##0"); else cellStyle.DataFormat = format.GetFormat("0"); break; case TkDataType.String: case TkDataType.Text: case TkDataType.Guid: case TkDataType.Xml: cellStyle.DataFormat = format.GetFormat("@"); break; case TkDataType.DateTime: case TkDataType.Date: if (fieldInfo.DataType == TkDataType.DateTime) cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm"); else cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); break; } } NameToStyle.Add(fieldInfo.NickName, cellStyle); } return NameToStyle; }
internal static ICellStyle BorderAndFontSetting(IWorkbook workbook, ExportExcelPageMaker configData, Tk5FieldInfoEx metaInfo, Model model) { ICellStyle cellStyle = workbook.CreateCellStyle(); if (configData.UserBorder) { cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; } if (model == Model.Content) { IFont fontContent = FontSetting(workbook, configData.Content); cellStyle.SetFont(fontContent); if (metaInfo.Extension != null) { AlignSetting(metaInfo.Extension.Align, cellStyle); } else { AlignSetting(configData.Content.Align, cellStyle); } } else { AlignSetting(configData.Header.Align, cellStyle); IFont fontHeader = FontSetting(workbook, configData.Header); cellStyle.SetFont(fontHeader); } return cellStyle; }
// 有DataTable生成Excel private static void DataTableExport(ExportExcelPageMaker configData, Dictionary<string, ICellStyle> contentStyles, ISheet sheet, DataTable dataTable) { int rowIndex = 1; foreach (DataRow row in dataTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); int columnIndex = 0; foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList) { ICell cell = dataRow.CreateCell(columnIndex); if (fieldInfo != null) { string strValue = string.Empty; Tk5ExtensionConfig ex = fieldInfo.Extension; SimpleFieldControl sfctrl = fieldInfo.InternalControl; if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None) { strValue = (row[fieldInfo.NickName]).ToString(); if (!string.IsNullOrEmpty(strValue)) { if (sfctrl != null && sfctrl.SrcControl == ControlType.CheckBox) { if ((ex != null && strValue == ex.CheckValue) || (ex == null && strValue == "1")) cell.SetCellValue("√"); } else CellPadding(strValue, cell, fieldInfo); } } else { strValue = row[fieldInfo.NickName + "_Name"].ToString(); if (!string.IsNullOrEmpty(strValue)) { if (sfctrl != null && (sfctrl.SrcControl == ControlType.CheckBoxList || sfctrl.SrcControl == ControlType.MultipleEasySearch)) { MultipleDecoderData data = MultipleDecoderData.ReadFromString(strValue); cell.SetCellValue(string.Join(", ", data)); } else cell.SetCellValue(strValue); } } cell.CellStyle = contentStyles[fieldInfo.NickName]; } columnIndex++; } rowIndex++; } }
// 模板 public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData) { MemoryStream ms = new MemoryStream(); using (ms) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc); HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); int index = 0; ExportExcelPageMaker headerConfigData = new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = true }; ExportExcelPageMaker contentConfigData = new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent) { UserBorder = false }; foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList) { int colWith = NPOIWrite.GetColWidth(fieldInfo); sheet.SetColumnWidth(index, colWith << 8); ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook, contentConfigData, fieldInfo, NPOIWrite.Model.Content); HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook); sheet.SetDefaultColumnStyle(index, styleContent); if (dataValidate != null) { ((HSSFSheet)sheet).AddValidationData(dataValidate); } ICell cell = dataRow.CreateCell(index); ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook, headerConfigData, fieldInfo, NPOIWrite.Model.Header); cell.SetCellValue(fieldInfo.DisplayName); cell.CellStyle = styleHeader; index++; } //string strFileName = @"D:\EmportTemplateTest.xls"; //using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) //{ // workbook.Write(fs); //} workbook.Write(ms); ms.Flush(); return ms.ToArray(); } }