一：第一张导出方法，简单快捷 请注意：一般表格都有真分页，查询数据时候注意把分页条件去掉 #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数据源","文件名字"）
////// 导出功能 此方法直接给table 和导出的文件名即可 已经封装好 直接调用 /// /// 数据源 /// 导出的名字 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.Webusing System.Collections.Generic;namespace DBUtility{ public class NPOIHelper { /// /// DataTable导出到Excel文件 /// /// 源DataTable /// 表头文本 /// 保存位置 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(); } } } /// /// DataTable导出到Excel的MemoryStream /// /// 源DataTable /// 表头文本 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; } } /// /// DataTable导出到Excel的MemoryStream /// /// 源DataSet /// 表头文本 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; } } ///// ///// 用于Web网页 直接导出 ///// ///// 源DataTable ///// 表头文本 ///// 文件名 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(); } /// 读取excel /// 默认第一行为标头 /// /// excel文档路径 /// /// 将datatable导出为excel /// 图片默认显示在excel 第二行最后一列 /// /// 数据源 /// Tuple /// 工作簿名称 /// 导出图片字节流 /// 合并单元格信息：null不合并单元格 /// > excelInfo, string sheetName, byte[] picBytes, List 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; } /// /// 将datatable导出为excel /// 图片默认显示在excel 第二行最后一列 /// /// 数据源 /// Tuple /// 工作簿名称 /// 导出图片字节流 /// 合并单元格信息：null不合并单元格 /// > excelInfo, string sheetName, byte[] picBytes, List 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; } /// Excel操作类 /// 字体名 /// 字体颜色 /// 字体大小 ///