using Adf.Core.Entity;
using Aspose.Cells;
using CYQ.Data.Table;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Windows.Forms;

namespace CompanyServer
{
    public class ExcelHelper
    {

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="datatable">系统自带的Datable</param>
        /// <param name="fileName">模版名字</param>
        /// <param name="outName">需要输出的文件名</param>
        public static void ToExcel(MDataTable datatable, string fileName, string outName, string title = "")
        {
            WorkbookDesigner designer = new WorkbookDesigner();

            String fileNamePath = HttpContext.Current.Server.MapPath("/DownLoad/" + fileName);
            //打开设置好的模版
            designer.Open(fileNamePath);

            DataTable MyexDatable = new DataTable();
            //将Mdatatab转换成系统datable
            MyexDatable = datatable.ToDataTable();
            MyexDatable.TableName = "DataTable";
            //切换Excel工作簿
            Worksheet ws = designer.Workbook.Worksheets[0];
            //设置数据源
            designer.SetDataSource(MyexDatable);
            //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
            designer.Process(0, false);


            if (!string.IsNullOrEmpty(title))
            {
                ws.Cells[0, 0].PutValue(title);

            }
            designer.ClearDataSource();
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            System.Web.HttpContext.Current.Response.Charset = "utf-8";
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            string fileToSave = outName + ".xls";
            designer.Save(String.Format(fileToSave), SaveType.OpenInExcel, FileFormatType.Excel2003, System.Web.HttpContext.Current.Response);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Close();
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dtInfo"></param>
        /// <param name="fileName"></param>
        /// <param name="outName"></param>
        /// <returns></returns>
        public static ExeMsgInfo ToExport(MDataTable dtInfo, string fileName, string outName, string title = "")
        {
            ExeMsgInfo res = null;
            string date = DateTime.Now.ToString("yyyy-MM-dd:HH:mm");
            ToExcel(dtInfo, fileName + ".xlsx", outName + date, title);
            string retResponse = string.Format("{{'Path':'{0}','Msg':'共导出{1}条数据！'}}", "", dtInfo.Rows.Count());
            res = new ExeMsgInfo(100, retResponse);
            return res;
        }

        /// <summary>
        /// 导出数据（主从表）
        /// </summary>
        /// <param name="Datatable">主从表所有table的list合集</param>
        /// <param name="fileName">模版名称</param>
        /// <param name="outName">导出文件名称</param>
        public static void ToExcel(MDataTable Datatable, MDataTable Detail, string fileName, string outName, string title = "")
        {
            bool isFireFox = false;
            if (HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") != -1)
            {
                isFireFox = true;
            }

            WorkbookDesigner designer = new WorkbookDesigner();

            String fileNamePath = HttpContext.Current.Server.MapPath("/DownLoad/" + fileName);
            //打开设置好的模版
            designer.Open(fileNamePath);
            if (Datatable.Rows.Count >= 0)
            {

                DataTable datatable = new DataTable();
                //将Mdatatab转换成系统datable
                datatable = Datatable.ToDataTable();
                datatable.TableName = "DataTable";
                //切换Excel工作簿
                Worksheet ws = designer.Workbook.Worksheets[0];
                //设置数据源
                designer.SetDataSource(datatable);
                //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
                designer.Process(0, false);
                if (datatable.Rows.Count > 0)
                {
                    #region 字体
                    //改变字体样式
                    Style style = new Style();
                    style.Font.Color = System.Drawing.Color.Red;//Color.Silver;
                    style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                    #endregion
                    #region 框架线条
                    //改变字体样式
                    Style framelineBottom = new Style();
                    framelineBottom.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                    framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                    framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                    framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                    #endregion
                    #region 字体样式赋值
                    //框架加粗

                    #region 框架加粗
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        for (int j = 0; j < datatable.Columns.Count - 2; j++)
                        {
                            if (i + 3 == datatable.Rows.Count || i + 3 == datatable.Rows.Count + 1 || i + 3 == datatable.Rows.Count + 2)
                            {
                                framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium; //应用边界线 左边界线 
                                framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium; //应用边界线 下边界线
                                designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(framelineBottom);
                            }
                            else if (i == 0)
                            {
                                framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                                designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(framelineBottom);
                                framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                                framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线  
                            }
                            else
                            {
                                designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(framelineBottom);
                            }
                        }
                    }
                    #endregion

                    #region 字体标红
                    //字体标红
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        for (int j = 3; j < datatable.Columns.Count - 3; j++)
                        {
                            if (Convert.ToDouble(datatable.Rows[i][j].ToString()) > 0)
                            {
                                if (i + 3 == datatable.Rows.Count || i + 3 == datatable.Rows.Count + 1 || i + 3 == datatable.Rows.Count + 2)
                                {
                                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium; //应用边界线 左边界线 
                                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium; //应用边界线 下边界线

                                    designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(style);
                                }
                                else if (i == 0)
                                {
                                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线

                                    designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(style);
                                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                                }
                                else
                                {
                                    designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(style);
                                }
                            }
                        }
                    }
                    #endregion

                    #endregion
                    //已报（合并单元格）
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 2, 1, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 1, 1, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2, 1, 1, 2);

                    //未报（合并单元格）
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 2, 3, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 1, 3, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2, 3, 1, 2);
                    if (!string.IsNullOrEmpty(title))
                    {
                        ws.Cells[0, 0].PutValue(title);

                    }
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        for (int j = 0; j < datatable.Columns.Count - 2; j++)
                        {
                            ws.Cells[i + 3, 5].PutValue(Convert.ToDouble(datatable.Rows[i][5].ToString()));
                        }
                    }
                    framelineBottom.Font.IsBold = true;
                    ws.Cells[datatable.Rows.Count + 2, 0].SetStyle(framelineBottom);
                }


                //填充完毕之后，清空数据源
                designer.ClearDataSource();

            }

            if (Detail.Rows.Count >= 0)
            {
                /////打开设置好的模版
                //designer.Open(fileNamePath);

                DataTable datatable = new DataTable();
                //将Mdatatab转换成系统datable
                datatable = Detail.ToDataTable();
                datatable.TableName = "DataTable";
                //切换Excel工作簿
                Worksheet ws = designer.Workbook.Worksheets[1];
                //设置数据源
                designer.SetDataSource(datatable);
                //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
                designer.Process(1, false);
                //填充完毕之后，清空数据源
                designer.ClearDataSource();

            }
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            System.Web.HttpContext.Current.Response.Charset = "utf-8";
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            string fileToSave = HttpUtility.UrlEncode(outName, Encoding.UTF8) + ".xls";

            if (isFireFox == true)
            {
                fileToSave = outName + ".xls";
            }
            else
            {
                fileToSave = outName + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ".xls";

            }

            designer.Save(String.Format(fileToSave), SaveType.OpenInExcel, FileFormatType.Excel2003, System.Web.HttpContext.Current.Response);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.Close();
            HttpContext.Current.Response.End();
        }


        /// <summary>
        /// 导出数据（主从表）
        /// </summary>
        /// <param name="Datatable">主从表所有table的list合集</param>
        /// <param name="fileName">模版名称</param>
        /// <param name="outName">导出文件名称</param>
        public static void ToSaveExcel(MDataTable Datatable, string fileName, string outName, string title, ref string fileToSave)
        {

            WorkbookDesigner designer = new WorkbookDesigner();

            String fileNamePath = Application.StartupPath +"/DownLoad/" + fileName;
            //打开设置好的模版
            designer.Open(fileNamePath);
            if (Datatable.Rows.Count >= 0)
            {

                DataTable datatable = new DataTable();
                //将Mdatatab转换成系统datable
                datatable = Datatable.ToDataTable();
                datatable.TableName = "DataTable";
                //切换Excel工作簿
                Worksheet ws = designer.Workbook.Worksheets[0];
                //设置数据源
                designer.SetDataSource(datatable);
                //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
                designer.Process(0, false);
                if (datatable.Rows.Count > 0)
                {
                    #region 字体
                    //改变字体样式
                    Style style = new Style();
                    style.Font.Color = System.Drawing.Color.Red;//Color.Silver;
                    style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                    #endregion
                    #region 框架线条
                    //改变字体样式
                    Style framelineBottom = new Style();
                    framelineBottom.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                    framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                    framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                    framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                    #endregion
                    #region 字体样式赋值
                    //框架加粗

                    #region 框架加粗
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        for (int j = 0; j < datatable.Columns.Count - 2; j++)
                        {
                            if (i + 3 == datatable.Rows.Count || i + 3 == datatable.Rows.Count + 1 || i + 3 == datatable.Rows.Count + 2)
                            {
                                framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium; //应用边界线 左边界线 
                                framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium; //应用边界线 下边界线
                                designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(framelineBottom);
                            }
                            else if (i == 0)
                            {
                                framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                                designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(framelineBottom);
                                framelineBottom.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                framelineBottom.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                framelineBottom.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                                framelineBottom.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线  
                            }
                            else
                            {
                                designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(framelineBottom);
                            }
                        }
                    }
                    #endregion

                    #region 字体标红
                    //字体标红
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        for (int j = 3; j < datatable.Columns.Count - 3; j++)
                        {
                            if (Convert.ToDouble(datatable.Rows[i][j].ToString()) > 0)
                            {
                                if (i + 3 == datatable.Rows.Count || i + 3 == datatable.Rows.Count + 1 || i + 3 == datatable.Rows.Count + 2)
                                {
                                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Medium; //应用边界线 左边界线 
                                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium; //应用边界线 下边界线

                                    designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(style);
                                }
                                else if (i == 0)
                                {
                                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Medium; //应用边界线 上边界线 
                                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线

                                    designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(style);
                                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 
                                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Medium; //应用边界线 右边界线 
                                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 
                                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
                                }
                                else
                                {
                                    designer.Workbook.Worksheets[0].Cells[i + 3, j].SetStyle(style);
                                }
                            }
                        }
                    }
                    #endregion

                    #endregion
                    //已报（合并单元格）
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 2, 1, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 1, 1, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2, 1, 1, 2);

                    //未报（合并单元格）
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 2, 3, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2 - 1, 3, 1, 2);
                    ws.Cells.Merge(datatable.Rows.Count + 2, 3, 1, 2);
                    if (!string.IsNullOrEmpty(title))
                    {
                        ws.Cells[0, 0].PutValue(title);

                    }
                    for (int i = 0; i < datatable.Rows.Count; i++)
                    {
                        for (int j = 0; j < datatable.Columns.Count - 2; j++)
                        {
                            ws.Cells[i + 3, 5].PutValue(Convert.ToDouble(datatable.Rows[i][5].ToString()));
                        }
                    }
                    framelineBottom.Font.IsBold = true;
                    ws.Cells[datatable.Rows.Count + 2, 0].SetStyle(framelineBottom);
                }


                //填充完毕之后，清空数据源
                designer.ClearDataSource();

            }

            //System.Web.HttpContext.Current.Response.Clear();
            //System.Web.HttpContext.Current.Response.Buffer = false;
            //System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            //System.Web.HttpContext.Current.Response.Charset = "utf-8";
            //System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            //fileToSave = HttpUtility.UrlEncode(outName + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
            if (outName.Length > 5)
            {
                fileToSave = HttpUtility.UrlEncode(outName.Substring(0, 5) + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
            }
            else
            {
                //fileToSave = HttpUtility.UrlEncode(outName + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
                fileToSave = outName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            }
            //designer.Save(String.Format(fileToSave), SaveType.OpenInExcel, FileFormatType.Excel2003, System.Web.HttpContext.Current.Response);
            designer.Save(Application.StartupPath +"/upload/" + fileToSave, FileFormatType.Default);
        }

        /// <summary>
        /// 导出数据（从表）
        /// </summary>
        /// <param name="Datatable">主从表所有table的list合集</param>
        /// <param name="fileName">模版名称</param>
        /// <param name="outName">导出文件名称</param>
        public static void ToSaveExcel2(MDataTable Datatable, string fileName, string outName, string title, ref string fileToSave)
        {

            WorkbookDesigner designer = new WorkbookDesigner();

            String fileNamePath = Application.StartupPath +"/DownLoad/" + fileName;
            //打开设置好的模版
            designer.Open(fileNamePath);

            DataTable MyexDatable = new DataTable();
            //将Mdatatab转换成系统datable
            MyexDatable = Datatable.ToDataTable();
            MyexDatable.TableName = "DataTable";
            //切换Excel工作簿
            Worksheet ws = designer.Workbook.Worksheets[0];
            //设置数据源
            designer.SetDataSource(MyexDatable);
            //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
            designer.Process(0, false);


            if (!string.IsNullOrEmpty(title))
            {
                ws.Cells[0, 0].PutValue(title);

            }
            designer.ClearDataSource();

            //System.Web.HttpContext.Current.Response.Clear();
            //System.Web.HttpContext.Current.Response.Buffer = false;
            //System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            //System.Web.HttpContext.Current.Response.Charset = "utf-8";
            //System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            //fileToSave = HttpUtility.UrlEncode(outName + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";

            if (outName.Length > 5)
            {
                fileToSave = HttpUtility.UrlEncode(outName.Substring(0, 5) + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
            }
            else
            {
                //fileToSave = HttpUtility.UrlEncode(outName + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
                fileToSave = outName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            }
            //designer.Save(String.Format(fileToSave), SaveType.OpenInExcel, FileFormatType.Excel2003, System.Web.HttpContext.Current.Response);
            designer.Save(Application.StartupPath +"/upload/" + fileToSave, FileFormatType.Default);
        }

        /// <summary>
        /// 导出数据（纯主从表）
        /// </summary>
        /// <param name="Datatable">主从表所有table的list合集</param>
        /// <param name="fileName">模版名称</param>
        /// <param name="outName">导出文件名称</param>
        public static void ToExcelmian(MDataTable Datatable, MDataTable Detail, string fileName, string outName, ref string fileToSave, string title = "")
        {

            WorkbookDesigner designer = new WorkbookDesigner();

            String fileNamePath = Application.StartupPath + "/DownLoad/" + fileName;
            //打开设置好的模版
            designer.Open(fileNamePath);
            if (Datatable.Rows.Count >= 0)
            {
                DataTable MyexDatable = new DataTable();
                //将Mdatatab转换成系统datable
                MyexDatable = Datatable.ToDataTable();
                MyexDatable.TableName = "DataTable";
                //切换Excel工作簿
                Worksheet ws = designer.Workbook.Worksheets[0];
                //设置数据源
                designer.SetDataSource(MyexDatable);
                //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
                designer.Process(0, false);


                if (!string.IsNullOrEmpty(title))
                {
                    ws.Cells[0, 0].PutValue(title);

                }
                designer.ClearDataSource();

            }

            if (Detail.Rows.Count >= 0)
            {
                /////打开设置好的模版
                //designer.Open(fileNamePath);

                DataTable datatable = new DataTable();
                //将Mdatatab转换成系统datable
                datatable = Detail.ToDataTable();
                datatable.TableName = "DataTable";
                //切换Excel工作簿
                Worksheet ws = designer.Workbook.Worksheets[1];
                //设置数据源
                designer.SetDataSource(datatable);
                //自动将数据填充至工作簿，如果table没有数据，将清空模版里的代码
                designer.Process(1, false);
                //填充完毕之后，清空数据源
                designer.ClearDataSource();

            }

            if (outName.Length > 5)
            {
                fileToSave = HttpUtility.UrlEncode(outName.Substring(0, 5) + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
            }
            else
            {
                //fileToSave = HttpUtility.UrlEncode(outName + DateTime.Now.ToString("yyyyMMddHHmmss"), Encoding.UTF8) + ".xls";
                fileToSave = outName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            }
            //designer.Save(String.Format(fileToSave), SaveType.OpenInExcel, FileFormatType.Excel2003, System.Web.HttpContext.Current.Response);
            designer.Save(Application.StartupPath + "/upload/" + fileToSave, FileFormatType.Default);
        }

    }
}
