List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getHeader
@Override
public HSSFHeader getHeader()
From source file:com.krawler.esp.servlets.exportExcel.java
License:Open Source License
public void exportexcel(HttpServletResponse response, JSONObject jobj, java.util.Hashtable ht, String sheetTitle, String fileName, JSONArray hdr, JSONArray xlshdr, String heading, String[] xtypeArr, com.krawler.spring.exportFunctionality.exportDAOImpl exportDao) throws ServletException, IOException { try {/* w w w . ja v a2s . co m*/ response.setContentType("application/vnd.ms-excel"); if (!StringUtil.isNullOrEmpty(heading)) { fileName = heading + fileName; } response.setHeader("Content-Disposition", "attachement; filename=" + fileName + ".xls"); HSSFSheet sheet = wb.createSheet(sheetTitle); CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); HSSFHeader hh = sheet.getHeader(); int j = 1; int width = 0; int maxrowno = 0; HSSFRow row1 = sheet.createRow((short) maxrowno); HashMap hm = extractData(jobj); JSONArray jarr = (JSONArray) hm.get("data"); JSONObject tempObj; for (int k = 0; k < jarr.length(); k++) { tempObj = jarr.getJSONObject(k); HSSFRow row = sheet.createRow((short) j); int cellcount = 0; for (int i = 0; i < hdr.length(); i++) { Object str = tempObj.optString(hdr.getString(i), ""); try { if (xtypeArr.length > 0) { str = convertValue(tempObj.optString(hdr.getString(i), ""), xtypeArr[i]); } } catch (Exception e) { } if (ht.containsValue(hdr.getString(i))) { if (j == maxrowno + 1) { HSSFCell cell1 = row1.createCell(cellcount); cell1.setCellStyle(cs); width = xlshdr.getString(i).length() * 325; if (width > sheet.getColumnWidth(cellcount)) { sheet.setColumnWidth(cellcount, width); } HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFRichTextString hst = new HSSFRichTextString(xlshdr.getString(i)); hst.applyFont(font); cell1.setCellValue(hst); } HSSFCell cell = row.createCell(cellcount); cell.setCellStyle(cs); if (str instanceof Date) { cal.setTime((Date) str); cell.setCellValue(cal); cell.setCellStyle(this.dateCellStyle); width = 4500; } else if (str instanceof Number) { cell.setCellValue(((Number) str).doubleValue()); width = 4500; } else { String colvalue = str.toString(); cell.setCellValue(new HSSFRichTextString(colvalue)); width = colvalue.length() * 325; } width = Math.min(width, MAX_CELL_WIDTH); if (width > sheet.getColumnWidth(cellcount)) { sheet.setColumnWidth(cellcount, width); } cellcount++; } } j++; } ConfigReader cr = ConfigReader.getinstance(); String dirpath = cr.get("store"); String path = dirpath + "baitheader.png"; // this.addimage(path,HSSFWorkbook.PICTURE_TYPE_PNG, wb, sheet,0,0,0,0,0,0,12,4); if (true) { OutputStream out = response.getOutputStream(); wb.write(out); out.close(); } } catch (JSONException ex) { Logger.getLogger(exportExcel.class.getName()).log(Level.SEVERE, null, ex); } catch (Exception ex) { Logger.getLogger(exportExcel.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.pureinfo.srm.project.model.compile.helper.CompileExcelExportHelper.java
License:Open Source License
public void export(OutputStream _os, IExportGoods _goods, int _year) throws PureException { try {/*from ww w .ja va2 s . co m*/ // 1. to create sheet HSSFWorkbook workbook = new HSSFWorkbook(); style = workbook.createCellStyle(); style.setWrapText(true); HSSFSheet sheet = workbook.createSheet(); workbook.setSheetName(0, _goods.getName(), (short) 1); // change the print way to landscape sheet.getPrintSetup().setLandscape(true); // headLine is the true header of the page. // Workbook book = new CompileWorkBook(); // int index = book.addSSTString(HSSFHeader.font("", "Border") + // HSSFHeader.fontSize((short) 16) // + "\u6d59\u6c5f\u5927\u5b66" + _year // + // "\u5e74\u9ad8\u65b0\u529e\u65b0\u4e0a\u9879\u76ee\u8ba1\u5212\u9879\u76ee\u5355",true); HSSFHeader headLine = sheet.getHeader(); headLine.setCenter(HSSFHeader.font("", "Border") + HSSFHeader.fontSize((short) 16) + "" + _year + ""); // footer HSSFFooter footer = sheet.getFooter(); footer.setRight("." + HSSFFooter.page() + "."); footer.setLeft(this.dateFormat(new Date())); // 2. to export headers if (_goods.hasHeader()) { HSSFCellStyle headerStyle = getHeaderStyle(workbook); exportHeaders(sheet, _goods.getHeaders(), headerStyle); } // 3. to export data Object[] values; HSSFCellStyle dateStyle = getDateStyle(workbook); HSSFCellStyle doubleStyle = getDoubleStyle(workbook); int nRowNum = 1; Iterator iter = _goods.iterator(); while (iter.hasNext()) { values = _goods.unpackGoods(iter.next()); exportRow(sheet, values, nRowNum++, dateStyle, doubleStyle); } // 4. to output to stream workbook.write(_os); } catch (IOException ex) { throw new PureException(PureException.UNKNOWN, "export " + _goods.getName() + " to excel", ex); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-4] ? : ? ? ?(Header, Footer)? *///from w ww. j a v a2s .com @Test public void testModifyDocAttribute() throws Exception { try { log.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())); log.debug("Delete file...." + sb.toString()); } HSSFWorkbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); log.debug("testModifyCellContents after loadWorkbook...."); HSSFSheet sheet = wb.createSheet("doc test sheet"); HSSFRow row = sheet.createRow(1); HSSFCell cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString("Header/Footer Test")); // Header HSSFHeader 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 HSSFFooter footer = sheet.getFooter(); footer.setCenter(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -"); log.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())); ////////////////////////////////////////////////////////////////////////// // ? HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString()); HSSFSheet sheetT = wbT.getSheet("doc test sheet"); HSSFHeader 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()); HSSFFooter 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) { log.error(e.toString()); throw new Exception(e); } finally { log.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);//from w w w . j a v a 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 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. ja v a 2s. co 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) {//from ww w .java2 s . co m 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 ww . jav a 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 www. j a v a 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 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;// www . j av a 2 s. co m 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);/*w w w.j a v a 2 s . com*/ //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; } }