
using Microsoft.Office.Interop.Excel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using XYY.Model.Standard.Finance;
using XYY.Service.Standard.Finance.Model;

namespace XYY.Service.Standard.Finance
{
    public class QuotationFileGenerator
    {
        //public byte[] MakeSpecialLineAndUsExpressFile(IEnumerable<View_Quotation> quotations)
        //{
        //    var expData = quotations.Where(i => i.线路名称.Contains("快线"));
        //    var electrictData = quotations.Where(i => i.线路名称.Contains("带电"));
        //    var specialLineData = quotations.Except(expData).Except(electrictData);
        //}

        public byte[] MakeSpecialLineFileFromTemplate(IEnumerable<View_Quotation> quotations, bool showSurcharge = true)
        {
            string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "专线报价模板.xlsx");
            byte[] data = MakeQuotationFile(quotations, templateFile, showSurcharge: showSurcharge);
            return data;
        }

        public byte[] MakeSpecialLineFile(IEnumerable<View_Quotation> quotations)
        {
            var groups = quotations.GroupBy(i => i.线路名称);

            IWorkbook workbook = new XSSFWorkbook();
            MemoryStream ms = null;
            try
            {
                ISheet sheet = workbook.CreateSheet("专线报价");
                sheet.SetColumnWidth(1, 10 * 256);
                sheet.SetColumnWidth(2, 10 * 256);
                sheet.SetColumnWidth(3, 10 * 256);
                sheet.SetColumnWidth(4, 20 * 256);
                sheet.SetColumnWidth(5, 30 * 256);
                sheet.SetColumnWidth(6, 30 * 256);
                sheet.SetColumnWidth(7, 20 * 256);
                sheet.SetColumnWidth(8, 30 * 256);
                sheet.SetColumnWidth(9, 30 * 256);
                sheet.SetColumnWidth(9, 30 * 256);
                IRow title = sheet.CreateRow(0);
                var titleCell = title.CreateCell(1, CellType.String);
                titleCell.SetCellValue("行运易专线报价");
                var titleCellStyle = workbook.CreateCellStyle();
                titleCellStyle.Alignment = HorizontalAlignment.Center;
                var titleFont = workbook.CreateFont();
                titleFont.FontHeightInPoints = 22;
                titleFont.IsBold = true;
                titleCellStyle.SetFont(titleFont);
                titleCellStyle.BorderBottom = titleCellStyle.BorderLeft = titleCellStyle.BorderRight = titleCellStyle.BorderTop = BorderStyle.Thin;
                titleCell.CellStyle = titleCellStyle;

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

                IRow headerRow = sheet.CreateRow(1);
                headerRow.HeightInPoints = 20;




                var headerCellStyle = CreateContentCellStyle(workbook);
                headerCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
                headerCellStyle.FillPattern = FillPattern.SolidForeground;
                var headerFont = workbook.CreateFont();
                headerFont.FontHeightInPoints = 16;
                headerCellStyle.SetFont(headerFont);
                for (int i = 0; i < headers.Count; i++)
                {
                    string colName = headers[i].Item1;
                    var cell = headerRow.CreateCell(headers[i].Item2, CellType.String);
                    cell.SetCellValue(colName);
                    cell.CellStyle = headerCellStyle;
                }


                var contentCellStyle = CreateContentCellStyle(workbook);
                int contentRowIdx = 2;
                foreach (var g in groups)
                {
                    string channelName = g.Key;
                    bool channelNameCellMerged = false;
                    var sortedByWeight = g.OrderBy(i => i.重量段);
                    foreach (var q in sortedByWeight)
                    {
                        IRow contentRow = sheet.CreateRow(contentRowIdx);
                        contentRow.Height = 22 * 20;
                        if (g.Count() > 1 && channelNameCellMerged == false)
                        {
                            int mergeRowCount = contentRowIdx + g.Count() - 1;
                            sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 1, 1));
                            sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 5, 5));
                            sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 6, 6));
                            sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 7, 7));
                            sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 8, 8));
                            sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 9, 9));
                            channelNameCellMerged = true;
                        }
                        foreach (var t in headers)
                        {
                            var cell = contentRow.CreateCell(t.Item2);
                            cell.CellStyle = contentCellStyle;

                            switch (t.Item2)
                            {
                                case 1:
                                    cell.SetCellValue(q.线路名称);
                                    break;
                                case 2:
                                    if (decimal.TryParse(q.运费, out decimal freight))
                                        cell.SetCellValue((double)freight);
                                    else cell.SetCellValue(q.运费);

                                    break;
                                case 3:
                                    if (decimal.TryParse(q.操作费, out decimal operateFee))
                                        cell.SetCellValue((double)operateFee);
                                    else cell.SetCellValue(q.操作费);
                                    break;
                                case 4:
                                    cell.SetCellValue(q.重量段);
                                    break;
                                case 5:
                                    string refEffective = GetRefEffective(q.线路名称);
                                    cell.SetCellValue(refEffective);
                                    break;
                                case 6:
                                    string dimension = GetDimension(q.线路名称);
                                    cell.SetCellValue(dimension);
                                    break;
                                case 7:
                                    cell.SetCellValue(q.渠道代码);
                                    break;
                                case 8:
                                    cell.SetCellValue(SurchargeDesc);
                                    break;
                                case 9:
                                    string remark = GetRemark(q.线路名称);
                                    cell.SetCellValue(remark);
                                    break;
                                case 10:
                                    string enableTime = q.生效时间;
                                    if (DateTime.TryParse(enableTime, out DateTime _enableTime) && _enableTime > DateTime.Now)
                                    {
                                        var style = CreateContentCellStyle(workbook);
                                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Gold.Index;
                                        style.FillPattern = FillPattern.SolidForeground;
                                        cell.CellStyle = style;
                                    }

                                    cell.SetCellValue(enableTime);
                                    break;
                            }

                        }

                        contentRowIdx++;
                    }
                }


                contentRowIdx += 1;




                for (int i = contentRowIdx, j = 0; i < contentRowIdx + 5; i++, j++)
                {
                    IRow row = sheet.CreateRow(i);
                    for (int c = 1; c < 10; c++)
                    {
                        ICellStyle noticeCellStyle = CreateNoticeCellStyle(workbook);
                        var cell = row.CreateCell(c);
                        cell.CellStyle = noticeCellStyle;
                    }

                    if (j == 0)
                    {
                        row.Cells[0].SetCellValue("注意事项");
                        sheet.AddMergedRegion(new CellRangeAddress(i, i, 1, 9));
                    }
                    else if (j == 1)
                    {
                        row.Cells[0].SetCellValue("小包理赔标准");
                        row.Cells[0].CellStyle.Alignment = HorizontalAlignment.Center;
                        sheet.AddMergedRegion(new CellRangeAddress(i, i, 1, 9));
                    }
                    else if (j == 2)
                    {
                        row.Cells[0].SetCellValue("渠道");
                        row.Cells[1].SetCellValue("关键点");
                        row.Cells[2].SetCellValue("丢失");
                        row.Cells[2].CellStyle.Alignment = HorizontalAlignment.Center;
                        sheet.AddMergedRegion(new CellRangeAddress(i, i, 3, 9));
                    }
                    else if (j == 3)
                    {
                        row.Cells[0].SetCellValue("各国专线小包");
                        row.Cells[0].CellStyle.VerticalAlignment = VerticalAlignment.Center;
                        row.Cells[1].SetCellValue("赔付标准");
                        row.Cells[1].CellStyle.VerticalAlignment = VerticalAlignment.Center;


                        string claimStandardContent = ReadClaimStandardContent(out int lines);
                        row.Cells[2].SetCellValue(claimStandardContent);
                        row.Cells[2].CellStyle.VerticalAlignment = VerticalAlignment.Top;
                        row.Cells[2].CellStyle.WrapText = true;

                        row.HeightInPoints = lines * 14;

                        sheet.AddMergedRegion(new CellRangeAddress(i, i, 3, 9));
                    }
                    else if (j == 4)
                    {
                        row.Cells[0].SetCellValue("各国专线小包");
                        row.Cells[0].CellStyle.VerticalAlignment = VerticalAlignment.Center;

                        row.Cells[1].SetCellValue("注意事项");
                        row.Cells[1].CellStyle.VerticalAlignment = VerticalAlignment.Center;


                        string noticeItems = ReadNoticeContent(out int lines);
                        row.Cells[2].SetCellValue(noticeItems);
                        row.Cells[2].CellStyle.VerticalAlignment = VerticalAlignment.Top;
                        row.Cells[2].CellStyle.WrapText = true;

                        row.HeightInPoints = lines * 14;
                        sheet.AddMergedRegion(new CellRangeAddress(i, i, 3, 9));

                    }
                }

                ms = new MemoryStream();
                workbook.Write(ms);
                var bytes = ms.ToArray();
                return bytes;
            }
            finally
            {
                ms.Close();
                ms.Dispose();
                workbook.Close();
            }
        }


        private class xxSort : IComparer<string>
        {
            public int Compare(string x, string y)
            {
                if (x == "欧洲空派包税")
                    return -1;
                else
                    return 1;
            }
        }
        public void MakeUSKPFile(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {

            try
            {
                if (quotations?.Any() == true)
                {
                    var workbook = app.Workbooks._Open(templateFile);
                    var tempsheet = (Worksheet)workbook.ActiveSheet;
                    tempsheet.Copy(Type.Missing, source.ActiveSheet);
                    workbook.Close();
                    var sheet = (Worksheet)source.ActiveSheet;
                    sheet.Name = "美国空派";
                    var groups = quotations.GroupBy(q => q.操作费).ToList();
                    var groups2 = quotations.GroupBy(x => x.重量段).ToList();
                    int contentIndex = 4;

                    object[,] mm = new object[groups.Count, 1];
                    for (int i = 0; i < groups.Count(); i++)
                    {
                        Microsoft.Office.Interop.Excel.Range range2 = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[contentIndex, Missing.Value];
                        range2.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        mm[i, 0] = groups[i].Key;
                    }
                    var range = sheet.get_Range("A3");
                    range = range.get_Resize(1, 5 + groups2.Count);
                    object[,] values = new object[1, 5 + groups2.Count];
                    values[0, 0] = "产品名称";
                    values[0, 1] = "产品代码";
                    values[0, 2] = "参考时效";
                    values[0, 3] = "生效时间";
                    values[0, 4] = "美国(含油/含税）";
                    for (var i = 0; i < groups2.Count; i++)
                    {
                        values[0, i + 5] = groups2[i].Key.Replace(".000", "") + "KG";
                    }
                    range.set_Value(Missing.Value, values);
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    range.Font.Size = 14;
                    range.Font.Bold = true;
                    range.RowHeight = 30;
                    range.Interior.Color = System.Drawing.Color.FromArgb(146, 208, 80);
                    range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                    var name = new object[1, 1];
                    name[0, 0] = "美国空派专线";
                    range = sheet.get_Range("A4");
                    range = range.get_Resize(groups.Count(), 1);
                    range.Merge();
                    range.set_Value(Missing.Value, name);

                    name[0, 0] = "USKPLine";
                    range = sheet.get_Range("B4");
                    range = range.get_Resize(groups.Count(), 1);
                    range.Merge();
                    range.set_Value(Missing.Value, name);

                    name[0, 0] = "5-8个自然日提取\r\n全程8 - 15个自然日签收";
                    range = sheet.get_Range("C4");
                    range = range.get_Resize(groups.Count(), 1);
                    range.Merge();
                    range.set_Value(Missing.Value, name);


                    name[0, 0] = quotations.First().生效时间;
                    range = sheet.get_Range("D4");
                    range = range.get_Resize(groups.Count(), 1);
                    range.Merge();
                    range.set_Value(Missing.Value, name);


                    range = sheet.get_Range("E4");
                    range = range.get_Resize(groups.Count(), 1);
                    range.set_Value(Missing.Value, mm);


                    object[,] rowValue = new object[groups.Count(), groups2.Count];
                    for (int i = 0; i < groups.Count; i++)
                    {
                        for (int j = 0; j < groups2.Count; j++)
                        {
                            rowValue[i, j] = quotations.Where(x => x.操作费 == groups[i].Key && x.重量段 == groups2[j].Key).First()?.运费;
                        }
                    }
                    range = sheet.get_Range("F4");
                    range = range.get_Resize(groups.Count(), groups2.Count);
                    range.set_Value(Missing.Value, rowValue);


                    //整体样式
                    range = sheet.get_Range("A4");
                    range = range.get_Resize(groups.Count(), 5 + groups2.Count);
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    range.Font.Size = 14;
                    range.Font.Bold = true;
                    range.RowHeight = 30;
                    range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    range.Borders.Weight = XlBorderWeight.xlMedium;
                }
            }
            catch (Exception ex)
            {
            }
        }
        public void MakeEUKPFile(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile, Worksheet ozkpsheets)
        {
            try
            {
                if (quotations?.Any() == true)
                {
                    var workbook = app.Workbooks._Open(templateFile);
                    var tempsheet = (Worksheet)workbook.ActiveSheet;
                    tempsheet.Copy(Type.Missing, source.ActiveSheet);
                    workbook.Close();
                    var sheet = (Worksheet)source.ActiveSheet;
                    sheet.Name = "欧洲空派";

                    var groups = quotations.GroupBy(i => i.线路名称).OrderBy(x => x.Key, new xxSort());
                    int insertCount = 0;
                    //暂时只加空派的

                    var g2 = quotations.GroupBy(x => new { x.重量段 }).ToList();
                    //列我们仅添加一次
                    for (int i = 1; i < g2.Count; i++)
                    {
                        Microsoft.Office.Interop.Excel.Range range2 = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[2 + i, Missing.Value];
                        range2.Insert(XlInsertFormatOrigin.xlFormatFromLeftOrAbove, Missing.Value);
                    }

                    foreach (var g in groups)
                    {
                        //if (g.Key == "欧洲空派包税")
                        //{

                        var g3 = g.GroupBy(x => new { x.操作费 }).ToList();
                        //插入行


                        int rowCount = g3.Count();
                        int rowIndex = g.Key == "欧洲空派包税" ? 6 : 8 + insertCount;
                        insertCount += rowCount;
                        for (int i = 0; i < rowCount; i++)
                        {
                            Microsoft.Office.Interop.Excel.Range range2 = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Missing.Value];
                            range2.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        }

                        for (int i = 0; i < g2.Count; i++)
                        {
                            sheet.get_Range(GetC(2 + i) + (rowIndex - 1)).Value2 = g2[i].Key.重量段.Replace(".000", "").Replace("-100000", "+");
                        }


                        var range = sheet.get_Range("A" + rowIndex);
                        range = range.get_Resize(rowCount, 6 + g2.Count - 1);
                        range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        range.Font.Size = 14;
                        range.RowHeight = 40;
                        range.Interior.Color = System.Drawing.Color.White;
                        range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                        object[,] cellData = new object[rowCount, 6 + g2.Count() - 1];

                        for (var i = 0; i < rowCount; i++)
                        {
                            string country = g3[i].Key.操作费;
                            cellData[i, 0] = country;
                            cellData[i, 1] = g.Key;
                            for (int j = 0; j < g2.Count; j++)
                            {
                                cellData[i, 2 + j] = g.Where(x => x.操作费 == country && x.重量段 == g2[j].Key.重量段).First().运费;
                            }
                            cellData[i, 3 + g2.Count - 1] = "6-8工作日提取DPD派送";
                            cellData[i, 4 + g2.Count - 1] = g.Key == "欧洲空派包税" ? "OZKPBS" : "OZKPVAT";
                            cellData[i, 5 + g2.Count - 1] = g.First().生效时间;
                        }

                        //字符串格式

                        range.set_Value(Missing.Value, cellData);
                        var formRange = sheet.get_Range(GetC(2 + g2.Count - 1) + rowIndex);
                        formRange = formRange.get_Resize(rowCount, 1);
                        formRange.NumberFormat = "@";

                        var mangerRange1 = sheet.get_Range(GetC(3 + g2.Count - 1) + rowIndex);
                        mangerRange1 = mangerRange1.get_Resize(rowCount, 1);
                        mangerRange1.Merge();
                        mangerRange1.WrapText = true;

                        var mangerRange2 = sheet.get_Range(GetC(4 + g2.Count - 1) + rowIndex);
                        mangerRange2 = mangerRange2.get_Resize(rowCount, 1);
                        mangerRange2.Merge();

                        mangerRange2 = sheet.get_Range(GetC(5 + g2.Count - 1) + rowIndex);
                        mangerRange2 = mangerRange2.get_Resize(rowCount, 1);
                        mangerRange2.Merge();
                        mangerRange2.NumberFormat = "yyyy-MM-dd";
                        if (g.FirstOrDefault()?.生效时间 != null && DateTime.Parse(g.FirstOrDefault()?.生效时间) >= DateTime.Now)
                        {
                            mangerRange2.Interior.Color = System.Drawing.Color.Yellow;
                        }
                        //}
                    }

                    ozkpsheets.Copy(Type.Missing, source.ActiveSheet);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        internal void MakeCAKPFile(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string caFabTemplateFile)
        {
            try
            {
                if (quotations?.Any() == true)
                {
                    var workbook = app.Workbooks._Open(caFabTemplateFile);
                    var tempsheet = (Worksheet)workbook.ActiveSheet;
                    tempsheet.Copy(Type.Missing, source.ActiveSheet);
                    var sheet = (Worksheet)source.ActiveSheet;
                    sheet.Name = "加拿大空派";

                    var g2 = quotations.GroupBy(x => new { x.重量段 }).ToList();
                    //列我们仅添加一次
                    for (int i = 1; i < g2.Count; i++)
                    {
                        Microsoft.Office.Interop.Excel.Range range2 = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[4 + i, Missing.Value];
                        range2.Insert(XlInsertFormatOrigin.xlFormatFromLeftOrAbove, Missing.Value);
                    }
                    for (int i = 0; i < g2.Count; i++)
                    {
                        sheet.get_Range(GetC(3 + i) + "3").Value2 = g2[i].Key.重量段.Replace(".000", "").Replace("-100000", "+") + "Kg";
                    }


                    object[,] cellData = new object[4, g2.Count];
                    for (int i = 0; i < g2.Count; i++)
                    {
                        for (int j = 0; j < 4; j++)
                        {
                            cellData[j, i] = g2[i].Where(x => x.操作费 == j + 1 + "区").FirstOrDefault()?.运费;
                        }
                    }

                    var range = sheet.get_Range("D4");
                    range = range.get_Resize(4, g2.Count);
                    range.set_Value(Missing.Value, cellData);
                    var mangerRange2 = sheet.get_Range("H4");
                    mangerRange2.Value2 = quotations.FirstOrDefault()?.生效时间;
                    if (quotations.FirstOrDefault()?.生效时间 != null && DateTime.Parse(quotations.FirstOrDefault()?.生效时间) >= DateTime.Now)
                    {
                        mangerRange2.Interior.Color = System.Drawing.Color.Yellow;
                    }
                    var fjf = (Worksheet)workbook.Worksheets[2];
                    fjf.Copy(Type.Missing, source.ActiveSheet);
                    workbook.Close();
                }
            }
            catch (Exception ex)
            {

            }
        }

        public string GetC(int count)
        {
            var list = new List<string> { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "H" };
            return list[count];
        }

        public byte[] MakeQuotationFile(IEnumerable<View_Quotation> quotations, string templateFile, bool showSurcharge = true)
        {
            //string templateFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "美国快线报价模板.xlsx");
            byte[] tempData = File.ReadAllBytes(templateFile);
            MemoryStream templateMs = new MemoryStream(tempData);
            MemoryStream ms = null;
            IWorkbook workbook = null;
            try
            {
                workbook = new XSSFWorkbook(templateMs);
                var sheet = workbook.GetSheetAt(0);
                sheet.SetColumnWidth(1, 10 * 256);
                sheet.SetColumnWidth(2, 10 * 256);
                sheet.SetColumnWidth(3, 10 * 256);
                sheet.SetColumnWidth(4, 20 * 256);
                sheet.SetColumnWidth(5, 30 * 256);
                sheet.SetColumnWidth(6, 30 * 256);
                sheet.SetColumnWidth(7, 20 * 256);
                sheet.SetColumnWidth(8, 20 * 256);
                sheet.SetColumnWidth(9, 30 * 256);
                sheet.SetColumnWidth(10, 30 * 256);
                if (quotations?.Any() == true)
                {
                    var groups = quotations.GroupBy(i => i.线路名称);
                    int contentRowIdx = 2;

                    int rowCount = quotations.Count();
                    //sheet.ShiftRows(contentRowIdx, rowCount + contentRowIdx, rowCount);
                    InsertRows(ref sheet, contentRowIdx, rowCount);
                    var contentCellStyle = CreateContentCellStyle(workbook);


                    foreach (var g in groups)
                    {
                        string channelName = g.Key;
                        bool channelNameCellMerged = false;

                        var sortedByWeight = g.OrderBy(i => i.重量段);
                        foreach (var q in sortedByWeight)
                        {
                            IRow contentRow = sheet.CreateRow(contentRowIdx);
                            contentRow.Height = 32 * 20;
                            if (g.Count() > 1 && channelNameCellMerged == false)
                            {
                                int mergeRowCount = contentRowIdx + g.Count() - 1;
                                sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 1, 1));
                                sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 5, 5));
                                sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 6, 6));
                                sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 7, 7));
                                sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 8, 8));
                                sheet.AddMergedRegion(new CellRangeAddress(contentRowIdx, mergeRowCount, 9, 9));
                                channelNameCellMerged = true;
                            }

                            ICell freightCell = null;
                            ICell operateFeeCell = null;
                            foreach (var t in headers)
                            {
                                var cell = contentRow.CreateCell(t.Item2);
                                cell.CellStyle = contentCellStyle;


                                switch (t.Item2)
                                {
                                    case 1:
                                        cell.SetCellValue(q.线路名称);
                                        break;
                                    case 2:
                                        if (decimal.TryParse(q.运费, out decimal freight))
                                            cell.SetCellValue((double)freight);
                                        else cell.SetCellValue(q.运费);

                                        freightCell = cell;
                                        break;
                                    case 3:
                                        if (decimal.TryParse(q.操作费, out decimal operateFee))
                                            cell.SetCellValue((double)operateFee);
                                        else cell.SetCellValue(q.操作费);

                                        operateFeeCell = cell;
                                        break;
                                    case 4:
                                        cell.SetCellValue(q.重量段);
                                        break;
                                    case 5:
                                        string refEffective = GetRefEffective(q.线路名称);
                                        cell.SetCellValue(refEffective);
                                        break;
                                    case 6:
                                        string dimension = GetDimension(q.线路名称);
                                        cell.SetCellValue(dimension);
                                        break;
                                    case 7:
                                        cell.SetCellValue(q.渠道代码);
                                        break;
                                    case 8:
                                        cell.SetCellValue(showSurcharge ? SurchargeDesc : string.Empty);
                                        break;
                                    case 9:
                                        string remark = GetRemark(q.线路名称);
                                        cell.SetCellValue(remark);
                                        break;
                                    case 10:
                                        string enableTime = q.生效时间;
                                        if (DateTime.TryParse(enableTime, out DateTime _enableTime) && _enableTime > DateTime.Now)
                                        {
                                            var style = CreateContentCellStyle(workbook);
                                            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                                            style.FillPattern = FillPattern.SolidForeground;
                                            cell.CellStyle = style;
                                            if (freightCell != null)
                                            {
                                                freightCell.CellStyle = style;
                                            }
                                            if (operateFeeCell != null)
                                            {
                                                operateFeeCell.CellStyle = style;
                                            }
                                        }

                                        cell.SetCellValue(enableTime);
                                        break;
                                }

                            }

                            contentRowIdx++;
                        }
                    }

                    //有些模板shiftRows之后，模板尾部的内容会莫名其妙的丢失合并，所以要重新做一下合并
                    if (templateFile.Contains("美国快线报价模板"))
                    {
                        var lastRowIdx = sheet.LastRowNum;
                        for (int i = contentRowIdx; i <= lastRowIdx; i++)
                        {
                            var row = sheet.GetRow(i);
                            if (row != null)
                            {
                                if (!sheet.IsMergedRegion(new CellRangeAddress(i, i, 1, 1)) && !sheet.IsMergedRegion(new CellRangeAddress(i, i, 2, 9)))
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(i, i, 1, 9));
                                }

                            }
                        }
                    }
                    if (templateFile.Contains("带电专线"))
                    {
                        var lastRowIdx = sheet.LastRowNum;
                        for (int i = contentRowIdx; i <= lastRowIdx; i++)
                        {
                            var row = sheet.GetRow(i);
                            if (row != null && row.Cells.Count > 1)
                            {
                                var cell = row.GetCell(1);
                                if (cell.CellType == CellType.String &&
                                    (cell.StringCellValue == "索赔标准" || cell.StringCellValue == "特别提示") &&
                                    !sheet.IsMergedRegion(new CellRangeAddress(i, i, 2, 10))
                                    )
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(i, i, 2, 10));
                                }
                            }
                        }
                    }
                }
                ms = new MemoryStream();
                workbook.Write(ms);
                var bytes = ms.ToArray();
                return bytes;
            }
            finally
            {
                if (templateMs != null)
                {
                    templateMs.Close();
                    templateMs.Dispose();
                }
                if (ms != null)
                {
                    ms.Close();
                    ms.Dispose();
                }
            }


        }
        static void InsertRows(ref ISheet sheet1, int fromRowIndex, int rowCount)
        {
            try
            {
                sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false);
            }
            catch (Exception ex)
            {

                throw;
            }


            for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
            {
                IRow rowSource = sheet1.GetRow(rowIndex + rowCount);
                if (rowSource == null)
                {
                    continue;
                }
                IRow rowInsert = sheet1.CreateRow(rowIndex);
                rowInsert.Height = rowSource.Height;
                for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                {
                    ICell cellSource = rowSource.GetCell(colIndex);
                    ICell cellInsert = rowInsert.CreateCell(colIndex);
                    if (cellSource != null)
                    {
                        cellInsert.CellStyle = cellSource.CellStyle;
                    }
                }
            }
        }
        private ICellStyle CreateNoticeCellStyle(IWorkbook workbook)
        {
            ICellStyle noticeCellStyle = workbook.CreateCellStyle();
            noticeCellStyle.FillForegroundColor = 47;
            noticeCellStyle.FillPattern = FillPattern.SolidForeground;
            noticeCellStyle.BorderBottom = noticeCellStyle.BorderLeft = noticeCellStyle.BorderRight = noticeCellStyle.BorderTop = BorderStyle.Thin;
            return noticeCellStyle;
        }

        private ICellStyle CreateContentCellStyle(IWorkbook workbook)
        {
            var contentCellStyle = workbook.CreateCellStyle();
            contentCellStyle.BorderTop = contentCellStyle.BorderBottom = contentCellStyle.BorderLeft = contentCellStyle.BorderRight = BorderStyle.Thin;
            contentCellStyle.VerticalAlignment = VerticalAlignment.Center;
            contentCellStyle.Alignment = HorizontalAlignment.Center;
            contentCellStyle.WrapText = true;
            return contentCellStyle;
        }

        private static readonly List<string> noticeItems = new List<string>
            {
                 @"1. 申请赔偿期限为在运输过程中我司确认货物整票丢失或者扣关起，客户需在发货后45天内提出申请，超过45天不再受理索赔事宜；",
                 @"2. 如因货物问题导致扣关，或侵权间题，我司不承担任何责任并且有权追究发件人因此对我司带来的损失；",
                 @"3. 如货物没有按照实际价值申报、侵犯知识产权、产品配件不符合欧盟国家的要求、当地禁止进口等原因而导致的海关扣货，此情况不在赔偿范围之内，我司只是协助发件人提供清关文件；",
                 @"4. 如遇自然灾害等不可抗因素，本公司不做任何赔偿；",
                 @"5. 产品数量缺少、部份丢失/损坏不受理赔偿； ",
                 @"6. 易碎品不接受任何损坏赔偿； ",
                 @"7. 高价值货物清自行购买保险；",
            };

        private string ReadNoticeContent(out int lines)
        {
            lines = noticeItems.Count;
            return string.Join(Environment.NewLine, noticeItems);
        }

        List<Tuple<string, int>> headers = new List<Tuple<string, int>> {
                 Tuple.Create("线路名称",1),
                 Tuple.Create("运费/KG",2),
                 Tuple.Create("操作费",3),
                 Tuple.Create("重量段",4),
                 Tuple.Create("参考时效",5),
                 Tuple.Create("尺寸限制",6),
                 Tuple.Create("渠道代码",7),
                 Tuple.Create("附加费",8),
                 Tuple.Create("备注",9),
                 Tuple.Create("生效时间",10)
            };

        private static List<string> claimItems = new List<string>
        {
            @"1. 提取前遗失，按货物釆购成本赔偿，退运费，最高不超过100元/票；",
            @"2. 提取后遗失，按货物釆购成本赔偿，最高不超过100元/票，不退运费；"
        };

        private string ReadClaimStandardContent(out int lines)
        {
            lines = claimItems.Count;
            return string.Join(Environment.NewLine, claimItems);
        }

        private static readonly Dictionary<string, string> 参考时效 = new Dictionary<string, string>
        {
            { "德国专线","6-10工作日"},
            { "法国专线","6-10工作日"},
            { "澳洲专线","7-15工作日"},
            { "意大利专线","7-10工作日"},
            { "巴西专线","15-30工作日"},
            { "英国专线","6-10工作日"},
            { "加拿大专线","7-10工作日"},
            { "美国专线","7-10工作日"},
            { "美国专线带电","7-10工作日"},
            { "德国专线带电","7-15工作日"},
            { "英国专线带电","7-15工作日"},
            { "法国专线带电","7-15工作日"},
            { "意大利专线带电","7-15工作日"},
            { "美国快线","5-7工作日"},
            { "德国快线","10天"},
            { "新西兰专线","6-8工作日"},
            { "英国快线","3-5工作日"},
            { "美森限时达海派","11天到港；六截三开；开船后第二天开始算，18自然日提取（不包含海关检查)。第19天开始赔偿1元每公斤，直到单票运费赔完为止。"},
            { "美森正班船海派A价","11天到港，六截三开，周六装柜，周三开船"},
            { "美森正班船海派B价","11天到港，六截三开，周六装柜，周三开船"},
            { "美森加班船海派","11天到港，六截四开，周六装柜，周四开船"},
        };
        private static readonly Dictionary<string, string> 备注 = new Dictionary<string, string>
        {
             { "意大利专线","意大利偏远10元/票"},
             { "澳洲专线","跟踪单号为33/34/36开头收取偏远费用20元/票。"},
             { "加拿大专线","申报价值20加币以内免税，超过20加币需支付税金，后更换末端派送单号"},
             { "新西兰专线","申报价值：不接受申报价值超过400新西兰币的包裹。"},
            { "美森限时达海派","时效承诺，未在承诺时效到达必赔"},
            { "美森正班船海派A价","专用码头卸货，提柜相对较快"},
            { "美森正班船海派B价","专用码头卸货，提柜相对较快"},
            { "美森加班船海派","尾程UPS派送，这个不是在美森专用码头卸货，整体时效慢一周左右"},
        };
        private static readonly Dictionary<string, string> 尺寸限制 = new Dictionary<string, string>
        {
             { "德国专线",@"最大尺寸限制60*60*120"},
             { "法国专线",@"最长边不超过 100cm，长+宽+高不超过 150cm"},
             { "澳洲专线",@"单边不超过1m，实重和体积重都不超过20kg"},
             { "意大利专线",@"长+宽+高≤150CM，单边长度≤100CM"},
             { "巴西专线",@"单边长度≤ 60cm;其他两边任意一边不超过40cm"},
             { "英国专线",@"最小尺寸10*15cm，最大尺寸：60cm*45cm*45cm"},
             { "新西兰专线",@"单边长度≤1.50m; 长*宽*高≤0.125 立方米"},
             { "加拿大专线",@"最长边≤1米，第二边长≤76厘米，长+(宽+高)*2≤2.5米"},
             { "美国专线",@"最长边≤55CM，长+(宽+高)*2≤200CM；最小尺寸限制：15*10CM"},
            { "美国快线","L≤60cm 长+2*（宽+高）≦250cm"},
            { "德国快线","L*W*H≦120*60*60cm,L+2*(W+H)≦360cm"},
            { "德国专线带电","单边长度≤ 60cm;其他两边任意一边不超过40cm"},
            { "英国专线带电","单边长度≤ 60cm;其他两边任意一边不超过40cm"},
            { "法国专线带电","单边长度≤ 60cm;其他两边任意一边不超过40cm"},
            { "意大利专线带电","单边长度≤ 60cm;其他两边任意一边不超过40cm"},
            { "美国专线带电","最长边≤60CM，长+(宽+高)*2≤210CM；最小尺寸限制：15*10CM"},
            { "英国快线","长*宽*高≤60cm*45cm*45cm"},
        };

        private string GetRefEffective(string channelName)
        {
            参考时效.TryGetValue(channelName, out string result);
            return result;

        }


        private string GetRemark(string channelName)
        {
            备注.TryGetValue(channelName, out string result);
            return result;
        }



        private string GetDimension(string channelName)
        {
            尺寸限制.TryGetValue(channelName, out string result);
            return result;
        }

        private string SurchargeDesc
        {
            get { return "使用邮局单号1元/票"; }
        }

        public class GorupModel
        {
            public string 线路名称
            {
                get; set;
            }

            public string 渠道代码
            {
                get; set;
            }
            public int Sort
            {
                get; set;
            }
        }

        internal void MakeUSHPFile(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == true)
                {

                    var workbook = app.Workbooks._Open(templateFile);
                    var tempsheet = (Worksheet)workbook.ActiveSheet;
                    tempsheet.Copy(Type.Missing, source.ActiveSheet);
                    workbook.Close();
                    var sheet = (Worksheet)source.ActiveSheet;
                    foreach (var item in quotations)
                    {
                        if (item.线路名称 == "美森限时达海派")
                        {
                            item.Sort = 1;
                        }
                        else if (item.线路名称 == "美森正班船海派A价")
                        {
                            item.Sort = 2;
                        }
                        else if (item.线路名称 == "美森正班船海派B价")
                        {
                            item.Sort = 3;
                        }
                        else if (item.线路名称 == "美森加班船海派")
                        {
                            item.Sort = 4;
                        }
                    }
                    var groups = quotations.GroupBy(i => new { 线路名称 = i.线路名称, 渠道代码 = i.渠道代码, Sort = i.Sort }).OrderBy(x => x.Key.Sort);



                    int insertCount = 0;
                    //暂时只加空派的

                    var g2 = quotations.GroupBy(x => new { x.重量段 }).ToList();

                    //列我们仅添加一次
                    //重量
                    //渠道代码
                    //生效时间
                    //备注说明
                    for (int i = 1; i < g2.Count + 4; i++)
                    {
                        Microsoft.Office.Interop.Excel.Range range2 = (Microsoft.Office.Interop.Excel.Range)sheet.Columns[i + 1, Missing.Value];
                        range2.Insert(XlInsertFormatOrigin.xlFormatFromLeftOrAbove, Missing.Value);
                    }


                    foreach (var g in groups)
                    {
                        //if (g.Key == "欧洲空派包税")
                        //{

                        var g3 = g.GroupBy(x => new { x.操作费 }).ToList();
                        //插入行



                        int rowCount = g3.Count();
                        int rowIndex = 0;
                        switch (g.Key.线路名称)
                        {
                            case "美森限时达海派":
                                rowIndex = 4;
                                break;

                            case "美森正班船海派A价":
                                rowIndex = 11;
                                break;

                            case "美森正班船海派B价":
                                rowIndex = 16;
                                break;

                            case "美森加班船海派":
                                rowIndex = 23;
                                break;
                        }

                        for (int i = 0; i < g2.Count; i++)
                        {
                            sheet.get_Range(GetC(1 + i) + (rowIndex - 1)).Value2 = g2[i].Key.重量段.Replace(".000", "").Replace("-100000", "+");
                        }

                        sheet.get_Range(GetC(1 + g2.Count) + (rowIndex - 1)).Value2 = "渠道代码";
                        sheet.get_Range(GetC(2 + g2.Count) + (rowIndex - 1)).Value2 = "参考时效";
                        sheet.get_Range(GetC(3 + g2.Count) + (rowIndex - 1)).Value2 = "生效时间";
                        sheet.get_Range(GetC(4 + g2.Count) + (rowIndex - 1)).Value2 = "优势";

                        for (int i = 0; i < rowCount; i++)
                        {
                            Microsoft.Office.Interop.Excel.Range range2 =
                                (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Missing.Value];
                            range2.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
                        }

                        var range = sheet.get_Range("A" + rowIndex);
                        range = range.get_Resize(rowCount, 7 + g2.Count - 1);
                        range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                        range.Font.Size = 14;
                        range.RowHeight = 40;
                        range.Interior.Color = System.Drawing.Color.White;
                        range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                        var v = range.Value;

                        object[,] cellData = new object[rowCount, 7 + g2.Count() - 1];

                        for (var i = 0; i < rowCount; i++)
                        {
                            string country = g3[i].Key.操作费;
                            cellData[i, 0] = country;
                            for (int j = 0; j < g2.Count; j++)
                            {
                                cellData[i, 1 + j] = g.Where(x => x.操作费 == country && x.重量段 == g2[j].Key.重量段).First().运费;
                            }
                            cellData[i, 1 + g2.Count] = g.Key.渠道代码;
                            cellData[i, 2 + g2.Count] = 参考时效[g.Key.线路名称];
                            cellData[i, 3 + g2.Count] = g.First().生效时间;
                            cellData[i, 4 + g2.Count] = 备注[g.Key.线路名称];
                        }

                        //字符串格式

                        range.set_Value(Missing.Value, cellData);
                        //var formRange = sheet.get_Range(GetC(2 + g2.Count - 1) + rowIndex);
                        //formRange = formRange.get_Resize(rowCount, 1);
                        //formRange.NumberFormat = "@";

                        var mangerRange1 = sheet.get_Range(GetC(2 + g2.Count - 1) + rowIndex);
                        mangerRange1 = mangerRange1.get_Resize(rowCount, 1);
                        mangerRange1.Merge();
                        mangerRange1.WrapText = true;


                        var mangerRange2 = sheet.get_Range(GetC(3 + g2.Count - 1) + rowIndex);
                        mangerRange2 = mangerRange2.get_Resize(rowCount, 1);
                        mangerRange2.Merge();

                        mangerRange2 = sheet.get_Range(GetC(4 + g2.Count - 1) + rowIndex);
                        mangerRange2 = mangerRange2.get_Resize(rowCount, 1);
                        mangerRange2.Merge();
                        mangerRange2.NumberFormat = "yyyy-MM-dd";

                        var mangerRange3 = sheet.get_Range(GetC(5 + g2.Count - 1) + rowIndex);
                        mangerRange3 = mangerRange3.get_Resize(rowCount, 1);
                        mangerRange3.Merge();
                        if (g.FirstOrDefault()?.生效时间 != null && DateTime.Parse(g.FirstOrDefault()?.生效时间) >= DateTime.Now)
                        {
                            mangerRange2.Interior.Color = System.Drawing.Color.Yellow;
                        }
                        //}
                    }

                    //sheet.Copy(Type.Missing, source.ActiveSheet);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region 2022
        #region 专线模板写入
        /// <summary>
        /// 专线模板通用
        /// </summary>
        /// <param name="quotations"></param>
        /// <param name="app"></param>
        /// <param name="templateFile"></param>
        /// <param name="showSurcharge"></param>
        /// <returns></returns>
        public Worksheet MakeQuotationFile2022(IEnumerable<View_Quotation> quotations, Microsoft.Office.Interop.Excel.Application app, Workbook source, string templateFile, bool showSurcharge = true, bool IsFirst = false)
        {
            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            if (IsFirst) { tempsheet.Copy(source.Sheets[1], Type.Missing); } else { tempsheet.Copy(Type.Missing, source.ActiveSheet); }

            var sheet = (Worksheet)source.ActiveSheet;

            if (quotations?.Any() == true)
            {
                var groups = quotations.GroupBy(i => i.线路名称);
                int contentRowIdx = 3;

                int rowCount = quotations.Count();
                if (rowCount > 1)
                {
                    InsertRows2022(sheet, contentRowIdx, rowCount, "A", "J", "K");
                }

                object[,] obj = new object[quotations.Count(), 10];
                int i = 0;
                List<int> needBackgroundColorRow = new List<int>();
                foreach (var g in groups)
                {
                    string channelName = g.Key;
                    var sortedByWeight = g.OrderBy(i => i.重量段);
                    foreach (var q in sortedByWeight)
                    {
                        obj[i, 0] = q.线路名称;
                        obj[i, 1] = decimal.TryParse(q.运费, out decimal freight) ? (double)freight : q.运费;
                        obj[i, 2] = decimal.TryParse(q.操作费, out decimal operateFee) ? (double)operateFee : q.操作费;
                        obj[i, 3] = q.重量段;
                        string refEffective = GetRefEffective(q.线路名称);
                        obj[i, 4] = refEffective;
                        string dimension = GetDimension(q.线路名称);
                        obj[i, 5] = dimension;
                        obj[i, 6] = q.渠道代码;
                        obj[i, 7] = showSurcharge ? SurchargeDesc : string.Empty;
                        string remark = GetRemark(q.线路名称);
                        obj[i, 8] = remark;
                        string enableTime = q.生效时间;
                        if (DateTime.TryParse(enableTime, out DateTime _enableTime) && _enableTime > DateTime.Now)
                        {
                            needBackgroundColorRow.Add(i);
                        }
                        obj[i, 9] = enableTime;
                        i++;
                    }
                }
                var range = sheet.get_Range("A3");
                range = range.get_Resize(i, 10);
                range.set_Value(Missing.Value, obj);
                //背景色处理
                foreach (var item in needBackgroundColorRow)
                {
                    sheet.Range[$"B{contentRowIdx + item}:B{contentRowIdx + item}"].Interior.Color = System.Drawing.Color.Yellow;
                    sheet.Range[$"C{contentRowIdx + item}:C{contentRowIdx + item}"].Interior.Color = System.Drawing.Color.Yellow;
                }
                //合并相同行
                MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "E", "F", "G", "H", "I", "J" }, rowCount);
            }
            return sheet;
        }
        #endregion
        #region FBA模板写入
        public bool MakeUSKPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;

                var vat5k = quotations.Where(i => i.线路名称 == "美国空派普货专线(体积/5000计费)").OrderBy(x => x.运费).ToList();
                var vat6k = quotations.Where(i => i.线路名称 == "美国空派普货专线(体积/6000计费)").OrderBy(x => x.运费).ToList();
                var group5ks = vat5k.GroupBy(x => x.操作费).ToList();//.OrderBy(x => x.Key)
                var group6ks = vat6k.GroupBy(x => x.操作费).ToList();//.OrderBy(x => x.Key)

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                Dictionary<string, string> areas = new Dictionary<string, string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight, item);
                }
                int contentRowIdx = 3;//第一个从第3行开始

                #region 增加列的处理
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "H";

                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);//有一列默认的复制行，所以减一
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);//菜单减1就是内容最后列
                #endregion

                #region 6K 
                var templateColName = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColName + (colRowIdx) + ":" + templateColName + (colRowIdx)];
                    colRange.Value = item.Key;
                    var indexCol = getIndexFromChar(templateColName, 1);
                    templateColName = indexCol.Item2.ToUpper();
                }

                int rowCount = group6ks.Count();
                if (vat6k != null && vat6k.Count > 0)
                {
                    InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);
                    object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCount; i++)
                    {
                        string showKey = group6ks[i].Key;//历史数据问题，6k需要转显示名称
                        switch (group6ks[i].Key)
                        {
                            case "美中0-3邮编开头":
                                showKey = "美东0-3邮编开头";
                                break;
                        }
                        mm[i, 0] = "美国空派普货专线(体积/6000计费)";
                        mm[i, 1] = "USKPLine";
                        mm[i, 2] = showKey;
                        var tempData = vat6k.Where(x => x.操作费 == group6ks[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Value.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mm[i, 2 + num + 1] = @"交货后5-8个自然日提取";
                        mm[i, 2 + num + 2] = @"交货第二天起算 超10自然日未提取延误赔偿1元/KG/自然日， 最高赔偿20元/KG。国内/国外假期不计算在内 ，提取当天不计算在内 。";
                        mm[i, 2 + num + 3] = vat6k.FirstOrDefault().生效时间;
                    }
                    var range = sheet.get_Range("A" + contentRowIdx);
                    range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
                    range.set_Value(Missing.Value, mm);
                    //合并相同行
                    MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, group6ks.Count());
                }
                #endregion

                #region 5K 
                //第二个开始行=第一个开始行+写入行数+第二个表头2行   提示：一和二均要保留一行默认的格式行
                int contentRowIdxVat = contentRowIdx + (rowCount == 0 ? 1 : rowCount) + 2;
                int colRowIdxVat = contentRowIdxVat - 1;
                var templateColNameVat = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColNameVat + (colRowIdxVat) + ":" + templateColNameVat + (colRowIdxVat)];
                    colRange.Value = item.Key;
                    var indexCol = getIndexFromChar(templateColNameVat, 1);
                    templateColNameVat = indexCol.Item2.ToUpper();
                }

                if (vat5k != null && vat5k.Count > 0)
                {
                    int rowCountVat = group5ks.Count();
                    InsertRows2022(sheet, contentRowIdxVat, rowCountVat, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                    object[,] mmVat = new object[rowCountVat, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCountVat; i++)
                    {
                        mmVat[i, 0] = "美国空派普货专线(体积/5000计费)";
                        mmVat[i, 1] = "USKPLine-5K";
                        mmVat[i, 2] = group5ks[i].Key;
                        var tempData = vat5k.Where(x => x.操作费 == group5ks[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mmVat[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Value.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mmVat[i, 2 + num + 1] = @"交货后5-8个自然日提取";
                        mmVat[i, 2 + num + 2] = @"交货第二天起算 超10自然日未提取延误赔偿1元/KG/自然日， 最高赔偿20元/KG。国内/国外假期不计算在内 ，提取当天不计算在内 。";
                        mmVat[i, 2 + num + 3] = vat5k.FirstOrDefault().生效时间;
                    }
                    var rangeVat = sheet.get_Range("A" + contentRowIdxVat);
                    rangeVat = rangeVat.get_Resize(rowCountVat, templateBehindContentEndCol.Item1);
                    rangeVat.set_Value(Missing.Value, mmVat);
                    //合并相同行
                    MergeCell(sheet, contentRowIdxVat, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, rowCountVat);
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public bool MakeUSECOKPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;

                var vat5k = quotations.Where(i => i.线路名称 == "美国空派普货经济专线(体积/5000计费)").OrderBy(x => x.运费).ToList();
                var vat6k = quotations.Where(i => i.线路名称 == "美国空派普货经济专线(体积/6000计费)").OrderBy(x => x.运费).ToList();
                var group5ks = vat5k.GroupBy(x => x.操作费).ToList();//.OrderBy(x => x.Key)
                var group6ks = vat6k.GroupBy(x => x.操作费).ToList();//.OrderBy(x => x.Key)

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                Dictionary<string, string> areas = new Dictionary<string, string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight, item);
                }
                int contentRowIdx = 3;//第一个从第3行开始

                #region 增加列的处理
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "H";

                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);//有一列默认的复制行，所以减一
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);//菜单减1就是内容最后列
                #endregion

                #region 6K 
                var templateColName = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColName + (colRowIdx) + ":" + templateColName + (colRowIdx)];
                    colRange.Value = item.Key;
                    var indexCol = getIndexFromChar(templateColName, 1);
                    templateColName = indexCol.Item2.ToUpper();
                }

                int rowCount = group6ks.Count();
                if (vat6k != null && vat6k.Count > 0)
                {
                    InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);
                    object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCount; i++)
                    {
                        mm[i, 0] = vat6k[i].线路名称;
                        mm[i, 1] = vat6k[i].渠道代码;
                        mm[i, 2] = group6ks[i].Key;
                        var tempData = vat6k.Where(x => x.操作费 == group6ks[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Value.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mm[i, 2 + num + 1] = @"交货后7-10个自然日提取";
                        mm[i, 2 + num + 2] = @"交货第二天起算 超10工作日未提取延误赔偿1元/KG/工作日， 最高赔偿20元/KG。国内/国外假期不计算在内 ，提取当天不计算在内 。";
                        mm[i, 2 + num + 3] = vat6k.FirstOrDefault().生效时间;
                    }
                    var range = sheet.get_Range("A" + contentRowIdx);
                    range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
                    range.set_Value(Missing.Value, mm);
                    //合并相同行
                    MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, group6ks.Count());
                }
                #endregion

                #region 5K 
                //第二个开始行=第一个开始行+写入行数+第二个表头2行   提示：一和二均要保留一行默认的格式行
                int contentRowIdxVat = contentRowIdx + (rowCount == 0 ? 1 : rowCount) + 2;
                int colRowIdxVat = contentRowIdxVat - 1;
                var templateColNameVat = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColNameVat + (colRowIdxVat) + ":" + templateColNameVat + (colRowIdxVat)];
                    colRange.Value = item.Key;
                    var indexCol = getIndexFromChar(templateColNameVat, 1);
                    templateColNameVat = indexCol.Item2.ToUpper();
                }

                if (vat5k != null && vat5k.Count > 0)
                {
                    int rowCountVat = group5ks.Count();
                    InsertRows2022(sheet, contentRowIdxVat, rowCountVat, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                    object[,] mmVat = new object[rowCountVat, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCountVat; i++)
                    {
                        mmVat[i, 0] = vat5k[i].线路名称;
                        mmVat[i, 1] = vat5k[i].渠道代码;
                        mmVat[i, 2] = group5ks[i].Key;
                        var tempData = vat5k.Where(x => x.操作费 == group5ks[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mmVat[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Value.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mmVat[i, 2 + num + 1] = @"交货后7-10个自然日提取";
                        mmVat[i, 2 + num + 2] = @"交货第二天起算 超10工作日未提取延误赔偿1元/KG/工作日， 最高赔偿20元/KG。国内/国外假期不计算在内 ，提取当天不计算在内 。";
                        mmVat[i, 2 + num + 3] = vat5k.FirstOrDefault().生效时间;
                    }
                    var rangeVat = sheet.get_Range("A" + contentRowIdxVat);
                    rangeVat = rangeVat.get_Resize(rowCountVat, templateBehindContentEndCol.Item1);
                    rangeVat.set_Value(Missing.Value, mmVat);
                    //合并相同行
                    MergeCell(sheet, contentRowIdxVat, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, rowCountVat);
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeUSKKFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;
                //默认模板信息初始化
                string copyCol = "F"; string templateMenuEndCol = "J";

                int contentRowIdx = 3;
                var groups = quotations.GroupBy(q => q.操作费).OrderBy(x => x.Key, new CharsComparer()).ThenBy(x => x.Key).ToList();

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }
                #region 增加列的处理
                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                foreach (var item in areas)//无论是否新增都循环修改列头名称
                {
                    var colRange = sheet.Range[copyCol + (colRowIdx) + ":" + copyCol + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(copyCol, 1);
                    copyCol = indexCol.Item2.ToUpper();
                }
                #endregion
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                object[,] mm = new object[groups.Count, templateBehindContentEndCol.Item1];
                for (int i = 0; i < groups.Count(); i++)
                {
                    mm[i, 0] = quotations.FirstOrDefault().线路名称;
                    mm[i, 1] = quotations.FirstOrDefault().渠道代码;
                    mm[i, 2] = groups[i].Key.Split(',')[0];
                    mm[i, 3] = groups[i].Key.Split(',')[1];
                    mm[i, 4] = groups[i].Key.Split(',')[2];
                    var tempData = quotations.Where(x => x.操作费 == groups[i].Key).ToList();
                    int num = 0;
                    foreach (var item in areas)
                    {
                        num++;
                        mm[i, 4 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                    }
                    mm[i, 4 + num + 1] = @"交货后12个自然日左右提供POD";
                    mm[i, 4 + num + 2] = @"交货第二天起算 超14自然日延误赔偿1元/KG/自然日， 最高赔偿20元/KG。国内/国外假期不计算在内 ，提取当天不计算在内 。";
                    mm[i, 4 + num + 3] = quotations.FirstOrDefault().生效时间;
                }
                var range = sheet.get_Range("A3");
                range = range.get_Resize(groups.Count(), templateBehindContentEndCol.Item1);
                range.set_Value(Missing.Value, mm);
                //合并相同行
                MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindContentEndCol.Item2, -2).Item2, getIndexFromChar(templateBehindContentEndCol.Item2, -1).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
                MergeCell(sheet, contentRowIdx, "E", new string[] { "E" }, groups.Count());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeEUKPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;


                var notVat = quotations.Where(i => i.线路名称 == "欧洲空派包税").ToList();
                var vats = quotations.Where(i => i.线路名称 != "欧洲空派包税").ToList();
                var groups = notVat.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();
                var groupVats = vats.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();

                List<int> needBackgroundColorRow = new List<int>();

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }

                int contentRowIdx = 7;//第一个从第七行开始

                #region 增加列的处理
                //默认模板信息初始化
                string copyCol = "C"; string templateMenuEndCol = "G";

                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);//有一列默认的复制行，所以减一
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);//菜单减1就是内容最后列
                #endregion
                #region 包税处理
                var templateColName = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColName + (colRowIdx) + ":" + templateColName + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColName, 1);
                    templateColName = indexCol.Item2.ToUpper();
                }

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                if (notVat != null && notVat.Count > 0)
                {
                    object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCount; i++)
                    {
                        mm[i, 0] = groups[i].Key;
                        mm[i, 1] = "OZKPBS";
                        var tempData = notVat.Where(x => x.操作费 == groups[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mm[i, 1 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mm[i, 1 + num + 1] = @"6-8工作日提取
DPD派送";
                        mm[i, 1 + num + 2] = "OZKPBS";
                        if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                        {
                            needBackgroundColorRow.Add(contentRowIdx + i);
                        }
                        mm[i, 1 + num + 3] = notVat.FirstOrDefault().生效时间;
                    }
                    var range = sheet.get_Range("A" + contentRowIdx);
                    range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
                    range.set_Value(Missing.Value, mm);
                    //合并相同行
                    MergeCell(sheet, contentRowIdx, "B", new string[] { "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
                }
                #endregion
                #region 不包税处理
                //第二个开始行=第一个开始行+写入行数+第二个表头3行   提示：一和二均要保留一行默认的格式行
                int contentRowIdxVat = contentRowIdx + rowCount + 3;
                int colRowIdxVat = contentRowIdxVat - 1;
                var templateColNameVat = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColNameVat + (colRowIdxVat) + ":" + templateColNameVat + (colRowIdxVat)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColNameVat, 1);
                    templateColNameVat = indexCol.Item2.ToUpper();
                }

                if (vats != null && vats.Count > 0)
                {
                    int rowCountVat = groupVats.Count();
                    InsertRows2022(sheet, contentRowIdxVat, rowCountVat, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                    object[,] mmVat = new object[rowCountVat, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCountVat; i++)
                    {
                        mmVat[i, 0] = groupVats[i].Key;
                        mmVat[i, 1] = "OZKPVAT";
                        var tempData = vats.Where(x => x.操作费 == groupVats[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mmVat[i, 1 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mmVat[i, 1 + num + 1] = @"6-8工作日提取
DPD派送";
                        mmVat[i, 1 + num + 2] = "OZKPVAT";
                        if (vats.FirstOrDefault().生效时间 != null && DateTime.Parse(vats.FirstOrDefault().生效时间) >= DateTime.Now)
                        {
                            needBackgroundColorRow.Add(contentRowIdxVat + i);
                        }
                        mmVat[i, 1 + num + 3] = vats.FirstOrDefault().生效时间;
                    }
                    var rangeVat = sheet.get_Range("A" + contentRowIdxVat);
                    rangeVat = rangeVat.get_Resize(rowCountVat, templateBehindContentEndCol.Item1);
                    rangeVat.set_Value(Missing.Value, mmVat);
                    //合并相同行
                    MergeCell(sheet, contentRowIdxVat, "B", new string[] { "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, rowCountVat);
                }
                #endregion
                //背景色处理
                foreach (var item in needBackgroundColorRow)
                {
                    sheet.Range[$"{copyCol}{item}:{templateColNameVat}{item}"].Interior.Color = System.Drawing.Color.Yellow;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeUSMSHPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;


                var notVat = quotations.Where(i => i.线路名称 == "美森正班船海派").ToList();
                var vats = quotations.Where(i => i.线路名称 != "美森正班船海派").ToList();
                var groups = notVat.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();
                var groupVats = vats.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();

                List<int> needBackgroundColorRow = new List<int>();

                var weightArea = notVat.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }

                int contentRowIdx = 3;//第一个从第3行开始

                #region 增加列的处理
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "H";

                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);//有一列默认的复制行，所以减一
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);//菜单减1就是内容最后列
                #endregion
                #region 美森正班船海派
                var templateColName = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColName + (colRowIdx) + ":" + templateColName + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColName, 1);
                    templateColName = indexCol.Item2.ToUpper();
                }

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
                for (int i = 0; i < rowCount; i++)
                {
                    mm[i, 0] = "美森正班船海派";
                    mm[i, 1] = "MS-CLX";
                    mm[i, 2] = groups[i].Key;
                    var tempData = notVat.Where(x => x.操作费 == groups[i].Key).ToList();
                    int num = 0;
                    foreach (var item in areas)
                    {
                        num++;
                        mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                    }
                    mm[i, 2 + num + 1] = @"19个自然日左右提取，
开船后超过19个自然日未提取，一天赔1元/KG，赔完为止（节假日，国内海关查验相关部门审核资料及码头罢工以及UPS/Fedex爆仓等不可抗拒因素不赔偿）";

                    if (groups[i].Key.Contains("8")) { mm[i, 2 + num + 2] = "2-3工作日"; }
                    else if (groups[i].Key.Contains("5")) { mm[i, 2 + num + 2] = "3-4工作日"; }
                    else { mm[i, 2 + num + 2] = "4-5工作日"; }

                    if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                    {
                        needBackgroundColorRow.Add(contentRowIdx + i);
                    }
                    mm[i, 2 + num + 3] = notVat.FirstOrDefault().生效时间;
                }
                var range = sheet.get_Range("A" + contentRowIdx);
                range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
                range.set_Value(Missing.Value, mm);
                //合并相同行
                MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
                #endregion
                #region 美森加班船海派
                //第二个开始行=第一个开始行+写入行数+第二个 空一行，表头一行   提示：一和二均要保留一行默认的格式行
                int contentRowIdxVat = contentRowIdx + rowCount + 2;
                int colRowIdxVat = contentRowIdxVat - 1;
                var templateColNameVat = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColNameVat + (colRowIdxVat) + ":" + templateColNameVat + (colRowIdxVat)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColNameVat, 1);
                    templateColNameVat = indexCol.Item2.ToUpper();
                }
                int rowCountVat = groupVats.Count();
                InsertRows2022(sheet, contentRowIdxVat, rowCountVat, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                object[,] mmVat = new object[rowCountVat, templateBehindContentEndCol.Item1];
                for (int i = 0; i < rowCountVat; i++)
                {
                    mmVat[i, 0] = "美森加班船海派";
                    mmVat[i, 1] = "MS-CLX+";
                    mmVat[i, 2] = groups[i].Key;
                    var tempData = vats.Where(x => x.操作费 == groups[i].Key).ToList();
                    int num = 0;
                    foreach (var item in areas)
                    {
                        num++;
                        mmVat[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                    }
                    mmVat[i, 2 + num + 1] = @"25-30个自然日左右提取";

                    if (groups[i].Key.Contains("8")) { mmVat[i, 2 + num + 2] = "2-3工作日"; }
                    else if (groups[i].Key.Contains("5")) { mmVat[i, 2 + num + 2] = "3-4工作日"; }
                    else { mmVat[i, 2 + num + 2] = "4-5工作日"; }

                    if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                    {
                        needBackgroundColorRow.Add(contentRowIdx + i);
                    }
                    mmVat[i, 2 + num + 3] = vats.FirstOrDefault().生效时间;
                }
                var rangeVat = sheet.get_Range("A" + contentRowIdxVat);
                rangeVat = rangeVat.get_Resize(rowCountVat, templateBehindContentEndCol.Item1);
                rangeVat.set_Value(Missing.Value, mmVat);
                //合并相同行
                MergeCell(sheet, contentRowIdxVat, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, templateBehindContentEndCol.Item2 }, rowCountVat);

                #endregion
                //背景色处理
                foreach (var item in needBackgroundColorRow)
                {
                    sheet.Range[$"{copyCol}{item}:{templateColNameVat}{item}"].Interior.Color = System.Drawing.Color.Yellow;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeUSHPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "H";

                int contentRowIdx = 3;
                var groups = quotations.GroupBy(q => q.操作费).OrderBy(x => x.Key).ToList();

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }
                #region 增加列的处理
                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                foreach (var item in areas)//无论是否新增都循环修改列头名称
                {
                    var colRange = sheet.Range[copyCol + (colRowIdx) + ":" + copyCol + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(copyCol, 1);
                    copyCol = indexCol.Item2.ToUpper();
                }
                #endregion
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                object[,] mm = new object[groups.Count, templateBehindContentEndCol.Item1];
                for (int i = 0; i < groups.Count(); i++)
                {
                    mm[i, 0] = "美国海派专线";
                    mm[i, 1] = "USST";
                    mm[i, 2] = groups[i].Key;
                    var tempData = quotations.Where(x => x.操作费 == groups[i].Key).ToList();
                    int num = 0;
                    foreach (var item in areas)
                    {
                        num++;
                        mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                    }
                    mm[i, 2 + num + 1] = @"35个自然日左右提取
【头程使用OA联盟船（EMC/COSCO/OOCL），尾程使用专车提取】";
                    if (groups[i].Key.Contains("8")) { mm[i, 2 + num + 2] = "2-3工作日"; }
                    else if (groups[i].Key.Contains("5")) { mm[i, 2 + num + 2] = "3-4工作日"; }
                    else { mm[i, 2 + num + 2] = "4-5工作日"; }
                    mm[i, 2 + num + 3] = quotations.FirstOrDefault().生效时间;
                }
                var range = sheet.get_Range("A3");
                range = range.get_Resize(groups.Count(), templateBehindContentEndCol.Item1);
                range.set_Value(Missing.Value, mm);
                //合并相同行
                MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindContentEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeEUHPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;


                var notVat = quotations.Where(i => i.线路名称 == "欧洲海派包税").ToList();
                var vats = quotations.Where(i => i.线路名称 != "欧洲海派包税").ToList();
                var groups = notVat.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();
                var groupVats = vats.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();

                List<int> needBackgroundColorRow = new List<int>();

                var weightArea = notVat.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }

                int contentRowIdx = 7;//第一个从第七行开始

                #region 增加列的处理
                //默认模板信息初始化
                string copyCol = "C"; string templateMenuEndCol = "F";

                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);//有一列默认的复制行，所以减一
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);//菜单减1就是内容最后列
                #endregion
                #region 包税处理
                var templateColName = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColName + (colRowIdx) + ":" + templateColName + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColName, 1);
                    templateColName = indexCol.Item2.ToUpper();
                }

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);
                if (notVat != null && notVat.Count > 0)
                {
                    object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCount; i++)
                    {
                        mm[i, 0] = groups[i].Key;
                        mm[i, 1] = "EUST";
                        var tempData = notVat.Where(x => x.操作费 == groups[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mm[i, 1 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mm[i, 1 + num + 1] = @"OA联盟直航船，开船后35-40个自然日提取";
                        if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                        {
                            needBackgroundColorRow.Add(contentRowIdx + i);
                        }
                        mm[i, 1 + num + 2] = notVat.FirstOrDefault().生效时间;
                    }
                    var range = sheet.get_Range("A" + contentRowIdx);
                    range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
                    range.set_Value(Missing.Value, mm);
                    //合并相同行
                    MergeCell(sheet, contentRowIdx, "B", new string[] { "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
                }
                #endregion
                #region 不包税处理
                //第二个开始行=第一个开始行+写入行数+第二个表头3行   提示：一和二均要保留一行默认的格式行
                int contentRowIdxVat = contentRowIdx + rowCount + 3;
                int colRowIdxVat = contentRowIdxVat - 1;
                var templateColNameVat = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColNameVat + (colRowIdxVat) + ":" + templateColNameVat + (colRowIdxVat)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColNameVat, 1);
                    templateColNameVat = indexCol.Item2.ToUpper();
                }
                int rowCountVat = groupVats.Count();
                InsertRows2022(sheet, contentRowIdxVat, rowCountVat, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                if (vats != null && vats.Count > 0)
                {
                    object[,] mmVat = new object[rowCountVat, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCountVat; i++)
                    {
                        mmVat[i, 0] = groups[i].Key;
                        mmVat[i, 1] = "EUST-V";
                        var tempData = vats.Where(x => x.操作费 == groups[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mmVat[i, 1 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mmVat[i, 1 + num + 1] = @"1.比利时LGG递延清关；
2.OA联盟直航船，开船后35-40个自然日提取";
                        if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                        {
                            needBackgroundColorRow.Add(contentRowIdx + i);
                        }
                        mmVat[i, 1 + num + 2] = vats.FirstOrDefault().生效时间;
                    }
                    var rangeVat = sheet.get_Range("A" + contentRowIdxVat);
                    rangeVat = rangeVat.get_Resize(rowCountVat, templateBehindContentEndCol.Item1);
                    rangeVat.set_Value(Missing.Value, mmVat);
                    //合并相同行
                    MergeCell(sheet, contentRowIdxVat, "B", new string[] { "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, rowCountVat);
                }
                #endregion
                //背景色处理
                foreach (var item in needBackgroundColorRow)
                {
                    sheet.Range[$"{copyCol}{item}:{templateColNameVat}{item}"].Interior.Color = System.Drawing.Color.Yellow;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeUSMSHKFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;


                var notVat = quotations.Where(i => i.线路名称 == "美森正班船海卡").ToList();
                var vats = quotations.Where(i => i.线路名称 != "美森正班船海卡").ToList();
                var groups = notVat.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();
                var groupVats = vats.GroupBy(x => x.操作费).OrderBy(x => x.Key).ToList();

                List<int> needBackgroundColorRow = new List<int>();

                var weightArea = notVat.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }

                int contentRowIdx = 3;//第一个从第3行开始

                #region 增加列的处理
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "G";

                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);//有一列默认的复制行，所以减一
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);//菜单减1就是内容最后列
                #endregion
                #region 美森正班船海派
                var templateColName = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColName + (colRowIdx) + ":" + templateColName + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColName, 1);
                    templateColName = indexCol.Item2.ToUpper();
                }

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);
                if (notVat != null && notVat.Count > 0)
                {
                    object[,] mm = new object[rowCount, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCount; i++)
                    {
                        mm[i, 0] = "美森正班船海卡";
                        mm[i, 1] = "MS-CLX-K";
                        mm[i, 2] = groups[i].Key;
                        var tempData = notVat.Where(x => x.操作费 == groups[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mm[i, 2 + num + 1] = @"13-16个自然日，
开船后超过19个自然日未提取，一天赔1元/KG，赔完为止（节假日，国内海关查验相关部门审核资料及码头罢工以及UPS/Fedex爆仓等不可抗拒因素不赔偿）";

                        if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                        {
                            needBackgroundColorRow.Add(contentRowIdx + i);
                        }
                        mm[i, 2 + num + 2] = notVat.FirstOrDefault().生效时间;
                    }
                    var range = sheet.get_Range("A" + contentRowIdx);
                    range = range.get_Resize(rowCount, templateBehindContentEndCol.Item1);
                    range.set_Value(Missing.Value, mm);
                    //合并相同行
                    MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
                }
                #endregion
                #region 美森加班船海派
                //第二个开始行=第一个开始行+写入行数+第二个 空一行，表头一行   提示：一和二均要保留一行默认的格式行
                int contentRowIdxVat = contentRowIdx + rowCount + 2;
                int colRowIdxVat = contentRowIdxVat - 1;
                var templateColNameVat = copyCol;
                foreach (var item in areas)//循环修改列头名称
                {
                    var colRange = sheet.Range[templateColNameVat + (colRowIdxVat) + ":" + templateColNameVat + (colRowIdxVat)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(templateColNameVat, 1);
                    templateColNameVat = indexCol.Item2.ToUpper();
                }
                int rowCountVat = groupVats.Count();
                InsertRows2022(sheet, contentRowIdxVat, rowCountVat, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);
                if (vats != null && vats.Count > 0)
                {
                    object[,] mmVat = new object[rowCountVat, templateBehindContentEndCol.Item1];
                    for (int i = 0; i < rowCountVat; i++)
                    {
                        mmVat[i, 0] = "美森加班船海卡";
                        mmVat[i, 1] = "MS-CLX+-K";
                        mmVat[i, 2] = groups[i].Key;
                        var tempData = vats.Where(x => x.操作费 == groups[i].Key).ToList();
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mmVat[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                        }
                        mmVat[i, 2 + num + 1] = @"18-23个自然日";

                        if (notVat.FirstOrDefault().生效时间 != null && DateTime.Parse(notVat.FirstOrDefault().生效时间) >= DateTime.Now)
                        {
                            needBackgroundColorRow.Add(contentRowIdx + i);
                        }
                        mmVat[i, 2 + num + 2] = vats.FirstOrDefault().生效时间;
                    }
                    var rangeVat = sheet.get_Range("A" + contentRowIdxVat);
                    rangeVat = rangeVat.get_Resize(rowCountVat, templateBehindContentEndCol.Item1);
                    rangeVat.set_Value(Missing.Value, mmVat);
                    //合并相同行
                    MergeCell(sheet, contentRowIdxVat, "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, templateBehindContentEndCol.Item2 }, rowCountVat);
                }
                #endregion
                //背景色处理
                foreach (var item in needBackgroundColorRow)
                {
                    sheet.Range[$"{copyCol}{item}:{templateColNameVat}{item}"].Interior.Color = System.Drawing.Color.Yellow;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeUSHKFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "G";

                int contentRowIdx = 3;
                var groups = quotations.GroupBy(q => q.操作费).OrderBy(x => x.Key).ToList();

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }
                #region 增加列的处理
                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                foreach (var item in areas)//无论是否新增都循环修改列头名称
                {
                    var colRange = sheet.Range[copyCol + (colRowIdx) + ":" + copyCol + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(copyCol, 1);
                    copyCol = indexCol.Item2.ToUpper();
                }
                #endregion
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                object[,] mm = new object[groups.Count, templateBehindContentEndCol.Item1];
                for (int i = 0; i < groups.Count(); i++)
                {
                    mm[i, 0] = "美国海卡专线";
                    mm[i, 1] = "USST-K";
                    mm[i, 2] = groups[i].Key;
                    var tempData = quotations.Where(x => x.操作费 == groups[i].Key).ToList();
                    int num = 0;
                    foreach (var item in areas)
                    {
                        num++;
                        mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                    }
                    mm[i, 2 + num + 1] = @"35-40个自然日
【头程使用OA联盟船（EMC/COSCO/OOCL），尾程使用专车提取】";
                    mm[i, 2 + num + 2] = quotations.FirstOrDefault().生效时间;
                }
                var range = sheet.get_Range("A3");
                range = range.get_Resize(groups.Count(), templateBehindContentEndCol.Item1);
                range.set_Value(Missing.Value, mm);
                //合并相同行
                MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindContentEndCol.Item2, -1).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeCAKPFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;
                //默认模板信息初始化
                string copyCol = "D"; string templateMenuEndCol = "G"; //string templateContentEndCol = "F";

                int contentRowIdx = 3;
                var groups = quotations.GroupBy(q => q.操作费).OrderBy(x => x.Key).ToList();

                var weightArea = quotations.Select(x => x.重量段).Distinct().ToList();
                List<string> areas = new List<string>();
                foreach (var item in weightArea)
                {
                    string weight = item.Replace(".000", "") + "KG";
                    areas.Add(weight);
                }

                #region 增加列的处理
                var insertColIndex = getIndexFromChar(copyCol, areas.Count - 1);
                if (areas.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, areas.Count - 1, copyCol, insertColIndex.Item2);
                }
                int colRowIdx = contentRowIdx - 1;//模板数据的上一行
                foreach (var item in areas)//无论是否新增都循环修改列头名称
                {
                    var colRange = sheet.Range[copyCol + (colRowIdx) + ":" + copyCol + (colRowIdx)];
                    colRange.Value = item;
                    var indexCol = getIndexFromChar(copyCol, 1);
                    copyCol = indexCol.Item2.ToUpper();
                }
                #endregion
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, areas.Count - 1);
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);

                int rowCount = groups.Count();
                InsertRows2022(sheet, contentRowIdx, rowCount, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);

                object[,] mm = new object[groups.Count, templateBehindContentEndCol.Item1];
                string showKey = string.Empty;
                for (int i = 0; i < groups.Count(); i++)
                {
                    switch (groups[i].Key)
                    {
                        case "1区":
                            showKey = @"L多伦多YYZ1-9 只接FBA
 K渥太华YOW 只接FBA
H蒙特利尔YUL 只接FBA";
                            break;
                        case "2区":
                            showKey = @"T卡尔加里YYC 只接FBA
T埃德蒙顿YEG 只接FBA";
                            break;
                        case "3区":
                            showKey = "V温哥华YVR 只接FBA";
                            break;
                        default:
                            showKey = string.Empty;
                            break;
                    }
                    mm[i, 0] = "加拿大空派专线";
                    mm[i, 1] = "CAKP";
                    mm[i, 2] = showKey;
                    var tempData = quotations.Where(x => x.操作费 == groups[i].Key).ToList();
                    int num = 0;
                    foreach (var item in areas)
                    {
                        num++;
                        mm[i, 2 + num] = tempData.Where(x => x.重量段.StartsWith(item.Split("-")[0])).FirstOrDefault().运费;
                    }
                    mm[i, 2 + num + 1] = @"5-8个自然日提取
全程8-15个自然日签收";
                    mm[i, 2 + num + 2] = quotations.FirstOrDefault().生效时间;
                }
                var range = sheet.get_Range("A3");
                range = range.get_Resize(groups.Count(), templateBehindContentEndCol.Item1);
                range.set_Value(Missing.Value, mm);
                //合并相同行
                MergeCell(sheet, contentRowIdx, "A", new string[] { "A", "B", getIndexFromChar(templateBehindContentEndCol.Item2, -1).Item2, templateBehindContentEndCol.Item2 }, groups.Count());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        public bool MakeUS5KFile2022(Application app, Workbook source, IEnumerable<View_Quotation> quotations, string templateFile)
        {
            try
            {
                if (quotations?.Any() == false) return false;

                var workbook = app.Workbooks._Open(templateFile);
                var tempsheet = (Worksheet)workbook.ActiveSheet;
                tempsheet.Copy(Type.Missing, source.ActiveSheet);
                var sheet = (Worksheet)source.ActiveSheet;
                //默认模板信息初始化
                string copyCol = "F"; string templateMenuEndCol = "J"; //string templateContentEndCol = "F";
                //定位位置
                List<int> rowIndex = new List<int> { 8, 12, 16, 20 };

                int contentRowIdx = 8;
                //根据ID顺序进行排序
                var groups = quotations.GroupBy(q => q.ExpressId).OrderBy(x => x.Key).ToList();

                var weightArea = groups.FirstOrDefault().Where(x => !x.重量段.StartsWith("0.5")).Select(x => x.重量段).Distinct().ToList();

                #region 增加列的处理
                var insertColIndex = getIndexFromChar(copyCol, weightArea.Count - 1);
                if (weightArea.Count > 1)//默认会保留一列
                {
                    InsertCols2022(sheet, contentRowIdx, weightArea.Count - 1, copyCol, insertColIndex.Item2);
                }

                #endregion
                var templateBehindMenuEndCol = getIndexFromChar(templateMenuEndCol, weightArea.Count - 1);
                var templateBehindContentEndCol = getIndexFromChar(templateBehindMenuEndCol.Item2, -1);

                int rowCount = groups.Count();

                for (int i = 0; i < groups.Count(); i++)
                {
                    var tempWeightArea = groups[i].Where(x => !x.重量段.StartsWith("0.5")).Select(x => x.重量段).Distinct().ToList();
                    List<string> areas = new List<string>();
                    foreach (var item in tempWeightArea)
                    {
                        string weight = item.Replace(".000", "") + "KG";
                        areas.Add(weight);
                    }
                    copyCol = "F";
                    foreach (var item in areas)//无论是否新增都循环修改列头名称
                    {
                        var colRange = sheet.Range[copyCol + (rowIndex[i] - 2) + ":" + copyCol + (rowIndex[i] - 1)];
                        colRange.Value = item;
                        var indexCol = getIndexFromChar(copyCol, 1);
                        copyCol = indexCol.Item2.ToUpper();
                    }

                    //判断是否有多个国家，需新增行
                    var country = groups[i].Select(x => x.Countory).Distinct().ToList();
                    if (country.Count > 1)
                    {
                        #region 增加行的处理
                        InsertRows2022(sheet, rowIndex[i], country.Count, "A", templateBehindContentEndCol.Item2, templateBehindMenuEndCol.Item2);
                        if (i + 1 < rowIndex.Count())
                        {
                            //非数组最后一位，则需要将后一位的定位增加 上一位到下一位间隔4行，再加新增行，即16+4+1=21
                            rowIndex[i + 1] = rowIndex[i] + 4 + country.Count - 1;//模版始终保留一行，所以减一
                        }
                        #endregion
                    }
                    for (int j = 0; j < country.Count(); j++)
                    {
                        object[,] mm = new object[1, templateBehindContentEndCol.Item1];
                        mm[0, 0] = groups[i].FirstOrDefault().线路名称;
                        mm[0, 1] = groups[i].FirstOrDefault().渠道代码;
                        mm[0, 2] = groups[i].Where(x => !x.重量段.StartsWith("0.5") && x.Countory == country[j]).FirstOrDefault().操作费;
                        mm[0, 3] = groups[i].Where(x => x.重量段.StartsWith("0.5") && x.Countory == country[j]).FirstOrDefault().操作费.Split("-")[0];
                        mm[0, 4] = Convert.ToDecimal(groups[i].Where(x => x.重量段.StartsWith("0.5") && x.Countory == country[j]).FirstOrDefault().运费) * 0.5m;
                        int num = 0;
                        foreach (var item in areas)
                        {
                            num++;
                            mm[0, 4 + num] = groups[i].Where(x => x.重量段.StartsWith(item.Split("-")[0]) && x.Countory == country[j]).FirstOrDefault().运费;
                        }
                        mm[0, 4 + num + 1] = country.Count == 1 ?
                            @"建议每票重量不超过99KG，货值不超USD750！"
: @"建议每票重量不超过99KG；
美国货值不超USD750
澳大利亚货值不超950AUD！";
                        mm[0, 4 + num + 2] = @"次日上网、3-5工作日签收";
                        mm[0, 4 + num + 3] = groups[i].FirstOrDefault().生效时间;

                        var range = sheet.get_Range("A" + (rowIndex[i] + j));
                        range = range.get_Resize(1, templateBehindContentEndCol.Item1);
                        range.set_Value(Missing.Value, mm);
                    }
                    if (country.Count > 1)
                    {
                        //合并相同行
                        MergeCell(sheet, rowIndex[i], "A", new string[] { "A", "B", getIndexFromChar(templateBehindMenuEndCol.Item2, -3).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -2).Item2, getIndexFromChar(templateBehindMenuEndCol.Item2, -1).Item2 }, country.Count());
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        #endregion
        #region 国际快递写入
        public bool MakeGZFedexFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile, DateTime evetime, bool isNewFedex = false)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            if (isNewFedex)
            {
                Microsoft.Office.Interop.Excel.Range RngToDelete = sheet.Range["A5:X5"];
                RngToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
            }
            int contentRowIdx = 4;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "X", "Y");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("A4", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            var timeRange = sheet.get_Range("Z1", Missing.Value);
            timeRange.Value2 = "生效时间:\r\n" + evetime.ToString_yyyyMMdd();
            if ((evetime - DateTime.Now).Days >= 0)
            {
                timeRange.Interior.Color = System.Drawing.Color.Yellow;
            }
            ////处理配套固定sheet
            //string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "FedEx_固定.xlsx");
            //BatchCopySheet(app, source, fixationFile);

            return true;
        }
        /// <summary>
        /// 处理fedex分区显示附加费
        /// </summary>
        /// <returns></returns>
        public bool MakeGZFedexElementFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0)
            {
                //没有新数据就是旧的，旧的使用默认
                string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "FedEx_固定.xlsx");
                BatchCopySheet(app, source, fixationFile);
                return false;
            }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 3;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "C", "F", "G");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("C3", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            return true;

        }
        public bool MakeCDFedexFile2022(Application app, Workbook source, System.Data.DataTable quotations, System.Data.DataTable quotations2, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 3;
            int colCount, pakRowCount, rowCount, totalRowCount;
            colCount = quotations.Columns.Count;
            totalRowCount = quotations.Rows.Count + quotations2.Rows.Count;
            pakRowCount = quotations.Rows.Count;
            rowCount = quotations2.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, totalRowCount, "A", "X", "Y");

            object[,] cellData = new object[totalRowCount, colCount + 1];//需要手动加一列

            for (int iRow = 0; iRow < pakRowCount; iRow++)
            {
                cellData[iRow, 0] = "快递袋\r\n（不计抛）";
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol + 1] = quotations.Rows[iRow][iCol];
                }
            }
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                cellData[iRow + pakRowCount, 0] = "包裹\r\n（体积除以5000）";
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow + pakRowCount, iCol + 1] = quotations2.Rows[iRow][iCol];
                }
            }

            var range = sheet.get_Range("A3", Missing.Value);
            range = range.get_Resize(totalRowCount, colCount + 1);
            range.set_Value(Missing.Value, cellData);
            //合并相同行
            MergeCell(sheet, contentRowIdx, "A", new string[] { "A" }, totalRowCount);
            //处理配套固定sheet
            string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "成都FedEx_固定.xlsx");
            BatchCopySheet(app, source, fixationFile);

            return true;
        }
        public bool MakeDHLFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile, DateTime evetime)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 4;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "J", "K");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("A4", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            var r = sheet.get_Range("L1", Missing.Value);
            r.Value2 = "生效时间:\r\n" + evetime.ToString_yyyyMMdd();
            ////处理配套固定sheet
            //string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "DHL_固定.xlsx");
            //BatchCopySheet(app, source, fixationFile);
            if ((evetime - DateTime.Now).Days >= 0)
            {
                r.Interior.Color = System.Drawing.Color.Yellow;
            }
            return true;
        }
        /// <summary>
        /// 处理fedex分区显示附加费
        /// </summary>
        /// <returns></returns>
        public bool MakeDHLElementFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 3;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "B", "E", "F");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("B3", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            return true;

        }
        public bool MakeHKFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 3;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "D", "E");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("A3", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            //合并相同行
            MergeCell(sheet, contentRowIdx, "B", new string[] { "B" }, rowCount);

            return true;
        }
        #endregion
        #region 邮政渠道
        public bool MakeETKFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;


            int contentRowIdx = 3;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "E", "F");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("A3", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            return true;
        }
        public bool MakeETKFile202210(Application app, Workbook source, System.Data.DataTable quotations, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            var sheet = (Worksheet)source.ActiveSheet;


            int contentRowIdx = 4;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "F", "G");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("A" + contentRowIdx, Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            //合并相同行
            MergeCell(sheet, contentRowIdx, "C", new string[] { "A", "C" }, rowCount);
            //处理配套固定sheet
            string fixationFile = System.IO.Path.Combine(System.AppContext.BaseDirectory, "ExcelTemplate", "2022", "E特快_固定.xlsx");
            BatchCopySheet(app, source, fixationFile);

            return true;
        }
        public bool MakeEUBFile2022(Application app, Workbook source, System.Data.DataTable quotations, string templateFile)
        {
            if (quotations == null || quotations.Rows.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 3;
            int colCount, rowCount;
            colCount = quotations.Columns.Count;
            rowCount = quotations.Rows.Count;
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "F", "G");

            object[,] cellData = new object[rowCount, colCount];
            for (int iRow = 0; iRow < rowCount; iRow++)
            {
                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    cellData[iRow, iCol] = quotations.Rows[iRow][iCol];
                }
            }
            var range = sheet.get_Range("A3", Missing.Value);
            range = range.get_Resize(rowCount, colCount);
            range.set_Value(Missing.Value, cellData);

            return true;
        }
        public bool MakeEUBFile202210(Application app, Workbook source, List<EubFile_202210> quotations, string templateFile)
        {
            if (quotations == null || quotations.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            List<string> stateNames = quotations.Select(x => x.洲名).Distinct().ToList();
            int copyStateNameRow = 4;//拷贝行 州
            int copyDataRow = 5; //拷贝行  数据
            int executeCount = 0;//州名计数
            int contentRowIdx = 4;//记录写数据开始行
            foreach (var item in stateNames)
            {
                var tempData = quotations.Where(x => x.洲名 == item).ToList();
                //州行处理 start
                if (executeCount == 0)
                {
                    var range = sheet.get_Range("A4", Missing.Value);
                    range.Value = item;
                    contentRowIdx++;
                }
                else
                {
                    InsertRows202210(sheet, copyStateNameRow, 1, contentRowIdx - 1, "A", "G", "H");
                    var range = sheet.get_Range("A" + contentRowIdx, Missing.Value);
                    range.Value = item;
                    contentRowIdx++;//插入了州行，并写入数据
                }
                //州行处理 end
                //新增数据行，并写入数据
                int rowCount = tempData.Count();
                //初始模板有保留一行数据行,但是复制粘贴的操作会覆盖该行
                InsertRows202210(sheet, copyDataRow, rowCount, contentRowIdx - 1, "A", "G", "H");
                object[,] cellData = new object[rowCount, 7];//模板7列，无新增列操作
                int cellDataCount = 0;//行数据写入
                foreach (var data in tempData)
                {
                    cellData[cellDataCount, 0] = data.路向;
                    cellData[cellDataCount, 1] = data.简码;
                    cellData[cellDataCount, 2] = data.元件;
                    cellData[cellDataCount, 3] = data.元千克;
                    cellData[cellDataCount, 4] = data.起重克;
                    cellData[cellDataCount, 5] = data.限重克;
                    cellData[cellDataCount, 6] = data.备注;
                    cellDataCount++;
                }
                var rangeData = sheet.get_Range("A" + contentRowIdx, Missing.Value);
                rangeData = rangeData.get_Resize(rowCount, 7);
                rangeData.set_Value(Missing.Value, cellData);
                contentRowIdx += rowCount;
                executeCount++;
            }
            //合并相同行
            MergeCell(sheet, 4, "B", new string[] { "A", "B" }, quotations.Count + stateNames.Count);//写死从第四行开始
            return true;
        }
        public bool MakePEUBFile202210(Application app, Workbook source, List<EubFile_202210> quotations, string templateFile)
        {
            if (quotations == null || quotations.Count == 0) { return false; }

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            // workbook.Close();
            var sheet = (Worksheet)source.ActiveSheet;

            List<string> stateNames = quotations.Select(x => x.洲名).Distinct().ToList();
            int copyStateNameRow = 4;//拷贝行 州
            int copyDataRow = 5; //拷贝行  数据
            int executeCount = 0;//州名计数
            int contentRowIdx = 4;//记录写数据开始行
            foreach (var item in stateNames)
            {
                var tempData = quotations.Where(x => x.洲名 == item).ToList();
                //州行处理 start
                if (executeCount == 0)
                {
                    var range = sheet.get_Range("A4", Missing.Value);
                    range.Value = item;
                    contentRowIdx++;
                }
                else
                {
                    InsertRows202210(sheet, copyStateNameRow, 1, contentRowIdx - 1, "A", "G", "H");
                    var range = sheet.get_Range("A" + contentRowIdx, Missing.Value);
                    range.Value = item;
                    contentRowIdx++;//插入了州行，并写入数据
                }
                //州行处理 end
                //新增数据行，并写入数据
                int rowCount = tempData.Count();
                //初始模板有保留一行数据行,但是复制粘贴的操作会覆盖该行
                InsertRows202210(sheet, copyDataRow, rowCount, contentRowIdx - 1, "A", "G", "H");
                object[,] cellData = new object[rowCount, 7];//模板7列，无新增列操作
                int cellDataCount = 0;//行数据写入
                foreach (var data in tempData)
                {
                    cellData[cellDataCount, 0] = data.路向;
                    cellData[cellDataCount, 1] = data.简码;
                    cellData[cellDataCount, 2] = data.元件;
                    cellData[cellDataCount, 3] = data.元千克;
                    cellData[cellDataCount, 4] = data.起重克;
                    cellData[cellDataCount, 5] = data.限重克;
                    cellData[cellDataCount, 6] = data.折扣;
                    cellDataCount++;
                }
                var rangeData = sheet.get_Range("A" + contentRowIdx, Missing.Value);
                rangeData = rangeData.get_Resize(rowCount, 7);
                rangeData.set_Value(Missing.Value, cellData);
                contentRowIdx += rowCount;
                executeCount++;
            }
            //合并相同行
            MergeCell(sheet, 4, "B", new string[] { "A", "B" }, quotations.Count + stateNames.Count);//写死从第四行开始
            return true;
        }
        public bool MakeNLEUBFile2023(Application app, Workbook source, List<NLEubFile_2023> quotations, string templateFile)
        {
            if (quotations?.Any() == false) return false;

            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            var sheet = (Worksheet)source.ActiveSheet;

            int contentRowIdx = 3;//第一个从第3行开始

            int rowCount = quotations.Count();
            InsertRows2022(sheet, contentRowIdx, rowCount, "A", "G", "H");

            object[,] mm = new object[rowCount, 7];
            for (int i = 0; i < rowCount; i++)
            {
                mm[i, 0] = quotations[i].rn;
                mm[i, 1] = quotations[i].StateCnName;
                mm[i, 2] = quotations[i].CnName;
                mm[i, 3] = quotations[i].Countory;
                mm[i, 4] = quotations[i].运费;
                mm[i, 5] = quotations[i].操作费;
                mm[i, 6] = quotations[i].重量段;
            }
            var range = sheet.get_Range("A" + contentRowIdx);
            range = range.get_Resize(rowCount, 7);
            range.set_Value(Missing.Value, mm);
            //合并相同行
            MergeCell(sheet, contentRowIdx, "B", new string[] { "B", }, quotations.Count());
            return true;
        }
        #endregion
        #region 共用sheet处理
        public bool MakeCommonFile2022(Application app, Workbook source, string templateFile)
        {
            bool IsFed = false; bool IsDHL = false;
            foreach (Worksheet item in source.Sheets)
            {
                if (item.Name == "FedEx") { IsFed = true; }
                if (item.Name == "广州DHL") { IsDHL = true; }
            }
            if (IsFed == false && IsDHL == false) { return true; }
            var workbook = app.Workbooks._Open(templateFile);
            var tempsheet = (Worksheet)workbook.ActiveSheet;
            tempsheet.Copy(Type.Missing, source.ActiveSheet);
            var sheet = (Worksheet)source.ActiveSheet;
            if (IsFed == false)
            {
                //多余列删除
                Microsoft.Office.Interop.Excel.Range RngToDelete = sheet.Range["D3:D3"];
                RngToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);//XlDirection.xlDown
            }
            else if (IsDHL == false)
            {
                //多余列删除
                Microsoft.Office.Interop.Excel.Range RngToDelete = sheet.Range["D4:D4"];
                RngToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);//XlDirection.xlDown
            }
            return true;
        }
        #endregion
        #region 通用方法
        Tuple<int, string> getIndexFromChar(string indexOfChar, int offset = 0)
        {
            String uid = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z";
            List<string> uidArray = uid.Split(",").ToList();
            int index = uidArray.IndexOf(indexOfChar.ToLower());

            string result = uidArray.Skip(index + offset).Take(1).FirstOrDefault();
            int resultIndex = uidArray.IndexOf(result.ToLower()) + 1;//最终获取值从1开始算，下标从0开始，故+1
            return new Tuple<int, string>(resultIndex, result);

        }

        /// <summary>
        /// 新增行，并处理多余列的行
        /// </summary>
        /// <param name="tempSheet"></param>
        /// <param name="fromRowIndex"></param>
        /// <param name="rowCount"></param>
        public void InsertRows2022(Worksheet tempSheet, int fromRowIndex, int rowCount, string startCol, string endCol, string menuCol)
        {

            //模板保留一行用于复制,可减少样式代码
            Microsoft.Office.Interop.Excel.Range RngToCopy = tempSheet.Range[startCol + (fromRowIndex) + ":" + endCol + (fromRowIndex)].EntireRow;
            //复制行插入
            Microsoft.Office.Interop.Excel.Range RngToInsert = tempSheet.Range[startCol + (fromRowIndex + 1) + ":" + endCol + (fromRowIndex + rowCount - 1)].EntireRow;
            RngToInsert.Insert(XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy(Type.Missing));
            //多余列删除
            Microsoft.Office.Interop.Excel.Range RngToDelete = tempSheet.Range[menuCol + (fromRowIndex + 1) + ":" + menuCol + (fromRowIndex + rowCount - 1)];
            RngToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);//XlDirection.xlDown
        }
        /// <summary>
        /// 特殊新增行
        /// 拷贝指定行，在尾部新增
        /// 例如：数据最后行为5 startRow=5 
        /// </summary>
        /// <param name="tempSheet"></param>
        /// <param name="fromRowIndex"></param>
        /// <param name="rowCount"></param>
        /// <param name="startCol"></param>
        /// <param name="endCol"></param>
        /// <param name="menuCol"></param>
        public void InsertRows202210(Worksheet tempSheet, int copyRowCount, int rowCount, int startRow, string startCol, string endCol, string menuCol)
        {
            //模板保留一行用于复制,可减少样式代码
            Microsoft.Office.Interop.Excel.Range RngToCopy = tempSheet.Range[startCol + (copyRowCount) + ":" + endCol + (copyRowCount)].EntireRow;
            //复制行插入
            Microsoft.Office.Interop.Excel.Range RngToInsert = tempSheet.Range[startCol + (startRow + 1) + ":" + endCol + (startRow + rowCount)].EntireRow;
            RngToInsert.Insert(XlInsertShiftDirection.xlShiftDown, RngToCopy.Copy(Type.Missing));
            //多余列删除
            Microsoft.Office.Interop.Excel.Range RngToDelete = tempSheet.Range[menuCol + (startRow + 1) + ":" + menuCol + (startRow + rowCount)];
            RngToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);//XlDirection.xlDown

        }
        public void InsertCols2022(Worksheet tempSheet, int fromRowIndex, int rowCount, string startCol, string endCol)
        {
            //模板保留一行用于复制,可减少样式代码
            int startRowIndex = fromRowIndex - 1;
            var insertColIndex = getIndexFromChar(startCol, 0);
            Microsoft.Office.Interop.Excel.Range RngToCopy = tempSheet.Range[startCol + (startRowIndex) + ":" + startCol + (startRowIndex)].EntireColumn;
            for (int i = 0; i < rowCount; i++)
            {
                Microsoft.Office.Interop.Excel.Range RngToInsert = (Microsoft.Office.Interop.Excel.Range)tempSheet.Columns[insertColIndex.Item1, Type.Missing];
                RngToInsert.Insert(XlInsertShiftDirection.xlShiftToRight, RngToCopy.Copy(Type.Missing));
            }


        }
        /// <summary>
        /// 合并相同数据单元格
        /// 写入excel时，应保持数据连续性
        /// 仅纵向合并，不处理横向合并
        /// </summary>
        /// <param name="tempSheet"></param>
        /// <param name="fromRowIndex">起始行 1开始</param>
        /// <param name="BasisColName">依据列，该列值相同合并</param>
        /// <param name="ColNames">需要合并的所有列</param>
        /// <param name="rowCount">写入的数据行数</param>
        public void MergeCell(Worksheet tempSheet, int fromRowIndex, string BasisColName, string[] ColNames, int rowCount)
        {
            string qy1 = tempSheet.get_Range(BasisColName + fromRowIndex.ToString(), BasisColName + fromRowIndex.ToString()).Text.ToString();//获得起始行合并列单元格的填充内容
            Microsoft.Office.Interop.Excel.Range rg1;
            string strtemp = "";
            bool endCycle = false;
            //从起始行到终止行做循环
            for (int i = fromRowIndex; i <= fromRowIndex + rowCount - 1 && !endCycle;)
            {
                for (int j = i + 1; j <= fromRowIndex + rowCount; j++)//多取一行，避免死循环
                {
                    rg1 = tempSheet.get_Range(BasisColName + j.ToString(), BasisColName + j.ToString());//获得下一行的填充内容
                    strtemp = rg1.Text.ToString().Trim();
                    if (strtemp.Trim() == qy1.Trim())//内容等于初始内容
                    {
                        foreach (var item in ColNames)//需要合并的所有列 合并
                        {
                            //取当前值
                            string curryColText = tempSheet.get_Range(item + j.ToString(), item + j.ToString()).Text.ToString();
                            rg1 = tempSheet.get_Range(item + i.ToString(), item + j.ToString());//选取上条合并位置和当前行的合并区域
                            rg1.ClearContents();//清空要合并的区域
                            rg1.MergeCells = true;
                            rg1.set_Value(Missing.Value, curryColText);
                        }
                        if (j == fromRowIndex + rowCount - 1)
                        {
                            endCycle = true;
                        }
                    }
                    else
                    {
                        i = j;//i获取新值
                        qy1 = tempSheet.get_Range(BasisColName + j.ToString(), BasisColName + j.ToString()).Text.ToString();
                        break;
                    }
                }
            }
        }
        public void BatchCopySheet(Application app, Workbook wb, string templateFile)
        {
            _Workbook templateWorkBook = app.Workbooks._Open(templateFile);
            foreach (Worksheet item in templateWorkBook.Worksheets)
            {
                item.Copy(Type.Missing, (Worksheet)wb.ActiveSheet);
            }
            templateWorkBook.Close(false, Type.Missing, Type.Missing);
        }
        #endregion

        #endregion
    }
}
