RMYY_CallCenter_Api/RMYY_CallCen.../Helper/NPOIHelper.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Wordprocessing;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace RMYY_CallCenter_Api.Utility
{
    public class NPOIHelper
    {

        /// <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(Name);

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

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


                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);

                if (cols == null || (cols != null && cols.Length == 0))
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(dc.ColumnName);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }
                }
                else
                {
                    foreach (string dc in cols)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(dc);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }
                }
                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


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

                //建立内容行
                int iRowIndex = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex + 1);

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string strsj = string.Empty;
                        if (dr[i] != null)
                        {
                            strsj = dr[i].ToString();
                        }
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(strsj);
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }

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

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("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
        /// </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>
        /// 话务工作报表
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string CallReport(string Name, DataTable dt)
        {
            try
            {
                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.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellfontbt.Boldweight = (short)FontBoldWeight.Normal;
                cellStylebt.SetFont(cellfontbt);
                cellfontbt.FontHeightInPoints = 10;
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;
                cellStylebt.WrapText = true;



                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.VerticalAlignment = VerticalAlignment.Center;
                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);



                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("坐席");
                cell1.CellStyle = HeadercellStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0));

                ICell cell2 = irow1.CreateCell(1);
                cell2.SetCellValue("电话总量");
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
                cell2.CellStyle = HeadercellStyle;

                ICell cell3 = irow1.CreateCell(2);
                cell3.SetCellValue("呼入");
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 2, 9));
                cell3.CellStyle = HeadercellStyle;


                ICell cell4 = irow1.CreateCell(10);
                cell4.SetCellValue("呼出");
               
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 10, 13));
                cell4.CellStyle = HeadercellStyle;

                ICell cell5 = irow1.CreateCell(14);
                cell5.SetCellValue("转接");
              
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 14, 17));
                cell5.CellStyle = HeadercellStyle;





                ICell cel8 = irow1.CreateCell(17);
                cel8.CellStyle = HeadercellStyle;

                IRow irow2 = sheet.CreateRow(1);


                string[] cols = {"总量","已接通","未接听","呼损率","接通率",
                "平均振铃时长(秒)","通话时长(秒)" ,"平均通话时长(秒)",
               "总量","已接通","未接听","接通率","总量","已接通","未接听","接通率" };
                int icolIndex = 0;


                ICell cel6 = irow2.CreateCell(0);
                cel6.CellStyle = HeadercellStyle;

                ICell cel7 = irow2.CreateCell(1);
                cel7.CellStyle = HeadercellStyle;


                foreach (string dc in cols)
                {
                    ICell cell = irow2.CreateCell(icolIndex+2);
                    cell.SetCellValue(dc);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }
                int iRowIndex = 1;

               
               
                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 < 17; i++)
                {
                    if(i >=7&&i <=9)
                    sheet.AutoSizeColumn(i);
                    else 
                    sheet.SetColumnWidth(i, 10 * 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>
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string CarMaintenance(string Name, DataTable dt, bool type = true)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                string message = "车辆调度科室维修情况";
                if (!type)
                    message = "车辆调度工勤维修情况";
                ISheet sheet = workbook.CreateSheet(message);
                ICellStyle cellStyle = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.SetFont(cellfont);



                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.VerticalAlignment = VerticalAlignment.Center;
                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);
                IRow irow1 = sheet.CreateRow(0);
                IRow irow3 = sheet.CreateRow(1);
                IRow irow2 = sheet.CreateRow(2);
                for (int i = 0; i < 9; i++)
                {
                    ICell cel16 = irow1.CreateCell(i);
                    cel16.CellStyle = HeadercellStyle;
                    ICell cel17 = irow2.CreateCell(i);
                    cel17.CellStyle = HeadercellStyle;
                    ICell cel18 = irow3.CreateCell(i);
                    cel18.CellStyle = HeadercellStyle;
                }


                ICell cell1 = irow1.CreateCell(0);
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 0));
                cell1.CellStyle = HeadercellStyle;
                cell1.SetCellValue("院区");

                ICell cell2 = irow1.CreateCell(1);
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 1, 1));
                cell2.SetCellValue("科室");
                cell2.CellStyle = HeadercellStyle;


                ICell cell3 = irow1.CreateCell(2);
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 2, 2));
                if (!type)
                    cell3.SetCellValue("工勤");
                else
                    cell3.SetCellValue("工单类型");
                cell3.CellStyle = HeadercellStyle;



                ICell cell4 = irow1.CreateCell(3);
                cell4.SetCellValue("工单数量");
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 6));
                cell4.CellStyle = HeadercellStyle;

                ICell cell5 = irow1.CreateCell(7);
                cell5.SetCellValue("不满意量");
                sheet.AddMergedRegion(new CellRangeAddress(0, 1, 7, 8));
                cell5.CellStyle = HeadercellStyle;



                ICell cell9 = irow3.CreateCell(3);
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 3, 3));
                cell9.SetCellValue("接单量");
                cell9.CellStyle = HeadercellStyle;

                ICell cell11 = irow3.CreateCell(4);
                cell11.SetCellValue("完成情况");
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 6));
                cell11.CellStyle = HeadercellStyle;

       

               


             




                // ICell cel8 = irow1.CreateCell(17);
                // cel8.CellStyle = HeadercellStyle;




                string[] cols = {"完成量","未完成量","完成率",
             "数量","不满意率" };
                int icolIndex = 0;


                //ICell cel6 = irow2.CreateCell(0);
                //cel6.CellStyle = HeadercellStyle;

                //ICell cel7 = irow2.CreateCell(1);
                //cel7.CellStyle = HeadercellStyle;


                foreach (string dc in cols)
                {
                    ICell cell = irow2.CreateCell(icolIndex + 4);
                    cell.SetCellValue(dc);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }


                int iRowIndex = 2;



                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++;
                }

                int start = 3;
                iRowIndex = 2;
                int end = 3;
                string woname = dt.Rows[0]["woname"].ToString();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (woname == dt.Rows[i]["woname"].ToString())
                    {
                        end++;
                    }
                    else
                    {
                        woname = dt.Rows[i]["woname"].ToString();
                        sheet.AddMergedRegion(new CellRangeAddress(start, end - 1, 0, 0));
                        start = end;
                        end++;
                    }
                }






                //自适应列宽度
                for (int i = 0; i < 9; i++)
                {
                    if (i == 0)
                    {
                        sheet.SetColumnWidth(i, 25 * 256);
                    }
                    else if (i < 3)
                        sheet.AutoSizeColumn(i);
                    else
                        sheet.SetColumnWidth(i, 10 * 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(message + ".xls", Encoding.UTF8));

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

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

                    curContext.Response.End();
                }
                return "";
            }
            catch
            {
                return "导出失败！";
            }
        }
        /// <summary>
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string Maintenance(string Name, DataTable dt,bool  type=true )
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                string message = "科室维修情况";
                if (!type)
                    message = "工勤维修情况";
                ISheet sheet = workbook.CreateSheet(message);
                ICellStyle cellStyle = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.SetFont(cellfont);



                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.VerticalAlignment = VerticalAlignment.Center;
                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);
                IRow irow1 = sheet.CreateRow(0);
                IRow irow3 = sheet.CreateRow(1);
                IRow irow2 = sheet.CreateRow(2);
                for (int i = 0; i < 15; i++)
                {
                    ICell cel16 = irow1.CreateCell(i);
                    cel16.CellStyle = HeadercellStyle;
                    ICell cel17 = irow2.CreateCell(i);
                    cel17.CellStyle = HeadercellStyle;
                    ICell cel18 = irow3.CreateCell(i);
                    cel18.CellStyle = HeadercellStyle;
                }

               
                ICell cell1 = irow1.CreateCell(0);
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 0));
                cell1.CellStyle = HeadercellStyle;
                cell1.SetCellValue("院区");

                ICell cell2 = irow1.CreateCell(1);
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 1, 1));
                cell2.SetCellValue("科室");
                cell2.CellStyle = HeadercellStyle;
               

                ICell cell3 = irow1.CreateCell(2);
                sheet.AddMergedRegion(new CellRangeAddress(0, 2, 2, 2));
                if (!type )
                cell3.SetCellValue("工勤");
                else
                 cell3.SetCellValue("工单类型");
                cell3.CellStyle = HeadercellStyle;
               


                ICell cell4 = irow1.CreateCell(3);
                cell4.SetCellValue("工单数量");
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 8));
                cell4.CellStyle = HeadercellStyle;

                ICell cell5 = irow1.CreateCell(9);
                cell5.SetCellValue("不满意量");
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 9, 14));
                cell5.CellStyle = HeadercellStyle;


                
                ICell cell9 = irow3.CreateCell(3);
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 3, 3));
                cell9.SetCellValue("接单量");
                cell9.CellStyle = HeadercellStyle;

                ICell cell11 = irow3.CreateCell(4);
                cell11.SetCellValue("完成情况");
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 6));
                cell11.CellStyle = HeadercellStyle;

                ICell cell12 = irow3.CreateCell(7);
                cell12.SetCellValue("超时情况");
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 7, 8));
                cell12.CellStyle = HeadercellStyle;

                ICell cell13 = irow3.CreateCell(9);
                cell13.SetCellValue("质量");
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 9, 10));
                cell13.CellStyle = HeadercellStyle;


                ICell cell14 = irow3.CreateCell(11);
                cell14.SetCellValue("态度");
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 11, 12));
                cell14.CellStyle = HeadercellStyle;


                ICell cell15 = irow3.CreateCell(13);
                cell15.SetCellValue("效率");
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 13, 14));
                cell15.CellStyle = HeadercellStyle;


               

                // ICell cel8 = irow1.CreateCell(17);
                // cel8.CellStyle = HeadercellStyle;

                


                string[] cols = {"完成量","未完成量","完成率","数量","超时率",
             "数量","不满意率", "数量","不满意率", "数量","不满意率" };
                int icolIndex = 0;


                //ICell cel6 = irow2.CreateCell(0);
                //cel6.CellStyle = HeadercellStyle;

                //ICell cel7 = irow2.CreateCell(1);
                //cel7.CellStyle = HeadercellStyle;


                foreach (string dc in cols)
                {
                    ICell cell = irow2.CreateCell(icolIndex + 4);
                    cell.SetCellValue(dc);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

              
                int iRowIndex = 2;



                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++;
                }
               
                   int start = 3;
                     iRowIndex = 2;
                    int end = 3;
                    string woname = dt.Rows[0]["woname"].ToString();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (woname ==dt.Rows [i ]["woname"].ToString())
                        {
                        end++;
                        }
                        else
                        {
                            woname = dt.Rows[i]["woname"].ToString();
                            sheet.AddMergedRegion(new CellRangeAddress(start, end - 1, 0, 0));
                            start = end;
                           end++;
                        }
                    }
                  
                




                //自适应列宽度
                for (int i = 0; i < 15; i++)
                {  if (i ==0)
                    {
                        sheet.SetColumnWidth(i, 25 * 256);
                    }
                    else if (i < 3)
                        sheet.AutoSizeColumn(i);
                    else
                    sheet.SetColumnWidth(i, 10 * 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(message  + ".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(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();
                    }
                }

                dt.Rows.Add(dr);
            }

            sheet = null;
            workbook = null;

            return dt;
        }



        /// <summary>
        /// riyuezhou报导出
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string SimpleExportToExcel(DataTable zongshudt, DataTable ywzxdt, DataTable zhdddt, DataTable gzbxdt, DataTable cldddt)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");
                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;
                cellfontbt.FontHeightInPoints = 14;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;

                int rowindex = 0;





                #region 业务咨询情况

                IRow irow1 = sheet.CreateRow(rowindex);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue(zongshudt.Rows[0]["typename"].ToString() + " ( " + zongshudt.Rows[0]["zscount"].ToString() + ")");
                cell1.CellStyle = cellStylebt;
                //起始行，结束行，起始列，结束列 从0 开始
                //  合并第一行 6个单元格
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));
                rowindex++;
                double xx = ywzxdt.Rows.Count / 3;
                int allrow = Convert.ToInt32(Math.Ceiling(xx));


                int zzrowindex = 0;
                for (int i = 0; i < allrow; i++)
                {
                    IRow irow2 = sheet.CreateRow(rowindex);


                    for (int j = 0; j < 3; j++)
                    {
                        if (zzrowindex < ywzxdt.Rows.Count)
                        {  
                            ICell cell2 = irow2.CreateCell(j * 2);
                            cell2.SetCellValue(ywzxdt.Rows[zzrowindex]["F_Name"].ToString());
                            cell2.CellStyle = cellStylebt;

                            ICell cell3 = irow2.CreateCell(j * 2 + 1);
                            cell3.SetCellValue(ywzxdt.Rows[zzrowindex]["COUNT"].ToString());
                            cell3.CellStyle = cellStylebt;

                            zzrowindex = zzrowindex + 1;
                        }

                    }
                    rowindex = rowindex + 1;

                }
                #endregion





                #region 故障报修情况

                IRow irowgz = sheet.CreateRow(rowindex);
                ICell cell1gz = irowgz.CreateCell(0);
                cell1gz.SetCellValue(zongshudt.Rows[2]["typename"].ToString() + " ( " + zongshudt.Rows[2]["zscount"].ToString() + ")");
                cell1gz.CellStyle = cellStylebt;
                //起始行，结束行，起始列，结束列 从0 开始
                //  合并第一行 6个单元格
                sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, 5));
                rowindex++;

                double bxxx = gzbxdt.Rows.Count / 3.0;
                int bxallrow = Convert.ToInt32(Math.Ceiling(bxxx));
                int gzrowindex = 0;

                for (int i = 0; i < bxallrow; i++)
                {
                    IRow irow2 = sheet.CreateRow(rowindex);


                    for (int j = 0; j < 3; j++)
                    {
                        if (gzrowindex < gzbxdt.Rows.Count)
                        {
                            ICell cell2 = irow2.CreateCell(j * 2);
                            cell2.SetCellValue(gzbxdt.Rows[gzrowindex]["F_Name"].ToString());
                            cell2.CellStyle = cellStylebt;

                            ICell cell3 = irow2.CreateCell(j * 2 + 1);
                            cell3.SetCellValue(gzbxdt.Rows[gzrowindex]["COUNT"].ToString());
                            cell3.CellStyle = cellStylebt;

                            gzrowindex = gzrowindex + 1;
                        }

                    }
                    rowindex = rowindex + 1;

                }
                #endregion




                #region 综合调度情况

                IRow irowdd = sheet.CreateRow(rowindex);
                ICell cell1gdd = irowdd.CreateCell(0);
                cell1gdd.SetCellValue(zongshudt.Rows[1]["typename"].ToString() + " ( " + zongshudt.Rows[1]["zscount"].ToString() + ")");
                cell1gdd.CellStyle = cellStylebt;
                //起始行，结束行，起始列，结束列 从0 开始
                //  合并第一行 6个单元格
                sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, 5));
                rowindex++;

                double zhddxx = zhdddt.Rows.Count / 3.0;
                int zhddallrow = Convert.ToInt32(Math.Ceiling(zhddxx));

                int zhddrowindex = 0;
                for (int i = 0; i < zhddallrow; i++)
                {
                    IRow irow2 = sheet.CreateRow(rowindex);


                    for (int j = 0; j < 3; j++)
                    {
                        if (zhddrowindex < zhdddt.Rows.Count)
                        {
                            ICell cell2 = irow2.CreateCell(j * 2);
                            cell2.SetCellValue(zhdddt.Rows[zhddrowindex]["F_Name"].ToString());
                            cell2.CellStyle = cellStylebt;

                            ICell cell3 = irow2.CreateCell(j * 2 + 1);
                            cell3.SetCellValue(zhdddt.Rows[zhddrowindex]["COUNT"].ToString());
                            cell3.CellStyle = cellStylebt;

                            zhddrowindex = zhddrowindex + 1;
                        }

                    }
                    rowindex = rowindex + 1;

                }
                #endregion



                #region 车辆调度情况

                IRow irowcldd = sheet.CreateRow(rowindex);
                ICell cell1cldd = irowcldd.CreateCell(0);
                cell1cldd.SetCellValue(zongshudt.Rows[3]["typename"].ToString() + " ( " + zongshudt.Rows[3]["zscount"].ToString() + ")");
                cell1cldd.CellStyle = cellStylebt;
                //起始行，结束行，起始列，结束列 从0 开始
                //  合并第一行 6个单元格
                sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, 5));
                rowindex++;

                double clddxx = cldddt.Rows.Count / 3.0;
                int clddallrow = Convert.ToInt32(Math.Ceiling(clddxx));


                int clddrowindex = 0;
                for (int i = 0; i < clddallrow; i++)
                {
                    IRow irow2 = sheet.CreateRow(rowindex);


                    for (int j = 0; j < 3; j++)
                    {
                        if (clddrowindex < cldddt.Rows.Count)
                        {
                            ICell cell2 = irow2.CreateCell(j * 2);
                            cell2.SetCellValue(cldddt.Rows[clddrowindex]["F_Name"].ToString());
                            cell2.CellStyle = cellStylebt;

                            ICell cell3 = irow2.CreateCell(j * 2 + 1);
                            cell3.SetCellValue(cldddt.Rows[clddrowindex]["COUNT"].ToString());
                            cell3.CellStyle = cellStylebt;

                            clddrowindex = clddrowindex + 1;
                        }
                    }
                    rowindex = rowindex + 1;

                }
                #endregion




                //自适应列宽度
                for (int i = 0; i < 8; i++)
                {
                    sheet.SetColumnWidth(i,30*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("日月周简报_导出文件_" + DateTime.Now.Ticks + ".xls", Encoding.UTF8));

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

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

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

        public class T_InvolvedEquip_RepairProcess
        {
            public string F_EquipName { set; get; }
            public string F_EquipNumber { set; get; }
            public string F_EquipAmount { set; get; }
            public string F_EquipPrice { set; get; }
            public string Allprice { set; get; }
        }
        public string AboutEquipExportToExcel(string Name, DataTable dt, string[] cols = null)
        {
            try
            {
                //if (dt.Rows.Count > 0)
                //{
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet(Name);

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

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


                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);

                //if (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;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;

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

                //建立内容行
                int iRowIndex = 0;
                int Equipscount = 1;

                foreach (DataRow dr in dt.Rows)
                {
                    if (!dr["Equips"].IsNullOrEmpty())
                    {
                       List < T_InvolvedEquip_RepairProcess > dt1= 
                            JsonConvert.DeserializeObject<List<T_InvolvedEquip_RepairProcess>>(dr["Equips"].ToJson());
                        Equipscount = dt1.Count;
                        if (dt1.Count>0)
                        {
                            int allcount = dt1.Count;
                            int first = iRowIndex;
                            for (int j = 0; j < dt1.Count; j++)
                            {
                                IRow irow3 = sheet.CreateRow(iRowIndex + 1);
                                for (int i = 0; i < cols.Count(); i++)
                                {
                                    if (i < 6&&j ==0)
                                    {
                                        ICell cell = irow3.CreateCell(i);
                                        cell.SetCellValue(dr[i].ToString());
                                        cell.CellStyle = cellStyle;
                                        sheet.AddMergedRegion(new CellRangeAddress(iRowIndex + 1, iRowIndex + allcount, i, i));
                                    }
                                    else if (i > 10 && j == 0)
                                    {
                                        ICell cell = irow3.CreateCell(i);
                                        cell.SetCellValue(dr[i - 5].ToString());
                                        cell.CellStyle = cellStyle;
                                        sheet.AddMergedRegion(new CellRangeAddress(iRowIndex + 1, iRowIndex + allcount, i, i));
                                    }
                                    else
                                    {
                                        ICell cell = irow3.CreateCell(i);
                                        if (i > 5 & i < 11)
                                        {
                                            switch (i)
                                            {
                                                case 6:
                                                    cell.SetCellValue(dt1[j].F_EquipName);
                                                    break;
                                                case 7:
                                                    cell.SetCellValue(dt1[j].F_EquipNumber);
                                                    break;
                                                case 8:
                                                    cell.SetCellValue(dt1[j].F_EquipAmount);
                                                    break;
                                                case 9:
                                                    cell.SetCellValue(dt1[j].F_EquipPrice);
                                                    break;
                                                case 10:
                                                    cell.SetCellValue(dt1[j].Allprice);
                                                    break;
                                            }
                                        }
                                        cell.CellStyle = cellStyle;
                                    }
                                }
                                iRowIndex++;
                            }

                        }
                    }
                    else
                    {
                      
                        IRow irow = sheet.CreateRow(iRowIndex + 1);
                        for (int i = 0; i < cols.Count(); i++)
                        {
                            string strsj = string.Empty;
                            if (i<6)
                            {
                                ICell cell = irow.CreateCell(i );
                                cell.SetCellValue(dr[i].ToString());
                                cell.CellStyle = cellStyle;
                              
                            }
                            else if (i >5&i <11)
                            {
                                ICell cell = irow.CreateCell(i );
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                              
                            }
                            else
                            {
                                ICell cell = irow.CreateCell(i );
                                cell.SetCellValue(dr[i-5].ToString());
                                cell.CellStyle = cellStyle;
                               

                            }
                        }
                        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(Exception e)
            {
                return e .Message ;
            }
        }

        public string  DeptRepairExportToExcel(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;
                HeadercellStyle.VerticalAlignment = VerticalAlignment.Center;

                //字体
                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))
                {

                    //ICell cell = headerRow.CreateCell(icolIndex);
                    //cell.SetCellValue("院区");
                    //cell.CellStyle = HeadercellStyle;
                    //icolIndex++;

                    //ICell cell2 = headerRow.CreateCell(icolIndex);
                    //cell2.SetCellValue("科室");
                    //cell2.CellStyle = HeadercellStyle;
                    //icolIndex++;

                    //ICell cell3 = headerRow.CreateCell(icolIndex);
                    //cell3.SetCellValue("工单数量");
                    //cell3.CellStyle = HeadercellStyle;
                    //icolIndex++;


                   
                    

                    IRow headerRow1 = sheet.CreateRow(1);

                    IRow headerRow2 = sheet.CreateRow(2);


                    IRow headerRow3 = sheet.CreateRow(3);

                    ICell cell01 = headerRow.CreateCell(icolIndex);
                    cell01.SetCellValue("院区");
                    cell01.CellStyle = HeadercellStyle;
                    icolIndex++;
                    ICell cell02 = headerRow.CreateCell(icolIndex);
                    cell02.SetCellValue("科室");
                    cell02.CellStyle = HeadercellStyle;
                    icolIndex++;

                    ICell cell03 = headerRow.CreateCell(icolIndex);
                    cell03.SetCellValue("工单数量");
                    cell03.CellStyle = HeadercellStyle;
                    icolIndex++;

                   

                    for (int createicol = icolIndex; createicol < 11; createicol++)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue("");
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }


                    ICell c11 = headerRow1.CreateCell(0);
                    c11.SetCellValue("");
                    c11.CellStyle = HeadercellStyle;

                    ICell c12 = headerRow1.CreateCell(1);

                    c12.SetCellValue("");
                    c12.CellStyle = HeadercellStyle;

                    ICell c13 = headerRow1.CreateCell(2);
                    c13.SetCellValue("故障报修");
                    c13.CellStyle = HeadercellStyle;

                    ICell c14 = headerRow1.CreateCell(3);

                    c14.SetCellValue("");
                    c14.CellStyle = HeadercellStyle;


                    ICell c15 = headerRow1.CreateCell(4);

                    c15.SetCellValue("");
                    c15.CellStyle = HeadercellStyle;

                    ICell c16 = headerRow1.CreateCell(5);
                    c16.SetCellValue("车辆调度");
                    c16.CellStyle = HeadercellStyle;

                    ICell c17 = headerRow1.CreateCell(6);

                    c17.SetCellValue("");
                    c17.CellStyle = HeadercellStyle;


                    ICell c19 = headerRow1.CreateCell(7);

                    c19.SetCellValue("");
                    c19.CellStyle = HeadercellStyle;

                    ICell c110 = headerRow1.CreateCell(8);
                    c110.SetCellValue("综合调度");
                    c110.CellStyle = HeadercellStyle;

                    ICell c111 = headerRow1.CreateCell(9);

                    c111.SetCellValue("");
                    c111.CellStyle = HeadercellStyle;


                    ICell c112 = headerRow1.CreateCell(10);

                    c112.SetCellValue("");
                    c112.CellStyle = HeadercellStyle;



                    ICell cc11 = headerRow2.CreateCell(0);
                    cc11.SetCellValue("");
                    cc11.CellStyle = HeadercellStyle;

                    ICell cc12 = headerRow2.CreateCell(1);

                    cc12.SetCellValue("");
                    cc12.CellStyle = HeadercellStyle;

                    ICell cc13 = headerRow2.CreateCell(2);
                    cc13.SetCellValue("总数");
                    cc13.CellStyle = HeadercellStyle;

                    ICell cc14 = headerRow2.CreateCell(3);

                    cc14.SetCellValue("已完成");
                    cc14.CellStyle = HeadercellStyle;


                    ICell cc15 = headerRow2.CreateCell(4);

                    cc15.SetCellValue("未完成");
                    cc15.CellStyle = HeadercellStyle;

                    ICell cc16 = headerRow2.CreateCell(5);
                    cc16.SetCellValue("总数");
                    cc16.CellStyle = HeadercellStyle;

                    ICell cc17 = headerRow2.CreateCell(6);

                    cc17.SetCellValue("已完成");
                    cc17.CellStyle = HeadercellStyle;


                    ICell cc19 = headerRow2.CreateCell(7);

                    cc19.SetCellValue("未完成");
                    cc19.CellStyle = HeadercellStyle;

                    ICell cc110 = headerRow2.CreateCell(8);
                    cc110.SetCellValue("总数");
                    cc110.CellStyle = HeadercellStyle;

                    ICell cc111 = headerRow2.CreateCell(9);

                    cc111.SetCellValue("已完成");
                    cc111.CellStyle = HeadercellStyle;


                    ICell cc112 = headerRow2.CreateCell(10);

                    cc112.SetCellValue("未完成");
                    cc112.CellStyle = HeadercellStyle;


                    //起始行，结束行，起始列，结束列
                    sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 0));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 2, 1, 1));
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 2, 10));



                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4));
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4));
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 5, 7));
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 8, 10));
                }

                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;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                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 + 3);

                    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++;
                }


                int start = 3;
                iRowIndex = 2;
                int end = 3;
                string woname = dt.Rows[0]["yqname"].ToString();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (woname == dt.Rows[i]["yqname"].ToString())
                    {
                        end++;
                    }
                    else
                    {
                        woname = dt.Rows[i]["yqname"].ToString();
                        sheet.AddMergedRegion(new CellRangeAddress(start, end - 1, 0, 0));
                        start = end;
                        end++;
                    }
                }




                //自适应列宽度
                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 "导出失败！";
            }
        }


    }
}