使用DocumentFormat.OpenXml在条件下着色Excel单元格 [英] Colouring Excel cell on condition using DocumentFormat.OpenXml
问题描述
大家好，
因为我在使用DocumentFormat.Spreadsheet对条件单元格着色有问题。 现在我可以为1 < sup style ="颜色创建颜色：＃2f5496;"> st< / sup>
时间，但我需要打开相同的Excel表格，并根据我需要为特定单元格着色的条件。例如。如果单元格D可以是黄色或绿色。 例如
As I have an issue with the colouring of sheet cell on condition using DocumentFormat.Spreadsheet.Now I can create the colour for the 1<sup style="color:#2f5496;">st</sup> time but I need to open the same excel sheet and based on the condition I need to colour a particular cell. E.g. if cell D which can be yellow or green.e.g.
姓名
IpAddress
IpAddress
Region
详细信息
开关
10.1.1.1
EMEA
根据条件，单元格将被着色
Based on Condition the cell would be coloured
开关
10.1.1.2
AMER
根据条件，单元格将被着色
Based on Condition the cell would be coloured
开关
10.1.1.3
亚太地区
根据条件，单元格将被着色
Based on Condition the cell would be coloured
开关
10.1.1.2
AMER
根据条件，单元格将被着色
Based on Condition the cell would be coloured
开关
10.1.1.2
AMER
根据条件，单元格将被着色
Based on Condition the cell would be coloured
我一次写每一行。我想打开我创建的现有excel表并传递价值，即
_ valuecolour
并根据其值对特定单元格进行着色以及其他单元格的对齐。 我需要知道如何实现这一点。
任何帮助都会非常感激。 我使用了下面的代码。
public static bool InsertRowExcel(string filepath, string _sb, string _switchinput){
int i = 0; int k = 0; bool bl = false; string[] _arr = _switchinput.Split(',');
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filepath, true)){
//Get workbookpart
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorkbookStylesPart stylePart = workbookPart.WorkbookStylesPart;
Row row = new Row();
//then access to the worksheet part
IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
foreach (WorksheetPart WSP in worksheetPart){//find sheet data
IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData){
IEnumerable<Row> rows = SD.Elements<Row>(); // Get the row IEnumerator
i = (rows.Count()); // Will give you the count of rows
do{ row = new Row();
row.Append(
ConstructCell(_arr[0], CellValues.String),
ConstructCell(_arr[1], CellValues.String),
ConstructCell(_arr[2], CellValues.String),
ConstructCell(_sb, CellValues.String,2U)
);}while (k > 0);
/* HERE I NEED TO ADD STYLE TO THE CELL on condition. */
}} bl = true; }
return bl;}
private static Stylesheet GenerateStylesheet(bool _valuecolour){
Stylesheet styleSheet = null;
Fills fills = new Fills();
if (_valuecolour)
{
fills = new Fills(
new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } })
{ PatternType = PatternValues.Solid })
);}
else{
fills = new Fills(
new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "008000" } })
{ PatternType = PatternValues.Solid }) // Index 2 - body
);}
CellFormats cellFormats = new CellFormats(
new CellFormat(), // default
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 0, BorderId = 1, ApplyAlignment = true },
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true }){ FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true }); // header
styleSheet = new Stylesheet(fills, cellFormats);
return styleSheet;
}
private static Cell ConstructCell(string value, CellValues dataType,uint styleIndex = 0){return new Cell(){
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(dataType),
StyleIndex = styleIndex};}
推荐答案
您好，
没有必要获取sheetData集合，因为只有一个工作表数据元素。
There is no need to get the sheetData collection because there is only one sheet data element.
所以你可以使用：
SheetData sheetData = WSP.Worksheet.Elements<SheetData>().First();
IEnumerable<Row> rows = sheetData.Elements<Row>(); // Get the row IEnumerator代码不会在sheetdata中附加新行。
The code doesn't append the new row in the sheetdata.
请使用
do
{
row = new Row();
row.Append(
ConstructCell(_arr[0], CellValues.String),
ConstructCell(_arr[1], CellValues.String),
ConstructCell(_arr[2], CellValues.String),
ConstructCell(_sb, CellValues.String,2U)
);
sheetData.Append(row);
} while (k > 0);要创建样式表的代码，是否要将原始样式表替换为您的样式表？
For your code to create the stylesheet, do you want to replace the original stylesheet into yours?
如果你想生成自己的样式表，我建议你创建整个元素：
If you want to generate your own stylesheet, I suggest you create whole elements:
我建议您保留旧样式表并添加新元素。
I would suggest you keep the old stylesheet and append new elements instead.
Or you could refer to https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/
问候，
Celeste
这篇关于使用DocumentFormat.OpenXml在条件下着色Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
