c# - 如何在生成 Excel 工作表时为列值添加颜色?
问题描述
我想在生成时为 Excel 工作表中的特定列添加颜色。
我已经生成了 excel 表,但现在我想为列添加颜色
//---------------------------
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Template"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
foreach (DataExchangeDefinition a in importColList)
{
defnExist = true;
row.Append(
ConstructCell(a.FieldCaption, CellValues.String));
}
if (defnExist == false)
{
row.Append(
ConstructCell("Excel Template Definition Missing", CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
worksheetPart.Worksheet.Save();
}
}
catch (Exception)
{
throw;
}
}
private Cell ConstructCell(string value, CellValues dataType)
{
Cell c= new Cell()
{
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(dataType),
};
Color color2 = new Color() { Rgb = "FF006100" };
c.Append(color2);
return c;
}
这是生成excel表格的代码。此 excel 表只是一个模板,因此只有列名。
谁能帮我这个
解决方案
以下代码将为所有列中的文本着色。(但请注意,它不会为单个列中的文本着色。)
首先,您检查:
foreach (DataExchangeDefinition a in importColList)
{
defnExist = true;
row.Append(ConstructCell(a.FieldCaption, CellValues.String));
}
这是完整的代码:
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Template" };
sheets.Append(sheet);
var stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("mc", "http: //schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http: //schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fills = new Fills() { Count = 5U };
var fonts = new Fonts() { Count = 1U, KnownFonts = true };
// var cellFormats = new CellFormats() { Count = 4U };
Font font = new Font();
font.Append(new Color() { Rgb = "ff0000" });
fonts.Append(font);
//Fill fill = new Fill();
//var patternFill = new PatternFill() { PatternType = PatternValues.Solid };
//patternFill.Append(new ForegroundColor() { Rgb = "00ff00" });
//patternFill.Append(new BackgroundColor() { Indexed = 64U });
//fill.Append(patternFill);
//fills.Append(fill);
// cellFormats.AppendChild(new CellFormat() { FontId = 0U, FillId = 0U });
stylesheet.Append(fonts);
stylesheet.Append(fills);
// stylesheet.Append(cellFormats);
var stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = stylesheet;
stylePart.Stylesheet.Save();
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
foreach (DataExchangeDefinition a in importColList)
{
defnExist = true;
row.Append(
ConstructCell(a.FieldCaption, CellValues.String));
}
if (defnExist == false)
{
row.Append(
ConstructCell("Excel Template Definition Missing", CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
worksheetPart.Worksheet.Save();
}
}
catch (Exception)
{
throw;
}
}
private Cell ConstructCell(string value, CellValues dataType)
{
Cell c = new Cell()
{
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(dataType)
// StyleIndex=0U,
};
return c;
}
推荐阅读
- python - Pandas pd.to_datetime 导致数据框中的日期值损坏
- python - Python Eclipse 中的代码完成限制和使用 Iris 数据集的对象类型
- javascript - 我的 node.js json 解析函数抛出错误
- javascript - 未捕获的错误:目标容器不是 DOM 元素。React - Karma - Karma-webpack
- webpack - 如何防止 Webpack 5(或其插件之一)从我导出的 CSS 中剥离 `@charset`?
- python - 如何在 python 主机 cpanel 上使用 OpenCV lib?
- node.js - AWS SDK 配置中缺少凭证
- css - !important 将文件上传到 cPanel 后无法正常工作
- python - Python 异常处理忽略对应的行数据
- python - qmk 设置失败并显示“没有名为隐藏的模块”