Asp. NET reading Excel file method: Using OLE DB to read Excel files:
To read an Excel file as a data source for data, examples are as follows:
Copy Code code 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;
}
For tables in Excel, that is, sheet ([sheet1$]) if not fixed, you can use the following methods to get
Copy Code code 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 Excel files, as shown in the following example:
Copy Code code as follows:
public void Dstoexcel (string Path,dataset oldds) {
The main purpose of the dataset to be summarized in Excel is to obtain 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 * from [sheet1$]";
MyConn.Open ();
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);
Ystem. Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder (mycommand);
QuotePrefix and QuoteSuffix are primarily used when generating insertcomment commands for builder.
Builder. Quoteprefix= "["; Get reserved characters in insert statement (starting position)
Builder. Quotesuffix= "]"; Get reserved characters in insert statement (end position)
DataSet newds=new DataSet ();
Mycommand.fill (Newds, "Table1");
for (int i=0;i<oldds. Tables[0]. rows.count;i++)
{
You cannot use the ImportRow method to import a row into news.
Because ImportRow retains all the settings of the original DataRow (DataRowState State is unchanged).
There is a value within Newds after using ImportRow, but cannot be updated to Excel because the datarowstate!=added of all imported rows
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 reading Excel file method Two: Referenced COM component: Microsoft.Office.Interop.Excel.dll reading Excel file
The first is Excel.dll, the Office installation directory under the Excel.exe file copy to the dotnet Bin directory, cmd to the directory, run TLBIMP EXCEL. EXE Excel.dll Get DLL file.
Add a reference to the DLL file in your project.
Copy Code code as follows:
Ways to read Excel (read data with 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 as read-only
Workbook WB = Excel. Application.Workbooks.Open (strFileName, Missing, true, missing, missing, missing,missing, missing, missing, true, Missing, missing, missing, missing, missing); Get the first work thin
Worksheet ws = (worksheet) WB. Worksheets.get_item (1); Get total number of rows (including header columns)
int rowsint = ws. UsedRange.Cells.Rows.Count; Get the number of rows
int columnsint = mysheet.usedrange.cells.columns.count;//Gets the number of columns
Get data range area (excluding 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 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 the process of killing
}
Gc. Collect ();
}
Asp. NET read Excel file Method Three: Convert Excel file to CSV (comma-delimited) file, read by file stream (equivalent to read a txt text file).
Reference Namespaces First:
Copy Code code 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);
}
}
Sr. Close ();
In addition, you can import the database data into a TXT file, as shown in the following example:
Copy Code code as follows:
TXT filename
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 read data with reader
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); into a text file
Write a title to a. 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 with "|" 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 (); Line Wrap
}
Strmwriter.flush ();
Strmwriter.close ();
if (Con. state = = ConnectionState.Open)
{
Con. Close ();
}
Asp. NET reading Excel file is introduced to you here, I hope you understand ASP.net read Excel file is helpful.
