I wrote an ExcelHelper generic class that can be used to read or generate data.

I wrote an ExcelHelper generic class that can be used to read or generate data.

There are many ways to read or generate EXCEL data, which are common:

1. Easy to read and generate EXCEL files through the office excel component. Disadvantages: OFFICE software must be installed on the server and the process cannot be released in time.

2. Third-party components (such as NPOI). Advantage: no OFFICE software is required. disadvantage: third-party components need to be introduced. Of course, this is relatively strong.

3. by using EXCEL as a database, you can use SQL statements to read data after connection. If you write data, you can splice it into an HTML table. Advantages: no additional components are required. Disadvantages: it is difficult to splice SQL statements and HTML tables;

I have used either of the three methods. If BS website programs are developed, we recommend the second and third methods. If the CS structure is developed, the first or second methods are recommended;

The following is a general ExcelHelper class I wrote for BS. It is convenient to read or generate data. The technical principle is the third method described above. The Code is as follows, which may have defects, experts forgive me:

Namespace ASOTS. models {public abstract class ExcelHelper {/// <summary> // obtain the specified sheet content in EXCEL /// </summary> /// <returns> </returns> public static DataTable GetTableFromExcel (string filePath, string fileExt, string tableName, int colsCount) {string connstr = null; if (fileExt = ". xls ") {connstr =" Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ filePath +"; Extended Properties = 'excel 8.0"; HDR = Yes; IMEX = 1' ";} else {connstr =" Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ filePath +"; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1' ";}using (OleDbConnection excelConn = new OleDbConnection (connstr )) {excelConn. open (); // obtain the EXCEL schema information DataTable schemaTable = excelConn. getOleDbSchemaTable (OleDbSchemaGuid. tables, new Object [] {null, "TABLE"}); // determines whether the specified sheet name has Da TaView schemaView = new DataView (schemaTable); schemaView. RowFilter = "TABLE_NAME = '" + tableName + "$"; schemaTable = schemaView. ToTable (); if (schemaTable! = Null & schemaTable. rows. count> 0) {DataTable schemaTable_Cols = excelConn. getOleDbSchemaTable (OleDbSchemaGuid. columns, new object [] {null, null, tableName + "$", null}); schemaView = new DataView (schemaTable_Cols); schemaView. rowFilter = "ORDINAL_POSITION <=" + colsCount. toString (); schemaView. sort = "ORDINAL_POSITION asc"; schemaTable_Cols = schemaView. toTable (); string selectCols = ""; (Int I = 0; I <schemaTable_Cols.Rows.Count; I ++) {selectCols + = "," + schemaTable_Cols.Rows [I] ["COLUMN_NAME"]. toString ();} selectCols = selectCols. substring (1); // query the data string strSql = "select" + selectCols + "from [" + tableName + "$]"; OleDbDataAdapter da = new OleDbDataAdapter (strSql, excelConn); DataSet ds = new DataSet (); da. fill (ds, tableName); excelConn. close (); return ds. tables [TableName];} else {excelConn. close (); return null ;}}} /// <summary> /// generate an HTML table string from the data model set object /// </summary> /// <param name = "data"> </param> /// <param name = "tableAttributes"> </param> /// <param name = "headers"> </param> /// <returns> </returns> public static string SetDataToHtmlTable (IEnumerable data, string tableAttributes, params KeyValuePair <string, string> [] headers) {StringBuilder htmlTabl EBuilder = new StringBuilder (); htmlTableBuilder. appendFormat ("<table {0}>", tableAttributes); if (data. getEnumerator (). current = null) {throw new Exception ("No data is obtained! ");} Type t = data. getEnumerator (). current. getType (); string [] cellIndexs = new string [headers. count ()]; htmlTableBuilder. append ("<tr>"); for (int I = 0; I 
The call method is as follows:
// Read the data DataTable resultTable = ExcelHelper. getTableFromExcel (saveFilePath, fileExt, "data", 10); // generate a table (below is the MVC call, WEBFORM likewise) KeyValueList <string, string> headers = new KeyValueList <string, string> () {"year", "annual" },{ "month", "Monthly" },{ "stage1count", "Level 1 "}, {"stage2count", "Level 2" },{ "stage3count", "Level 3" },{ "stage4count", "Level 4" },{ "yearincount ", "entering the factory within one year" },{ "stagetotalcount", "base disk customer count" },{ "stage1rate", "Level 1 proportion" },{ "stage2rate ", "Second-Order proportion" },{ "stage3rate", "third-order proportion" },{ "stage4rate", "fourth-order proportion "}}; string tableAttributes = "border = '1' cellspacing = '3' cellpadding = '3'"; string htmlTable = ExcelHelper. setDataToHtmlTable (model, tableAttributes, headers. toArray (); byte [] B = System. text. encoding. UTF8.GetBytes (htmlTable); return File (B, "application/vnd. ms-excel ", string. format ("stagesummary_1_01__1_11__1_22.16.xls", orgcode, startym, endym ));
Here: KeyValueList is a collection class that I created. It is mainly used to generate a table header and correspond to a table header and data column. It is written as a class because List <KeyValuePair <TKey, TValue>, you cannot directly use the set initializer. You must ADD objects one by one. This is complicated. After the ADD method is added, you can directly use: new KeyValueList <string, string> (){{"",""},...} it is very convenient. Some people may say why SortedDictionary and other existing sorting classes are not needed, because SortedDictionary is based on Key sorting, and here it uses the order of ADD to fix the order.
For more IT related information and technical articles, welcome to my personal website: http://www.zuowenjun.cn/