[Posting] The best solution for reading and writing data in Excel

The best way to read and write data in Excel (I personally think)
In the past, when I used Excel files, I generally thought that I used COM components to perform Excel document operations.ProgramThat 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 = Microsoft 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 = Microsoft 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, the dataset of Excel is summarized to obtain the structure of Excel in dataset.
  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 = New Oledbcommandbuilder (mycommand );
  // Quoteprefix and quotesuffix are mainly used to generate insertcomment commands for builder.
Builder. quoteprefix = " [ " ; // Obtain the reserved characters in the insert Statement (starting position)
Builder. quotesuffix = " ] " ; // Obtain the reserved characters (ending position) in the insert statement)
Dataset newds = New Dataset ();
Mycommand. Fill (newds, " Table1 " );
  For ( Int I = 0 ; I {
// The importrow method cannot be used to import a row to news, because importrow retains all the settings of the original datarow (the 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 {
Nrow [J]=Oldds. Tables [0]. Rows [I] [J];
}
Newds. Tables [ " Table1 " ]. Rows. Add (nrow );
}
Mycommand. Update (newds, " Table1 " );
Myconn. Close ();
}