DGV 导出Excel【原生态控件】和【封装控件DGV】
一、Inf封装控件导出Excel
/// <summary> /// 将一个DataGridView导入Excel(YS) /// </summary> /// <param name="dgv"></param> public void ExportDataToExcel(MzGridControl dgv) { //string fileName = ""; string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; //saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; //导出Excel dgv.GridExport(saveFileName, GridExportType.Excel); //导出Word // dgv.GridExport(saveFileName, GridExportType.Excel); }
内部封装
/// <summary> /// 文档导出 /// </summary> public void GridExport(string filename, GridExportType exportType) { if (string.IsNullOrEmpty(filename)) { throw new ArgumentNullException(nameof(filename), "导出文件名为空"); } if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(filename))) { throw new ArgumentException(nameof(filename), "导出路径不存在"); } string extension = System.IO.Path.GetExtension(filename); switch (exportType) { case GridExportType.Excel: if (extension != ".xls" && extension != ".xlsx") { throw new ArgumentException(nameof(filename), "导出文件扩展名不正确"); } gridExcelExporter.Export(Grid, filename); break; } }
二、原生态控件DGV导出Excel【NPOI】
#region 读取excel /// <summary> /// 根据Excel和Sheet返回DataTable /// </summary> /// <param name="filePath">Excel文件地址</param> /// <param name="sheetIndex">Sheet索引</param> /// <returns>DataTable</returns> public static DataTable GetDataTableByExcelPath(string filePath, int sheetIndex) { return GetDataSetByExcelPath(filePath, sheetIndex).Tables[0]; } /// <summary> /// 根据Excel返回DataSet /// </summary> /// <param name="filePath">Excel文件地址</param> /// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param> /// <returns>DataSet</returns> public static DataSet GetDataSetByExcelPath(string filePath, int? sheetIndex = null) { DataSet ds = new DataSet(); IWorkbook fileWorkbook; using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (filePath.Last() == 's') { try { fileWorkbook = new HSSFWorkbook(fs); } catch (Exception ex) { //logger.Error("打开Excel文件失败！", ex); throw ex; } } else { try { fileWorkbook = new XSSFWorkbook(fs); } catch { fileWorkbook = new HSSFWorkbook(fs); } } } for (int i = 0; i < fileWorkbook.NumberOfSheets; i++) { if (sheetIndex != null && sheetIndex != i) continue; DataTable dt = new DataTable(); ISheet sheet = fileWorkbook.GetSheetAt(i); // 表名 dt.TableName = sheet.SheetName; //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int j = 0; j < header.LastCellNum; j++) { object obj = GetValueTypeForXLS(header.GetCell(j) as ICell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + j.ToString())); } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(j); } //数据 IEnumerator rows = sheet.GetEnumerator(); int RowIndex = sheet.FirstRowNum + 1; while (rows.MoveNext()) { if (sheet.GetRow(RowIndex) == null) { break; } DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int K in columns) { dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as ICell); if (dr[K] != null && dr[K].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } RowIndex++; } ds.Tables.Add(dt); } return ds; } #endregion #region 导出excel 【使用NPOI库】 /// <summary> /// DataTable转存为Excel文件【使用NPOI库】 /// </summary> /// <param name="dt"></param> /// <param name="filePath"></param> public static void GetExcelByDataTable(DataTable dt, string filePath) { // 创建一个新的Excel工作簿 IWorkbook workbook = new XSSFWorkbook(); // 创建一个新的工作表并命名为“Sheet1” ISheet worksheet = workbook.CreateSheet("Sheet1"); // 将DataTable的列名写入工作表中 IRow headerRow = worksheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = headerRow.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } // 将DataTable的数据写入工作表中 for (int i = 0; i < dt.Rows.Count; i++) { IRow dataRow = worksheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = dataRow.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } // 保存Excel文件 using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fileStream); } } /// <summary> /// 将excel导入到datatable /// </summary> /// <param name="filePath">excel路径</param> /// <param name="isColumnName">第一行是否是列名</param> /// <returns>返回datatable</returns> public static DataTable GetDataTableByExcel(string filePath, bool isColumnName) { DataTable dataTable = null; FileStream fs = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; int startRow = 0; try { using (fs = File.OpenRead(filePath)) { // 版本后缀控制 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs); // 版本后缀控制 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0);//第一行 int cellCount = firstRow.LastCellNum;//列数 //构建datatable的列 if (isColumnName) { startRow = 1;//如果第一行是列名，则从第二行开始读取 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { cell = firstRow.GetCell(i); if (cell != null) { if (cell.StringCellValue != null) { column = new DataColumn(cell.StringCellValue); dataTable.Columns.Add(column); } } } } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { column = new DataColumn("column" + (i + 1)); dataTable.Columns.Add(column); } } //填充行 for (int i = startRow; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row == null) continue; dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { cell = row.GetCell(j); if (cell == null) { dataRow[j] = ""; } else { //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) switch (cell.CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 if (format == 14 || format == 31 || format == 57 || format == 58) dataRow[j] = cell.DateCellValue; else dataRow[j] = cell.NumericCellValue; break; case CellType.String: dataRow[j] = cell.StringCellValue; break; } } } dataTable.Rows.Add(dataRow); } } } } } return dataTable; } catch (Exception) { if (fs != null) { fs.Close(); } return null; } } public static void GetExcelByDataGridView(DataGridView dgv, string filePath) { // 创建一个新的Excel工作簿 IWorkbook workbook = new XSSFWorkbook(); // 创建一个新的工作表并命名为“Sheet1” ISheet worksheet = workbook.CreateSheet("Sheet1"); // 将DataTable的列名写入工作表中 IRow headerRow = worksheet.CreateRow(0); for (int i = 0; i < dgv.Columns.Count; i++) { ICell cell = headerRow.CreateCell(i); cell.SetCellValue(dgv.Columns[i].HeaderText); } // 将DataTable的数据写入工作表中 for (int i = 0; i < dgv.Rows.Count; i++) { IRow dataRow = worksheet.CreateRow(i + 1); for (int j = 0; j < dgv.Columns.Count; j++) { ICell cell = dataRow.CreateCell(j); cell.SetCellValue(dgv.Rows[i].Cells[j].ToString()); } } // 保存Excel文件 using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fileStream); } } #endregion
使用：
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
if (dlg.ShowDialog() != DialogResult.OK)
{
return;
}
ExcelHelper.GetExcelByDataGridView()
