Cell

• 如何将数据写入单元格
• 如何将数字转为字母

如何将数据写入单元格

    SheetData sheetData = worksheet.GetFirstChild<SheetData>() 
            ?? new SheetData();
    if (worksheet.GetFirstChild<SheetData>() == null)
    {
            worksheet.Append(sheetData);
    }

    Row targetRow = sheetData.Elements<Row>()
        .FirstOrDefault(r => r.RowIndex == rowIndex);

    if (targetRow == null)
    {
        targetRow = new Row() { RowIndex = (uint)rowIndex };

        Row firstLargerRow = sheetData.Elements<Row>()
            .FirstOrDefault(r => r.RowIndex > rowIndex);

        if (firstLargerRow != null)
        {
            sheetData.InsertBefore(targetRow, firstLargerRow);
        }
        else
        {
            sheetData.Append(targetRow); 
        }
    }

    string columnName = GetColumnName(columnIndex);
    Cell cell = targetRow.Elements<Cell>()
        .FirstOrDefault(c => c.CellReference.Value == $"{columnName}{rowIndex}");

    if (cell == null)
    {
        cell = new Cell() { CellReference = $"{columnName}{rowIndex}" };
        targetRow.Append(cell);
    }

    cell.CellValue = new CellValue(value);
    cell.DataType = new EnumValue<CellValues>(CellValues.String);

IMPORTANT

需要注意 不论是行还是列要完全按照正序排序,需要对处理后的结构进行排序

如何将数字转为字母

    int dividend = columnIndex;
    string columnName = "";
    while (dividend > 0)
    {
        int modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        dividend = (dividend - modulo) / 26;
    }
    return columnName;