none
change Font size RRS feed

  • Question

  • hi,

    how to change the font size in particular cell (based on validation) in an existing excel sheet (sheet2) using openXML in C#.

     

    thanks,

    Elangovan P

    Wednesday, November 2, 2011 1:42 PM

Answers

  • Hi Elangovan,

    Thanks for posting in the MSDN Forum.

    Would you please try this snippet:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    
    namespace OpenXmlSpreadsheetFontsize
    {
        public partial class Form1 : Form
        {
            private string FilePath { set; get; }
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                if (FilePath != null)
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(FilePath, true))
                    {
                        WorkbookPart objWorkbookPart = doc.WorkbookPart;
                        WorkbookStylesPart objWorkbookStylePart = objWorkbookPart.WorkbookStylesPart;
                        Stylesheet objStylesheet = objWorkbookStylePart.Stylesheet;
                        List<DocumentFormat.OpenXml.Spreadsheet.Font> Fonts = objStylesheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>().ToList();
                        UInt32 FontIndex = 0U;
                        Workbook objWorkbook = objWorkbookPart.Workbook;
                        Sheet objSheet = objWorkbook.Descendants<Sheet>().Where(s => s.Name == comboBox1.Text).FirstOrDefault();
                        bool hasSize = false;
                        WorksheetPart objWorksheetPart = (WorksheetPart)objWorkbookPart.GetPartById(objSheet.Id);
                        foreach (DocumentFormat.OpenXml.Spreadsheet.Font eFont in Fonts)
                        {
                            if (eFont.FontSize != null && eFont.FontSize.Val == Double.Parse(textBox1.Text))
                            {
                                hasSize = true;
                                break;
                            }
                            FontIndex++;
                        }
                        if (!hasSize)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Font newFont = new DocumentFormat.OpenXml.Spreadsheet.Font();
                            FontSize newFontSize = new FontSize() { Val = Double.Parse(textBox1.Text) };
                            DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U };
                            FontName fontName1 = new FontName() { Val = "Times New Roman" };
                            FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 1 };
    
                            newFont.Append(newFontSize);
                            newFont.Append(color1);
                            newFont.Append(fontName1);
                            newFont.Append(fontFamilyNumbering1);
                            objStylesheet.Fonts.Append(newFont);
                            objStylesheet.Save();
                            Fonts = objStylesheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>().ToList();
                            FontIndex = 0U;
                            foreach (DocumentFormat.OpenXml.Spreadsheet.Font eFont in Fonts)
                            {
                                if (eFont.FontSize != null && eFont.FontSize.Val == Double.Parse(textBox1.Text))
                                {
                                    break;
                                }
                                FontIndex++;
                            }
                        }
                        CellFormat newFormat = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = FontIndex, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
                        objStylesheet.CellFormats.Append(newFormat);
                        objStylesheet.Save();
                        List<CellFormat>cCellFormat = objStylesheet.Descendants<CellFormat>().ToList();
                        UInt32 CellFormatIndex = 0U;
                        foreach (CellFormat eCellFormat in cCellFormat)
                        {
                            if (eCellFormat.Equals(newFormat))
                                break;
                            CellFormatIndex++;
                        }
                        CellFormatIndex--;
                        Column newColumn = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)16384U, Width = DoubleValue.FromDouble(Double.Parse(textBox1.Text)), Style =UInt32Value.FromUInt32(CellFormatIndex)};
                        Columns newColumns = new Columns();
                        newColumns.Append(newColumn);
                        objWorksheetPart.Worksheet.Append(newColumns);
                        objWorkbook.Save();
                    }
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                openFileDialog1.Filter = "Excel Document xlsx| *.xlsx";
                openFileDialog1.ShowDialog();
                FilePath = openFileDialog1.FileName;
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(FilePath, false))
                {
                    WorkbookPart objWorkbookPart = doc.WorkbookPart;
                    List<Sheet> objSheets = objWorkbookPart.Workbook.Descendants<Sheet>().ToList();
                    comboBox1.Items.Clear();
                    foreach (Sheet sheet in objSheets)
                    {
                        comboBox1.Items.Add(sheet.Name);
                    }
                    comboBox1.SelectedIndex = 0;
                }
            }
        }
    
    

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, November 7, 2011 10:07 AM
    Moderator