C # parsing Excel Rollup

The project has been used many times to Excel operations, think of the need to summarize these methods, methods to be able to have a clear understanding of the overall, this article mainly discusses the use of C # for Excel operations, parsing three ways: OLE DB, COM components, Npoi

1.OleDb

This method is simple, fast, and can operate a high version of Excel, the disadvantage is not flexible enough, only simple read and write operations to Excel. when it comes to programming at the very beginning, it is unheard of: Objectlink andembed, which is primarily intended to be compatible with more types of data sources. is a lower-level operation, which is very complex to use, and another feature is the ability to directly manipulate an Excel data source using SQL statements:

<span style= "White-space:pre" ></span>///<summary>/////reading data from Excel to a DataTable///&LT;/S ummary>//<param name= "Strsavepath" > File save path </param>//<param name= "strSheetName" >she ET name </param>///<returns></returns> protected DataTable exceltodatatable (String Strsavepa  Th, String strsheetname) {#region Read file sheet, convert to datatable string strconn;string Strfiletype =              System.IO.Path.GetExtension (Strsavepath); if (string. IsNullOrEmpty (Strfiletype)) return null;if (Strfiletype = = ". xls") strconn = "provider=microsoft.jet.oled b.4.0; "+" Data source= "+ strsavepath+"; "+"; Extended properties=\ "Excel 8.0;              Hdr=yes;imex=1\ ""; else strconn = "provider=microsoft.ace.oledb.12.0;" + "Data source=" + strsavepath+ ";" + "; Extended properties=\ "Excel 12.0;             Hdr=yes;imex=1\ ""; OleDbConnection conn = new OledbconnectIon (strconn); Conn.            Open ();            OleDbDataAdapter mycommand = new OleDbDataAdapter ("SELECT * FROM [" + strSheetName + "$]", strconn);            DataSet myDataSet = new DataSet ();            try {Mycommand.fill (myDataSet, "excelinfo"); } catch {throw new Exception ("config file sheet name configuration Error!            "); } finally {if (conn. state = = ConnectionState.Open) {Conn.                      Close ();                      Mycommand.dispose (); Conn.                  Dispose (); }} DataTable table = mydataset.tables["Excelinfo"].            Defaultview.totable ();            return table; #endregion Read file sheet, convert to DataTable}

2.Com components

very flexible and complete operation Excel, which precisely controls the various properties of an Excel document (storage format, style, content, formulas, and conditions, etc.), if the computer must have Excel installed, the Excel process will start. the program needs to add a reference:Microsoft.Office.Interop.Excel

Use this method to the basic operation of Excel, can refer to: C # Operations Excel Summary

3.NPOI

as the name implies,Npoi is the. NET version of the POI project. Poi is an open source Java program that reads and writes Microsoft OLE2 component documents such as Excel, Word, and so on. This approach avoids the disadvantage of using COM components, does not install Office on the appropriate environment machine, but also combines the flexible features of the operation, reading speed is relatively fast.

NPOI2.0 Main composition:

Assembly name	Modules/namespaces	Description
npoi. DLL	Npoi. Poifs	ole2/activex document properties read-write library
npoi. DLL	Npoi. DDF	Microsoft Office drawing read-write library
npoi. DLL	Npoi. HPSF	ole2/activex document read-write library
npoi. DLL	Npoi. HSSF	Microsoft Excel BIFF (Excel 97-2003, doc) format read/write library
npoi. DLL	Npoi. SS	excel Common interface and Excel formula calculation engine
npoi. DLL	Npoi. Util	basic class library, which provides many useful functions for developing other read-write file format projects
npoi. OOXML. DLL	npoi. XSSF	excel (xlsx) format read-write library
npoi. OOXML. DLL	npoi. XWPF	word (docx) format read-write library
npoi. OpenXml4Net.DLL	npoi. Openxml4net	openxml Bottom zip package read-write library
Npoi. OpenXmlFormats.DLL	Npoi. Openxmlformats	Microsoft Office OpenXML Object Relational Library

use npoi to import Excel as follows:

<pre name= "code" class= "CSharp" >public static DataTable renderfromexcel<t> (Stream excelfilestream) where T            : New () {using (Excelfilestream) {using (Iworkbook workbook = new Hssfworkbook (Excelfilestream)) { using (isheet sheet = workbook.                Getsheetat (0))//Take the first table {DataTable table = new DataTable (); IRow HeaderRow = sheet.                GetRow (0);//First action header line int cellcount = Headerrow.lastcellnum;//lastcellnum = Physicalnumberofcells int rowCount = sheet.  Lastrownum;//lastrownum = PhysicalNumberOfRows-1//Defines an array of Access DB field names string[] Strarrayheader =                New String[cellcount-headerrow.firstcellnum];                Defines the index value required for an array int intarrayindex = 0;                    Handling header. Gets the header row string array for the Excel import file for (int i = 0; i < strarrayheader.length; i++) { Strarrayheader[intarrayindex] = Headerrow.getcell (i). StRingcellvalue;                ++intarrayindex; }//Call method to convert header line literal description to corresponding property name Strarrayheader = CONFIGOPERATER.QUERYIMPORTENTITYPROPERTY&LT;T&G                t; (Strarrayheader);                Intarrayindex = 0; for (int i = headerrow.firstcellnum; i < Cellcount; i++) {//datacolumn column = new DataColumn (Headerrow.getcell (i).                    Stringcellvalue);                    DataColumn column = new DataColumn (Strarrayheader[intarrayindex]); Table.                    Columns.Add (column);                ++intarrayindex; } for (int i = (sheet. Firstrownum + 1); I <= RowCount; i++) {IRow row = sheet.                    GetRow (i); DataRow datarow = table.                    NewRow (); if (row! = null) {for (int j = row). Firstcellnum; J < Cellcount; J + +) {if (row. Getcell (j) = null) datarow[j] = Getcellvalue (row.                        Getcell (j)); }} table.                Rows.Add (DataRow);            } return table; }        }    }}

Summarize:

in the parsing Excel, three different ways, which npoi more like the first two ways of neutralization, let us in the operation of Excel more than a choice, or that sentence, there is nothing good and bad, can only be said in a specific case to choose a more appropriate way.

More information:

C # Operations Excel

NPOI1.2 Tutorials

Experience of several methods of reading Excel in C #

C # parsing Excel Rollup