none
Coloring cells in excel sheet using openXML in C# RRS feed

  • Question

  • Hi,

    Please explain, how to color the two different cells (C4,F6) with two different colors in the excel sheet using OpenXML in C#.

    thanks,

    Elangovan P

     

    Wednesday, December 14, 2011 5:50 PM

Answers

  • Hi Elangovan P,

    Below is the console application which I have made and it can create an simple excel file which cells are set like this:

    using System;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
     
    namespace OpenXmlExcelGenerateStyle
    {
        class Program
        {
            static void Main(string[] args)
            {
                string sFile = "ExcelAndStyles.xlsx";
                if (File.Exists(sFile))
                {
                    File.Delete(sFile);
                }
                BuildWorkbook(sFile);
            }
     
            private static void BuildWorkbook(string sFile)
            {
                try
                {
                    using (SpreadsheetDocument xl = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook))
                    {
                        WorkbookPart wbp = xl.AddWorkbookPart();
                        WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                        Workbook wb = new Workbook();
                        FileVersion fv = new FileVersion();
                        fv.ApplicationName = "Microsoft Office Excel";
     
                        Worksheet ws = new Worksheet();
                        WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                        // add styles to sheet
                        wbsp.Stylesheet = CreateStylesheet();
                        wbsp.Stylesheet.Save();
                        
                        // generate rows
                        SheetData sd = CreateSheetData();
                        ws.Append(sd);
                        wsp.Worksheet = ws;
                        wsp.Worksheet.Save();
                        Sheets sheets = new Sheets();
                        Sheet sheet = new Sheet();
                        sheet.Name = "Sheet1";
                        sheet.SheetId = 1;
                        sheet.Id = wbp.GetIdOfPart(wsp);
                        sheets.Append(sheet);
                        wb.Append(fv);
                        wb.Append(sheets);
     
                        xl.WorkbookPart.Workbook = wb;
                        xl.WorkbookPart.Workbook.Save();
                        xl.Close();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    Console.ReadLine();
                }
            }
            private static SheetData CreateSheetData()
            {
                SheetData sheetData1 = new SheetData();
                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
     
                row1.Append(cell1);
     
                Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                Cell cell2 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)2U };
     
                row2.Append(cell2);
     
                Row row3 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                Cell cell3 = new Cell() { CellReference = "C3", StyleIndex = (UInt32Value)3U };
     
                row3.Append(cell3);
     
                sheetData1.Append(row1);
                sheetData1.Append(row2);
                sheetData1.Append(row3);
     
                return sheetData1;
            }
     
            private static Stylesheet CreateStylesheet()
            {
                Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                stylesheet1.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                stylesheet1.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
     
                Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
     
                Font font1 = new Font();
                FontSize fontSize1 = new FontSize() { Val = 11D };
                Color color1 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName1 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
     
                font1.Append(fontSize1);
                font1.Append(color1);
                font1.Append(fontName1);
                font1.Append(fontFamilyNumbering1);
                font1.Append(fontScheme1);
     
                fonts1.Append(font1);
     
                Fills fills1 = new Fills() { Count = (UInt32Value)5U };
     
                // FillId = 0
                Fill fill1 = new Fill();
                PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
                fill1.Append(patternFill1);
     
                // FillId = 1
                Fill fill2 = new Fill();
                PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
                fill2.Append(patternFill2);
     
                // FillId = 2,RED
                Fill fill3 = new Fill();
                PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
                BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill3.Append(foregroundColor1);
                patternFill3.Append(backgroundColor1);
                fill3.Append(patternFill3);
     
                // FillId = 3,BLUE
                Fill fill4 = new Fill();
                PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "FF0070C0" };
                BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill4.Append(foregroundColor2);
                patternFill4.Append(backgroundColor2);
                fill4.Append(patternFill4);
     
                // FillId = 4,YELLO
                Fill fill5 = new Fill();
                PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" };
                BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill5.Append(foregroundColor3);
                patternFill5.Append(backgroundColor3);
                fill5.Append(patternFill5);
     
                fills1.Append(fill1);
                fills1.Append(fill2);
                fills1.Append(fill3);
                fills1.Append(fill4);
                fills1.Append(fill5);
     
                Borders borders1 = new Borders() { Count = (UInt32Value)1U };
     
                Border border1 = new Border();
                LeftBorder leftBorder1 = new LeftBorder();
                RightBorder rightBorder1 = new RightBorder();
                TopBorder topBorder1 = new TopBorder();
                BottomBorder bottomBorder1 = new BottomBorder();
                DiagonalBorder diagonalBorder1 = new DiagonalBorder();
     
                border1.Append(leftBorder1);
                border1.Append(rightBorder1);
                border1.Append(topBorder1);
                border1.Append(bottomBorder1);
                border1.Append(diagonalBorder1);
     
                borders1.Append(border1);
     
                CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
     
                cellStyleFormats1.Append(cellFormat1);
     
                CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
                CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
                CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
                CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
     
                cellFormats1.Append(cellFormat2);
                cellFormats1.Append(cellFormat3);
                cellFormats1.Append(cellFormat4);
                cellFormats1.Append(cellFormat5);
     
                CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
                CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
     
                cellStyles1.Append(cellStyle1);
                DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
                TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
     
                StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
     
                StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
                stylesheetExtension1.AddNamespaceDeclaration("x14""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
                X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
     
                stylesheetExtension1.Append(slicerStyles1);
     
                stylesheetExtensionList1.Append(stylesheetExtension1);
     
                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;
            }
        }
    }
    

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 5:26 AM

All replies

  • Hi Elangovan,

    Thank you for posting.

    Please refer to this thread which is very similar to your problem:

    http://stackoverflow.com/questions/1012547/creating-excel-document-with-openxml-sdk-2-0

    The steps is that we can add the style and then apply the CellStyle index we want to format, just like:

    Cell cell1 = new Cell(){ CellReference = "C4", StyleIndex = (UInt32Value)1U };

    Hope this can give you some hint and just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    • Edited by Bruce Song Thursday, December 15, 2011 9:24 AM
    • Proposed as answer by Bruce Song Monday, December 26, 2011 6:22 AM
    Thursday, December 15, 2011 9:21 AM
  • Elangovan, how about the problem on your side? If you still show any concern on the problem, just feel free to let us know.
     
    Best Regards,
    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 20, 2011 9:49 AM
  • Hi Bruce Song,

    Thanks for the reply. i do want to color some cells with "RED", some cells with "Blue",some cells with "Yellow" . Please explain in detail.

     

    thanks,

    Elangovan P

    Tuesday, December 20, 2011 2:19 PM
  • Hi Elangovan P,

    Below is the console application which I have made and it can create an simple excel file which cells are set like this:

    using System;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
     
    namespace OpenXmlExcelGenerateStyle
    {
        class Program
        {
            static void Main(string[] args)
            {
                string sFile = "ExcelAndStyles.xlsx";
                if (File.Exists(sFile))
                {
                    File.Delete(sFile);
                }
                BuildWorkbook(sFile);
            }
     
            private static void BuildWorkbook(string sFile)
            {
                try
                {
                    using (SpreadsheetDocument xl = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook))
                    {
                        WorkbookPart wbp = xl.AddWorkbookPart();
                        WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                        Workbook wb = new Workbook();
                        FileVersion fv = new FileVersion();
                        fv.ApplicationName = "Microsoft Office Excel";
     
                        Worksheet ws = new Worksheet();
                        WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                        // add styles to sheet
                        wbsp.Stylesheet = CreateStylesheet();
                        wbsp.Stylesheet.Save();
                        
                        // generate rows
                        SheetData sd = CreateSheetData();
                        ws.Append(sd);
                        wsp.Worksheet = ws;
                        wsp.Worksheet.Save();
                        Sheets sheets = new Sheets();
                        Sheet sheet = new Sheet();
                        sheet.Name = "Sheet1";
                        sheet.SheetId = 1;
                        sheet.Id = wbp.GetIdOfPart(wsp);
                        sheets.Append(sheet);
                        wb.Append(fv);
                        wb.Append(sheets);
     
                        xl.WorkbookPart.Workbook = wb;
                        xl.WorkbookPart.Workbook.Save();
                        xl.Close();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    Console.ReadLine();
                }
            }
            private static SheetData CreateSheetData()
            {
                SheetData sheetData1 = new SheetData();
                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
     
                row1.Append(cell1);
     
                Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                Cell cell2 = new Cell() { CellReference = "B2", StyleIndex = (UInt32Value)2U };
     
                row2.Append(cell2);
     
                Row row3 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
                Cell cell3 = new Cell() { CellReference = "C3", StyleIndex = (UInt32Value)3U };
     
                row3.Append(cell3);
     
                sheetData1.Append(row1);
                sheetData1.Append(row2);
                sheetData1.Append(row3);
     
                return sheetData1;
            }
     
            private static Stylesheet CreateStylesheet()
            {
                Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                stylesheet1.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                stylesheet1.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
     
                Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
     
                Font font1 = new Font();
                FontSize fontSize1 = new FontSize() { Val = 11D };
                Color color1 = new Color() { Theme = (UInt32Value)1U };
                FontName fontName1 = new FontName() { Val = "Calibri" };
                FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
                FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
     
                font1.Append(fontSize1);
                font1.Append(color1);
                font1.Append(fontName1);
                font1.Append(fontFamilyNumbering1);
                font1.Append(fontScheme1);
     
                fonts1.Append(font1);
     
                Fills fills1 = new Fills() { Count = (UInt32Value)5U };
     
                // FillId = 0
                Fill fill1 = new Fill();
                PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
                fill1.Append(patternFill1);
     
                // FillId = 1
                Fill fill2 = new Fill();
                PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
                fill2.Append(patternFill2);
     
                // FillId = 2,RED
                Fill fill3 = new Fill();
                PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
                BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill3.Append(foregroundColor1);
                patternFill3.Append(backgroundColor1);
                fill3.Append(patternFill3);
     
                // FillId = 3,BLUE
                Fill fill4 = new Fill();
                PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "FF0070C0" };
                BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill4.Append(foregroundColor2);
                patternFill4.Append(backgroundColor2);
                fill4.Append(patternFill4);
     
                // FillId = 4,YELLO
                Fill fill5 = new Fill();
                PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
                ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" };
                BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
                patternFill5.Append(foregroundColor3);
                patternFill5.Append(backgroundColor3);
                fill5.Append(patternFill5);
     
                fills1.Append(fill1);
                fills1.Append(fill2);
                fills1.Append(fill3);
                fills1.Append(fill4);
                fills1.Append(fill5);
     
                Borders borders1 = new Borders() { Count = (UInt32Value)1U };
     
                Border border1 = new Border();
                LeftBorder leftBorder1 = new LeftBorder();
                RightBorder rightBorder1 = new RightBorder();
                TopBorder topBorder1 = new TopBorder();
                BottomBorder bottomBorder1 = new BottomBorder();
                DiagonalBorder diagonalBorder1 = new DiagonalBorder();
     
                border1.Append(leftBorder1);
                border1.Append(rightBorder1);
                border1.Append(topBorder1);
                border1.Append(bottomBorder1);
                border1.Append(diagonalBorder1);
     
                borders1.Append(border1);
     
                CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
     
                cellStyleFormats1.Append(cellFormat1);
     
                CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
                CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
                CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
                CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
                CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
     
                cellFormats1.Append(cellFormat2);
                cellFormats1.Append(cellFormat3);
                cellFormats1.Append(cellFormat4);
                cellFormats1.Append(cellFormat5);
     
                CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
                CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
     
                cellStyles1.Append(cellStyle1);
                DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
                TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
     
                StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
     
                StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
                stylesheetExtension1.AddNamespaceDeclaration("x14""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
                X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
     
                stylesheetExtension1.Append(slicerStyles1);
     
                stylesheetExtensionList1.Append(stylesheetExtension1);
     
                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;
            }
        }
    }
    

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 5:26 AM
  • Hi Bruce,

    Really thanks for you reply. Can you please please explain about "Indexed = (UInt32Value)64U ".

    thanks,

    Elangovan P

    Wednesday, December 21, 2011 5:56 AM
  • Hi Elangovan P,

    From the msdn article:

    http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.colortype.indexed.aspx

    it represents the attribte in schema: indexed.

    Hope this can help you.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 22, 2011 6:52 AM
  • I have a problem. I try this sample with Excel and OpenOffice and works.

    When I change the code to show data in cells in Excel works but in openoffice not show the style and not show the data. Why??

    The only change is:

    //exiting
    Cell
     cell1 = newCell() { CellReference = "A1", StyleIndex = (UInt32Value)1U }; 
    //new
    CellValue cv = new CellValue();
    cell1.DataType = CellValues.String;
    cv.Text = "lalalala";
    cell1.Append(cv);
    //

    Tuesday, July 31, 2012 3:40 PM
  • Hi Bruce,

    Thanks for your Thread . I need a help .I want to change A particular Column Width (Not Font) of a Excel Column.How can i Do it ..I can share my code to You if you have time to help me ..

    Friday, August 9, 2013 2:09 PM
  • Hi Bruce,

    I am totally new with Open XML, I need to implement something like below url.

    http://tinypic.com/r/2yuwnbl/8

    I have already exported the data ( which was in data table)using Open xml, to excel for which I have already defined few Styles/Formats. I have taken help of below link to implement the exporting part.

    http://justgeeks.blogspot.com/2012/03/writing-large-amounts-of-data-from.html

    Now the coloring of cell based on cell values is left.

    Can U please guide me on the same.

    I was trying to do something like this

    DoColourCell(excelWriter,table.Rows[numDataRows][i],null,table.Columns[i].ColumnName);

    protected void DoColourCell(OpenXmlWriter excelWriter, object value1, uint? styleIndex, string value2)
    {

    cell.DataType = CellValues.Number;
    cell.CellValue.Text = Convert.ToString(value1);
    string s = cell.CellValue.Text;

    if ((value2 ==”A” && s != null) || (value2 == “B” && s != null))
    {
    if (Convert.ToDouble(s) < 1.0)
    {
    …………
    //I really don't how should I fill colours to the
    cell based on cell value? what should I do in this part…………

    }
    }
    if (styleIndex.HasValue)
    {
    cell.StyleIndex = styleIndex.Value;
    }
    excelWriter.WriteElement(cell);
    }


    Supriya

    Wednesday, March 19, 2014 5:32 AM
  • Hi Bruce,

    I am new to Open Xml.

    I have used the same CreateStyleSheet() method to apply formatting on newly created excel file. I want to add DefaultTableStyle for the excel which I am assuming should work with following code. But Its not working and excel is creating without any style.

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

    Also, I want to change color for the header text in the excel. But not sure about the StyleIndex value from your code. How StyleIndex works?

    writer.WriteElement(new Cell { CellValue = new CellValue("Report"), CellReference = "A1", DataType = CellValues.String, StyleIndex = ?});

    Thanks in advance.

    Gaurav

    Saturday, April 26, 2014 5:55 PM