. NET Excel operation instance sharing

1. Read

It seems that there are several ways to read data: Using ADO.net, using Microsoft. Interop. Excel to support the class library to read data by calling COM, and finally reading the DOM through ZIP extraction (this seems quite complicated)
Here I only use ADO. NET to introduce this one.

Copy codeThe Code is as follows:
Public DataTable ExcelToDataTable (string strExcelPath, string strSheetName)
{
String strConn =
"Provider = Microsoft. Jet. OLEDB.4.0;" + "Data Source =" + strExcelPath + ";" + "Extended Properties = Excel 5.0 ;";
String strExcel = string. Format ("select * from [{0} $]", strSheetName );
DataSet ds = new DataSet ();
OleDbDataAdapter adapter = new OleDbDataAdapter (strExcel, strConn );
Adapter. Fill (ds, strSheetName );
Conn. Close ();
Return ds. Tables [strSheetName];
}

Parameter: path of the StrExcelPath Excel file. The name of the table to be read by strSheetName
Paste the code for reading the SheetName. This is to call Microsoft. Interop. Excel to read
Excel. workbook theWorkbook = ExcelObj. workbooks. open ("Excel file path", 0, true, 5, "", "", true, Excel. xlPlatform. xlWindows, "\ t", false, false, 0, true );
Excel. Sheets sheets = theWorkbook. Worksheets;
Excel. Worksheet worksheet = (Excel. Worksheet) sheets. get_Item (1 );
ExecName = worksheet. Name;
TheWorkbook. Close (null, null, null); // remember to Close it. Otherwise, the program is closed and the Excel process is still
2. Create a New excel File
Copy codeThe Code is as follows:
Excel. ApplicationClass myExcel = new Excel. ApplicationClass (); // an excel instance
Excel. _ Workbook xBk; // The Workbook is equivalent to an Excel file.
Excel. _ Worksheet xSt; // tables in a file of the Work Sheet
XBk = myExcel. Workbooks. Add (true); // you can Add sheet.
Object missing = System. Reflection. Missing. Value; // null
MyExcel. Sheets. Add (missing, missing, 1, Excel. XlSheetType. xlWorksheet); // Add sheet
XSt = (Excel. _ Worksheet) xBk. ActiveSheet; // get the default sheet
XSt. Name = "new table"; // set the table Name here
XBk. SaveAs ("Save path", missing, missing,
Missing, Excel. XlSaveAsAccessMode. xlShared,
Missing, and missing); // save the file
MyExcel. Quit (); // remember to close it again

3. Add content
This section only describes how to add General Data. Charts are not introduced. In Excel operations, tables and ranges are used as units. Rows or columns start from 1 rather than 0.
First, we will introduce the format settings as follows:
For example, set the first line to Red: (Excel. range) xSt. rows [1, Type. missing]). font. color = 0xFF0000; // xSt is the variable name of the preceding code. The Color value is in hexadecimal RGB format.
Set the format of the second column (Excel. Range) xSt. Columns [2, Type. Missing]). NumberFormat = "0.00% ";
Common backup format: Text: @ Date: yyyy/mm/dd numbers: #, #0.00 currency: ￥ #, #0.00 percentage: 0.00%
The above two options are row selection and column selection. If you want to select 1st rows, 2nd columns, and 1st columns, use get_Range ();
XSt. get_Range (xSt. Cells [1, 2], xSt. Cells [1, 5])
If you need other formats, such as cell background, border, and font style, check the document. However, most of the method names from the vertex are available.
Then, add the content and directly write the code reference.
Copy codeThe Code is as follows:
Int rowIdx = 2; // starts from 2nd
// Here, dt is the DataTable data source.
Foreach (DataRow dr in dt. Rows)
{
Int j = 1; // starting from column 1st
MyExcel. Cells [rowIdx, j ++] = dr ["dt column name"]. ToString ();
MyExcel. Cells [rowIdx, j ++] = dr ["dt column name"]. ToString ();
MyExcel. Cells [rowIdx, j ++] = dr ["dt column name"]. ToString ();
MyExcel. Cells [rowIdx, j ++] = dr ["dt column name"]. ToString ();
MyExcel. Cells [rowIdx, j ++] = dr ["dt column name"]. ToString ();
MyExcel. Cells [rowIdx, j ++] = dr ["dt column name"]. ToString ();
// Use the formula to display the result of A + B + C + D
MyExcel. Cells [rowIdx, j ++] = string. Format ("= SUM (A {0}: D {0})", rowIdx );
RowIdx ++;
}

After saving xBk. save (), remember to close it.