asp.net中利用NPOI实现导出DataSet到Excel中,首先下载对应的dll,下载地址:http://download.csdn.net/detail/taomanman/8865699
将其添加到项目引用中。
下面给出一个方法NPOIHelper.cs,代码如下:
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.IO; using System.Reflection; using System.Text; using NPOI; using NPOI.HPSF; using NPOI.HSSF; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.SS.Util; using NPOI.POIFS; using NPOI.Util; using NPOI.HSSF.Util; using NPOI.XSSF.UserModel; /// <summary> ///NPOIHelper 的摘要说明 /// </summary> public class NPOIHelper { #region 由DataSet、DataTable导出Excel /// <summary> /// 由DataSet导出Excel,被外界调用的方法 /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="fileName">指定Excel工作表名称</param> /// <param name="fileName">strType=0:普通格式 1有格式化的形式</param> /// <returns>Excel工作表</returns> public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName, string strType) { MemoryStream ms = null; if (strType == "0") { ms = ExportDataSetToBasicExcel(sourceDs, sheetName) as MemoryStream; } else { ms = ExportDataSetToFormatExcel(sourceDs, sheetName) as MemoryStream; } HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls"); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; } /// <summary> /// 由DataSet导出Excel(基本形式) /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> private static Stream ExportDataSetToBasicExcel(DataSet sourceDs, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符 for (int i = 0; i < sheetNames.Length; i++) { HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]); // handling value. int rowIndex = 0; int sheetnum = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { #region 创建表头 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheetnum++; sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString()); } var headerRow = sheet.CreateRow(0); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in sourceDs.Tables[i].Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); rowIndex = 1; } #endregion #region 创建内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceDs.Tables[i].Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; #endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; } /// <summary> /// 由DataSet导出Excel(带有格式) /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> private static Stream ExportDataSetToFormatExcel(DataSet sourceDs, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符 for (int i = 0; i < sheetNames.Length; i++) { HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]); var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count]; foreach (DataColumn item in sourceDs.Tables[i].Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int k = 0; k < sourceDs.Tables[i].Rows.Count; k++) { for (int j = 0; j < sourceDs.Tables[i].Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(sourceDs.Tables[i].Rows[k][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int sheetnum = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { #region 创建表头 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheetnum++; sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString()); } var headerRow = sheet.CreateRow(0); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //设置边框 headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.BottomBorderColor = HSSFColor.BLACK.index; headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.LeftBorderColor = HSSFColor.GREEN.index; headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.RightBorderColor = HSSFColor.BLUE.index; headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.TopBorderColor = HSSFColor.ORANGE.index; //设置背景色 headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index; headStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.BIG_SPOTS; headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; foreach (DataColumn column in sourceDs.Tables[i].Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } rowIndex = 1; } #endregion #region 创建内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceDs.Tables[i].Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 case "MySql.Data.Types.MySqlDateTime": //MySql类型 if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue)) { //当时间为空,防止生成的execl 中是一串“#######”号,所有赋值为空字符串 newCell.SetCellValue(""); } else { 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); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } //设置首行首列冻结 //第一个参数表示要冻结的列数 //第二个参数表示要冻结的行数 //第三个参数表示右边区域可见的首列序号,从1开始计算 //第四个参数表示下边区域可见的首行序号,也是从1开始计算 sheet.CreateFreezePane(1, 1, 0, 10); } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return ms; } /// <summary> /// 由DataTable导出Excel,基本方法 /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <returns>Excel工作表</returns> private static Stream ExportDataTableToBasicExcel(DataTable sourceTable, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); var sheet = workbook.CreateSheet(sheetName); int rowIndex = 0; int sheetnum = 1; foreach (DataRow row in sourceTable.Rows) { #region 创建表头 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheetnum++; sheet = workbook.CreateSheet(sheetName + "-" + sheetnum.ToString()); } var headerRow = sheet.CreateRow(0); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in sourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); rowIndex = 1; } #endregion HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; workbook = null; return ms; } /// <summary> /// 带格式化的 /// </summary> /// <param name="dtSource">数据源</param> /// <param name="sheetName">创建的Sheet名称</param> /// <returns></returns> private static Stream ExportDataTableToFormatExcel(DataTable dtSource, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(sheetName); var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 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; int sheetnum = 1; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheetnum++; sheet = workbook.CreateSheet(sheetName + "-" + sheetnum.ToString()); } #region 列头及样式 { var headerRow = sheet.CreateRow(0); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //设置背景色 headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index; 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); } } #endregion rowIndex = 1; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 case "MySql.Data.Types.MySqlDateTime": //MySql类型 if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue)) { //当时间为空,防止生成的execl 中是一串“#######”号,所有赋值为空字符串 newCell.SetCellValue(""); } else { 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); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } } /// <summary> /// 由DataTable导出Excel /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="fileName">指定Excel工作表名称</param> /// <param name="sheetName">指定Sheet名称</param> /// <param name="strType">strType=0:基本的,1:带有格式的</param> /// <returns>Excel工作表</returns> public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName, string strType) { MemoryStream ms = null; if (strType == "0") { ms = ExportDataTableToBasicExcel(sourceTable, sheetName) as MemoryStream; } else { ms = ExportDataTableToFormatExcel(sourceTable, sheetName) as MemoryStream; } HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls"); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; } /// <summary> /// 由DataTable导出Excel(适应于基本的模版导出,且不超过65535条) /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="modelpath">模版文件实体路径</param> /// <param name="modelName">模版文件名称</param> /// <param name="fileName">指定Excel工作表名称</param> /// <param name="sheetName">作为模型的Excel</param> /// <param name="rowindex">从第几行开始写入数据(此为行索引,若为1则从第2行开始写入数据)</param> /// <returns>Excel工作表</returns> public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName, int rowIndex) { int colIndex = 0; FileStream file = new FileStream(modelpath + "/" + modelName, FileMode.Open, FileAccess.Read);//读入excel模板 HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName); if (sourceTable.Rows.Count + rowIndex > 65535) { throw new ArgumentException("数据太多,系统尚不支持,请缩小查询范围!"); } foreach (DataRow row in sourceTable.Rows) { //双循环写入sourceTable中的数据 colIndex = 0; HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex); foreach (DataColumn col in sourceTable.Columns) { xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString()); colIndex++; } rowIndex++; } sheet1.ForceFormulaRecalculation = true; //CS项目适用胡方法 //FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存 //hssfworkbook.Write(fileS); //fileS.Close(); MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls"); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; } #endregion #region 从Excel中读数据到DataTable /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="strFileName">Excel文件全路径(服务器路径)</param> /// <param name="extension">Excel文件的扩展名</param> /// <param name="SheetName">要获取数据的工作表名称</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string strFileName, string extension, string SheetName, int HeaderRowIndex) { using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = null; if (extension.Equals(".xls") || extension.Equals(".XLS")) { workbook = new HSSFWorkbook(file); } else { workbook = new XSSFWorkbook(file); } return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex); } } /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="strFileName">Excel文件全路径(服务器路径)</param> /// <param name="extension">Excel文件的扩展名</param> /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string strFileName, string extension, int SheetIndex, int HeaderRowIndex) { using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = null; if (extension.Equals(".xls") || extension.Equals(".XLS")) { workbook = new HSSFWorkbook(file); } else { workbook = new XSSFWorkbook(file); } string SheetName = workbook.GetSheetName(SheetIndex); return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex); } } /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="ExcelFileStream">Excel文件流</param> /// <param name="SheetName">要获取数据的工作表名称</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex) { IWorkbook workbook = new HSSFWorkbook(ExcelFileStream); ExcelFileStream.Close(); return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex); } /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="ExcelFileStream">Excel文件流</param> /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) { IWorkbook workbook = new HSSFWorkbook(ExcelFileStream); ExcelFileStream.Close(); string SheetName = workbook.GetSheetName(SheetIndex); return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex); } /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="workbook">要处理的工作薄</param> /// <param name="SheetName">要获取数据的工作表名称</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex) { ISheet sheet = workbook.GetSheet(SheetName); DataTable table = new DataTable(); try { IRow headerRow = sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; #region 循环各行各列,写入数据到DataTable for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); if (cell == null) { dataRow[j] = null; } else { switch (cell.CellType) { case CellType.BLANK: dataRow[j] = null; break; case CellType.BOOLEAN: dataRow[j] = cell.BooleanCellValue; break; case CellType.NUMERIC: dataRow[j] = cell.ToString(); break; case CellType.STRING: dataRow[j] = cell.StringCellValue; break; case CellType.ERROR: dataRow[j] = cell.ErrorCellValue; break; case CellType.FORMULA: default: dataRow[j] = "=" + cell.CellFormula; break; } } } table.Rows.Add(dataRow); //dataRow[j] = row.GetCell(j).ToString(); } #endregion } catch (System.Exception ex) { table.Clear(); table.Columns.Clear(); table.Columns.Add("出错了"); DataRow dr = table.NewRow(); dr[0] = ex.Message; table.Rows.Add(dr); return table; } finally { //sheet.Dispose(); workbook = null; sheet = null; } #region 清除最后的空行 for (int i = table.Rows.Count - 1; i > 0; i--) { bool isnull = true; for (int j = 0; j < table.Columns.Count; j++) { if (table.Rows[i][j] != null) { if (table.Rows[i][j].ToString() != "") { isnull = false; break; } } } if (isnull) { table.Rows[i].Delete(); } } #endregion return table; } #endregion }
aspx页面中某个按钮的点击事件代码如下调用:
DataSet ds = XXXX(); //用于获取数据库数据
NPOIHelper.ExportDataSetToExcel(ds, "统计报表" + DateTime.Now.ToString("yyyyMMddHHmmss"), "统计表", "0");