在Excel中的CellValue中更改文本的字体颜色-OpenXml [英] Change font color of text in CellValue in Excel - OpenXml
问题描述
如何在Excel中更改CellValue的文本颜色?我可以更改单元格的前景色，但是它可以更改单元格内存在的所有文本的颜色，这是我不想要的.我只想突出显示单元格内的特定文本，即CellValue文本.
How can I change the text color of CellValue in excel? I am able to change foreground color of a cell but it changes color of all text present inside the cell, which I don't want. I want to highlight only particular text inside the cell i.e CellValue text.
我正在使用以下代码突出显示单元格文本，如何对CellValue进行处理?
I am using below code to highlight cell text, how it can be done for CellValue?
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell currentCell in allCells)
{
Fill fill = new Fill()
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor() { Rgb = "FFFF00" }
}
};
styleSheet.Fills.AppendChild(fill);
//Adding the CellFormat which uses the Fill element
CellFormats cellFormats = styleSheet.CellFormats;
CellFormat cf = new CellFormat();
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
currentCell.StyleIndex = styleSheet.CellFormats.Count;
}
我在CellValue中看不到Style的任何属性
I dont see any property of Style in CellValue
CellValue currentCellValue = currentCell.GetFirstChild<CellValue>();
if (currentCell.DataType == CellValues.SharedString) // cell has a cell value that is a string, thus, stored else where
{
data = doc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault().SharedStringTable.ElementAt(int.Parse(currentCellValue.Text)).InnerText;
}
OpenXML工具生成的代码-
Generated code from OpenXML Tool -
SharedStringTable sharedStringTable1 = new SharedStringTable(){ Count = (UInt32Value)1U, UniqueCount = (UInt32Value)1U };
SharedStringItem sharedStringItem1 = new SharedStringItem();
Run run1 = new Run();
RunProperties runProperties1 = new RunProperties();
FontSize fontSize3 = new FontSize(){ Val = 11D };
Color color3 = new Color(){ Rgb = "FFFF0000" };
RunFont runFont1 = new RunFont(){ Val = "Calibri" };
FontFamily fontFamily1 = new FontFamily(){ Val = 2 };
FontScheme fontScheme4 = new FontScheme(){ Val = FontSchemeValues.Minor };
runProperties1.Append(fontSize3);
runProperties1.Append(color3);
runProperties1.Append(runFont1);
runProperties1.Append(fontFamily1);
runProperties1.Append(fontScheme4);
Text text1 = new Text();
text1.Text = "Microsoft";
run1.Append(runProperties1);
run1.Append(text1);
Run run2 = new Run();
RunProperties runProperties2 = new RunProperties();
FontSize fontSize4 = new FontSize(){ Val = 11D };
Color color4 = new Color(){ Theme = (UInt32Value)1U };
RunFont runFont2 = new RunFont(){ Val = "Calibri" };
FontFamily fontFamily2 = new FontFamily(){ Val = 2 };
FontScheme fontScheme5 = new FontScheme(){ Val = FontSchemeValues.Minor };
runProperties2.Append(fontSize4);
runProperties2.Append(color4);
runProperties2.Append(runFont2);
runProperties2.Append(fontFamily2);
runProperties2.Append(fontScheme5);
Text text2 = new Text(){ Space = SpaceProcessingModeValues.Preserve };
text2.Text = " is great";
run2.Append(runProperties2);
run2.Append(text2);
sharedStringItem1.Append(run1);
sharedStringItem1.Append(run2);
sharedStringTable1.Append(sharedStringItem1);
sharedStringTablePart1.SharedStringTable = sharedStringTable1;
推荐答案
您必须通过SharedStringItem元素进行操作.
这样的SharedStringItem 可以包含Run元素.
您可以在Run元素上应用样式.
You have to go via the SharedStringItem elements.
Such a SharedStringItem can contain Run elements.
You apply the styling on this Run element.
重要的是，您的代码还应涵盖SharedStringItem不包含任何Run子元素的情况.
当单元格仅保存文本而没有任何格式化的子元素时，就是这种情况.
在这里，您必须创建一个新的Run才能应用样式.
It is important that your code also covers the situation where a SharedStringItem does not contain any Run child elements.
This is the case when the cell only holds text, without any formatted child elements.
Here you have to create a new Run in order to apply the styling.
下面的代码使用Excel文件将第一行中单元格的单词RED的颜色设置为红色，如下图所示.
单元格A1包含Run元素，单元格B1不包含.
The code below sets the color of the word RED to red for the cells in the first row using the Excel file as shown in the image below.
Cell A1 contains Run elements, cell B1 doesn't.
最终结果看起来像
String pathToYourExcelFile = @"C:\Folder\ExcelFile.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToYourExcelFile, true))
{
WorkbookPart workbook = document.WorkbookPart;
WorksheetPart firstWorksheet = document.WorkbookPart.WorksheetParts.FirstOrDefault();
SharedStringTablePart stringTable = workbook.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
IEnumerable<Row> rows = firstWorksheet.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
Row firstRow = rows.FirstOrDefault();
foreach (Cell cell in firstRow.Elements<Cell>())
{
foreach (CellValue cellValue in cell.Elements<CellValue>())
{
IEnumerable<SharedStringItem> sharedStrings =
stringTable.SharedStringTable.Elements<SharedStringItem>()
.Where((o, i) => i == Convert.ToInt32(cellValue.InnerText));
foreach (SharedStringItem sharedString in sharedStrings)
{
IEnumerable<Run> runs = sharedString.Elements<Run>();
if (runs.Count() > 0)
{
foreach (Run run in runs)
{
if (run.InnerText == "RED")
{
RunProperties properties = run.RunProperties ?? new RunProperties();
Color color = properties.Elements<Color>().FirstOrDefault();
if (color != null)
{
properties.RemoveChild<Color>(color);
}
properties.Append(new Color { Rgb = "FFFF0000" }) ;
}
}
}
else
{
// No Runs, only text; create a Run.
Text text = new Text(sharedString.InnerText);
sharedString.RemoveAllChildren();
Run run = new Run();
run.Append(text);
run.RunProperties = new RunProperties();
run.RunProperties.Append(new Color { Rgb = "FFFF0000" }) ;
sharedString.Append(run);
}
}
}
}
document.Save();
(我将上面代码中的清除和异常处理留给您... )
编辑
对于您的特定情况，单元格值为"Microsoft很棒"，
您必须将此字符串拆分为单独的部分，并为每个部分创建一个Run.
仅在文本值为"Microsoft"的部分上应用自定义字体颜色.
For your specific case, having the cell value "Microsoft is great",
you'll have to split this string into separate parts and create a Run for each part.
Only on the part having the text value 'Microsoft' you apply a custom font color.
下面的简约代码显示了此概念.
The minimalistic code below shows this concept.
(This code can use some improvements, as it is better not to split on separate words, but you get the idea ...)
// No Runs, only text.
const String MS = "Microsoft";
String innerText = sharedString.InnerText;
if (innerText.IndexOf(MS, StringComparison.OrdinalIgnoreCase) >= 0)
{
sharedString.RemoveAllChildren();
String[] parts = innerText.Split(' ');
for (Int32 i = 0; i < parts.Length; i++)
{
String part = parts[i];
Text text = new Text((i > 0 ? " " : String.Empty) + part);
text.Space = SpaceProcessingModeValues.Preserve;
Run run = new Run();
run.Append(text);
if (part.Equals(MS, StringComparison.OrdinalIgnoreCase))
{
run.RunProperties = new RunProperties();
run.RunProperties.Append(new Color { Rgb = "FFFF0000" }) ;
}
sharedString.Append(run);
}
下图显示了之前和之后.
The image below shows before and after.
编辑
针对您对如何遍历Excel文档中所有单元格的评论；参见下面的代码.
In response to your comment about how to loop over all cells in the Excel document; see the code below.
String pathToYourExcelFile = @"C:\Folder\ExcelFile.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToYourExcelFile, true))
{
WorkbookPart workbook = document.WorkbookPart;
// Loop over all worksheets.
IEnumerable<WorksheetPart> worksheets = document.WorkbookPart.WorksheetParts;
foreach (WorksheetPart worksheet in worksheets)
{
// Loop over all rows.
IEnumerable<Row> rows = worksheet.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
foreach (Row row in rows)
{
// Loop over all cells.
foreach (Cell cell in row.Elements<Cell>())
{
// Loop over all cell values.
foreach (CellValue cellValue in cell.Elements<CellValue>())
{
// Apply content formatting as in code above ...
}
}
}
}
}
这篇关于在Excel中的CellValue中更改文本的字体颜色-OpenXml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
