导出Excel数据
方法一：--------------------------------------
前端
<div>
<button type="button" class="btn btn-primary btn-app btn-xs" id="btn_query" ><i class="ace-icon fa fa-search bigger-120"></i>查询</button>
<div class="btn btn-primary btn-app btn-xs" onclick="download()"><i class="ace-icon fa fa-file-excel-o bigger-120"></i>下载</div>
</div>
<script type="text/javascript">
function download() { //下载excel文件
$.ajax({
type: 'POST',
url: '@Url.Action("GetSPexcelFile","SP")',
//contentType: "application/json; charset=utf-8",
data: {
SPItemNo: $('#SPItemNo').val(),
Desc: $('#Desc').val(),
Snno: $('#snno').val(),
Building: $('#building').find("option:selected").val(),
WC: $('#WC').find("option:selected").val(),
Equipid: $('#Equipid').find("option:selected").val(),
StartDate: $('#startDate').val(),
EndDate: $('#endDate').val(),
StartIndex: 1,
PageSize: 100000,//最大只能是10万行
},
async: false,
success: function (result) {
console.log(result);
window.location = '/SP/Download?fileName=' + result;
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
//$.dialog.alert("查询失败");
//$wrapper.spinModal(false);
}
});
}
</script>
后台
[HttpPost]
public JsonResult GetSPexcelFile(string SPItemNo, string Desc, string snno, string Building, string WC, string Equipid, string StartDate, string EndDate)
{
string ExcelFileName = Server.MapPath("~/temp/") + Guid.NewGuid().ToString() + ".xlsx";
try
{
BaseManager bm = new BaseManager();
var sql = string.Format("exec sp_SparepartsFromtable '{0}','{1}','{2}','B1','{4}','{5}','{6}','{7}'"
, SPItemNo, Desc, snno, Building, WC, Equipid, StartDate, EndDate);
DataTable data = bm.FillTable(sql);
EMMS.Web.Helper.ExcelHelper.TableToExcelForXLSX(data,ExcelFileName);
return Json(ExcelFileName);
//DataSet ds = com.aaa.DAL.DataAnalysis.MultiGND.GetMultiGND1(qm);
//ExcelHelper.DataTableToExcel(ExcelFileName, ds.Tables[0], "sheet", true);
}
catch (Exception ex)
{
return Json(ExcelFileName);
}
}
public ActionResult Download(string fileName)
{
return File(fileName, "application/vnd.ms-excel", "welcome.xlsx");
}
方法二：
<div class="widget-header">
<button class="btn btn-primary" data-action="grid-operation" data-role="ace-export" data-url="/@(ViewContext.RouteData.Values["controller"])/ExportAll">
</div>
<form id="queryForm" class="form-group" method="post">
<div class="col-sm-2" style="padding-left: 2px; padding-right: 2px">
<label>事业部(Building)</label>
@Html.AceDropDownList("plant", ViewData["Plants"] as IEnumerable<SelectListItem>, new { onchange = "SetMainWC()", @style = "width:100%" })
</div>
</form>
<script>
$(document).ready(function () {
$('button[data-role=ace-export]').each(function () {
var element = $(this);
var action = $(element).data("url");
if (action) {
$(this).on('click', function () {
var form = $('#queryForm');
var options = { plant: $('#plant').find("option:selected").val() };
if (form) {
options.data = $('#queryForm').serialize();
}
$.download(action, options);
});
}
});
});
</script>
后台：public ActionResult ExportAll(string plant)
{
BaseManager bm = new BaseManager();
var sql = string.Format("exec sp_exportsql '{0}'", "Schedule");
var data1 = bm.FillTable(sql);
sql = data1.Rows[0][0].ToString();
sql = sql + " where A.[plant] in ('" + plant + "') ";
sql += " order by PlanDate,A.EquipID,A.[CreateDate]";
var data = bm.FillTable(sql);
if (data.Rows.Count == 0)
{
var result = new OperationResult();
result.Errors.Add("没有数据(No Data Found)!");
return Json(result, JsonRequestBehavior.AllowGet);
}
else
{
string ExcelFileName = Server.MapPath("~/temp/") + Guid.NewGuid().ToString() + ".xlsx";
EMMS.Core.Helper.ExcelHelper.PMScheduleTableToExcelForXLSX(data, ExcelFileName);
return ExportExcel(ExcelFileName, "PMSchedule");
}
}
public ActionResult ExportExcel(string path, string downloadname)
{
var fi = new System.IO.FileInfo(downloadname);
if (fi.Extension == "")
downloadname += ".xlsx";
JeffSoft.Logger.Error("ExportExcel path"+ path);
return Json(new { Success = true, path = path, contenttype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", downloadname = downloadname }, JsonRequestBehavior.AllowGet);
}
引用ExcelHelper.cs
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.Core.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"></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> /// 将DataTable数据导出到Excel文件中(xlsx) /// </summary> /// <param name="dt"></param> /// <param name="file"></param> public static void FAmasterTableToExcelForXLSX(DataTable dt, string file) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet("Sheet1"); ICellStyle cellStyle = xssfworkbook.CreateCellStyle(); cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.FillForegroundColor = 26; //表头 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()); cell.CellStyle = cellStyle; } } //转为字节数组 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> /// 将DataTable数据导出到Excel文件中(xlsx) /// </summary> /// <param name="dt"></param> /// <param name="file"></param> public static void PMScheduleTableToExcelForXLSX(DataTable dt, string file) { XSSFWorkbook xssfworkbook = new XSSFWorkbook(); ISheet sheet = xssfworkbook.CreateSheet("Sheet1"); ICellStyle cellStyle = xssfworkbook.CreateCellStyle(); cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.FillForegroundColor = 26; //表头 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); } //给第二行第一列的单元格赋值 IRow rowtemp = sheet.CreateRow(1); rowtemp.CreateCell(0).SetCellValue("第一行变红色字体内容"); XSSFCellStyle fCellStyle = (XSSFCellStyle)xssfworkbook.CreateCellStyle(); //创建单元格样式 XSSFFont ffont = (XSSFFont)xssfworkbook.CreateFont(); //创建字体 ffont.Color = NPOI.HSSF.Util.HSSFColor.Red.Index; //给字体设置颜色 fCellStyle.SetFont(ffont); //给样式添加字体 rowtemp.GetCell(0).CellStyle = fCellStyle; //给第二行第一列单元格添加样式 //提示内容 //IRow rowWarm = sheet.CreateRow(1); //ICell cellWarm = rowWarm.CreateCell(0); //HSSFFont fontt = (HSSFFont)xssfworkbook.CreateFont(); //fontt.Color = NPOI.HSSF.Util.HSSFColor.Red.Index; //cellWarm.SetCellValue("变红色字体内容"); //cellWarm.CellStyle = cellStyle; //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 2); 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(); } } private static ICellStyle CreateTQReportSimpleBorderStyle(IWorkbook wb) { ICellStyle style = wb.CreateCellStyle(); style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; return style; } /// <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; } } }
