Rank 2
Iron
Iron
[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
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/.
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/.
Rank 2
Iron
Iron
Hi Maria,
May I know what's Assembly Reference for Class <ConditionalFormattingRange> (WinForm)?
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;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.
Rank 2
Iron
Iron
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;
}