Three classic ways to read Excel files in C #

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

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

In addition: You can also write to Excel file, the example is as follows:

public void Dstoexcel (string Path,dataset oldds) {//To get the dataset summarized in Excel first is to get the structure of Excel in the dataset string Strcon = "Provide R = 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= "["; Gets the reserved character (starting position) builder in the INSERT statement. Quotesuffix= "]"; Gets the reserved character (end position) of the INSERT statement in the DataSet Newds=new DataSet (); Mycommand.fill (Newds, "Table1"); for (int i=0;i<oldds. Tables[0].   rows.count;i++) {//Here you cannot use the ImportRow method to import a row into news because ImportRow retains all of the settings for the original DataRow (the DataRowState state remains the same). 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 ();  }

2. 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.reflectio        N.missing.value; Application Excel = new application ();//lauch Excel application if (Excel = = null) {Response.wri        Te ("<script>alert (' Can ' t access Excel ') </script>"); } else {Excel. Visible = false; Excel.            UserControl = true; Open the Excel file in read-only form Workbook WB = Excel. Application.Workbooks.Open (strFileName, Missing, true, missing, missing, missing, missing, missing, missing, T            Rue, missing, missing, missing, missing, missing); Get the first workbook Worksheet ws = (Worksheet) WB.            Worksheets.get_item (1); Gets the total number of record rows (including the title column) int rowsint = ws. UsedRange.Cells.Rows.Count;             Gets 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 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 kill process} GC.    Collect (); }

3. Method Three: Convert the Excel file into a CSV (comma delimited) file, read with a file stream (equivalent to reading 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); break;                }           }   Sr. Close ();

You can also import database data into a TXT file with the following examples:

           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 in 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 the title to a. txt file in//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 open 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 (); }

C # Excel related code

• Asp. NET implementing Excel data export with Myxls
• C # Methods for setting Excel cells
• Java Read Excel file
• C # Export data to an Excel file
• C # Export data to an Excel file
• C # reads the Excel file and saves it as a text file
• C # Create, read, and modify Excel
• PHP Export Excel Class
• POI Operations Excel Common operations
• PHP uses Phpexcel to export table data

Related documents

• DataGridView or DataTable Export to Excel support progress bar. doc
• Algorithm refinement: C language Description (Chinese version). pdf
• OBJECTIVE-C 2.0 Program Design (original book 2nd edition). pdf
• "Algorithm fine Solution: C Language description" sample. pdf
• Objective-c Basics Quick Start. pdf
• Objective-c Basics Quick Start. pdf
• C # Read Excel writes to the database. doc
• C # implements Excel data import efficiently into SQL database. doc
• C language and C + + in the compilation preprocessing of the learning record. doc
• C # Operation Excel.doc

Related experience

• Comprehensive understanding of POI operations in Microsoft Office (Word, Excel, PowerPoint)
• Import and export based on npoi+excelreport implementation of Excel class library: excelutility
• My MySQL learning experience (14): Backup and Recovery
• Python handles Excel
• Implement file Download in Struts 2 (fix Chinese issue)
• These. NET open source project you know what? (third edit). NET platform open source document and report processing component
• Develop a WYSIWYG IDE development environment using. NET WinForm, enabling you to build your application without writing code directly
• Summary of common methods of POI operations Excel
• Scott Hanselman's 2014 software recommendations
• JXL exporting Excel

Contact Us | Java Open Source Encyclopedia | Open Document | Open Home | Open News | Open Experience Library

Three classic ways to read Excel files in C #