Analysis on three methods of ASP. NET reading Excel files

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.