使用NPOI库更改单元格的背景颜色，C#语言

Question

你好，我正在尝试使用C#库NPOI编写一个.xls文件。我已经成功创建了该文件，但是当我尝试更改工作表中某些单元格的背景颜色时，所有单元格的颜色都会改变，这让我很困惑，请帮帮我。

以下是我使用的代码：

// Creation of XLS

// I create a new excel work
HSSFWorkbook workbook = new HSSFWorkbook();

int rowNumber = 0;
//I add to the excel work a sheet of work
ISheet sheet = workbook.CreateSheet("Sheet 1");

// I create the Header of the sheet
var headerRow = sheet.CreateRow(0);

headerRow.CreateCell(0).SetCellValue("Famiglia");
headerRow.CreateCell(1).SetCellValue("Quantità Tagliata Fogli");
headerRow.CreateCell(2).SetCellValue("Lotto Medio di Produzione Fogli");
headerRow.CreateCell(3).SetCellValue("Quantità Scarto Medio(pezzi)");
headerRow.CreateCell(4).SetCellValue("Valore Scarti Euro");
headerRow.CreateCell(5).SetCellValue("% Scarto");
headerRow.CreateCell(6).SetCellValue(" Lead Time Medio Produttivo");

for (int c = 0; c < headerRow.Cells.Count; c++)
{                               
    headerRow.Cells[0].CellStyle.FillForegroundColor= IndexedColors.LightBlue.Index;
    headerRow.Cells[0].CellStyle.FillPattern = FillPattern.SolidForeground;
}

// Now what I have to do is to write the data in to the cells creating so a new record
rowNumber++;
IRow row = sheet.CreateRow(rowNumber);                    

row.CreateCell(0).SetCellValue(f.family);
row.CreateCell(1).SetCellValue(f.QuantitàTagliataFogli);
row.CreateCell(2).SetCellValue(f.LottoMedioProduzioneFogli);
row.CreateCell(3).SetCellValue(f.QuantitàScartoMedioInPezzi);
row.CreateCell(4).SetCellValue(f.ValoreScartoInEuro);
row.CreateCell(5).SetCellValue(f.ScartoMedio);
row.CreateCell(6).SetCellValue(f.LeadTimeMedioProduttivo);

// Now I have to try to write the file XLS

MemoryStream output = new MemoryStream();
workbook.Write(output);

SaveFileDialog SaveFileDialog = new SaveFileDialog();
SaveFileDialog.Title = "Save As...";
SaveFileDialog.Filter = "xls File (*.xls)|*.xls";
SaveFileDialog.InitialDirectory = @"C:\";

if (SaveFileDialog.ShowDialog() == DialogResult.OK)
{
    FileStream fs = new FileStream(SaveFileDialog.FileName, FileMode.Create);
    // Create the writer for data.
    workbook.Write(fs);
    fs.Close();
}

我原以为只有第一行单元格的背景色是浅蓝色，但实际上整个表格的单元格都被涂成了这种颜色。

为什么会这样？！

- Ale61268967

Answer 1

请尝试以下方法。我已经将字体、样式分离，并最终使用了您的for循环分配单元格样式，同时还修复了循环内部的错误，您每次只将样式分配给Cells[0]。

HSSFFont headerFont = (HSSFFont)workbook.CreateFont();
headerFont.FontHeightInPoints = (short)12;
headerFont.FontName = "Arial";
headerFont.Color = IndexedColors.White.Index;
headerFont.IsBold = true;
headerFont.IsItalic = false;
headerFont.Boldweight = 700;

HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.WrapText = true;
headerStyle.FillForegroundColor = IndexedColors.LightBlue.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.SetFont(headerFont);

for (int c = 0; c < headerRow.Cells.Count; c++)
{                               
    headerRow.Cells[c].CellStyle = headerStyle;
}

- kumar chandraketu

---

这将会把所有表头单元格的颜色都变成浅蓝色，但是 OP 不想要这样。OP 只想填充选定单元格的颜色。 - Virender Thakur

Answer 2

替换这些行：

for (int c = 0; c < headerRow.Cells.Count; c++)
{
    headerRow.Cells[0].CellStyle.FillForegroundColor= IndexedColors.LightBlue.Index;
    headerRow.Cells[0].CellStyle.FillPattern = FillPattern.SolidForeground;
}

使用：

HSSFCellStyle cellStyleBlue = (HSSFCellStyle)workbook.CreateCellStyle();
cellStyleBlue.FillForegroundColor = IndexedColors.LightBlue.Index;
cellStyleBlue.FillPattern = FillPattern.SolidForeground;

for (int c = 0; c < headerRow.Cells.Count; c++)
{
    headerRow.Cells[c].CellStyle = cellStyleBlue;
}

然后，只有第一行的单元格将应用单元格样式。

- krlzlx