Workbook cell style in POI/NPOI doesn't work properly with multiple styles in workbook

Question

I'm running into strange problem with .Net version of POI library for Excel Spreadsheets. I'm rewriting from text files to Excel 97-2003 documents and I'm like to add some formatting programmatically depend on some values gather at the begging of the program.

At the beginning, in the same method where I was creating a new cell from given value I was creating also a new Workbook CellStyle which was wrong, because I was running out of the styles very quickly (or I was just thought it was the cause of the problem).

Constructor of the class responsible for Excel Workbook:

public OldExcelWriter(TextWriter logger) : base(logger)
{
    _workbook = new HSSFWorkbook();
    _sheetData = _workbook.CreateSheet("sheet1");
    _creationHelper = _workbook.GetCreationHelper();
}

Method that is calling all the chains of operations:

public void Write(string path, Data data)
{
    FillSpreadSheetWithData(data, _sheetData);
    SaveSpreadSheet(_workbook, path);
}

Long story short, in FillSpreadSheetWithData I have method for creating a row inside which I'm have a loop for each cell, so basically I'm iterating thru every column, passing IRow references to a row, column value, index and formatting information like this:

for (int j = 0; j < column.Count; j++)
{
    CreateCell(row, column[j], j, data.Formatting[j]);
}

and while creating a new styles (for first shot I was trying to pass some date time values) I had situation like this in my rewrited Excel: screenshot of excel workbook

So formatting was passed correctly (also Horizontal Aligment etc.) but it get ugly after 15th row (always the same amount).

DateTime dataCell = DateTime.MaxValue;
var cell = row.CreateCell(columnIndex);

 _cellStyle = _workbook.CreateCellStyle();

            switch (format.Type)
            {
                case DataType.Date:
                    _cellStyle.DataFormat = _creationHelper.CreateDataFormat().GetFormat("m/dd/yyyy");
                    if (value.Replace("\n", "") != string.Empty)
                    {
                        dataCell = DateTime.ParseExact(value.Replace("\n", ""), "m/dd/yyyy",
                            System.Globalization.CultureInfo.InvariantCulture);
                    }
                    break;
            }

            switch (format.HorizontalAlignment)
            {
                case Enums.HorizontalAlignment.Left:
                    _cellStyle.Alignment = HorizontalAlignment.LEFT;
                    break;
                case Enums.HorizontalAlignment.Center:
                    _cellStyle.Alignment = HorizontalAlignment.CENTER;
                    break;
            }

            if (dataCell != DateTime.MaxValue)
            {
                cell.CellStyle = _cellStyle;
                cell.SetCellValue(dataCell);
                dataCell = DateTime.MaxValue;
            }
            else
            {
                cell.CellStyle = _cellStyle;
                cell.SetCellValue(value);
            }

(It's not the cleanest code but I will don refactor after getting this work).

After running into this issue I thought that maybe I will create _cellStyle variable in the constructor and only change it's value depends on the case, because it's assigned to the new cell anyway and I see while debugging that object values are correct.

But after creating everything, it won't get any better. Styles was override by the last value of the style, and dates are spoiled also, but later: screnshoot of excel workbook after creating one instance of cell style

I'm running out of ideas, maybe I should create every combination of the cell styles (I'm using only few data formats and alignments) but before I will do that (because I'm running out of easy options right now) I wonder what you guys think that should be done here.

cell format is set to custom with date type

Answer 1

I am using this code to create my custom style and format. Its for XSSF Format of excel sheet. but it will work for HSSF format with some modification.

XSSFFont defaultFont = (XSSFFont)workbook.CreateFont();
defaultFont.FontHeightInPoints = (short)10;
defaultFont.FontName = "Arial";
defaultFont.Color = IndexedColors.Black.Index;
defaultFont.IsBold = false;
defaultFont.IsItalic = false;


XSSFCellStyle dateCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat dateDataFormat = (XSSFDataFormat)workbook.CreateDataFormat();
dateCellStyle.SetDataFormat(dateDataFormat.GetFormat("m/d/yy h:mm")); //Replace format by m/dd/yyyy. try similar approach for phone number etc.
dateCellStyle.FillBackgroundColor = IndexedColors.LightYellow.Index;
//dateCellStyle.FillPattern = FillPattern.NoFill;
dateCellStyle.FillForegroundColor = IndexedColors.LightTurquoise.Index;
dateCellStyle.FillPattern = FillPattern.SolidForeground;
dateCellStyle.Alignment = HorizontalAlignment.Left;
dateCellStyle.VerticalAlignment = VerticalAlignment.Top;
dateCellStyle.BorderBottom = BorderStyle.Thin;
dateCellStyle.BorderTop = BorderStyle.Thin;
dateCellStyle.BorderLeft = BorderStyle.Thin;
dateCellStyle.BorderRight = BorderStyle.Thin;
dateCellStyle.SetFont(defaultFont);


//Apply your style to column 
  _sheetData.SetDefaultColumnStyle(columnIndex, dateCellStyle);  

// Or you can also apply style cell wise like 

  var row =  _sheetData.CreateRow(0);
for (int cellIndex = 0;cellIndex < TotalHeaderCount;cellIndex++)
  {
     row.Cells[cellIndex].CellStyle = dateCellStyle;
  }