1. Read
There seem to be several ways to read, through the support class library to read with call COM, and also through ZIP decompression and finally read the DOM (this seems quite complicated)
I will only introduce this one here.
public DataTable ExcelToDataTable(string strExcelPath, string strSheetName)
{
string strConn =
"Provider=.4.0;" + "Data Source=" + strExcelPath + ";" + "Extended Properties=Excel 5.0;";
string strExcel = ("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
(ds, strSheetName);
();
return [strSheetName];
}
Parameters: StrExcelPath The path of the Excel file, strSheetName The name of the table to be read
Here is a code to read SheetName, which is called to read
theWorkbook = ("Excel file path", 0, true, 5, "", "", true, , "\t", false, false, 0, true);
sheets = ;
worksheet = ()sheets.get_Item(1);
ExecName = ;
(null, null, null);//Remember to close, otherwise the program will be closed and the Excel process will still be there
2. Create a new excel file
myExcel = new (); //Instance an excel
Excel._Workbook xBk; //Workbook Equivalent to an Excel file
Excel._Worksheet xSt; //Worksheet Table in a file
xBk = (true); //Add to add sheet
object missing = ;//Null value
(missing, missing, 1, );//Add sheet
xSt = (Excel._Worksheet);//Get the default sheet
= "New table"; //Set the table name here
("Save path", missing, missing,
missing, missing, missing, ,
Missing, missing, missing, missing); //Save the file
(); //Remember to close it too
3. Add content
Here we will only talk about the addition of general data, but we will not introduce the chart. In Excel operations, the table and range are generally used as units, and rows or columns start from 1 instead of 0.
Let me introduce the format settings first, as follows
If the font on the first line is set to red: (()[1, ]). = 0xFF0000; //xSt is the variable name of the above code, and the color value is in hexadecimal RGB
Set the format of the second column as a percentage (()[2, ]).NumberFormat = "0.00%";
Common formats: Text: @ Date: yyyy/mm/dd Number: #,##0.00 Currency: ￥#,##0.00 Percentage: 0.00%
The above two are row selection and column selection. If you want to select row 1, column 2 to row 1, column 5, use get_Range();
xSt.get_Range([1,2],[1,5])
If you need other formats, such as cell background, border, font style, check the document below. But most of the methods you click on can be known.
Then add content and write some code to reference it directly.
int rowIdx = 2; //From row 2
//Here dt is the DataTable data source
foreach( DataRow dr in )
{
int j = 1; //Start from column 1
[rowIdx, j++] = dr["dt column name"].ToString();
[rowIdx, j++] = dr["dt column name"].ToString();
[rowIdx, j++] = dr["dt column name"].ToString();
[rowIdx, j++] = dr["dt column name"].ToString();
[rowIdx, j++] = dr["dt column name"].ToString();
[rowIdx, j++] = dr["dt column name"].ToString();
//Use the formula to display the results of A+B+C+D
[rowIdx, j++] = ("=SUM(A{0}:D{0})",rowIdx);
rowIdx++;
}
After writing save (), remember to close it.