How to create Table inside Excel Sheet?

user_1405423 0
Viewed 529

Use DocumentFormat.Openxml 2.8.1.

I try to create Excel Sheet and create Table inside it. But when i do it - and try to open excel file - excel says that can not open and try to restore this file.

So, i create excel file:

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

Then , create sheet:

        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;
    }

So, how to create table in Excel sheet and insert data in table? Thank you.

openxml-sdk excel c#
2 Answers

TableStyle class is what you`re looking for.

You actually have to create TableStyle and add it to your spreadsheet.

user_61519390

In CreateTable1(), TableProperties, TableLayout, TableRow, and TableCell are WordProcessing elements.

Instead, you will want to use code similar to this:

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);
}

Slightly modify the rest of your code to look like the following (most of which I borrowed from here).

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();
user_59037770
Related