获取细胞的backgroundColor在Excel与Open XML 2.0 [英] Getting cell-backgroundcolor in Excel with Open XML 2.0

问题描述

我试图让一个excel-S preadsheet细胞的backgroundColor。我使用的Open XML 2.0 SDK和我能够打开*的.xlsx文件，并得到细胞值为例。我的$ C $下获得背景颜色如下：

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;
    }

我这里的问题是，该 PatternFill.BackgroundColor 返回只是一个自然数，我认为这是风格的ID。我的问题是，那code行

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)];

会返回一个错误，因为 Stylesheet.Colors 是空 ......也许是因为我使用内置的颜色在Excel - ？！不是一个自定义的颜色

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?!

任何的想法如何，我可以计算，从BACKGROUNDCOLOR价值？

Any ideas how I could "calculate" the real color-number from the "backGroundColor-Value"?

推荐答案

细胞在一个Excel US preadsheet填充图案 由两个颜色：背景颜色和前景颜色。 术语前景颜色是有点误导这里。它不是 颜色的字体，但图案填充前景色的。

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.

例如，如果您填写的单元格的背景纯色 电池的相关型号 PatternFill 对象的 ForegroundColor 属性 设置为已选定的纯色值，其中作为 BackgroundColor中对象 被设置为系统前景色。的的 PatternType 属性 PatternFill 对象设置为 PatternValues​​.Solid 。

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.

所以，要想让你的单元格背景（实心填充）的颜色值，你必须分析 在相关型号 PatternFill 对象的 ForegroundColor 属性。你必须 确定了型色再presents实例：

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:

1. 自动色彩与系统相关的颜色
2. 在一个索引颜色。
3. 在一个ARGB颜色（alpha，红色，绿色和蓝色）
4. 一个基于主题颜色。
5. 适用于色色调值。

有关不同的颜色类型的详细信息，请参阅下面的 <一href="http://msdn.microsoft.com/en-us/library/documentformat.openxml.s$p$padsheet.foregroundcolor.aspx">link.

For more information about the different "color types" see the following link.

请注意，在的InnerText 属性的含义 ForegroundColor 和 BackgroundColor中 类依赖于颜色类型。例如，在情况下，基于主题颜色的InnerText 属性 设置为索引到对ColorScheme 集合。

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.

下面的例子打印所有背景颜色信息中所有单元为preadsheet文件：

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();
}

这篇关于获取细胞的backgroundColor在Excel与Open XML 2.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！