Three classic methods for ASP. NET to read Excel files

I recently studied how ASP. NET can efficiently read Excel files. The following is a summary:

 

1. Method 1: Use oledb to read Excel files:
Use an Excel file as a data source to read data. The example is as follows:
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 ();

In addition Write an Excel file , For example:
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 ();
}

2. Method 2:Referenced COM component: Microsoft. Office. InterOP. Excel. dll reads an Excel file
First, copy the excel.exe file under the offline installation directory to the bin directory of DOTNET, CMD to the directory, run tlbimp excel. EXE excel. DLL to get the DLL file. Click here to download this file:/files/songliang/dllfile .rar
Add reference to the DLL file in the project.

// Read Excel (read data in the range area)
Private void openexcel (string strfilename)
{
Object missing = system. reflection. Missing. value;
Application Excel = new application (); // lauch Excel application
If (Excel = NULL)
{
Response. Write ("<SCRIPT> alert ('can't access Excel ') </SCRIPT> ");
}
Else
{
Excel. Visible = false; excel. usercontrol = true;
// Open an Excel file in read-only mode
Workbook WB = excel. application. workbooks. Open (strfilename, missing, true, missing,
Missing, missing, missing, true, missing, and missing );
// Obtain the first workbook
Worksheet Ws = (worksheet) WB. worksheets. get_item (1 );

// Obtain the total number of records (including the title column)
Int rowsint = ws. usedrange. cells. Rows. Count; // obtain the number of rows.
// Int columnsint = mysheet. usedrange. cells. Columns. Count; // obtain the number of Columns

// Obtain the data range area (excluding the title column)
Range rng1 = ws. cells. get_range ("B2", "B" + rowsint); // item

Range rng2 = ws. cells. get_range ("K2", "K" + rowsint); // customer
Object [,] arryitem = (object [,]) rng1.value2; // get range's Value
Object [,] arrycus = (object [,]) rng2.value2;
// Assign the new value to an array
String [,] arry = new string [rowsint-1, 2];
For (INT I = 1; I <= rowsint-1; I ++)
{
// Item_code Column
Arry [I-1, 0] = arryitem [I, 1]. tostring ();
// Customer_name Column
Arry [I-1, 1] = arrycus [I, 1]. tostring ();
}
Response. write (arry [0, 0] + "/" + arry [0, 1] + "#" + arry [rowsint-2, 0] + "/" + arry [rowsint-2, 1]);
}
Excel. Quit (); Excel = NULL;
Process [] procs = process. getprocessesbyname ("Excel ");

Foreach (process pro in procs)
{
Pro. Kill (); // There is no better way, only to kill the process
}
GC. Collect ();
}

3. Method 3:Convert an Excel file into a CSV file (separated by commas) and read it using a file stream (equivalent to reading a TXT text file ).
First reference the namespace: using system. text; and using system. IO;
Filestream FS = new filestream ("D: \ customer.csv", filemode. Open, fileaccess. Read, fileshare. None );
Streamreader sr = new streamreader (FS, system. Text. encoding. getencoding (936 ));

String STR = "";
String S = console. Readline ();
While (STR! = NULL)
{STR = Sr. Readline ();
String [] Xu = new string [2];
Xu = Str. Split (',');
String SER = Xu [0];
String DSE = Xu [1]; If (SER = s)
{Console. writeline (DSE); break;
}
} Sr. Close ();

You can also Import database data to a TXT file , For example:

// TXT file name
String fn = datetime. Now. tostring ("yyyymmddhhmmss") + "-" + "po014" + ". txt ";

oledbconnection con = new oledbconnection (constr);
con. open ();
string SQL = "select item, reqd_date, qty, pur_flg, po_num from tsd_po014 ";
// oledbcommand mycom = new oledbcommand ("select * From tsd_po014", mycon);
// oledbdatareader myreader = mycom. executereader (); // you can also use reader to read data.
dataset DS = new dataset ();
oledbdataadapter ODA = new oledbdataadapter (SQL, con );
ODA. fill (DS, "po014");
datatable dt = Ds. tables [0];

Filestream FS = new filestream (server. mappath ("download/" + FN), filemode. Create, fileaccess. readwrite );
Streamwriter strmwriter = new streamwriter (FS); // save it to a text file

// Write the handler to The. txt file.
// For (INT I = 0; I <DT. Columns. Count; I ++)
//{
// Strmwriter. Write (Dt. Columns [I]. columnname + "");
//}

Foreach (datarow DR in DT. Rows)
{
String str0, str1, str2, str3;
String STR = "|"; // data is separated by "|"
Str0 = Dr [0]. tostring ();
Str1 = Dr [1]. tostring ();
Str2 = Dr [2]. tostring ();
Str3 = Dr [3]. tostring ();
Str4 = Dr [4]. tostring (). Trim ();
Strmwriter. Write (str0 );
Strmwriter. Write (STR );
Strmwriter. Write (str1 );
Strmwriter. Write (STR );
Strmwriter. Write (str2 );
Strmwriter. Write (STR );
Strmwriter. Write (str3 );
Strmwriter. writeline (); // line feed
}
Strmwriter. Flush ();
Strmwriter. Close ();
If (con. State = connectionstate. open)
{
Con. Close ();
}