Openxml getting started --- openxm reading Excel Data

Openxml reads Excel Data: if there are some problems, if the cell contains the date and floating point type, the corresponding cell. datatype = NULL, the corresponding time will be converted to a floating point type. For this part, you can use datetime. fromoadate (double D) is converted to time. However, if cell. datatype = NULL, you cannot determine whether the data is a floating point or [converted to a floating point number of the date]. After reading a lot of foreign materials, some of the foreign blogs have indeed reflected this. The cell. ype = NULL when openxml reads excel. The problem was not considered in this example and has not been resolved yet. You can find more detailed information in the solution .. Second, the only way to solve this problem is to know the type of data in this column during data processing, and then according to your own needs, convert the obtained data. However, if you use the SELECT statement of oledb to read excel, this problem will not occur, and the data read to datable will not be converted to floating point data. In addition, the data in the cell corresponding to the object's datable can be forcibly converted to datetime. However, it is too late to use oledb to read data because openxml is not tested yet. Sleep. If you have knowledge about how to read an openxml table, please refer to [the problem that needs to be solved is: how to differentiate the datetime type and floating point type data of the cell in the Excel table after obtaining the data. Because the date obtained using openxml will be automatically converted to the floating point type]

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. open (@ "test. X LSX ", false) {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) spreadsheetdocument. workbookpart. getpartbyid (relationshipid); W Orksheet worksheet = worksheetpart. worksheet; sheetdata = worksheet. getfirstchild <sheetdata> (); row [] rows = sheetdata. descendants <row> (). toarray (); // sets the header datatable foreach (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) {sharedstringtablepart stringtablepart = document. workbookpart. sharedstringtablepart; string value = cell. cellvalue. inne Rxml; 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 (value)]. innertext;} else // cell corresponding to the floating point number and date. datatype is null {
// Datetime. fromoadate (double. parse (value); if it is determined to be a date, you can directly use this method to convert it to a date object, but you cannot determine whether the cell data is floating or date when datatype = NULL. (date is automatically converted to floating point return value ;}}}}

 

Openxml getting started --- openxm reading Excel Data