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"));
                        else
                            newCell.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()
}