c#_导出table功能
一：第一张导出方法，简单快捷 请注意：一般表格都有真分页，查询数据时候注意把分页条件去掉
#region 此处是获取的list数组 然后转table再调用ExportExcel
var list="你的list数据库源" DataTable dt = new DataTable(); dt.Columns.Add("序号", typeof(string)); dt.Columns.Add("姓名", typeof(string)); int datacount = 1; foreach (var item in list)//list给table赋值 { DataRow tr = dt.NewRow(); tr[0] = datacount; tr[1] = item.XM; datacount++; }
//然后调用
ExportExcel（"dt","文件名字"）
//或者直接调用
ExportExcel（"table数据源","文件名字"）
/// <summary> /// 导出功能 此方法直接给table 和导出的文件名即可 已经封装好 直接调用 /// </summary> /// <param name="dt">数据源</param> /// <param name="tablename">导出的名字</param> public void ExportExcel(DataTable dt, string filename) { string path = AppDomain.CurrentDomain.BaseDirectory + @"" + filename + ".xls"; WriteExcel(dt, path); System.IO.FileInfo filet = new System.IO.FileInfo(path); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(filename + ".xls")); Response.AddHeader("Content-Length", filet.Length.ToString()); Response.ContentType = "application/ms-excel"; Response.WriteFile(filet.FullName); Response.End(); } public void WriteExcel(DataTable dt, string path) { try { long totalCount = dt.Rows.Count; long rowRead = 0; float percent = 0; System.IO.StreamWriter sw = new System.IO.StreamWriter(path, false, System.Text.Encoding.GetEncoding("gb2312")); System.Text.StringBuilder sb = new System.Text.StringBuilder(); for (int k = 0; k < dt.Columns.Count; k++) { sb.Append(dt.Columns[k].ColumnName.ToString() + " \t"); } sb.Append(Environment.NewLine); for (int i = 0; i < dt.Rows.Count; i++) { rowRead++; percent = ((float)(100 * rowRead)) / totalCount; for (int j = 0; j < dt.Columns.Count; j++) { sb.Append(dt.Rows[i][j].ToString() + "\t"); } sb.Append(Environment.NewLine); } sw.Write(sb.ToString()); sw.Flush(); sw.Close(); } catch (Exception ex) { } }
此导出方式实际是.CSV(数字与纯文本格式)能用excel打开而已 所以在设置导出格式无能为力（如时间格式就会出现问题）
二：第二种，能改变导出excel格式
引用 NPOI文件来进行 导出， 也是封装好的， 只需要把相对应的引用文件 引用好直接调用就可以
引用到的NPOI 
请注意： 此方法无法用于AJAX ，可解决导出时间或者身份证显示######问题
例子
//前台调用导出按钮 $("#WriteDoctor").click(function () { var StartTime = $("#startDate").val(); var EndTime = $("#endDate").val(); var Department = $(".drop_btn .drop_btn_val").text(); var DoctorName = $(".drop_btn2 .drop_btn_val").text(); if (Department == "全部") { Department = "" } if (DoctorName == "全部") { DoctorName = "" } window.location.href = "@Url.Action("WriteDoctor")?StartTime=" + StartTime + "&EndTime=" + EndTime ； }) //后台方法 public ActionResult WriteDoctor(DateTime StartTime) { DataSet ds = 去查询数据库数据(DateTIme, StarTime); string paths = "table表名字" + DateTime.Now.ToString("yyyyMMdd") + ".xls"; NPOIHelper.ExportByWeb(ds, "table表名字" + DateTime.Now.ToString("yyyyMMdd"), paths); return Json(new { success = true }, JsonRequestBehavior.AllowGet); }
using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Web; //引用 System.Web using System.Collections.Generic; namespace DBUtility { public class NPOIHelper { /// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> public static void Export(DataTable dtSource, string strHeaderText, string strFileName) { using (MemoryStream ms = Export(dtSource, strHeaderText)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = ""; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = ""; //填加xls文件作者信息 si.ApplicationName = ""; //填加xls文件创建程序信息 si.LastAuthor = ""; //填加xls文件最后保存者信息 si.Comments = ""; //填加xls文件作者信息 si.Title = ""; //填加xls文件标题信息 si.Subject = "";//填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); ICellStyle dataStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); dateStyle.Alignment = HorizontalAlignment.Center; dateStyle.VerticalAlignment = VerticalAlignment.Center; dateStyle.BorderBottom = BorderStyle.Thin; dateStyle.BorderLeft = BorderStyle.Thin; dateStyle.BorderRight = BorderStyle.Thin; dateStyle.BorderTop = BorderStyle.Thin; //----- dataStyle.Alignment = HorizontalAlignment.Center; dataStyle.VerticalAlignment = VerticalAlignment.Center; dataStyle.BorderBottom = BorderStyle.Thin; dataStyle.BorderLeft = BorderStyle.Thin; dataStyle.BorderRight = BorderStyle.Thin; dataStyle.BorderTop = BorderStyle.Thin; //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.VerticalAlignment = VerticalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 600; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.VerticalAlignment = VerticalAlignment.Center; headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 600; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); newCell.CellStyle = dataStyle; break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); newCell.CellStyle = dataStyle; break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); newCell.CellStyle = dataStyle; break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); newCell.CellStyle = dataStyle; break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); newCell.CellStyle = dataStyle; break; default: newCell.SetCellValue(""); newCell.CellStyle = dataStyle; break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; ////workbook.c // workbook //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet return ms; } } /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataSet</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream ExportMoreTable(DataSet dsSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); foreach (DataTable dtSource in dsSource.Tables) { ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = ""; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = ""; //填加xls文件作者信息 si.ApplicationName = ""; //填加xls文件创建程序信息 si.LastAuthor = ""; //填加xls文件最后保存者信息 si.Comments = ""; //填加xls文件作者信息 si.Title = ""; //填加xls文件标题信息 si.Subject = "";//填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); ICellStyle dataStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); dateStyle.Alignment = HorizontalAlignment.Center; dateStyle.VerticalAlignment = VerticalAlignment.Center; dateStyle.BorderBottom = BorderStyle.Thin; dateStyle.BorderLeft = BorderStyle.Thin; dateStyle.BorderRight = BorderStyle.Thin; dateStyle.BorderTop = BorderStyle.Thin; //----- dataStyle.Alignment = HorizontalAlignment.Center; dataStyle.VerticalAlignment = VerticalAlignment.Center; dataStyle.BorderBottom = BorderStyle.Thin; dataStyle.BorderLeft = BorderStyle.Thin; dataStyle.BorderRight = BorderStyle.Thin; dataStyle.BorderTop = BorderStyle.Thin; //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.VerticalAlignment = VerticalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 600; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.VerticalAlignment = VerticalAlignment.Center; headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 600; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); newCell.CellStyle = dataStyle; break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); newCell.CellStyle = dataStyle; break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); newCell.CellStyle = dataStyle; break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); newCell.CellStyle = dataStyle; break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); newCell.CellStyle = dataStyle; break; default: newCell.SetCellValue(""); newCell.CellStyle = dataStyle; break; } } #endregion rowIndex++; } } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; ////workbook.c // workbook //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet return ms; } } ///// <summary> ///// 用于Web网页 直接导出 ///// </summary> ///// <param name="dtSource">源DataTable</param> ///// <param name="strHeaderText">表头文本</param> ///// <param name="strFileName">文件名</param> public static void ExportByWeb(DataSet dtSource, string strHeaderText, string strFileName) { HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(ExportMoreTable(dtSource, strHeaderText).GetBuffer()); curContext.Response.End(); } public static void ExportByWeb(MemoryStream file, string strFileName) { HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(file.ToArray()); curContext.Response.End(); } /// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable Import(string strFileName) { DataTable dt = new DataTable(); //XSSFWorkbook xhssfworkbook; HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; } public static DataTable ImportOther(string strFileName) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; XSSFWorkbook xhssfworkbook; ISheet sheet = null; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (!strFileName.Contains(".xlsx")) { hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); } else { xhssfworkbook = new XSSFWorkbook(file); sheet = xhssfworkbook.GetSheetAt(0); } } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; } /// <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); // workbook.Close(); } } 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; } /// <param name="hssfworkbook">Excel操作类</param> /// <param name="fontname">字体名</param> /// <param name="fontcolor">字体颜色</param> /// <param name="fontsize">字体大小</param> /// <returns></returns> //public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize) //{ // IFont font1 = hssfworkbook.CreateFont(); // if (string.IsNullOrEmpty(fontfamily)) // { // font1.FontName = fontfamily; // } // if (fontcolor != null) // { // font1.Color = fontcolor.Indexed; // } // font1.IsItalic = true; // font1.FontHeightInPoints = (short)fontsize; // return font1; //} /// <param name="hssfworkbook">Excel操作类</param> /// <param name="font">单元格字体</param> /// <param name="fillForegroundColor">图案的颜色</param> /// <param name="fillPattern">图案样式</param> /// <param name="fillBackgroundColor">单元格背景</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> //public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, IFont font, NPOI.HSSF.Util.HSSFColor fillForegroundColor, FillPattern fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va) //{ // ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); // cellstyle.FillPattern = fillPattern; // cellstyle.Alignment = ha; // cellstyle.VerticalAlignment = va; // if (fillForegroundColor != null) // { // cellstyle.FillForegroundColor = fillForegroundColor.Indexed; // } // if (fillBackgroundColor != null) // { // cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed; // } // if (font != null) // { // cellstyle.SetFont(font); // } // //有边框 // cellstyle.BorderBottom = BorderStyle.Thin; // cellstyle.BorderLeft = BorderStyle.Thin; // cellstyle.BorderRight = BorderStyle.Thin; // cellstyle.BorderTop = BorderStyle.Thin; // return cellstyle; //} /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); } } }
本文来自博客园，作者：12不懂3，转载请注明原文链接：https://www.cnblogs.com/LZXX/p/8761766.html
