List of usage examples for org.apache.poi.ss.usermodel Header setCenter
void setCenter(String newCenter);
From source file:com.AllenBarr.CallSheetGenerator.Generator.java
License:Open Source License
public int generateSheet(File file, Contributor contrib) { //create workbook file final String fileName = file.toString(); final Workbook wb; if (fileName.endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else if (fileName.endsWith(".xls")) { wb = new HSSFWorkbook(); } else {// ww w .j a v a 2 s . c o m return 1; } //create sheet final Sheet sheet = wb.createSheet("Call Sheet"); final Header header = sheet.getHeader(); header.setCenter("Anderson for Iowa Call Sheet"); //add empty cells final Row[] row = new Row[22 + contrib.getDonationsLength()]; final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()]; for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) { row[i] = sheet.createRow((short) i); for (int j = 0; j < 6; j++) { cell[j][i] = row[i].createCell(j); } } //populate cells with data //column 1 cell[0][0].setCellValue(contrib.getName()); cell[0][3].setCellValue("Sex:"); cell[0][4].setCellValue("Party:"); cell[0][5].setCellValue("Phone #:"); cell[0][6].setCellValue("Home #:"); cell[0][7].setCellValue("Cell #:"); cell[0][8].setCellValue("Work #:"); cell[0][10].setCellValue("Email:"); cell[0][12].setCellValue("Employer:"); cell[0][13].setCellValue("Occupation:"); cell[0][15].setCellValue("Past Contact:"); cell[0][17].setCellValue("Notes:"); cell[0][21].setCellValue("Contribution History:"); //column 2 cell[1][3].setCellValue(contrib.getSex()); cell[1][4].setCellValue(contrib.getParty()); cell[1][5].setCellValue(contrib.getPhone()); cell[1][6].setCellValue(contrib.getHomePhone()); cell[1][7].setCellValue(contrib.getCellPhone()); cell[1][8].setCellValue(contrib.getWorkPhone()); cell[1][9].setCellValue("x" + contrib.getWorkExtension()); cell[1][10].setCellValue(contrib.getEmail()); cell[1][12].setCellValue(contrib.getEmployer()); cell[1][13].setCellValue(contrib.getOccupation()); cell[1][17].setCellValue(contrib.getNotes()); //column 4 cell[3][3].setCellValue("Salutation:"); cell[3][4].setCellValue("Age:"); cell[3][5].setCellValue("Spouse:"); cell[3][7].setCellValue("Address:"); cell[3][10].setCellValue("TARGET:"); //column 5 cell[4][0].setCellValue("VANID:"); cell[4][3].setCellValue(contrib.getSalutation()); cell[4][4].setCellValue(contrib.getAge()); cell[4][5].setCellValue(contrib.getSpouse()); cell[4][7].setCellValue(contrib.getStreetAddress()); cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip()); //column 6 cell[5][0].setCellValue(contrib.getVANID()); //contribution cells for (int i = 0; i < contrib.getDonationsLength(); i++) { cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate()); cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient()); cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount()); } //format cells //Name cell sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); final CellStyle leftBoldUnderline14Style = wb.createCellStyle(); final Font boldUnderline14Font = wb.createFont(); boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldUnderline14Font.setUnderline(Font.U_SINGLE); boldUnderline14Font.setFontHeightInPoints((short) 14); boldUnderline14Font.setFontName("Garamond"); leftBoldUnderline14Style.setFont(boldUnderline14Font); leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT); cell[0][0].setCellStyle(leftBoldUnderline14Style); //field name cells final CellStyle rightBold10Style = wb.createCellStyle(); final Font bold10Font = wb.createFont(); bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD); bold10Font.setFontHeightInPoints((short) 10); bold10Font.setFontName("Garamond"); rightBold10Style.setFont(bold10Font); rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 3; i < 22; i++) { cell[0][i].setCellStyle(rightBold10Style); } sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1)); for (int i = 3; i < 11; i++) { cell[3][i].setCellStyle(rightBold10Style); } cell[4][0].setCellStyle(rightBold10Style); //field content cells final CellStyle left10Style = wb.createCellStyle(); final Font garamond10Font = wb.createFont(); garamond10Font.setFontHeightInPoints((short) 10); garamond10Font.setFontName("Garamond"); left10Style.setFont(garamond10Font); left10Style.setAlignment(CellStyle.ALIGN_LEFT); for (int i = 3; i < 5; i++) { cell[1][i].setCellStyle(left10Style); } //phone number cells final CellStyle phoneStyle = wb.createCellStyle(); phoneStyle.setFont(garamond10Font); phoneStyle.setAlignment(CellStyle.ALIGN_LEFT); final CreationHelper createHelper = wb.getCreationHelper(); phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####")); for (int i = 5; i < 9; i++) { cell[1][i].setCellStyle(phoneStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2)); } cell[1][9].setCellStyle(left10Style); //email through past contact for (int i = 10; i < 16; i++) { cell[1][i].setCellStyle(left10Style); } //notes CellStyle noteStyle = wb.createCellStyle(); noteStyle.cloneStyleFrom(left10Style); noteStyle.setWrapText(true); cell[1][17].setCellStyle(noteStyle); //column E for (int i = 3; i < 11; i++) { cell[4][i].setCellStyle(left10Style); } //VanID Cell final CellStyle right10Style = wb.createCellStyle(); right10Style.setFont(garamond10Font); right10Style.setAlignment(CellStyle.ALIGN_RIGHT); cell[5][0].setCellStyle(right10Style); //Notes cell sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5)); //contribution cells final CellStyle date10Style = wb.createCellStyle(); date10Style.setFont(garamond10Font); date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); date10Style.setBorderBottom(CellStyle.BORDER_THIN); date10Style.setBorderTop(CellStyle.BORDER_THIN); date10Style.setBorderLeft(CellStyle.BORDER_THIN); date10Style.setBorderRight(CellStyle.BORDER_THIN); final CellStyle contributionStyle = wb.createCellStyle(); contributionStyle.cloneStyleFrom(left10Style); contributionStyle.setBorderBottom(CellStyle.BORDER_THIN); contributionStyle.setBorderTop(CellStyle.BORDER_THIN); contributionStyle.setBorderLeft(CellStyle.BORDER_THIN); contributionStyle.setBorderRight(CellStyle.BORDER_THIN); final CellStyle money10Style = wb.createCellStyle(); money10Style.setFont(garamond10Font); money10Style.setDataFormat( createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); money10Style.setBorderBottom(CellStyle.BORDER_THIN); money10Style.setBorderTop(CellStyle.BORDER_THIN); money10Style.setBorderLeft(CellStyle.BORDER_THIN); money10Style.setBorderRight(CellStyle.BORDER_THIN); for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) { cell[0][i].setCellStyle(date10Style); cell[1][i].setCellStyle(contributionStyle); cell[2][i].setCellStyle(contributionStyle); cell[3][i].setCellStyle(contributionStyle); cell[4][i].setCellStyle(contributionStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4)); cell[5][i].setCellStyle(money10Style); } //resize columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); try { FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { return 1; } catch (IOException ex) { return 1; } return 0; }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-4] ? : ? ? ?(Header, Footer)? *//*ww w.j a v a 2s. c o m*/ @Test public void testModifyDocAttribute() throws Exception { try { LOGGER.debug("testModifyDocAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyDocAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString()); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.createSheet("doc test sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString("Header/Footer Test")); // Header Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right Stencil-Normal Italic font and size 16"); // Footer Footer footer = sheet.getFooter(); footer.setCenter(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -"); LOGGER.debug("Style is ...{}", HSSFHeader.font("Fixedsys", "Normal"), HSSFHeader.fontSize((short) 12) + "- 1 -"); footer.setLeft("Left Footer"); footer.setRight("Right Footer"); // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); assertTrue(EgovFileUtil.isExistsFile(sb.toString())); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString()); Sheet sheetT = wbT.getSheet("doc test sheet"); Header headerT = sheetT.getHeader(); assertEquals("Center Header", headerT.getCenter()); assertEquals("Left Header", headerT.getLeft()); assertEquals(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right Stencil-Normal Italic font and size 16", headerT.getRight()); Footer footerT = sheetT.getFooter(); assertEquals("Right Footer", footerT.getRight()); assertEquals("Left Footer", footerT.getLeft()); assertEquals(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -", footerT.getCenter()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyDocAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-4] ? : ? ? ?(Header, Footer)? *///w w w. jav a 2 s .co m @Test public void testModifyDocAttribute() throws Exception { try { LOGGER.debug("testModifyDocAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyDocAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.createSheet("doc test sheet"); Row row = sheet.createRow(1); Cell cell = row.createCell(1); cell.setCellValue(new XSSFRichTextString("Header/Footer Test")); // Header Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight(XSSFOddHeader.stripFields("&IRight Stencil-Normal Italic font and size 16")); // Footer Footer footer = (XSSFOddFooter) sheet.getFooter(); footer.setCenter(XSSFOddHeader.stripFields("Fixedsys")); LOGGER.debug("Style is ... {}", XSSFOddHeader.stripFields("Fixedsys")); footer.setLeft("Left Footer"); footer.setRight("Right Footer"); // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); assertTrue(EgovFileUtil.isExistsFile(sb.toString())); ////////////////////////////////////////////////////////////////////////// // ? Workbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("doc test sheet"); Header headerT = sheetT.getHeader(); assertEquals("Center Header", headerT.getCenter()); assertEquals("Left Header", headerT.getLeft()); assertEquals(XSSFOddHeader.stripFields("Right Stencil-Normal Italic font and size 16"), headerT.getRight()); Footer footerT = sheetT.getFooter(); assertEquals("Right Footer", footerT.getRight()); assertEquals("Left Footer", footerT.getLeft()); assertEquals(XSSFOddHeader.stripFields("Fixedsys"), footerT.getCenter()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyDocAttribute end...."); } }
From source file:excel.FileExcel.java
public File excel_create_katalog_update(ArrayList<UpdKatalog> newKatalogUpdate) { if (newKatalogUpdate.size() != 0) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "KatalogDiff_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + "_" + time.format(newKatalogUpdate.get(0).update_date_new) + ".xls"; File ExcelKatalogDiff = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(true); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName);//www . j av a 2s .c o m //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL PERUBAHAN HARGA"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5)); // create file info header = sheet.createRow(3); cell = header.createCell(2); cell.setCellValue("Tanggal Update : "); cell = header.createCell(3); cell.setCellValue(fmt.format(newKatalogUpdate.get(0).update_date_new)); // create the header datastyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(6); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Barang"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Harga TPG Lama"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Tanggal Update Lama"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Harga TPG Baru"); cell = header.createCell(5); cell.setCellStyle(headerstyle); cell.setCellValue("Tanggal Update Baru"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < newKatalogUpdate.size(); i++) { header = sheet.createRow(i + 7); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(newKatalogUpdate.get(i).kode_barang); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_old); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_old)); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_new); cell = header.createCell(5); cell.setCellStyle(datastyle); cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_new)); } try { // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls"; FileOutputStream out = new FileOutputStream(ExcelKatalogDiff); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelKatalogDiff = null; } catch (IOException e) { e.printStackTrace(); ExcelKatalogDiff = null; } return ExcelKatalogDiff; } else return null; }
From source file:excel.FileExcel.java
public File excel_create_diff_upd_stock(ArrayList<DiffUpdStock> diffinput, Date currtime) { if (diffinput.size() != 0) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "DailyDiff_" + fmt.format(currtime) + "_" + time.format(currtime) + ".xls"; File ExcelDailyDiff = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(false); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName);/*from w w w.j ava 2s .c o m*/ //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL UPDATE DAILY STOK"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4)); // create file info header = sheet.createRow(3); cell = header.createCell(2); cell.setCellValue("Tanggal Stock : "); cell = header.createCell(3); cell.setCellValue(currtime); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(6); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Unedited Kode Barang"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Unedited Jumlah Barang"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Edited Kode Barang"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Edited Jumlah Barang"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < diffinput.size(); i++) { header = sheet.createRow(i + 7); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(diffinput.get(i).oriKodeBarang); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(diffinput.get(i).oriJumlahBarang); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(diffinput.get(i).edtKodeBarang); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(diffinput.get(i).oriJumlahBarang); } try { // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls"; FileOutputStream out = new FileOutputStream(ExcelDailyDiff); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelDailyDiff = null; } catch (IOException e) { e.printStackTrace(); ExcelDailyDiff = null; } return ExcelDailyDiff; } return null; }
From source file:excel.FileExcel.java
public File excel_create_order_pusat_coming(ArrayList<ComingOrderReportData> comingorder, Date comingorderdate) {/* w w w.j av a2 s . c om*/ if (comingorder.size() != 0) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "ComingOrder_" + fmt.format(comingorderdate) + "_" + time.format(comingorderdate) + ".xls"; File ExcelComingOrder = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(false); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName); //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL COMING ORDER"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5)); // create file info header = sheet.createRow(3); cell = header.createCell(2); cell.setCellValue("Tanggal Stock : "); cell = header.createCell(3); cell.setCellValue(fmt.format(comingorderdate)); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(6); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Konter"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Barang"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Jumlah"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Nama Barang"); cell = header.createCell(5); cell.setCellStyle(headerstyle); cell.setCellValue("Kategori"); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // Sorting Collections.sort(comingorder, new Comparator<ComingOrderReportData>() { @Override public int compare(ComingOrderReportData data2, ComingOrderReportData data1) { return data1.nama_barang.compareTo(data2.nama_barang); } }); for (int i = 0; i < comingorder.size(); i++) { header = sheet.createRow(i + 7); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(comingorder.get(i).kode_konter); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(comingorder.get(i).kode_barang); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(comingorder.get(i).jumlah_barang); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(comingorder.get(i).nama_barang); cell = header.createCell(5); cell.setCellStyle(datastyle); cell.setCellValue(comingorder.get(i).kategori); } sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); try { // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls"; FileOutputStream out = new FileOutputStream(ExcelComingOrder); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelComingOrder = null; } catch (IOException e) { e.printStackTrace(); ExcelComingOrder = null; } return ExcelComingOrder; } else { return null; } }
From source file:excel.FileExcel.java
public File excel_create_popular(ArrayList<PopulerData> PopulerItem, Date startdate, Date enddate) { if (PopulerItem.size() != 0) { String fileName = "PopularItem_" + fmt.format(startdate) + "-" + fmt.format(enddate) + ".xls"; File ExcelPopular = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(false); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName);//from w w w . ja va 2 s.c o m //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL ORDER POPULAR"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4)); // create file info header = sheet.createRow(3); cell = header.createCell(1); cell.setCellValue("Tanggal : "); cell = header.createCell(2); cell.setCellValue(fmt.format(startdate)); cell = header.createCell(3); cell.setCellValue(" - "); cell = header.createCell(4); cell.setCellValue(fmt.format(enddate)); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(6); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Barang"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Jumlah Order"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Jumlah Konter"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Stok"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < PopulerItem.size(); i++) { header = sheet.createRow(i + 7); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(PopulerItem.get(i).kode_barang); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(PopulerItem.get(i).jumlah_order); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(PopulerItem.get(i).jumlah_konter); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(PopulerItem.get(i).stock); } try { FileOutputStream out = new FileOutputStream(ExcelPopular); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelPopular = null; } catch (IOException e) { e.printStackTrace(); ExcelPopular = null; } return ExcelPopular; } else { return null; } }
From source file:excel.FileExcel.java
public File excel_create_kirim_barang(ArrayList<ItemReadyData> ItemsReadyData, Date currentdate) { if (ItemsReadyData.size() != 0) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "Kirim_" + fmt.format(currentdate) + "_" + time.format(currentdate) + "_konter_" + ItemsReadyData.get(0).kode_konter + ".xls"; File ExcelKirimBarang = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(true); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName);/*from w w w . ja v a 2s. c om*/ //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL KIRIM BARANG"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 9)); // create file info // create file info header = sheet.createRow(3); cell = header.createCell(2); cell.setCellValue("Tanggal : "); cell = header.createCell(3); cell.setCellValue(fmt.format(currentdate)); header = sheet.createRow(4); cell = header.createCell(2); cell.setCellValue("Konter : "); cell = header.createCell(3); cell.setCellValue(ItemsReadyData.get(0).kode_konter); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(7); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Barang"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Jumlah"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Nama Barang"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Kategori"); cell = header.createCell(5); cell.setCellStyle(headerstyle); cell.setCellValue("Harga TPG"); cell = header.createCell(6); cell.setCellStyle(headerstyle); cell.setCellValue("Discount"); cell = header.createCell(7); cell.setCellStyle(headerstyle); cell.setCellValue("Harga Net"); cell = header.createCell(8); cell.setCellStyle(headerstyle); cell.setCellValue("Total TPG"); cell = header.createCell(9); cell.setCellStyle(headerstyle); cell.setCellValue("Total Net"); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); HSSFDataFormat df = workbook.createDataFormat(); datastyle.setDataFormat(df.getFormat("#,###")); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); double net = 0; double total_tpg = 0; double total_net = 0; double sum_total_tpg = 0; double sum_total_net = 0; int j; // fill the data for (j = 0; j < ItemsReadyData.size(); j++) { net = (double) ItemsReadyData.get(j).harga_tpg * (100.0 - (double) ItemsReadyData.get(j).disc_member) / 100.0; total_tpg = (double) ItemsReadyData.get(j).harga_tpg * (double) ItemsReadyData.get(j).jumlah_barang; total_net = (double) net * (double) ItemsReadyData.get(j).jumlah_barang; sum_total_tpg += total_tpg; sum_total_net += total_net; header = sheet.createRow(8 + j); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(ItemsReadyData.get(j).kode_barang); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(ItemsReadyData.get(j).jumlah_barang); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(ItemsReadyData.get(j).nama_barang); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(ItemsReadyData.get(j).kategori); cell = header.createCell(5); cell.setCellStyle(datastyle); cell.setCellValue(ItemsReadyData.get(j).harga_tpg); cell = header.createCell(6); cell.setCellStyle(datastyle); cell.setCellValue(ItemsReadyData.get(j).disc_member); cell = header.createCell(7); cell.setCellStyle(datastyle); cell.setCellValue(net); cell = header.createCell(8); cell.setCellStyle(datastyle); cell.setCellValue(total_tpg); cell = header.createCell(9); cell.setCellStyle(datastyle); cell.setCellValue(total_net); } sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(9); datastyle.setFont(boldfont); header = sheet.createRow(j + 8); cell = header.createCell(7); cell.setCellStyle(datastyle); cell.setCellValue("TOTAL"); cell = header.createCell(8); cell.setCellStyle(datastyle); cell.setCellValue(sum_total_tpg); cell = header.createCell(9); cell.setCellStyle(datastyle); cell.setCellValue(sum_total_net); try { FileOutputStream out = new FileOutputStream(ExcelKirimBarang); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelKirimBarang = null; } catch (IOException e) { e.printStackTrace(); ExcelKirimBarang = null; } return ExcelKirimBarang; } else { return null; } }
From source file:excel.FileExcel.java
public File excel_create_cari_gudang(ArrayList<CariGudangReportData> CariGudang, Date waktuprint) { // find number of counter, save in Counter Index if (!CariGudang.isEmpty()) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "CariGudang_" + fmt.format(waktuprint) + "_" + time.format(waktuprint) + ".xls"; File FileCariGudang = new File(fileName); HSSFWorkbook workbook;//from w ww. ja v a 2s . c om HSSFSheet sheet; workbook = new HSSFWorkbook(); sheet = workbook.createSheet(); // set page HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setLandscape(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); sheet.setFitToPage(true); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName); //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL CARI GUDANG"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4)); // create file info header = sheet.createRow(4); cell = header.createCell(1); cell.setCellValue("Tanggal : "); cell = header.createCell(2); cell.setCellValue(fmt.format(waktuprint)); header = sheet.createRow(5); cell = header.createCell(1); cell.setCellValue("Jam : "); cell = header.createCell(2); cell.setCellValue(time.format(waktuprint)); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(7); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Order"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Konter"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Barang"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Jumlah"); cell = header.createCell(5); cell.setCellStyle(headerstyle); cell.setCellValue("Nama Barang"); cell = header.createCell(6); cell.setCellStyle(headerstyle); cell.setCellValue("Kategori"); cell = header.createCell(7); cell.setCellStyle(headerstyle); cell.setCellValue("HargaTPG"); cell = header.createCell(8); cell.setCellStyle(headerstyle); cell.setCellValue("Disc"); cell = header.createCell(9); cell.setCellStyle(headerstyle); cell.setCellValue("Harga Net"); cell = header.createCell(10); cell.setCellStyle(headerstyle); cell.setCellValue("Total Net"); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); HSSFDataFormat df = workbook.createDataFormat(); datastyle.setDataFormat(df.getFormat("#,###")); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); int row_num = 0; double net = 0; double total_tpg = 0; double total_net = 0; double sum_total_tpg = 0; double sum_total_net = 0; int j; for (j = 0; j < CariGudang.size(); j++) { net = (double) CariGudang.get(j).harga_tpg * (100.0 - (double) CariGudang.get(j).disc) / 100.0; total_net = (double) net * (double) CariGudang.get(j).jumlah; sum_total_tpg += total_tpg; sum_total_net += total_net; header = sheet.createRow(8 + row_num); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).kode_order); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).kode_konter); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).kode_barang); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).jumlah); cell = header.createCell(5); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).nama_barang); cell = header.createCell(6); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).kategori); cell = header.createCell(7); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).harga_tpg); cell = header.createCell(8); cell.setCellStyle(datastyle); cell.setCellValue(CariGudang.get(j).disc); cell = header.createCell(9); cell.setCellStyle(datastyle); cell.setCellValue(net); cell = header.createCell(10); cell.setCellStyle(datastyle); cell.setCellValue(total_net); row_num++; } datastyle.setFont(boldfont); header = sheet.createRow(j + 8); cell = header.createCell(9); cell.setCellStyle(datastyle); cell.setCellValue("TOTAL"); cell = header.createCell(10); cell.setCellStyle(datastyle); cell.setCellValue(sum_total_net); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(9); sheet.autoSizeColumn(10); try { FileOutputStream out = new FileOutputStream(FileCariGudang); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); FileCariGudang = null; } catch (IOException e) { e.printStackTrace(); FileCariGudang = null; } return FileCariGudang; } else { return null; } }
From source file:excel.FileExcel.java
public File excel_create_dead_style(ArrayList<DeadStyleData> DSData, Date currentdate, int discDS) { if (DSData.size() != 0) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "DeadStyle_" + fmt.format(currentdate) + "_" + time.format(currentdate) + ".xls"; File ExcelDeadStyle = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(false); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName);//from www.j ava 2s . c o m //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL DEAD STYLE"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 9)); // create file info // create file info header = sheet.createRow(3); cell = header.createCell(2); cell.setCellValue("Tanggal : "); cell = header.createCell(3); cell.setCellValue(fmt.format(currentdate)); header = sheet.createRow(4); cell = header.createCell(2); cell.setCellValue("Jam : "); cell = header.createCell(3); cell.setCellValue(time.format(currentdate)); header = sheet.createRow(5); cell = header.createCell(2); cell.setCellValue("Diskon : "); cell = header.createCell(3); cell.setCellValue(discDS); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(7); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Kode Barang"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Nama Barang"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Jumlah"); cell = header.createCell(4); cell.setCellStyle(headerstyle); cell.setCellValue("Kategori"); cell = header.createCell(5); cell.setCellStyle(headerstyle); cell.setCellValue("Harga TPG"); cell = header.createCell(6); cell.setCellStyle(headerstyle); cell.setCellValue("Discount"); cell = header.createCell(7); cell.setCellStyle(headerstyle); cell.setCellValue("Harga Net"); cell = header.createCell(8); cell.setCellStyle(headerstyle); cell.setCellValue("Total TPG"); cell = header.createCell(9); cell.setCellStyle(headerstyle); cell.setCellValue("Total Net"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(9); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); double net = 0; double total_tpg = 0; double total_net = 0; double sum_total_tpg = 0; double sum_total_net = 0; int j; double dsdisc; // fill the data for (j = 0; j < DSData.size(); j++) { dsdisc = (DSData.get(j).disc_member == 10) ? 0 : discDS; net = (double) DSData.get(j).harga_tpg * (100.0 - dsdisc) / 100.0; total_tpg = (double) DSData.get(j).harga_tpg * (double) DSData.get(j).Jumlah; total_net = (double) net * (double) DSData.get(j).Jumlah; sum_total_tpg += total_tpg; sum_total_net += total_net; header = sheet.createRow(8 + j); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(DSData.get(j).kode_barang); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(DSData.get(j).nama_barang); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(DSData.get(j).Jumlah); cell = header.createCell(4); cell.setCellStyle(datastyle); cell.setCellValue(DSData.get(j).kategori); cell = header.createCell(5); cell.setCellStyle(datastyle); cell.setCellValue(DSData.get(j).harga_tpg); cell = header.createCell(6); cell.setCellStyle(datastyle); cell.setCellValue(DSData.get(j).disc_member); cell = header.createCell(7); cell.setCellStyle(datastyle); cell.setCellValue(net); cell = header.createCell(8); cell.setCellStyle(datastyle); cell.setCellValue(total_tpg); cell = header.createCell(9); cell.setCellStyle(datastyle); cell.setCellValue(total_net); } datastyle.setFont(boldfont); header = sheet.createRow(j + 8); cell = header.createCell(7); cell.setCellStyle(datastyle); cell.setCellValue("TOTAL"); cell = header.createCell(8); cell.setCellStyle(datastyle); cell.setCellValue(sum_total_tpg); cell = header.createCell(9); cell.setCellStyle(datastyle); cell.setCellValue(sum_total_net); try { FileOutputStream out = new FileOutputStream(ExcelDeadStyle); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelDeadStyle = null; } catch (IOException e) { e.printStackTrace(); ExcelDeadStyle = null; } return ExcelDeadStyle; } else { return null; } }