C # uses OPENXML to read Excel data instances

The example of this paper is about C # using OPENXML to read Excel data, and share it for everyone's reference. The specific analysis is as follows:

Here are some questions, if the cell inside is a date and float, the corresponding cell.datatype==null, the corresponding time will be converted to a floating point type, for this block can be converted to time by Datetime.fromoadate (double D). But the disadvantage is that if cell.datatype ==null, there is no way to confirm whether the data is floating point or [is converted to a date floating point number]. Read a lot of foreign materials, indeed, some of the foreign blog has been reflected. Questions about Cell.datatype==null when OpenXML reads Excel. This example does not take into account the problem, and it is not solved yet. And so on later query to more detailed information to solve.

Second, the solution to this problem only, in the data processing time, we can know this column of data in the end is what type, and then according to their own needs, the data obtained by the corresponding conversion processing. However, if you use OLE DB's SELECT statement to read Excel, this problem does not occur, and when read to datable, the date is not converted to floating-point data. Also, the datable of the object can be cast directly to DateTime for that cell data. But reading the data with OLE DB feels like there should be no OPENXML. It's too late to test big data yet. It's time to sleep. If a great God knows OPENXML read the table, point to [the problem is: the datetime type and floating-point type data of the cell in the Excel table, how to differentiate after fetching. Because the date will be automatically converted to floating-point type when OPENXML gets used)

The reference code is as follows:

Using system;using system.collections.generic;using system.data;using system.linq;using System.Text;using System.threading.tasks;using documentformat.openxml.packaging;using Documentformat.openxml.spreadsheet;namespace      readexcel{public class Program {static void Main (string[] args) {DataTable dt = new DataTable ();  using (spreadsheetdocument spreadsheetdocument = Spreadsheetdocument.open (@ "test.xlsx", False)) {Workbookpart        Workbookpart = Spreadsheetdocument.workbookpart; ienumerable<sheet> sheets = spreadsheetdocument.workbookpart.workbook.getfirstchild<sheets> ().        Elements<sheet> (); String relationshipid = Sheets. First (). Id.value = Sheets. First (x = X.name = = "Testsheet").        Id.value;        Worksheetpart Worksheetpart = (worksheetpart) spreadSheetDocument.WorkbookPart.GetPartById (relationshipid);        Worksheet Worksheet = Worksheetpart.worksheet;  Sheetdata sheetdata = worksheet.getfirstchild<sheetdata> ();      row[] rows = sheetdata.descendants<row> ().        ToArray (); Set the table header DataTable foreach (cell cell in rows. ElementAt (0)) {dt.        Columns.Add (String) getcellvalue (spreadsheetdocument, cell)); }//Add content for (int rowIndex = 1; rowIndex < rows. Count (); rowindex++) {DataRow temprow = dt.          NewRow (); for (int i = 0; i < Rows[rowindex]. Descendants<cell> (). Count (); i++) {Temprow[i] = Getcellvalue (Spreadsheetdocument, Rows[rowindex]. Descendants<cell> ().          ElementAt (i)); } dt.        Rows.Add (Temprow);    }} console.readkey (); public static string Getcellvalue (spreadsheetdocument document, cell cell) {Sharedstringtablepart stringtabl Epart = document.      Workbookpart.sharedstringtablepart; String value = Cell.      Cellvalue.innerxml; if (cell. DataType! = null && (cell. Datatype.value = = Cellvalues.sharedstring | | Cell. Datatype.value = = Cellvalues.string | | Cell. Datatype.value = = Cellvalues.number)) {return Stringtablepart.sharedstringtable.childelements[int32.parse (val UE)].      InnerText; } else//floating-point number and the date corresponding to the cell. DataType are null {//Datetime.fromoadate (double. Parse (value)); If a date is determined, it can be converted directly to a Date object using this method, but it is not possible to determine whether the cell data is floating-point or date datatype==null.      (the date is automatically converted to a floating-point return value; }    }  }}

I hope this article is helpful to everyone's C # programming.

In addition to the Declaration, Running GuestArticles are original, reproduced please link to the form of the address of this article
C # uses OPENXML to read Excel data instances

This address: http://www.paobuke.com/develop/c-develop/pbk23476.html

Related content C # implementation of File upload and multi-file upload function WinForm realization of the mouse can penetrate the form of the hollow effect in-depth explanation of the definition of nested types and anonymous types in C # programming and the use of C # Loop statement collection and case study
C # Console for file reading and writing C # Create a Thumbnail action class instance analysis of C # Implementation of multi-threaded synchronous Method Example DevExpress setting lable position instance of pie chart

C # uses OPENXML to read Excel data instances