StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPOI: Cell format for 2000 and above records is corrupted
text
Body
copied!<p>Can anyone help me on how can I fix this one.</p> <p>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 <em>"To many different cell formats"</em> and the next message is <em>"Excel encountered an error and had to remove some formatting to avoid corrupting of workbook. Please re-check you formatting carefully."</em></p> <p>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.</p> <p>The below code is my sample code.</p> <pre><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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload