使用纯色填充单元格，使用保留其他现有工作表格式的 OpenXML [英] Filling a cell with a solid color, using OpenXML that preserves otherwise existing sheet format

问题描述

以下采用现有工作表 sSheet 的格式，并在 A19 处创建红色字体(以及一些时间戳内容):

The following takes the format of existing sheet sSheet and creates a red font (along with some timestamp content) at A19:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(sPath, true)) {
 IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sSheet);
 WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.FirstOrDefault().Id);


 Worksheet worksheet = worksheetPart.Worksheet;
 WorkbookStylesPart styles = document.WorkbookPart.WorkbookStylesPart;
 Stylesheet stylesheet = styles.Stylesheet;
 CellFormats cellformats = stylesheet.CellFormats;
 Fonts fonts = stylesheet.Fonts;

 UInt32 fontIndex = fonts.Count;
 UInt32 formatIndex = cellformats.Count;

 Cell cell = GetCell(worksheet, "A", 19);
 cell.CellValue = new CellValue(DateTime.Now.ToLongTimeString());
 cell.DataType = new EnumValue<CellValues>(CellValues.String);

 CellFormat f = (CellFormat)cellformats.ElementAt((int)cell.StyleIndex.Value);

 var font = (Font)fonts.ElementAt((int)f.FontId.Value);
 var newfont = (Font)font.Clone();
 newfont.Color = new Color() { Rgb = new HexBinaryValue("ff0000") };
 fonts.Append(newfont);

 CellFormat newformat = (CellFormat)f.Clone();
 newformat.FontId = fontIndex;
 cellformats.Append(newformat);
 stylesheet.Save();
 cell.StyleIndex = formatIndex;



 worksheetPart.Worksheet.Save();
 document.WorkbookPart.Workbook.Save();
 document.Close();
}

但我不能让它创建一个实体填充.我尝试添加Fills fills = stylesheet.Fills; 在Fonts fonts = stylesheet.Fonts; 下方，以及在

But I can't make it create a solid fill. I tried adding Fills fills = stylesheet.Fills; underneath Fonts fonts = stylesheet.Fonts;, as well as adding some wording that uses similar Fill language underneath the

var font = (Font)fonts.ElementAt((int)f.FontId.Value);
var newfont = (Font)font.Clone();
newfont.Color = new Color() { Rgb = new HexBinaryValue("ff0000") };
fonts.Append(newfont);

...以及它的许多变体，但没有编译.我不知道如何添加纯色填充.任何帮助深表感谢.谢谢.

...as well as a lot of variations to it, but nothing compiled. I cannot figure out how to add a solid color fill. Any help is much appreciated. Thank you.

推荐答案

我通过创建两个基本空"的工作簿获得了以下代码，一个单元格具有背景色，另一个没有背景色.然后我使用 Open XML SDK Productivity Tool 打开后者，并用它与具有背景填充的工作簿进行比较.这产生了将一个转换为另一个的代码.

I obtained the code that follows by creating two basically "empty" workbooks, one with a cell having the background color, the other with no background color. I then opened the latter using the Open XML SDK Productivity Tool and used it to compare with the workbook having a background fill. This yields the code to turn the one into the other.

如您所见，有必要使用 PatternFill、BackgroundColor 和 ForegroundColor 元素创建一个新的 Fill.这将添加到CellFormats` 列表中，以便工作表上的单元格可以引用它.

As you can see, it's necessary to create a new Fill with PatternFill, BackgroundColor and ForegroundColorelements. This is added to the list ofCellFormats` so that it can be referenced by the cell on the worksheet.

    private void btnAddFillColor_Click(object sender, EventArgs e)
    {
        string filePath = "C:\\Test\\OpenXMLTest_NoFillColor.xlsx";

        using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
        {
            WorkbookPart wbPart = document.WorkbookPart;
            WorksheetPart wsPart = wbPart.WorksheetParts.FirstOrDefault();
            WorkbookStylesPart stylesPart = document.WorkbookPart.WorkbookStylesPart;
            ChangeWorkbookStylesPart(stylesPart);
            ChangeWorksheetPart(wsPart);
        }
    }

    private void ChangeWorkbookStylesPart(WorkbookStylesPart workbookStylesPart1)
    {
        xl.Stylesheet stylesheet1 = workbookStylesPart1.Stylesheet;

        xl.Fills fills1 = stylesheet1.GetFirstChild<xl.Fills>();
        xl.CellFormats cellFormats1 = stylesheet1.GetFirstChild<xl.CellFormats>();
        fills1.Count = (UInt32Value)3U;

        xl.Fill fill1 = new xl.Fill();

        xl.PatternFill patternFill1 = new xl.PatternFill() { PatternType = xl.PatternValues.Solid };
        xl.ForegroundColor foregroundColor1 = new xl.ForegroundColor() { Rgb = "FFC00000" };
        xl.BackgroundColor backgroundColor1 = new xl.BackgroundColor() { Indexed = (UInt32Value)64U };

        patternFill1.Append(foregroundColor1);
        patternFill1.Append(backgroundColor1);

        fill1.Append(patternFill1);
        fills1.Append(fill1);
        cellFormats1.Count = (UInt32Value)2U;

        xl.CellFormat cellFormat1 = new xl.CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
        cellFormats1.Append(cellFormat1);
    }

    private void ChangeWorksheetPart(WorksheetPart worksheetPart1)
    {
        xl.Worksheet worksheet1 = worksheetPart1.Worksheet;

        xl.SheetData sheetData1 = worksheet1.GetFirstChild<xl.SheetData>();

        xl.Row row1 = sheetData1.GetFirstChild<xl.Row>();

        xl.Cell cell1 = row1.GetFirstChild<xl.Cell>();
        cell1.StyleIndex = (UInt32Value)1U;
    }

这篇关于使用纯色填充单元格，使用保留其他现有工作表格式的 OpenXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！