c# - 在c#中使用openxml sdk创建xlsx文件

Question

我在使用 OpenXml SDK 创建 excel 文件时遇到了一点问题，我按照 here 上的说明进行操作.

class Program
{
    static void Main(string[] args)
    {

        List<Person> persons = new List<Person>()
            {
                new Person() {FirstName="Brecht", LastName="Baekelandt", Age=29},
                new Person() {FirstName="Pieter", LastName="Baekelandt", Age=28},
                new Person() {FirstName="Leonie", LastName="Baekelandt", Age=21}
           };

        string directory = Path.Combine(@"C:\Temp", "TestFiles");

        string fileName = Path.Combine(directory, string.Format("TestFile{0:yyyy-MM-dd HH.mm.ss}.xlsx", DateTime.Now));

        // Create a spreadsheet document by supplying the filepath.
        // By default, AutoSave = true, Editable = true, and Type = xlsx.
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
            Create(fileName, SpreadsheetDocumentType.Workbook))
        {

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                                                AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                                             GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = "mySheet"
                };


            sheets.Append(sheet);
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();


            UInt32 rowIndex = 0;

            foreach (var person in persons)
            {
                var row = new Row() { RowIndex = rowIndex };

                var firstNameCell = new Cell() { CellReference = "A" + (rowIndex + 1)  };
                firstNameCell.CellValue = new CellValue(person.FirstName);
                firstNameCell.DataType = CellValues.String;

                row.AppendChild(firstNameCell);

                Cell lastNameCell = new Cell() { CellReference = "B" + (rowIndex + 1) };
                lastNameCell.CellValue = new CellValue(person.LastName);
                lastNameCell.DataType = new EnumValue<CellValues>(CellValues.String);

                row.AppendChild(lastNameCell);

                Cell ageCell = new Cell() { CellReference = "C" + (rowIndex + 1) };
                ageCell.CellValue = new CellValue(person.Age.ToString());
                ageCell.DataType = new EnumValue<CellValues>(CellValues.Number);

                row.AppendChild(ageCell);

                sheetData.AppendChild(row);

                rowIndex++;
            }

            workbookpart.Workbook.Save();
        }
    }
}

这将创建以下 xml 文件:

<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheetData>
    <x:row r="0">
        <x:c r="A1" t="str">
            <x:v>Brecht</x:v>
        </x:c>
        <x:c r="B1" t="str">
            <x:v>Baekelandt</x:v>
        </x:c>
        <x:c r="C1" t="n">
            <x:v>29</x:v>
        </x:c>
    </x:row>
    <x:row r="1">
        <x:c r="A2" t="str">
            <x:v>Pieter</x:v>
        </x:c>
        <x:c r="B2" t="str">
            <x:v>Baekelandt</x:v>
        </x:c>
        <x:c r="C2" t="n">
            <x:v>28</x:v>
        </x:c>
    </x:row>
    <x:row r="2">
        <x:c r="A3" t="str">
            <x:v>Leonie</x:v>
        </x:c>
        <x:c r="B3" t="str">
            <x:v>Baekelandt</x:v>
        </x:c>
        <x:c r="C3" t="n">
            <x:v>21</x:v>
        </x:c>
    </x:row>
</x:sheetData>
</x:worksheet>

这应该没问题，但是当我打开 xlsx 文件时出现以下错误:

Excel 在 [文件] 中发现不可读的内容。是否要恢复此工作簿的内容？

当我单击"is"时，只有第一个人被添加到工作表中。

我在这里做错了什么？

Answer 1

您的问题与索引有关。行索引从 index 1

开始

流动应该起作用。

      UInt32 rowIndex = 1;

      foreach (var person in persons)
      {
          var row = new Row() { RowIndex = rowIndex };

          var firstNameCell = new Cell() { CellReference = "A" +rowIndex };
          firstNameCell.CellValue = new CellValue(person.FirstName);
          firstNameCell.DataType = CellValues.String;

          row.Append(firstNameCell);

          Cell lastNameCell = new Cell() { CellReference = "B"+rowIndex };
          lastNameCell.CellValue = new CellValue(person.LastName);
          lastNameCell.DataType = new EnumValue<CellValues>(CellValues.String);

          row.Append(lastNameCell);


          Cell ageCell = new Cell() { CellReference = "C"+rowIndex };

          ageCell.CellValue = new CellValue(person.Age.ToString());
          ageCell.DataType = new EnumValue<CellValues>(CellValues.Number);

          row.Append(ageCell);

          sheetData.Append(row);

            rowIndex++;
        }