1 Workbook wb = null;
2 Worksheet ws = null;
3 bool isEqual = false;//不相等
4 var columnArr = new ArrayList();//列字段表
5 var myDs = new DataSet();
6 var xlsTable = new System.Data.DataTable("show");
7 object missing = System.Reflection.Missing.Value;
8 var excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application
9 if (excel != null)
10 {
11 excel.Visible = false;
12 excel.UserControl = true;
13 // 以只读的形式打开EXCEL文件
14 wb = excel.Workbooks.Open(filePath + name, missing, true, missing, missing, missing,
15 missing, missing, missing, true, missing, missing, missing, missing, missing);
16 //取得第一个工作薄
17 ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
18 //取得总记录行数(包括标题列)
19 int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
20 int columnsint = ws.UsedRange.Cells.Columns.Count;//得到列数
21 DataRow dr;
22 for (int i = 1; i <= columnsint; i++)
23 {
24 //判断是否有列相同
25 if (i >= 2)
26 {
27 int r = 0;
28 for (int k = 1; k <= i - 1; k++)//列从第一列到第i-1列遍历进行比较
29 {
30 if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString() == ((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, k]).Text.ToString())
31 {
32 //如果该列的值等于前面列中某一列的值
33 xlsTable.Columns.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString(), typeof(string));
34 columnArr.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString());
35 isEqual = true;
36 r++;
37 break;
38 }
39 else
40 {
41 isEqual = false;
42 continue;
43 }
44 }
45 if (!isEqual)
46 {
47 xlsTable.Columns.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString(), typeof(string));
48 columnArr.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString());
49 }
50 }
51 else
52 {
53 xlsTable.Columns.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString(), typeof(string));
54 columnArr.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, i]).Text.ToString());
55 }
56 }
57 for (int i = 2; i <= rowsint; i++)
58 {
59 dr = xlsTable.NewRow();
60 for (int j = 1; j <= columnsint; j++)
61 {
62 dr[columnArr[j - 1].ToString()] = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[i, j]).Text.ToString();
63 }
64 xlsTable.Rows.Add(dr);
65 }
66 }
67 excel.Quit();
68 excel = null;
69 myDs.Tables.Add(xlsTable);
70 return myDs;
