using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace CallCenter.Utility
{
    public class NPOIHelper
    {
        private string _title;
        private string _sheetName;
        private string _filePath;

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string[] columns = null)
        {
            FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            if (string.IsNullOrWhiteSpace(this._sheetName))
            {
                this._sheetName = "sheet1";
            }

            ISheet sheet = workBook.CreateSheet(this._sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(this._title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;

            //处理表格列头
            row = sheet.CreateRow(1);
            if (columns == null)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }
            }
            else
            {
                for (int i = 0; i < columns.Length; i++)
                {
                    row.CreateCell(i).SetCellValue(columns[i]);
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }
            }




            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(2 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="sheetName">空字符串或null的话默认sheet1</param>
        /// <param name="columns">自定义表格列头，默认null</param>
        /// <returns></returns>
        public bool ToExcel(DataTable table, string title, string sheetName, string filePath, string[] columns = null)
        {
            this._title = title;
            this._sheetName = sheetName;
            this._filePath = filePath;
            return ToExcel(table, columns);
        }

        /// <summary>
        /// 弹出下载框导出excel
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string ExportToExcel(string Name, DataTable dt, string[] cols = null)
        {
            try
            {
                //if (dt.Rows.Count > 0)
                //{
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet(Name);

                    ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.Alignment = HorizontalAlignment.Center;
                    HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
                    HeadercellStyle.FillPattern = FillPattern.SolidForeground;
                    HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;

                    //字体
                    NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                    headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    headerfont.FontHeightInPoints = 12;
                    HeadercellStyle.SetFont(headerfont);


                    //用column name 作为列名
                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    if (cols == null || (cols != null && cols.Length == 0))
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            ICell cell = headerRow.CreateCell(icolIndex);
                            cell.SetCellValue(dc.ColumnName);
                            cell.CellStyle = HeadercellStyle;
                            icolIndex++;
                        }
                    }
                    else
                    {
                        foreach (string dc in cols)
                        {
                            ICell cell = headerRow.CreateCell(icolIndex);
                            cell.SetCellValue(dc);
                            cell.CellStyle = HeadercellStyle;
                            icolIndex++;
                        }
                    }
                    ICellStyle cellStyle = workbook.CreateCellStyle();

                    //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                    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;


                    NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                    cellfont.Boldweight = (short)FontBoldWeight.Normal;
                    cellStyle.SetFont(cellfont);

                    //建立内容行
                    int iRowIndex = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        int iCellIndex = 0;
                        IRow irow = sheet.CreateRow(iRowIndex + 1);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            string strsj = string.Empty;
                            if (dr[i] != null)
                            {
                                strsj = dr[i].ToString();
                            }
                            ICell cell = irow.CreateCell(iCellIndex);
                            cell.SetCellValue(strsj);
                            cell.CellStyle = cellStyle;
                            iCellIndex++;
                        }
                        iRowIndex++;
                    }

                    //自适应列宽度
                    for (int i = 0; i < icolIndex; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }

                    using (MemoryStream ms = new MemoryStream())
                    {
                        workbook.Write(ms);

                        HttpContext curContext = HttpContext.Current;


                        // 设置编码和附件格式
                        curContext.Response.ContentType = "application/vnd.ms-excel";
                        curContext.Response.ContentEncoding = Encoding.UTF8;
                        curContext.Response.Charset = "";
                        curContext.Response.AppendHeader("Content-Disposition",
                            "attachment;filename=" + HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));

                        curContext.Response.BinaryWrite(ms.GetBuffer());

                        workbook = null;
                        ms.Close();
                        ms.Dispose();

                        curContext.Response.End();
                    }
                //}
                return "";
            }
            catch
            {
                return "导出失败！";
            }
        }

        /// <summary>
        /// 导入excel转换为datatable
        /// </summary>
        /// <param name="upfile"></param>
        /// <param name="headrow"></param>
        /// <returns></returns>
        public DataTable ExcelToTable(HttpPostedFile upfile, int headrow)
        {
            DataTable dt = new DataTable();

            IWorkbook workbook = null;

            Stream stream = upfile.InputStream;

            string suffix = upfile.FileName.Substring(upfile.FileName.LastIndexOf(".") + 1).ToLower();
            if (suffix == "xlsx") // 2007版本
            {
                workbook = new XSSFWorkbook(stream);
            }
            else if (suffix == "xls") // 2003版本
            {
                workbook = new HSSFWorkbook(stream);
            }

            //获取excel的第一个sheet
            ISheet sheet = workbook.GetSheetAt(0);

            //获取sheet的第一行
            IRow headerRow = sheet.GetRow(headrow);

            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.LastCellNum;
            //最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;
            //列名
            for (int i = 0; i < cellCount; i++)
            {
                dt.Columns.Add(headerRow.GetCell(i).ToString());
            }

            for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();

                IRow row = sheet.GetRow(i);
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dr[j] = row.GetCell(j).ToString();
                    }
                }

                dt.Rows.Add(dr);
            }

            sheet = null;
            workbook = null;

            return dt;
        }

        /// <summary>
        /// 导入excel转换为datatable
        /// </summary>
        /// <param name="upfile"></param>
        /// <param name="headrow"></param>
        /// <returns></returns>
        public DataTable ExcelToTable(HttpPostedFileBase upfile, int headrow)
        {
            DataTable dt = new DataTable();

            IWorkbook workbook = null;

            Stream stream = upfile.InputStream;

            string suffix = upfile.FileName.Substring(upfile.FileName.LastIndexOf(".") + 1).ToLower();
            if (suffix == "xlsx") // 2007版本
            {
                workbook = new XSSFWorkbook(stream);
            }
            else if (suffix == "xls") // 2003版本
            {
                workbook = new HSSFWorkbook(stream);
            }

            //获取excel的第一个sheet
            ISheet sheet = workbook.GetSheetAt(0);

            //获取sheet的第一行
            IRow headerRow = sheet.GetRow(headrow);

            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.LastCellNum;
            //最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;
            //列名
            for (int i = 0; i < cellCount; i++)
            {
                dt.Columns.Add(headerRow.GetCell(i).ToString());
            }

            for (int i = (sheet.FirstRowNum + headrow + 1); i <= sheet.LastRowNum; i++)
            {
                DataRow dr = dt.NewRow();

                IRow row = sheet.GetRow(i);
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dr[j] = row.GetCell(j).ToString().Trim(' ').Trim('\t');
                    }
                }

                dt.Rows.Add(dr);
            }

            sheet = null;
            workbook = null;

            return dt;
        }

        /// <summary>
        /// 弹出下载框导出excel
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string TSExportToExcel(DataTable dt, int tscount)
        {
            try
            {
                //if (dt.Rows.Count > 0)
                //{
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet("Sheet1");

                    ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.Alignment = HorizontalAlignment.Center;
                    HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
                    HeadercellStyle.FillPattern = FillPattern.SolidForeground;
                    HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;

                    //字体
                    NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                    headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    headerfont.FontHeightInPoints = 12;
                    HeadercellStyle.SetFont(headerfont);


                    //用column name 作为列名
                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    foreach (DataColumn dc in dt.Columns)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(dc.ColumnName);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }
                    ICellStyle cellStyle = workbook.CreateCellStyle();

                    //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                    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;


                    NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                    cellfont.Boldweight = (short)FontBoldWeight.Normal;
                    cellStyle.SetFont(cellfont);

                    //建立内容行
                    int iRowIndex = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        int iCellIndex = 0;
                        IRow irow = sheet.CreateRow(iRowIndex + 1);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            string strsj = string.Empty;
                            if (dr[i] != null)
                            {
                                strsj = dr[i].ToString();
                            }
                            ICell cell = irow.CreateCell(iCellIndex);
                            cell.SetCellValue(strsj);
                            cell.CellStyle = cellStyle;
                            iCellIndex++;
                        }
                        iRowIndex++;
                    }

                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
                    sheet.AddMergedRegion(new CellRangeAddress(1, tscount, 0, 0));
                    for (int i = iRowIndex; i > tscount; i--)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(i, i, 0, 1));
                    }

                    //自适应列宽度
                    for (int i = 0; i < icolIndex; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }

                    using (MemoryStream ms = new MemoryStream())
                    {
                        workbook.Write(ms);

                        HttpContext curContext = HttpContext.Current;


                        // 设置编码和附件格式
                        curContext.Response.ContentType = "application/vnd.ms-excel";
                        curContext.Response.ContentEncoding = Encoding.UTF8;
                        curContext.Response.Charset = "";
                        curContext.Response.AppendHeader("Content-Disposition",
                            "attachment;filename=" + HttpUtility.UrlEncode("投诉统计_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));

                        curContext.Response.BinaryWrite(ms.GetBuffer());

                        workbook = null;
                        ms.Close();
                        ms.Dispose();

                        curContext.Response.End();
                    }
                //}
                return "";
            }
            catch
            {
                return "导出失败！";
            }
        }

        /// <summary>
        /// 生成excel到路径
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string CreateExcelFile(string Name, DataTable dt, string Path,string[] cols = null)
        {
            try
            {
                if (dt.Rows.Count > 0)
                {
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet(Name);

                    ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.Alignment = HorizontalAlignment.Center;
                    HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
                    HeadercellStyle.FillPattern = FillPattern.SolidForeground;
                    HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;

                    //字体
                    NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                    headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    headerfont.FontHeightInPoints = 12;
                    HeadercellStyle.SetFont(headerfont);


                    //用column name 作为列名
                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    if (cols == null || (cols != null && cols.Length == 0))
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            ICell cell = headerRow.CreateCell(icolIndex);
                            cell.SetCellValue(dc.ColumnName);
                            cell.CellStyle = HeadercellStyle;
                            icolIndex++;
                        }
                    }
                    else
                    {
                        foreach (string dc in cols)
                        {
                            ICell cell = headerRow.CreateCell(icolIndex);
                            cell.SetCellValue(dc);
                            cell.CellStyle = HeadercellStyle;
                            icolIndex++;
                        }
                    }
                    ICellStyle cellStyle = workbook.CreateCellStyle();

                    //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                    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;


                    NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                    cellfont.Boldweight = (short)FontBoldWeight.Normal;
                    cellStyle.SetFont(cellfont);

                    //建立内容行
                    int iRowIndex = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        int iCellIndex = 0;
                        IRow irow = sheet.CreateRow(iRowIndex + 1);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            string strsj = string.Empty;
                            if (dr[i] != null)
                            {
                                strsj = dr[i].ToString();
                            }
                            ICell cell = irow.CreateCell(iCellIndex);
                            cell.SetCellValue(strsj);
                            cell.CellStyle = cellStyle;
                            iCellIndex++;
                        }
                        iRowIndex++;
                    }

                    //自适应列宽度
                    for (int i = 0; i < icolIndex; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                    Name = HttpUtility.UrlEncode(Name + "_导出文件_" + DateTime.Now.Ticks + ".xls");
                    Path = HttpContext.Current.Server.MapPath("..") + Path;
                    if (!Directory.Exists(Path)) Directory.CreateDirectory(Path);
                    FileStream fileHSSF = new FileStream(Path+ Name, FileMode.Create);
                    workbook.Write(fileHSSF);
                    fileHSSF.Close();
                    fileHSSF.Dispose();
                    workbook = null;
                }
                return "";
            }
            catch
            {
                return "生成失败！";
            }
        }

        /// <summary>
        /// 弹出下载框导出excel
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string ExportToExcel64(string name, string base64url)
        {
            try
            {
                int delLength = base64url.IndexOf(',') + 1;
                string str = base64url.Substring(delLength, base64url.Length - delLength);
                byte[] bData = Convert.FromBase64String(str);

                HttpContext curContext = HttpContext.Current;

                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "";
                curContext.Response.AppendHeader("Content-Disposition",
                    "attachment;filename=" + HttpUtility.UrlEncode(name + "_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));

                curContext.Response.BinaryWrite(bData);

                curContext.Response.End();

                return "";
            }
            catch
            {
                return "导出失败！";
            }
        }

        /// <summary>
        /// 简报导出
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string SimpleExportToExcel(DataSet ds)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");
                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                //cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                //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;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                IRow irow1 = sheet.CreateRow(1);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("时间："+ ds.Tables[5].Rows[0]["sdate"].ToString()+ " 至 "+ ds.Tables[5].Rows[0]["edate"].ToString());
                cell1.CellStyle = cellStyle;

                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));

                #region 话务受理情况
                DataTable dt1 = ds.Tables[0];

                IRow irow2 = sheet.CreateRow(2);
                ICell cell2 = irow2.CreateCell(0);
                cell2.SetCellValue("话务受理情况");
                cell2.CellStyle = cellStyle;

                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 7));

                IRow irow3 = sheet.CreateRow(3);
                ICell cell31 = irow3.CreateCell(0);
                cell31.SetCellValue("来电");
                cell31.CellStyle = cellStyle;
                ICell cell32 = irow3.CreateCell(1);
                cell32.SetCellValue(Int32.Parse(dt1.Rows[0]["ldcount"].ToString()));
                cell32.CellStyle = cellStyle;
                ICell cell33 = irow3.CreateCell(2);
                cell33.SetCellValue("接听");
                cell33.CellStyle = cellStyle;
                ICell cell34 = irow3.CreateCell(3);
                cell34.SetCellValue(Int32.Parse(dt1.Rows[0]["jtcount"].ToString()));
                cell34.CellStyle = cellStyle;
                ICell cell35 = irow3.CreateCell(4);
                cell35.SetCellValue("有效接听");
                cell35.CellStyle = cellStyle;
                ICell cell36 = irow3.CreateCell(5);
                cell36.SetCellValue(Int32.Parse(dt1.Rows[0]["yxcount"].ToString()));
                cell36.CellStyle = cellStyle;
                #endregion

                #region 工单受理情况
                DataTable dt2 = ds.Tables[1];

                IRow irow4 = sheet.CreateRow(4);
                ICell cell4 = irow4.CreateCell(0);
                cell4.SetCellValue("工单受理情况");
                cell4.CellStyle = cellStyle;

                sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 7));

                IRow irow5 = sheet.CreateRow(5);
                ICell cell51 = irow5.CreateCell(0);
                cell51.SetCellValue("受理");
                cell51.CellStyle = cellStyle;
                ICell cell52 = irow5.CreateCell(1);
                cell52.SetCellValue(Int32.Parse(dt2.Rows[0]["slcount"].ToString()));
                cell52.CellStyle = cellStyle;
                ICell cell53 = irow5.CreateCell(2);
                cell53.SetCellValue("待提交");
                cell53.CellStyle = cellStyle;
                ICell cell54 = irow5.CreateCell(3);
                cell54.SetCellValue(Int32.Parse(dt2.Rows[0]["dtjcount"].ToString()));
                cell54.CellStyle = cellStyle;
                ICell cell55 = irow5.CreateCell(4);
                cell55.SetCellValue("待交办");
                cell55.CellStyle = cellStyle;
                ICell cell56 = irow5.CreateCell(5);
                cell56.SetCellValue(Int32.Parse(dt2.Rows[0]["djbcount"].ToString()));
                cell56.CellStyle = cellStyle;
                ICell cell57 = irow5.CreateCell(6);
                cell57.SetCellValue("待查收");
                cell57.CellStyle = cellStyle;
                ICell cell58 = irow5.CreateCell(7);
                cell58.SetCellValue(Int32.Parse(dt2.Rows[0]["dcscount"].ToString()));
                cell58.CellStyle = cellStyle;

                IRow irow6 = sheet.CreateRow(6);
                ICell cell61 = irow6.CreateCell(0);
                cell61.SetCellValue("待审核退回");
                cell61.CellStyle = cellStyle;
                ICell cell62 = irow6.CreateCell(1);
                cell62.SetCellValue(Int32.Parse(dt2.Rows[0]["dshthcount"].ToString()));
                cell62.CellStyle = cellStyle;
                ICell cell63 = irow6.CreateCell(2);
                cell63.SetCellValue("待办理");
                cell63.CellStyle = cellStyle;
                ICell cell64 = irow6.CreateCell(3);
                cell64.SetCellValue(Int32.Parse(dt2.Rows[0]["dblcount"].ToString()));
                cell64.CellStyle = cellStyle;
                ICell cell65 = irow6.CreateCell(4);
                cell65.SetCellValue("待延时审核");
                cell65.CellStyle = cellStyle;
                ICell cell66 = irow6.CreateCell(5);
                cell66.SetCellValue(Int32.Parse(dt2.Rows[0]["dshyscount"].ToString()));
                cell66.CellStyle = cellStyle;
                ICell cell67 = irow6.CreateCell(6);
                cell67.SetCellValue("待回访");
                cell67.CellStyle = cellStyle;
                ICell cell68 = irow6.CreateCell(7);
                cell68.SetCellValue(Int32.Parse(dt2.Rows[0]["dhfcount"].ToString()));
                cell68.CellStyle = cellStyle;

                IRow irow7 = sheet.CreateRow(7);
                ICell cell71 = irow7.CreateCell(0);
                cell71.SetCellValue("待结案");
                cell71.CellStyle = cellStyle;
                ICell cell72 = irow7.CreateCell(1);
                cell72.SetCellValue(Int32.Parse(dt2.Rows[0]["dwjcount"].ToString()));
                cell72.CellStyle = cellStyle;
                ICell cell73 = irow7.CreateCell(2);
                cell73.SetCellValue("待重办");
                cell73.CellStyle = cellStyle;
                ICell cell74 = irow7.CreateCell(3);
                cell74.SetCellValue(Int32.Parse(dt2.Rows[0]["dcbcount"].ToString()));
                cell74.CellStyle = cellStyle;
                ICell cell75 = irow7.CreateCell(4);
                cell75.SetCellValue("已结案");
                cell75.CellStyle = cellStyle;
                ICell cell76 = irow7.CreateCell(5);
                cell76.SetCellValue(Int32.Parse(dt2.Rows[0]["ywjcount"].ToString()));
                cell76.CellStyle = cellStyle;
                ICell cell77 = irow7.CreateCell(6);
                cell77.SetCellValue("在线办理");
                cell77.CellStyle = cellStyle;
                ICell cell78 = irow7.CreateCell(7);
                cell78.SetCellValue(Int32.Parse(dt2.Rows[0]["zxbjcount"].ToString()));
                cell78.CellStyle = cellStyle;

                IRow irow8 = sheet.CreateRow(8);
                ICell cell81 = irow8.CreateCell(0);
                cell81.SetCellValue("中心转派");
                cell81.CellStyle = cellStyle;
                ICell cell82 = irow8.CreateCell(1);
                cell82.SetCellValue(Int32.Parse(dt2.Rows[0]["zxzpcount"].ToString()));
                cell82.CellStyle = cellStyle;

                #endregion

                #region 政府热线受理情况
                DataTable dt3 = ds.Tables[2];

                IRow irow9 = sheet.CreateRow(9);
                ICell cell9 = irow9.CreateCell(0);
                cell9.SetCellValue("政府热线受理情况");
                cell9.CellStyle = cellStyle;

                sheet.AddMergedRegion(new CellRangeAddress(9, 9, 0, 7));

                IRow irow10 = sheet.CreateRow(10);
                IRow irow11 = sheet.CreateRow(11);
                int n = 0;
                foreach (DataRow dr3 in dt3.Rows)
                {
                    if (n < 4)
                    {
                        ICell cell101 = irow10.CreateCell(2 * (n + 1) - 2);
                        cell101.SetCellValue(dr3["source"].ToString());
                        cell101.CellStyle = cellStyle;
                        ICell cell102 = irow10.CreateCell(2 * (n + 1) - 1);
                        cell102.SetCellValue(Int32.Parse(dr3["count"].ToString()));
                        cell102.CellStyle = cellStyle;
                    }
                    else
                    {
                        ICell cell111 = irow11.CreateCell(2 * (n -3) - 2);
                        cell111.SetCellValue(dr3["source"].ToString());
                        cell111.CellStyle = cellStyle;
                        ICell cell112 = irow11.CreateCell(2 * (n - 3) - 1);
                        cell112.SetCellValue(Int32.Parse(dr3["count"].ToString()));
                        cell112.CellStyle = cellStyle;
                    }
                    n = n + 1;
                }

                #endregion

                #region 工单受理类型情况
                DataTable dt4 = ds.Tables[3];

                IRow irow12 = sheet.CreateRow(12);
                ICell cell12 = irow12.CreateCell(0);
                cell12.SetCellValue("工单受理类型情况");
                cell12.CellStyle = cellStyle;

                sheet.AddMergedRegion(new CellRangeAddress(12, 12, 0, 7));

                IRow irow13 = sheet.CreateRow(13);
                IRow irow14 = sheet.CreateRow(14);
                int m = 0;
                foreach (DataRow dr4 in dt4.Rows)
                {
                    if (m < 4)
                    {
                        ICell cell131 = irow13.CreateCell(2 * (m + 1) - 2);
                        cell131.SetCellValue(dr4["type"].ToString());
                        cell131.CellStyle = cellStyle;
                        ICell cell132 = irow13.CreateCell(2 * (m + 1) - 1);
                        cell132.SetCellValue(Int32.Parse(dr4["count"].ToString()));
                        cell132.CellStyle = cellStyle;
                    }
                    else
                    {
                        ICell cell141 = irow14.CreateCell(2 * (m - 3) - 2);
                        cell141.SetCellValue(dr4["type"].ToString());
                        cell141.CellStyle = cellStyle;
                        ICell cell152 = irow14.CreateCell(2 * (m - 3) - 1);
                        cell152.SetCellValue(Int32.Parse(dr4["count"].ToString()));
                        cell152.CellStyle = cellStyle;
                    }
                    m = m + 1;
                }

                #endregion

                #region 工单事发区域情况
                DataTable dt5 = ds.Tables[4];

                IRow irow15 = sheet.CreateRow(15);
                ICell cell15 = irow15.CreateCell(0);
                cell15.SetCellValue("工单事发区域情况");
                cell15.CellStyle = cellStyle;

                sheet.AddMergedRegion(new CellRangeAddress(15, 15, 0, 7));

                int rowcount = (dt5.Rows.Count / 4) + 1;

                for (int i = 0; i < rowcount; i++)
                {
                    IRow irow = sheet.CreateRow(16 + i);
                    for (int j = 0; j < 4; j++)
                    {
                        int num = i * 4 + j;
                        if (num != dt5.Rows.Count)
                        {
                            var dr = dt5.Rows[num];
                            ICell cellname = irow.CreateCell(2 * (j + 1) - 2);
                            cellname.SetCellValue(dr["areaname"].ToString());
                            cellname.CellStyle = cellStyle;
                            ICell cellcount = irow.CreateCell(2 * (j + 1) - 1);
                            cellcount.SetCellValue(Int32.Parse(dr["count"].ToString()));
                            cellcount.CellStyle = cellStyle;
                        }
                        else
                        {
                            break;
                        }
                    }
                }

                #endregion

                //自适应列宽度
                for (int i = 0; i < 8; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("业务简报_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));

                    curContext.Response.BinaryWrite(ms.GetBuffer());

                    workbook = null;
                    ms.Close();
                    ms.Dispose();

                    curContext.Response.End();
                }
                return "";
            }
            catch
            {
                return "导出失败！";
            }
        }

    }
}