Youzi needs to use it. Put it here first, and then use it again :)
In the past, when I used Excel files, I generally thought that I used COM components to perform Excel document operations. Program That is, it is time-consuming and labor-consuming (because the Excel component does not help prompt), and the program to be written is still a large repetition. Code . Why? Because we always operate an Excel file as a system file.
If you use an Excel file as a data source to read data, you can use the data access method provided by the development environment to access the Excel file. In this way, you can use the help (prompt) of the development environment to quickly and effortlessly read and write data to excel. Such code is neat and efficient, and the code can be reused.
The following example shows how to use. Net to read and write Excel files.
The application environment of the example reads data from one or more other Excel files, and then writes multiple data to one summary Excel file (the summary Excel file contains only the header and no data ). The premise is that the structure in the Excel file is the same.
Reading Excel files
Public dataset exceltods (string path)
{
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Conn. open ();
String strexcel = "";
Oledbdataadapter mycommand = NULL;
Dataset DS = NULL;
Strexcel = "select * from [sheet1 $]";
Mycommand = new oledbdataadapter (strexcel, strconn );
DS = new dataset ();
Mycommand. Fill (DS, "Table1 ");
Return Ds;
}
If the table in Excel is sheet ([sheet1 $]), you can use the following method to obtain
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + path + ";" + "extended properties = Excel 8.0 ;";
Oledbconnection conn = new oledbconnection (strconn );
Datatable schematable = objconn. getoledbschematable (system. Data. oledb. oledbschemaguid. Tables, null );
String tablename = schematable. Rows [0] [2]. tostring (). Trim ();
Write an Excel file
Public void dstoexcel (string path, dataset oldds)
{
// First obtain the dataset of the summary Excel file. The main purpose is to obtain the structure of the Excel file in the dataset file.
String strcon = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + path1 + "; extended properties = Excel 8.0 ";
Oledbconnection myconn = new oledbconnection (strcon );
String strcom = "select * from [sheet1 $]";
Myconn. open ();
Oledbdataadapter mycommand = new oledbdataadapter (strcom, myconn );
Ystem. Data. oledb. oledbcommandbuilder builder = new oledbcommandbuilder (mycommand );
// Quoteprefix and quotesuffix are mainly used to generate insertcomment commands for builder.
Builder. quoteprefix = "["; // obtain the reserved characters (starting position) in the insert statement)
Builder. quotesuffix = "]"; // obtain the reserved characters (end position) in the insert statement)
Dataset newds = new dataset ();
Mycommand. Fill (newds, "Table1 ");
For (INT I = 0; I <oldds. Tables [0]. Rows. Count; I ++)
{
// The importrow method cannot be used to import a row to news, because importrow retains all the settings of the original datarow (the status of datarowstate remains unchanged ). There is a value in newds after importrow is used, but it cannot be updated to excel because datarowstate of all import rows! = Added
Datarow nrow = adataset. Tables ["Table1"]. newrow ();
For (Int J = 0; j <newds. Tables [0]. Columns. Count; j ++)
{
Nrow [J] = oldds. Tables [0]. Rows [I] [J];
}
Newds. Tables ["Table1"]. Rows. Add (nrow );
}
Mycommand. Update (newds, "Table1 ");
Myconn. Close ();
}
The above is just an example. We can only provide you with one idea about how to operate Excel. The specific case is developed !!!!!!!