C# 导出Excel
C# 导出Excel
前端：
function downloadReport() {
var queryParam = $.param($.bi.form.getData("form-searchBox"));
if (queryParam != "") {
window.open('@Url.Content("~/Order/DownloadReport?")' + queryParam);
} else {
window.open('@Url.Content("~/Order/DownloadReport")');
}
}
后端：
public ActionResult DownloadReport()
{
DataSet ds = ExportSaleOrderIntakeData();
DataTable dtResult = ds.Tables[0];
decimal total = dtResult.AsEnumerable().Select(t => t.Field<decimal>("Amount")).Sum();
DataRow drTotal = dtResult.NewRow();
drTotal[dtResult.Columns.IndexOf("Amount") - 1] = "Amount Total";
drTotal[dtResult.Columns.IndexOf("Amount")] = total;
dtResult.Rows.InsertAt(drTotal, 0);
var file = System.Web.HttpContext.Current.Server.MapPath("~/temp/") + Guid.NewGuid() + ".xlsx";
string fileName = ExcelHelper.TableToExcelForXLSX(dtResult,file);
return File(fileName, "application/ms-excel", "SalesReport_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx");
}
public static string TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Sheet1");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//file = System.Web.HttpContext.Current.Server.MapPath("~/temp/") + Guid.NewGuid() + ".xlsx";
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
return file;
}
ExcelHelper
using System; using System.Collections.Generic; using System.Data; using System.IO; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using NPOI.SS.Util; namespace EMMS.Web.Helper { public class ExcelHelper { #region Excel2003 /// <summary> /// 将Excel文件中的数据读出到DataTable中(xls) /// </summary> /// <param name="file"></param> /// <returns></returns> public static DataTable ExcelToTableForXLS(string file) { DataTable dt = new DataTable(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); ISheet sheet = hssfworkbook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell); if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } } return dt; } /// <summary> /// 将DataTable数据导出到Excel文件中(xls) /// </summary> /// <param name="dt"></param> /// <param name="file"></param> public static void TableToExcelForXLS(DataTable dt, string file) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("Test"); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //转为字节数组 MemoryStream stream = new MemoryStream(); hssfworkbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } /// <summary> /// 获取单元格类型(xls) /// </summary> /// <param name="cell"></param> /// <returns></returns> private static object GetValueTypeForXLS(HSSFCell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return null; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: return cell.NumericCellValue; case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default: return "=" + cell.CellFormula; } } #endregion #region Excel2007 public static List<List<String>> ExcelToListXLSX(string file) { List<List<String>> list = new List<List<String>>(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); ISheet sheet = xssfworkbook.GetSheetAt(0); //数据 for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row != null) { List<String> dataList = new List<String>(); for (int j = 0; j < row.LastCellNum; j++) { object obj = GetValueTypeForXLSX(row.GetCell(j) as XSSFCell); if (obj == null || obj.ToString() == string.Empty) { dataList.Add(""); } else { dataList.Add(obj.ToString()); } } list.Add(dataList); } } } return list; } /// <summary> /// 将Excel文件中的数据读出到DataTable中(xlsx) /// </summary> /// <param name="file"></param> /// <returns></returns> public static DataTable ExcelToTableForXLSX(string file, int headerRowNum) { DataTable dt = new DataTable(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); ISheet sheet = xssfworkbook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(headerRowNum); List<int> columns = new List<int>(); for (int i = 0; i < header.LastCellNum; i++) { object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell); if (obj == null || obj.ToString() == string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); //continue; } else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } //数据 int ii = 0; try { for (int i = headerRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr = dt.NewRow(); bool hasValue = false; foreach (int j in columns) { IRow row = sheet.GetRow(i); if (row != null) { ICell cell = row.GetCell(j); dr[j] = GetValueTypeForXLSX(cell as XSSFCell); } else { dr[j] = ""; } if (dr[j] != null && dr[j].ToString() != string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } ii++; } } catch (Exception e) { throw new Exception(string.Format("读取文件{0}出错，第{1}行，原因{2}", file, ii, e.Message)); //System.Diagnostics.Debug.WriteLine(e.Message); } } return dt; } public static MemoryStream ExportToExcel(DataTable dt, string header) { IWorkbook workbook = new XSSFWorkbook();//2007 ISheet sheet = workbook.CreateSheet("sheet0"); //设置大标题行 int rowCount = 0; //设置全局列宽和行高 sheet.DefaultColumnWidth = 14; //全局列宽 sheet.DefaultRowHeightInPoints = 15; //全局行高 //设置标题行数据 int a = 0; IRow row1 = sheet.CreateRow(rowCount); //创建报表表头标题列 string[] columnHeaders = header.Split(new char[] { ',' });//按逗号拆分标题 for (int k = 0; k < columnHeaders.Length; k++) { //将传递过来的字符串表头进行拆分到Excel string columnName = columnHeaders[k]; ICell cell = row1.CreateCell(a); cell.SetCellValue(columnName); a++; } //填写ds数据进excel for (int i = 0; i < dt.Rows.Count; i++) //写行数据 { IRow row2 = sheet.CreateRow(i + rowCount + 1); int b = 0; for (int j = 0; j < dt.Columns.Count; j++) { string dgvValue = string.Empty; dgvValue = dt.Rows[i][j].ToString(); ICell cell = row2.CreateCell(b); cell.SetCellValue(dgvValue); b++; } } MemoryStream memoryStream = new MemoryStream(); workbook.Write(memoryStream); byte[] bytes = memoryStream.ToArray(); // 设置当前流的位置为流的开始 return new MemoryStream(bytes); } public static MemoryStream ExportToExcel(DataTable dt) { IWorkbook workbook = new XSSFWorkbook();//2007 ISheet sheet = workbook.CreateSheet("sheet0"); //设置大标题行 int rowCount = 0; //设置全局列宽和行高 sheet.DefaultColumnWidth = 14; //全局列宽 sheet.DefaultRowHeightInPoints = 15; //全局行高 //设置标题行数据 int a = 0; IRow row1 = sheet.CreateRow(rowCount); //创建报表表头标题列 foreach (DataColumn dtColums in dt.Columns) { //将传递过来的字符串表头进行拆分到Excel string columnName = dtColums.ColumnName; ICell cell = row1.CreateCell(a); cell.SetCellValue(columnName); a++; } //填写ds数据进excel for (int i = 0; i < dt.Rows.Count; i++) //写行数据 { IRow row2 = sheet.CreateRow(i + rowCount + 1); int b = 0; for (int j = 0; j < dt.Columns.Count; j++) { string dgvValue = string.Empty; dgvValue = dt.Rows[i][j].ToString(); ICell cell = row2.CreateCell(b); cell.SetCellValue(dgvValue); b++; } } MemoryStream memoryStream = new MemoryStream(); workbook.Write(memoryStream); byte[] bytes = memoryStream.ToArray(); // 设置当前流的位置为流的开始 return new MemoryStream(bytes); } public static MemoryStream InsertPic(string file, string imagePath,int col = 0, int row = 0) { DataTable dt = new DataTable(); using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { byte[] bytes1 = System.IO.File.ReadAllBytes(imagePath); XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs); ISheet sheet = xssfworkbook.GetSheetAt(0); int pictureIdx = xssfworkbook.AddPicture(bytes1, XSSFWorkbook.PICTURE_TYPE_PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); //add a picture XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 0, col, row, 1, 3); IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); MemoryStream memoryStream = new MemoryStream(); xssfworkbook.Write(memoryStream); byte[] bytes = memoryStream.ToArray(); // 设置当前流的位置为流的开始 return new MemoryStream(bytes); } } /// <summary> /// 将DataTable数据导出到Excel文件中(xlsx) /// </summary> /// <param name="dt">DataTable数据</param> /// <param name="file">文件完整路径</param> public static void TableToExcelForXLSX(DataTable dt, string file) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet("Sheet1"); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //转为字节数组 MemoryStream stream = new MemoryStream(); xssfworkbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } } /// <summary> /// 获取单元格类型(xlsx) /// </summary> /// <param name="cell"></param> /// <returns></returns> private static object GetValueTypeForXLSX(XSSFCell cell) { if (cell == null) return null; switch (cell.CellType) { case CellType.Blank: //BLANK: return ""; case CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case CellType.Numeric: //NUMERIC: short format = cell.CellStyle.DataFormat; if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || DateUtil.IsCellDateFormatted(cell)) { return cell.DateCellValue; } else { return cell.NumericCellValue; } /** if (DateUtil.IsValidExcelDate(cell.NumericCellValue) || DateUtil.IsCellDateFormatted(cell)) { return cell.DateCellValue; } else { return cell.NumericCellValue; }*/ case CellType.String: //STRING: return cell.StringCellValue; case CellType.Error: //ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: string v = ""; switch (cell.CachedFormulaResultType) { case CellType.String: string strFORMULA = cell.StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { v = strFORMULA.ToString(); } else { v = ""; } break; case CellType.Numeric: v = Convert.ToString(cell.NumericCellValue); break; case CellType.Boolean: v = Convert.ToString(cell.BooleanCellValue); break; case CellType.Error: v = NPOI.SS.Formula.Eval.ErrorEval.GetText(cell.ErrorCellValue); break; default: v = cell.CellFormula; break; } return v; default: return cell.StringCellValue; } } #endregion /// <summary> /// 将datatable导出为excel /// 图片默认显示在excel 第二行最后一列 /// </summary> /// <param name="table">数据源</param> /// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param> /// <param name="sheetName">工作簿名称</param> /// <param name="picBytes">导出图片字节流</param> /// <param name="mergedRegion">合并单元格信息：null不合并单元格</param> /// <returns></returns> public static MemoryStream ExportToExcel2007(DataTable table, List<Tuple<string, string, int>> excelInfo, string sheetName, byte[] picBytes, List<CellRangeAddress> mergedRegion) { MemoryStream ms = new MemoryStream(); try { using (table) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); for (int i = 0; i < excelInfo.Count; i++) { sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256); } IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < excelInfo.Count; i++) { headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1); } int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < excelInfo.Count; i++) { dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString()); } rowIndex++; } //合并单元格 if (mergedRegion != null && mergedRegion.Count > 0) { foreach (CellRangeAddress cellRangeAddress in mergedRegion) { //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列 sheet.AddMergedRegion(cellRangeAddress); ICellStyle style = workbook.CreateCellStyle(); //设置单元格的样式：水平对齐居中 style.Alignment = HorizontalAlignment.Center; //将新的样式赋给单元格 var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn); cell.CellStyle = style; } } //插入图片 if (picBytes != null && picBytes.Length > 0) { var row1 = 2; var col1 = excelInfo.Count + 1; /* Add Picture to Workbook, Specify picture type as PNG and Get an Index */ int pictureIdx = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG); //添加图片 /* Create the drawing container */ XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); /* Create an anchor point */ XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 240, col1, row1, col1 + 1, row1 + 1); /* Invoke createPicture and pass the anchor point and ID */ XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx); /* Call resize method, which resizes the image */ picture.Resize(); picBytes = null; } workbook.Write(ms); } } catch (Exception ex) { ms = null; } return ms; } /// <summary> /// 将datatable导出为excel /// 图片默认显示在excel 第二行最后一列 /// </summary> /// <param name="table">数据源</param> /// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param> /// <param name="sheetName">工作簿名称</param> /// <param name="picBytes">导出图片字节流</param> /// <param name="mergedRegion">合并单元格信息：null不合并单元格</param> /// <returns></returns> public static MemoryStream ExportToExcel97(DataTable table, List<Tuple<string, string, int>> excelInfo, string sheetName, byte[] picBytes, List<CellRangeAddress> mergedRegion) { MemoryStream ms = new MemoryStream(); try { using (table) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); for (int i = 0; i < excelInfo.Count; i++) { sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256); } IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < excelInfo.Count; i++) { headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1); } int rowIndex = 1; foreach (DataRow row in table.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < excelInfo.Count; i++) { dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString()); } rowIndex++; } //合并单元格 if (mergedRegion != null && mergedRegion.Count > 0) { foreach (CellRangeAddress cellRangeAddress in mergedRegion) { //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域 //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列 sheet.AddMergedRegion(cellRangeAddress); ICellStyle style = workbook.CreateCellStyle(); //设置单元格的样式：水平对齐居中 style.Alignment = HorizontalAlignment.Center; //将新的样式赋给单元格 var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn); cell.CellStyle = style; } } //插入图片 if (picBytes != null && picBytes.Length > 0) { var row1 = 2; var col1 = excelInfo.Count + 1; int pictureIdx = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG); //添加图片 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 240, col1, row1, col1 + 1, row1 + 1); //图片位置，图片左上角为（col, row） HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); //用图片原始大小来显示 picBytes = null; } workbook.Write(ms); ms.Flush(); ms.Position = 0; } } catch (Exception ex) { ms = null; } return ms; } } }
