The problem I'm facing is when I'm trying to introduce any styling to any particular cell, the same styling gets applied to entire workbook, even to the sheets which I'm not accessing at all(the additional sheets present at the end).
e.g., when I'm using setFillForegroundColor() method for headerCell, it puts the same color into all cells after the one I'm trying to change. Same issue happened to dataCell as well when I tried to set borders to them(code commented now)
These affect only the cells which have no data previously. Cells having data before my program runs(I'm trying to add/modify sheets with pre-existing data), remain unaffected. I'm still not able to find the issue. Have given my entire method's code below.
private void prepareDataForExcel() {
try {
System.out.println(dataMap);
String sheetNameMapArr[] = props.getProperty("sheetNameMap").split(",");
XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(new File("mydata.xlsx").getAbsolutePath()));
for (String sheetMap : sheetNameMapArr) {
String table = sheetMap.split("~")[0];
String sheetNm = sheetMap.split("~")[1];
int rowNum = 0;
String headerArr[] = null;
String opDataArr[] = dataMap.get(table).toString().split(",");
XSSFSheet sheet = workbook.getSheet(sheetNm);
XSSFRow row = sheet.getRow(rowNum);
int lstCol = row.getLastCellNum();
// Setting Headers
String headerMapArr[] = props.getProperty("tableHeaderMap").split(";");
for (String header : headerMapArr) {
if (header.split("~")[0].equalsIgnoreCase(table)) {
headerArr = header.split("~")[1].split(",");
}
}
int headerIndx = lstCol;
if (headerArr != null) {
++rowNum;
for (String header : headerArr) {
XSSFCell headerCell = row.createCell(headerIndx, Cell.CELL_TYPE_STRING);
++headerIndx;
headerCell.setCellValue(header);
headerCell.getCellStyle()
.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 255, 204)));
headerCell.getCellStyle().setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
}
// Setting Date
String dt = new SimpleDateFormat("dd/MM/yyyy").format(new Date());
row = sheet.getRow(rowNum);
if (headerIndx > lstCol)
sheet.addMergedRegion(
new CellRangeAddress(rowNum, rowNum, lstCol, lstCol + (headerIndx - lstCol - 1)));
XSSFCell dtCell = row.createCell(lstCol, Cell.CELL_TYPE_STRING);
dtCell.setCellValue(dt);
// Populating Data
int dataRowIndx = ++rowNum;
int dataColIndx = lstCol;
for (String tableData : opDataArr) {
String dataArr[] = tableData.split("~");
for (String data : dataArr) {
row = sheet.getRow(dataRowIndx);
if (row == null)
row = sheet.createRow(dataRowIndx);
XSSFCell dataCell = row.createCell(dataColIndx, Cell.CELL_TYPE_STRING);
++dataColIndx;
if (isNumeric(data))
dataCell.setCellValue(Double.valueOf(data));
else
dataCell.setCellValue(data);
// dataCell.getCellStyle().setBorderBottom(XSSFCellStyle.BORDER_THIN);
// dataCell.getCellStyle().setBorderLeft(XSSFCellStyle.BORDER_THIN);
// dataCell.getCellStyle().setBorderRight(XSSFCellStyle.BORDER_THIN);
// dataCell.getCellStyle().setBorderTop(XSSFCellStyle.BORDER_THIN);
}
++dataRowIndx;
dataColIndx = lstCol;
}
}
FileOutputStream fileOut = new FileOutputStream(new File("mydata_res.xlsx"));
workbook.write(fileOut);
fileOut.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
}