1 //传入DataGridView
2 /// <summary>
3 /// 输出数据到Excel
4 /// </summary>
5 /// <param name="dataGridView">DataGridView</param>
6 /// <param name="includeHeader">是否包含字段名</param>
7 /// <param name="savePath">保存路径</param>
8 /// <returns>成功TRUE/失败FALSE</returns>
9 public static bool ExportExcel(DataGridView dataGridView, bool includeHeader, string savePath)
10 {
11 bool succeed = true;
12 try
13 {
14 //创建工作薄
15 IWorkbook wbook = null;
16 //创建工作表
17 ISheet sheet = null;
18 wbook = ICreateIWorkBook(savePath, null);
19 sheet = wbook.CreateSheet("Sheet1"); //创建工作表
20 int columnCount = dataGridView.ColumnCount; //获取行数
21 int rowCount = dataGridView.RowCount; //获取列数
22 int rowIndex = 0;
23 IRow row = null;
24 ICell cell;
25 int start = 0; //是否包含表头行数
26 //dataGridView是否有数据
27 if (rowCount <= 0)
28 {
29 succeed = false;
30 Exception error = new Exception("无可用于导出的数据");
31 throw error;
32 }
33 //是否包含表头
34 if (includeHeader)
35 {
36 row = sheet.CreateRow(rowIndex);
37 //输入标题表头行
38 for (int i = 0; i < columnCount; i++)
39 {
40 cell = row.CreateCell(i);
41 cell.SetCellValue(dataGridView.Columns[i].HeaderText);
42 }
43 ++start; //如果包含表头行索引累加
44 }
45
46 //DataGridView有多少行
47 for (; rowIndex < rowCount; ++rowIndex)
48 {
49 row = sheet.CreateRow(rowIndex + start); //创建行
50 for (int cellIndex = 0; cellIndex < columnCount; ++cellIndex)
51 {
52 Type type = dataGridView[cellIndex, rowIndex].ValueType;
53 object value = dataGridView[cellIndex, rowIndex].Value;
54 cell = row.CreateCell(cellIndex); //创建单元格
55 //写入数据
56 if (type == typeof(Int32))
57 {
58 cell.SetCellValue((int)dataGridView[cellIndex, rowIndex].Value);
59 }
60 else if (type == typeof(Double))
61 {
62 cell.SetCellValue((double)value);
63 }
64 else if (type == typeof(float))
65 {
66 cell.SetCellValue((float)value);
67 }
68 else if (type == typeof(DateTime))
69 {
70 cell.SetCellValue((DateTime)value);
71 }
72 else if (type == typeof(Boolean))
73 {
74 cell.SetCellValue((bool)value);
75 }
76 else
77 {
78 cell.SetCellValue((string)value);
79 }
80 }
81 }
82 //保存的文件流保存或创建
83 FileStream fs = new FileStream(savePath, FileMode.OpenOrCreate);
84 wbook.Write(fs); //写入文件
85 wbook.Close();
86 fs.Close();
87
88 }
89 catch (Exception ex)
90 {
91 succeed = false;
92 throw ex;
93 }
94 return succeed;
95 }
96
97
98 /// <summary>
99 /// 根据文件格式返回表文件
100 /// </summary>
101 /// <param name="filePath">文件路径</param>
102 /// <param name="fs">文件流</param>
103 /// <returns>IWorkBook 表</returns>
104 private static IWorkbook ICreateIWorkBook(string filePath, FileStream fs)
105 {
106 IWorkbook wbook = null;
107 //读取用户选择的保存格式
108 string extesion = Path.GetExtension(filePath).ToLower();
109 if (extesion.Equals(".xlsx"))
110 //if( saveFile.FileName.IndexOf(".xlsx") > 0);
111 {
112 if (null != fs)
113 {
114 wbook = new XSSFWorkbook(fs);
115 }
116 else
117 {
118 wbook = new XSSFWorkbook();
119 }
120 }
121 else if (extesion.Equals(".xls"))
122 //else if (saveFile.FileName.IndexOf(".xls") > 0) ; //这种方法也可以实现但.xlsx必须在前面
123 {
124 if (null != fs)
125 {
126 wbook = new HSSFWorkbook(fs);
127 }
128 else
129 {
130 wbook = new HSSFWorkbook();
131 }
132 }
133 else //如果不是上述两种格式,因为设置了过滤器几乎不会出现下面的情况
134 {
135 //MessageBox.Show("对不起，您所输入的文件格式不受支持！");
136 //return null;
137 Exception error = new Exception("文件格式不正确!");
138 throw error;
139 }
140 return wbook;
141 }
//如有问题欢迎大家指正