Different excel column appearance when working with open xml

Question

This question edited several times and I Found the problem and its solution. I edited this post for other users use.
I created an excel file using open xml. The created excel file opens with no errors. It has the following problems:

The size of column is set correctly, but it is shown bigger than I expected ( bigger than template file).
The below image shows the template file:
and next image shows the created file using open xml :

As you see,the columns of the second file is wider, while there is no difference between the values.

Edit

The code begins with following lines:

using OpenXml=DocumentFormat.OpenXml;
using ExcelSpreadSheet = DocumentFormat.OpenXml.Spreadsheet;
using Packaging = DocumentFormat.OpenXml.Packaging;

        Packaging.SpreadsheetDocument spreadsheet = Packaging.SpreadsheetDocument.Create(filename, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);


        Packaging.ExtendedFilePropertiesPart extendedFilePropertiesPart = spreadsheet.AddExtendedFilePropertiesPart();
        GenerateExtendedFilePropertiesPart(extendedFilePropertiesPart);

        Packaging.CoreFilePropertiesPart coreFilePropertiesPart = spreadsheet.AddCoreFilePropertiesPart();
        GenerateCoreFilePropertiesPart(coreFilePropertiesPart);

        Packaging.WorkbookPart workBookPart = spreadsheet.AddWorkbookPart();
        workBookPart.Workbook = new ExcelSpreadSheet.Workbook();

        //Setting Style Part
        SetSheetStyle(workBookPart);

        //Generated using xml tools
        Packaging.ThemePart themePart = workBookPart.AddNewPart<Packaging.ThemePart>();
        GenerateThemePart(themePart);

        Packaging.WorksheetPart workSheetPart = workBookPart.AddNewPart<Packaging.WorksheetPart>();

        workSheetPart.Worksheet = new ExcelSpreadSheet.Worksheet();

        ExcelSpreadSheet.SheetDimension sheetDimension1 = new ExcelSpreadSheet.SheetDimension() { Reference = "A12:AA17" };
        workSheetPart.Worksheet.Append(sheetDimension1);

        ExcelSpreadSheet.Columns columns = new ExcelSpreadSheet.Columns();
        for (uint col = 0; col < PropertiesOfColumns.Length; col++)
        {
            UInt32 rangeIndex = col + 1;
            ExcelSpreadSheet.Column column1 = new ExcelSpreadSheet.Column()
            {
                Min = (OpenXml.UInt32Value)rangeIndex,
                Max = (OpenXml.UInt32Value)rangeIndex,
                Width = PropertiesOfColumns[col].Width,//Width of every column
                CustomWidth = OpenXml.BooleanValue.FromBoolean(true)
            };
            columns.Append(column1);
        }

        workSheetPart.Worksheet.Append(columns);

        ExcelSpreadSheet.SheetData sheetData = new ExcelSpreadSheet.SheetData();
        workSheetPart.Worksheet.Append(sheetData);

        ExcelSpreadSheet.PageMargins pageMargins1 = new ExcelSpreadSheet.PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
        workSheetPart.Worksheet.Append(pageMargins1);

        ExcelSpreadSheet.Sheets sheets = workBookPart.Workbook.AppendChild<ExcelSpreadSheet.Sheets>(new ExcelSpreadSheet.Sheets());

        ExcelSpreadSheet.Sheet sheet = new ExcelSpreadSheet.Sheet()
        {
            Id = workBookPart.GetIdOfPart(workSheetPart),
            SheetId = 1,
            Name = "mySheet"
        };
        sheets.Append(sheet);

        workSheetPart.Worksheet.SheetViews = new ExcelSpreadSheet.SheetViews();

        ExcelSpreadSheet.SheetView sheetView = new ExcelSpreadSheet.SheetView() {
            RightToLeft = true,
            TabSelected = true,
            ZoomScale=(OpenXml.UInt32Value)60U,
            ZoomScaleNormal=(OpenXml.UInt32Value)60U,
            WorkbookViewId = (DocumentFormat.OpenXml.UInt32Value)0U };

        workSheetPart.Worksheet.SheetViews.Append(sheetView);

        //And so on

I am more suspicious to SetSheetStyle:

private static void SetSheetStyle(Packaging.WorkbookPart workBookPart )

    {


        //Fonts
        ExcelSpreadSheet.Fonts fonts = new ExcelSpreadSheet.Fonts() { Count = (OpenXml.UInt32Value)2U, KnownFonts = true };

        ExcelSpreadSheet.Font font1 = new ExcelSpreadSheet.Font();
        ExcelSpreadSheet.Bold bold1 = new ExcelSpreadSheet.Bold();
        ExcelSpreadSheet.FontSize fontSize1 = new ExcelSpreadSheet.FontSize() { Val = 16D };
        ExcelSpreadSheet.Color color1 = new ExcelSpreadSheet.Color() { Theme = (OpenXml.UInt32Value)1U };
        ExcelSpreadSheet.FontName fontName1 = new ExcelSpreadSheet.FontName() { Val = "B Nazanin" };
        ExcelSpreadSheet.FontFamilyNumbering fontFamilyNumbering1 = new ExcelSpreadSheet.FontFamilyNumbering() { Val = 2 };
        ExcelSpreadSheet.FontScheme fontScheme1 = new ExcelSpreadSheet.FontScheme() { Val = ExcelSpreadSheet.FontSchemeValues.Minor };

        font1.Append(fontSize1);
        font1.Append(bold1);
        font1.Append(color1);
        font1.Append(fontName1);
        font1.Append(fontFamilyNumbering1);
        font1.Append(fontScheme1);

        ExcelSpreadSheet.Font font2 = new ExcelSpreadSheet.Font();
        ExcelSpreadSheet.Bold bold2 = new ExcelSpreadSheet.Bold();
        ExcelSpreadSheet.FontSize fontSize2 = new ExcelSpreadSheet.FontSize() { Val = 11D };
        ExcelSpreadSheet.Color color2 = new ExcelSpreadSheet.Color() { Theme = (OpenXml.UInt32Value)1U };
        ExcelSpreadSheet.FontName fontName2 = new ExcelSpreadSheet.FontName() { Val = "Calibri" };
        ExcelSpreadSheet.FontFamilyNumbering fontFamilyNumbering2 = new ExcelSpreadSheet.FontFamilyNumbering() { Val = 2 };
        ExcelSpreadSheet.FontScheme fontScheme2 = new ExcelSpreadSheet.FontScheme() { Val = ExcelSpreadSheet.FontSchemeValues.Minor };

        //font2.Append(bold1);
        font2.Append(fontSize2);
        font2.Append(color2);
        font2.Append(fontName2);
        font2.Append(fontFamilyNumbering2);
        font2.Append(fontScheme2);

        fonts.Append(font1);
        fonts.Append(font2);
        //Fills
        ExcelSpreadSheet.Fills fills = new ExcelSpreadSheet.Fills() { Count = (OpenXml.UInt32Value)3U };

        ExcelSpreadSheet.Fill fill1 = new ExcelSpreadSheet.Fill();
        ExcelSpreadSheet.PatternFill patternFill1 = new ExcelSpreadSheet.PatternFill() { PatternType = ExcelSpreadSheet.PatternValues.None };

        fill1.Append(patternFill1);

        ExcelSpreadSheet.Fill fill2 = new ExcelSpreadSheet.Fill();
        ExcelSpreadSheet.PatternFill patternFill2 = new ExcelSpreadSheet.PatternFill() { PatternType = ExcelSpreadSheet.PatternValues.Gray125 };

        fill2.Append(patternFill2);

        ExcelSpreadSheet.Fill fill3 = new ExcelSpreadSheet.Fill();

        ExcelSpreadSheet.PatternFill patternFill3 = new ExcelSpreadSheet.PatternFill() { PatternType = ExcelSpreadSheet.PatternValues.Solid };
        ExcelSpreadSheet.ForegroundColor foregroundColor1 = new ExcelSpreadSheet.ForegroundColor() { Rgb = "FFFFCC66" };
        ExcelSpreadSheet.BackgroundColor backgroundColor1 = new ExcelSpreadSheet.BackgroundColor() { Indexed = (OpenXml.UInt32Value)64U };

        patternFill3.Append(foregroundColor1);
        patternFill3.Append(backgroundColor1);

        fill3.Append(patternFill3);

        fills.Append(fill1);
        fills.Append(fill2);
        fills.Append(fill3);

        //Borders

        ExcelSpreadSheet.Borders borders = new ExcelSpreadSheet.Borders() { Count = (OpenXml.UInt32Value)2U };

        ExcelSpreadSheet.Border border1 = new ExcelSpreadSheet.Border();
        ExcelSpreadSheet.LeftBorder leftBorder1 = new ExcelSpreadSheet.LeftBorder();
        ExcelSpreadSheet.RightBorder rightBorder1 = new ExcelSpreadSheet.RightBorder();
        ExcelSpreadSheet.TopBorder topBorder1 = new ExcelSpreadSheet.TopBorder();
        ExcelSpreadSheet.BottomBorder bottomBorder1 = new ExcelSpreadSheet.BottomBorder();
        ExcelSpreadSheet.DiagonalBorder diagonalBorder1 = new ExcelSpreadSheet.DiagonalBorder();

        border1.Append(leftBorder1);
        border1.Append(rightBorder1);
        border1.Append(topBorder1);
        border1.Append(bottomBorder1);
        border1.Append(diagonalBorder1);

        ExcelSpreadSheet.Border border2 = new ExcelSpreadSheet.Border();

        ExcelSpreadSheet.LeftBorder leftBorder2 = new ExcelSpreadSheet.LeftBorder() { Style = ExcelSpreadSheet.BorderStyleValues.Thin };
        color1 = new ExcelSpreadSheet.Color() { Indexed = (OpenXml.UInt32Value)64U };

        leftBorder2.Append(color1);

        ExcelSpreadSheet.RightBorder rightBorder2 = new ExcelSpreadSheet.RightBorder() { Style = ExcelSpreadSheet.BorderStyleValues.Thin };
        color2 = new ExcelSpreadSheet.Color() { Indexed = (OpenXml.UInt32Value)64U };

        rightBorder2.Append(color2);

        ExcelSpreadSheet.TopBorder topBorder2 = new ExcelSpreadSheet.TopBorder() { Style = ExcelSpreadSheet.BorderStyleValues.Thin };
        ExcelSpreadSheet.Color color3 = new ExcelSpreadSheet.Color() { Indexed = (OpenXml.UInt32Value)64U };

        topBorder2.Append(color3);
        ExcelSpreadSheet.BottomBorder bottomBorder2 = new ExcelSpreadSheet.BottomBorder();
        ExcelSpreadSheet.DiagonalBorder diagonalBorder2 = new ExcelSpreadSheet.DiagonalBorder();

        border2.Append(leftBorder2);
        border2.Append(rightBorder2);
        border2.Append(topBorder2);
        border2.Append(bottomBorder2);
        border2.Append(diagonalBorder2);

        borders.Append(border1);
        borders.Append(border2);





        ExcelSpreadSheet.CellFormats cellFormats = new ExcelSpreadSheet.CellFormats() { Count = (OpenXml.UInt32Value)3U };
        //Default Cell format (Not Applied)
        ExcelSpreadSheet.CellFormat cellFormat1 = new ExcelSpreadSheet.CellFormat()
        {
            NumberFormatId = (OpenXml.UInt32Value)0U,
            FontId = (OpenXml.UInt32Value)0U,
            FillId = (OpenXml.UInt32Value)0U,
            BorderId = (OpenXml.UInt32Value)0U,
            FormatId = (OpenXml.UInt32Value)0U
        };

        //Style for Header
        ExcelSpreadSheet.CellFormat cellFormat2 = new ExcelSpreadSheet.CellFormat()
        {
            NumberFormatId = (OpenXml.UInt32Value)0U,
            FontId = (OpenXml.UInt32Value)0U,
            FillId = (OpenXml.UInt32Value)2U,
            BorderId = (OpenXml.UInt32Value)1U,
            FormatId = (OpenXml.UInt32Value)0U,
            ApplyFont = true,
            ApplyFill = true,
            ApplyBorder = true,
            ApplyAlignment=true
        };

        ExcelSpreadSheet.Alignment alignment1 = new ExcelSpreadSheet.Alignment() { Horizontal = ExcelSpreadSheet.HorizontalAlignmentValues.Center, Vertical = ExcelSpreadSheet.VerticalAlignmentValues.Center, WrapText = true };
        cellFormat2.Append(alignment1);


        //Style for Rows
        ExcelSpreadSheet.CellFormat cellFormat3 = new ExcelSpreadSheet.CellFormat()
        {
            NumberFormatId = (OpenXml.UInt32Value)0U,
            FontId = (OpenXml.UInt32Value)1U,
            FillId = (OpenXml.UInt32Value)0U,
            BorderId = (OpenXml.UInt32Value)1U,
            FormatId = (OpenXml.UInt32Value)0U,
            ApplyFont = true,
            ApplyBorder = true
        };

        cellFormats.Append(cellFormat1);
        cellFormats.Append(cellFormat2);
        cellFormats.Append(cellFormat3);
        //Cell Style
        ExcelSpreadSheet.CellStyles cellStyles = new ExcelSpreadSheet.CellStyles() { Count = (OpenXml.UInt32Value)2U };
        ExcelSpreadSheet.CellStyle cellStyle1 = new ExcelSpreadSheet.CellStyle() { Name = "Normal", FormatId = (OpenXml.UInt32Value)0U, BuiltinId = (OpenXml.UInt32Value)0U };

        cellStyles.Append(cellStyle1);

        //ExcelSpreadSheet.CellStyle cellStyle2 = new ExcelSpreadSheet.CellStyle() { Name = "Normal", FormatId = (OpenXml.UInt32Value)3U, BuiltinId = (OpenXml.UInt32Value)0U };

        //cellStyles.Append(cellStyle2);



        workBookPart.AddNewPart<Packaging.WorkbookStylesPart>();
        workBookPart.WorkbookStylesPart.Stylesheet = new ExcelSpreadSheet.Stylesheet();
        ExcelSpreadSheet.Stylesheet stylesheet1 = workBookPart.WorkbookStylesPart.Stylesheet;



        stylesheet1.Append(fonts);
        stylesheet1.Append(fills);
        stylesheet1.Append(borders);

        stylesheet1.Append(cellFormats);
        stylesheet1.Append(cellStyles);
        //.Append(stylesheet1);

        workBookPart.WorkbookStylesPart.Stylesheet.Save();


    }

Is this a programming question? If so, you should post some code. — Andy G, May 17 '14 at 19:21
the code is so much. and I don't know where the problem is. I added some pieces of my code. — Ali Fattahian, May 17 '14 at 19:43
Looks like you have 2 separate problems. Can I suggest you split this into 2 separate questions? For the first one, include the XML contents of your workbook part. — JasonPlutext, May 17 '14 at 21:43

Ali Fattahian · Answer 1 · 2014-05-18T08:27:53.973

I found a clue for one of my questions. The second problem is because cell style is set to have different font . Here is the link which will help me to make my code. Font of the column Heading and Measurement units and rulers in Excel
I change following code for set font of cellstyle:

ExcelSpreadSheet.CellStyle cellStyle1 = new ExcelSpreadSheet.CellStyle() { Name = "Normal", FormatId = (OpenXml.UInt32Value)1U, BuiltinId = (OpenXml.UInt32Value)0U };

I set FormatId = (OpenXml.UInt32Value)1U which means set second font("Calibri 11") for cell style .

Different excel column appearance when working with open xml

1 Answers1