[Jim SnyderParticipant]

I’ll admit I don’t know how to do it the way you are asking, but this is how I do it:
` private void SetPackageProperties(SpreadsheetDocument wkbk)
{
wkbk.PackageProperties.Creator = “Jim Snyder”;
wkbk.PackageProperties.Created = DateTime.Now;
wkbk.PackageProperties.Modified = DateTime.Now;
wkbk.PackageProperties.LastModifiedBy = “Jim Snyder”;

wkbk.PackageProperties.Category = “Regional Report”;
wkbk.PackageProperties.Description = “Inventory Recon rpt.”;
wkbk.PackageProperties.Subject = “Regional recon”;
wkbk.PackageProperties.Title = “O’Bleness/AdviCare Inventory Recon – Daily”;
}

[Jim SnyderParticipant]

Great! Thanks for the update.

[Jim SnyderParticipant]

Too few OpenXML experts as it is. If I don’t chime in, who will help me?

[Jim SnyderParticipant]

That will have to wait for Eric then. I am only aware of the “Developer” tab being added in Excel.

[Jim SnyderParticipant]

Are you referring to the “Developer” tab in Excel? If so, it is a setting you have to change. Under the “File” menu open the “Options”. Open the “Customize Ribbon” window and select the “Developer” checkbox.

• This reply was modified 7 years, 6 months ago by  Jim Snyder. Reason: missspellling

[Jim SnyderParticipant]

You are welcome! I am still learning it myself…

[Jim SnyderParticipant]

You didn’t read far enough:

There, you have your widths. Go back to your code and plug that in. Tada, mission accomplished.

“Wait, so why can’t those widths be calculated?”

Well, they can. I just think the effort’s not worth it. First, you need to know the font of the text in that cell/column. Then there’s some weird truncation calculation based on the width of characters in that font… You know what, here’s an extract from the Open XML SDK documentation:

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256

[Jim SnyderParticipant]

Try this link as a starter:
https://social.msdn.microsoft.com/Forums/office/en-US/1d93eca8-2949-4d12-8dd9-15cc24128b10/trouble-added-columns-to-define-width-using-openxml?forum=oxmlsdk

Vincent Tan wrote about this. Unfortunately, he no longer participates:
http://polymathprogrammer.com/2010/01/11/custom-column-widths-in-excel-open-xml/

I personally haven’t done it, but the links are better than no answer.

[Jim SnyderParticipant]

This is getting long in the tooth, but an answer still needs to be there for others. There are some things that are background to cover first. Since you didn’t give much detail as to where you are starting from, you start with building the top level container to add parts to. At an absolute minimum, you need this top level container and a worksheet (can be empty).
You need to add other parts to make this happen. One is the WorkbookPart which is the container to add a workbook to. Another is the WorksheetPart to contain the sheet description. This also requires a SheetData part to build your spreadsheet rows and cells in. The part that will be of help to you is the WorkbookStylesPart that will be the container for your stylesheet. An example stylesheet would look like this below. It has a commented bold style for you to emulate:

        // Style part
        private Stylesheet GenerateWorkbookStylesPartContent(WorkbookStylesPart stylesPart)
        {
            return new Stylesheet
            (
                new Fonts
                (
                    new Font(new FontSize(){Val = 11}, new Color(){Rgb = new HexBinaryValue(){Value = "000000"}}, new FontName(){Val = "Calibri"}),
                    new Font(new Bold(), new FontSize(){Val = 11}, new Color(){Rgb = new HexBinaryValue(){Value = "000000"}}, new FontName(){Val = "Calibri"}),
                    new Font(new Italic(), new FontSize(){Val = 11}, new Color(){Rgb = new HexBinaryValue(){Value = "000000"}}, new FontName(){Val = "Calibri"})
                ),
                new Fills
                (
                    new Fill(new PatternFill(){PatternType = PatternValues.None}),       // Required. FillID = 0
                    new Fill(new PatternFill(){PatternType = PatternValues.Gray125}),    // Required. FillID = 1
                    new Fill(new PatternFill(new ForegroundColor(){Rgb = new HexBinaryValue(){Value = tabColor1}}){PatternType = PatternValues.Solid}), // FillID = 3
                    new Fill(new PatternFill(new ForegroundColor(){Rgb = new HexBinaryValue(){Value = tabColor2}}){PatternType = PatternValues.Solid}), // FillID = 4
                    new Fill(new PatternFill(new ForegroundColor(){Rgb = new HexBinaryValue(){Value = "FFE2E2E2"}}){PatternType = PatternValues.Solid}) // FillID = 5
                ),
                new Borders
                (
                    new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder()),     // Default - required
                    new Border
                    (
                        new LeftBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                        new RightBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                        new TopBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                        new BottomBorder(new Color(){Auto = true}){Style = BorderStyleValues.Thin},
                        new DiagonalBorder(new Color(){Auto = true}){Style = BorderStyleValues.None}
                    )
                ),
                new CellFormats
                (
                    new CellFormat(){FontId = 0, FillId = 0, BorderId = 0, ApplyFont = true},   // Index 0 - default cell style.
                    new CellFormat(){FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true},   // Index 1 - Bold.
                    new CellFormat(){FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true},   // Index 2 - Italic.
                    new CellFormat(){FontId = 0, FillId = 3, BorderId = 0, ApplyFill = true},   // Index 3 - Lt yellow fill.
                    new CellFormat(){FontId = 0, FillId = 4, BorderId = 0, ApplyFill = true},   // Index 4 - Lt green fill.
                    new CellFormat(){FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true},  // Index 5 - Border.
                    new CellFormat(){FontId = 1, FillId = 5, BorderId = 1, ApplyFont = true, ApplyFill = true, ApplyBorder = true}   // Index 6 - Bold w/lt gray fill and borders.
                )
            );              // Return StyleSheet
        }

There are some important things to note:

[Jim SnyderParticipant]

Fifa, I do not see the correlation to generating conditional formatting in Excel.
Leebean, I am working on the same thing at the moment. From your post, you have successfully built a stylesheet. I have found a few links that are helping me. The key thing I got from this article was that entries for DifferentialFormats and TableStyles must exist to get the completed spreadsheet to open. I have been working on trying to flesh that out with official details for a few days. The pertinent code is:

    DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
    TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };

...

    stylesheet1.Append(fonts1);
    stylesheet1.Append(fills1);
    stylesheet1.Append(borders1);
    stylesheet1.Append(cellStyleFormats1);
    stylesheet1.Append(cellFormats1);
    stylesheet1.Append(cellStyles1);
    stylesheet1.Append(differentialFormats1);
    stylesheet1.Append(tableStyles1);
    stylesheet1.Append(stylesheetExtensionList1);
    return stylesheet1;

[Author]

Posts