Share my EXCEL class library for Import and Export based on NPOI + ExcelReport: ExcelUtility (Continued 3-dynamic generation of multiple Sheet EXCEL during export) and npoi for exporting multiple sheet

Share my EXCEL class library for Import and Export based on NPOI + ExcelReport: ExcelUtility (Continued 3-dynamic generation of multiple Sheet EXCEL during export) and npoi for exporting multiple sheet

The ExcelUtility class library has been constantly optimized and added to my (dream journey) recently. It is now basically stable and can basically meet most of the EXCEL export requirements, this class library has been fully used in our company's large ERP system, and the effect is good. At the special needs of users today, I have added another feature to dynamically generate multiple Sheet EXCEL during export.

 

Add Method 1: authorize GetFormatterContainer Func to export a multi-Sheet file based on an EXCEL template. The method is defined as follows:

/// <Summary> /// use GetFormatterContainer Func to delegate the export of multiple thin files based on EXCEL templates /// </summary> /// <typeparam name = "T"> Data Source </typeparam> /// <param name = "templatePath"> template path </param> /// used in the <param name = "sheetName"> Template workbook name </param> /// <param name = "dataSource"> data source </param> /// <param name = "getFormatterContainer"> Generate a template data format container (SheetFormatterContainer)) delegate: implement the template formatting process in the delegate method </param> /// <param name = "sheetSize"> Number of entries displayed on each workbook Data Records </param> /// <param name = "filePath"> export path, optional </param> // <returns> </returns> public static string ToExcelWithTemplate <T> (string templatePath, string sheetName, IEnumerable <T> dataSource, func <IEnumerable <T>, SheetFormatterContainer> getFormatterContainer, int sheetSize, string filePath = null) {if (! File. Exists (templatePath) {throw new FileNotFoundException (templatePath + "the File does not exist! ");} Bool isCompatible = Common. getIsCompatible (templatePath); if (string. isNullOrEmpty (filePath) {filePath = Common. getSaveFilePath (isCompatible);} else if (isCompatible &&! Path. getExtension (filePath ). equals (". xls ", StringComparison. ordinalIgnoreCase) {throw new ArgumentException ("when the template adopts the compatibility mode (lower version format, such as xls and xlt), the specified exported file format must be xls. ");} If (string. isNullOrEmpty (filePath) return null; int sheetCount = 0; var formatterContainers = new Dictionary <string, SheetFormatterContainer> (); IEnumerable <T> data = null; while (data = dataSource. take (sheetSize )). count ()> 0) {var sheetFormatterContainer = getFormatterContainer (data); sheetCount ++; if (sheetCount = 1) {formatterContainers. add (sheetName, sheetFormatterContainer);} else {FormatterContainers. add (sheetName + sheetCount. toString (), sheetFormatterContainer);} dataSource = dataSource. skip (sheetSize);} string temp_templatePath = null; try {temp_templatePath = Common. createTempFileByTemplate (templatePath, sheetName, sheetCount); filePath = ToExcelWithTemplate (temp_templatePath, formatterContainers, filePath);} finally {if (! String. isNullOrEmpty (temp_templatePath) & File. exists (temp_templatePath) {File. delete (temp_templatePath);} string temp_templateConfigFilePath = Path. changeExtension (temp_templatePath ,". xml "); if (File. exists (temp_templateConfigFilePath) {File. delete (temp_templateConfigFilePath);} return filePath ;}

　　

Briefly describe the implementation principles and steps of the above method:

1. specify the template path, initial work thin name, exported data source, number of records displayed for each work thin, encapsulate and generate a template data format container (SheetFormatterContainer) delegate, and implement the template formatting process in the delegate method;

2. split the data source cyclically based on the number of records displayed for each workbook, calculate the total number of required workbooks, and generate template data to format the container Dictionary (Key: Sheet Name, Value: template data format container object );

3. generate the required temporary template file for calculation in step 2 (stored in the local temporary directory of the system: Temp)

4. Call other reload methods of ToExcelWithTemplate (generate an EXCEL Method Based on the template + multiple sheet) to export the EXCEL;

5. No matter whether the final export is successful or not, the temporary template and the temporary template configuration file will be deleted;

The sample code for testing is as follows:

/// <Summary> // Test method: generate a multi-workbook EXCEL File Based on the template + DataTable. /// </summary> [TestMethod] public void TestExportToExcelWithTemplateByDataTable2 () {DataTable dt = GetDataTable (); string templateFilePath = AppDomain. currentDomain. baseDirectory + "/excel.xls"; // obtain the EXCEL template path string excelPath = ExcelUtility. export. toExcelWithTemplate <DataRow> (templateFilePath, "table", dt. select (), (data) =>{ SheetFormatterContainer formatterContainers = new SheetFormatterContainer (); // instantiate a template data formatting container PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder (); // instantiate a local element formatter partFormatterBuilder. addFormatter ("Title", "cross IT student"); // set the Title value in the template table to cross IT student formatterContainers. appendFormatterBuilder (partFormatterBuilder); // Add it to a thin-format container. Note that CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder () takes effect only when it is added. // instantiate a cell formatterbuilder cellFormatterBuilder. addFormatter ("rptdate", DateTime. today. toString ("yyyy-MM-dd HH: mm"); // set the rptdate value in the template table to the current date formatterContainers. appendFormatterBuilder (cellFormatterBuilder); // Add it to a thin-format container. Note that it takes effect only when it is added. // instantiate a table formatter, data is a split data source (10 records ), tableFormatterBuilder <DataRow> tableFormatterBuilder = new TableFormatterBuilder <DataRow> (data, "name "); // data source settings: data is the focus of tableFormatterBuilder. addFormatters (new Dictionary <string, Func <DataRow, object >{{ "name", r => r ["Col1"]}, // set the name in the template table to the column Col1 {"sex", r => r ["Col2"]} in the DataTable, // set sex in the template table to Col2 {"km", r => r ["Col3"]} in the DataTable column. // set the column Col3 {"score", r => r ["Col4"]} in the DataTable corresponding to the km in the template table. // set score in the template table to Col4 {"result" in the corresponding DataTable column ", r => r ["Col5"]} // map the result in the template table to the Co5 column in the able}); formatterContainers. appendFormatterBuilder (tableFormatterBuilder); // Add it to the thin-format container. Note that the return formatterContainers takes effect only when it is added; // return a template data formatting container}, 10 ); // note that 10 indicates that a new work thin is dynamically generated, and 10 records of Assert are displayed for each work thin. isTrue (File. exists (excelPath ));}

The test results are as follows:

1. generated temporary templates and template configuration files:

 

2. the exported EXCE result is as follows:

 

3. The temporary template and template configuration file have been cleared.

 

Add Method 2: add the multi-Sheet Excel method exported from DataTable (to be precise, modify the ToExcel method and add a sheetSize parameter). The method is defined as follows:

/// <Summary> /// export Excel from DataTable /// </summary> /// <param name = "sourceTable"> DataTable to export data </param> /// <param name = "sheetName"> workbook name, optional </param> /// <param name = "filePath"> export path. optional </param> /// <param name = "colNames"> name of the column to be exported, optional </param> /// <param name = "colAliasNames"> rename the exported column name, optional </param> /// <param name = "colDataFormats"> column formatting set, optional </param> /// <param name = "sheetSize"> specify the number of records displayed on each Workbook. Optional (if this parameter is not specified or the value is smaller than 0, only Generate a workbook) </param> // <returns> </returns> public static string ToExcel (DataTable sourceTable, string sheetName = "result", string filePath = null, string [] colNames = null, IDictionary <string, string> colAliasNames = null, IDictionary <string, string> colDataFormats = null, int sheetSize = 0) {if (sourceTable. rows. count <= 0) return null; if (string. isNullOrEmpty (filePath) {filePath = Common. getS AveFilePath ();} if (string. isNullOrEmpty (filePath) return null; bool isCompatible = Common. getIsCompatible (filePath); IWorkbook workbook = Common. createWorkbook (isCompatible); ICellStyle headerCellStyle = Common. getCellStyle (workbook, true); // ICellStyle cellStyle = Common. getCellStyle (workbook); if (colNames = null | colNames. length <= 0) {colNames = sourceTable. columns. cast <DataColumn> (). or DerBy (c => c. ordinal ). select (c => c. columnName ). toArray ();} IEnumerable <DataRow> batchDataRows, dataRows = sourceTable. rows. cast <DataRow> (); int sheetCount = 0; if (sheetSize <= 0) {sheetSize = sourceTable. rows. count;} while (batchDataRows = dataRows. take (sheetSize )). count ()> 0) {Dictionary <int, ICellStyle> colStyles = new Dictionary <int, ICellStyle> (); ISheet sheet = workbook. createSheet (sh EetName + (++ sheetCount ). toString (); IRow headerRow = sheet. createRow (0); // handling header. for (int I = 0; I <colNames. length; I ++) {ICell headerCell = headerRow. createCell (I); if (colAliasNames! = Null & colAliasNames. containsKey (colNames [I]) {headerCell. setCellValue (colAliasNames [colNames [I]);} else {headerCell. setCellValue (colNames [I]);} headerCell. cellStyle = headerCellStyle; sheet. autoSizeColumn (headerCell. columnIndex); if (colDataFormats! = Null & colDataFormats. containsKey (colNames [I]) {colStyles [headerCell. columnIndex] = Common. getCellStyleWithDataFormat (workbook, colDataFormats [colNames [I]);} else {colStyles [headerCell. columnIndex] = Common. getCellStyle (workbook) ;}/// handling value. int rowIndex = 1; foreach (DataRow row in batchDataRows) {IRow dataRow = sheet. createRow (rowIndex); for (int I = 0; I <colNames. length; I ++) {ICell cell = dataRow. CreateCell (I); // cell. SetCellValue (row [colNames [I]? ""). ToString (); // cell. CellStyle = cellStyle; Common. SetCellValue (cell, (row [colNames [I]? ""). ToString (), sourceTable. columns [colNames [I]. dataType, colStyles); Common. reSizeColumnWidth (sheet, cell);} rowIndex ++;} sheet. forceFormulaRecalculation = true; dataRows = dataRows. skip (sheetSize);} FileStream fs = new FileStream (filePath, FileMode. openOrCreate, FileAccess. readWrite); workbook. write (fs); fs. dispose (); workbook = null; return filePath ;}

Description of code modification: adds the number of records (sheetSize) displayed for each specified workbook to split the data source cyclically and create multiple workbooks;

The sample code for testing is as follows:

/// <Summary> /// Test method: export the DataTable to a multi-workbook EXCEL file. /// </summary> [TestMethod] public void TestExportToExcelByDataTable8 () {DataTable dt = GetDataTable (); string excelPath = ExcelUtility. export. toExcel (dt, "sheet", sheetSize: 10); // specify the number of records displayed for each workbook Assert. isTrue (File. exists (excelPath ));}

The exported EXCE result is as follows:

The source code is synchronously updated to the GIT directory of the open-source community. For the specific address, see the articles in my previous series.

 

Share my NPOI + ExcelReport-based EXCEL class library Import and Export: ExcelUtility other related articles:

Share my EXCEL class library for Import and Export based on NPOI + ExcelReport: ExcelUtility

 

Share my EXCEL class library Import and Export based on NPOI + ExcelReport: ExcelUtility (continued)

 

Share my EXCEL class library for Import and Export based on NPOI + ExcelReport: ExcelUtility (Part 2-template export example)