vs上NuGet搜索NPOI.Excel 和 Newtonsoft.Json，安装。https://www.cnblogs.com/mq0036/p/9835975.html
/// <summary>
/// 导出账单汇总
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExportBillSummary_Click(object sender, EventArgs e)
{
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
string shortPath = "/download/" + fileName;
string destPath = HttpContext.Current.Server.MapPath("~" + shortPath);
IWorkbook workbook = null;
#region 创建工作表
if (destPath.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook();
}
else if (destPath.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook();
}
var sheet = workbook.CreateSheet();
sheet.SetColumnWidth(0, 30 * 200);//索引从0开始，乘以200是因为宽度是256分之1
#endregion
#region 赋值单元格
ICellStyle styleContentCenter = workbook.CreateCellStyle();
styleContentCenter.Alignment = HorizontalAlignment.Center;
styleContentCenter.VerticalAlignment = VerticalAlignment.Center;
styleContentCenter.WrapText = true;
ICellStyle styleContentLeft = workbook.CreateCellStyle();
styleContentLeft.Alignment = HorizontalAlignment.Left;
styleContentLeft.WrapText = true;
ICellStyle styleContentLeftRed = workbook.CreateCellStyle();
styleContentLeftRed.Alignment = HorizontalAlignment.Left;
styleContentLeftRed.WrapText = true;
styleContentLeftRed.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
styleContentLeftRed.FillPattern = FillPattern.SolidForeground;
{
IRow row1 = sheet.CreateRow(0);//索引从0开始
var cell1 = row1.CreateCell(0);
cell1.SetCellValue("xxxxxx");
cell1.CellStyle = styleContentCenter;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));//合并单元格，索引从0开始
}
{
int rowIndex = 28;
IRow row29 = sheet.CreateRow(rowIndex);
row29.Height = 2 * 256;//2乘以256，是两行的高度
var cell1 = row29.CreateCell(0);
cell1.SetCellValue("The storage cost above only refers to one SKU for each pallet. For those mixed SKUs on one pallet, the rate will increase by 10% for each extra SKU .");
cell1.CellStyle = styleContentLeft;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 5));
}
#endregion
#region 生成服务器端文件
using (FileStream fs2 = new FileStream(destPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs2);
}
#endregion
#region 以字符流的形式下载文件
byte[] bytes = null;
using (FileStream fs3 = new FileStream(destPath, FileMode.Open))
{
bytes = new byte[(int)fs3.Length];
fs3.Read(bytes, 0, bytes.Length);
fs3.Close();
HttpContext.Current.Response.ContentType = "application/octet-stream";
//通知浏览器下载文件而不是打开
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
// HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BinaryWrite(bytes);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
File.Delete(destPath);//删除服务器端文件
#endregion
}
using Newtonsoft.Json.Linq; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.IO; namespace Framework.Common { /// <summary> /// Excel 导入导出 /// </summary> public class ExcelHelpers { #region 导入excel /// <summary> /// 读取 Excel /// 如果遇到整行为空行就停止读取并返回 /// </summary> /// <param name="jsonHeader"> /// 返回 JSON 结构所需要字段名称，与 Excel 的列头相对应 /// </param> /// <param name="sheetIndex"> /// 读取 Excel 时从 sheetIndex Sheet 开始读取，最小值为 0 /// </param> /// <param name="startRowIndex"> /// 读取 Excel 时从 startRowIndex 行开始读取，最小值为 0 /// </param> /// <param name="filePath"> /// 要读取的 Excel 文件路径 /// </param> /// <returns></returns> public JArray ReadExcel(string[] jsonHeader, int sheetIndex, int startRowIndex, string filePath) { if (sheetIndex < 0) throw new Exception("sheetIndex 超出最小值，最小值为 0."); if (startRowIndex < 0) throw new Exception("startRowIndex 超出最小值，最小值为 0."); if (!File.Exists(filePath)) throw new Exception("文件不存，请检查文件路径是否正确"); JArray array = new JArray(); XSSFWorkbook workbook = null; using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(fs); } var sheet = workbook.GetSheetAt(sheetIndex); GetRow(array, jsonHeader, sheet, startRowIndex); return array; } /// <summary> /// 读取 Excel /// 如果遇到整行为空行就停止读取并返回 /// </summary> /// <param name="jsonHeader"> /// 返回 JSON 结构所需要字段名称，与 Excel 的列头相对应 /// </param> /// <param name="sheetIndex"> /// 读取 Excel 时从 sheetIndex Sheet 开始读取，最小值为 0 /// </param> /// <param name="startRowIndex"> /// 读取 Excel 时从 startRowIndex 行开始读取，最小值为 0 /// </param> /// <param name="fs"></param> /// <returns></returns> public JArray ReadExcel(string[] jsonHeader, int sheetIndex, int startRowIndex, Stream fs) { if (sheetIndex < 0) throw new Exception("sheetIndex 超出最小值，最小值为 0."); if (startRowIndex < 0) throw new Exception("startRowIndex 超出最小值，最小值为 0."); JArray array = new JArray(); XSSFWorkbook workbook = new XSSFWorkbook(fs); var sheet = workbook.GetSheetAt(sheetIndex); GetRow(array, jsonHeader, sheet, startRowIndex); return array; } private void GetRow(JArray array, string[] jsonHeader, ISheet sheet, int rowIndex) { JObject json = new JObject(); var row = sheet.GetRow(rowIndex); if (row != null) { var hasNext = false; object val = string.Empty; for (var i = 0; i < jsonHeader.Length; i++) { var cell = row.GetCell(i); if (cell == null) { val = null; json[jsonHeader[i]] = string.Empty; } else { val = GetCellValue(json, jsonHeader[i], cell); } if (!hasNext && val != null) { // 若该行中，任意单元格不是 string.Empty 则将 hasNext 设置为 true 表示可以递归读取下一行 hasNext = true; } } if (hasNext) { json["rowIndex"] = row.RowNum + 1; array.Add(json); GetRow(array, jsonHeader, sheet, rowIndex + 1); } else { return; } } } private object GetCellValue(JToken json, string head, ICell cell) { switch (cell.CellType) { case CellType.Error: case CellType.Unknown: case CellType.Blank: return null; case CellType.Boolean: json[head] = cell.BooleanCellValue;//.ToString(); return cell.BooleanCellValue; case CellType.Numeric: //对时间格式的处理 short format = cell.CellStyle.DataFormat; if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20) { json[head] = cell.DateCellValue;//.ToString("yyyy-mm-dd HH:mm:ss"); return cell.DateCellValue; } json[head] = cell.NumericCellValue; return cell.NumericCellValue;//.ToString("N4"); } json[head] = cell.StringCellValue; return cell.StringCellValue;//.ToString("N4"); } #endregion #region 导出excel /// <summary> /// 导出excel /// </summary> /// <param name="dtSource"></param> /// <param name="exportFileName"></param> /// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks> public void ExportExcel(JArray array, string[] jsonHeader, string exportFileName) { IWorkbook workbook = null; if (exportFileName.Contains(".xls")) { workbook = new HSSFWorkbook(); } else { workbook = new XSSFWorkbook(); } ISheet sheet = workbook.CreateSheet(); //填充表头 IRow row = sheet.CreateRow(0); for (var h = 0; h < jsonHeader.Length; h++) { row.CreateCell(h).SetCellValue(jsonHeader[h]); } //填充内容 int startRowIndex = 1; foreach (var item in array) { row = sheet.CreateRow(startRowIndex); for (var h = 0; h < jsonHeader.Length; h++) { row.CreateCell(h).SetCellValue(item[jsonHeader[h]].Value<string>()); } startRowIndex++; } //保存excel到路径exportFileName using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(exportFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } } #endregion } }
使用：
JArray array = new JArray(); string fileName = "123.xls"; for (int i = 1; i <= 10; i++) { JObject json = new JObject(); json["A"] = i.ToString(); json["B"] = i.ToString(); array.Add(json); } string exportFileName = MapPath("/") + fileName + ".xls"; string[] jsonHeader = new string[] { "A", "B" }; new ExcelHelper().ExportExcel(array, jsonHeader, exportFileName);
/// <summary> /// 获取物理路径 /// </summary> /// <param name="seedFile">/floder1/floder2/</param> /// <returns></returns> public static string MapPath(string seedFile) { var absolutePath = new Uri(Assembly.GetExecutingAssembly().CodeBase).AbsolutePath.Replace("/bin/Debug", ""); var directoryName = Path.GetDirectoryName(absolutePath); var path = directoryName + seedFile.Replace('/', '\\'); return path; }
代码：
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; namespace CMS.Common { public class ExcelHelper : IDisposable { private string fileName = null; //文件名 private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public ExcelHelper(string fileName) { this.fileName = fileName; disposed = false; } /// <summary> /// 将DataTable数据导入到excel中 /// </summary> /// <param name="data">要导入的数据</param> /// <param name="isColumnWritten">DataTable的列名是否要导入</param> /// <param name="sheetName">要导入的excel的sheet的名称</param> /// <returns>导入数据行数(包含列名那一行)</returns> public int NPOIDataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) { int i = 0; int j = 0; int count = 0; ISheet sheet = null; fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); try { if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); } count = 1; } else { count = 0; } for (i = 0; i < data.Rows.Count; ++i) { IRow row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } workbook.Write(fs); //写入到excel return count; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return -1; } } /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable NPOIExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fs); } if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //startRow = sheet.FirstRowNum + 1; //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { if (fs != null) fs.Close(); } fs = null; disposed = true; } } } }
使用方法：
private DataTable dt = new DataTable(); protected void Button1_Click(object sender, EventArgs e) { Inidata(); string path = Server.MapPath("~/ExportData/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"); int no = new Common.ExcelHelper(path).NPOIDataTableToExcel(dt, "sheet1", true); } /// <summary> /// 装载数据 /// </summary> private void Inidata() { dt.TableName = "sheet1"; dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Team", typeof(string)); DataRow dr = dt.NewRow(); DataRow dr1 = dt.NewRow(); dr["Name"] = "科比"; dr["Team"] = "湖人"; dt.Rows.Add(dr); dr1["Name"] = "詹姆斯"; dr1["Team"] = "骑士"; dt.Rows.Add(dr1); }
最近发现，导出成 .xls 的已经不行了，只能导出 .xlsx 格式的。相关dll下载：http://pan.baidu.com/s/1o7KzI22
using Abp.Domain.Services; using Newtonsoft.Json.Linq; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.IO; using System.Text; using System.Web; namespace Framework.Common { /// <summary> /// Excel 导入导出 /// </summary> public class ExcelHelper : DomainService { /// <summary> /// 读取 Excel /// 如果遇到整行为空行就停止读取并返回 /// </summary> /// <param name="jsonHeader"> /// 返回 JSON 结构所需要字段名称，与 Excel 的列头相对应 /// </param> /// <param name="sheetIndex"> /// 读取 Excel 时从 sheetIndex Sheet 开始读取，最小值为 0 /// </param> /// <param name="startRowIndex"> /// 读取 Excel 时从 startRowIndex 行开始读取，最小值为 0 /// </param> /// <param name="filePath"> /// 要读取的 Excel 文件路径 /// </param> /// <returns></returns> public JArray ReadExcel(string[] jsonHeader, int sheetIndex, int startRowIndex, string filePath) { if (sheetIndex < 0) throw new Exception("sheetIndex 超出最小值，最小值为 0."); if (startRowIndex < 0) throw new Exception("startRowIndex 超出最小值，最小值为 0."); if (!File.Exists(filePath)) throw new Exception("文件不存，请检查文件路径是否正确"); JArray array = new JArray(); XSSFWorkbook workbook = null; using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(fs); } var sheet = workbook.GetSheetAt(sheetIndex); GetRow(array, jsonHeader, sheet, startRowIndex); return array; } /// <summary> /// 读取 Excel /// 如果遇到整行为空行就停止读取并返回 /// </summary> /// <param name="jsonHeader"> /// 返回 JSON 结构所需要字段名称，与 Excel 的列头相对应 /// </param> /// <param name="sheetIndex"> /// 读取 Excel 时从 sheetIndex Sheet 开始读取，最小值为 0 /// </param> /// <param name="startRowIndex"> /// 读取 Excel 时从 startRowIndex 行开始读取，最小值为 0 /// </param> /// <param name="fs"></param> /// <returns></returns> public JArray ReadExcel(string[] jsonHeader, int sheetIndex, int startRowIndex, Stream fs) { if (sheetIndex < 0) throw new Exception("sheetIndex 超出最小值，最小值为 0."); if (startRowIndex < 0) throw new Exception("startRowIndex 超出最小值，最小值为 0."); JArray array = new JArray(); XSSFWorkbook workbook = new XSSFWorkbook(fs); var sheet = workbook.GetSheetAt(sheetIndex); GetRow(array, jsonHeader, sheet, startRowIndex); return array; } /// <summary> /// 将数据导入到 Excel 模版 /// </summary> /// <param name="array"> /// JArray 结构，需要导入到 Excel 中的数据 /// </param> /// <param name="jsonHeader"> /// JSON 结构所需要字段名称，与 Excel 的列头相对应 /// </param> /// <param name="sheetIndex"> /// 读取 Excel 时从 sheetIndex Sheet 开始读取，最小值为 0 /// </param> /// <param name="startRowIndex"> /// 读取 Excel 时从 startRowIndex 行开始读取，最小值为 0 /// </param> /// <param name="excelTempPath"> /// Excel 模版路径 /// </param> /// <param name="exportFileName"> /// Excel 导出时的文件名称 /// </param> /// <param name="context"> /// HttpContext 对象 /// </param> public void ImportDataToExcel(JArray array, string[] jsonHeader, int sheetIndex, int startRowIndex, string excelTempPath, string exportFileName, HttpContext context) { if (sheetIndex < 0) throw new Exception("sheetIndex 超出最小值，最小值为 0."); if (startRowIndex < 0) throw new Exception("startRowIndex 超出最小值，最小值为 0."); if (!File.Exists(excelTempPath)) throw new Exception("文件不存，请检查文件路径是否正确"); IWorkbook workbook = null; string tempExt = Path.GetExtension(excelTempPath).ToLower(); using (FileStream fs = File.Open(excelTempPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { if (tempExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = new XSSFWorkbook(fs); } } ISheet sheet = null; if (tempExt == ".xls") { sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex); } else { sheet = (XSSFSheet)workbook.GetSheetAt(sheetIndex); } IRow row = null; ICell cell = null; foreach (var item in array) { row = sheet.CreateRow(startRowIndex); for (var h = 0; h < jsonHeader.Length; h++) { cell = row.CreateCell(h); cell.SetCellValue(item[jsonHeader[h]].Value<string>()); } startRowIndex++; } context.Response.ContentType = "application/vnd.ms-excel"; context.Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"); context.Response.AddHeader("Content-Transfer-Encoding", "binary"); context.Response.ContentType = "application/octet-stream"; context.Response.ContentEncoding = Encoding.UTF8; using (MemoryStream file = new MemoryStream()) { workbook.Write(file); byte[] buff = file.GetBuffer(); //context.Response.BinaryWrite(file.GetBuffer());//输出到浏览器 #region 输出文件 if (File.Exists(exportFileName)) { File.Delete(exportFileName); } using (FileStream fs = new FileStream(exportFileName, FileMode.Create)) { using (BinaryWriter bw = new BinaryWriter(fs)) { bw.Write(buff, 0, buff.Length); } } #endregion } } private void GetRow(JArray array, string[] jsonHeader, ISheet sheet, int rowIndex) { JObject json = new JObject(); var row = sheet.GetRow(rowIndex); if (row != null) { var hasNext = false; object val = string.Empty; for (var i = 0; i < jsonHeader.Length; i++) { var cell = row.GetCell(i); if (cell == null) { val = null; json[jsonHeader[i]] = string.Empty; } else { val = GetCellValue(json, jsonHeader[i], cell); } if (!hasNext && val != null) { // 若该行中，任意单元格不是 string.Empty 则将 hasNext 设置为 true 表示可以递归读取下一行 hasNext = true; } } if (hasNext) { json["rowIndex"] = row.RowNum + 1; array.Add(json); GetRow(array, jsonHeader, sheet, rowIndex + 1); } else { return; } } } private object GetCellValue(JToken json, string head, ICell cell) { switch (cell.CellType) { case CellType.Error: case CellType.Unknown: case CellType.Blank: return null; case CellType.Boolean: json[head] = cell.BooleanCellValue;//.ToString(); return cell.BooleanCellValue; case CellType.Numeric: //对时间格式的处理 short format = cell.CellStyle.DataFormat; if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20) { json[head] = cell.DateCellValue;//.ToString("yyyy-mm-dd HH:mm:ss"); return cell.DateCellValue; } json[head] = cell.NumericCellValue; return cell.NumericCellValue;//.ToString("N4"); } json[head] = cell.StringCellValue; return cell.StringCellValue;//.ToString("N4"); } } }
使用
string excelTempPath = System.Web.HttpContext.Current.Server.MapPath("~/TemplateFile/ItemTemp.xls"); string exportFileName = System.Web.HttpContext.Current.Server.MapPath("~/ExportFile/" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); string[] jsonHeader = new string[] { "Code", "Name"}; JArray array = new JArray(); foreach (var item in items) { JObject json = new JObject(); json["Code"] = item.Code; json["Name"] = item.Name; array.Add(json); } new ExcelHelp().ImportDataToExcel(array, jsonHeader, 0, 1, excelTempPath, exportFileName, System.Web.HttpContext.Current);
导出的时候，注意判断后缀名。.xls 使用 HSSFWorkbook，.xlsx 使用XSSFWorkbook
