Trouble added columns to define width using OpenXML

Question

            //-----------------------------------------
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            //-----------------------------------------
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);


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

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

            //------------------------------------------------------------
            // create column specs
            //<sheetFormatPr defaultRowHeight="14.4"/>
            //<cols>
            //  <col customWidth="1" width="44.33203125" max="1" min="1"/>
            //  <col customWidth="1" width="27" max="2" min="2"/>
            //  <col customWidth="1" width="18.21875" max="3" min="3"/></col
            //</cols>
            // http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/
            //http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.worksheet
            //------------------------------------------------------------
            Columns cs = new Columns();
            cs.Append(createColumn(44.33203125, 1, 1));
            cs.Append(createColumn(27, 2, 2));
            cs.Append(createColumn(18.21875, 3, 3));
            worksheetPart.Worksheet.Append(cs);

In the code above, I am trying to create columns so I can define column width. When I append the columns object to the worksheet, the worksheet fails to open. If I look at the zip gile and the worksheet created, the column statement come after the worksheet data (on the one that fails). On one that I create manually, the cols statement comes before the data rows.

What am I doing wrong when creating the columns?

---

dave

Answer 1

Hi dave,

Thanks for posting in the MSDN forum.

As far as I know that Columns must be appended to Worksheet before the SheetData has been appended.

I hope it can help you.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

The code below follows is the code block above.  So I think I am adding the columns before the sheet data. What am I misisng?

 //-----------------------------
            // now create the 1st row...
            //-----------------------------
            insertCellString(worksheetPart, "A", 1, "sss", StyleIndex.Bold);
            insertCellString(worksheetPart, "B", 1, "dddy", StyleIndex.Bold);
            insertCellString(worksheetPart, "C", 1, "ffffng", StyleIndex.Bold);
      

---

dave

Answer 3

I have tries moving the column code around but it doesn;t seem to matter.  Still fails when trying to open spreadsheet.

---

dave

Answer 4

I have tries moving the column code around but it doesn;t seem to matter.  Still fails when trying to open spreadsheet.

---

dave

Answer 5

Hi Dave,

I think this snippet might help you. It works fine on my side.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using log4net;

namespace ExcelColumnWidthOX
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            ILog log = log4net.LogManager.GetLogger(typeof(Program));
            string strPath = null;
            SaveFileDialog objSaveFileDialog = new SaveFileDialog();
            objSaveFileDialog.Filter = "Excel Document (*.xlsx)|*.xlsx";
            objSaveFileDialog.ShowDialog();
            strPath = objSaveFileDialog.FileName;
            log.Info("Excel File Name : " + strPath);
            if (strPath.Length > 0)
            {
                using (SpreadsheetDocument objDocument =
                    SpreadsheetDocument.Create(strPath,
SpreadsheetDocumentType.Workbook))
                {
                    log.Info("Begin to create Excel document");
                    WorkbookPart objWorkbookPart =
                        objDocument.AddWorkbookPart();
                    WorksheetPart objWorksheetPart =
                         objWorkbookPart.AddNewPart<WorksheetPart>();
                    string strWorkSheetPartrId =
                         objWorkbookPart.GetIdOfPart(objWorksheetPart);
                    objWorksheetPart.Worksheet = new Worksheet();
                    SheetData objSheetData = new SheetData();
                    Sheets objSheets = new Sheets();
                    Sheet objSheet = new Sheet()
                    {
                        Name = "MySheet",
                        SheetId = (UInt32Value)1U,
                        Id = strWorkSheetPartrId
                    };
                    objSheets.Append(objSheet);
                    objWorkbookPart.Workbook = new Workbook();
                    objWorkbookPart.Workbook.Append(objSheets);
                    log.Info("Finish to create Excel document");
                    Columns cs = new Columns();
                    log.Info("Create Columns");
                    Column c = new Column()
                    {
                        Min = (UInt32Value)1U,
                        Max = (UInt32Value)1U,
                        Width = 44.33203125D,
                        CustomWidth = true
                    };
                    log.Info("Create Column");
                    cs.Append(c);
                    log.Info("Columns append column");
                    objWorksheetPart.Worksheet.Append(cs);
                    log.Info("worksheet append columns");
                    // This is a important step.
                    // If Columns appended to worksheet after sheetdata Excel
                    // will throw error.
                    objWorksheetPart.Worksheet.Append(objSheetData);
                }
            }
            Console.ReadKey();
        }
    }
}

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us