how to use npoi to set the background color of a cell instead of an entire row?

[michaelbrucelin]

I need to write a method to export the data displayed by the DataGridView to an Excel, in addition to the data, the background color of the DataGridView also needs to be exported to Excel, so I wrote the following method:

public static void WriteExcelWithStyle(DataGridView dgv, string file)
{
    using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
    {
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        List<string> columns = new List<string>();
        IRow header = sheet.CreateRow(0);
        int colcnt = 0;
        for (int i = 0; i < dgv.Columns.Count && dgv.Columns[i].Visible; i++)
        {
            columns.Add(dgv.Columns[i].Name);
            header.CreateCell(colcnt++).SetCellValue(dgv.Columns[i].HeaderText);
        }

        int rowcnt = 1;
        for (int i = 0; i < dgv.Rows.Count && dgv.Rows[i].Visible; i++)
        {
            XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();

            // cell borders
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;

            // cell backgroundcolor
            style.FillPattern = FillPattern.SolidForeground;

            IRow row = sheet.CreateRow(rowcnt++);
            int colid = 0;
            foreach (string col in columns)
            {
                DataGridViewCell dgvcell = dgv.Rows[i].Cells[col];

                // cell backgroundcolor
                if (dgvcell.Style.BackColor.IsEmpty)
                    style.SetFillForegroundColor(new XSSFColor(new byte[] { 255, 255, 255 }));
                else
                    style.SetFillForegroundColor(new XSSFColor(new byte[] { dgvcell.Style.BackColor.R, dgvcell.Style.BackColor.G, dgvcell.Style.BackColor.B }));

                ICell cell = row.CreateCell(colid++);
                cell.SetCellValue(dgvcell.Value.ToString());
                cell.CellStyle = style;
            }
        }

        workbook.Write(fs);
    }
}

But it backfired. The background color of the cell is indeed set successfully, but the background color of the entire row is set, and I only want to set the background color of a single cell. How can I do this?

[michaelbrucelin]

I see why, it depends on where the XSSFCellStyle is declared, if so:

XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
for(...row)
{
    for(...column)
    {
        style... ... // The specific settings of the style
    }
}

The setting of the last cell affects the entire sheet.

if so:

for(...row)
{
    XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
    for(...column)
    {
        style... ... // The specific settings of the style
    }
}

The setting for the last cell of each row affects the entire row.

And if so:

for(...row)
{
    for(...column)
    {
        XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
        style... ... // The specific settings of the style
    }
}

style will only affect the current cell.

I should know what's going on. It should be caused by the reference type. When the data is written to the disk, the style setting will be read, and the style is a reference type. The latter setting will overwrite the former setting, so this problem occurs.