C # reading excel,

C # reading excel,

The Provider is set based on the actual EXCEL version. It is recommended to use the ACE interface for reading. Access database Engine is required.

Note that the values of the following two items in the Registry are 0. Otherwise, when the number of characters in the cell exceeds 255, the data is truncated !!!

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Office \ 12.0 \ AccessConnectivity Engine \ Engines \ Excel \ TypeGuessRows

64-bit System

HKEY_LOCAL_MACHINE \ SOFTWARE \ Wow6432Node \ Microsoft \ Office \ 12.0 \ Access Connectivity Engine \ Engines \ Excel \ TypeGuessRows

 

I have been asking questions about reading and importing excel files on the forum. In my spare time, I will summarize what I know about excel operations and share them with you. I hope to help you.
In addition, we also need to pay attention to some simple problems 1. the excel file can only store 65535 rows of data. If your data is larger than 65535 rows, you need to separate the excel file. 2. garbled characters.

1. Loading Excel (reading excel content) the returned value is a DataSet

[Csharp]View plaincopy

1. // Load the Excel file
2. Public static DataSet LoadDataFromExcel (string filePath)
3. {
4. Try
5. {
6. String strConn;
7. StrConn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + filePath + "; Extended Properties = 'excel 8.0; HDR = False; IMEX = 1 '";
8. OleDbConnection OleConn = new OleDbConnection (strConn );
9. OleConn. Open ();
10. String SQL = "SELECT * FROM [Sheet1 $]"; // However, you can change the Sheet name, such as sheet2.
12. OleDbDataAdapter OleDaExcel = new OleDbDataAdapter (SQL, OleConn );
13. DataSet OleDsExcle = new DataSet ();
14. OleDaExcel. Fill (OleDsExcle, "Sheet1 ");
15. OleConn. Close ();
16. Return OleDsExcle;
17. }
18. Catch (Exception err)
19. {
20. MessageBox. Show ("An error occurred while binding data to Excel! Cause of failure: "+ err. Message," prompt Message ",
21. MessageBoxButtons. OK, MessageBoxIcon. Information );
22. Return null;
23. }
24. }

2. Write the Excel content. Parameter: excelTable is a table to be imported into excel.

[Csharp]View plaincopy

1. Public static bool SaveDataTableToExcel (System. Data. DataTable excelTable, string filePath)
2. {
3. Microsoft. Office. Interop. Excel. Application app =
4. New Microsoft. Office. Interop. Excel. ApplicationClass ();
5. Try
6. {
7. App. Visible = false;
8. Workbook wBook = app. Workbooks. Add (true );
9. Worksheet wSheet = wBook. Worksheets [1] as Worksheet;
10. If (excelTable. Rows. Count> 0)
11. {
12. Int row = 0;
13. Row = excelTable. Rows. Count;
14. Int col = excelTable. Columns. Count;
15. For (int I = 0; I <row; I ++)
16. {
17. For (int j = 0; j <col; j ++)
18. {
19. String str = excelTable. Rows [I] [j]. ToString ();
20. WSheet. Cells [I + 2, j + 1] = str;
21. }
22. }
23. }
25. Int size = excelTable. Columns. Count;
26. For (int I = 0; I <size; I ++)
27. {
28. WSheet. Cells [1, 1 + I] = excelTable. Columns [I]. ColumnName;
29. }
30. // Set to prohibit the pop-up prompt box for saving and overwriting
31. App. DisplayAlerts = false;
32. App. AlertBeforeOverwriting = false;
33. // Save the workbook
34. WBook. Save ();
35. // Save the excel file
36. App. Save (filePath );
37. App. SaveWorkspace (filePath );
38. App. Quit ();
39. App = null;
40. Return true;
41. }
42. Catch (Exception err)
43. {
44. MessageBox. Show ("An error occurred while exporting Excel! Error cause: "+ err. Message," prompt Message ",
45. MessageBoxButtons. OK, MessageBoxIcon. Information );
46. Return false;
47. }
48. Finally
49. {
50. }
51. }

Reprinted friends must indicate the source. Thank you! Http://blog.csdn.net/gisfarmer/