CODEWITHSUNDEEP
c#excelnpoi
Ale61268967
Ale61268967

Reputation: 117

C# changing backColor of a cell using the NPOI library

Hi I am trying to write an .xls file using the c# library NPOI, I have been able to create the file but when I tried to change the back color of some of the cells of my sheet I don't know why all the cells of the sheet change color it's driving me crazy could you please help me.

Here is the code I am using:

// 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();
}

I would have expected only the cells of the first row with backColor lightblue instead I get all the cells of the sheet with that color.

Why?!?

Upvotes: 4

Views: 5664

Answers (2)

kumar chandraketu
kumar chandraketu

Reputation: 2370

Please try like below approach. I have separated the font , styling and finally used your for loop assignment for Cell styling also fixed the error inside for loop , you are assigning the style only to Cells[0] every time.

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;
}

Upvotes: 2

krlzlx
krlzlx

Reputation: 5822

Replace these lines:

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

With:

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;
}

Then, only the cells of the first row will have the cell style applied to them.

Upvotes: 0

Related Questions