C # operations on Excel files

There are several problems involved here. First, if the data type of each row in the Excel column is different when the Excel column is converted to a able, the data will be lost, the solution is to convert the Excel file into a CSV file first, but this is done by the program rather than manually. The following code:

/// <Summary>
/// Save the Excel file to another CSV file
/// </Summary>
/// <Param name = "excelfilename"> Excel file path </param>
/// <Param name = "csvfilename"> CSV file path </param>
Public static void saveasexcel (string excelfilename, string csvfilename)
{
// Define a com hollow object (similar to null)
Object missing = system. reflection. Missing. value;

// Create an Excel application object (which will help us start the Excel process)
Excel. Application APP = new excel. applicationclass ();

Workbook WB = app. application. workbooks. Open (excelfilename, missing,
Missing, missing, and missing );
// If no prompt is displayed, use the default option
App. application. displayalerts = false;
// Do not run the Excel Interface
App. application. Visible = false;

Excel. worksheet sheet = (Excel. worksheet) WB. activesheet;
Try
{
// Save it as a CSV job. Pay attention to the Excel. xlfileformat. xlcsv parameter. Save it as another format and set it here
Sheet. saveas (csvfilename, Excel. xlfileformat. xlcsv, missing, missing, false, missing, missing, false );
}
Finally
{

WB. Close (false, missing, missing );

App. Quit ();
}
}
Of course, the prerequisite is that you want to add the Mircosoft. Excel. Core 11 COM component (add reference ).

Next, write VCs to the able, where stream writing is used.

/// <Summary>
/// Convert the data in the CSV file into a Abel
/// </Summary>
/// <Param name = "path"> CSV path </param>
/// <Returns> datatable </returns>
Public datatable csvtods (string path)
{
String line;
String [] split = NULL;
Datatable table = new datatable ("Auto ");
Datarow ROW = NULL;
Streamreader sr = new streamreader (path, encoding. Default );

// Create a data column corresponding to the data source
Line = Sr. Readline ();
Split = line. Split (',');
Foreach (string colname in Split)
{
Table. Columns. Add (colname, system. type. GetType ("system. String "));
}
// Enter the data in the data table
Int J = 0;
While (line = Sr. Readline ())! = NULL)
{
J = 0;
Row = table. newrow ();
Split = line. Split (',');
Foreach (string colname in Split)
{
Row [J] = colname;
J ++;
}
Table. Rows. Add (ROW );
}
Sr. Close ();
// Display data
Return table;
}
The contents saved in CSV files are separated by commas (,). Therefore, separate them with commas.

The second problem is that creating an Excel file does not meet the standards. If you use the file. Create () method to create an Excel file, you cannot create a connection when you use oledbconnetion to create a connection. Therefore, you cannot create an Excel file using this method. Use the following method:

/// <Summary>
/// Create an Excel file
/// </Summary>
/// <Param name = "FILENAME"> file name </param>
/// <Param name = "headerlist"> Excel File Header </param>
Public static void createfile (string filename, list <string> headerlist)
{
If (! File. exists (filename ))
{
// Define a com hollow object (similar to null)
Object missing = system. reflection. Missing. value;

// Create an Excel application object (which will help us start the Excel process)
Excel. Application APP = new excel. applicationclass ();

// Create a new workbook in Excel
App. application. workbooks. Add (true );

// Obtain the current workbook (the one you just created)
Excel. Workbook books = (Excel. workbook) app. activeworkbook;

// Obtain the worksheet of the current activity from the current workbook
Excel. worksheet sheets = (Excel. worksheet) books. activesheet;

// Write data directly to the specified cell through rows and columns (2nd rows and 2nd columns, that is, "B2 ")
For (INT I = 0; I {
Sheets. cells [1, I + 1] = headerlist [I];
}

// Save the file
Books. savecopyas (filename );

// Close the file
Books. Close (false, missing, missing );

// Exit Excel
App. Quit ();
}
}

Here, filename is the absolute path of the Excel file to be created, and headerlist is the title of the Excel file to be prestored.

The third problem is to write data in the able into excel. If the data in the datatable has single quotation marks or other special symbols, it is easy to make mistakes when inserting the data in the Excel file, the solution is to replace the data in the datatable with parameters.

Use OLE to create an Excel connection
/// <Summary>
/// Obtain the Excel connection
/// </Summary>
/// <Returns> conn </returns>
Public static oledbconnection getexcelconnection (string path)
{
String connstring = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (connstring );
Return conn;
}
The parameter path is the absolute path of Excel.

Then, write the data in the datatable into Excel using the following method:
/// <Summary>
/// Write data to excel
/// </Summary>
/// <Param name = "FILENAME"> Excel path </param>
/// <Param name = "table"> datatable with data </param>
Public void writedatatoexcel (string filename, datatable table)
{

Oledbconnection excelconn = connectionmanager. getexcelconnection (filename );
Try
{
Excelconn. open ();
Oledbcommand cmd = new oledbcommand ();
Cmd. Connection = excelconn;
For (INT I = 0; I <Table. Rows. Count; I ++)
{
Oledbparameter [] Param = new oledbparameter [Table. Columns. Count];
String SQL = "insert into [sheet1 $] values (";
For (Int J = 0; j <Table. Columns. Count; j ++)
{
String para = "@ Param" + J;
If (j = table. Columns. Count-1)
{
SQL = SQL + para;
}
Else
{
SQL = SQL + para + ",";
}
String value = table. Rows [I] [J]. tostring ();
Param [J] = new oledbparameter (para, value );
Cmd. Parameters. Add (Param [J]);
}
SQL = SQL + ")";
Cmd. commandtext = SQL;
Cmd. executenonquery ();
Cmd. Parameters. Clear ();
}
}
Catch
{
Throw;
}
Finally
{
Connectionmanager. Close (excelconn );
}
}

C # this is basically the case for Excel operations. please correct me!