. NET import data from an Excel file

. NET COM Components

This method is also available when the Office suite is not installed on the computer. So do not rely on software,

However, it is still necessary to xcel.exe the compiled DLL file into the appropriate program to reference. This will DLL file "

Carry it with you. " It's pretty good!

1. Register Microsoft.Office.Interop.Excel.dll

Under the Office installation folder, locate Excel.exe, path D:\Program Files (x86) \microsoft

Office\office15 . will be Excel.exe files copied to D:\ProgramFiles (x86) \microsoft Visual

under Studio 11.0\VC. switch to D:\Program Files with the Visual Studio Command line tool

(x86) \microsoft Visual STUDIO11.0\VC, typically switches automatically. The execution of tlbimp/

Out:interop. Excel.dll Excel.exe. Tips

2.ReferencesInterop.Excel.dll

will compile a good DLL the file is copied to the program's bin file. Add Reference

Here is a small demo of my own .

<pre name= "code" class= "CSharp" >private void Openexcel (String strfilename) {Object missing = System.refle Ction.        Missing.Value; Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application ();//lauch Excel Application if (Excel = = null) {} 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 (); }

Results

OLE DB mode

This approach is like using SQL Server as usual to treat Excel files as a data source. Just

the database at this time is Excel actually, it's just as simple as that. SQL Server which is complicated. Excel so this

The way is relative or

More common.

Code

<pre name= "code" class= "CSharp" >///<summary>///Read Excel data to DS///</summary>/// Lt;param name= "excelname" >xls file path (server physical path) string RootDir =server.mappath ( System.Web.HttpContext.Current.Request.ApplicationPath.ToString ());//Get the program root directory </param>///<returns>  </returns> Public DataSet Excelreader (string excelname) {//spelling connection string, opening connection strconn = "provider=microsoft.ace.oledb.12.0;" + "Data source=" + Excelname + "; Extended properties= ' Excel 8.0; Hdr=yes;            Imex=1 ' ";            OleDbConnection objconn = new OleDbConnection (strconn);            objConn.Open ();            Get all worksheets in the Excel workbook DataTable schematable = objconn.getoledbschematable (OleDbSchemaGuid.Tables, NULL);            OleDbDataAdapter Sqlada = new OleDbDataAdapter ();            DataSet ds = new DataSet ();              Traverse the worksheet to get the data and deposit the dataset foreach (DataRow Dr in schemaTable.Rows) {  String strSQL = "SELECT * FROM [" + dr[2]. ToString ().                Trim () + "]";                OleDbCommand objcmd = new OleDbCommand (strSQL, objconn); Sqlada.                SelectCommand = objcmd; Sqlada. Fill (ds, dr[2]. ToString ().            Trim ());            } objconn.close ();        return DS; }

Several key code sentences:

C # Garbage collection:

   Get all the Excel processes     process[] procs = Process.getprocessesbyname ("Excel");     foreach (Process Pro in procs)     {         Pro. Kill ();//      }     GC. Collect ();

COM components create Excel action objects

  Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application (); Open the Excel file in read-only form                Workbook wb = Excel. Application.Workbooks.Open (strFileName, Missing, true, missing, missing, missing, missing, missing, missing, true, Missing, missing, missing, missing, missing);//Get the first workbook Worksheet ws = (Worksheet) WB. Worksheets.get_item (1);//Gets the value of the cell string cellstr = ws. CELLS[I][J]. Value;

OLE DB establishes Excel connection

To spell the connection string, open connect string            strconn = "provider=microsoft.ace.oledb.12.0;" + "Data source=" + Excelname + "; Extended properties= ' Excel 8.0; Hdr=yes; Imex=1 ' ";            OleDbConnection objconn = new OleDbConnection (strconn); objConn.Open ();//Get all worksheets in Excel workbook datatable schematable = Objconn.getoledbschematable (OleDbSchemaGuid.Tables, NULL);

The first method is to create an Excel object, and the second method is to Excel as the data source. The first kind of application surface is more

Wide. There is also a binary data stream in the way to read, you need to convert the Excel file into a CSV file.