C# OpenXML-only specific cell styling for Excel export

Question

I'm relatively new to C#. Below is the code I currently have. It works for converting a DataTable into an Excel-format exportable Byte array. I first call Create() on a populated DataSet, storing it in Byte[], which is then populated by data from the DataSet. Note that all the code below works perfectly and I'm looking to add some styling to the exported excel file as per certain conditions. I'm wondering where such 'if'(?) statements could go, as in where exactly could I append this styling info, given my specific constraints (I'm supposed to identify data in particular rows and highlight them.) I've tried a lot of resources online and there's an issue with almost each one, given the implementation I'm using. I'm also not allowed to use any libraries outside of standard OpenXML.

    public static Byte[] Create(DataSet ds)
    {
        Dictionary<String, List<OpenXmlElement>> sets = ToSheets(ds);
        Byte[] _data = new Byte[] { };

        using (MemoryStream _ms = new MemoryStream())
        {
            using (SpreadsheetDocument package = SpreadsheetDocument.Create(_ms, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookpart = package.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());
                foreach (KeyValuePair<String, List<OpenXmlElement>> set in sets)
                {
                    WorksheetPart worksheetpart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value));
                    worksheetpart.Worksheet.Save();

                    Sheet sheet = new Sheet()
                    {
                        Id = workbookpart.GetIdOfPart(worksheetpart),
                        SheetId = Convert.ToUInt32(sheets.Count() + 1),
                        Name = set.Key
                    };
                    sheets.AppendChild(sheet);
                }
                workbookpart.Workbook.Save();
            }
            _data = _ms.ToArray();
        }
        return _data;
    }

Above is the main Create() function and below is the ToSheets() function. It should be self-explanatory.

    public static Dictionary<String, List<OpenXmlElement>> ToSheets(DataSet ds)
    {
        return
            (from dt in ds.Tables.OfType<DataTable>()
             select new
             {
                 Key = dt.TableName,
                 Value = (
                 new List<OpenXmlElement>(
                    new OpenXmlElement[] 
            {
                new Row(
                    from d in dt.Columns.OfType<DataColumn>()
                    select (OpenXmlElement)new Cell()
                    {
                        CellValue = new CellValue(d.ColumnName),
                        DataType = CellValues.String
                    })
            })).Union
                 ((from dr in dt.Rows.OfType<DataRow>()
                   select ((OpenXmlElement)new Row(from dc in dr.ItemArray
                                                   select (OpenXmlElement)new Cell()
                                                   {
                                                       CellValue = new CellValue(dc.ToString()),
                                                       DataType = CellValues.String
                                                   })))).ToList()
             }).ToDictionary(p => p.Key, p => p.Value);
    }

I would appreciate it if anyone could point me in the right direction as to where I can append the style info and how I could go about doing that (remember I have to style rows based on data in the rows, ie. rows with certain cell values should have that row styled in a particular way.) Specifically, I'm looking to style cells in rows with a blue background and no borders when the first cell in said rows contains data, say "X".

Thank you for your time!

score 0 · Accepted Answer · edited May 23 '17 at 12:07

I managed to solve this myself. I added styling information as detailed at this page.

Then I modified my own code to reflect selective changes.

    public Dictionary<String, List<OpenXmlElement>> ToSheets(DataSet ds)
    {
        return
            (from dt in ds.Tables.OfType<DataTable>()
             select new
             {
                 Key = dt.TableName,
                 Value = (
                 new List<OpenXmlElement>(
                    new OpenXmlElement[] 
            {
                new Row(
                    from d in dt.Columns.OfType<DataColumn>()
                    select (OpenXmlElement)new Cell()
                    {
                        CellValue = new CellValue(d.ColumnName),
                        StyleIndex = 1,
                        DataType = CellValues.String
                    })
            })).Union
                 ((from dr in dt.Rows.OfType<DataRow>()
                   select ((OpenXmlElement)new Row(from dc in dr.ItemArray
                                                   select (OpenXmlElement)new Cell()
                                                   {
                                                       CellValue = new CellValue(dc.ToString()),
                                                       DataType = CellValues.String,
                                                       StyleIndex = styleIdentifier(dc.ToString())
                                                   })))).ToList()
             }).ToDictionary(p => p.Key, p => p.Value);
    }

As clearly noticeable, I defined multiple StyleIndex values by appending multiple children to stylesPart.Stylesheet.CellFormats using the linked guide above. It worked! Then I simply used the function below to dynamically identify the styling information I want to append for each particular row.

    public UInt32Value styleIdentifier(String str)
    {
        int temp;

        xCount.Value = (Convert.ToInt32(this.xCount.Value) + 1).ToString();

        if (int.TryParse(str, out temp))
        { xCount.Value = "0"; }

        if (Convert.ToInt32(this.xCount.Value) < 4)
        { return 2; }

        return 3;
    }

On the code-front side of things, I simply used "xCount" as a HiddenField value with initial value >5. This way, for every row identified from data in column 1 as the row to be highlighted, every cell is highlighted (upto 4, given I have 4 columns). Then the value of xCount is reset until another such cell is encountered and the process repeats, until it encounters the next to-be-highlighted row.

Thanks a ton once more to the good folks over at StackOverflow chat and especially @juanvan for guided me in the right direction!

C# OpenXML-only specific cell styling for Excel export

1 Answers1