[Import from Excel] How to get the cell background color?

3 Answers 1110 Views
Spreadsheet
WEI TZE
Top achievements
Rank 2
Iron
Iron
WEI TZE asked on 14 Nov 2022, 06:17 AM | edited on 17 Nov 2022, 12:07 AM

[WinForm]

***Updated with Excel spreadsheet (Template.zip) and a simple Project (ReadExcelMap.zip) attachment.

Reference: https://docs.telerik.com/devtools/winforms/knowledge-base/import-data-from-excel

Question: How to get the Cell background color while importing from Excel spreadsheet?

E.g. The spreadsheet below with value and background color.

 

Import Spreadsheet:

XlsxFormatProvider formatProvider = new XlsxFormatProvider();
            Workbook workbook = formatProvider.Import(File.ReadAllBytes(@"C:\temp\Template.xlsx"));

            var worksheet = workbook.Sheets[0] as Worksheet;
            var table = new DataTable();


            CellSelection selection = worksheet.Cells[3, 25];
            ICellValue value = selection.GetValue().Value;
            CellValueFormat format = selection.GetFormat().Value;
            CellValueFormatResult formatResult = format.GetFormatResult(value);
            string result = formatResult.InfosText;

 

I could not find the relevant functions. Attempted the following but not working:
            IFill f = selection.GetFill().Value;
            ThemableColor c = selection.GetForeColor().Value;

 

3 Answers, 1 is accepted

Sort by
1
Accepted
Maria
Telerik team
answered on 17 Nov 2022, 12:04 PM

Hi Wei Tze,

Thank you for the project file and provided screenshots.

I saw that you are using an older version of our assembly references. The conditional formatting was introduced in R1 2022. I suggest you upgrade the product version so you can have access to this. 

I hope this is useful, if you have any further questions I will be glad to help.

Regards,
Maria
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

1
Maria
Telerik team
answered on 16 Nov 2022, 01:29 PM

Hello Wei Tze,

Thank you for the screenshot and the attached files you sent.

I looked back at your project and in this case, you are using conditional formatting to style the cells. To get the color you need to evaluate the rule and check the result. Here is a snippet:

IEnumerable<ConditionalFormattingRange> formattingRanges = worksheet.Cells[1, 10].GetConditionalFormattings();

var range = formattingRanges.FirstOrDefault();
var formating = range.Formattings.FirstOrDefault();
var rule = formating.Rule as ColorScaleRule;
 
var value = worksheet.Cells[1, 10].GetValue().Value as NumberCellValue;
 
if (value != null)
{
     var result = formating.Resolve(worksheet.Cells[1, 10]);

    if (result == 0)
    {
        Console.WriteLine(rule.ValueContext.MinimumColor.LocalValue);
    }
    else
    {
        Console.WriteLine(rule.ValueContext.MaximumColor.LocalValue);
    } 
}

I hope this information is useful. Let me know if you need further assistance.

Regards,
Maria
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

WEI TZE
Top achievements
Rank 2
Iron
Iron
commented on 17 Nov 2022, 12:06 AM

Hi Maria,

May I know what's Assembly Reference for Class <ConditionalFormattingRange> (WinForm)?

Reference: https://docs.telerik.com/devtools/document-processing/libraries/radspreadstreamprocessing/getting-started#assembly-references

I have added the state references.

Project References:

 


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using Telerik.Windows.Documents.Spreadsheet.Formatting.FormatStrings;
using Telerik.Windows.Documents.Spreadsheet.Model;

0
Maria
Telerik team
answered on 14 Nov 2022, 02:19 PM

Hello Wei Tze,

To get the value of the cell's background color, you need to use the GetFill() method and cast its value to PaternFill. In this way, you can have access to the BackgroundColor of the cell. I am sending you a code snippet below:

var fill = selection.GetFill().Value as PatternFill;
ThemableColor backgroundColor = fill.BackgroundColor;

I am sending you a link to our documentation as well, where you can find more information: Get, Set, and Clear Cell Properties.

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,
Maria
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

WEI TZE
Top achievements
Rank 2
Iron
Iron
commented on 14 Nov 2022, 03:01 PM | edited

Hi Maria,

It doesn't seem working. Regardless of the actual background color, the value returned is always #FF000000 (as shown in screen shot below).

I have attached the Excel spreadsheet (Template.zip) and a simple Project (ReadExcelMap.zip) for your reference.

Spreadsheet:


 private void Form1_Load(object sender, EventArgs e)
        {
            XlsxFormatProvider formatProvider = new XlsxFormatProvider();
            Workbook workbook = formatProvider.Import(File.ReadAllBytes(@"C:\temp\Template.xlsx"));

            var worksheet = workbook.Sheets[0] as Worksheet;
            var table = new DataTable();


            CellSelection selection = worksheet.Cells[2, 30];
            ICellValue value = selection.GetValue().Value;
            CellValueFormat format = selection.GetFormat().Value;
            CellValueFormatResult formatResult = format.GetFormatResult(value);
            string result = formatResult.InfosText;

            var fill = selection.GetFill().Value as PatternFill;
            ThemableColor backgroundColor = fill.BackgroundColor;
}

 

Tags
Spreadsheet
Asked by
WEI TZE
Top achievements
Rank 2
Iron
Iron
Answers by
Maria
Telerik team
Share this question
or