Anyang_api/CallCenterCo.../CallCenter.U.../NPOI/NPOIHelper.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Wordprocessing;
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>
        /// 投诉产品日期分布表
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string SatisfiedToExcel(string Name, IOrderedEnumerable<deptSatisfiedReport>   deptSatisfied, string[] cols = null)
        {
            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 (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;
                string strsj = "";
                foreach (var  dr in deptSatisfied )
                {
                    IRow irow = sheet.CreateRow(iRowIndex + 1);
                    for (int i = 0; i <5   ; i++)
                    {
                      
                        switch (i )
                        {
                            case 0:
                                  strsj = dr.deptname;
                                ICell cell = irow.CreateCell(i);
                                cell.SetCellValue(strsj);
                                cell.CellStyle = cellStyle;
                                break;
                            case 1:
                                 strsj = dr.count.ToString ();
                                ICell cell1 = irow.CreateCell(i);
                                cell1.SetCellValue(strsj);
                                cell1.CellStyle = cellStyle;
                                break;
                            case 2:
                                strsj = dr.satisfiedcount.ToString();
                                ICell cell2 = irow.CreateCell(i);
                                cell2.SetCellValue(strsj);
                                cell2.CellStyle = cellStyle;
                                break;
                            case 3:
                                 strsj = dr.notsatisfiedcount.ToString();
                                ICell cell3 = irow.CreateCell(i);
                                cell3.SetCellValue(strsj);
                                cell3.CellStyle = cellStyle;
                                break;
                            case 4:
                                 strsj = dr.satisfiedrate;
                                ICell cell4 = irow.CreateCell(i);
                                cell4.SetCellValue(strsj);
                                cell4.CellStyle = cellStyle;
                                break;
                        }
                    }
                    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("Access-Control-Expose-Headers", "Content-Disposition");
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode(Name + ".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 ExportToExcel(string Name, DataTable dt, string[] cols = null, int issort = 0)
        {
            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 (issort == 1)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue("序号");
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

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

                    if (issort == 1)
                    {
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(iRowIndex + 1);
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    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("Access-Control-Expose-Headers", "Content-Disposition");
                    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;
        }
        private string GetCellValue(DataRow dr,int index,int i   )
        {
            string str = "";
            switch (i)
            {
                case 0:
                    str = index.ToString();
                    break;
                case 1:
                    str = dr["deptname"].ToString();
                    break;
                case 2:
                    str = dr["Undertakers"].ToString();
                    break;
                case 3:
                    str = dr["Undertakersrate"].ToString();
                    break;
                case 4:
                    str = dr["Undertakersscore"].ToString();
                    break;
                case 5:
                    str = dr["overdue"].ToString();
                    break;
                case 6:
                    str = dr["overduerate"].ToString();
                    break;
                case 7:
                    str = dr["overduescore"].ToString();
                    break;
                case 8:
                    str = dr["unsuccessful"].ToString();
                    break;
                case 9:
                    str = dr["unsuccessfulrate"].ToString();
                    break;
                case 10:
                    str = dr["unsuccessfulscore"].ToString();
                    break;
                case 11:
                    str = dr["Chargeback"].ToString();
                    break;
                case 12:
                    str = dr["Chargebackrate"].ToString();
                    break;
                case 13:
                    str = dr["Chargebackscore"].ToString();
                    break;
                case 14:
                    str = dr["Comment"].ToString();
                    break;
                case 15:
                    str = dr["Oncedissatisfied"].ToString();
                    break;
                case 16:
                    str = dr["dissatisfied"].ToString();
                    break;
                case 17:
                    str = dr["satisfiedrate"].ToString();
                    break;
                case 18:
                    str = dr["satisfiedscore"].ToString();
                    break;
                case 19:
                    str = dr["total"].ToString();
                    break;
                case 20:
                    str = index.ToString();
                    break;

            }
            return str;
        }
        public string ExcelMergerHotline(List <MergerHot.MergerHotSource> mergerHotSources 
        )
        {
            return "";
        }
        /// <summary>
        /// 督办数据报表导出
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string DBExportToExcel(DataTable dt,string Name,string month,string starttime ,string endtime )
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet(Name);
                ICellStyle cellStyle = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("12345联动服务工作"+ month + "月份办理情况通报表");
                cell1.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 19));
                IRow irow2 = sheet.CreateRow(2);
                ICell cell2 = irow2.CreateCell(0);
                cell2.SetCellValue("统计周期："+ starttime+"至"+ endtime+"                统计时间："+DateTime .Now.ToString ("yyyy年MM月dd日"));
                cell2.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 19));
                IRow irow3 = sheet.CreateRow(3);
                ICell cell3 = irow3.CreateCell(0);
                cell3.SetCellValue("一、县（市、区）联动单位");
                cell3.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 19));
                string[] cols = {"序号","联动单位","承办件",
                "承办率","得分(5分)","超期件","按时反馈率","得分(20分)",
                "未果件","办结率","得分(10分)","退单件","有效回复率",
                "得分(15分)","群众评议总数","一次不满意件","不满意件","满意率","得分(50分)","总分","排名"};
                IRow irow4 = sheet.CreateRow(4);
                int icolIndex = 0;
                foreach (string dc in cols)
                {
                    ICell cell = irow4.CreateCell(icolIndex);
                    cell.SetCellValue(dc);
                    cell.CellStyle = cellStylebt;
                    icolIndex++;
                }
                int iRowIndex = 5;
                DataRow[] rows = dt.Select("category=1");
                int index = 0;
                foreach (DataRow dr in rows)
                {
                    index++;
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex );
                    for (int i = 0; i < 21; i++)
                    {
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(GetCellValue(dr, index,i ));
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }
               
                IRow irow5 = sheet.CreateRow(iRowIndex);
                ICell cell5 = irow5.CreateCell(0);
                cell5.SetCellValue("二、市直机关联动单位一组");
                cell5.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0, 19));
                iRowIndex++;
                IRow irow6 = sheet.CreateRow(iRowIndex);
                 icolIndex = 0;
                foreach (string dc in cols)
                {
                    ICell cell = irow6.CreateCell(icolIndex);
                    cell.SetCellValue(dc);
                    cell.CellStyle = cellStylebt;
                    icolIndex++;
                }
                 iRowIndex++;
                 rows = dt.Select("category=2");
                 index = 0;
                foreach (DataRow dr in rows)
                {
                    index++;
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex);
                    for (int i = 0; i < 21; i++)
                    {
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(GetCellValue(dr, index,i ));
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }
                IRow irow7 = sheet.CreateRow(iRowIndex);
                ICell cell7 = irow7.CreateCell(0);
                cell7.SetCellValue("三、市直机关联动单位二组");
                cell7.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0, 19));
                iRowIndex++;
                IRow irow8 = sheet.CreateRow(iRowIndex);
                icolIndex = 0;
                foreach (string dc in cols)
                {
                    ICell cell = irow8.CreateCell(icolIndex);
                    cell.SetCellValue(dc);
                    cell.CellStyle = cellStylebt;
                    icolIndex++;
                }
                iRowIndex++;
                rows = dt.Select("category=3");
                index = 0;
                foreach (DataRow dr in rows)
                {
                    index++;
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex);
                    for (int i = 0; i < 21; i++)
                    {
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(GetCellValue(dr, index,i ));
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }
                IRow irow9 = sheet.CreateRow(iRowIndex);
                ICell cell9 = irow9.CreateCell(0);
                cell9.SetCellValue("四、公益型企业联动单位");
                cell9.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0, 19));
                iRowIndex++;
                IRow irow10 = sheet.CreateRow(iRowIndex);
                icolIndex = 0;
                foreach (string dc in cols)
                {
                    ICell cell = irow10.CreateCell(icolIndex);
                    cell.SetCellValue(dc);
                    cell.CellStyle = cellStylebt;
                    icolIndex++;
                }
                iRowIndex++;
                rows = dt.Select("category=4");
                index = 0;
                foreach (DataRow dr in rows)
                {
                    index++;
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex);
                    for (int i = 0; i < 21; i++)
                    {
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(GetCellValue(dr, index,i ));
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }
                //自适应列宽度
                for (int i = 0; i < 20; i++)
                {
                     sheet.AutoSizeColumn(i);
                   // sheet.SetColumnWidth(i, 20 * 256);
                }

                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("Access-Control-Expose-Headers", "Content-Disposition");
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));

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

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

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

        /// <summary>
        /// 督办数据报表导出
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string DBEJExportToExcel(DataTable dt, string Name, string month, string starttime, string endtime)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet(Name);
                ICellStyle cellStyle = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("12345联动服务工作" + month + "月份办理情况通报表");
                cell1.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 19));
                IRow irow2 = sheet.CreateRow(2);
                ICell cell2 = irow2.CreateCell(0);
                cell2.SetCellValue("统计周期：" + starttime + "至" + endtime + "                统计时间：" + DateTime.Now.ToString("yyyy年MM月dd日"));
                cell2.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 19));
                IRow irow3 = sheet.CreateRow(3);
                ICell cell3 = irow3.CreateCell(0);
                cell3.SetCellValue("一、县（市、区）联动单位");
                cell3.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 19));
                string[] cols = {"序号","联动单位","承办件",
                "承办率","得分(5分)","超期件","按时反馈率","得分(20分)",
                "未果件","办结率","得分(10分)","退单件","有效回复率",
                "得分(15分)","群众评议总数","一次不满意件","不满意件","满意率","得分(50分)","总分","排名"};
                IRow irow4 = sheet.CreateRow(4);
                int icolIndex = 0;
                foreach (string dc in cols)
                {
                    ICell cell = irow4.CreateCell(icolIndex);
                    cell.SetCellValue(dc);
                    cell.CellStyle = cellStylebt;
                    icolIndex++;
                }
                int iRowIndex = 5;
               // DataRow[] rows = dt.Select("category=1");
                int index = 0;
                foreach (DataRow dr in dt.Rows )
                {
                    index++;
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex);
                    for (int i = 0; i < 21; i++)
                    {
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(GetCellValue(dr, index, i));
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }
                //自适应列宽度
                for (int i = 0; i < 20; i++)
                {
                    sheet.AutoSizeColumn(i);
                    // sheet.SetColumnWidth(i, 20 * 256);
                }

                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("Access-Control-Expose-Headers", "Content-Disposition");
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));

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

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

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



       
        //
       
        /// <summary>
        /// 督办数据报表导出
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string ExportMergerToExcel(List<MergerHot.MergerHotSource> MergerHotSource)
        {
            try
            {
                int lenth = 0;
                if (MergerHotSource!=null&& MergerHotSource.Count>0)
                {
                   if (MergerHotSource[0]!=null && MergerHotSource[0].MergerHot !=null
                        && MergerHotSource[0].MergerHot.Count >0)
                    {
                        lenth = MergerHotSource[0].MergerHot.Count * 5+4;
                    }
                    else
                    {
                        return "";
                    }
                }
                else
                {
                    return "";
                }

                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("归并热线每日数据统计");
                ICellStyle cellStyle = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;
                cellStylebt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("归并热线每日数据统计");
                cell1.CellStyle=cellStylebt;
                 ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
                cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeightInPoints = 22;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                cell1.CellStyle = cellStylebt1;
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, lenth));
                IRow irow2 = sheet.CreateRow(3);
                int icolIndex = 0;
                ICellStyle cellStylebt2 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt2 = workbook.CreateFont();
                cellfontbt2.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt2.WrapText = true;//设置换行这个要先设置
                cellfontbt2.FontHeightInPoints = 14;
                cellStylebt2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt2.SetFont(cellfontbt2);
                cellStylebt2.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt2.Alignment = HorizontalAlignment.Center;
                HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例
                palette.SetColorAtIndex(8, 237,237, 237);
                NPOI.HSSF.Util.HSSFColor hssFColor = palette.FindColor(237, 237, 237);
                cellStylebt2.FillForegroundColor = hssFColor.Indexed ;
                cellStylebt2.FillPattern = FillPattern.SolidForeground;
                cellStylebt2.FillBackgroundColor = hssFColor.Indexed; ;

                ICellStyle cellStylebt3 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
                cellfontbt3.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt3.WrapText = true;//设置换行这个要先设置
                cellfontbt3.FontHeightInPoints = 14;
                cellStylebt3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt3.SetFont(cellfontbt3);
                cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                HSSFPalette palette1 = workbook.GetCustomPalette(); //调色板实例
                palette1.SetColorAtIndex(9, 221,235,247);
                NPOI.HSSF.Util.HSSFColor hssFColor1 = palette1.FindColor(221, 235, 247);
                cellStylebt3.FillForegroundColor = hssFColor1.Indexed ;
                cellStylebt3.FillPattern = FillPattern.SolidForeground;
                cellStylebt3.FillBackgroundColor = hssFColor1.Indexed;


                ICellStyle cellStylebt4 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt4 = workbook.CreateFont();
                cellStylebt4.WrapText = true;//设置换行这个要先设置
                cellfontbt4.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt4.FontHeightInPoints = 11;
                cellStylebt4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt4.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt4.SetFont(cellfontbt4);
                cellStylebt4.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt4.Alignment = HorizontalAlignment.Center;


                ICellStyle cellStylebt5 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt5 = workbook.CreateFont();
                cellfontbt5.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt5.FontHeightInPoints = 11;
                cellStylebt5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt5.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt5.WrapText = true;//设置换行这个要先设置
                cellStylebt5.SetFont(cellfontbt5);
                cellStylebt5.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt5.Alignment = HorizontalAlignment.Center;
                HSSFPalette palette2 = workbook.GetCustomPalette(); //调色板实例
                palette2.SetColorAtIndex(10, 226, 239, 218);
                NPOI.HSSF.Util.HSSFColor hssFColor2 = palette2.FindColor(226, 239, 218);
                cellStylebt5.FillForegroundColor = hssFColor2.Indexed ;
                cellStylebt5.FillPattern = FillPattern.SolidForeground;
                cellStylebt5.FillBackgroundColor = hssFColor2.Indexed;


                ICellStyle cellStylebt8 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt8 = workbook.CreateFont();
                cellfontbt8.FontHeightInPoints = 10;
                cellStylebt8.SetFont(cellfontbt8);
                cellStylebt8.WrapText = true;//设置换行这个要先设置
                cellStylebt8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt8.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt8.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt8.Alignment = HorizontalAlignment.Center;
                HSSFPalette palette3 = workbook.GetCustomPalette(); //调色板实例
                palette3.SetColorAtIndex(11, 198, 224, 180);
                NPOI.HSSF.Util.HSSFColor hssFColor3 = palette3.FindColor(198, 224, 180);
                cellStylebt8.FillForegroundColor = hssFColor3.Indexed ;
                cellStylebt8.FillPattern = FillPattern.SolidForeground;
                cellStylebt8.FillBackgroundColor = hssFColor3.Indexed;


                ICellStyle cellStylebt9 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt9 = workbook.CreateFont();
                cellfontbt9.FontHeightInPoints = 11;
                cellfontbt9.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt9.SetFont(cellfontbt9);
                cellStylebt9.WrapText = true;//设置换行这个要先设置
                cellStylebt9.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt9.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt9.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt9.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt9.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt9.Alignment = HorizontalAlignment.Center;
                HSSFPalette palette4 = workbook.GetCustomPalette(); //调色板实例
                palette4.SetColorAtIndex(12, 255, 242, 204);
                NPOI.HSSF.Util.HSSFColor hssFColor4 = palette4.FindColor(255, 242, 204);
                cellStylebt9.FillForegroundColor = hssFColor4.Indexed ; ;
                cellStylebt9.FillPattern = FillPattern.SolidForeground;
                cellStylebt9.FillBackgroundColor = hssFColor4.Indexed; ; ;

                ICellStyle cellStylebt10 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt10 = workbook.CreateFont();
                cellfontbt10.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt10.FontHeightInPoints = 11;
                cellStylebt10.WrapText = true;//设置换行这个要先设置
                cellStylebt10.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt10.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt10.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt10.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt10.SetFont(cellfontbt10);
                cellStylebt10.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt10.Alignment = HorizontalAlignment.Center;
                HSSFPalette palette5 = workbook.GetCustomPalette(); //调色板实例
                palette5.SetColorAtIndex(13, 217, 225, 242);
                NPOI.HSSF.Util.HSSFColor hssFColor5 = palette5.FindColor(217, 225, 242);
                cellStylebt10.FillForegroundColor = hssFColor5.Indexed ;
                cellStylebt10.FillPattern = FillPattern.SolidForeground;
                cellStylebt10.FillBackgroundColor = hssFColor5.Indexed; ;

                ICellStyle cellStylebt6 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt6 = workbook.CreateFont();
                cellfontbt6.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt6.WrapText = true;//设置换行这个要先设置
                cellfontbt6.FontHeightInPoints = 10;
                cellStylebt6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt6.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt6.SetFont(cellfontbt6);
                cellStylebt6.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt6.Alignment = HorizontalAlignment.Center;
                cellStylebt6.FillForegroundColor = hssFColor.Indexed ;
                cellStylebt6.FillPattern = FillPattern.SolidForeground;
                cellStylebt6.FillBackgroundColor = hssFColor.Indexed; ;

                ICellStyle cellStylebt11 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt11 = workbook.CreateFont();
                cellfontbt11.FontHeightInPoints = 10;
                cellStylebt11.WrapText = true;//设置换行这个要先设置
                cellStylebt11.SetFont(cellfontbt11);
                cellStylebt11.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt11.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt11.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt11.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt11.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt11.Alignment = HorizontalAlignment.Center;

                ICellStyle cellStylebt12 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt12 = workbook.CreateFont();
                cellfontbt12.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt12.FontHeightInPoints = 10;
                cellStylebt12.WrapText = true;//设置换行这个要先设置
                cellStylebt12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt12.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt12.SetFont(cellfontbt12);
                cellStylebt12.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt12.Alignment = HorizontalAlignment.Center;
                cellStylebt12.FillForegroundColor = hssFColor5.Indexed;
                cellStylebt12.FillPattern = FillPattern.SolidForeground;
                cellStylebt12.FillBackgroundColor = hssFColor5.Indexed; ;


                ICellStyle cellStylebt13 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt13 = workbook.CreateFont();
                cellfontbt13.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt13.WrapText = true;//设置换行这个要先设置
                cellfontbt13.FontHeightInPoints = 11;
                cellStylebt13.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt13.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt13.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt13.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt13.SetFont(cellfontbt13);
                cellStylebt13.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt13.Alignment = HorizontalAlignment.Center;
                cellStylebt13.FillForegroundColor = hssFColor.Indexed;
                cellStylebt13.FillPattern = FillPattern.SolidForeground;
                cellStylebt13.FillBackgroundColor = hssFColor.Indexed; ;



                ICellStyle cellStylebt14 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt14 = workbook.CreateFont();
                cellfontbt14.FontHeightInPoints = 10;
                cellStylebt14.SetFont(cellfontbt14);
                cellStylebt14.WrapText = true;//设置换行这个要先设置
                cellStylebt14.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt14.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt14.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt14.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt14.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt14.Alignment = HorizontalAlignment.Center;
                cellStylebt14.FillForegroundColor = hssFColor4.Indexed; ;
                cellStylebt14.FillPattern = FillPattern.SolidForeground;
                cellStylebt14.FillBackgroundColor = hssFColor4.Indexed; ; ;



                ICellStyle cellStylebt15 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt15 = workbook.CreateFont();
                cellfontbt15.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt15.FontHeightInPoints = 10;
                cellStylebt15.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt15.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt15.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt15.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt15.WrapText = true;//设置换行这个要先设置
                cellStylebt15.SetFont(cellfontbt15);
                cellStylebt15.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt15.Alignment = HorizontalAlignment.Center;
                cellStylebt15.FillForegroundColor = hssFColor2.Indexed;
                cellStylebt15.FillPattern = FillPattern.SolidForeground;
                cellStylebt15.FillBackgroundColor = hssFColor2.Indexed;

                int indexsource = -1; 
                for (int i = 0; i <= MergerHotSource[0].MergerHot.Count
                    ;i++)
                {
                   
                    if (i == 0)
                    {
                        ICell cell = irow2.CreateCell(0);
                        cell.SetCellValue("来源");
                        cell.CellStyle = cellStylebt2;
                        indexsource = 0;
                        sheet.SetColumnWidth(0, 12 * 300);
                    }
                    else if (i >0&&i <= MergerHotSource[0].MergerHot.Count-2)
                    {
                        ICell cell = irow2.CreateCell(indexsource+1);
                        cell.SetCellValue(MergerHotSource[0].MergerHot[i - 1].Source);
                        cell.CellStyle = cellStylebt2;
                        if (i ==1 )
                        {
                            if (MergerHotSource[0].MergerHot[i - 1].Source=="12345")
                            {
                                indexsource = 6;
                                for (int z=2;z <= indexsource - 1;z++)
                                {
                                    ICell cell4 = irow2.CreateCell(z);
                                    cell4.CellStyle = cellStylebt2;
                                }
                                sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, indexsource-1));
                            }
                            else
                            {
                                indexsource = 5;
                                for (int z = 2; z <= indexsource - 1; z++)
                                {
                                    ICell cell4 = irow2.CreateCell(z);
                                    cell4.CellStyle = cellStylebt2;
                                }
                                sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, indexsource-1));
                            }
                        }
                        else
                        {
                            if (MergerHotSource[0].MergerHot[i - 1].Source == "12345")
                            {
                                for (int z = indexsource + 2; z <= indexsource + 5 ; z++)
                                {
                                    ICell cell4 = irow2.CreateCell(z);
                                    cell4.CellStyle = cellStylebt2;
                                }
                                sheet.AddMergedRegion(new CellRangeAddress(3, 3, indexsource + 1, indexsource + 5));
                                indexsource = indexsource + 6;

                            }
                            else
                            {
                                for (int z = indexsource + 2; z <= indexsource + 4; z++)
                                {
                                    ICell cell4 = irow2.CreateCell(z);
                                    cell4.CellStyle = cellStylebt2;
                                }
                                sheet.AddMergedRegion(new CellRangeAddress(3, 3, indexsource + 1, indexsource + 4));
                                indexsource += 5;
                               
                            }
                        }
                       
                    }
                    else
                    {
                        ICell cell = irow2.CreateCell(indexsource + 1);
                        if (i == MergerHotSource[0].MergerHot.Count - 1)
                        {
                            cell.SetCellValue("当日小计");
                        }
                        else if (i == MergerHotSource[0].MergerHot.Count )
                        {
                            cell.SetCellValue("累计");
                        }
                        for (int z = indexsource + 2; z <= indexsource + 5; z++)
                        {
                            ICell cell4 = irow2.CreateCell(z);
                            cell4.CellStyle = cellStylebt2;
                        }
                        sheet.AddMergedRegion(new CellRangeAddress(3, 3, indexsource + 1, indexsource + 5));
                        cell.CellStyle = cellStylebt3;
                        indexsource += 6;
                    }
                }
                IRow irow3 = sheet.CreateRow(4);
                indexsource = 0;
                sheet.CreateRow(4).Height = 200 * 8;
                for (int i = 0; i <= MergerHotSource[0].MergerHot.Count; i++)
                {
                    if (i == 0)
                    {
                        ICell cell = irow3.CreateCell(0);
                        cell.SetCellValue("日期");
                        cell.CellStyle = cellStylebt4;
                    }
                    else if (i > 0 && i <= MergerHotSource[0].MergerHot.Count - 2)
                    {
                   
                        if (i == 1)
                        {
                            indexsource = 1;
                            if (MergerHotSource[0].MergerHot[i - 1].Source == "12345")
                            {
                                for (int j = 0; j < 5; j++)
                                {
                                    ICell cell = irow3.CreateCell(indexsource);
                                    switch (j)
                                    {
                                        case 0:
                                            cell.SetCellValue("来\n电\n量\n");
                                        
                                            cell.CellStyle = cellStylebt5;
                                          
                                            sheet.SetColumnWidth(1, 12 * 100);
                                            break;
                                        case 1:
                                            cell.SetCellValue("接\n通\n量\n");
                                           
                                            cell.CellStyle = cellStylebt9;
                                            sheet.SetColumnWidth(2, 12 * 100);
                                          
                                            break;
                                        case 2:
                                            cell.SetCellValue("接\n通\n率\n");
                                           
                                            cell.CellStyle = cellStylebt13;
                                            sheet.SetColumnWidth(3, 12 * 100);
                                           
                                           
                                            break;
                                        case 3:
                                            cell.SetCellValue("未\n接\n通\n");
                                            
                                            cell.CellStyle = cellStylebt4;
                                           
                                           
                                            sheet.SetColumnWidth(4, 12 * 100);
                                            break;
                                        case 4:
                                            cell.SetCellValue("主\n动\n放\n弃\n");
                                           
                                            cell.CellStyle = cellStylebt4;
                                            sheet.SetColumnWidth(5, 12 * 100);
                                            break;
                                      
                                    }
                                    indexsource += 1;
                                }
                                sheet.SetColumnWidth(6, 12 * 35);
                                indexsource = 6;
                            }
                            else
                            {
                                indexsource = 1;
                                for (int j = 0; j < 4; j++)
                                {
                                    ICell cell = irow3.CreateCell(indexsource);
                                    switch (j)
                                    {
                                        case 0:
                                            cell.SetCellValue("来\n电\n量\n");
                                           
                                            cell.CellStyle = cellStylebt5;
                                          
                                            sheet.SetColumnWidth(1, 12 * 100);
                                          
                                            break;
                                        case 1:
                                            cell.SetCellValue("接\n通\n量\n");
                                           
                                            cell.CellStyle = cellStylebt9;
                                            sheet.SetColumnWidth(2, 12 * 100);
                                          
                                          
                                            break;
                                        case 2:
                                            cell.SetCellValue("未\n接\n通\n");
                                            
                                            cell.CellStyle = cellStylebt4;
                                          
                                          
                                            sheet.SetColumnWidth(3, 12 * 100);
                                            break;
                                        case 3:
                                            cell.SetCellValue("主\n动\n放\n弃\n");
                                           
                                            cell.CellStyle = cellStylebt4;
                                           
                                          
                                            sheet.SetColumnWidth(4, 12 * 100);
                                            break;

                                    }
                                  
                                    indexsource += 1;
                                }
                                sheet.SetColumnWidth(5, 12 * 35);
                                indexsource = 5;
                            }
                        }
                        else
                        {
                            if (MergerHotSource[0].MergerHot[i - 1].Source == "12345")
                            {
                                for (int j = 0; j < 5; j++)
                                {
                                    indexsource += 1;
                                    ICell cell = irow3.CreateCell(indexsource);
                                    switch (j)
                                    {
                                        case 0:
                                            cell.SetCellValue("来\n电\n量\n");
                                            cell.CellStyle = cellStylebt5;
                                           
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                            break;
                                        case 1:
                                            cell.SetCellValue("接\n通\n量\n");
                                            cell.CellStyle = cellStylebt9;
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                           
                                            break;
                                        case 2:
                                            cell.SetCellValue("接\n通\n率\n");
                                            cell.CellStyle = cellStylebt2;
                                            sheet.SetColumnWidth(7, 12 * 100);
                                           
                                            break;
                                        case 3:
                                            cell.SetCellValue("未\n接\n通\n");
                                            cell.CellStyle = cellStylebt4;
                                          
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                            break;
                                        case 4:
                                            cell.SetCellValue("主\n动\n放\n弃\n");
                                            cell.CellStyle = cellStylebt4;
                                          
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                            break;
                                    }
                                  
                                }
                                indexsource += 1;
                                sheet.SetColumnWidth(indexsource, 12 * 35);

                            }
                            else
                            {
                                for (int j = 0; j < 4; j++)
                                {
                                    indexsource += 1;
                                    ICell cell = irow3.CreateCell(indexsource);
                                    switch (j)
                                    {
                                        case 0:
                                            cell.SetCellValue("来\n电\n量\n");
                                           
                                            cell.CellStyle = cellStylebt5;
                                          
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                            break;
                                        case 1:
                                            cell.SetCellValue("接\n通\n量\n");
                                           
                                            cell.CellStyle = cellStylebt9;
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                           
                                            break;
                                        case 2:
                                            cell.SetCellValue("未\n接\n通\n");
                                           
                                            cell.CellStyle = cellStylebt4;
                                           
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                            break;
                                        case 3:
                                            cell.SetCellValue("主\n动\n放\n弃\n");
                                          
                                            cell.CellStyle = cellStylebt4;
                                          
                                            sheet.SetColumnWidth(indexsource, 12 * 100);
                                            break;

                                    }
                                 
                                  
                                }

                                indexsource += 1;
                                sheet.SetColumnWidth(indexsource, 12 * 35);
                            }
                          
                        }
                    }
                    else
                    {
                        for (int j = 0; j < 5; j++)
                        {
                            indexsource += 1;
                            ICell cell = irow3.CreateCell(indexsource);
                            switch (j)
                            {
                                case 0:
                                    cell.SetCellValue("来\n电\n量\n");
                                
                                    cell.CellStyle = cellStylebt5;
                                  
                                    sheet.SetColumnWidth(indexsource, 12 * 100);
                                    break;
                                case 1:
                                    cell.SetCellValue("接\n通\n量\n");
                                   
                                    cell.CellStyle = cellStylebt9;
                                    sheet.SetColumnWidth(indexsource, 12 * 100);
                                   
                                    break;
                                case 2:
                                    cell.SetCellValue("未\n接\n通\n");
                                  
                                    cell.CellStyle = cellStylebt4;
                                  
                                    sheet.SetColumnWidth(indexsource, 12 * 100);
                                    break;
                                case 3:
                                    cell.SetCellValue("主\n动\n放\n弃\n");
                                  
                                    cell.CellStyle = cellStylebt4;
                                   
                                    sheet.SetColumnWidth(indexsource, 12 * 100);
                                    break;
                                case 4:
                                    cell.SetCellValue("接\n通\n率\n");
                                   
                                    cell.CellStyle = cellStylebt10;
                                    sheet.SetColumnWidth(indexsource, 12 * 200);
                                  
                               
                                    break;
                            }
                        

                        }
                        indexsource += 1;
                        sheet.SetColumnWidth(indexsource, 12 * 35);
                    }
                }

                for (int i=0;i< MergerHotSource.Count; i++)
                {
                    IRow irow4 = sheet.CreateRow(5+i );
                    for (int j = 0; j <= MergerHotSource[i].MergerHot.Count; j++)
                    {
                        if (j == 0)
                        {
                            ICell cell = irow4.CreateCell(0);
                            cell.SetCellValue(MergerHotSource[i].Data);
                            cell.CellStyle = cellStylebt11;
                            indexsource = 0;
                        }
                        else
                        {
                            if (i == MergerHotSource.Count-1)
                            {
                                if (j == 1)
                                {
                                    if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
                                    {
                                        for (int z = 0; z < 5; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].rate);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt8;

                                                    break;
                                                case 4:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;

                                            }
                                        }
                                        indexsource += 1;

                                    }
                                    else
                                    {
                                        for (int z = 0; z < 4; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;

                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt8;


                                                    break;

                                            }
                                        }
                                        indexsource += 1;
                                    }
                                }
                                else
                                {
                                    if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
                                    {
                                        for (int z = 0; z < 5; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt8;


                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt8;



                                                    break;
                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].rate);
                                                    cell.CellStyle = cellStylebt8;



                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt8;


                                                    break;
                                                case 4:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt8;


                                                    break;

                                            }
                                        }
                                        indexsource += 1;


                                    }
                                    else if (MergerHotSource[i].MergerHot[j - 1].Source == "当日小计"
                                        || MergerHotSource[i].MergerHot[j - 1].Source == "累计")
                                    {

                                        for (int z = 0; z < 5; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;
                                                case 4:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].rate);
                                                    cell.CellStyle = cellStylebt8;
                                                    break;

                                            }
                                        }
                                        indexsource += 1;


                                    }
                                    else
                                    {
                                        for (int z = 0; z < 4; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt8;


                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt8;


                                                    break;

                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt8;

                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt8;

                                                    break;

                                            }
                                        }
                                        indexsource += 1;
                                    }
                                }
                            }
                            else
                            {
                                if (j == 1)
                                {
                                    if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
                                    {
                                        for (int z = 0; z < 5; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt15;
                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt14;
                                                    break;
                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].rate);
                                                    cell.CellStyle = cellStylebt6;
                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt11;

                                                    break;
                                                case 4:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt11;
                                                    break;

                                            }
                                        }
                                        indexsource += 1;

                                    }
                                    else
                                    {
                                        for (int z = 0; z < 4; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt15;
                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt14;
                                                    break;

                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt11;
                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt11;


                                                    break;

                                            }
                                        }
                                        indexsource += 1;
                                    }
                                }
                                else
                                {
                                    if (MergerHotSource[i].MergerHot[j - 1].Source == "12345")
                                    {
                                        for (int z = 0; z < 5; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt15;


                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt14;



                                                    break;
                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].rate);
                                                    cell.CellStyle = cellStylebt6;



                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt11;


                                                    break;
                                                case 4:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt11;


                                                    break;

                                            }
                                        }
                                        indexsource += 1;


                                    }
                                    else if (MergerHotSource[i].MergerHot[j - 1].Source == "当日小计"
                                        || MergerHotSource[i].MergerHot[j - 1].Source == "累计")
                                    {

                                        for (int z = 0; z < 5; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt15;
                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt14;
                                                    break;
                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt11;
                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt11;
                                                    break;
                                                case 4:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].rate);
                                                    cell.CellStyle = cellStylebt12;
                                                    break;

                                            }
                                        }
                                        indexsource += 1;


                                    }
                                    else
                                    {
                                        for (int z = 0; z < 4; z++)
                                        {
                                            indexsource += 1;
                                            ICell cell = irow4.CreateCell(indexsource);
                                            switch (z)
                                            {
                                                case 0:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Call);
                                                    cell.CellStyle = cellStylebt15;


                                                    break;
                                                case 1:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Connect);
                                                    cell.CellStyle = cellStylebt14;


                                                    break;

                                                case 2:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].Notconnected);
                                                    cell.CellStyle = cellStylebt11;

                                                    break;
                                                case 3:
                                                    cell.SetCellValue(MergerHotSource[i]
                                                        .MergerHot[j - 1].voluntarily);
                                                    cell.CellStyle = cellStylebt11;

                                                    break;

                                            }
                                        }
                                        indexsource += 1;
                                    }
                                }
                            }
                          
                        }
                    }
                }

                //自适应列宽度
                //for (int i = 0; i < 20; i++)
                //{
                //    sheet.AutoSizeColumn(i);
                //    // sheet.SetColumnWidth(i, 20 * 256);
                //}

                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("Access-Control-Expose-Headers", "Content-Disposition");
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("归并数据统计" + ".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 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("Access-Control-Expose-Headers", "Content-Disposition");
                    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("Access-Control-Expose-Headers", "Content-Disposition");
                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);

                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellfontbt.Boldweight = (short)FontBoldWeight.Bold;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;

                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 = cellStylebt;

                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
                //((HSSFSheet)sheet).SetEnclosedBorderOfRegion(new CellRangeAddress(1, 1, 0, 7), BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);

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

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

                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 = cellStylebt;

                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;
                ICell cell83 = irow8.CreateCell(2);
                cell83.SetCellValue("在线办理率");
                cell83.CellStyle = cellStyle;
                ICell cell84 = irow8.CreateCell(3);
                cell84.SetCellValue(dt2.Rows[0]["zxbjrate"].ToString());
                cell84.CellStyle = cellStyle;

                #endregion

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

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

                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 = cellStylebt;

                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 = cellStylebt;

                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("Access-Control-Expose-Headers", "Content-Disposition");
                    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 ExportToExcel2(string Name, DataTable dt, string[] cols = null)
        {
            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);
                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++;
                    //}
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(dt.Rows[0][i].ToString());
                        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)
                {
                    if (iRowIndex > 0)
                    {


                        int iCellIndex = 0;
                        IRow irow = sheet.CreateRow(iRowIndex);
                        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("Access-Control-Expose-Headers", "Content-Disposition");
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode(Name + ".xls", Encoding.UTF8));

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

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

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

        /// <summary>
        /// 导出word
        /// </summary>
        /// <returns></returns>
        public byte[] ExportToWordDemo()
        {
            try
            {
                //创建document文档对象对象实例
                NPOI.XWPF.UserModel.XWPFDocument document = new NPOI.XWPF.UserModel.XWPFDocument();

                document.CreateParagraph();

                NPOI.XWPF.UserModel.XWPFParagraph paragraph = document.CreateParagraph();//创建段落对象
                paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;//文字显示位置,段落排列（左对齐，居中，右对齐）

                NPOI.XWPF.UserModel.XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象
                xwpfRun.IsBold = true;//文字加粗
                xwpfRun.SetText("安阳市12345政务服务热线交办单");//填充内容
                xwpfRun.FontSize = 18;//设置文字大小
                xwpfRun.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定

                document.CreateParagraph();

                //创建文档中的表格对象实例
                NPOI.XWPF.UserModel.XWPFTable table = document.CreateTable(9, 8);//显示的行列数rows:3行,cols:4列
                table.Width = 5200;//总宽度
                table.SetColumnWidth(0, 650); /* 设置列宽 */
                table.SetColumnWidth(1, 650);
                table.SetColumnWidth(2, 650);
                table.SetColumnWidth(3, 650);
                table.SetColumnWidth(4, 650);
                table.SetColumnWidth(5, 650);
                table.SetColumnWidth(6, 650);
                table.SetColumnWidth(7, 650);
                for (int i = 0; i < 9; i++)
                {
                    for (int j = 0; j < 8; j++)
                    {
                        var cp = table.GetRow(i).GetCell(j).GetCTTc().AddNewTcPr();
                        cp.AddNewVAlign().val = ST_VerticalJc.center;
                    }
                }

                //第一行
                table.GetRow(0).GetCTRow().AddNewTrPr().AddNewTrHeight().val= (ulong)300;
                var cp0 = table.GetRow(0).GetCell(0).GetCTTc().AddNewTcPr();
                cp0.AddNewVMerge().val = ST_Merge.restart;
                cp0.AddNewVAlign().val = ST_VerticalJc.center;
                cp0.tcW = new CT_TblWidth();cp0.tcW.w = "650";cp0.tcW.type = ST_TblWidth.dxa;
                table.GetRow(0).GetCell(0).SetParagraph(GetParagraph(table, true, "事项编号"));

                var cp1 = table.GetRow(0).GetCell(1).GetCTTc().AddNewTcPr();
                cp1.AddNewVMerge().val = ST_Merge.restart;
                cp1.AddNewVAlign().val = ST_VerticalJc.center;
                table.GetRow(0).GetCell(1).SetParagraph(GetParagraph(table, false, "DH9941052721041200351"));
                table.GetRow(0).GetCell(2).SetParagraph(GetParagraph(table, true, "工单来源"));
                table.GetRow(0).GetCell(3).SetParagraph(GetParagraph(table, false, "市长电话"));
                table.GetRow(0).GetCell(4).SetParagraph(GetParagraph(table, true, "办理时限"));
                table.GetRow(0).GetCell(5).SetParagraph(GetParagraph(table, false, "2021-04-21"));
                table.GetRow(0).GetCell(6).SetParagraph(GetParagraph(table, true, "联系电话"));
                table.GetRow(0).GetCell(7).SetParagraph(GetParagraph(table, false, "017185328292"));
                //第二行
                table.GetRow(1).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)300;
                var cp2 = table.GetRow(1).GetCell(0).GetCTTc().AddNewTcPr();
                cp2.AddNewVMerge().val = ST_Merge.@continue;
                var cp3 = table.GetRow(1).GetCell(1).GetCTTc().AddNewTcPr();
                cp3.AddNewVMerge().val = ST_Merge.@continue;
                table.GetRow(1).GetCell(2).SetParagraph(GetParagraph(table, true, "签收时间"));
                table.GetRow(1).GetCell(3).SetParagraph(GetParagraph(table, false, "2021-04-12"));
                table.GetRow(1).GetCell(4).SetParagraph(GetParagraph(table, true, "办理时间"));
                table.GetRow(1).GetCell(5).SetParagraph(GetParagraph(table, false, "2021-04-12"));
                table.GetRow(1).GetCell(6).SetParagraph(GetParagraph(table, true, "来电人"));
                table.GetRow(1).GetCell(7).SetParagraph(GetParagraph(table, false, "徐青田"));
                //第三行
                table.GetRow(2).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)300;
                table.GetRow(2).MergeCells(1, 7);//合并列
                table.GetRow(2).GetCell(0).SetParagraph(GetParagraph(table, true, "事项区域"));
                table.GetRow(2).GetCell(1).SetParagraph(GetParagraph(table, false, "河南省郑州市高新技术开发区大学科技园东区", false));
                //第四行
                table.GetRow(3).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)2000;
                table.GetRow(3).MergeCells(1, 7);//合并列
                table.GetRow(3).GetCell(0).SetParagraph(GetParagraph(table, true, "内容摘要"));
                table.GetRow(3).GetCell(1).SetParagraph(GetParagraph(table, false, "来电人反映：内黄县城关镇宛庄村第四生产队第二、三小组土地至今无法确权（遗留问题），村委会和镇政府承诺给解决，至今未解决，请政府责成相关部门调查处理。", false));
                //第五行
                table.GetRow(4).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)300;
                table.GetRow(4).MergeCells(1, 4);//合并列
                table.GetRow(4).MergeCells(3, 4);//合并列
                table.GetRow(4).GetCell(0).SetParagraph(GetParagraph(table, true, "承办单位"));
                table.GetRow(4).GetCell(1).SetParagraph(GetParagraph(table, false, "内黄县政府"));
                table.GetRow(4).GetCell(2).SetParagraph(GetParagraph(table, true, "协办单位"));
                table.GetRow(4).GetCell(3).SetParagraph(GetParagraph(table, false, "内黄县政府"));
                //第六行
                table.GetRow(5).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)2000;
                table.GetRow(5).MergeCells(1, 7);//合并列
                table.GetRow(5).GetCell(0).SetParagraph(GetParagraph(table, true, "调度意见"));
                table.GetRow(5).GetCell(1).SetParagraph(GetParagraph(table, false, "请于1小时内与来电人取得联系（匿名除外），并于7个工作日内将办理情况反馈来电人，并将办理结果报市长便民公开电话受理中心，同时做好来电人信息保密工作。（若超期将进行通报；若无故不回复，将转交市委市政府督查局。）", false));
                //第七行
                table.GetRow(6).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)1500;
                table.GetRow(6).MergeCells(1, 7);//合并列
                table.GetRow(6).GetCell(0).SetParagraph(GetParagraph(table, true, "承办意见"));
                table.GetRow(6).GetCell(1).SetParagraph(GetParagraph(table, false, "请于1小时内与来电人取得联系（匿名除外），并于7个工作日内将办理情况反馈来电人，并将办理结果报市长便民公开电话受理中心，同时做好来电人信息保密工作。（若超期将进行通报；若无故不回复，将转交市委市政府督查局。）", false));
                //第八行
                table.GetRow(7).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)1500;
                table.GetRow(7).MergeCells(1, 7);//合并列
                table.GetRow(7).GetCell(0).SetParagraph(GetParagraph(table, true, "领导批示"));
                table.GetRow(7).GetCell(1).SetParagraph(GetParagraph(table, false, "请于1小时内与来电人取得联系（匿名除外），并于7个工作日内将办理情况反馈来电人，并将办理结果报市长便民公开电话受理中心，同时做好来电人信息保密工作。（若超期将进行通报；若无故不回复，将转交市委市政府督查局。）", false));
                //第九行
                table.GetRow(8).GetCTRow().AddNewTrPr().AddNewTrHeight().val = (ulong)2000;
                table.GetRow(8).MergeCells(1, 7);//合并列
                table.GetRow(8).GetCell(0).SetParagraph(GetParagraph(table, true, "处理结果"));
                table.GetRow(8).GetCell(1).SetParagraph(GetParagraph(table, false, "2021-04-12 10:19:14 内黄县政府部门接到市长电话来源工单（编号：DH9941052721041200351）反映土地确权问题，已联系郑州第三方中标土地确权公司负责人待工作人员到我县后，就可以确权 [办理结果：已办理，承办人：刘志强，职务：城关镇人大主席，联系电话：15824608111，刘志强向当事人进行了反馈，当事人对结果表示未评价。]", false));

                document.CreateParagraph();

                NPOI.XWPF.UserModel.XWPFParagraph paragraph1 = document.CreateParagraph();//创建段落对象
                paragraph1.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;//文字显示位置,段落排列（左对齐，居中，右对齐）

                NPOI.XWPF.UserModel.XWPFRun xwpfRun1 = paragraph1.CreateRun();//创建段落文本对象
                xwpfRun1.IsBold = true;//文字加粗
                xwpfRun1.SetText("联系电话：(0372)           12345审核人：8000           调度员：8065");//填充内容
                xwpfRun1.FontSize = 9;//设置文字大小
                xwpfRun1.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定

                NPOI.XWPF.UserModel.XWPFParagraph paragraph2 = document.CreateParagraph();//创建段落对象
                paragraph2.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.RIGHT;//文字显示位置,段落排列（左对齐，居中，右对齐）
                NPOI.XWPF.UserModel.XWPFRun xwpfRun2 = paragraph2.CreateRun();//创建段落文本对象
                using (FileStream picData = new FileStream(HttpContext.Current.Server.MapPath("/Upload/Word/word.png"), FileMode.Open, FileAccess.Read))
                {
                    //图片的文件流   图片类型   图片名称   设置的宽度以及高度
                    xwpfRun2.AddPicture(picData, (int)PictureType.PNG, "word.png", NPOI.Util.Units.ToEMU(100), NPOI.Util.Units.ToEMU(100));
                }


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

                    return ms.GetBuffer();
                }
            }
            catch(Exception ex)
            {
                return null;
            }
            
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="document"></param>
        /// <param name="isbold"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        private NPOI.XWPF.UserModel.XWPFParagraph GetParagraph(NPOI.XWPF.UserModel.XWPFTable table,bool isbold,string content, bool iscenter=true)
        {
            var para = new CT_P();
            //设置单元格文本对齐
            para.AddNewPPr().AddNewTextAlignment();

            NPOI.XWPF.UserModel.XWPFParagraph paragraph = new NPOI.XWPF.UserModel.XWPFParagraph(para,table.Body);
            paragraph.VerticalAlignment = NPOI.XWPF.UserModel.TextAlignment.CENTER;
            if (iscenter)
            {
                paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;
            }
            else
            {
                paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.LEFT;
            }

            NPOI.XWPF.UserModel.XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象
            xwpfRun.IsBold = isbold;//文字加粗
            xwpfRun.SetText(content);//填充内容
            xwpfRun.FontSize = 9;//设置文字大小
            xwpfRun.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定

            return paragraph;
        }
        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private NPOI.XWPF.UserModel.XWPFParagraph GetParagraphImg(NPOI.XWPF.UserModel.XWPFTable table, bool isbold, string content, bool iscenter = true)
        {
            var para = new CT_P();
            //设置单元格文本对齐
            para.AddNewPPr().AddNewTextAlignment();

            NPOI.XWPF.UserModel.XWPFParagraph paragraph = new NPOI.XWPF.UserModel.XWPFParagraph(para, table.Body);
            paragraph.VerticalAlignment = NPOI.XWPF.UserModel.TextAlignment.CENTER;
            if (iscenter)
            {
                paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER;
            }
            else
            {
                paragraph.Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.LEFT;
            }

            NPOI.XWPF.UserModel.XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象
            xwpfRun.IsBold = isbold;//文字加粗
            xwpfRun.SetText(content);//填充内容
            xwpfRun.FontSize = 9;//设置文字大小
            xwpfRun.SetFontFamily("宋体", NPOI.XWPF.UserModel.FontCharRange.None); //设置标题样式如：（微软雅黑，隶书，楷体）根据自己的需求而定

            using (FileStream picData = new FileStream(HttpContext.Current.Server.MapPath("/Upload/Word/word.png"), FileMode.Open, FileAccess.Read))
            {
                //图片的文件流   图片类型   图片名称   设置的宽度以及高度
                xwpfRun.AddPicture(picData, (int)PictureType.PNG, "word.png", NPOI.Util.Units.ToEMU(100), NPOI.Util.Units.ToEMU(100));
            }

            return paragraph;
        }
    }
}