Differences between. NET methods for reading Excel files

ASP. NET Method 1: Use OleDB to read Excel files:

Use an Excel file as a data source to read data. The example is as follows:

Copy codeThe Code 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

Copy codeThe Code is as follows:
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, you can also write an Excel file, for example:

Copy codeThe Code is as follows:
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 ();
}

ASP. NET Method 2: Reference com component: Microsoft. Office. Interop. Excel. dll

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.

Add reference to the dll file in the project.

Copy codeThe Code is as follows:
// 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, true, missing, and missing); // obtain the first working thin
Worksheet ws = (Worksheet) wb. Worksheets. get_Item (1); // obtain the total number of record rows (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 a 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 (); // mer_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 ();
}

ASP. NET 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 ).

Reference namespace first:

Copy codeThe Code is as follows:
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 the database data to a txt file. The example is as follows:

Copy codeThe Code is as follows:
// 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 ();
}

The ASP. NET Method for reading Excel files is introduced here. I hope to help you understand ASP. NET in reading Excel files.