asp. net examples of three ways to read excel files

Method 1: OleDB is used to read Excel files

The Excel file is used as a data source to read data. Examples are 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;   
} 

The table in Excel, sheet([sheet1$]), can be obtained using the following method if it is not fixed

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, Excel file can also be written. Examples are as follows:

public void DSToExcel(string Path,DataSet oldds)   
{   
// You get the summary first Excel the DataSet  The main purpose is to obtain Excel in DataSet In the structure    
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 ) ;   
system.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand);   
//QuotePrefix and QuoteSuffix Is mainly to builder generate InsertComment Command.    
builder.QuotePrefix="["; // To obtain insert Reserve characters (starting position) in a statement    
builder.QuoteSuffix="]"; // To obtain insert Reserve character (end position) in statement    
DataSet newds=new DataSet();   
myCommand.Fill(newds ,"Table1") ;   
for(int i=0;i<oldds.Tables[0].Rows.Count;i++)   
{   
// Can't be used here ImportRow Method will be 1 Line imported into the news ,    
// because ImportRow I'm going to keep the original DataRow All Settings of (DataRowState The state remains the same ) .    
// In the use of ImportRow after newds Contains a value, but cannot be updated to Excel Because all imported rows DataRowState!=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: reference com components: Microsoft Office. Interop. Excel. dll read Excel file

The first is the acquisition of ES33en.dll. The Office installation directory of Excel.exe file Copy to bin directory of DotNet,cmd to the directory, run TlbImp EXCEL.EXE Excel.dll to get Dll file.

Add a reference to the dll file to your project

// read EXCEL The method of     ( Read data with a 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 as read-only EXCEL file    
Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,   
missing, missing, missing, true, missing, missing, missing, missing, missing);   
// In the first 1 A work sheet 
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);
// Gets the total number of record rows ( Include header column )
int rowsint = ws.UsedRange.Cells.Rows.Count; // Get the number of rows 
//int columnsint = mySheet.UsedRange.Cells.Columns.Count;// Get the number of columns 
// Get the data range area ( Not including header columns )
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 1 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 kill the process 
}
GC.Collect();
}

Method 3: Convert Excel file to CSV (comma-separated) file and read with file stream (equivalent to reading 1 txt text file).

First refer to 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();

In addition, the database data can also be imported into an txt file. Examples are as follows:

//txt The 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 Read the 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);    // Put it in a text file    
// Put the title in there .txt In the 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 = "|";  // The data used "|" separated    
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();// A newline 
}
strmWriter.Flush();
strmWriter.Close();
if(con.State==ConnectionState.Open)
{
con.Close();
}