NPOIHelp 按固定模板导出和直接导出

完整代码如下

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
using System.IO;
using NPOI.XWPF.UserModel;
using NPOI.XSSF.UserModel;
using System.Web;
using NPOI.SS.UserModel;

namespace Public
{
    /// <summary>
    ///  数据导出-NPOIHelp
    /// </summary>
    public class NPOIHelp
    {


        #region 数据按固定模板导出

        /*
         * 
         * 调用使用案例
         * using (FileStream fs = System.IO.File.OpenRead(path))
        {
            设置表格
            HSSFWorkbook wk = new HSSFWorkbook(fs);
            fs.Close();
            //设置行
            ISheet sheet = wk.GetSheetAt(0);
            //设置行内容
            sheet.GetRow(0).GetCell(0).SetCellValue("测试");
            //设置列属性
            IRow row = sheet.GetRow(1);
            ICell cell = row.GetCell(0);
            cell.SetCellType(CellType.String);
            cell.SetCellValue("统计单位：" + (System.Web.HttpContext.Current.Session["GG"] as GROUP).NAME + str + "统计时间：" + DateTime.Now.ToString("yyyy年MM月dd日"));
            ExportWorkBook(xxx,xx,xxx);
        }
         * 
         * 
         * **/

        /// <summary>
        /// 根据数据源按固定模板数据填充
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="hssfworkbook">工作簿</param>
        /// <param name="strFileName">下载名称.xls</param>
        /// <param name="rowOffset">偏移行数</param>
        /// <param name="colOffset">偏移列数</param>
        /// <param name="sheetIndex">工作薄索引</param>
        public static void ExportWorkBook(DataTable dataSource, HSSFWorkbook hssfworkbook, string strFileName, int rowOffset = 0, int colOffset = 0, int sheetIndex = 0)
        {
            ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            //设置单元格上下左右边框线  
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

            for (int i = 0; i < dataSource.Rows.Count; i++)
            {
                for (int j = 0; j < dataSource.Columns.Count; j++)
                {
                    IRow row = sheet.GetRow(i + rowOffset);

                    if (row == null)
                        row = sheet.CreateRow(i + rowOffset);
                    NPOI.SS.UserModel.ICell newCell = row.GetCell(j + colOffset);

                    if (newCell == null)
                    {
                        newCell = row.CreateCell(j + colOffset);
                        newCell.CellStyle = cellStyle;
                    }
                    string drValue = dataSource.Rows[i][j].ToString();
                    string drType = dataSource.Columns[j].DataType.ToString();
                    #region 数据类型

                    switch (drType)
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            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);
                            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
                }
            }

            //提供下载程序
            ExportHSSFWorkbookByWeb(hssfworkbook, null, strFileName);

        }

        #endregion

        #region  NPOI- 数据导出 xls



        /**
        * 
        *  调用使用案例
        *   DataTable dt = new DataTable();
           dt.Columns.Add("编号", typeof(string));
           DataRow dr = dt.NewRow();
           dr[0] = 1;
           dt.Rows.Add(dr);
           Public.NPOIHelp.ExportXls("/emplate/AdministrationList/Excel/Book1.xls", dt, "一般案件管理.xls");
        * 
        * 
        * ***/

        /// <summary>
        ///  数据导出.xls -wps
        /// </summary>
        /// <param name="localFilePath">模板路径[虚拟路径]</param>
        /// <param name="dtSource">数据源 data</param>
        /// <param name="downLoadName">下载名称</param>
        public static void ExportXls(string localFilePath, DataTable dtSource, string downLoadName, int rowIndex = 0)
        {
            localFilePath = HttpContext.Current.Server.MapPath("~" + localFilePath);
            if (!File.Exists(localFilePath))
                new AccessViolationException("模板不存在");

            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

            HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            HSSFDataFormat format = (HSSFDataFormat)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;
                    }
                }
            }



            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建Sheet，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = (HSSFSheet)workbook.CreateSheet();
                    }

                    #region 列头及样式
                    {
                        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                        HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        HSSFFont font = (HSSFFont)workbook.CreateFont();
                        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);
                        }

                    }
                    #endregion
                    rowIndex = 1;
                }
                #endregion

                #region 填充内容
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);

                    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);
                            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++;
            }


            //模板文件  
            string path = HttpContext.Current.Server.MapPath(string.Format("~/Template/AdministrationList/Excel{0}.xls", Public.Property.getDateString));
            using (FileStream filess = new FileStream(path, FileMode.Create))
            {
                workbook.Write(filess);
                workbook.Close();
            }

            ExportHSSFWorkbookByWeb(workbook, null, downLoadName);

        }

        #endregion

        #region NPOI- 数据导出 xlsx

        /**
        * 
        *  调用使用案例
        *   DataTable dt = new DataTable();
           dt.Columns.Add("编号", typeof(string));
           DataRow dr = dt.NewRow();
           dr[0] = 1;
           dt.Rows.Add(dr);
           Public.NPOIHelp.ExportXls("/emplate/AdministrationList/Excel/Book1.xls", dt, "一般案件管理.xls");
        * 
        * 
        * ***/

        /// <summary>
        /// 导出Xlsx
        /// </summary>
        /// <param name="localFilePath">文件保存路径[虚拟路径]</param>
        /// <param name="dtSource">数据源</param>
        public static void ExportXlsx(string localFilePath, DataTable dtSource, string fileName)
        {
            localFilePath = HttpContext.Current.Server.MapPath("~" + localFilePath);
            if (!File.Exists(localFilePath))
                new AccessViolationException("模板不存在");

            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();

            XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFDataFormat format = (XSSFDataFormat)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;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = (XSSFSheet)workbook.CreateSheet();
                    }

                    #region 列头及样式
                    {
                        XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
                        XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        XSSFFont font = (XSSFFont)workbook.CreateFont();
                        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);
                        }
                    }
                    #endregion
                    rowIndex = 1;
                }
                #endregion

                #region 填充内容
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);

                    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);
                            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 (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
            //{
            //    workbook.Write(fs);
            //}

            string path = HttpContext.Current.Server.MapPath(string.Format("~/{ 0}.xls", Public.Property.getDateString));      //模板文件  
            File.Create(path);
            using (FileStream filess = File.OpenWrite(path))
            {
                workbook.Write(filess);
            }
            ExportHSSFWorkbookByWeb(null, workbook, fileName);
        }

        #endregion

        #region NPOI-导出Doc


        /// <summary>
        /// 导出Docx
        /// </summary>
        /// <param name="localFilePath">文件保存路径</param>
        /// <param name="dtSource">数据源</param>
        public static void ExportDocx(string localFilePath, DataTable dtSource)
        {

            XWPFDocument doc = new XWPFDocument();

            XWPFTable table = doc.CreateTable(dtSource.Rows.Count + 1, dtSource.Columns.Count);

            for (int i = 0; i < dtSource.Rows.Count + 1; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    if (i == 0)
                    {
                        table.GetRow(i).GetCell(j).SetText(dtSource.Columns[j].ColumnName);
                    }
                    else
                    {
                        table.GetRow(i).GetCell(j).SetText(dtSource.Rows[i - 1][j].ToString());
                    }
                }
            }

            using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
            {
                doc.Write(fs);
            }
        }

        #endregion

        #region NPOI-下载数据
        /// <summary>
        /// 将指定的HSSFWorkbook输出到流
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="strFileName">文件名</param>
        public static void ExportHSSFWorkbookByWeb(HSSFWorkbook hssWorkbook = null, XSSFWorkbook xssWorkbook = null, string strFileName = null)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                if (hssWorkbook == null)
                    xssWorkbook.Write(ms);
                else
                    hssWorkbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                HttpContext curContext = HttpContext.Current;

                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.Default;
                curContext.Response.Charset = "";
                curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName);
                curContext.Response.BinaryWrite(ms.GetBuffer());
                curContext.Response.End();
            }


        }

        #endregion


    }
}

 

以下是 按模板调用数据使用案例 

DataTable dt = new DataTable();
dt.Columns.Add("序号", typeof(string));

DataRow dr = dt.NewRow();
dr[0] = "测试";

 

using (FileStream fs = System.IO.File.OpenRead(Server.MapPath("~/Template/LawSix/Excel/涉案财物保管、处理情况登记表.xls")))
{
HSSFWorkbook wk = new HSSFWorkbook(fs);
fs.Close();
ISheet sheet = wk.GetSheetAt(0);
//设置行内容
sheet.GetRow(1).GetCell(0).SetCellValue(string.Format("时间：{0}", DateTime.Now.ToString("yyyy年MM月dd日")));
Public.NPOIHelp.ExportWorkBook(dt, wk, "涉案财物保管、处理情况登记表.xls", 6);

}