时间戳

首页 > 解决方案 > 如何在 Excel 工作表中创建表格？

c# - 如何在 Excel 工作表中创建表格？

问题描述

使用 DocumentFormat.Openxml 2.8.1。

我尝试创建 Excel 工作表并在其中创建表格。但是当我这样做时 - 并尝试打开 excel 文件 - excel 说无法打开并尝试恢复此文件。

所以，我创建了excel文件：

 var  fStream = new FileStream(tempPathName, FileMode.Open, FileAccess.ReadWrite, FileShare.None, 1048, FileOptions.DeleteOnClose);
    SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(inputStream,
   SpreadsheetDocumentType.Workbook);

然后，创建工作表：

        var tempPathName = Path.GetTempFileName();
        WorkbookPart workbookPart = ssDoc.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();
        Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        WorksheetPart worksheetPart4 = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart4.Worksheet = new Worksheet();

        Worksheet workSheet4 = worksheetPart4.Worksheet;
        
        var table = CreateTable1();
        TableParts tableParts = new TableParts() { Count = (UInt32)1 };
        TablePart tablePart = new TablePart() { Id = "rId" + 1 };

        tableParts.Append(table);
        
        tableParts.Append(tablePart);
        workSheet4.AppendChild(tableParts);

        Sheet sheet4 = new Sheet()
        {
            Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart4),
            SheetId = 4,
            Name = "test"
        };
        sheets.Append(sheet4);
         ssDoc.Close();



     private Table CreateTable1()
    {
        // First, we create the table, its properties and we append it.
        Table table = new Table();
        TableProperties props = new TableProperties();
        table.AppendChild<TableProperties>(props);

        // Now we create a new layout and make it "fixed".
        TableLayout tl = new TableLayout() { Type = TableLayoutValues.Fixed };
        props.TableLayout = tl;

        // Then we just create a new row and a few cells and we give them a width
        var tr = new TableRow();
        var tc1 = new TableCell();
        
            
        var tc2 = new TableCell();
        tc1.Append(new TableCellProperties(new TableCellWidth() { Type = TableWidthUnitValues.Dxa, Width = "2000" }));
        tc2.Append(new TableCellProperties(new TableCellWidth() { Type = TableWidthUnitValues.Dxa, Width = "2000" }));
        table.Append(tr);

        return table;
    }

那么，如何在 Excel 工作表中创建表格并在表格中插入数据呢？谢谢你。

标签： c#excelopenxml-sdk

解决方案

在CreateTable1()中，TableProperties、TableLayout、TableRow和TableCell是WordProcessing元素。

相反，您将需要使用与此类似的代码：

private void CreateTable2(SheetData sheetData)
{
    var row1 = new Row(){ RowIndex = 1 };
    sheetData.Append(row1);

    var cell1 = new Cell();
    cell1.DataType = CellValues.InlineString;
    cell1.InlineString = new InlineString() { Text = new Text("Hello") };
    row1.InsertAt(cell1, 0);

    var cell2 = new Cell();
    cell2.DataType = CellValues.InlineString;
    cell2.InlineString = new InlineString() { Text = new Text("World") };
    row1.InsertAt(cell2, 1);
}

稍微修改其余代码，如下所示（其中大部分是我从这里借来的）。

var fStream = new FileStream(tempPathName, FileMode.Create);
SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(fStream, SpreadsheetDocumentType.Workbook);

// Add a WorkbookPart to the document.
WorkbookPart workbookPart = ssDoc.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 = ssDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

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

// Get the sheetData cell table.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

CreateTable2(sheetData);

workbookPart.Workbook.Save();

ssDoc.Close();
fStream.Close();

推荐阅读