在Excel中使用Open XML 2.0获取单元格背景色 [英] Getting cell-backgroundcolor in Excel with Open XML 2.0
问题描述
我试图在excel电子表格中获取单元格的backgroundcolor。我使用的是Open XML 2.0 SDK，我可以打开* .xlsx文件并获取单元格值。我获取背景颜色的代码如下：
public BackgroundColor GetCellBackColor（Cell theCell，SpreadsheetDocument文档）
{
BackgroundColor backGroundColor = null;
WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles（document）;
int cellStyleIndex =（int）theCell.StyleIndex.Value;
CellFormat cellFormat =（CellFormat）styles.Stylesheet.CellFormats.ChildElements [cellStyleIndex];
填充=（填充）styles.Stylesheet.Fills.ChildElements [（int）cellFormat.FillId.Value];
backGroundColor = fill.PatternFill.BackgroundColor;
return backGroundColor;
}
我的问题是， PatternFill.BackgroundColor 只返回一个自然数，我认为这是风格的id。我的问题是，代码行
DocumentFormat.OpenXml.Spreadsheet.Color c =（DocumentFormat.OpenXml.Spreadsheet.Color ）styles.Stylesheet.Colors.ChildElements [Int32.Parse（backGroundColor.InnerText）];
返回错误，因为 Stylesheet.Colors 是 null ... ...也许是因为我在excel中使用了内置颜色 - 而不是自定义颜色？
excel电子表格中单元格的填充图案是
，由两种颜色组成：背景颜色和前景色。
术语前景色在这里有点误导。它不是字体的
颜色，而是填充图案的前景颜色。
例如，如果您使用纯色填充单元格的背景
的单元格
的相关 PatternFill对象的 ForegroundColor 属性设置为选择纯色值，其中将 BackgroundColor 对象
设置为系统前景色。
PatternFill 对象的 PatternType 属性设置为 PatternValues.Solid 。
所以，要获取你的单元格背景的颜色值（实心填充），你必须分析
$相关的 PatternFill 对象的c $ c> ForegroundColor 属性。您必须
确定实例代表的颜色类型：
- 自动颜色和系统相关颜色
- 索引颜色
- ARGB颜色（alpha，red，green和blue）
- 基于主题的颜色。
- 应用于颜色的色调值。
有关不同颜色类型请参阅以下
链接< a>。
请注意， ForegroundColor 的含义 InnerText / code>和 BackgroundColor
类依赖于颜色类型。例如，在基于主题的颜色的情况下，将 InnerText 属性
设置为 ColorScheme 中的索引，
以下示例打印电子表格文档中所有单元格的所有背景颜色信息：
public static PatternFill GetCellPatternFill（Cell theCell，SpreadsheetDocument文档）
{
WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles（document）;
int cellStyleIndex;
if（theCell.StyleIndex == null）//我认为（从测试中）如果StyleIndex为null
{//这意味着使用单元格样式索引0.
cellStyleIndex = 0; //但是我没有在open xml
} //中找到它。
else
{
cellStyleIndex =（int）theCell.StyleIndex.Value;
}
CellFormat cellFormat =（CellFormat）styles.Stylesheet.CellFormats.ChildElements [cellStyleIndex];
填充=（Fill）styles.Stylesheet.Fills.ChildElements [（int）cellFormat.FillId.Value];
return fill.PatternFill;
}
private static void PrintColorType（SpreadsheetDocument sd，DocumentFormat.OpenXml.Spreadsheet.ColorType ct）
{
if（ct.Auto！= null）
{
Console.Out.WriteLine（System auto color）;
}
if（ct.Rgb！= null）
{
Console.Out.WriteLine（RGB value - > {0}，ct。 Rgb.Value）;
}
if（ct.Indexed！= null）
{
Console.Out.WriteLine（Indexed color - > {0}，ct。 Indexed.Value）;
// IndexedColors ic =（IndexedColors）styles.Stylesheet.Colors.IndexedColors.ChildElements [（int）bgc.Indexed.Value];
}
if（ct.Theme！= null）
{
Console.Out.WriteLine（Theme - > {0}，ct.Theme 。值）;
Color2Type c2t =（Color2Type）sd.WorkbookPart.ThemePart.Theme.ThemeElements.ColorScheme.ChildElements [（int）ct.Theme.Value];
Console.Out.WriteLine（RGB color model hex - > {0}，c2t.RgbColorModelHex.Val）;
}
if（ct.Tint！= null）
{
Console.Out.WriteLine（Tint value - > {0}，ct。 Tint.Value）;
}
}
static void ReadAllBackgroundColors（）
{
using（SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open（c：\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\bgcolor.xlsx，false））
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
foreach（WorksheetPart worksheetPart in workbookPart.WorksheetParts）
{
SheetData sheetData = worksheetPart.Worksheet.Elements< SheetData>（）。
foreach（sheetData.Elements< Row>（））中的行r
{
foreach（r.Elements< Cell>（）中的单元格c）
{
Console.Out.WriteLine（----------------）;
PatternFill pf = GetCellPatternFill（c，spreadsheetDocument）;
Console.Out.WriteLine（Pattern fill type - > {0}，pf.PatternType.Value）;
if（pf.PatternType == PatternValues.None）
{
Console.Out.WriteLine（No fill color specified）;
继续;
}
Console.Out.WriteLine（Summary foreground color：）;
PrintColorType（spreadsheetDocument，pf.ForegroundColor）;
Console.Out.WriteLine（Summary background color：）;
PrintColorType（spreadsheetDocument，pf.BackgroundColor）;
}
}
}
}
}
static void Main（string [] args）
{
ReadAllBackgroundColors（）;
}
I am trying to get the backgroundcolor of a cell in a excel-spreadsheet. I am using Open XML 2.0 SDK and I am able to open the *.xlsx-file and to get cell-values for example. My code for getting the Background-Color is the following:
public BackgroundColor GetCellBackColor(Cell theCell, SpreadsheetDocument document)
{
BackgroundColor backGroundColor = null;
WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles(document);
int cellStyleIndex = (int)theCell.StyleIndex.Value;
CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];
Fill fill = (Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];
backGroundColor = fill.PatternFill.BackgroundColor;
return backGroundColor;
}
My problem here is, that PatternFill.BackgroundColor returns just a natural number, I think it's the id of the style. My problem is, that the line of code
DocumentFormat.OpenXml.Spreadsheet.Color c = (DocumentFormat.OpenXml.Spreadsheet.Color)styles.Stylesheet.Colors.ChildElements[Int32.Parse(backGroundColor.InnerText)];
returns with an error, because Stylesheet.Colors is null... ...maybe it's because I used a "built in" color in excel - not an self-defined color?!
Any ideas how I could "calculate" the real color-number from the "backGroundColor-Value"?
The fill pattern of a cell in an excel spreadsheet is composed of two colors: The background color and the foreground color. The term foreground color is a little bit misleading here. It is not the color of the font but the foreground color of the pattern fill.
For example if you fill the background of a cell with a solid color
the ForegroundColor property of the releated PatternFill object of the cell
is set to the choosen solid color value where as The BackgroundColor object
is set to the system foreground color. The PatternType property of the
PatternFill object is set to PatternValues.Solid.
So, to get the color value of your cell background (solid fill), you have to analyze the
the ForegroundColor property of the releated PatternFill object. You have to
determine the "type of color" the instance represents:
- An automatic color and system dependent color
- An indexed color.
- An ARGB color (alpha, red, green and blue)
- A theme based color.
- A tint value applied to the color.
For more information about the different "color types" see the following link.
Please note that the meaning of the InnerText property of the ForegroundColor and BackgroundColor
class depends on the color type. For example in case of a theme based color the InnerText property
is set to the index into the ColorScheme collection.
The following example prints all background color information for all cells in a spreadsheet document:
public static PatternFill GetCellPatternFill(Cell theCell, SpreadsheetDocument document)
{
WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles(document);
int cellStyleIndex;
if (theCell.StyleIndex == null) // I think (from testing) if the StyleIndex is null
{ // then this means use cell style index 0.
cellStyleIndex = 0; // However I did not found it in the open xml
} // specification.
else
{
cellStyleIndex = (int)theCell.StyleIndex.Value;
}
CellFormat cellFormat = (CellFormat)styles.Stylesheet.CellFormats.ChildElements[cellStyleIndex];
Fill fill = (Fill)styles.Stylesheet.Fills.ChildElements[(int)cellFormat.FillId.Value];
return fill.PatternFill;
}
private static void PrintColorType(SpreadsheetDocument sd, DocumentFormat.OpenXml.Spreadsheet.ColorType ct)
{
if (ct.Auto != null)
{
Console.Out.WriteLine("System auto color");
}
if (ct.Rgb != null)
{
Console.Out.WriteLine("RGB value -> {0}", ct.Rgb.Value);
}
if (ct.Indexed != null)
{
Console.Out.WriteLine("Indexed color -> {0}", ct.Indexed.Value);
//IndexedColors ic = (IndexedColors)styles.Stylesheet.Colors.IndexedColors.ChildElements[(int)bgc.Indexed.Value];
}
if (ct.Theme != null)
{
Console.Out.WriteLine("Theme -> {0}", ct.Theme.Value);
Color2Type c2t = (Color2Type)sd.WorkbookPart.ThemePart.Theme.ThemeElements.ColorScheme.ChildElements[(int)ct.Theme.Value];
Console.Out.WriteLine("RGB color model hex -> {0}", c2t.RgbColorModelHex.Val);
}
if (ct.Tint != null)
{
Console.Out.WriteLine("Tint value -> {0}", ct.Tint.Value);
}
}
static void ReadAllBackgroundColors()
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("c:\\temp\\bgcolor.xlsx", false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
foreach(WorksheetPart worksheetPart in workbookPart.WorksheetParts)
{
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
foreach (Row r in sheetData.Elements<Row>())
{
foreach (Cell c in r.Elements<Cell>())
{
Console.Out.WriteLine("----------------");
PatternFill pf = GetCellPatternFill(c, spreadsheetDocument);
Console.Out.WriteLine("Pattern fill type -> {0}", pf.PatternType.Value);
if (pf.PatternType == PatternValues.None)
{
Console.Out.WriteLine("No fill color specified");
continue;
}
Console.Out.WriteLine("Summary foreground color:");
PrintColorType(spreadsheetDocument, pf.ForegroundColor);
Console.Out.WriteLine("Summary background color:");
PrintColorType(spreadsheetDocument, pf.BackgroundColor);
}
}
}
}
}
static void Main(string[] args)
{
ReadAllBackgroundColors();
}
这篇关于在Excel中使用Open XML 2.0获取单元格背景色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
