I use POI read a excel template file, then fill in some data, saved. Then use jacob to SaveAs this excel file to pdf. Excel file only have date from A column to P column. Before POI touch the excel file, jacob can SaveAs this excel property. Only fetch A~P column. After POI touch the excel file, jacob SaveAs fetch A~AC column which P ~ AC have not any data. This effect the context display in the left of pdf , the mostly is space on the right.
Can you share the file and some code that shows the problem?
Created attachment 32242 [details] Before POI Touch Before POI Touch, Use jacob or directly use Microsoft Excel SaveAs , The result is same refer this attach 'Before POI Touch'
Created attachment 32243 [details] After POI Touch After POI touch , use jacob and Microsoft Excel do SaveAs to pdf , the result is same as this attchement . 'After POI Touch'
1. Below is the code, attach is before and after this code do SaveAs to pdf's result. public static void generateReport( String reportFile, List<ExampleVO> voList) { InputStream in = null; FileOutputStream out = null; try { in = Report.class.getResourceAsStream("/template.xls"); Workbook workbook = WorkbookFactory.create(in); Sheet sheet = workbook.getSheetAt(0); int rowNum ; if (null != voList) { rowNum = 12; int sn = 1; for (ExampleVO vo : voList) { int colNum = 2; row = sheet.createRow(rowNum++); row.createCell(colNum++).setCellValue(sn++); row.createCell(colNum++).setCellValue(vo.getAt()); // replace value to same for security row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); row.createCell(colNum++).setCellValue(vo.getAt()); } CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); for(int i = 12; i< rowNum; i++){ for(int j = 1; j< 16; j++){ Cell cell = sheet.getRow(i).getCell(j); if(null==cell){ cell = sheet.getRow(i).createCell(j); } cell.setCellStyle(cellStyle); } } } File f = new File(reportFile); FileUtil.createFile(f); out = new FileOutputStream(f); //workbook.setPrintArea(0, "$A$1:$Q$" + rowNum); logger.info(row.getLastCellNum()+""); workbook.write(out); out.close(); in.close(); } catch (FileNotFoundException e) { logger.error("File XXX Not Found!", e); } catch (IOException e) { logger.error(e.getMessage(), e); } catch (InvalidFormatException e) { logger.error("Template report file XXX invalid format", e); } }
1.Confirm is POI's problem, i use Java Excel API , works fine! public static void generateAmSch001Report(String buName, String reportFile, List<ExampleVO> voList) { File srcFile = new File("template.xls"); File destFile = new File("destFile.xls"); try { Workbook wb = Workbook.getWorkbook(srcFile); WritableWorkbook wwb = Workbook.createWorkbook(destFile, wb); WritableSheet wws = wwb.getSheet(0); int rowNum = 9; if (null != voList) { rowNum = 12; int sn = 1; WritableCellFormat wcs = new WritableCellFormat(); wcs.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); for (ExampleVO vo : voList) { int colNum = 1; label = new Label(colNum++, rowNum, "", wcs); wws.addCell(label); label = new Label(colNum++, rowNum, sn + "", wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, vo.getAt(), wcs); wws.addCell(label); label = new Label(colNum++, rowNum, "", wcs); wws.addCell(label); label = new Label(colNum++, rowNum, "", wcs); wws.addCell(label); rowNum++; sn++; } } wwb.write(); wwb.close(); wb.close(); } catch (IOException e) { e.printStackTrace(); } catch (RowsExceededException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } }
Created attachment 32244 [details] The Reason I found the reason, because the row is too many, if only a few row , the result is fine! So how to config?
if too many rows , the Java Excel API also not function porperty. So should be Excel's problem.
iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.PrintArea = "" iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.Zoom = false iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.ScaleWithDocHeaderFooter = true iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.PrintQuality = 600 iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.PaperSize = 8 //xlPaperA3 iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.Orientation = 2//xlLandscape iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.FitToPagesWide = 1 iExcel.ActiveWorkbook.WorkSheets.Item[1].PageSetup.FitToPagesTall = 99 In .Net , Setting above parameter is ok , so this is not a problem.