c# - 按子字符串将字符串列表排序到Excel工作表中

Question

我有一个字符串列表。我已按子字符串位置 0、18 对它们进行了排序

在这些位置具有匹配子字符串的任何字符串都应填写到同一个 Excel 工作表中

我有一个现有模板，必须从中复制每张工作表。然后在按这些位置排序后，我检查最后一个子字符串是否与当前匹配，如果匹配，我将其填充到工作表中。如果不在同一工作簿中创建新工作表，则字符串将添加到新工作表中

var data = File.ReadAllLines(strFileName);
List<string> agencyCode = new List<string>();
List<string> invoiceCode = new List<string>();
List<string> uniqueCode = new List<string>();
string checkCode = "";
string strFile = @"//template.xlsx";
var xlFile = new FileInfo(strFile);
string strDir = Path.GetDirectoryName(strFileName) + Path.DirectorySeparatorChar;
// capture all agency codes; one sheet per agency code

var newdata = data.OrderBy(s => s.Substring(0, 18));
#region collectCodes
foreach (var i in newdata)
{
    var parts = i.Split('\t');
    if (!agencyCode.Contains(parts[0]))
    {
        agencyCode.Add(parts[0]);
    }

    if (!invoiceCode.Contains(parts[2]))
    {
        invoiceCode.Add(parts[2]);
    }

    if (!uniqueCode.Contains(parts[0] + "\t" + parts[1] + "\t" + parts[2]))
    {
        uniqueCode.Add((parts[0] + "\t" + parts[1] + "\t" + parts[2]));
    }
}
#endregion

foreach (var code in agencyCode)
{
    using (ExcelPackage agencySheet = new ExcelPackage(xlFile))
    {// create two tabs (charge off and pre charge off)for each invoice number associated with agency codes
        int row = 23;
        int rowTwo = 23;
        int l = 1;
        int n = 1;
        int a = 0;

        var wsCO = agencySheet.Workbook.Worksheets["CO"];
        var wsPreCO = agencySheet.Workbook.Worksheets["PreCO"];

        agencySheet.Workbook.Worksheets.Delete("PreCO");
        wsPreChargeOff = agencySheet.Workbook.Worksheets.Copy("CO", "PreCO");

        var wsCleanCopy = agencySheet.Workbook.Worksheets.Copy("CO", "COCopy");

        using (StreamWriter sw = new StreamWriter(strDir + "test.txt"))
        {
            foreach (var i in newdata)
            {
                sw.WriteLine(i);
            }
        }

        foreach (var k in newdata)
        {
            var pieces = k.Split('\t');
            //var parts = codeTwo.Split('\t');

            if (pieces[1] == "CO")
            {
                if (code == pieces[0])
                {
                    if (checkCode == k.Substring(0, 15))
                    {
                        if (a == 0)
                        {
                                                   
                        }

                        else
                        {
                            wsCO = agencySheet.Workbook.Worksheets["CO" + "(" + (l) + ")"];
                        }
                    }
                    else
                    {
                        if (l >= 2)
                        {
                            l++;
                            wsChargeOff = agencySheet.Workbook.Worksheets.Copy("COCopy", "CO" + "(" + l + ")");
                        }

                        else
                        {
                            wsCO = agencySheet.Workbook.Worksheets["ChargeOff"];
                            l++;
                        }
                    }

                    if (checkCode != k.Substring(0, 15) && checkCode != "")
                    {
                        a++;
                    }
                    checkCode = k.Substring(0, 15);
                    wsCO.Cells["J6"].Value = pieces[2];
                    wsPreCOCells["J6"].Value = pieces[2];

                    wsCO.Cells[row, 1].Value = pieces[4];
                    wsCO.Cells[row, 2].Value = pieces[5];
                    wsCO.Cells[row, 3].Value = pieces[6];
                    // wsCO.Cells[row, 4].Value = pieces[6];
                    wsCO.Cells[row, 5].Value = Convert.ToDecimal(pieces[8]);
                    wsCO.Cells[row, 6].Value = Convert.ToDecimal(pieces[9]);
                    wsCO.Cells[row, 7].Value = Convert.ToDecimal(pieces[10]);
                    wsCO.Cells[row, 8].Value = Convert.ToDecimal(pieces[11]);
                    wsChargeOff.Cells[row, 9].Value = Convert.ToDecimal(pieces[12]);

                    // wsCO.Cells[row, 5].Style.Numberformat.Format = "#,##0.00";
                    //wsCO.Cells[row, 6].Style.Numberformat.Format = "#,##0.00";
                    // wsCO.Cells[row, 8].Style.Numberformat.Format = "#,##0.00";
                    //wsCO.Cells[row, 9].Style.Numberformat.Format = "#,##0.00";

                    wsCO.Cells["J7"].Value = pieces[3];
                    for (int m = 1; m < 10; m++)
                    {
                        wsCO.Cells[row, m].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }
                    row++;
                }
            }

标签： c#excel