c# - 使用 Open Xml 写入 Excel 在某些列之后出现问题

Question

我正在使用带有 Open XML 格式 SDK 2.0 的 C#，尝试编写一个 Excel 报告文件。我的问题是当我越过 Z 列(达到 AA)时，文件已损坏且用户无法手动打开。有什么想法吗？

  static void Main(string[] args)
            {
                PortfolioReport report = new PortfolioReport("Keywords");
                report.CreateReport();
            }
 public PortfolioReport(string client)
        {
            string newFileName = path + client + ".xlsx";
            if (File.Exists(newFileName))
            {
                File.Delete(newFileName);
            }
            FileInfo newFile = new FileInfo(newFileName);
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Keywords");
                package.Save();
            }
            document = SpreadsheetDocument.Open(newFileName, true);
            wbPart = document.WorkbookPart;
        }

               static readonly string[] Columns = new[] { "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", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH" };
                    public static string IndexToColumn(int index)
                    {
                        if (index < 0)
                            throw new IndexOutOfRangeException("index must be a positive number");

                        return Columns[index];
                    }
                    public static object GetPropValue(object src, string propName)
                    {
                        return src.GetType().GetProperty(propName).GetValue(src, null);
                    }
                    // Create a new Portfolio report
                    public void CreateReport()
                    {
                        string wsName = "Report Summary";
                        wbPart.Workbook.Descendants<Sheet>().FirstOrDefault().Name = wsName;

                        var currentUser =  UsersInfo.Keywords;

                        //set the domainAge
                        KeywordsModule.SetYearsAndMonths(currentUser.Keywords);
                        //set the url site relevency in words
                        KeywordsModule.SetAverageUrlSiteRelevencyLiteral(currentUser.Keywords);
                        //set a model for the excel which will convert the keyword to the custom model according to gui names
                        List<KeywordModelForExcelExport> keywordsForExports = KeywordsModule.PrepreKeywordSforExport(currentUser.Keywords);

                        //we set the column headings
                        var properties = typeof(KeywordModelForExcelExport).GetProperties();
                        for (int i = 0; i < properties.Length; i++)
                        {
                            var cell = IndexToColumn(i) + 1;
                            UpdateValue(wsName, cell, properties[i].Name, 0, true);

                        }

                        //now we set the keyword values
                        int row = 2;
                        foreach (var keywordForExport in keywordsForExports)
                        {
                            for (int i = 0; i < properties.Length; i++)
                            {

                                var val = GetPropValue(keywordForExport, properties[i].Name);
                                var cell = IndexToColumn(i) + row;
                                if (val != null)
                                    UpdateValue(wsName, cell, val.ToString(), 0, true);

                            }
                            row++;
                        }

                        // All done! Close and save the document.
                        document.Close();
                    }

                    // Given a Worksheet and an address (like "AZ254"), either return a cell reference, or 
                    // create the cell reference and return it.
                    private Cell InsertCellInWorksheet(Worksheet ws, string addressName)
                    {
                        SheetData sheetData = ws.GetFirstChild<SheetData>();
                        Cell cell = null;

                        UInt32 rowNumber = GetRowIndex(addressName);
                        Row row = GetRow(sheetData, rowNumber);

                        // If the cell you need already exists, return it.
                        // If there is not a cell with the specified column name, insert one.  
                        Cell refCell = row.Elements<Cell>().
                            Where(c => c.CellReference.Value == addressName).FirstOrDefault();
                        if (refCell != null)
                        {
                            cell = refCell;
                        }
                        else
                        {
                            cell = CreateCell(row, addressName);
                        }
                        return cell;
                    }

                    private Cell CreateCell(Row row, String address)
                    {
                        Cell cellResult;
                        Cell refCell = null;

                        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                        foreach (Cell cell in row.Elements<Cell>())
                        {
                            if (string.Compare(cell.CellReference.Value, address, true) > 0)
                            {
                                refCell = cell;
                                break;
                            }
                        }

                        cellResult = new Cell();
                        cellResult.CellReference = address;

                        row.InsertBefore(cellResult, refCell);
                        return cellResult;
                    }

                    private Row GetRow(SheetData wsData, UInt32 rowIndex)
                    {
                        var row = wsData.Elements<Row>().
                        Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
                        if (row == null)
                        {
                            row = new Row();
                            row.RowIndex = rowIndex;
                            wsData.Append(row);
                        }
                        return row;
                    }

                    private UInt32 GetRowIndex(string address)
                    {
                        string rowPart;
                        UInt32 l;
                        UInt32 result = 0;

                        for (int i = 0; i < address.Length; i++)
                        {
                            if (UInt32.TryParse(address.Substring(i, 1), out l))
                            {
                                rowPart = address.Substring(i, address.Length - i);
                                if (UInt32.TryParse(rowPart, out l))
                                {
                                    result = l;
                                    break;
                                }
                            }
                        }
                        return result;
                    }

                    public bool UpdateValue(string sheetName, string addressName, string value, UInt32Value styleIndex, bool isString)
                    {
                        // Assume failure.
                        bool updated = false;

                        Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();

                        if (sheet != null)
                        {
                            Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
                            Cell cell = InsertCellInWorksheet(ws, addressName);

                            if (isString)
                            {
                                // Either retrieve the index of an existing string,
                                // or insert the string into the shared string table
                                // and get the index of the new item.
                                int stringIndex = InsertSharedStringItem(wbPart, value);

                                cell.CellValue = new CellValue(stringIndex.ToString());
                                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                            }
                            else
                            {
                                cell.CellValue = new CellValue(value);
                                cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            }

                            if (styleIndex > 0)
                                cell.StyleIndex = styleIndex;

                            // Save the worksheet.
                            ws.Save();
                            updated = true;
                        }

                        return updated;
                    }

                    // Given the main workbook part, and a text value, insert the text into the shared
                    // string table. Create the table if necessary. If the value already exists, return
                    // its index. If it doesn't exist, insert it and return its new index.
                    private int InsertSharedStringItem(WorkbookPart wbPart, string value)
                    {
                        int index = 0;
                        bool found = false;
                        var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

                        // If the shared string table is missing, something's wrong.
                        // Just return the index that you found in the cell.
                        // Otherwise, look up the correct text in the table.
                        if (stringTablePart == null)
                        {
                            // Create it.
                            stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
                            stringTablePart.SharedStringTable = new SharedStringTable();
                        }

                        var stringTable = stringTablePart.SharedStringTable;
                        //            if (stringTable == null)
                        //            {
                        //                stringTable = new SharedStringTable();
                        //            }

                        // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                        foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
                        {
                            if (item.InnerText == value)
                            {
                                found = true;
                                break;
                            }
                            index += 1;
                        }

                        if (!found)
                        {
                            stringTable.AppendChild(new SharedStringItem(new Text(value)));
                            stringTable.Save();
                        }

                        return index;
                    }

Answer 1

对于 Z 之后的单元格引用，您在 CreateCell 中的排序被破坏了。您当前正在使用 string.Compare，但这将进行 alpha 比较，这意味着 AA1 是 before Z1 而不是之后

有多种方法可以解决此问题 - 一种方法是将 cellReference 转换为列索引，然后比较它们而不是直接比较单元格引用。例如:

private static int? GetColumnIndex(string cellRef)
{
    if (string.IsNullOrEmpty(cellRef))
        return null;

    cellRef = cellRef.ToUpper();

    int columnIndex = -1;
    int mulitplier = 1;

    foreach (char c in cellRef.ToCharArray().Reverse())
    {
        if (char.IsLetter(c))
        {
            columnIndex += mulitplier * ((int)c - 64);
            mulitplier = mulitplier * 26;
        }
    }

    return columnIndex;
}

然后代替

if (string.Compare(cell.CellReference.Value, address, true) > 0)
{
    refCell = cell;
    break;
}

你可以做到

if (GetColumnIndex(cell.CellReference.Value) > GetColumnIndex(address))
{
    refCell = cell;
    break;
}