Three Excel operations in ASP. NET

Method 1 use oledb to Operate Excel files (I have also written a full range of Excel files, and compared them ):
① Read the Excel file as a data source. 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 it:

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

Method 2: Use Microsoft. Office. InterOP. Excel. DLL to read the Excel file:

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

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 ):

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