POI: Cell format for 2000 and above records is corrupted

Question

Can anyone help me on how can I fix this one.

I'm using vb.net 2003 and I've tried to generate a report in Excel using POI, but I have a problem when the records is above 2000, cell formats has been missing or corrupted for the next 2000 and above records. And when I'm opening the generated report and It shows a message of "To many different cell formats" and the next message is "Excel encountered an error and had to remove some formatting to avoid corrupting of workbook. Please re-check you formatting carefully."

Can anyone help me on how to fix it or can anyone else have an another idea for me to format all does cell whether it's 2000 records an above.

The below code is my sample code.

Private Sub btnExtract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExtract.Click

        Try
            Dim source As String = Application.StartupPath & "\Sample Template.xls"
            Dim sfdialog As New SaveFileDialog

            'Save File
            sfdialog.Filter = "Excel File | *.xls"
            If sfdialog.ShowDialog = DialogResult.OK Then

                'Variable Decleration
                Dim fileIn As java.io.InputStream = New java.io.FileInputStream(source)
                Dim fileOut As java.io.OutputStream = New java.io.FileOutputStream(sfdialog.FileName)
                Dim wb As HSSFWorkbook = New HSSFWorkbook(fileIn)
                Dim sheet As HSSFSheet = wb.getSheet("Sample Template")
                Dim row As HSSFRow
                Dim cell As HSSFCell
                Dim fileName As String = sfdialog.FileName

                'Inputs Data
                For rowNum As Integer = 3 To 10000
                    row = createRowCell(sheet, rowNum)
                    cell = row.getCell(1)
                    cell.setCellValue(rowNum - 2)
                    cell = row.getCell(2)
                    cell.setCellValue("EN" & rowNum - 2)
                    cell = row.getCell(3)
                    cell.setCellValue("TN" & rowNum - 2)
                Next

                fileIn.close()
                wb.write(fileOut)
                fileOut.flush()
                fileOut.close()

                If fileName <> "" Then
                    If MessageBox.Show("Open Generated Excel?", "Open File", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) = DialogResult.Yes Then
                        Try
                            Process.Start(fileName)
                        Catch ex As Exception
                            MessageBox.Show("Cannot open file", "Open error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End Try
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


    End Sub


    Private Function createRowCell(ByRef sheet As HSSFSheet, ByVal rowIndex As Int32) As HSSFRow
        Dim cellIndex As Int32 = 1
        Dim newRow As HSSFRow = sheet.createRow(rowIndex)
        Dim commonStyle As HSSFCellStyle = sheet.getWorkbook().createCellStyle()
        Dim dateStyle As HSSFCellStyle = sheet.getWorkbook().createCellStyle()
        Dim createHelper As HSSFCreationHelper = sheet.getWorkbook().getCreationHelper()
        Dim newCell As HSSFCell

        commonStyle.setBorderBottom(1)
        commonStyle.setBorderTop(1)
        commonStyle.setBorderLeft(1)
        commonStyle.setBorderRight(1)

        newCell = newRow.createCell(cellIndex)
        newCell.setCellStyle(commonStyle)
        cellIndex += 1
        newCell = newRow.createCell(cellIndex)
        newCell.setCellStyle(commonStyle)
        cellIndex += 1
        newCell = newRow.createCell(cellIndex)
        newCell.setCellStyle(commonStyle)


        Return newRow
    End Function

Answer 1

It's probably not be a POI issue. According to this Microsoft KB article, there's a limit to the number of custom formats that can be in one Workbook. The article presents a couple solutions (workarounds, really). Hopefully you can figure something out.

Answer 2

It's now working! just check you formatting if it was not redundant. Like formatting borders of one cell (E.G. right,bottom,left,right). It's nice to format only the top and left border for every cell then add some border format if it was at the end of a row or column (E.G. add border in bottom if it reach the maximum row and add border in right if it was reach the last column of a cell) :D

Here is the equivalent code:

Try
            Dim source As String = Application.StartupPath & "\Sample Template.xls"
            Dim sfdialog As New SaveFileDialog

            'Save File
            sfdialog.Filter = "Excel File | *.xls"
            If sfdialog.ShowDialog = DialogResult.OK Then

                'Variable Decleration
                Dim fileIn As java.io.InputStream = New java.io.FileInputStream(source)
                Dim fileOut As java.io.OutputStream = New java.io.FileOutputStream(sfdialog.FileName)
                Dim wb As HSSFWorkbook = New HSSFWorkbook(fileIn)
                Dim sheet As HSSFSheet = wb.getSheet("Sample Template")
                Dim row As HSSFRow
                Dim cell As HSSFCell

                Dim bgStyle As HSSFCellStyle = wb.createCellStyle
                Dim rightBorder As HSSFCellStyle = wb.createCellStyle
                Dim fileName As String = sfdialog.FileName
                Dim records As Integer = 60000
                'Inputs Data

                bgStyle.setBorderTop(1)
                bgStyle.setBorderLeft(1)
                rightBorder.setBorderTop(1)
                rightBorder.setBorderLeft(1)
                rightBorder.setBorderRight(1)

                For rowNum As Integer = 3 To records
                    row = sheet.createRow(rowNum)

                    If rowNum = records Then bgStyle.setBorderBottom(1)
                    '1
                    cell = row.createCell(1)
                    cell.setCellValue(rowNum - 2)
                    cell.setCellStyle(bgStyle)

                    cell = row.createCell(2)
                    cell.setCellValue("EN" & rowNum - 2)
                    cell.setCellStyle(bgStyle)

                    cell = row.createCell(3)
                    cell.setCellValue("TN" & rowNum - 2)
                    cell.setCellStyle(bgStyle)

                Next

                fileIn.close()
                wb.write(fileOut)
                fileOut.flush()
                fileOut.close()

                If fileName <> "" Then
                    If MessageBox.Show("Open Generated Excel?", "Open File", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) = DialogResult.Yes Then
                        Try
                            Process.Start(fileName)
                        Catch ex As Exception
                            MessageBox.Show("Cannot open file", "Open error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End Try
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try