locked
Apply color to cells RRS feed

  • Question

  • hi,

    how to color the particular cells in existing excel sheet ( sheet1 or sheet2) using openXML in C#.

    thanks,

    Elangovan P

    Wednesday, November 2, 2011 1:39 PM

Answers

  • Hi Elangovan,

    Thanks for posting in the MSDN Forum.

    I hope this snippet can solve your issue.

    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 OpenXmlSpreadsheetSetColor
    {
        public partial class Form1 : Form
        {
            private string FilePath { set; get; }
            private System.Drawing.Color FontColor { set; get; }
            private System.Drawing.Color BackGroundColor { set; get; }
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button2_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;
                }
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                if (FilePath != null)
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(FilePath, true))
                    {
                        WorkbookPart objWorkbookPart = doc.WorkbookPart;
                        WorkbookStylesPart objWorkbookStylePart = objWorkbookPart.WorkbookStylesPart;
                        Sheet TargetSheet = objWorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == comboBox1.Text).FirstOrDefault();
                        WorksheetPart TargetWorkSheet = (WorksheetPart)objWorkbookPart.GetPartById(TargetSheet.Id);
                        Cell TargetCell = TargetWorkSheet.Worksheet.Descendants<Cell>().Where(s => s.CellReference == textBox1.Text).FirstOrDefault();
                        if (TargetCell != null)
                        {
                            
                        }
                        else
                        {
                            Row newRow = new Row() { RowIndex = UInt32Value.FromUInt32(UInt32.Parse(textBox2.Text))};
    
                            TargetCell = new Cell() { CellReference = textBox1.Text+textBox2.Text };
                            CellValue newValue = new CellValue();
                            newValue.Text = "1";
    
                            TargetCell.Append(newValue);
                            newRow.Append(TargetCell);
                            SheetData data = TargetWorkSheet.Worksheet.Descendants<SheetData>().FirstOrDefault();
                            data.Append(newRow);
    
                            TargetWorkSheet.Worksheet.Save();
                        }
                        Stylesheet objStylesheet = objWorkbookStylePart.Stylesheet;
    
                        CellFormat newCF = new CellFormat();
                        if (checkBox1.Checked)
                        {
                            List<DocumentFormat.OpenXml.Spreadsheet.Font> Fonts = objStylesheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>().ToList();
                            bool fcf = false;
                            uint fi = 0U;
                            foreach (DocumentFormat.OpenXml.Spreadsheet.Font font in Fonts)
                            {
                                if (checkBox1.Checked && font.Color != null && font.Color.Rgb != null && font.Color.Rgb.Value.ToUpper().Equals(string.Format("{0:x2}{1:x2}{2:x2}{3:x2}", FontColor.A, FontColor.R, FontColor.G, FontColor.B).ToUpper()))
                                {
                                    fcf = true;
                                    break;
                                }
                                fi++;
                            }
                            if (fcf)
                            {
                                newCF.FontId = UInt32Value.FromUInt32(fi);
                            }
                            else
                            {
                                DocumentFormat.OpenXml.Spreadsheet.Font newFont = new DocumentFormat.OpenXml.Spreadsheet.Font() { };
                                DocumentFormat.OpenXml.Spreadsheet.Color newColor = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = string.Format("{0:x2}{1:x2}{2:x2}{3:x2}", FontColor.A, FontColor.R, FontColor.G, FontColor.B).ToUpper() };
                                newFont.Color = newColor;
                                objWorkbookStylePart.Stylesheet.Fonts.Append(newFont);
                                objWorkbookStylePart.Stylesheet.Save();
                                List<DocumentFormat.OpenXml.Spreadsheet.Font> Fonts1 = objStylesheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Font>().ToList();
                                uint fi1 = 0U;
                                foreach (DocumentFormat.OpenXml.Spreadsheet.Font font in Fonts)
                                {
                                    if (checkBox1.Checked && font.Color != null && font.Color.Rgb != null && font.Color.Rgb.Value.ToUpper().Equals(string.Format("{0:x2}{1:x2}{2:x2}{3:x2}", FontColor.A, FontColor.R, FontColor.G, FontColor.B).ToUpper()))
                                    {
                                        break;
                                    }
                                    fi1++;
                                }
                                newCF.FontId = UInt32Value.FromUInt32(fi1);
                            }
                        }
                        else
                        {
                            newCF.FontId = (UInt32Value)0U;
                        }
                        if (checkBox2.Checked)
                        {
                            List<Fill> Fills = objStylesheet.Descendants<Fill>().ToList();
                            bool bFF = false;
                            UInt32 bsf = 0U;
                            foreach (Fill fill in Fills)
                            {
                                if (checkBox2.Checked)
                                {
                                    BackgroundColor bc = fill.Descendants<BackgroundColor>().FirstOrDefault();
                                    if (bc != null && bc.Rgb != null && bc.Rgb.Value.ToUpper().Equals(string.Format("{0:x2}{1:x2}{2:x2}{3:x2}", BackGroundColor.A, BackGroundColor.R, BackGroundColor.G, BackGroundColor.B).ToUpper()))
                                    {
                                        bFF = true;
                                        break;
                                    }
                                }
                                bsf++;
                            }
                            if (bFF)
                            {
                                newCF.FillId = bsf;
                            }
                            else
                            {
                                Fill newFill = new Fill();
                                PatternFill newPatternFill = new PatternFill() { PatternType = PatternValues.Solid };
                                ForegroundColor newFore = new ForegroundColor() { Rgb = string.Format("{0:x2}{1:x2}{2:x2}{3:x2}", BackGroundColor.A, BackGroundColor.R, BackGroundColor.G, BackGroundColor.B).ToUpper() };
                                newPatternFill.Append(newFore);
                                newFill.Append(newPatternFill);
                                objWorkbookStylePart.Stylesheet.Fills.Append(newFill);
                                objWorkbookStylePart.Stylesheet.Save();
                                List<Fill> Fills1 = objStylesheet.Descendants<Fill>().ToList();
                                UInt32 bsf1 = 0U;
                                foreach (Fill fill1 in Fills1)
                                {
                                    if (checkBox2.Checked)
                                    {
                                        ForegroundColor bc = fill1.Descendants<ForegroundColor>().FirstOrDefault();
                                        if (bc != null && bc.Rgb.Value.ToUpper().Equals(string.Format("{0:x2}{1:x2}{2:x2}{3:x2}", BackGroundColor.A, BackGroundColor.R, BackGroundColor.G, BackGroundColor.B).ToUpper()))
                                        {
                                            break;
                                        }
                                    }
                                    bsf1++;
                                }
                                newCF.FillId = bsf1;
                            }
                        }
                        else
                        {
                            newCF.FillId = (UInt32Value)0U;
                        }
                        if (checkBox1.Checked || checkBox2.Checked)
                        {
                            objStylesheet.CellFormats.Append(newCF);
                            objStylesheet.Save();
                            List<CellFormat> lcfl = objStylesheet.CellFormats.Descendants<CellFormat>().ToList();
                            UInt32 cfi = 0U;
                            foreach (CellFormat cfii in lcfl)
                            {
                                if (cfii.FillId == newCF.FillId && cfii.FontId == newCF.FontId)
                                {
                                    break;
                                }
                                cfi++;
                            }
                            TargetCell.StyleIndex = cfi;
                            TargetWorkSheet.Worksheet.Save();
                        }
                    }
                }
            }
    
            private void checkBox1_CheckedChanged(object sender, EventArgs e)
            {
                CheckBox checkbox = (CheckBox)sender;
                if (checkbox != null)
                {
                    CheckBoxIssue(checkbox);
                }
            }
    
            private void CheckBoxIssue(CheckBox checkBox)
            {
                if (checkBox.Checked)
                {
                    colorDialog1.ShowDialog();
                    checkBox.ForeColor = colorDialog1.Color;
                    SetColor(colorDialog1.Color, checkBox);
                }
                else
                {
                    checkBox.ForeColor = SystemColors.ControlText;
                    SetColor(SystemColors.ControlText, checkBox);
                }
            }
    
            private void SetColor(System.Drawing.Color color,CheckBox checkbox)
            {
                switch (checkbox.Text)
                {
                    case "Font":
                        FontColor = color;
                        break;
                    case "BackGround":
                        BackGroundColor = color;
                        break;
                }
            }
    
            private void checkBox2_CheckedChanged(object sender, EventArgs e)
            {
                CheckBox checkbox = (CheckBox)sender;
                if (checkbox != null)
                {
                    CheckBoxIssue(checkbox);
                }
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                FilePath = null;
                FontColor = SystemColors.ControlText;
                BackGroundColor = SystemColors.ControlText;
            }
        }
    }
    
    

     


    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.

    • Marked as answer by 许阳(无锡) Wednesday, November 9, 2011 7:01 AM
    Monday, November 7, 2011 1:55 AM