Use openxml to read the Excel content to the datatable

The previous articles briefly introduced how to use openxml to create an Excel document. In normal work, you often need to use Excel read/write operations. In a brief introduction, you can use openxml to read data in Excel. Of course, it is not important to use openxml to read data into any format. This article only describes how to read data to the able.

Preparations:

1. One excel2007 document;

2. openxml Library: documentformat. openxml. dll;

3. Add references to the openxml library and windowsbase. dll in the console project.

If you don't talk nonsense, go to the topic.

The following describes how to read an Excel file:

1. Load the Excel file to the stream;

2. Use openxml to operate stream and write it to datatable.

There are many ways to load files to stream. I will not go into details here. This article mainly introduces step 2.

/// <Summary> /// organize the data into a datatable according to the given Excel stream // </Summary> /// <Param name = "stream"> Excel file stream </ param> /// <Param name = "sheetname"> sheet to be read </param> /// <returns> organized datatable </returns> private datatable readexcel (string sheetname, stream stream) {using (spreadsheetdocument document = spreadsheetdocument. open (stream, false) {// open stream ienumerable <sheet> sheets = document. workbookpart. workbook. desce Ndants <sheet> (). where (S => S. name = sheetname); If (sheets. count () = 0) {// find the appropriate sheet. If no sheet is found, return NULL;} worksheetpart = (worksheetpart) document. workbookpart. getpartbyid (sheets. first (). ID); // obtain the sharedstringtable stringtable = document. workbookpart. sharedstringtablepart. sharedstringtable; ienumerable <row> rows = worksheetpart. worksheet. descendants <row> (); // obtain data in Excel Row datatable dt = new datatable ("Excel"); // Since data needs to be imported into the datatable, we assume that the first row of Excel is the column name, starting from the second row is the row data foreach (Row row in rows) {If (row. rowindex = 1) {// Excel first action column name getdatacolumn (row, stringtable, ref DT);} getdatarow (row, stringtable, ref DT ); // The second row of Excel is the first row of data of datatable at the same time} return DT ;}} /// <summary> /// construct the column of the datatable /// </Summary> /// <Param name = "row"> row object defined in openxml </param> /// <Param name = "stri Ngtablepart "> </param> /// <Param name =" DT "> able object to be returned </param> /// <returns> </returns> Public void getdatacolumn (row row, sharedstringtable stringtable, ref datatable DT) {datacolumn Col = new datacolumn (); dictionary <string, int> columncount = new dictionary <string, int> (); foreach (cell in row) {string cellval = getvalue (cell, stringtable); Col = new datacolumn (cellval); If (iscontai Nscolumn (DT, col. columnname) {If (! Columncount. containskey (Col. columnname) columncount. add (Col. columnname, 0); Col. columnname = Col. columnname + (columncount [col. columnname] ++);} DT. columns. add (COL) ;}//< summary> /// construct each row of data in the datatable, and return the datatable /// </Summary> /// <Param name = "row"> openxml row </param> /// <Param name = "stringtablepart"> </param> // <Param name = "DT"> datatable </param> private void getdatarow (Row row, sharedstri Ngtable stringtable, ref datatable DT) {// read algorithm: Read a grid by row, if the entire row is empty, ignore the change (because I do not need to empty rows)-_-datarow DR = DT. newrow (); int I = 0; int nullrowcount = I; foreach (cell in row) {string cellval = getvalue (cell, stringtable); If (cellval = string. empty) {nullrowcount ++;} Dr [I] = cellval; I ++;} If (nullrowcount! = I) {DT. rows. add (DR );}} /// <summary> /// obtain the value of the unit cell. /// </Summary> /// <Param name = "cell"> </param> // <param name = "stringtablepart"> </param> // <returns> </returns> private string getvalue (cell, sharedstringtable stringtable) {// because Excel data is stored in sharedstringtable, You need to obtain the index string value = string in sharedstringtable. empty; try {If (cell. childelements. count = 0) return value; value = double. pa RSE (cell. cellvalue. innertext). tostring (); If (cell. datatype! = NULL) & (cell. datatype = cellvalues. sharedstring) {value = stringtable. childelements [int32.parse (value)]. innertext ;}} catch (exception) {value = "N/A";} return value ;} /// <summary> /// determine whether a grid has a column. /// </Summary> /// <Param name = "DT"> grid </param> /// <Param name = "columnname"> column name </param> // <returns> </returns> Public bool iscontainscolumn (datatable DT, string columnname) {If (Dt = NULL | columnname = NULL) {return false;} return DT. columns. contains (columnname );}

Usage:

 

Filestream FS = new filestream (@ "D: \ 1.xlsx", filemode. Open, fileaccess. Read, fileshare. Read); datatable dt = readexcel ("sheet1", FS );

 

 

 

Via: http://www.cnblogs.com/tewuapple/archive/2012/09/03/2668725.html

Use openxml to read the Excel content to the datatable