Openxml getting started --- Openxm reading Excel Data, openxml --- openxm

Openxml getting started --- Openxm reading Excel Data, openxml --- openxm

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 = SpreadsheetDocument. open (@ "Test. x Lsx ", 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); W Orksheet workSheet = worksheetPart. worksheet; SheetData sheetData = workSheet. getFirstChild <SheetData> (); Row [] rows = sheetData. descendants <Row> (). toArray (); // sets the 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 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 ;}}}}

 

How to import an Excel file to data by reading an XML configuration file

1. Generate XML based on the dataset and use it in Excel
This section describes how to create a DataSet object and how to use the WriteXML method to export the data contained in the object to an XML file. DataSet object is a key part of data access in Microsoft. NET Framework. It is an object in memory that contains tables, views, and relationships.

You can open an XML file directly generated in Excel. For instructions, use the Jet OLEDB provider to create a DataSet object based on the Access Northwind example. Similar code can be used to create a DataSet object using Visual Basic. NET.

1.
Start Microsoft Visual Studio®. NET. On the File menu, click New, and then click Project ". Select Windows application from the Visual Basic project type ". By default, Form1 is created.

2.
On the "View" menu, select "toolbox" and add a button in Form1.

3.
Double-click Button1. The code window of the form appears.

4.
Add the following dedicated member variables to the Form1 class:

Private strConn As String = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & _ "C: \ Program Files \ Microsoft Office \ Office11 \ Samples \ Northwind. mdb ;"
Note: you may need to modify the path of Northwind. mdb in the connection string to match the installation. You can download the Northwind Traders sample database from the Microsoft download center. In addition, note that there is a space between Data and Source in the path string.

5.
Add the following code to the button#click handler:

'Connect to the data source.
Dim objConn As New System. Data. OleDb. OleDbConnection (strConn)
Try
ObjConn. Open ()
'Fill the dataset with records in the customer table.
Dim strSQL As String
Dim objDataset As New DataSet
Dim objAdapter As New System. Data. OleDb. OleDbDataAdapter
'Oledbdataadapter serves as a bridge between data sources,
'In this example, it is a bridge between the customer table and the dataset.
StrSQL = "Select CustomerID, CompanyName, ContactName ,"&_
"Country/region and phone number of the customer"
ObjAdapter. SelectCommand = New System. Data. OleDb. OleDbCommand (_
StrSQL, objConn)
ObjAdapter. Fill (objDataset)
'Create the FileStream to use.
Dim strFilename As String
StrFilename = "C: \ Cu ...... remaining full text>

C # How to Use openxml as an excel report? How to use excel to populate the data queried by a template with the specified cells in the template?

We recommend that you reference SpreadsheetGear. dll to create an excel worksheet. It is very powerful.
After the template is available, no matter whether your data source is xml or whatever, use the methods and interfaces here to directly fill in the document.