NPOI导出Excel

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace MyExcelTest
{
    public partial class ExportExcelTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        #region 导出附图1

        #region 导出
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("Trd_ExportTest_GetExportList", null);
            SimpleExcelName(dt, "附图1导出测试", "2016年第一季度数据", "附图1导出测试");
        }
        #endregion

        #region 导出Excel
        //导出Excel
        /// <summary>
        ///导出Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="sheetName">工作薄名称</param>
        /// <param name="excelTitle">Excel第一行标题</param>
        /// <param name="excelName">Excel名称</param>
        public void SimpleExcelName(DataTable dt, string sheetName, string excelTitle, string excelName)
        {
            try
            {
                HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                ISheet sheet = book.CreateSheet(sheetName);//设置Ecxel Head 格式
                HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle();
                HSSFFont font = (HSSFFont)book.CreateFont();
                font.FontName = "黑体";
                font.FontHeightInPoints = 12;
                style.SetFont(font);
                style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;

                if (dt.Rows.Count > 0)
                {
                    int index = 0;
                    if (excelTitle != "")
                    {
                        //合并5行  表头第一行
                        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 5);

                        IRow irow = sheet.CreateRow(0);
                        HSSFCell Headcell = (HSSFCell)irow.CreateCell(0);
                        Headcell.SetCellValue(excelTitle);

                        Headcell.CellStyle = style;
                        sheet.AddMergedRegion(cellRangeAddress);
                        index += 1;
                    }

                    IRow row1 = sheet.CreateRow(index);
                    //表头第二行（每一列的列名）
                    for (var j = 0; j < dt.Columns.Count; j++)
                    {
                        HSSFCell cell = (HSSFCell)row1.CreateCell(j);
                        cell.SetCellValue(dt.Columns[j].ColumnName.ToString());
                        sheet.SetColumnWidth(j, 20 * 256);
                        cell.CellStyle = style;
                    }

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row = sheet.CreateRow(i + index + 1);

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            HSSFCell cell = (HSSFCell)row.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            //设置列宽
                            sheet.SetColumnWidth(j, 20 * 256);
                        }
                    }
                }
                sheet.ForceFormulaRecalculation = true; //合并 
                for (int m = 2; m < 4; m++)
                {
                    for (int n = 2; n < dt.Rows.Count + 2; n++)
                    {
                        //sheet.GetRow(n + 1).GetCell(m);
                        if (sheet.GetRow(n + 1) != null)
                        {
                            if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n + 1).GetCell(m).ToString())
                            {
                                //sheet.AddMergedRegion(new Rvegion(n, m, n + 1, m));
                                CellRangeAddress cellRangeAddress = new CellRangeAddress(n, n + 1, m, m);

                                sheet.AddMergedRegion(cellRangeAddress);

                                //for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
                                //{
                                //    IRow row = HSSFCellUtil.GetRow(i, sheet);
                                //    ICell singleCell = HSSFCellUtil.GetCell(row, m);
                                //    singleCell.CellStyle = style;
                                //}  
                                //样式
                                //ICellStyle sty = book.CreateCellStyle();
                                ////设置单元格的样式：水平对齐居中
                                //sty.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
                                ////sty.Alignment = HorizontalAlignment.CENTER;
                                ////将新的样式赋给单元格
                                //var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
                                //cell.CellStyle = sty;
                            }
                        }
                    }
                }

                //写入到客户端
                MemoryStream ms = new MemoryStream();
                book.Write(ms);

                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(excelName, Encoding.UTF8).ToString() + DateTime.Now.ToString("yyyyMMdd")));
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                book = null;
                ms.Close();
                ms.Dispose();
            }
            catch
            {
                throw;
            }

        }
        #endregion

        #endregion 导出附图1

        
    }
}


导出效果图