none
in the spreadsheet i create a new font and want to set the FontId how to do it? RRS feed

  • Question

  •  first ,I create a new font(the third <x:font>)
    - <x:fonts count="3">
    - <x:font>
      <x:sz val="11" />
      <x:color theme="1" />
      <x:name val="宋体" />
      <x:family val="2" />
      <x:charset val="134" />
      <x:scheme val="minor" />
      </x:font>
    - <x:font>
      <x:sz val="9" />
      <x:name val="宋体" />
      <x:family val="2" />
      <x:charset val="134" />
      <x:scheme val="minor" />
      </x:font>
    - <x:font>
      <x:sz val="14" />
      <x:color rgb="FFFF0000" />
      <x:name val="黑体" />
      <x:family val="2" />
      <x:charset val="134" />
      <x:scheme val="minor" />
      </x:font>
      </x:fonts>

    secend I want to add fontId="2" in the blue area of below code

    - <x:cellXfs count="2">
    - <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
      <x:alignment vertical="center" />
      </x:xf>
    - <x:xf  fontId="2" applyAlignment="1"> //I want to add "fontId="2" in the blue area 
      <x:alignment horizontal="center" vertical="center" wrapText="1" />
      </x:xf>
      </x:cellXfs>

    third,I creat a method,but it does not work,what shoud i do?

    public static bool setstyle(SpreadsheetDocument spreadSheet, int fontsize, string color, string name, int family, int charset)  
            {  
                Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;  
                styleSheet.Fonts.AppendChild(  
                    new Font(  
                        new FontSize() { Val = fontsize },  
                        new Color() { Rgb = color },  
                new FontName() { Val = name },  
                new FontFamily() { Val = family },  
                new Charset() { Val = charset },  
                new FontScheme() { Val = FontSchemeValues.Minor }  
                ));  
                WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Sheet1");  
                Cell cell = GetCell(worksheetPart.Worksheet, "A", 1);  
                OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s""");  
                CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;  
                CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(Convert.ToInt32(cellStyleAttribute.Value));  
                CellFormat cf = new CellFormat(cellFormat.OuterXml);  
                cf.FontId = 2;  //it dose not work
                styleSheet.Fonts.Count++;  
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();  
                int a = (int)styleSheet.CellFormats.Count.Value - 1;  
                cell.SetAttribute(new OpenXmlAttribute("""s""", a.ToString()));  
                worksheetPart.Worksheet.Save();  
                return true;  
            } 
    Saturday, January 31, 2009 12:58 PM

Answers

  • there are 2 issues in the code:
    1) you will need to append the newly created cellformat 'cf' into before it could be referenced; and cf.FontId should not be hard-coded as '2' but the index of the last font you've appended above
    2) the cell's styleIndex should be set as the index of the last cellFormat

    I changed a few lines of your code and it works:
    ...
                CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(Convert.ToInt32(cellStyleAttribute.Value));
                CellFormat cf = new CellFormat(cellFormat.OuterXml);
                cf.FontId = styleSheet.Fonts.Count;  //it dose not work
                styleSheet.Fonts.Count++;
                cellFormats.AppendChild(cf);
                spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
                //cell.SetAttribute(new OpenXmlAttribute("", "s", "", styleSheet.CellFormats.Count.ToString()));
                cell.StyleIndex = styleSheet.CellFormats.Count; //BTW, you could set the attribute directly as a property here instead of setting the attribute
                styleSheet.CellFormats.Count++;
    ...
    • Marked as answer by 杜宏宇 Monday, February 9, 2009 3:23 PM
    Thursday, February 5, 2009 5:12 AM