后台数据导出为Excel

数据导出的方法如下：

一、下载office的类库：microsoft.office.interop.excel.zip

根据电脑安装的office版本选择引入相应的类库，office2007选择12.0的版本，office2010选择14的版本

二、设置该类库的嵌入互操作类型属性为false，如图

 

三、调用该方法实现数据的导出

主要代码如下：

 1 public static bool ExportExcel(System.Data.DataTable dt, string path)
 2 {
 3 bool succeed = false;
 4 if (dt != null)
 5 {
 6 Microsoft.Office.Interop.Excel.Application xlApp = null;
 7 try
 8 {
 9 xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
10 }
11 catch (Exception ex)
12 {
13 throw ex;
14 }
15 
16 if (xlApp != null)
17 {
18 try
19 {
20 Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
21 object oMissing = System.Reflection.Missing.Value;
22 Microsoft.Office.Interop.Excel.Worksheet xlSheet = null;
23 
24 xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1];
25 xlSheet.Name = dt.TableName;
26 
27 int rowIndex = 1;
28 int colIndex = 1;
29 int colCount = dt.Columns.Count;
30 int rowCount = dt.Rows.Count;
31 
32 //列名的处理
33 for (int i = 0; i < colCount; i++)
34 {
35 xlSheet.Cells[rowIndex, colIndex] = dt.Columns[i].ColumnName;
36 colIndex++;
37 }
38 //列名加粗显示
39 
40 //同时可以设置样式等
41 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, colCount]).Font.Bold = true;
42 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
43 xlSheet.get_Range(xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowCount + 1, colCount]).Font.Size = "10";
44 rowIndex++;
45 
46 //数据内容逐行写入，当数据量大的时候建议采用异步回调方法；
47 
48 for (int i = 0; i < rowCount; i++)
49 {
50 colIndex = 1;
51 for (int j = 0; j < colCount; j++)
52 {
53 xlSheet.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();
54 colIndex++;
55 }
56 rowIndex++;
57 }
58 xlSheet.Cells.EntireColumn.AutoFit();
59 
60 xlApp.DisplayAlerts = false;
61 path = System.IO.Path.GetFullPath(path);
62 xlBook.SaveCopyAs(path);
63 xlBook.Close(false, null, null);
64 xlApp.Workbooks.Close();
65 Marshal.ReleaseComObject(xlSheet);
66 Marshal.ReleaseComObject(xlBook);
67 xlBook = null;
68 succeed = true;
69 }
70 catch (Exception ex)
71 {
72 succeed = false;
73 }
74 finally
75 {
76 xlApp.Quit();
77 Marshal.ReleaseComObject(xlApp);
78 int generation = System.GC.GetGeneration(xlApp);
79 xlApp = null;
80 System.GC.Collect(generation);
81 }
82 }
83 }
84 return succeed;
85 }

office链接类库：https://files.cnblogs.com/files/weiweiboqi/microsoft.office.interop.excel.zip

 

posted @ 2015-05-20 09:08  五好青年，勇往直前  阅读(632)  评论(0)  编辑  收藏  举报