Share a simple Excel-exported class library, excel class library

Share a simple Excel-exported class library, excel class library

Features:

Let's talk about the whole design,

1. First, there is an IExcelExporter interface. The first is as follows:

1 public interface IExcelExporter2     {3         Task BuildSheetsAsync(4             Stream stream, IEnumerable<Settings> settings_list, bool useOldVersion = false,5             object hostContext = null);6     }

2. Then there is an abstract class, ExcelExporterBase <TWorkBook, TWorkSheet, TCellStyle> to implement the IExcelExporter interface.

Abstract classes encapsulate algorithms for creating headers, creating table topics, and merging repeated values in columns. Then some abstract methods that need to be implemented in the subclass are defined. As follows:

 1 #region [ protected ] 2  3         protected abstract TWorkBook CreateWorkBook(bool useOldVersion); 4  5         protected abstract TWorkSheet CreateWorkSheet(TWorkBook workbook, string name, Settings settings); 6  7         protected abstract TCellStyle CreateCellStyle(TWorkBook workbook, TCellStyle style, CellStyle cellStyle); 8  9         protected abstract TCellStyle GetCellStyle(TWorkBook workbook, TWorkSheet sheet, int row, int col);10 11         protected abstract void SetCell(TWorkBook workbook, TWorkSheet sheet, int row, int col, Settings settings, ColNode node, object value, TCellStyle style, bool setValue = true, bool setStyle = true);12 13         protected abstract void MergeCells(TWorkBook workbook, TWorkSheet sheet, int startRow, int endRow, int startCol, int endCol, Settings settings);14 15         protected abstract void FreezePane(TWorkBook workbook, TWorkSheet sheet, int rowSplit, int colSplit);16 17         protected abstract void SetColumnsWidth(TWorkBook workbook, TWorkSheet sheet, Settings settings, uint[] widthArray, int startCol);18 19         protected abstract void AutoFillColumns(TWorkBook workbook, TWorkSheet sheet, Settings settings, int startCol);20 21         protected abstract void Save(TWorkBook workbook, bool useOldVersion, Stream stream);22 23         #endregion

Several abstract methods are well understood, and the functions to be implemented are easily known based on the method name.

3. In the specific implementation, I used Aspose. Cells and NPOI to implement one set.

Aspose_Cells_ExcelExporter and NPOI_ExcelExporter classes.

 

4. Example.

DataTable dt = MAIN. get_Users (); Settings set = new Settings () {PageSize = 100, Name = "", ColumnsAutoWidth = true, StartColIndex = 1, StartRowIndex = 1, DataSource = new Lazy <System. collections. generic. IEnumerable <SysUser> () => {string str = ConfigurationManager. connectionStrings ["mysql_demo"]. connectionString; return MAIN. helper. toEnumerable2 <SysUser> ("SELECT * FROM SysUser"); // MAIN. get_Users () ;}), FreezeHeader = true, // RowStyleGetter = (I, row) => I % 2 = 0? CellStyle. body: new CellStyle () {BgColor = Color. white, FgColor = RandColor ()}, RootNodes = new [] {new ColNode () {Title = "user list", ChildColNodes = dt. columns. cast <DataColumn> (). select (col => new ColNode {Title = col. columnName, Field = col. columnName, MergeField = col. columnName }). toArray () }}; IExcelExporter ep = new NPOI_ExcelExporter (); Task task = ep. buildSheetsAsync (new FileStream (". /test.xlsx ", FileMode. create, FileAccess. write), new [] {set}, false); task. wait ();

During the export, the header is used as a parent column, and all columns of the DataTable are used as subcolumns.

For example, the SysUser table has the following structures: ID, UserName, and Sex.

The exported Excel format is as follows:

User List
ID	UserName	Sex

 

 

 

You are already open-source in China. If you need it, you can download it.