NPOI 导出Excel兼容xlsx和xls

1.可以从任何来源获取npoi的dll文件；

2.把这四个dll文件引入到你的bin目录下面

3.引入程序集：

XSSF.UserModel对应的导出是xlsx；

HSSF.UserModel对应的导出是xls；在导出时莫要用错了

下面奉上实例：（此处我用的版本较高的xlsx对应XSSF）

public static byte[] Export(DataTable dtSource,string strfilepath）

{ISheet sheet;XSSFWorkbook workbook = new XSSFWorkbook();ICellStyle dateStyle = workbook.CreateCellStyle();IDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");strfilepath = System.IO.Path.GetFileNameWithoutExtension(strfilepath);string fileFullName = HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath + "/Export/" + strfilepath + ".xlsx");if (File.Exists(fileFullName)){GC.Collect();//GC.SuppressFinalize(this);GC.WaitForPendingFinalizers();File.Delete(fileFullName);}sheet = workbook.CreateSheet(dtSource.TableName.ToString());//取得列宽 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){// 新建表，填充表头，填充列头，样式if (rowIndex == 65535 || rowIndex == 0){if (rowIndex != 0){sheet = workbook.CreateSheet(dtSource.TableName);}// 列头及样式{IRow headerRow = sheet.CreateRow(0);XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;XSSFFont font = workbook.CreateFont() as XSSFFont;font.FontHeightInPoints = 10;font.Boldweight = 700;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);}}rowIndex = 1;}// 填充内容IRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;foreach (DataColumn column in dtSource.Columns){XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;string drValue = row[column].ToString();switch (column.DataType.ToString()){case "System.String"://字符串类型 newCell.SetCellValue(drValue);break;case "System.DateTime"://日期类型 DateTime dateV;DateTime.TryParse(drValue, out dateV);if (!string.IsNullOrEmpty(drValue))newCell.SetCellValue(dateV.ToString("yyyy/MM/dd"));elsenewCell.SetCellValue("");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;}}rowIndex++;}}FileStream fileStream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write);workbook.Write(fileStream);workbook = null;fileStream.Dispose();FileStream fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Read);long fileSize = fs.Length;byte[] fileBuffer = new byte[fileSize];fs.Read(fileBuffer, 0, (int)fileSize);fs.Close();fileStream.Dispose();File.Delete(fileFullName);return fileBuffer;}

最后一步，就是导出了，直接看代码：

public static void ExportXlsxFile(DataTable dtSource, string strFileName)

{

HttpContext curContext = HttpContext.Current;

// 设置编码和附件格式

curContext.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;

curContext.Response.ContentEncoding = Encoding.UTF8;

curContext.Response.Charset = “”;

curContext.Response.AppendHeader(“Content-Disposition”,

“attachment;filename=” + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

curContext.Response.BinaryWrite(Export(dtSource, strFileName));

curContext.Response.End()

}