using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Linq;
using CallCenterApi.DB;
using System.Threading.Tasks;
using System.Net.Http;
using System.Net;

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

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

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

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

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

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




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

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

            return true;
        }

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

        /// <summary>
        /// 弹出下载框导出excel
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string ExportToExcel(string Name, DataTable dt, string[] cols = null,int iswork=0)
        {
            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;
                //字体
               
                if (iswork>0)
                {
                   IDataFormat format = workbook.CreateDataFormat();
                   HeadercellStyle.DataFormat = format.GetFormat("[DbNum2][$-804]0");
                }
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                headerfont.FontHeightInPoints = 12;
                HeadercellStyle.SetFont(headerfont);
                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                if (cols == null || (cols != null && cols.Length == 0))
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(dc.ColumnName);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }
                }
                else
                {
                    foreach (string dc in cols)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(dc);
                        cell.CellStyle = HeadercellStyle;
                        icolIndex++;
                    }
                }
                ICellStyle cellStyle = workbook.CreateCellStyle();

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


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

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

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

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

                    HttpContext curContext = HttpContext.Current;


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

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

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

                    curContext.Response.End();
                }
                //}
                return "";
            }
            catch (Exception e)
            {
                LogFactory.GetLogger("导出").Error(e.ToJson());
                return "导出失败！";
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 工单类型弹出下载框导出excel
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dt"></param>
        /// <param name="typeclass">仪器或试剂</param>
        /// <returns></returns>
        public string ExportToExcelForGDLX(string Name, DataTable dt, string typeclass, List<string> colnames, List<int> erows, List<string> secolnames)
        {
            try
            {
                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);
                
                for (int i = 0; i < colnames.Count; i++)
                {
                    ICell cell = headerRow.CreateCell(i);
                    cell.SetCellValue(colnames[i]);
                    cell.CellStyle = HeadercellStyle;
                    //SetCellRangeAddress(sheet, 0, 0, erows[i * 2], erows[i * 2 + 1]);
                }
                
                for (int k = 0; k < erows.Count / 2; k++)
                {
                    SetCellRangeAddress(sheet, 0, 0, erows[k * 2], erows[k * 2 + 1]);
                }
                //添加第二行标题
                IRow SecRow = sheet.CreateRow(1);
                for (int i = 0; i < secolnames.Count; i++)
                {
                    ICell cell = SecRow.CreateCell(i);
                    cell.SetCellValue(secolnames[i].ToString());
                }

                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 + 2);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string strsj = string.Empty;
                        if (dr[i] != null)
                        {
                            strsj = dr[i].ToString();
                        }
                        ICell cell = irow.CreateCell(iCellIndex);
                        cell.SetCellValue(strsj);
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iRowIndex++;
                }
                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

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

                    HttpContext curContext = HttpContext.Current;


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

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

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

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

        /// <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(string fileName, int headrow)
        {
            DataTable dt = new DataTable();

            IWorkbook workbook = null;

            Stream stream = new FileStream(fileName, FileMode.OpenOrCreate);

            string suffix = fileName.Substring(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>
        /// 简报导出
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string MarketExportToExcel(string stime,string etime,Market market )
        {
            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("时间：" + stime + " 至 " + etime);
                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 产品投诉情况
                List<Ification>    dt1 = market.product ;
                IRow irow2 = sheet.CreateRow(2);
                ICell cell2 = irow2.CreateCell(0);
                cell2.SetCellValue("产品投诉情况");
                cell2.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 7));
               int dtrow = 3;
                 if (dt1.Count >0)
                {
                    int dt = 0;
                    int  a = dt1.Count / 3;
                    float b = dt1.Count % 3;
                    if (b >0)
                        a = a + 1;
                    dtrow += a;
                    List<IRow> irow3list = new List<IRow>();
                      for (int i =0;i <a;i++)
                      {
                        IRow irow3 = sheet.CreateRow(3 + i);
                        irow3list.Add(irow3);
                      }
                    int td = 0;
                        foreach (var it in dt1)
                        {
                            dt++;
                            if (dt <4)
                            {
                                ICell cell101 = irow3list[td].CreateCell(3 * dt  - 3);
                                cell101.SetCellValue(it.name );
                                cell101.CellStyle = cellStylebt;
                                ICell cell102 = irow3list[td].CreateCell(3 * dt  - 2);
                                cell102.SetCellValue(it .number );
                                cell102.CellStyle = cellStylebt;
                               ICell cell103= irow3list[td].CreateCell(3 * dt-1 );
                               cell103.SetCellValue(it.Proportion );
                               cell103.CellStyle = cellStylebt;
                        }
                            else
                            {
                                dt = 1;
                                td++;
                                ICell cell101 = irow3list[td].CreateCell(3 * dt - 3);
                                cell101.SetCellValue(it.name);
                                cell101.CellStyle = cellStylebt;
                                ICell cell102 = irow3list[td].CreateCell(3 * dt - 2);
                                cell102.SetCellValue(it.number);
                                cell102.CellStyle = cellStylebt;
                               ICell cell103 = irow3list[td].CreateCell(3 * dt-1);
                                cell103.SetCellValue(it.Proportion);
                                cell103.CellStyle = cellStylebt;
                        }
                        }
                   
                }

                #endregion

                #region 服务投诉情况
                Ification dt2 = market.service;
                dtrow++;
                IRow irow4 = sheet.CreateRow(dtrow);
                ICell cell4 = irow4.CreateCell(0);
                cell4.SetCellValue("服务投诉情况");
                cell4.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(dtrow, dtrow, 0, 7));
                if (dt2!=null )
                {
                    dtrow++;
                    IRow irow5 = sheet.CreateRow(dtrow);
                    ICell cell51 = irow5.CreateCell(0);
                    cell51.SetCellValue(dt2.name);
                    cell51.CellStyle = cellStylebt;
                    ICell cell52 = irow5.CreateCell(1);
                    cell52.SetCellValue(dt2.number);
                    cell52.CellStyle = cellStylebt;
                    ICell cell53 = irow5.CreateCell(2);
                    cell53.SetCellValue(dt2.Proportion);
                    cell53.CellStyle = cellStylebt;
                }
               
                #endregion
                #region 涉媒投诉
                Ification dt3 = market.sediainvolved;
                dtrow++;
                IRow irow9 = sheet.CreateRow(dtrow);
                ICell cell9 = irow9.CreateCell(0);
                cell9.SetCellValue("涉媒投诉");
                cell9.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(dtrow, dtrow, 0, 7));
                if (dt3!=null )
                {
                    dtrow++;
                    IRow irow10 = sheet.CreateRow(dtrow);
                    ICell cell53 = irow10.CreateCell(0);
                    cell53.SetCellValue(dt3.name);
                    cell53.CellStyle = cellStylebt;
                    ICell cell54 = irow10.CreateCell(1);
                    cell54.SetCellValue(dt3.number);
                    cell54.CellStyle = cellStylebt;
                    ICell cell55 = irow10.CreateCell(2);
                    cell55.SetCellValue(dt3.Proportion);
                    cell55.CellStyle = cellStylebt;
                }
               
                #endregion

                #region 市场抽检
                Ification dt4 = market.spotcheck;
                dtrow++;
                IRow irow11 = sheet.CreateRow(dtrow);
                ICell cell12 = irow11.CreateCell(0);
                cell12.SetCellValue("市场抽检");
                cell12.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(dtrow, dtrow, 0, 7));
                if (dt4!=null )
                {
                    dtrow++;
                    IRow irow12 = sheet.CreateRow(dtrow);
                    ICell cell55 = irow12.CreateCell(0);
                    cell55.SetCellValue(dt4.name);
                    cell55.CellStyle = cellStylebt;
                    ICell cell56 = irow12.CreateCell(1);
                    cell56.SetCellValue(dt4.number);
                    cell56.CellStyle = cellStylebt;
                    ICell cell57 = irow12.CreateCell(2);
                    cell57.SetCellValue(dt4.Proportion);
                    cell57.CellStyle = cellStylebt;
                }
             

                #endregion

                #region 其他信息
                Ification dt5 = market.other;
                dtrow++;
                IRow irow13 = sheet.CreateRow(dtrow);
                ICell cell13 = irow13.CreateCell(0);
                cell13.SetCellValue("其他信息");
                cell13.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(dtrow, dtrow, 0, 7));
                if (dt5!=null )
                {
                    dtrow++;
                    IRow irow15 = sheet.CreateRow(dtrow);
                    ICell cell57 = irow15.CreateCell(0);
                    cell57.SetCellValue(dt4.name);
                    cell57.CellStyle = cellStylebt;
                    ICell cell58 = irow15.CreateCell(1);
                    cell58.SetCellValue(dt4.number);
                    cell58.CellStyle = cellStylebt;
                    ICell cell59 = irow15.CreateCell(2);
                    cell59.SetCellValue(dt5.Proportion);
                    cell59.CellStyle = cellStylebt;
                }
               
                #endregion
                //自适应列宽度
                for (int i = 0; i < 8; i++)
                {
                    // sheet.AutoSizeColumn(i);
                    sheet.SetColumnWidth(i, 12 * 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("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 "导出失败！";
            }
        }

        private void Columnwidth(int number, ISheet ffSheet)
        {
            for (int columnNum = 0; columnNum <= number; columnNum++)
            {
                int columnWidth = ffSheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度  
                for (int rowNum = 1; rowNum <= ffSheet.LastRowNum; rowNum++)//在这一列上循环行  
                {
                    IRow currentRow = ffSheet.GetRow(rowNum);
                    ICell currentCell = currentRow.GetCell(columnNum);
                    int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度  
                    if (columnWidth < length + 1)
                    {
                        columnWidth = length + 1;
                    }//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符  
                }
            }
        }
        private string ReturnMsg(int j, int i, List<Factory> Factory1)
        {
            string msg = "";
            switch (j)
            {
                case 0:
                    msg = Factory1[i * 2].name;
                    break;
                case 1:
                    msg = Factory1[i * 2].number.ToString();
                    break;
                case 2:
                    msg = Factory1[i * 2].proportion;
                    break;
                case 3:
                    msg = Factory1[i * 2 + 1].name;
                    break;
                case 4:
                    msg = Factory1[i * 2 + 1].number.ToString();
                    break;
                case 5:
                    msg = Factory1[i * 2 + 1].proportion;
                    break;
            }
            return msg;
        }
        /// <summary>
        /// 投诉产品日期分布表
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public string DistributionToExcel(DateTime datetime, Product product)
        {
            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();
                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;
                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(datetime.Year +"年"+datetime .Month +"月份投诉产品日期分布情况表");
                cell1.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 13));
                #region 产品投诉情况
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                cellStylebt1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStylebt1.SetFont(cellfontbt);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                IRow irow2 = sheet.CreateRow(1);
                ICell cell2 = irow2.CreateCell(0);
                ICellStyle style13 = workbook.CreateCellStyle();
                style13.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderDiagonalLineStyle = BorderStyle.Thin;
                style13.BorderDiagonal = BorderDiagonal.Backward;
                style13.BorderDiagonalColor = IndexedColors.Black .Index;
                string sb = "                  数量\n日期";
                cell2.SetCellValue(sb);
                cell2.CellStyle = cellStylebt;
                style13.WrapText = true;
                irow2.GetCell(0).CellStyle = style13;
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0,0 ));
                ICell cell6 = irow2.CreateCell(1);
                cell6.SetCellValue("投诉产品");
                cell6.CellStyle = cellStylebt;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 1));
                for (int i=0;i<5;i ++)
                {
                    string msg = "";
                    switch (i)
                    {
                        case 0:
                            msg = datetime.AddMonths(-11).Month + "-" + datetime.AddMonths(-3).Month + "月份日期";
                            break;
                        case 1:
                            msg = datetime.AddMonths(-2).Month + "月份日期";
                            break;
                        case 2:
                            msg = datetime.AddMonths(-1).Month + "月份日期";
                            break;
                        case 3:
                            msg = datetime.Month + "月份日期";
                            break;
                        case 4:
                            msg = "不清楚日期";
                            break;
                    }
                    ICell cell3 = irow2.CreateCell(i *2+ 2 );
                    cell3.SetCellValue(msg);
                    cell3.CellStyle = cellStylebt;
                    ICell cell4 = irow2.CreateCell(i * 2 + 3);
                    cell4.CellStyle = cellStylebt;
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, i * 2 + 2, i * 2 + 3));
                }
                 IRow irow3 = sheet.CreateRow(2);
                for (int i = 0; i < 11; i++)
                {

                    string msg = "";
                    if (i ==0)
                    {
                        msg = "数量";
                    }
                    else
                    {
                        if (i % 2 == 0)
                        {
                            msg = "占比";
                        }
                        else
                        {
                            msg = "数量";
                        }
                    }
                    ICell cell4 = irow3.CreateCell(i+1);
                    cell4.SetCellValue(msg);
                    cell4.CellStyle = cellStylebt;
                    
                }
                List<Date> dt1 = product.dates;
                if (dt1!=null )
                {
                    for (int i=0;i < dt1.Count;i ++)
                    {
                        IRow irow4 = sheet.CreateRow(3 + i);
                        string msg = "";
                        for (int j = 0; j < 12; j ++)
                        {
                            switch (j )
                            {
                                case 0:
                                    msg = dt1[i].name;
                                    break;
                                case 1:
                                    msg = dt1[i].total.ToString();
                                    break;
                                case 2:
                                    msg = dt1[i].MonthCount1 .ToString ();
                                    break;
                                case 3:
                                    msg = dt1[i].MonthCountmix1 .ToString();
                                    break;
                                case 4:
                                    msg = dt1[i].MonthCount2.ToString();
                                    break;
                                case 5:
                                    msg = dt1[i].MonthCountmix2.ToString();
                                    break;
                                case 6:
                                    msg = dt1[i].MonthCount3.ToString();
                                    break;
                                case 7:
                                    msg = dt1[i].MonthCountmix3.ToString();
                                    break;
                                case 8:
                                    msg = dt1[i].MonthCount4.ToString();
                                    break;
                                case 9:
                                    msg = dt1[i].MonthCountmix4.ToString();
                                    break;
                                case 10:
                                    msg = dt1[i].MonthCount5.ToString();
                                    break;
                                case 11:
                                    msg = dt1[i].MonthCountmix5.ToString();
                                    break;
                                   
                            }
                            ICell cell5 = irow4.CreateCell(j);
                            cell5.SetCellValue(msg);
                            cell5.CellStyle = cellStylebt;
                        }
                    }
                }
                int t = dt1.Count + 2;int count = 0;
                List<Factory> Factory1 = product.factory;
                List<Factory> Factory2 = product.problem;
                List<Factory> Factory3 = product.product;
                int factory = 0, problem = 0, productcode = 0;
                if (Factory1 != null)
                    factory = (Factory1.Count / 2) + (0 == Factory1.Count % 2 ? 0 : 1);  
                if (Factory2 != null)
                    problem = Factory2.Count;
                if (Factory3 != null)
                    productcode = Factory3.Count;
                bool istrue = true; int a = 0, total = problem + productcode;
                if (factory>total)
                {
                    count = factory;
                   a = factory - total;
                }
                else
                {
                    istrue = false;
                    count = total;
                }
                IRow irow5 = sheet.CreateRow(t+1);
                ICell cell7 = irow5.CreateCell(0);
                cell7.SetCellValue("工\n厂\n投\n诉\n占\n比\n");
                cell7.CellStyle = cellStylebt;
                cell7.CellStyle.WrapText=true ;
                sheet.AddMergedRegion(new CellRangeAddress(t+1, t+ count, 0, 0));
                SetMergedRegionBorders(sheet, new CellRangeAddress(t + 1, t + count, 0, 0), workbook);
                ICell cell10 = irow5.CreateCell(7);
                cell10.SetCellValue("各\n质\n量\n问\n题\n占\n比\n");
                cell10.CellStyle = cellStylebt;
                cell10.CellStyle.WrapText = true;
                int structure = t + problem;
                if (istrue)
                {
                  if (a >1)
                    {
                        structure = t + problem + a / 2;
                        sheet.AddMergedRegion(new CellRangeAddress(t+1, structure, 7, 7));
                       
                    }
                       
                  else
                    {
                        structure = t + problem;
                        sheet.AddMergedRegion(new CellRangeAddress(t+1, structure, 7, 7));
                    
                    }
                }
                    else
                {
                    sheet.AddMergedRegion(new CellRangeAddress(t + 1, structure, 7, 7));
                    
                }
              
                IRow irow7 = sheet.CreateRow(structure+1);
                ICell cell11 = irow7.CreateCell(7);
                cell11.SetCellValue("结\n构\n占\n比\n");
                cell11.CellStyle = cellStylebt;
                cell11.CellStyle.WrapText = true;
                sheet.AddMergedRegion(new CellRangeAddress(structure + 1, t + count, 7, 7));
                int index = 0;
                if (Factory1 != null)
                {
                    for (int i = 0; i < count; i++)
                    {
                        if (i ==0 || t +1+ i== structure+1)
                        {
                             if (Factory1.Count > i*2)
                            {
                                for (int j = 0; j < 6; j++)
                                {
                                    string msg = ReturnMsg(j, i, Factory1);
                                    if (i == 0)
                                    {
                                        ICell cell8 = irow5.CreateCell(j + 1);
                                        cell8.SetCellValue(msg);
                                        cell8.CellStyle = cellStylebt;
                                    }
                                    else
                                    {
                                        ICell cell9 = irow7.CreateCell(j + 1);
                                        cell9.SetCellValue(msg);
                                        cell9.CellStyle = cellStylebt;
                                    }
                                }
                            }
                               
                            if (i ==0)
                            {
                                for (int z = 0; z < 3; z++)
                                {
                                    switch (z)
                                    {
                                        case 0:
                                            ICell cell12 = irow5.CreateCell(8);
                                            cell12.SetCellValue(Factory2[0].name );
                                            cell12.CellStyle = cellStylebt;
                                            sheet.AddMergedRegion(new CellRangeAddress(t+1, t+1 , 8, 9));
                                            ICell cell22 = irow5.CreateCell(9);
                                            cell22.CellStyle = cellStylebt;
                                            break;
                                        case 1:
                                            ICell cell13 = irow5.CreateCell(10);
                                            cell13.SetCellValue(Factory2[0].number .ToString ());
                                            cell13.CellStyle = cellStylebt;
                                           
                                            break;
                                        case 2:
                                            ICell cell14 = irow5.CreateCell(11);
                                            cell14.SetCellValue(Factory2[0].proportion);
                                            cell14.CellStyle = cellStylebt;
                                            break;
                                    }
                                    
                                }
                            }
                            else 
                            {
                                ICell cell119 = irow7.CreateCell(0);
                                cell119.CellStyle = cellStylebt;
                                for (int z = 0; z < 3; z++)
                                {
                                    switch (z)
                                    {
                                        case 0:
                                            ICell cell15 = irow7.CreateCell(8);
                                            cell15.SetCellValue(Factory3[0].name);
                                            cell15.CellStyle = cellStylebt;
                                            sheet.AddMergedRegion(new CellRangeAddress(structure+1, structure+1, 8, 9));
                                            ICell cell31 = irow7.CreateCell(9);
                                            cell15.CellStyle = cellStylebt;
                                            break;
                                        case 1:
                                            ICell cell16 = irow7.CreateCell(10);
                                            cell16.SetCellValue(Factory3[0].number.ToString());
                                            cell16.CellStyle = cellStylebt;

                                            break;
                                        case 2:
                                            ICell cell17= irow7.CreateCell(11);
                                            cell17.SetCellValue(Factory3[0].proportion);
                                            cell17.CellStyle = cellStylebt;
                                            break;
                                    }

                                }
                                index++;
                            }
                           
                        }
                        else
                        {
                            
                            IRow irow6 = sheet.CreateRow(t+1 + i);
                            if (Factory1.Count %2!=0)
                            {
                                if (i * 2+1 < Factory1.Count)
                                {
                                    for (int j = 0; j < 6; j++)
                                    {
                                        string msg = ReturnMsg(j, i, Factory1);
                                        ICell cell18 = irow6.CreateCell(j + 1);
                                        cell18.SetCellValue(msg);
                                        cell18.CellStyle = cellStylebt;
                                    }
                                }
                                else if (i * 2 + 1 == Factory1.Count)
                                {
                                    for (int j = 0; j < 3; j++)
                                    {
                                        string msg = ReturnMsg(j, i, Factory1);
                                        ICell cell18 = irow6.CreateCell(j + 1);
                                        cell18.SetCellValue(msg);
                                        cell18.CellStyle = cellStylebt;
                                    }
                                }

                            }
                            else
                            {
                                if (i * 2 < Factory1.Count)
                                {
                                    for (int j = 0; j < 6; j++)
                                    {
                                        string msg = ReturnMsg(j, i, Factory1);
                                        ICell cell18 = irow6.CreateCell(j + 1);
                                        cell18.SetCellValue(msg);
                                        cell18.CellStyle = cellStylebt;
                                    }
                                }
                            }
                           
                                int b = 0;
                                if (a >1)
                                {
                                    b= (a / 2) + (0 == a % 2 ? 0 : 1);
                                }
                                if (i < Factory2.Count -1)
                                {
                                    for (int z = 0; z < 3; z++)
                                    {
                                        switch (z)
                                        {
                                            case 0:
                                                ICell cell8 = irow6.CreateCell(8);
                                                cell8.SetCellValue(Factory2[i].name);
                                                cell8.CellStyle = cellStylebt;
                                                sheet.AddMergedRegion(new CellRangeAddress(t+1 + i, t+1 + i, 8, 9));
                                                ICell cell22 = irow6.CreateCell(9);
                                                cell22.CellStyle = cellStylebt;
                                                break;
                                            case 1:
                                                ICell cell9 = irow6.CreateCell(10);
                                                cell9.SetCellValue(Factory2[i].number.ToString());
                                                cell9.CellStyle = cellStylebt;

                                                break;
                                            case 2:
                                                ICell cell12 = irow6.CreateCell(11);
                                                cell12.SetCellValue(Factory2[i].proportion);
                                                cell12.CellStyle = cellStylebt;
                                                break;
                                        }
                                    }
                                }
                               else  if (i == Factory2.Count-1 )
                                {
                                    for (int z = 0; z < 3; z++)
                                    {
                                        string msg = "";
                                        switch (z)
                                        {
                                            case 0:
                                                ICell cell8 = irow6.CreateCell(8);
                                                cell8.SetCellValue(Factory2[i].name);
                                                cell8.CellStyle = cellStylebt;
                                                sheet.AddMergedRegion(new CellRangeAddress(t +1+ i, structure, 8, 9));
                                            if (istrue )
                                            {
                                                ICell cell22 = irow6.CreateCell(9);
                                                cell22.CellStyle = cellStylebt;
                                            }
                                                break;
                                            case 1:
                                                ICell cell9 = irow6.CreateCell(10);
                                                cell9.SetCellValue(Factory2[i].number.ToString());
                                                cell9.CellStyle = cellStylebt;
                                                sheet.AddMergedRegion(new CellRangeAddress(t+1 + i, structure, 10, 10));

                                                break;
                                            case 2:
                                                ICell cell12 = irow6.CreateCell(11);
                                                cell12.SetCellValue(Factory2[i].proportion);
                                                cell12.CellStyle = cellStylebt;
                                                sheet.AddMergedRegion(new CellRangeAddress(t+1 + i, structure, 11, 11));
                                                break;
                                        }
                                    
                                    }
                                }
                                if (t +1+ i>structure + 1&& t + 1 + i < structure+ Factory3.Count)
                                {
                                    for (int z = 0; z < 3; z++)
                                    {
                                       
                                        switch (z)
                                        {
                                            case 0:
                                                ICell cell8 = irow6.CreateCell(8);
                                                cell8.SetCellValue(Factory3[index].name);
                                                cell8.CellStyle = cellStylebt;
                                                sheet.AddMergedRegion(new CellRangeAddress(t+1+i, t + 1 + i, 8, 9));
                                                ICell cel27 = irow6.CreateCell(9);
                                                cel27.CellStyle = cellStylebt;
                                                break;
                                            case 1:
                                                ICell cell9 = irow6.CreateCell(10);
                                                cell9.SetCellValue(Factory3[index].number.ToString());
                                                cell9.CellStyle = cellStylebt;

                                                break;
                                            case 2:
                                                ICell cell12 = irow6.CreateCell(11);
                                                cell12.SetCellValue(Factory3[index].proportion);
                                                cell12.CellStyle = cellStylebt;
                                                break;
                                        }
                                    }
                                    index++;
                                }
                                else  if (t + 1 + i == structure + Factory3.Count)
                                {
                                    for (int z = 0; z < 3; z++)
                                    {
                                        switch (z)
                                        {
                                            case 0:
                                                ICell cell8 = irow6.CreateCell(8);
                                                cell8.SetCellValue(Factory3[index].name);
                                                cell8.CellStyle = cellStylebt;
                                          
                                                sheet.AddMergedRegion(new CellRangeAddress(t + 1 + i, t + count, 8, 9));
                                            SetMergedRegionBorders(sheet, new CellRangeAddress(t + 1 + i, t + 1 + i, 8, 9), workbook);
                                            break;
                                            case 1:
                                                ICell cell9 = irow6.CreateCell(10);
                                                cell9.SetCellValue(Factory3[index].number.ToString());
                                                cell9.CellStyle = cellStylebt;
                                          
                                            sheet.AddMergedRegion(new CellRangeAddress(t + 1 + i, t + count, 10, 10));
                                             SetMergedRegionBorders(sheet, new CellRangeAddress(t + 1 + i, t + 1 + i, 10, 10), workbook);
                                            break;

                                        case 2:
                                                ICell cell12 = irow6.CreateCell(11);
                                                cell12.SetCellValue(Factory3[index].proportion);
                                                cell12.CellStyle = cellStylebt;
                                           
                                            sheet.AddMergedRegion(new CellRangeAddress(t + 1 + i, t + count, 11, 11));
                                            SetMergedRegionBorders(sheet, new CellRangeAddress(t + 1 + i, t + 1 + i, 11, 11), workbook);
                                            break;
                                        }
                                    }
                                }

                            
                            if ( i == Factory2.Count)
                            {
                                ICell cell120 = irow6.CreateCell(7);
                                cell120.CellStyle = cellStylebt;
                                ICell cell124 = irow6.CreateCell(9);
                                cell124.CellStyle = cellStylebt;
                                    ICell cell125 = irow6.CreateCell(10);
                                    cell125.CellStyle = cellStylebt;
                                    ICell cell126 = irow6.CreateCell(11);
                                    cell126.CellStyle = cellStylebt;
                                
                            }
                            if (!istrue )
                            {
                                if (i * 2>= Factory1.Count)
                                {
                                    ICell cell119 = irow6.CreateCell(0);
                                    cell119.CellStyle = cellStylebt;
                                   
                                }
                                if (i *2 +1>= Factory1.Count&i < Factory2.Count)
                                {
                                    ICell cell119 = irow6.CreateCell(7);
                                    cell119.CellStyle = cellStylebt;
                                }
                                if (i > Factory2.Count)
                                {
                                    ICell cell119 = irow6.CreateCell(7);
                                    cell119.CellStyle = cellStylebt;
                                }
                                if (i == count - 1)
                                {
                                    ICell cell119 = irow6.CreateCell(1);
                                    cell119.CellStyle = cellStylebt1;
                                    ICell cell121 = irow6.CreateCell(2);
                                    cell121.CellStyle = cellStylebt1;
                                    ICell cell132 = irow6.CreateCell(3);
                                    cell132.CellStyle = cellStylebt1;
                                    ICell cell128 = irow6.CreateCell(4);
                                    cell128.CellStyle = cellStylebt1;
                                    ICell cell129 = irow6.CreateCell(5);
                                    cell129.CellStyle = cellStylebt1;
                                    ICell cell130 = irow6.CreateCell(6);
                                    cell130.CellStyle = cellStylebt1;
                                    ICell cell131 = irow6.CreateCell(9);
                                    cell131.CellStyle = cellStylebt1;
                                }
                            }
                            else  if (i == count - 1)
                            {
                                ICell cell119 = irow6.CreateCell(0);
                                cell119.CellStyle = cellStylebt;
                                ICell cell121 = irow6.CreateCell(7);
                                cell121.CellStyle = cellStylebt;
                                ICell cell122 = irow6.CreateCell(4);
                                cell122.CellStyle = cellStylebt;
                                ICell cell123 = irow6.CreateCell(5);
                                cell123.CellStyle = cellStylebt;
                                ICell cell124 = irow6.CreateCell(6);
                                cell124.CellStyle = cellStylebt;
                                sheet.AddMergedRegion(new CellRangeAddress(t + 1 + i, t + 1 + i, 4, 6));
                               
                             //   sheet.AddMergedRegion(new CellRangeAddress(t + 1 + i, t + 1 + i, 8, 11));
                            }
                            if (t + 1 + i >structure + Factory3.Count)
                            {
                                ICell cell132 = irow6.CreateCell(8);
                                cell132.CellStyle = cellStylebt;
                                ICell cell128 = irow6.CreateCell(9);
                                cell128.CellStyle = cellStylebt;
                                ICell cell129 = irow6.CreateCell(10);
                                cell129.CellStyle = cellStylebt;
                                ICell cell130 = irow6.CreateCell(11);
                                cell130.CellStyle = cellStylebt;
                            }
                        }

                    }
                }
            




                #endregion



                //自适应列宽度
                for (int i = 0; i < 12; i++)
                {
                 //  sheet.AutoSizeColumn(i);
                    sheet.SetColumnWidth(i, 15 * 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("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 "导出失败！";
              
            }
        }
        public string Complainthandling(DateTime stime, DateTime etime, List<Complaint>  complaints,string maxarea,string minarea)
        {
            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;
                cellfont.FontHeight = 300;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;


                #region 标题
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
                cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeight = 500;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("各大区投诉处理情况（"+ stime.Month + "月"+ stime .Day + "-"+ etime .Day + "日）");
                cell1.CellStyle = cellStylebt1;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
                #endregion
                #region 说明
                ICellStyle cellStylebt2 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt2 = workbook.CreateFont();
                cellfontbt2.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt2.FontHeight = 300;
                cellStylebt2.SetFont(cellfontbt2);
                cellStylebt2.WrapText = true;//设置换行这个要先设置
                cellStylebt2.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt2.Alignment = HorizontalAlignment.Center;
                IRow irow2 = sheet.CreateRow(1);
                ICell cell2 = irow2.CreateCell(0);
              
                cell2.SetCellValue("   " + stime.Month + "月" + stime.Day + "日-" + etime.Month + "月" + + etime.Day + "日共接400热线投诉"+ complaints [complaints.Count -1].complaintnumber + "起，" +
                    "待分派投诉"+ complaints[complaints.Count - 1].assignmentnumber + "起，待处理投诉" + complaints[complaints.Count - 1].pendingnumber + "起，已处理投诉" + complaints[complaints.Count - 1].processednumber + "起，占比" + complaints[complaints.Count - 1].processedrate + "%，处理中投诉" + complaints[complaints.Count - 1].processingnumber + "起，占比" + complaints[complaints.Count - 1].processingnumberrate + "。19个大区中，"+ maxarea + "大区处理率最高，"+minarea +"大区处理率最低。");
                cell2.CellStyle = cellStylebt2;
                sheet.AddMergedRegion(new CellRangeAddress(1, 5, 0, 10));
                #endregion
                ICellStyle cellStylebt3 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
                cellfontbt3.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt3.FontHeight = 200;
                cellStylebt3.SetFont(cellfontbt3);
                cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                IRow irow3 = sheet.CreateRow(6);
                string [] cols= { "序号", "大区", "投诉起数", "待分派投诉", "当日分派率", "待处理投诉", "处理中投诉", "已处理投诉", "处理率", "公关经理跟单率" };
                int icolIndex = 0;
                foreach (string dc in cols)
                {
                    ICell cell = irow3.CreateCell(icolIndex);
                    cell.SetCellValue(dc);
                    cell.CellStyle = cellStylebt3;
                    icolIndex++;
                }
                for (int i=0;i < complaints.Count -1;i ++)
                {
                    IRow irow4 = sheet.CreateRow(7+i );
                   for (int j=0; j < 10; j++)
                    {
                        switch (j )
                        {
                            case 0:
                                ICell cell = irow4.CreateCell(j);
                                cell.SetCellValue(complaints[i].serialnumbe);
                                cell.CellStyle = cellStylebt;
                                
                                break;
                            case 1:
                                ICell cell4 = irow4.CreateCell(j);
                                cell4.SetCellValue(complaints[i].area);
                                cell4.CellStyle = cellStylebt;
                                break;
                            case 2:
                                ICell cel5 = irow4.CreateCell(j);
                                cel5.SetCellValue(complaints[i].complaintnumber);
                                cel5.CellStyle = cellStylebt;
                                break;
                            case 3:
                                ICell cel6 = irow4.CreateCell(j);
                                cel6.SetCellValue(complaints[i].assignmentnumber);
                                cel6.CellStyle = cellStylebt;
                                break;
                            case 4:
                                ICell cel7 = irow4.CreateCell(j);
                                cel7.SetCellValue(complaints[i].assignmentrate);
                                cel7.CellStyle = cellStylebt;
                                break;
                            case 5:
                                ICell cel8 = irow4.CreateCell(j);
                                cel8.SetCellValue(complaints[i].pendingnumber);
                                cel8.CellStyle = cellStylebt;
                                break;
                            case 6:
                                ICell cel9 = irow4.CreateCell(j);
                                cel9.SetCellValue(complaints[i].processingnumber);
                                cel9.CellStyle = cellStylebt;
                                break;
                            case 7:
                                ICell cell0 = irow4.CreateCell(j);
                                cell0.SetCellValue(complaints[i].processednumber);
                                cell0.CellStyle = cellStylebt;
                                break;
                            case 8:
                                ICell cell11 = irow4.CreateCell(j);
                                cell11.SetCellValue(complaints[i].processedrate);
                                cell11.CellStyle = cellStylebt;
                                break;
                            case 9:
                                ICell cell12 = irow4.CreateCell(j);
                                cell12.SetCellValue(complaints[i].documentaryrate);
                                cell12.CellStyle = cellStylebt;
                                break;
                         
                        }
                       
                    }
                   
                  

                }

                IRow irow5 = sheet.CreateRow(7+ complaints.Count - 1);

                for (int j = 0; j < 9; j++)
                {
                    switch (j)
                    {
                        case 0:
                            ICell cell = irow5.CreateCell(j);
                            cell.SetCellValue(complaints[complaints.Count -1].area );
                            cell.CellStyle = cellStylebt;
                            sheet.AddMergedRegion(new CellRangeAddress(7 + complaints.Count - 1, 7 + complaints.Count - 1, 0, 1));
                            break;
                        case 1:
                            ICell cel5 = irow5.CreateCell(j+1);
                            cel5.SetCellValue(complaints[complaints.Count - 1].complaintnumber);
                            cel5.CellStyle = cellStylebt;
                            break;
                        case 2:
                            ICell cel6 = irow5.CreateCell(j+1);
                            cel6.SetCellValue(complaints[complaints.Count - 1].assignmentnumber);
                            cel6.CellStyle = cellStylebt;
                            break;
                        case 3:
                            ICell cel7 = irow5.CreateCell(j+1);
                            cel7.SetCellValue(complaints[complaints.Count - 1].assignmentrate);
                            cel7.CellStyle = cellStylebt;
                            break;
                        case 4:
                            ICell cel8 = irow5.CreateCell(j+1);
                            cel8.SetCellValue(complaints[complaints.Count - 1].pendingnumber);
                            cel8.CellStyle = cellStylebt;
                            break;
                        case 5:
                            ICell cel9 = irow5.CreateCell(j+1);
                            cel9.SetCellValue(complaints[complaints.Count - 1].processingnumber);
                            cel9.CellStyle = cellStylebt;
                            break;
                        case 6:
                            ICell cell0 = irow5.CreateCell(j+1);
                            cell0.SetCellValue(complaints[complaints.Count - 1].processednumber);
                            cell0.CellStyle = cellStylebt;
                            break;
                        case 7:
                            ICell cell11 = irow5.CreateCell(j+1);
                            cell11.SetCellValue(complaints[complaints.Count - 1].processedrate);
                            cell11.CellStyle = cellStylebt;
                            break;
                        case 8:
                            ICell cell12 = irow5.CreateCell(j+1);
                            cell12.SetCellValue(complaints[complaints.Count - 1].documentaryrate);
                            cell12.CellStyle = cellStylebt;
                            break;
                    }

                }




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

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("各大区投诉处理情况" + ".xls", Encoding.UTF8));

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

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

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

        public string AftersaleToExcel(DateTime stime, DateTime etime, List<Aftersale> complaints, string maxarea, string minarea,int pendingnumber,int Tobeassignment)
        {
            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;
                cellfont.FontHeight = 300;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;


                #region 标题
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
                cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeight = 500;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("售后APP工单进度统计表（" + stime.Month + "月" + stime.Day + "-" + etime.Month + "月" + etime.Day + "日）");
                cell1.CellStyle = cellStylebt1;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 11));
                #endregion
                #region 说明
                ICellStyle cellStylebt2 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt2 = workbook.CreateFont();
                cellfontbt2.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt2.FontHeight = 300;
                cellStylebt2.SetFont(cellfontbt2);
                cellStylebt2.WrapText = true;//设置换行这个要先设置
                cellStylebt2.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt2.Alignment = HorizontalAlignment.Center;
                IRow irow2 = sheet.CreateRow(1);
                ICell cell2 = irow2.CreateCell(0);

                cell2.SetCellValue("   "+ stime.Year +"年" + stime.Month + "月" + stime.Day + "日-" + etime.Month + "月" + +etime.Day + "日共有售后APP工单" + complaints[complaints.Count - 1].total  + "条，" +
                    "已完结工单" + complaints[complaints.Count - 1].processednumber + "条，完结率" + complaints[complaints.Count - 1].processedrate + "，待分派工单" + complaints[complaints.Count - 1].assignmentnumber + "条，待处理工单" + pendingnumber + "条。19个大区中，" + maxarea + "大区处理率最高，" + minarea + "大区处理率最低。目前待分派"+ Tobeassignment+"条");
                cell2.CellStyle = cellStylebt2;
                sheet.AddMergedRegion(new CellRangeAddress(1, 5, 0, 11));
                #endregion
                #region 
                ICellStyle cellStylebt3 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
                cellfontbt3.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt3.FontHeight = 200;
                cellStylebt3.WrapText = true;//设置换行这个要先设置
                cellStylebt3.SetFont(cellfontbt3);
                cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                IRow irow3 = sheet.CreateRow(6);
                IRow irow6 = sheet.CreateRow(7);
                ICell title1 = irow3.CreateCell(0);
                title1.SetCellValue("序号");
                title1.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 0, 0));
                ICell title12 = irow3.CreateCell(1);
                title12.SetCellValue("大区");
                title12.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 1, 1));
                ICell title13 = irow3.CreateCell(2);
                title13.SetCellValue("工单总数\n（条）");
                title13.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 2, 2));
                ICell title14= irow3.CreateCell(3);
                title14.SetCellValue("办事处经理当日分派情况");
                title14.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 6, 3, 4));
                ICell title15 = irow6.CreateCell(3);
                title15.SetCellValue("起数（条）");
                title15.CellStyle = cellStylebt3;
                ICell title16 = irow6.CreateCell(4);
                title16.SetCellValue("分派率");
                title16.CellStyle = cellStylebt3;
                ICell title17= irow3.CreateCell(5);
                title17.SetCellValue("业务员当日接单情况");
                title17.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 6, 5, 6));
                ICell title18 = irow6.CreateCell(5);
                title18.SetCellValue("起数（条）");
                title18.CellStyle = cellStylebt3;
                ICell title19 = irow6.CreateCell(6);
                title19.SetCellValue("接单率");
                title19.CellStyle = cellStylebt3;

                ICell title110 = irow3.CreateCell(7);
                title110.SetCellValue("处理中工单（条）");
                title110.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 7, 7));

                ICell title111 = irow3.CreateCell(8);
                title111.SetCellValue("已完结工单起数（条）");
                title111.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 8, 8));
                ICell title112 = irow3.CreateCell(9);
                title112.SetCellValue("完结率");
                title112.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 9, 9));
                ICell title113 = irow3.CreateCell(10);
                title113.SetCellValue("公关经理催单率");
                title113.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 10, 10));
                ICell title114 = irow3.CreateCell(11);
                title114.SetCellValue("公关经理");
                title114.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(6, 7, 11, 11));

                #endregion
                for (int i = 0; i < complaints.Count ; i++)
                {
                    IRow irow4 = sheet.CreateRow(8 + i);
                    for (int j = 0; j < 13; j++)
                    {
                        switch (j)
                        {
                            case 0:
                                ICell cell3 = irow4.CreateCell(j);
                                cell3.SetCellValue(complaints[i].serialnumbe);
                                cell3.CellStyle = cellStylebt;

                                break;
                            case 1:
                                ICell cell4 = irow4.CreateCell(j);
                                cell4.SetCellValue(complaints[i].area);
                                cell4.CellStyle = cellStylebt;
                                break;
                            case 2:
                                ICell cel5 = irow4.CreateCell(j);
                                cel5.SetCellValue(complaints[i].total);
                                cel5.CellStyle = cellStylebt;
                                break;
                            case 3:
                                ICell cel6 = irow4.CreateCell(j);
                                cel6.SetCellValue(complaints[i].assignmentnumber);
                                cel6.CellStyle = cellStylebt;
                                break;
                            case 4:
                                ICell cel7 = irow4.CreateCell(j);
                                cel7.SetCellValue(complaints[i].assignmentrate);
                                cel7.CellStyle = cellStylebt;
                                break;
                            case 5:
                                ICell cel8 = irow4.CreateCell(j);
                                cel8.SetCellValue(complaints[i].receiving);
                                cel8.CellStyle = cellStylebt;
                                break;
                            case 6:
                                ICell cel9 = irow4.CreateCell(j);
                                cel9.SetCellValue(complaints[i].receivingrate);
                                cel9.CellStyle = cellStylebt;
                                break;
                            case 7:
                                ICell cell0 = irow4.CreateCell(j);
                                cell0.SetCellValue(complaints[i].processingnumber);
                                cell0.CellStyle = cellStylebt;
                                break;
                            case 8:
                                ICell cell11 = irow4.CreateCell(j);
                                cell11.SetCellValue(complaints[i].processednumber);
                                cell11.CellStyle = cellStylebt;
                                break;
                            case 9:
                                ICell cell12 = irow4.CreateCell(j);
                                cell12.SetCellValue(complaints[i].processedrate);
                                cell12.CellStyle = cellStylebt;
                                break;
                            case 10:
                                ICell cell13 = irow4.CreateCell(j);
                                cell13.SetCellValue(complaints[i].reminderrate);
                                cell13.CellStyle = cellStylebt;
                                break;
                            case 11:
                                ICell cell14 = irow4.CreateCell(j);
                                cell14.SetCellValue(complaints[i].name);
                                cell14.CellStyle = cellStylebt;
                                break;
                        }
                    }
                }
                //自适应列宽度
                for (int i = 0; i < 10; i++)
                {
                    //
                    sheet.SetColumnWidth(i, 12 * 400);
                }

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("售后APP工单进度统计表" + ".xls", Encoding.UTF8));

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

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

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




        public string TSDBToExcel(  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.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
              //  cellfont.FontHeightInPoints = 17;
                cellfont.FontHeight = 300;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;


                #region 标题
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
              //  cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeight = 500;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("各生产单位市场投诉对比情况");
                cell1.CellStyle = cellStylebt1;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 12));
                #endregion
              
              
                #region 
                ICellStyle cellStylebt3 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
              //  cellfontbt3.Boldweight = (short)FontBoldWeight.Bold;
             //   cellfontbt3.FontHeight = 400;
                cellfontbt3.FontHeightInPoints = 15;
                cellStylebt3.WrapText = true;//设置换行这个要先设置
                cellStylebt3.SetFont(cellfontbt3);
                cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                IRow irow3 = sheet.CreateRow(1);
                IRow irow6 = sheet.CreateRow(2);

                ICell title1 = irow3.CreateCell(0);
                title1.SetCellValue("生产厂家");
                title1.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
                ICell title12 = irow3.CreateCell(1);
                title12.SetCellValue("总投诉");
                title12.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 3));

                ICell title13 = irow3.CreateCell(4);
                title13.SetCellValue("杂质异物类(Z**)");
                title13.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 6));
                ICell title14 = irow3.CreateCell(7);
                title14.SetCellValue("破袋发霉类(P)");
                title14.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 7, 9));
                ICell title15 = irow3.CreateCell(10);
                title15.SetCellValue("变质异味类(B)");
                title15.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 10, 12));


                ICell title16 = irow6.CreateCell(1);
                title16.SetCellValue("起数");
                title16.CellStyle = cellStylebt3;

                ICell title17 = irow6.CreateCell(2);
                title17.SetCellValue("同期");
                title17.CellStyle = cellStylebt3;
               
                ICell title18 = irow6.CreateCell(3);
                title18.SetCellValue("增幅%");
                title18.CellStyle = cellStylebt3;

                ICell title19 = irow6.CreateCell(4);
                title19.SetCellValue("起数");
                title19.CellStyle = cellStylebt3;

                ICell title110 = irow6.CreateCell(5);
                title110.SetCellValue("同期");
                title110.CellStyle = cellStylebt3;
             

                ICell title111 = irow6.CreateCell(6);
                title111.SetCellValue("增幅%");
                title111.CellStyle = cellStylebt3;
          
                ICell title112 = irow6.CreateCell(7);
                title112.SetCellValue("起数");
                title112.CellStyle = cellStylebt3;

                ICell title113 = irow6.CreateCell(8);
                title113.SetCellValue("同期");
                title113.CellStyle = cellStylebt3;
          
                ICell title114 = irow6.CreateCell(9);
                title114.SetCellValue("增幅%");
                title114.CellStyle = cellStylebt3;


                ICell title1122 = irow6.CreateCell(10);
                title1122.SetCellValue("起数");
                title1122.CellStyle = cellStylebt3;

                ICell title1133 = irow6.CreateCell(11);
                title1133.SetCellValue("同期");
                title1133.CellStyle = cellStylebt3;

                ICell title1144 = irow6.CreateCell(12);
                title1144.SetCellValue("增幅%");
                title1144.CellStyle = cellStylebt3;


                #endregion
                //建立内容行
                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++;
                }

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

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("各生产单位市场投诉对比情况" + ".xls", Encoding.UTF8));

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

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

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

        public string Qualityproblem(DataTable dt, List<qualityproblem> jo,int size)
        {
            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;
                cellfont.FontHeight = 300;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;


                #region 标题
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
                cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeight = 500;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("质量问题情况统计分析表");
                cell1.CellStyle = cellStylebt1;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, size));
                #endregion
                #region 说明
                ICellStyle cellStylebt2 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt2 = workbook.CreateFont();
                //cellfontbt2.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt2.FontHeight = 300;
                cellStylebt2.SetFont(cellfontbt2);
                cellStylebt2.WrapText = true;//设置换行这个要先设置
                cellStylebt2.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt2.Alignment = HorizontalAlignment.Center;
                IRow irow2 = sheet.CreateRow(1);
                IRow irow3 = sheet.CreateRow(2);
                int index = 0;
                foreach (var it  in jo)
                {
                    if (it.junior.Count==1&& it.junior[0].name == it.junior[0].type)
                    {
                        ICell cell2 = irow2.CreateCell(index);
                        cell2.SetCellValue(it.name);
                        cell2.CellStyle = cellStylebt2;
                        sheet.AddMergedRegion(new CellRangeAddress(1, 2, index, index));
                    }
                    else
                    {
                        ICell cell2 = irow2.CreateCell(index);
                        cell2.SetCellValue(it.name);
                        cell2.CellStyle = cellStylebt2;
                        sheet.AddMergedRegion(new CellRangeAddress(1, 1, index, index+ it.junior.Count-1));
                        for(int i=0;i < it.junior.Count;i++)
                        {
                            ICell cell3 = irow3.CreateCell(index);
                            cell3.SetCellValue(it.junior[i].name);
                            cell3.CellStyle = cellStylebt2;
                            index = index + 1;
                        }
                        index = index -1;
                    }
                    index++;
                }
                #endregion
               
                DataView view = dt.DefaultView;
                view.RowFilter = "生产厂家 <> '肉制品事业部'"; // 设置筛选条件
                view.Sort = "序号 ASC"; // 设置排序条件
                DataTable dt1 = view.ToTable();  
               // var dt1 = dt.Select("生产厂家 <> '合计'");
               
                int iRowIndex = 2;
                foreach (DataRow dr in dt1.Rows)
                {
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex + 1);
                    for (int i = 0; i < dt1.Rows.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++;
                }
                IRow irow4 = sheet.CreateRow(iRowIndex );
                var dt2 = dt.Select("生产厂家 ='肉制品事业部'");
                int iCellIndex1 = 0;
                for (int i = 0; i < dt.Columns.Count; i++)
                { 
                    if (i==0)
                    {
                        ICell cell = irow4.CreateCell(iCellIndex1);
                        cell.SetCellValue("肉制品事业部");
                        cell.CellStyle = cellStyle;
                        sheet.AddMergedRegion(new CellRangeAddress(iRowIndex, iRowIndex, 0,1));
                    }
                    else if (i==1)
                    {
                        iCellIndex1++;
                        continue;
                    }
                    else
                    {
                         string strsj = string.Empty;
                        if (dt2[0][i] != null)
                        {
                            strsj = dt2[0][i].ToString();
                        }
                        ICell cell = irow4.CreateCell(iCellIndex1);
                        cell.SetCellValue(strsj);
                        cell.CellStyle = cellStyle;
                    }
                   
                    iCellIndex1++;
                }

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

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("质量问题情况统计分析表" + ".xls", Encoding.UTF8));

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

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

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


        public string QualityComplaints(List<qualityComplaints> data)
        {
            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.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                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.BorderDiagonalLineStyle = BorderStyle.Thin;
                //  cellfont.FontHeightInPoints = 17;
                cellfont.FontHeight = 300;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                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;
                cellStylebt.BorderDiagonalLineStyle = BorderStyle.Thin;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;

                //ICellStyle frame = workbook.CreateCellStyle();
                //frame.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                //frame.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                //frame.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                //frame.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
              
                #region 标题
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
                //  cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeight = 500;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                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.BorderDiagonalLineStyle = BorderStyle.Thin;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("年度质量投诉问题情况对比");
                cell1.CellStyle = cellStylebt1;
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 16);
                sheet.AddMergedRegion(region);
                SetMergedRegionBorders(sheet, region, workbook);
                #endregion
                ICellStyle style13 = workbook.CreateCellStyle();
                style13.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderDiagonalLineStyle = BorderStyle.Thin;
                style13.BorderDiagonal = BorderDiagonal.Backward;
                style13.BorderDiagonalColor = IndexedColors.Black.Index;
                string sb = "                                项目\n时间";
                IRow irow2 = sheet.CreateRow(1);
                ICell cell2 = irow2.CreateCell(0);
                cell2.SetCellValue(sb);
                cell2.CellStyle = cellStylebt;
                style13.WrapText = true;
                irow2.GetCell(0).CellStyle = style13;
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 1));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 2, 0, 1), workbook);
                #region 
                ICellStyle cellStylebt3 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
                cellfontbt3.FontHeightInPoints = 15;
                cellStylebt3.WrapText = true;//设置换行这个要先设置
                cellStylebt3.SetFont(cellfontbt3);
                cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                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.BorderDiagonalLineStyle = BorderStyle.Thin;
                IRow irow6 = sheet.CreateRow(2);

                ICell title1 = irow2.CreateCell(2);
                title1.SetCellValue("总投诉");
                title1.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 2, 4), workbook);
                ICell title13 = irow2.CreateCell(5);
                title13.SetCellValue("破袋发霉类（P）");
                title13.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 5, 8));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 5, 8), workbook);
                ICell title14 = irow2.CreateCell(9);
                title14.SetCellValue("杂质异物类（Z**）");
                title14.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 9, 12));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 9, 12), workbook);
                ICell title15 = irow2.CreateCell(13);
                title15.SetCellValue("变质异味类（B)");
                title15.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 13, 16));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 13, 16), workbook);

                ICell title16 = irow6.CreateCell(2);
                title16.SetCellValue("起数");
                title16.CellStyle = cellStylebt3;

                ICell title17 = irow6.CreateCell(3);
                title17.SetCellValue("同期");
                title17.CellStyle = cellStylebt3;

                ICell title18 = irow6.CreateCell(4);
                title18.SetCellValue("增幅");
                title18.CellStyle = cellStylebt3;

                ICell title19 = irow6.CreateCell(5);
                title19.SetCellValue("起数");
                title19.CellStyle = cellStylebt3;

                ICell title115 = irow6.CreateCell(6);
                title115.SetCellValue("占比");
                title115.CellStyle = cellStylebt3;

                ICell title110 = irow6.CreateCell(7);
                title110.SetCellValue("同期");
                title110.CellStyle = cellStylebt3;


                ICell title111 = irow6.CreateCell(8);
                title111.SetCellValue("增幅");
                title111.CellStyle = cellStylebt3;

                ICell title112 = irow6.CreateCell(9);
                title112.SetCellValue("起数");
                title112.CellStyle = cellStylebt3;

                ICell title116 = irow6.CreateCell(10);
                title116.SetCellValue("占比");
                title116.CellStyle = cellStylebt3;

                ICell title113 = irow6.CreateCell(11);
                title113.SetCellValue("同期");
                title113.CellStyle = cellStylebt3;

                ICell title114 = irow6.CreateCell(12);
                title114.SetCellValue("增幅");
                title114.CellStyle = cellStylebt3;


                ICell title1122 = irow6.CreateCell(13);
                title1122.SetCellValue("起数");
                title1122.CellStyle = cellStylebt3;

                ICell title117 = irow6.CreateCell(14);
                title117.SetCellValue("占比");
                title117.CellStyle = cellStylebt3;

                ICell title1133 = irow6.CreateCell(15);
                title1133.SetCellValue("同期");
                title1133.CellStyle = cellStylebt3;

                ICell title1144 = irow6.CreateCell(16);
                title1144.SetCellValue("增幅");
                title1144.CellStyle = cellStylebt3;


                IRow irow3 = sheet.CreateRow(3);
                string year = data.FirstOrDefault().year;
                char specialChar = '\n';
                StringBuilder sbyear = new StringBuilder();
                for (int i = 0; i < year.Length; i++)
                {
                    sbyear.Append(year[i]);
                    if (i < year.Length - 1) 
                    {
                        sbyear.Append(specialChar);
                    }
                }
                string result = sbyear.ToString();
                ICell cell3 = irow3.CreateCell(0);
                cell3.SetCellValue(sbyear.ToString());
                cell3.CellStyle = cellStyle;
                cell3.CellStyle.WrapText = true;
                sheet.AddMergedRegion(new CellRangeAddress(3, 21, 0, 0));
                #endregion
                //建立内容行
                int iRowIndex = 3;
                foreach(var it in data )
                {
                    if (iRowIndex==3)
                    {
                        for(int i=1;i<17;i ++)
                        {
                            ICell cell = irow3.CreateCell(i);
                            cell.SetCellValue(GetQualityComplaints(it ,i ));
                            cell.CellStyle = cellStyle;
                        }
                        
                    }
                    else
                    {
                        IRow irow = sheet.CreateRow(iRowIndex);
                        if (iRowIndex == data.Count + 2)
                        {
                            ICell cell = irow.CreateCell(0);
                            cell.CellStyle = cellStyle;
                        }
                        for (int i = 1; i < 17; i++)
                        {
                            ICell cell = irow.CreateCell(i);
                            cell.SetCellValue(GetQualityComplaints(it, i));
                            cell.CellStyle = cellStyle;
                        }
                    }
                    iRowIndex++;
                }
                for (int i = 0; i < 17; i++)
                {
                    //
                    sheet.AutoSizeColumn(i);
                    // sheet.SetColumnWidth(i, 12 * 400);
                }

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("各生产单位市场投诉对比情况" + ".xls", Encoding.UTF8));

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

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

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

        public string DateOfManufacture(DataTable dt,DateTime time)
        {
            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.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                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.BorderDiagonalLineStyle = BorderStyle.Thin;
                //  cellfont.FontHeightInPoints = 17;
                cellfont.FontHeight = 300;
                cellStyle.SetFont(cellfont);
                ICellStyle cellStylebt = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt = workbook.CreateFont();
                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;
                cellStylebt.BorderDiagonalLineStyle = BorderStyle.Thin;
                cellStylebt.SetFont(cellfontbt);
                cellStylebt.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt.Alignment = HorizontalAlignment.Center;
                
                //ICellStyle frame = workbook.CreateCellStyle();
                //frame.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                //frame.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                //frame.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                //frame.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                #region 标题
                ICellStyle cellStylebt1 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt1 = workbook.CreateFont();
                //  cellfontbt1.Boldweight = (short)FontBoldWeight.Bold;
                cellfontbt1.FontHeight = 500;
                cellStylebt1.SetFont(cellfontbt1);
                cellStylebt1.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt1.Alignment = HorizontalAlignment.Center;
                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.BorderDiagonalLineStyle = BorderStyle.Thin;
                IRow irow1 = sheet.CreateRow(0);
                ICell cell1 = irow1.CreateCell(0);
                cell1.SetCellValue("近三年投诉产品生产日期分布表");
                cell1.CellStyle = cellStylebt1;
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 37);
                sheet.AddMergedRegion(region);
                SetMergedRegionBorders(sheet, region, workbook);
                #endregion
                ICellStyle style13 = workbook.CreateCellStyle();
                style13.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                style13.BorderDiagonalLineStyle = BorderStyle.Thin;
                style13.VerticalAlignment = VerticalAlignment.Center;
                style13.Alignment = HorizontalAlignment.Left;
                style13.BorderDiagonal = BorderDiagonal.Backward;
                style13.BorderDiagonalColor = IndexedColors.Black.Index;
                string sb = "              投诉月份\n生产月份";
                IRow irow2 = sheet.CreateRow(1);
                ICell cell2 = irow2.CreateCell(0);
                cell2.SetCellValue(sb);
                cell2.CellStyle = cellStylebt;
                style13.WrapText = true;
                irow2.GetCell(0).CellStyle = style13;
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 2, 0, 0), workbook);

                ICellStyle cellStylebt3 = workbook.CreateCellStyle();
                NPOI.SS.UserModel.IFont cellfontbt3 = workbook.CreateFont();
                cellfontbt3.FontHeightInPoints = 15;
                cellStylebt3.WrapText = true;//设置换行这个要先设置
                cellStylebt3.SetFont(cellfontbt3);
                cellStylebt3.VerticalAlignment = VerticalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                cellStylebt3.Alignment = HorizontalAlignment.Center;
                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.BorderDiagonalLineStyle = BorderStyle.Thin;
                IRow irow6 = sheet.CreateRow(2);

                ICell title1 = irow2.CreateCell(1);
                title1.SetCellValue(time.AddYears(-3).Year.ToString()+"年");
                title1.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 12));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 1, 12), workbook);
                ICell title13 = irow2.CreateCell(13);
                title13.SetCellValue(time.AddYears(-2).Year.ToString() + "年");
                title13.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 13, 24));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 13, 24), workbook);
                ICell title14 = irow2.CreateCell(25);
                title14.SetCellValue(time.AddYears(-1).Year.ToString() + "年");
                title14.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 25, 36));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 1, 25, 36), workbook);
                ICell title15 = irow2.CreateCell(37);
                title15.SetCellValue("投诉起数");
                title15.CellStyle = cellStylebt3;
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, 37, 37));
                SetMergedRegionBorders(sheet, new CellRangeAddress(1, 2, 37, 37), workbook);
                DateTime start = DateTime.Parse(time.AddYears(-3).Year.ToString() + "-01-01 00:00:00");
                DateTime end = DateTime.Parse(time.AddYears(-1).Year.ToString() + "-12-31 23:59:59");
                int index = 1;
                for (DateTime i = start; i <= end; i = i.AddMonths(1))
                {
                    ICell title16 = irow6.CreateCell(index);
                    title16.SetCellValue(i.ToString("MM月"));
                    title16.CellStyle = cellStylebt3;
                    index++;
                }
                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++;
                }
                for (int i = 0; i < 37; i++)
                {
                    //
                    sheet.AutoSizeColumn(i);
                    // sheet.SetColumnWidth(i, 12 * 400);
                }

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("近三年投诉产品生产日期分布表" + ".xls", Encoding.UTF8));

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

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

                    curContext.Response.End();
                }
                return "";
            }
            catch
            {
                return "导出失败！";
            }
        }
        private void SetMergedRegionBorders(ISheet sheet, CellRangeAddress region, IWorkbook workbook)
        {
            int rowStart = region.FirstRow;
            int rowEnd = region.LastRow;
            int colStart = region.FirstColumn;
            int colEnd = region.LastColumn;

            // 设置顶部边框
            for (int col = colStart; col <= colEnd; col++)
            {
                SetCellBorder(sheet, rowStart, col, "Top", BorderStyle.Thin, workbook);
            }

            // 设置底部边框
            for (int col = colStart; col <= colEnd; col++)
            {
                SetCellBorder(sheet, rowEnd, col, "Bottom", BorderStyle.Thin, workbook);
            }

            // 设置左侧边框
            for (int row = rowStart; row <= rowEnd; row++)
            {
                SetCellBorder(sheet, row, colStart, "Left", BorderStyle.Thin, workbook);
            }

            // 设置右侧边框
            for (int row = rowStart; row <= rowEnd; row++)
            {
                SetCellBorder(sheet, row, colEnd, "Right", BorderStyle.Thin, workbook);
            }
        }

        private void SetCellBorder(ISheet sheet, int rowIdx, int colIdx, string  direction, BorderStyle style, IWorkbook workbook)
        {
            IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
            ICell cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
            ICellStyle originalStyle = cell.CellStyle;
            ICellStyle newStyle = workbook.CreateCellStyle();

            // 复制原有样式
            if (originalStyle != null)
            {
                newStyle.CloneStyleFrom(originalStyle);
            }

            // 设置边框
            switch (direction)
            {
                case "Top":
                    newStyle.BorderTop = style;
                    break;
                case "Bottom":
                    newStyle.BorderBottom = style;
                    break;
                case "Left":
                    newStyle.BorderLeft = style;
                    break;
                case "Right":
                    newStyle.BorderRight = style;
                    break;
            }

            // 应用新样式
            cell.CellStyle = newStyle;
        }
        private string GetQualityComplaints(qualityComplaints data,int i)
        {
            object str = "";
            switch(i )
            {
                case 1:
                    str = data.month;
                    break;
                case 2:
                    str = data.Total.ToString();
                    break;
                case 3:
                    str = data.Previous;
                    break;
                case 4:
                    str = data.Percent;
                    break;
                case 5:
                    str = data.P_Total;
                    break;
                case 6:
                    str = data.P_Proportion;
                    break;
                case 7:
                    str = data.P_Previous;
                    break;
                case 8:
                    str = data.P_Percent;
                    break;
                case 9:
                    str = data.Z_Total;
                    break;
                case 10:
                    str = data.Z_Proportion;
                    break;
                case 11:
                    str = data.Z_Previous;
                    break;
                case 12:
                    str = data.Z_Percent;
                    break;
                case 13:
                    str = data.B_Total;
                    break;
                case 14:
                    str = data.B_Proportion;
                    break;
                case 15:
                    str = data.B_Previous;
                    break;
                case 16:
                    str = data.B_Percent;
                    break;
            }
            return str.ToString();
        }
        /// <summary>
        /// NPOI导出EXCEL
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="title">导出文件的名称</param>
        /// <param name="array">列宽数组</param>
        public string NpoiExcel(DataTable table,string F_ParamValue)
        {
         try {
               
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
            headerrow.Height = 30 * 20;
            ICellStyle style = book.CreateCellStyle();
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.WrapText = true;
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;

            List<Workorderpicture> workorderpictures = new List<Workorderpicture>();
            foreach (DataRow dr in table.Rows)
            {
                Workorderpicture workorderpicture = new Workorderpicture();
                    workorderpicture.picture = new List<string>();
               var  FileUrl = GetFileData(dr["F_DealFile"].ToString (), F_ParamValue);
                workorderpicture.workorder = dr["F_WorkOrderId"].ToString();
                foreach (var  dt in FileUrl  )
                {
                    workorderpicture.picture.Add(dt);
                }
                workorderpictures.Add(workorderpicture);
            }
            var count = workorderpictures.Select(x => x.picture.Count).Max();
                //建立内容行
                int iRowIndex = 0;
                 foreach (var it in workorderpictures)
                {
                    int iCellIndex = 0;
                    IRow irow = sheet.CreateRow(iRowIndex );
                    for (int i = 0; i < count; i++)
                    {
                        string strsj = string.Empty;
                        if (iCellIndex == 0)
                        {
                            ICell cell = irow.CreateCell(iCellIndex);
                            cell.SetCellValue(it.workorder);
                            cell.CellStyle = style;
                            iCellIndex++;
                        }
                        else
                        {
                            if (it .picture .Count <= count)
                            {
                               
                                ICell cell = irow.CreateCell(iCellIndex);
                                //  cell.SetCellValue(it.workorder);
                                if (iCellIndex<= it.picture.Count)
                                {
                                 bool n=   AddPieChartAsync(book, sheet, it.picture[iCellIndex - 1], iRowIndex, iCellIndex, 1000);
                                    if (!n )
                                        cell.SetCellValue("图片不存在!");
                                }
                                else
                                    cell.SetCellValue("");
                                cell.CellStyle = style;
                                iCellIndex++;

                            }
                        }
                       
                    }
                    iRowIndex++;

                }

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

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

                    HttpContext curContext = HttpContext.Current;


                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/vnd.ms-excel";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "";
                    curContext.Response.AppendHeader("Content-Disposition",
                        "attachment;filename=" + HttpUtility.UrlEncode("工单列表" + ".xls", Encoding.UTF8));

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

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

                    curContext.Response.End();
                }

                return "";
            }
            catch (Exception e)
            {
                LogFactory.GetLogger("导出").Error(e.ToJson());
                return "导出失败！" + e.Message;
            }
        }
        public class Workorderpicture
        {
            public string workorder;
            public List<string> picture;
        }
        public List<string> GetFileData(string ids, string prefix)
        {
            List<string> F_Url = new List<string>();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(ids))
            {
                dt = DbHelperSQL.Query("select * from T_Sys_Accessories where F_Id in (" + ids + ")").Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    string  Url = prefix + dr["F_Url"].ToString();
                    F_Url.Add(Url);
                }
            }
            return F_Url;
        }
        /// <summary>
        /// 向sheet插入图片
        /// </summary>
        /// <param name="workbook">工作辅</param>
        /// <param name="sheet">sheet页</param>
        /// <param name="fileurl">图片地址</param>
        /// <param name="row">当前行</param>
        /// <param name="col">当前列</param>
        /// <param name="RowHeight">行高</param> 
        public bool     AddPieChartAsync(HSSFWorkbook workbook, ISheet sheet, string fileurl, int row, int col, short RowHeight)
        {
            WebClient myWebClient = new WebClient();
            myWebClient.Credentials = CredentialCache.DefaultCredentials;
            myWebClient.Headers.Add("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705;)");
            try
            {
                byte[] bytes = myWebClient.DownloadData(fileurl);
                if (bytes != null)
                {
                    int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 20, 20, col, row, col + 1, row + 1);
                    //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50

                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    var index = sheet.GetRow(row) ?? sheet.CreateRow(row);
                    index.Height = RowHeight;
                    //pict.Resize(0);//这句话一定不要，这是用图片原始大小来显示
                    
                }
                return true;
            }
            catch
            {
                return false ;
            }
          
           
           
        }


}
}