Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Setting Excel spreadsheet column properties issue

Tags:

c#

excel

openxml

I'm trying to set up default column width in Excel spreadsheet using OpenXML framework and as a result I've got broken file. Here is code

private void initSpreadsheetDocument()
{
    // Add a WorkbookPart to the spreadsheet document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    var sheetData = new SheetData();
    var properties = new SheetFormatProperties { DefaultColumnWidth = 25D };

    Worksheet worksheet = new Worksheet();
    worksheet.AppendChild(sheetData);
    
    // here is line of code that corrupt file
    // without it - file is being generated properly
    worksheet.AppendChild(properties);

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

    // Init sheets
    sheetsStorage = new Sheets();

    defaultSheet = new Sheet();
    defaultSheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart);
    defaultSheet.SheetId = 1;
    defaultSheet.Name = DEFAULT_SHEET_NAME;

    sheetsStorage.AppendChild(defaultSheet);

    spreadsheetDocument.WorkbookPart.Workbook.AppendChild(sheetsStorage);
    spreadsheetDocument.WorkbookPart.Workbook.Save();
}

I was trying to use "Append" vs "AppendChild" but result was the same

Does anybody has a point of view how to sort it out?

like image 898
Andriy Zakharko Avatar asked Oct 29 '12 10:10

Andriy Zakharko


People also ask

Why is my Excel column not sorting properly?

The most common reason for data not sorting correctly is due to the leading space ahead of the text. Many people using encounter this problem. The text with leading space is sorted at the top in ascending and at the bottom in descending order sort. Try correcting this, and it will work.

Why does Excel keep changing my formatting?

This is because, as a default, Excel worksheets are globally formatted using the General format, which automatically adopts the number format you use to initially enter numbers into a cell.


1 Answers

Okay. The reason is SheetFormatProperties Object has required attribute defaultRowHeight. You could even assign defaultRowHeight = 0, but it must presents. So use

SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties(){ DefaultColumnWidth = 12.75D, DefaultRowHeight = 0D};

and be happy=) Слава Украине!

like image 56
Shelest Avatar answered Sep 28 '22 08:09

Shelest