1. Method One: Use OLE DB to read Excel files:
The Excel file as a data source for the data read operation, 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;
- }
Copy Codefor a table in Excel, sheet ([sheet1$]) if it is not fixed, you can use the following method to get
- 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 ();
Copy CodeIn addition: You can also write to Excel file, the example is as follows:
- public void Dstoexcel (String path,dataset oldds)
- {
- The primary purpose of getting a dataset that summarizes Excel is to get 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 building insertcomment commands for builder.
- Builder. Quoteprefix= "["; Get reserved characters in insert statement (start 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 here to import a row into news because ImportRow retains all the settings for the original DataRow (DataRowState State is unchanged).
- There are values within Newds after using ImportRow, but cannot be updated to Excel because all imported rows are 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 ();
Copy Code2. Method Two: Referenced COM component: Microsoft.Office.Interop.Excel.dll read Excel file
first is the Excel.dll, copy the Excel.exe file under the Office installation directory to the Dotnet Bin directory, cmd to the directory, and run TLBIMP EXCEL. EXE Excel.dll to get the DLL file. Then add references to the DLL file in the project.
- Method of reading Excel (reading 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 in read-only format
- Workbook wb = Excel. Application.Workbooks.Open (strFileName, Missing, true, missing, missing, missing,
- Missing, missing, missing, true, missing, missing, missing, missing, missing);
- Get the first job book
- Worksheet ws = (Worksheet) WB. Worksheets.get_item (1);
- Total record rows obtained (including header column)
- 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 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;
- Assigns 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 ();
- 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 ();
- }
Copy Code3. Method Three: Convert Excel file into CSV (comma delimited) file, read with file stream (equivalent to read a txt text file)
- Reference namespaces first: 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 ();
Copy CodeYou can also import database data into a TXT file with the following examples:
- 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 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); Deposit 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 "|" Separate
- 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 break
- }
- Strmwriter.flush ();
- Strmwriter.close ();
- if (Con. state = = ConnectionState.Open)
- {
- Con. Close ();
- }
Copy Code |
