c# - 在 openXML 中添加单元格和行

Question

我已经预先定义了 excel 格式，我需要将数据传递给 excel。我能够获得特定的工作表。但不知道如何将数据传递给单元格。

var excelDocument = new ExcelDocument();
var fileName = Guid.NewGuid();
string filePath = HttpContext.Current.Server.MapPath("~/Uploads/TemplateFiles/test.xlsx");

using (SpreadsheetDocument document =
       SpreadsheetDocument.Open(filePath, false))
{
       WorkbookPart workbookPart = document.WorkbookPart;
       Workbook workbook = document.WorkbookPart.Workbook;
       string sheetName = workbookPart.Workbook.Descendants<Sheet>().ElementAt(1).Name;
       IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Census Template for Import");
       if (sheets.Count() == 0)
       {
              // The specified worksheet does not exist.
              return null;
       }
       WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
       SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
       var excelRows = sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Row>().ToList();
       int rowindex = 10;
       foreach (var item in census)
       {
              //how to write the data in cell
              rowindex++;
       }

       worksheetPart.Worksheet.Save();
       workbookPart.Workbook.Save();
       document.Close();
       //worksheetPart.Worksheet.Save();
 }
 return filePath;

Answer 1

这是获取单元格或添加新单元格的方法，如果单元格不存在，当您知道行索引和列索引时。

注意:

• rowIndex 和 columnIndex 应该以 1 开头
• Row 的属性 RowIndex 应在创建行期间初始化
• Cell 的属性 CellReference 应该在创建单元格时初始化

如果RowIndex 或CellReference 为null，则抛出NullReferenceException。

private Cell InsertCell(uint rowIndex, uint columnIndex, Worksheet worksheet)
{
    Row row = null;
    var sheetData = worksheet.GetFirstChild<SheetData>();

    // Check if the worksheet contains a row with the specified row index.
    row = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
    if (row == null)
    {
        row = new Row() { RowIndex = rowIndex };
        sheetData.Append(row);
    }

    // Convert column index to column name for cell reference.
    var columnName = GetExcelColumnName(columnIndex);
    var cellReference = columnName + rowIndex;      // e.g. A1

    // Check if the row contains a cell with the specified column name.
    var cell = row.Elements<Cell>()
               .FirstOrDefault(c => c.CellReference.Value == cellReference);
    if (cell == null)
    {
        cell = new Cell() { CellReference = cellReference };
        if (row.ChildElements.Count < columnIndex)
            row.AppendChild(cell);
        else
            row.InsertAt(cell, (int)columnIndex);
    }

    return cell;
}

Here您将找到 GetExcelColumnName() 方法的代码。