Three classic ways to read Excel files in C #

Source: Internet
Author: User
Tags import database

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:
    1. Public DataSet exceltods (string Path)
    2. {
    3. String strconn = "provider=microsoft.jet.oledb.4.0;" + "Data source=" + Path + ";" + "Extended Properties=excel 8.0;";
    4. OleDbConnection conn = new OleDbConnection (strconn);
    5. Conn. Open ();
    6. String strexcel = "";
    7. OleDbDataAdapter mycommand = null;
    8. DataSet ds = null;
    9. Strexcel= "SELECT * from [sheet1$]";
    10. mycommand = new OleDbDataAdapter (Strexcel, strconn);
    11. ds = new DataSet ();
    12. Mycommand.fill (ds, "Table1");
    13. return DS;
    14. }
Copy Codefor a table in Excel, sheet ([sheet1$]) if it is not fixed, you can use the following method to get
    1. String strconn = "provider=microsoft.jet.oledb.4.0;" + "Data source=" + Path + ";" + "Extended Properties=excel 8.0;";
    2. OleDbConnection conn = new OleDbConnection (strconn);
    3. DataTable schematable = objconn.getoledbschematable (system.data.oledb.oledbschemaguid.tables,null);
    4. String tablename=schematable.rows[0][2]. ToString (). Trim ();
Copy CodeIn addition: You can also write to Excel file, the example is as follows:
  1. public void Dstoexcel (String path,dataset oldds)
  2. {
  3. The primary purpose of getting a dataset that summarizes Excel is to get the structure of excel in the dataset
  4. String Strcon = "Provider = microsoft.jet.oledb.4.0; Data Source = "+path1+"; Extended Properties=excel 8.0 ";
  5. OleDbConnection myconn = new OleDbConnection (Strcon);
  6. String strcom= "select * from [sheet1$]";
  7. MyConn.Open ();
  8. OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);
  9. Ystem. Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder (mycommand);
  10. QuotePrefix and QuoteSuffix are primarily used when building insertcomment commands for builder.
  11. Builder.     Quoteprefix= "["; Get reserved characters in insert statement (start position)
  12. Builder. Quotesuffix= "]"; Get reserved characters in insert statement (end position)
  13. DataSet newds=new DataSet ();
  14. Mycommand.fill (Newds, "Table1");
  15. for (int i=0;i<oldds. Tables[0]. rows.count;i++)
  16. {
  17. 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).
  18. There are values within Newds after using ImportRow, but cannot be updated to Excel because all imported rows are datarowstate!=added
  19. DataRow nrow=adataset.tables["Table1"]. NewRow ();
  20. for (int j=0;j<newds. Tables[0]. columns.count;j++)
  21. {
  22. Nrow[j]=oldds. Tables[0]. ROWS[I][J];
  23. }
  24. Newds. tables["Table1"]. Rows.Add (Nrow);
  25. }
  26. Mycommand.update (Newds, "Table1");
  27. 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.
  1. Method of reading Excel (reading data with range area)
  2. private void Openexcel (String strfilename)
  3. {
  4. Object missing = System.Reflection.Missing.Value;
  5. Application Excel = new application ();//lauch Excel Application
  6. if (Excel = = null)
  7. {
  8. Response.Write ("<script>alert (' Can ' t access Excel ') </script>");
  9. }
  10. Else
  11. {
  12. Excel. Visible = false; Excel. UserControl = true;
  13. Open an Excel file in read-only format
  14. Workbook wb = Excel. Application.Workbooks.Open (strFileName, Missing, true, missing, missing, missing,
  15. Missing, missing, missing, true, missing, missing, missing, missing, missing);
  16. Get the first job book
  17. Worksheet ws = (Worksheet) WB. Worksheets.get_item (1);
  18. Total record rows obtained (including header column)
  19. int rowsint = ws. UsedRange.Cells.Rows.Count; Get the number of rows
  20. int columnsint = mysheet.usedrange.cells.columns.count;//Gets the number of columns
  21. Get data range area (excluding header column)
  22. Range rng1 = ws.   Cells.get_range ("B2", "B" + rowsint); Item
  23. Range rng2 = ws. Cells.get_range ("K2", "K" + rowsint); Customer
  24. object[,] arryitem= (object[,]) rng1.   Value2; Get Range ' s value
  25. object[,] Arrycus = (object[,]) rng2. Value2;
  26. Assigns a new value to an array
  27. string[,] Arry = new string[rowsint-1, 2];
  28. for (int i = 1; I <= rowsint-1; i++)
  29. {
  30. Item_code column
  31. Arry[i-1, 0] =arryitem[i, 1]. ToString ();
  32. Customer_name column
  33. Arry[i-1, 1] = arrycus[i, 1]. ToString ();
  34. }
  35. Response.Write (arry[0, 0] + "/" + arry[0, 1] + "#" + arry[rowsint-2, 0] + "/" + arry[rowsint-2, 1]);
  36. }
  37. Excel. Quit (); Excel = null;
  38. process[] procs = Process.getprocessesbyname ("Excel");
  39. foreach (Process Pro in procs)
  40. {
  41. Pro. Kill ();//There is no better way, only kill the process
  42. }
  43. Gc. Collect ();
  44. }
Copy Code3. Method Three: Convert Excel file into CSV (comma delimited) file, read with file stream (equivalent to read a txt text file)
    1. Reference namespaces first: using System.Text; and using System.IO;
    2. FileStream fs = new FileStream ("D:\\customer.csv", FileMode.Open, FileAccess.Read, Fileshare.none);
    3. StreamReader sr = new StreamReader (FS, System.Text.Encoding.GetEncoding (936));
    4. String str = "";
    5. string s = Console.ReadLine ();
    6. while (str! = NULL)
    7. {str = Sr. ReadLine ();
    8. string[] Xu = new string[2];
    9. Xu = str. Split (', ');
    10. String ser = Xu[0];
    11. String DSE = Xu[1]; if (Ser = = s)
    12. {Console.WriteLine (DSE);
    13. }
    14. } Sr. Close ();
Copy CodeYou can also import database data into a TXT file with the following examples:
  1. TXT file name
  2. STRING fn = DateTime.Now.ToString ("YYYYMMDDHHMMSS") + "-" + "PO014" + ". txt";
  3. OleDbConnection con = new OleDbConnection (CONSTR);
  4. Con. Open ();
  5. String sql = "Select Item,reqd_date,qty,pur_flg,po_num from tsd_po014";
  6. OleDbCommand mycom = new OleDbCommand ("SELECT * from tsd_po014", mycon);
  7. OleDbDataReader myreader = mycom. ExecuteReader (); You can also read data with reader
  8. DataSet ds = new DataSet ();
  9. OleDbDataAdapter ODA = new OleDbDataAdapter (sql, con);
  10. Oda. Fill (ds, "PO014");
  11. DataTable dt = ds. Tables[0];
  12. FileStream fs = new FileStream (Server.MapPath ("download/" + fn), FileMode.Create, FileAccess.ReadWrite);
  13. StreamWriter strmwriter = new StreamWriter (FS); Deposit into a text file
  14. Write a title to a. txt file
  15. for (int i = 0; I <dt. columns.count;i++)
  16. //{
  17. Strmwriter.write (dt. Columns[i]. ColumnName + "");
  18. //}
  19. foreach (DataRow dr in Dt. Rows)
  20. {
  21. String str0, str1, str2, STR3;
  22. String str = "|"; Data with "|" Separate
  23. STR0 = dr[0]. ToString ();
  24. STR1 = dr[1]. ToString ();
  25. STR2 = dr[2]. ToString ();
  26. STR3 = dr[3]. ToString ();
  27. STR4 = Dr[4]. ToString (). Trim ();
  28. Strmwriter.write (STR0);
  29. Strmwriter.write (str);
  30. Strmwriter.write (STR1);
  31. Strmwriter.write (str);
  32. Strmwriter.write (STR2);
  33. Strmwriter.write (str);
  34. Strmwriter.write (STR3);
  35. Strmwriter.writeline (); Line break
  36. }
  37. Strmwriter.flush ();
  38. Strmwriter.close ();
  39. if (Con. state = = ConnectionState.Open)
  40. {
  41. Con. Close ();
  42. }
Copy Code

Three classic ways to read Excel files in C #

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.