CODEWITHSUNDEEP
c#cellbackground-color
user565447
user565447

Reputation: 999

C# Flexcel. Change the colour of background cell. Excell

I am using Flexcel library and want to change the colour of the cell in the table (Excel document).

How can I do it? I can't found necessary API. Can I do it with Flexcell?

enter image description here

Upvotes: 0

Views: 5144

Answers (4)

Marius Rusu
Marius Rusu

Reputation: 125

I know it is a old topic but still relevant for many. One other helpful thing is to create a xls file, open in Excel, add the formatting you want, etc, save and close and then load it in APIMate.exe (which comes with FlexCel) and you will get FlexCel code that generates the file from scratch. For example I coloured two cells and wrote in them "red" and "yellow" and this is what was produced (I pasted the relevant part only):

//Set the cell values
TFlxFormat fmt;
fmt = xls.GetCellVisibleFormatDef(1, 1);
fmt.FillPattern.Pattern = TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor = TExcelColor.FromArgb(0xFF, 0x00, 0x00);
fmt.FillPattern.BgColor = TExcelColor.Automatic;
xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
xls.SetCellValue(1, 1, "red");

fmt = xls.GetCellVisibleFormatDef(2, 1);
fmt.FillPattern.Pattern = TFlxPatternStyle.Solid;
fmt.FillPattern.FgColor = TExcelColor.FromArgb(0xFF, 0xFF, 0x00);
fmt.FillPattern.BgColor = TExcelColor.Automatic;
xls.SetCellFormat(2, 1, xls.AddFormat(fmt));
xls.SetCellValue(2, 1, "yellow");

Upvotes: 0

carey walker
carey walker

Reputation: 183

I had this same issue where I was trying to set the background colour of a cell. I never managed to set it directly but what I did manage to do was set the FillPattern of the cell which in effect set the background colour.

Here I have a private method that sets up the 4 different background colours I plan on using. Note that when you add in styles you add them to the Excel file directly. This is why I am passing in the excelFile to this method and then getting the excelFile back. You have to add the styles to the excelFile before you can use the styles.

        private XlsFile SetUpBackgroundColours(XlsFile excelFile)
    {
        var patternStyle = TFlxPatternStyle.Solid;

        TFlxFormat format = excelFile.GetDefaultFormat;
        format.FillPattern = new TFlxFillPattern { Pattern = patternStyle, FgColor = Color.Yellow }; //1
        format.VAlignment = TVFlxAlignment.top;
        format.HAlignment = THFlxAlignment.left;
        excelFile.AddFormat(format);

        format.FillPattern = new TFlxFillPattern { Pattern = patternStyle, FgColor = Color.LightBlue }; //2
        format.VAlignment = TVFlxAlignment.top;
        format.HAlignment = THFlxAlignment.left;
        excelFile.AddFormat(format);

        format.FillPattern = new TFlxFillPattern { Pattern = patternStyle, FgColor = Color.LightGray }; //3
        format.VAlignment = TVFlxAlignment.top;
        format.HAlignment = THFlxAlignment.left;
        excelFile.AddFormat(format);

        format.FillPattern = new TFlxFillPattern { Pattern = patternStyle, FgColor = Color.White }; //4
        format.VAlignment = TVFlxAlignment.top;
        format.HAlignment = THFlxAlignment.left;
        excelFile.AddFormat(format);

        return excelFile;
    }

This will set up 4 new styles that I can then reference using the index of the order they were added in. The index with Flexcel starts with 1.

So then when you are adding values in your cells using the SetCellValue method, you can provide the index of the style you have added to change the background color. The following shows as example of how to do this:

excelFile.SetCellValue(rowNumber, columnNumber, "Some text for the cell", 1)

Where you see the 1 as the last value for the method, this 1 refers to the first style that I added to the Excel file using the SetUpBackgroundColours() method above.

If I wanted my cell to have a lighgray colour I would use 3 instead of 1 as follows:

excelFile.SetCellValue(rowNumber, columnNumber, "Some text for the cell", 3)

Some additional code that shows the call to SetUpBackgroundColours:

var excelFile = new XlsFile(true);
excelFile.NewFile(someList.Count() + 1, TExcelFileFormat.v2007);
var sheetIndex = 0;
excelFile = SetUpBackgroundColours(excelFile);

excelFile.SetCellValue(1, 1, "Some text for the cell A1", 1) //cell colour will be yellow
excelFile.SetCellValue(2, 2, "Some text for the cell B1", 2) //cell colour will be lightblue

Upvotes: 5

Tabish Matin
Tabish Matin

Reputation: 85

You can use TFlxFormat class for applying color. Using this class You can apply any format you want.

Upvotes: 0

Andrei Neagu
Andrei Neagu

Reputation: 896

I haven't used that API, but if you can't find a way, this is how you can do it with C#:

    //http://www.mvps.org/dmcritchie/excel/colors.htm
    private void setCellColor(Microsoft.Office.Interop.Excel.Range cell, int index)
    {
        cell.Interior.ColorIndex = index;
    }

Upvotes: 2

Related Questions