none
Setting Column Widths Corrupts Spreadsheet RRS feed

  • Question

  • I'm working with Open XML for the first time and I am astonished at how complicated it is.  The Excel object model was complicated, but it seems simple by comparison.  I'm about ready to give up and look for a different solution, but I'll try posting this question here and see if I can get any simple answers. 

    I have an ASP.NET MVC web page and I want to let my user download a spreadsheet with data from the database.  I can get some data to appear in the downloaded spreadsheet, but when I tried to set the column widths in Open XML it started given me file validation errors and none of the data appears, and the column widths are not set.  It's just a blank spreadsheet.  This is the error it displays:

    Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
    Replaced Part: /xl/worksheets/sheet.xml part with XML error.  Load error. Line 1, column 211.

    Here is the test code that has only one cell loaded with the value Test 4.  It appears when I don't load the Columns object.  It disappears with this error when I try to load the Columns object.  Here is my code:

            private void CreatePartsTest()
            {
                document = SpreadsheetDocument.Create(moMemoryStream, SpreadsheetDocumentType.Workbook);
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
                Stylesheet stylesheet = new Stylesheet();
                workbookStylesPart.Stylesheet = stylesheet;
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("TestID");
                Worksheet worksheet = new Worksheet();
    
                Columns cs = new Columns();
                Column c = new Column()
                {
                    Min = (UInt32Value)1U,
                    Max = (UInt32Value)1U,
                    Width = 44.33203125D,
                    CustomWidth = true
                };
                cs.Append(c);
                //worksheetPart.Worksheet.Append(cs);
                worksheet.Append(cs);
    
                // LoadColumns(ref worksheet, oGridColumns);
                //worksheet.Append(new Columns(new Column() { Min = (uint)1U, Max = (uint)1U, Width = 24 }));
                SheetViews sheetViews = new SheetViews();
                SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
                sheetViews.Append(sheetView);
                SheetData sheetData1 = new SheetData();
                Row row1 = new Row() { RowIndex = (UInt32Value)1U };
                sheetData1.Append(row1);
                worksheet.Append(sheetViews);
                worksheet.Append(sheetData1);
                worksheetPart.Worksheet = worksheet;
    
                Sheet sheet = new Sheet();
                sheet.Name = "Test";
                sheet.SheetId = (uint)1;
                sheet.Id = "TestID";
                Sheets sheets = new Sheets();
                sheets.Append(sheet);
                workbook.Append(sheets);
    
                //oSpreadSheet.WorkbookPart.Workbook.Save();
                wbPart = document.WorkbookPart;
            }
    


    MCSD .NET developer in Dallas, Texas

    Friday, January 9, 2015 5:48 PM

Answers

  • I gave up trying to solve this.  I found a sample on Code Project that worked and I am modifying their sample to create my project.

    MCSD .NET developer in Dallas, Texas

    Monday, January 12, 2015 2:38 PM

All replies

  • Here is the test code that has only one cell loaded with the value Test 4.  It appears when I don't load the Columns object.  It disappears with this error when I try to load the Columns object. 

    Hi DallasSteve,

    How did you load the cells and why? Could you please post the details of the method "LoadColumns(ref worksheet, oGridColumns)"?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 12, 2015 7:40 AM
    Moderator
  • I gave up trying to solve this.  I found a sample on Code Project that worked and I am modifying their sample to create my project.

    MCSD .NET developer in Dallas, Texas

    Monday, January 12, 2015 2:38 PM
  • That would be great if you can post the link of the CodeProject sample.

    Thank you very much!


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 13, 2015 5:29 AM
    Moderator