Bold a cell/row

May 9, 2008 at 4:32 PM
Is there a way to bold a cell, row, column, without having to use a named style? It seems like the simple formatting: bold, underline, italic, font color, etc. should be available. Does anyone know how to use these?

Or how to create a style on the fly to be able to do this?
May 9, 2008 at 8:52 PM
There isn't any way to do that (that I'm aware of). I also don't think that it supports creating a style on the fly. I created styles in a blank worksheet for the basic stuff (bold, etc) and then pulled the syles.xml file out and used it for my workbook creation. Not ideal by any means, but it works.

As an aside, I would say that if development resumed on this project, styles should be one of the first things that they focus on.
May 10, 2008 at 12:46 AM
Edited May 10, 2008 at 12:49 AM
skiafnm,
I am curious how you did it.  Do you mind posting the details (code) of how you used the style sheet for your workbook creation?


I ended up implementing bold myself, as well as adding in the streaming constructor mentioned in other posts.  Here is what you need to add to get basic bold.  I realize it is a hack and only allows bold/not bold, but for our needs that was sufficient for now.  Unfortunately there are some things half implemented, like you can access Row.StyleID, but setting it does nothing...

Once you make the changes you simply do this to bold a field:
worksheet.Cell(1, 1).Bold = true;

Anyway, 3 files need to be modified:
ExcelCell.cs (add a setter)
------------
/// <summary>
/// Toggles if the cell is bold
/// </summary>
public bool Bold { set { this.StyleID = value ? 1 : 0; } }


ExcelPackage.cs
------------
// remove the temporary part that created the default xml content type
_package.DeletePart(uriDefaultContentType);

// Right after the above lines in the constructor(s) you need to add:
// Force create the initial Styles.xml file
this.Workbook.StylesXml = this.Workbook.StylesXml;

ExcelWorkbook.cs
------------------
//change the StylesXml getter/setter to the following:
#region StylesXml
/// <summary>
/// Provides access to the XML data representing the styles in the package.
/// </summary>
public XmlDocument StylesXml
{
    get
    {
        if (_xmlStyles == null)
        {
            if (_xlPackage.Package.PartExists(StylesUri))
                _xmlStyles = _xlPackage.GetXmlFromUri(StylesUri);
            else
            {
                // create a new styles part and add to the package
                PackagePart partSyles = _xlPackage.Package.CreatePart(StylesUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml");

                // create the style sheet
                _xmlStyles = new XmlDocument();
                XmlElement tagStylesheet = _xmlStyles.CreateElement("styleSheet", ExcelPackage.schemaMain);
                _xmlStyles.AppendChild(tagStylesheet);
                // create the fonts tag
                XmlElement tagFonts = _xmlStyles.CreateElement("fonts", ExcelPackage.schemaMain);
                tagFonts.SetAttribute("count", "2");
                tagStylesheet.AppendChild(tagFonts);

                // create the basic font tag
                XmlElement tagFont = _xmlStyles.CreateElement("font", ExcelPackage.schemaMain);
                tagFonts.AppendChild(tagFont);
                // create the sz tag
                XmlElement tagSz = _xmlStyles.CreateElement("sz", ExcelPackage.schemaMain);
                tagSz.SetAttribute("val", "11");
                tagFont.AppendChild(tagSz);
                // create the name tag
                XmlElement tagName = _xmlStyles.CreateElement("name", ExcelPackage.schemaMain);
                tagName.SetAttribute("val", "Calibri");
                tagFont.AppendChild(tagName);

                // create a bold font tag
                tagFont = _xmlStyles.CreateElement("font", ExcelPackage.schemaMain);
                tagFonts.AppendChild(tagFont);
                // create the sz tag
                tagSz = _xmlStyles.CreateElement("sz", ExcelPackage.schemaMain);
                tagSz.SetAttribute("val", "11");
                tagFont.AppendChild(tagSz);
                // create the name tag
                tagName = _xmlStyles.CreateElement("name", ExcelPackage.schemaMain);
                tagName.SetAttribute("val", "Calibri");
                tagFont.AppendChild(tagName);
                // create the name tag
                XmlElement tagBold = _xmlStyles.CreateElement("b", ExcelPackage.schemaMain);
                tagFont.AppendChild(tagBold);

                // create the fills tag
                XmlElement tagFills = _xmlStyles.CreateElement("fills", ExcelPackage.schemaMain);
                tagFills.SetAttribute("count", "1");
                tagStylesheet.AppendChild(tagFills);

                // create the fill tag
                XmlElement tagFill = _xmlStyles.CreateElement("fill", ExcelPackage.schemaMain);
                tagFills.AppendChild(tagFill);

                // create the borders tag
                XmlElement tagBorders = _xmlStyles.CreateElement("borders", ExcelPackage.schemaMain);
                tagBorders.SetAttribute("count", "1");
                tagStylesheet.AppendChild(tagBorders);

                // create the border tag
                XmlElement tagBorder = _xmlStyles.CreateElement("border", ExcelPackage.schemaMain);
                tagBorders.AppendChild(tagBorder);

                // create the cellStyleXfs tag
                XmlElement tagCellStyleXfs = _xmlStyles.CreateElement("cellStyleXfs", ExcelPackage.schemaMain);
                tagCellStyleXfs.SetAttribute("count", "1");
                tagStylesheet.AppendChild(tagCellStyleXfs);

                // create the xf tag
                XmlElement tagXf = _xmlStyles.CreateElement("xf", ExcelPackage.schemaMain);
                tagXf.SetAttribute("numFmtId", "0");
                tagXf.SetAttribute("fontId", "0");
                tagCellStyleXfs.AppendChild(tagXf);

                // create the cellXfs tag
                XmlElement tagCellXfs = _xmlStyles.CreateElement("cellXfs", ExcelPackage.schemaMain);
                tagCellXfs.SetAttribute("count", "2");
                tagStylesheet.AppendChild(tagCellXfs);

                // create the xf tag
                XmlElement tagXf2 = _xmlStyles.CreateElement("xf", ExcelPackage.schemaMain);
                tagXf2.SetAttribute("numFmtId", "0");
                tagXf2.SetAttribute("fontId", "0");
                tagXf2.SetAttribute("xfId", "0");
                tagCellXfs.AppendChild(tagXf2);

                // create the xf tag
                XmlElement tagXf3 = _xmlStyles.CreateElement("xf", ExcelPackage.schemaMain);
                tagXf3.SetAttribute("numFmtId", "0");
                tagXf3.SetAttribute("fontId", "1");
                tagXf3.SetAttribute("xfId", "1");
                tagXf3.SetAttribute("applyFont", "1");
                tagCellXfs.AppendChild(tagXf3);

                // save it to the package
                StreamWriter streamStyles = new StreamWriter(partSyles.GetStream(FileMode.Create, FileAccess.Write));
                _xmlStyles.Save(streamStyles);
                streamStyles.Close();
                _xlPackage.Package.Flush();

                // create the relationship between the workbook and the new shared strings part
                _xlPackage.Workbook.Part.CreateRelationship(StylesUri, TargetMode.Internal, ExcelPackage.schemaRelationships + "/styles");
                _xlPackage.Package.Flush();
            }
        }
        return (_xmlStyles);
    }
    set
    {
        _xmlStyles = value;
        if (!_xlPackage.Package.PartExists(StylesUri))
        {
            // create a new styles part and add to the package
            PackagePart partSyles = _xlPackage.Package.CreatePart(StylesUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml");

            // save it to the package
            StreamWriter streamStyles = new StreamWriter(partSyles.GetStream(FileMode.Create, FileAccess.Write));
            _xmlStyles.Save(streamStyles);
            streamStyles.Close();
            _xlPackage.Package.Flush();

            // create the relationship between the workbook and the new shared strings part
            _xlPackage.Workbook.Part.CreateRelationship(StylesUri, TargetMode.Internal, ExcelPackage.schemaRelationships + "/styles");
            _xlPackage.Package.Flush();
        }
    }
}
#endregion
May 11, 2008 at 9:49 PM
I'm not sure exactly what code you're looking for, but here's how I'm applying an external style sheet:

using (ExcelPackage xlPackage = new ExcelPackage(ms))
        {
            XmlDocument styles = new XmlDocument();
            XmlTextReader reader = new XmlTextReader(path_to_styles.xml);
            styles.Load(reader);
            reader.Close();

            xlPackage.Workbook.StylesXml = styles;

            worksheet = xlPackage.Workbook.Worksheets.Add("Test");
           
            worksheet.Cell(1, 1).Value = "This is a heading";
            worksheet.Cell(1, 1).Style = "MyHeading";

             ...etc...
}

To get the styles.xml file, simply create a workbook with styles and save it.  Then add .zip to the end of the file (Styles.xlsx.zip).  Unzip the file and in one of the folders, you'll see the styles.xml file.

What I like about how I'm doing it:  I just keep an empty workbook that has a list of my styles, so updating a style is very easy to do.
What I don't like:  Every font change, bold, underline, etc, requires its own style.  I've taken to naming my styles very descriptively: "Times 12 Bold Underline"

Let me know if that's not what you were looking for.
Jun 13, 2008 at 7:49 PM


using (ExcelPackage xlPackage = new ExcelPackage(ms))
        {
            XmlDocument styles = new XmlDocument();
            XmlTextReader reader = new XmlTextReader(path_to_styles.xml);
            styles.Load(reader);
            reader.Close();

            xlPackage.Workbook.StylesXml = styles;

            worksheet = xlPackage.Workbook.Worksheets.Add("Test");
           
            worksheet.Cell(1, 1).Value = "This is a heading";
            worksheet.Cell(1, 1).Style = "MyHeading";

             ...etc...
}

I had a problem with this code - when I do xlPackage.Save() it throws an exception that no URI part exists: /xl/styles. Instead, what worked fine for me is:
using (ExcelPackage xlPackage = new ExcelPackage(ms))
        {
            xlPackage.Workbook.StylesXml.Load(path_to_styles.xml);

            worksheet = xlPackage.Workbook.Worksheets.Add("Test");
           
            worksheet.Cell(1, 1).Value = "This is a heading";
            worksheet.Cell(1, 1).Style = "MyHeading";

             ...etc...
}
and it's a lot shorter.
Jun 26, 2008 at 6:28 PM


skiafnm wrote:
There isn't any way to do that (that I'm aware of). I also don't think that it supports creating a style on the fly. I created styles in a blank worksheet for the basic stuff (bold, etc) and then pulled the syles.xml file out and used it for my workbook creation. Not ideal by any means, but it works.

As an aside, I would say that if development resumed on this project, styles should be one of the first things that they focus on.


I agree. I think adding a simple styles API (being able to set styles on a range of cells, or single cell and having the API take care of it would be HUGE). I think the COM API of yesteryear would be a good model for how this would look.

slide
Jun 27, 2008 at 2:02 AM
The COM API would be a great model.  It handled styles very well.