1

I'm new to OpenXml and I'm trying to generate an excel file with custom column width. I've searched for solution online and here but every solution produce the same error. I've tried this:

private byte[] GenerateExcelFromTreeData(TreeData[] treeData)
    {
        using (var ms = new MemoryStream())
        using (var spreadsheetDocument = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            var sheetData = new SheetData();
            worksheetPart.Worksheet = new Worksheet(sheetData);

            Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());

            Sheet sheet = new Sheet()
            {
                Id = workbookpart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "sheet"
            };

            Columns columns = new Columns();

            columns.Append(new Column() { Min = 1, Max = 1, Width = 120, CustomWidth = true });
            columns.Append(new Column() { Min = 2, Max = 50, Width = 50, CustomWidth = true });

            worksheetPart.Worksheet.Append(columns);

            Row headerRow = new Row();
            headerRow.Append(new Cell()
            {
                CellValue = new CellValue("Node"),
                DataType = CellValues.String
            });
            headerRow.Append(new Cell()
            {
                CellValue = new CellValue("Value"),
                DataType = CellValues.String
            });
            headerRow.Append(new Cell()
            {
                CellValue = new CellValue("Delta"),
                DataType = CellValues.String
            });
            headerRow.Append(new Cell()
            {
                CellValue = new CellValue("∆ Avegage Premium " + treeData[0].delta_AP + " %"),
                DataType = CellValues.String
            });
            headerRow.Append(new Cell()
            {
                CellValue = new CellValue("∆ New Policies " + treeData[0].delta_NP + " %"),
                DataType = CellValues.String
            });
            headerRow.Append(new Cell()
            {
                CellValue = new CellValue("∆ Average Sum Insurance " + treeData[0].delta_AS + " %"),
                DataType = CellValues.String
            });
            sheetData.Append(headerRow);

            //foreach (var data in treeData)
            for(int i = 1; i < treeData.Count(); i++)
            {
                var data = treeData[i]; 
                Row row = new Row();
                row.Append(new Cell()
                {
                    CellValue = new CellValue(data.name),
                    DataType = CellValues.String
                });
                row.Append(new Cell()
                {
                    CellValue = new CellValue(data.description.ToString()),
                    DataType = CellValues.Number
                });
                row.Append(new Cell()
                {
                    CellValue = new CellValue(data.delta.ToString()),
                    DataType = CellValues.Number
                });
                row.Append(new Cell()
                {
                    CellValue = new CellValue(data.delta_AP.ToString()),
                    DataType = CellValues.Number
                });
                row.Append(new Cell()
                {
                    CellValue = new CellValue(data.delta_NP.ToString()),
                    DataType = CellValues.Number
                });
                row.Append(new Cell()
                {
                    CellValue = new CellValue(data.delta_AS.ToString()),
                    DataType = CellValues.Number
                });
                sheetData.Append(row);
            }

            sheets.Append(sheet);
            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();

            ms.Position = 0;
            return ms.ToArray();
        }
    }

but I get a corrupted file, but if you remove:

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 1, Width = 120, CustomWidth = true }); 
columns.Append(new Column() { Min = 2, Max = 50, Width = 50, CustomWidth = true });

worksheetPart.Worksheet.Append(columns);

it works fine, but with default width. I also would like to make the header row bolder. Any Tips?

abecce
  • 11
  • 1
  • Download the Open XML SDK Productivity Tool. Create the file in the way that it works. Open it in Excel and make the change you want to have (the code you removed) and save it to a different name. Open the original file in the Tool, use its "Compare" feature to open the modified version. Look at the code that's generated to create the second from the first - that should give you the basic syntax you need. – Cindy Meister Apr 15 '20 at 07:24

0 Answers0