以下代码中最关键的代码是
|
1 |
Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.Worksheets.Add(miss, miss, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); |
网上很资料中都没有提到，我也是试了很久才搞出来的，上面这一行是新建了2个Sheet,想建多个就修改Add的第3个参数，数量为参数值+1
|
1 |
xlapp.Visible = false; //是否显示导出过程 ，建议关闭，否则在导出过程中鼠标点击Excel文件时会出错。 |
引用：COM --》 Microsoft Office xx.x Object Library 再引用 Microsoft.Office.Interop.Excel （可以到网上搜索或以下地址下载：http://files.cnblogs.com/fan0136/Microsoft.Office.Interop.Excel.rar）
完整的代码台下，实际使用中数据表参数可以修改成List<object>：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111 |
public
class ToExcel { /// <summary> /// 可以自定义导出Excel的格式，传的参数为GridView /// </summary> /// <param name="gridView"></param> /// <param name="filename"></param> public
static void ExportGridViewToExcel(DevExpress.XtraGrid.Views.Grid.GridView gridView, DevExpress.XtraGrid.Views.Grid.GridView gridView1, string
filename) { //System.Data.DataTable dt = (System.Data.DataTable)gridView.DataSource; SaveFileDialog sfd = new
SaveFileDialog(); filename += DateTime.Now.ToString("yyyyMMdd") + "-"
+ DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString(); sfd.FileName = filename; sfd.Filter = "Excel files (*xls) | *.xls"; sfd.RestoreDirectory = true; if
(sfd.ShowDialog() == DialogResult.OK && sfd.FileName.Trim() != null) { int
rowIndex = 1; int
rowIndex1 = 1; int
colIndex = 0; int
colNum = gridView.Columns.Count; System.Reflection.Missing miss = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application xlapp = new
Microsoft.Office.Interop.Excel.Application(); xlapp.Visible = true; Microsoft.Office.Interop.Excel.Workbooks mBooks = (Microsoft.Office.Interop.Excel.Workbooks)xlapp.Workbooks; Microsoft.Office.Interop.Excel.Workbook mBook = (Microsoft.Office.Interop.Excel.Workbook)mBooks.Add(miss); Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.Worksheets.Add(miss, miss, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet); Worksheet sm = mBook.Worksheets[1]; sm.Name = "q"; Worksheet ws = mBook.Worksheets[2]; ws.Name = "B"; //设置对齐方式 mSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; //设置文字自动换行 //mSheet.Cells.WrapText = true; //设置第一行高度，即标题栏 ((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["1:1", System.Type.Missing]).RowHeight = 20; //设置数据行行高度 ((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["2:"
+ gridView.RowCount + 1, System.Type.Missing]).RowHeight = 16; //设置字体大小（10号字体） mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]].Font.Size = 10; //设置单元格边框 Microsoft.Office.Interop.Excel.Range range1 = mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]]; range1.Borders.LineStyle = 1; //写标题 for
(int
row = 1; row <= gridView.Columns.Count; row++) { sm.Cells[1, row] = gridView.Columns[row - 1].GetTextCaption(); } //写标题 for
(int
row = 1; row <= gridView1.Columns.Count; row++) { ws.Cells[1, row] = gridView1.Columns[row - 1].GetTextCaption(); } try { for
(int
i = 0; i < gridView.RowCount; i++) { rowIndex++; colIndex = 0; for
(int
j = 0; j < gridView.Columns.Count; j++) { colIndex++; sm.Cells[rowIndex, colIndex] = gridView.GetRowCellValue(i, gridView.Columns[j]); } } for
(int
i = 0; i < gridView1.RowCount; i++) { rowIndex1++; colIndex = 0; for
(int
j = 0; j < gridView1.Columns.Count; j++) { colIndex++; ws.Cells[rowIndex1, colIndex] = gridView1.GetRowCellValue(i, gridView1.Columns[j]); } } mBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); } catch
(Exception ex) { throw
new Exception(ex.Message); } finally { mBooks.Close(); xlapp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp); GC.Collect(); } } else { //return false; } } } |
C# 导出Excel 多个Sheet,布布扣,bubuko.com
原文：http://www.cnblogs.com/Ares-blog/p/3632897.html