Analysis on three methods of ASP. NET reading Excel files

Source: Internet
Author: User

I recently studied how to make ASP. NET more efficient in reading Excel files. The following is a summary:

ASP. NET Method 1: Use oledb to read Excel files:

Use an Excel file as a data source to read data. 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. }

If the table in Excel is sheet ([sheet1 $]), you can use the following method to obtain

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

In addition, you can also write an Excel file, for example:

  1. Public void dstoexcel (string path, dataset oldds)
  2. {
  3. // First obtain the dataset of the summary Excel file. The main purpose is to obtain the structure of the Excel file in the dataset file.
  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 mainly used to generate insertcomment commands for builder.
  11. Builder. quoteprefix = "["; // obtain the reserved characters (starting position) in the insert statement)
  12. Builder. quotesuffix = "]"; // obtain the reserved characters (end position) in the insert statement)
  13. Dataset newds = new dataset ();
  14. Mycommand. Fill (newds, "Table1 ");
  15. For (INT I = 0; I <oldds. Tables [0]. Rows. Count; I ++)
  16. {
  17. // The importrow method cannot be used to import a row to news,
  18. // Because importrow retains all the settings of the original datarow (the status of datarowstate remains unchanged ).
  19. // There is a value in newds after importrow is used, but it cannot be updated to excel because datarowstate of all import rows! = Added
  20. Datarow nrow = adataset. Tables ["Table1"]. newrow ();
  21. For (Int J = 0; j <newds. Tables [0]. Columns. Count; j ++)
  22. {
  23. Nrow [J] = oldds. Tables [0]. Rows [I] [J];
  24. }
  25. Newds. Tables ["Table1"]. Rows. Add (nrow );
  26. }
  27. Mycommand. Update (newds, "Table1 ");
  28. Myconn. Close ();
  29. }

ASP. NET Method 2: Reference COM component: Microsoft. Office. InterOP. Excel. dll

First, copy the excel.exe file under the offline installation directory to the bin directory of DOTNET, CMD to the directory, run tlbimp excel. EXE excel. DLL to get the DLL file.

Add reference to the DLL file in the project.

  1. // Read Excel (read data in the 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 mode
  14. Workbook WB = excel. application. workbooks. Open (strfilename, missing, true, missing,
  15. Missing, missing, missing, true, missing, and missing );
  16. // Obtain the first workbook
  17. Worksheet Ws = (worksheet) WB. worksheets. get_item (1 );
  18. // Obtain the total number of records (including the title column)
  19. Int rowsint = ws. usedrange. cells. Rows. Count; // obtain the number of rows.
  20. // Int columnsint = mysheet. usedrange. cells. Columns. Count; // obtain the number of Columns
  21. // Obtain the data range area (excluding the title 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. // Assign the 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 to kill the process
  42. }
  43. GC. Collect ();
  44. }

ASP. NET method 3: convert an Excel file into a CSV file (separated by commas) and read it using a file stream (equivalent to reading a TXT text file ).

Reference namespace first:

  1. 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); break;
  13. }
  14. } Sr. Close ();

You can also import the database data to a TXT file. The example is as follows:

  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 use reader to read data.
  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); // save it to a text file
  14. // Write the handler to The. 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 is separated by "|"
  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 feed
  36. }
  37. Strmwriter. Flush ();
  38. Strmwriter. Close ();
  39. If (con. State = connectionstate. open)
  40. {
  41. Con. Close ();
  42. }

The ASP. Net Method for reading Excel files is introduced here. I hope to help you understand ASP. NET in reading Excel files.

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.