. Net reads EXCEL | CSV operation

1 /// <summary>
2 // how to read Excel files in xls \ xlsx format
3 /// </ummary>
4 /// <param name = "path"> full path of the Excel file to be read </param>
5 /// <returns> </returns>
6 private DataTable ReadExcelToTable (string path)
7 {
8 // connection string
9 string connstring = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ path +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1 ';"; // no extra spaces are allowed for Office 07 and later versions, and note the semicolon
10 // string connstring = Provider = Microsoft. JET. OLEDB.4.0; Data Source = "+ path +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1 ';";
11 // for versions earlier than Office 07, the connection string is not used because I use Office2010. You can select the connection string or program based on your own situation to determine which connection string to use/
12 using (OleDbConnection conn = new OleDbConnection (connstring ))
13 {
14 conn. Open ();
15 DataTable sheetsName = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new object [] {null, "Table"}); // obtain the names of all sheets
16 string firstSheetName = sheetsName. Rows [0] [2]. ToString (); // obtain the name of the first sheet.
17 string SQL = string. Format ("SELECT * FROM [{0}], firstSheetName); // query a string
18 OleDbDataAdapter ada = new OleDbDataAdapter (SQL, connstring );
19 DataSet set = new DataSet ();
20 ada. Fill (set );
21 return set. Tables [0];
22}
23}
24
25
26 /// <summary>
27 // how to read an Excel file in csv format
28 /// </ummary>
29 /// <param name = "path"> full path of the Excel file to be read </param>
30 /// <returns> </returns>
31 private DataTable ReadExcelWithStream (string path)
32 {
33 DataTable dt = new DataTable ();
34 bool isDtHasColumn = false; // mark whether the DataTable has generated a column
35 StreamReader reader = new StreamReader (path, System. Text. Encoding. Default); // data stream
36 while (! Reader. EndOfStream)
37 {
38 string meaage = reader. ReadLine ();
39 string [] splitResult = message. split (new char [] {','}, StringSplitOption. none); // read a row separated by commas and save it to the array DataRow row = dt. newRow ();
40 for (int I = 0; I <splitResult. Length; I ++)
41 {
42 if (! IsDtHasColumn) // if no column is generated
43 {
44 dt. Columns. Add ("column" + I, typeof (string ));
45}
46 row [I] = splitResult [I];
47}
48 dt. Rows. Add (row); // Add a row
49 isDtHasColumn = true; // when the first row is read, the column is no longer generated when the row is marked as an existing column and then read.
50}
51 return dt;
52}