You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2018/05/21 09:51:31 UTC
[Bug 62394] New: Excel loses styles and formating after writing
certain rows using apache poi library
https://bz.apache.org/bugzilla/show_bug.cgi?id=62394
Bug ID: 62394
Summary: Excel loses styles and formating after writing certain
rows using apache poi library
Product: POI
Version: unspecified
Hardware: PC
Status: NEW
Severity: critical
Priority: P2
Component: HSSF
Assignee: dev@poi.apache.org
Reporter: nileshwani.vit@gmail.com
Target Milestone: ---
Created attachment 35941
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35941&action=edit
The output .xls file generated by the above code.
I am trying to create a excel file(.xls) while writing a excel for tolal of
9000 rows, after row number 3000 all the styles and formatting are lost. I have
tried to get the number of cellstyle after writing the complete workbook by
using workbook.getNumCellStyles(), it returns 2091. It is clear that the number
of cellstyle are not exceeded(i.e are less than 4000) Still the styling is
lost. Please help me understand why would this happen?
I have created a Sample code, below this fails even if i am creating 75
CellStyle Objects.
public class ExcelSheet {
static Workbook workbook;
static Sheet spreadsheet;
static CellStyle tempCellStyle;
static Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>();
static boolean flag = false;
public static void main(String[] args) throws IOException {
workbook = new HSSFWorkbook();
spreadsheet = workbook.createSheet(" Employee Info ");
Row row;
System.out.println(" Writing data to excel...");
for (int i = 1; i < 500; i++) {
row = spreadsheet.createRow(i);
createCells(row, i);
}
for (int i = 0; i < 10; i++) {
spreadsheet.setColumnWidth(i, 4500);
}
FileOutputStream out = new FileOutputStream(new
File("E:\\Dan\\Writesheet.xls"));
System.out.println(" No. of cell styles : " +
workbook.getNumCellStyles());
System.out.println(" No. of font styles : " +
workbook.getNumberOfFonts());
workbook.write(out);
out.close();
}
private static void createCells(Row row, int rowNo) {
for (int i = 0; i < 10; i++) {
Cell cell = row.createCell(i);
try {
writeData(cell, i, rowNo);
} catch (Exception e) {
System.out.println("Exception : " + e);
}
}
}
private static void writeData(Cell cell, int i, int rowNo) throws Exception
{
CellStyle style;
if (workbook.getNumCellStyles() < 75) {
style = createStyleObject();
} else {
style = null;
}
switch (i) {
case 0:
if (style == null) {
style = styles.get(0);
} else {
styles.put(0, style);
}
cell.setCellValue(rowNo);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style);
break;
case 1:
if (style == null) {
style = styles.get(1);
} else {
styles.put(1, style);
}
cell.setCellValue("Test Data " + rowNo);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(style);
styles.put(0, style);
break;
case 2:
if (style == null) {
style = styles.get(2);
} else {
styles.put(2, style);
}
DataFormat poiFormat =
spreadsheet.getWorkbook().createDataFormat();
String excelFormatPattern = DateFormatConverter.convert(Locale.US,
"yyyy-MM-dd");
style.setDataFormat(poiFormat.getFormat(excelFormatPattern));
cell.setCellValue(new Date(rowNo % 10, rowNo % 12, rowNo % 28,
rowNo % 12, rowNo % 60, rowNo % 60));
cell.setCellStyle(style);
break;
case 3:
if (style == null) {
style = styles.get(3);
} else {
styles.put(3, style);
}
cell.setCellValue(rowNo + rowNo);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style);
break;
case 4:
if (style == null) {
style = styles.get(4);
} else {
styles.put(4, style);
}
cell.setCellValue("Sample Text " + rowNo);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(style);
break;
case 5:
if (style == null) {
style = styles.get(5);
} else {
styles.put(5, style);
}
DataFormat poiFormat1 =
spreadsheet.getWorkbook().createDataFormat();
String excelFormatPattern1 = DateFormatConverter.convert(Locale.US,
"yyyy-MM-dd");
style.setDataFormat(poiFormat1.getFormat(excelFormatPattern1));
cell.setCellValue(new Date(rowNo % 18, rowNo % 12, rowNo % 28,
rowNo % 12, rowNo % 60, rowNo % 60));
cell.setCellStyle(style);
break;
case 6:
if (style == null) {
style = styles.get(6);
} else {
styles.put(6, style);
}
cell.setCellValue(rowNo + i);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style);
break;
case 7:
if (style == null) {
style = styles.get(7);
} else {
styles.put(7, style);
}
cell.setCellValue("Exports Data " + rowNo);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(style);
break;
case 8:
if (style == null) {
style = styles.get(8);
} else {
styles.put(8, style);
}
DataFormat poiFormat2 =
spreadsheet.getWorkbook().createDataFormat();
String excelFormatPattern2 = DateFormatConverter.convert(Locale.US,
"yyyy-MM-dd");
style.setDataFormat(poiFormat2.getFormat(excelFormatPattern2));
cell.setCellValue(new Date(rowNo % 25, rowNo % 12, rowNo % 28,
rowNo % 12, rowNo % 60, rowNo % 60));
cell.setCellStyle(style);
break;
case 9:
if (style == null) {
style = styles.get(9);
} else {
styles.put(9, style);
}
DataFormat poiFormat3 =
spreadsheet.getWorkbook().createDataFormat();
String excelFormatPattern3 = DateFormatConverter.convert(Locale.US,
"yyyy-MM-dd");
style.setDataFormat(poiFormat3.getFormat(excelFormatPattern3));
cell.setCellValue(new Date());
cell.setCellStyle(style);
break;
default:
if (style == null) {
style = styles.get(7);
} else {
styles.put(7, style);
}
cell.setCellValue("Default Value " + rowNo);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(style);
break;
}
}
private static CellStyle createStyleObject() {
Workbook workbook = spreadsheet.getWorkbook();
CellStyle style = (HSSFCellStyle) workbook.createCellStyle();
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor((short) 67);
style.setWrapText(true);
return style;
}
}
why should it lose style even if i use one unique styles or more , unless the
number is less than 4000. This is just a example code i have put in here to
demonstrate the problem, my actual code generates only unique styles.
This also Happens for .xlsx but after a larger number of cellstyle objects.
The POI version here is 3.8 but i have also tried it with latest 3.17 it gives
the same issue.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62394] Excel loses styles and formating after writing certain
rows using apache poi library
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62394
Nilesh <ni...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62394] Excel loses styles and formating after writing certain
rows. Even if Cellstyles are not exhausted
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62394
Praveen Madduri <mp...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |mpraveen0311@gmail.com
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62394] Excel loses styles and formating after writing certain
rows. Even if Cellstyles are not exhausted
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62394
Nilesh <ni...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|Excel loses styles and |Excel loses styles and
|formating after writing |formating after writing
|certain rows using apache |certain rows. Even if
|poi library |Cellstyles are not
| |exhausted
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62394] Excel loses styles and formating after writing certain
rows using apache poi library
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62394
--- Comment #1 from Mark Murphy <jm...@apache.org> ---
I have tested XLSX and it does not fail. I tested up to 50,000 styles with POI
3.14 and Java 8. This test does show the failure with HSSF though.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62394] Excel loses styles and formating after writing certain rows. Even if Cellstyles are not exhausted
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62394
Hamza Gobir <hg...@googlemail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |hgobir@googlemail.com
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org