NPOI生成不规则Excel表格（并以流的形式下载，不将文件保存在服务器上，直接在客户端导出excel）

//下载NPOI类库并添加引用
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

public static void MergeCell(ISheet tb, ICell cl, int firstrow, int lastrow, int firstcol, int lastcol, string name)
        {
            tb.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol));
            cl.SetCellValue(name);
}


public static ICellStyle SetCellStyle(IWorkbook wk, int number)
        {
            ICellStyle cellStyle = wk.CreateCellStyle();
            if (number == 0)//标题栏不设置边框样式
            {
                //对齐  
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                //自动换行  
                cellStyle.WrapText = true;
            }
            else
            {
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
                //对齐  
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                //自动换行  
                cellStyle.WrapText = true;
            }
            return cellStyle;
}

 

public static string ExportDLAnalyse2Excel(string excleTitle,string area)
        {
            string[] dlDataArr = area.Split(',');
            int rowlength = dlDataArr.Length / 3;//数据表格的行数
            //创建工作薄  
            HSSFWorkbook wk = new HSSFWorkbook();
            //创建一个名称为Sheet1的表  
            ISheet tb = wk.CreateSheet();
            wk.SetSheetName(0, "Sheet1");
            for (int i = 0; i < rowlength+2; i++)
            {
                ICellStyle cellStyle = SetCellStyle(wk, i);
                IRow row = tb.CreateRow(i);
                for (int j = 0; j < 8; j++)//数据表格的列数
                {
                    ICell cell = row.CreateCell(j);
                    cell.CellStyle = cellStyle;
                }

            }

            MergeCell(tb, tb.GetRow(0).GetCell(2), 0, 0, 2, 5, excleTitle);//设置excel标题
            MergeCell(tb, tb.GetRow(1).GetCell(0), 1, 1, 0, 1, "编码");//设置表格标题栏
            MergeCell(tb, tb.GetRow(1).GetCell(2), 1, 1, 2, 5, "名称");
            MergeCell(tb, tb.GetRow(1).GetCell(6), 1, 1, 6, 7, "面积(/公顷)");

            int rownum = 2;
            for (int i = 0; i < dlDataArr.Length; i+=3)
            {
                MergeCell(tb, tb.GetRow(rownum).GetCell(0), rownum, rownum, 0, 1, dlDataArr[i]);
                MergeCell(tb, tb.GetRow(rownum).GetCell(2), rownum, rownum, 2, 5, dlDataArr[i + 1]);
                MergeCell(tb, tb.GetRow(rownum).GetCell(6), rownum, rownum, 6, 7, dlDataArr[i + 2]);
                rownum++;
            }

            MemoryStream mstream = new MemoryStream();
            wk.Write(mstream);
            DownloadFile(mstream, excleTitle, "xls");
            return null;
}

 

/// <summary>
/// 从服务器下载Excel到客户端
/// </summary>
/// <param name="fs"></param>
/// <param name="filename"></param>
/// <returns></returns>
public static string DownloadFile(MemoryStream fs, string filename,string extenname)
        {
            string fileName = filename + "."+extenname;//客户端保存的文件名 

            //以字符流的形式下载文件 
            byte[] bytes = fs.ToArray();
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ClearContent();
            System.Web.HttpContext.Current.Response.ClearHeaders();
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            //通知浏览器下载文件而不是打开 
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            System.Web.HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
            System.Web.HttpContext.Current.Response.BinaryWrite(bytes);
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
            return null;
}