最近在项目中,遇到一些需求,就是将数据导出来,以Excel文件为主:就自己简单的做一些demo:供初学者来学习:
1 // 定义一个保存文件的路径位置
2 SaveFileDialog dlgPath = new SaveFileDialog();
3 string sPath = string.Empty;
4 // 需要保存的格式
5 dlgPath.Filter = "Excel文件(*.xls)|*.xls";
6
7 // 对Excel相应的操作定义变量
8 ApplicationClass app = new ApplicationClass();
9 Microsoft.Office.Interop.Excel._Workbook _workBook = null;
10
11 try
12 {
13 // 弹出要保存文件的位置(可选择也可以默认路径)
14 if (dlgPath.ShowDialog() == System.Windows.Forms.DialogResult.OK)
15 {
16 sPath = dlgPath.FileName;
17 }
18 else
19 {
20 return;
21 }
22
23
24 Workbooks workBooks = app.Workbooks;
25 _workBook = workBooks.Add(true);
26
27 Sheets sheets = _workBook.Sheets;
28
29 Worksheet sheet = (Worksheet)sheets[1];
30
31 //判断表头是为空
32 if (!string.IsNullOrEmpty(dt.TableName))
33 {
34 sheet.Name = dt.TableName;
35 }
36
37 char sExcelColn = 'A';
38 char sExcelColnEx = 'A';
39 for (int i = 0; i < dt.Columns.Count; i++)
40 {
41 // 设置Excel某列单元格的文本格式,从第二行开始
42 for (int index = 2; index < dt.Rows.Count+2; index++)
43 {
44 if (i > 25)
45 {
46 Range objRange = sheet.get_Range("A" + sExcelColnEx + index.ToString(), Type.Missing);
47 if (null!=objRange)
48 {
49 objRange.NumberFormatLocal = "@";
50 }
51 else {
52 break;
53 }
54 }
55 else
56 {
57 Range objRange = sheet.get_Range("A" + sExcelColn + index.ToString(), Type.Missing);
58 if (null != objRange)
59 {
60 objRange.NumberFormatLocal = "@";
61 }
62 }
63 }
64 if (i<=25)
65 {
66 sExcelColn++;
67 }else
68 {
69 sExcelColnEx++;
70 }
71 }
72 int x = 1, y = 1;
73 foreach (DataColumn cloumn in dt.Columns)
74 {
75 // 如果Caption不为空表头输出使用Caption,否则使用ColumnName
76 if (string.IsNullOrEmpty(cloumn.Caption.ToString()))
77 {
78 sheet.Cells[x, y++] = cloumn.ColumnName.ToString();
79 }
80 else
81 {
82
83 sheet.Cells[x, y++] = cloumn.Caption.ToString();
84 }
85 }
86 x++;
87 y = 1;
88
89 foreach (DataRow row in dt.Rows)
90 {
91 foreach (DataColumn column in dt.Columns)
92 {
93 sheet.Cells[x,y++] = row[column.ColumnName].ToString();
94 }
95 x++;
96 y = 1;
97 }
98 _workBook.SaveAs(sPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
99 XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
100
101 }
102 catch (Exception ex)
103 {
104
105 throw ex;
106 }
107 finally
108 {
109 if (_workBook!=null)
110 {
111 _workBook.Close(Type.Missing, Type.Missing, Type.Missing);
112 }
113 if (app!=null)
114 {
115 app.Quit();
116 }
117 }
上面的代码是将数据转换成Excel的过程:
我们在做一组假数据,看看效果:
1 // 表格数据
2 System.Data.DataTable dt = new System.Data.DataTable();
3 dt.Columns.Add("name");
4 dt.Columns.Add("age");
5 dt.Columns.Add("sex");
6 dt.Columns.Add("weight");
7 dt.Columns.Add("height");
8 dt.Columns.Add("adress");
9 dt.Columns.Add("phone");
10 dt.Columns.Add("workAdress");
11
12 DataRow dr = dt.NewRow();
13 dr["name"] = "万三窦";
14 dr["age"] = "28";
15 dr["sex"] = "男";
16 dr["weight"] = "68kg";
17 dr["height"] = "167cm";
18 dr["adress"] = "丰台区大井";
19 dr["phone"] = "13866669999";
20 dr["workAdress"] = "金堂国际大厦配楼301";
21 dt.Rows.Add(dr);
我上面是用WindowForm做的demo:只需在界面加个按钮调用下,就可以,我们来看下效果
若有不正确的地方,请大家多多指教!