none
How to create font/color/border for couple of cells or range of cells in spreadsheet using openxml sdk openxmlwriter RRS feed

  • Question

  • I have an excel workbook(xlsm file) genmerated from temaplate file(xltm file),
    and will write 5000 record in the sheet using openxmlwriter.

    Requirement is to create font/color/border for couple of cells or range of cells .

    Can any one have the c#/vb.net code[short & ready code] to fulfill the above requirement.

    Sunday, October 14, 2012 9:00 AM

Answers

  • Hi sudipta,

    Thanks for posting in the MSDN Forum.

    Following Class will let every valid cell have border and background. I think you can modify it to fit your goal. Please omit all the variables method of name space “com.mksword.Net.OpenXml” it just a customize assembly.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using com.mksword.Net.OpenXmlTools;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using System.Windows.Forms;
    
    namespace ConsoleApplication7
    {
        public class ApplyCellsStyle : OpenXmlUtil
        {
            public void Action()
            {
                OpenFile(OXUFileFilter.EXCEL,
                    StartPath: Environment
                    .GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Work");
                using (SpreadsheetDocument SSD = SpreadsheetDocument
                    .Open(OriginalPath, true))
                {
                    WorkbookPart WBP = SSD.WorkbookPart;
                    Workbook WB = WBP.Workbook;
                    Sheet S = WB.Descendants<Sheet>()
                        .Where(Sh => Sh.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                    UInt32Value StyleId = null;
                    CreateStyle(WBP,ref StyleId);
                    if (WSP != null)
                    {
                        Worksheet WS = WSP.Worksheet;
                        SheetData SD = WS.Descendants<SheetData>().FirstOrDefault();
                        foreach(Row row in SD.Descendants<Row>())
                        {
                            foreach (Cell cell in row.Descendants<Cell>())
                            {
                                cell.StyleIndex = StyleId;
                            }
                        }
                    }
                    else
                    {
                        SetLog("WSP is null", OXULogType.FATAL);
                    }
                    WB.Save();
                }
            }
    
            private void CreateStyle(WorkbookPart WBP, ref UInt32Value StyleId)
            {
                try
                {
                    WorkbookStylesPart WSTLP = WBP
                        .GetPartsOfType<WorkbookStylesPart>().ToList()[0];
                    Stylesheet SS = WSTLP.Stylesheet;
                    Fills FS = SS.Fills;
                    UInt32Value FillId = null;
                    Fill F = new Fill();
                    PatternFill PF = new PatternFill()
                    {
                        PatternType = PatternValues.Solid
                    };
                    ForegroundColor FC = new ForegroundColor() { Rgb = "FF00FF00" };
                    PF.Append(FC);
                    F.Append(PF);
                    FS.Append(F);
                    FillId = UInt32Value.FromUInt32((uint)(FS.Descendants<Fill>()
                        .Count() - 1));
                    SetLog(FillId, OXULogType.INFO);
                    Borders BS = SS.Borders;
                    UInt32Value BorderId = null;
                    Border B = new Border();
                    LeftBorder LB = new LeftBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color LBC = new Color() { Auto = true };
                    LB.Append(LBC);
                    B.Append(LB);
                    RightBorder RB = new RightBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color RBC = new Color() { Auto = true };
                    RB.Append(RBC);
                    B.Append(RB);
                    TopBorder TB = new TopBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color TBC = new Color() { Auto = true };
                    TB.Append(TBC);
                    B.Append(TB);
                    BottomBorder BB = new BottomBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color BBC = new Color() { Auto = true };
                    BB.Append(BBC);
                    B.Append(BB);
                    BS.Append(B);
                    BorderId = UInt32Value.FromUInt32((uint)(BS
                        .Descendants<Border>().Count() - 1));
                    SetLog(BorderId, OXULogType.INFO);
                    CellFormats CFS = SS.CellFormats;
                    CellFormat CF = new CellFormat()
                    {
                        NumberFormatId = (UInt32Value)0U,
                        FontId = (UInt32Value)0U,
                        FillId = FillId,
                        BorderId = BorderId,
                        FormatId = (UInt32Value)0U,
                        ApplyFill = true,
                        ApplyBorder = true
                    };
                    CFS.Append(CF);
                    StyleId = UInt32Value.FromUInt32((uint)(CFS
                        .Descendants<CellFormat>().Count() - 1));
                    SetLog(StyleId.ToString(), OXULogType.INFO);
                }catch(Exception ex)
                {
                    SetLog(ex,OXULogType.FATAL);
                }
            }
        }
    }
    

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 15, 2012 5:24 AM
    Moderator

All replies

  • Hi sudipta,

    Thanks for posting in the MSDN Forum.

    Following Class will let every valid cell have border and background. I think you can modify it to fit your goal. Please omit all the variables method of name space “com.mksword.Net.OpenXml” it just a customize assembly.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using com.mksword.Net.OpenXmlTools;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using System.Windows.Forms;
    
    namespace ConsoleApplication7
    {
        public class ApplyCellsStyle : OpenXmlUtil
        {
            public void Action()
            {
                OpenFile(OXUFileFilter.EXCEL,
                    StartPath: Environment
                    .GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Work");
                using (SpreadsheetDocument SSD = SpreadsheetDocument
                    .Open(OriginalPath, true))
                {
                    WorkbookPart WBP = SSD.WorkbookPart;
                    Workbook WB = WBP.Workbook;
                    Sheet S = WB.Descendants<Sheet>()
                        .Where(Sh => Sh.Name == "Sheet1").FirstOrDefault();
                    WorksheetPart WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                    UInt32Value StyleId = null;
                    CreateStyle(WBP,ref StyleId);
                    if (WSP != null)
                    {
                        Worksheet WS = WSP.Worksheet;
                        SheetData SD = WS.Descendants<SheetData>().FirstOrDefault();
                        foreach(Row row in SD.Descendants<Row>())
                        {
                            foreach (Cell cell in row.Descendants<Cell>())
                            {
                                cell.StyleIndex = StyleId;
                            }
                        }
                    }
                    else
                    {
                        SetLog("WSP is null", OXULogType.FATAL);
                    }
                    WB.Save();
                }
            }
    
            private void CreateStyle(WorkbookPart WBP, ref UInt32Value StyleId)
            {
                try
                {
                    WorkbookStylesPart WSTLP = WBP
                        .GetPartsOfType<WorkbookStylesPart>().ToList()[0];
                    Stylesheet SS = WSTLP.Stylesheet;
                    Fills FS = SS.Fills;
                    UInt32Value FillId = null;
                    Fill F = new Fill();
                    PatternFill PF = new PatternFill()
                    {
                        PatternType = PatternValues.Solid
                    };
                    ForegroundColor FC = new ForegroundColor() { Rgb = "FF00FF00" };
                    PF.Append(FC);
                    F.Append(PF);
                    FS.Append(F);
                    FillId = UInt32Value.FromUInt32((uint)(FS.Descendants<Fill>()
                        .Count() - 1));
                    SetLog(FillId, OXULogType.INFO);
                    Borders BS = SS.Borders;
                    UInt32Value BorderId = null;
                    Border B = new Border();
                    LeftBorder LB = new LeftBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color LBC = new Color() { Auto = true };
                    LB.Append(LBC);
                    B.Append(LB);
                    RightBorder RB = new RightBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color RBC = new Color() { Auto = true };
                    RB.Append(RBC);
                    B.Append(RB);
                    TopBorder TB = new TopBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color TBC = new Color() { Auto = true };
                    TB.Append(TBC);
                    B.Append(TB);
                    BottomBorder BB = new BottomBorder()
                    {
                        Style = BorderStyleValues.Thin
                    };
                    Color BBC = new Color() { Auto = true };
                    BB.Append(BBC);
                    B.Append(BB);
                    BS.Append(B);
                    BorderId = UInt32Value.FromUInt32((uint)(BS
                        .Descendants<Border>().Count() - 1));
                    SetLog(BorderId, OXULogType.INFO);
                    CellFormats CFS = SS.CellFormats;
                    CellFormat CF = new CellFormat()
                    {
                        NumberFormatId = (UInt32Value)0U,
                        FontId = (UInt32Value)0U,
                        FillId = FillId,
                        BorderId = BorderId,
                        FormatId = (UInt32Value)0U,
                        ApplyFill = true,
                        ApplyBorder = true
                    };
                    CFS.Append(CF);
                    StyleId = UInt32Value.FromUInt32((uint)(CFS
                        .Descendants<CellFormat>().Count() - 1));
                    SetLog(StyleId.ToString(), OXULogType.INFO);
                }catch(Exception ex)
                {
                    SetLog(ex,OXULogType.FATAL);
                }
            }
        }
    }
    

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 15, 2012 5:24 AM
    Moderator
  • Hi Tom

    I don't see a definition for OU. What is assigned to OU, which is used three times at the end of CreateStyle. For example:

      NumberFormatId = (UInt32Value)0U


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, October 15, 2012 9:10 AM
    Moderator
  • Hi Cindy,

    It just Zero there. It's my habit. Only "0" works also.

    Cindy & sudipta,

    I will close it, please feel free to unmark it if you don't think so.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Monday, October 22, 2012 3:51 AM
    Moderator