none
Update to cell's properties RRS feed

  • Question

  • Hi,
    I want to write to Excel cell with value, by also putting exact values to such properties

    font.Color.Rgb.Value
    font.Color.Theme.Value

    is there an example to this?

    Many Thanks & Best Regards, Hua Min

    Thursday, December 8, 2016 8:57 AM

Answers

  • Hi,

    You could create a new workbook and test code below. It inserts number 0 into cell A1 with red font.

     private static void insertCellWithFontStyle(string filePath,string sheetName)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filePath, true))
                {
                    WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                    Sheets sheets = workbookPart.Workbook.Sheets;
                    Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(theSheet.Id);
                    ChangeWorksheetPart1(worksheetPart);
                    WorkbookStylesPart workbookStylesPart1 = workbookPart.WorkbookStylesPart;
                    ChangeWorkbookStylesPart1(workbookStylesPart1);
                }
            }
    
            private static void ChangeWorkbookStylesPart1(WorkbookStylesPart workbookStylesPart1)
            {
                Stylesheet stylesheet1 = workbookStylesPart1.Stylesheet;
    
                Fonts fonts1 = stylesheet1.GetFirstChild<Fonts>();
                CellFormats cellFormats1 = stylesheet1.GetFirstChild<CellFormats>();
                fonts1.Count = (UInt32Value)2U;
    
                Font font1 = new Font();
                FontSize fontSize1 = new FontSize() { Val = 11D };
                Color color1 = new Color() { Rgb = "FFFF0000" };
                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);
                cellFormats1.Count = (UInt32Value)2U;
    
                CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
                cellFormats1.Append(cellFormat1);
            }
    
            private static void ChangeWorksheetPart1(WorksheetPart worksheetPart1)
            {
                Worksheet worksheet1 = worksheetPart1.Worksheet;
                SheetData sheetData1 = worksheet1.GetFirstChild<SheetData>();
                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };
                Cell cell1 = new Cell() { CellReference = "A1", StyleIndex = (UInt32Value)1U };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = "0";
                cell1.Append(cellValue1);
                row1.Append(cell1);
                sheetData1.Append(row1);
            }
    

    And you could see the links below.

    Stylizing your Excel worksheets with Open XML 2.0

    Use openxml to create the new cellformat and apply it to cell. Use openxml to modify the cellformat of cell.

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 9, 2016 5:07 AM
    Moderator