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 }
//如有问题欢迎大家指正