Excel reads the best solution for writing data [转]

xiaoxiao2021-04-04  257

Friendly is going to use, put it here, then use it again :)

When using the Excel file in the past, I generally believe that the use of the COM component for the operation of the Excel document, so that the write program is time-consuming, it is laborious (because the Excel component does not help prompts), the written program is still a large repetition code. Why is this? Because we always operate an Excel file as a system file.

If the Excel file is used as a data source to perform data read operations, you can use the data access method provided by the development environment to access the Excel file. This allows help (prompts) of the development environment to read and write data to Excel quickly and not labor. Such a code is neat and efficient, and the code can also be reused.

The following example is to use .NET to read and write to Excel files.

Examples of application environments are read from other one or more Excel, and then write multiple data to a summary Excel (aggregated Excel file only with no data). The premise is the same in the Excel file.

Reading the Excel file

Public DataSet Excelt 4 (String Path)

{

String strconn = "provider = microsoft.jet.Oledb.4.0;" "data source =" PATH ";" "extended Properties = Excel 8.0;"

OLEDBCONNECTION CONN = New OLEDBCONNECTION (STRCONN);

Cn.open ();

String strexcel = ""

OLEDBDataAdapter myCommand = NULL;

DataSet DS = NULL;

strexcel = "SELECT *" ";

MyCommand = New OLEDBDataAdapter (strexcel, strconn);

DS = new dataset ();

MyCommand.Fill (DS, "Table1");

Return DS;

}

For tables in Excel, you are Sheet ([Sheet1 $]) if not fixed, you can use the following method

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 get the Summary Excel's DataSet main purpose is to get the structure of Excel in the 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 *" ";

Myconn.open ();

OLEDBDataAdapter mycommand = new oledbdataadapter (strcom, myconn);

YSTEM.DATA.OLDB.OLDBCOMMAVANDBUILDER Builder = New OLEDBCommandbuilder (MyCommand);

// QuotePrefix and Quotesuffi are mainly used when generating the insertcomment command for Builder.

Builder.quoteprefix = "["; // Get the insert statement to keep characters (start positions)

Builder.quotesuffix = "]"; // Get the reserved character in the INSERT statement (end location)

Dataset newds = new dataset ();

MyCommand.Fill (NewDS, "Table1");

For (int i = 0; i

{

/ / You cannot use the importrow method to import a row into the news because IMPORTROW will retain all settings for DataRow (the DataRowState status is unchanged). There is a value in NewDS after using Importrow, but you can't update to Excel because of DataRowState! = Added in all passed lines.

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 ();

}

The above is just an example can only provide you with another idea of ​​operating Excel, specific cases develop !!!!!!!

转载请注明原文地址:https://www.9cbs.com/read-131899.html

New Post(0)