NPOI导出excel（居中，合并单元格）

NPOI导出excel（居中，合并单元格），excel表头作为参数传入

BLL层：

using System;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using NPOI.SS.Util;
namespace Business
{
    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="title">excel表头数组</param>
        /// <param name="data">datatable数据</param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public int DataTableToExcel(string[] title, DataTable data, string sheetName)
        {
            int count = 1;
            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();
            ICellStyle cellstyle = workbook.CreateCellStyle();
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.Alignment = HorizontalAlignment.Center;
            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }
                //excel表头
                IRow rowtitle = sheet.CreateRow(0);
                for (var j = 0; j < title.Length; ++j)
                {
                    ICell cell = rowtitle.CreateCell(j);
                    cell.SetCellValue(title[j]);
                    cell.CellStyle = cellstyle;
                }
                //data
                for (var i = 0; i < data.Rows.Count; ++i)
                {
                    IRow rowdata = sheet.CreateRow(count);
                    ICell cellNo = rowdata.CreateCell(0);
                    cellNo.SetCellValue(i+1);
                    cellNo.CellStyle = cellstyle;
                    for (var j = 0; j < data.Columns.Count; ++j)
                    {
                        ICell cell = rowdata.CreateCell(j+1);
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        cell.CellStyle = cellstyle;
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }
        /// <summary>
        /// DataTable数据导出到excel
        /// </summary>
        /// <param name="title">多表头二维数组</param>
        /// <param name="data">dt数据</param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public int DataTableToExcel(string[,] title,DataTable data, string sheetName)
        {
            int count;
            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();

            ICellStyle cellstyle = workbook.CreateCellStyle();
            cellstyle.VerticalAlignment = VerticalAlignment.Center;
            cellstyle.Alignment = HorizontalAlignment.Center;

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }
                //表头
                for (var k = 0; k < title.GetLength(0); k++)
                {
                    IRow rowtitle = sheet.CreateRow(k);
                    for (var m = 0; m < title.GetLength(1); m++)
                    {
                        ICell cell = rowtitle.CreateCell(m);
                        cell.SetCellValue(title[k, m]);
                        cell.CellStyle = cellstyle;
                        if ((k == 0 && m < 9) || (k == 0 && m > 14))
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(k, k + 1, m, m));
                        }
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 8, 10));
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 11, 14));
                    }
                }
                //data
                count = title.GetLength(0);
                for (var i = 0; i < data.Rows.Count; ++i)
                {
                    IRow rowdata = sheet.CreateRow(count);
                    ICell cellNo = rowdata.CreateCell(0);
                    cellNo.SetCellValue(i + 1);
                    cellNo.CellStyle = cellstyle;
                    for (var j = 0; j < data.Columns.Count; ++j)
                    {
                        ICell cell = rowdata.CreateCell(j+1);
                        cell.SetCellValue(data.Rows[i][j].ToString());
                        cell.CellStyle = cellstyle;
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

}

Controller代码：

        public string CreateProjectExcel()
        {
            string fileName = Utils.GetCurrentTime() + ".xls";
            string path = Server.MapPath("~/") + "Temp\\";
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            path = path + fileName;
            DataTable data = QueryData(9);

            string[] title = new string[] { "序号", "编号", "企业名称", "所属一级企业", "变化类型", "调整后的上级单位", "所在企业层级代码", "调整前企业编码", "原所属一级企业", "备注" };
            using (ExcelHelper eh = new ExcelHelper(path))
            {
                int count = eh.DataTableToExcel(title, data, "二级企业宗地编号");
            }
            return fileName;
        }

        public string CreateExcel_tdjbqk()
        {
            string[,] title = {{"序号", "宗地编号", "所属一级企业", "地籍号", "土地面积（平方米）", "宗地位置", "土地使用权人", "土地区域范围", "土地取得方式", "土地权属","", "土地利用、开发","","","","使用者与权利人是否一致", "变化类型", "调整列的列号", "调整前内容", "原宗地编号", "原所属一级企业", "备注"},{ "", "", "", "", "", "", "", "", "", "土地证件办理情况", "未办理土地证原因", "出租情况", "是否已列入污染扰民搬迁", "是否已列入拆迁范围", "是否已列入开发土地","","","","","","",""}};
            string fileName = Utils.GetCurrentTime() + ".xls";
            DataTable dt = QueryData(16);
            string path = Server.MapPath("~/") + "Temp\\";
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);
            }
            path = path + fileName;
            using (ExcelHelper eh = new ExcelHelper(path))
            {
                int count = eh.DataTableToExcel(title,dt, "土地基本情况");
            }
            return fileName;
        }

html中：

        $("#btn_op_search").click(function () {
            $.post('/Report/CreateExcel_tdjbqk', function (data) {
                window.location.href = '../Temp/' + data;
            });
        });

 

转载于:https://www.cnblogs.com/peaceOfMind/p/6026265.html