List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportKalenderExcel.java
License:Open Source License
/** * //from w w w. j av a 2s . co m * @throws FileNotFoundException * @throws IOException */ public void write() throws FileNotFoundException, IOException { Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); HSSFWorkbook wb = new HSSFWorkbook(); Map<String, HSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month HSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); HSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); HSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); // sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles HSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth((i * 2), (5 * 256)); //the column is 5 characters wide sheet.setColumnWidth((i * 2 + 1), (13 * 256)); //the column is 13 characters wide //sheet.addMergedRegion(new Region(1, (short) 1, i*2, (short) (i * 2 + 1))); sheet.addMergedRegion(new CellRangeAddress(1, i * 2, 1, (i * 2 + 1))); // TODO Test HSSFCell monthCell = monthRow.createCell((i * 2)); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { HSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { HSSFCell dayCell_1 = row.createCell((i * 2)); HSSFCell dayCell_2 = row.createCell((i * 2 + 1)); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream(this.filename); wb.write(out); out.close(); }
From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java
License:BSD License
public void testGenerateEXCEL() throws Exception { HealthcareSite hcs = healthcareSiteDao.getById(1100); DateFormat format = new SimpleDateFormat("MM/dd/yyyy"); Date startDate = (Date) format.parse("01/11/1990"); Date endDate = (Date) format.parse("01/01/2007"); String grantNumber = "GRANT-NO 1232"; Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate); String reportingSource = healthcareSiteDao.getById(1000).getName(); summary3Report.setReportingSource(reportingSource); summaryReportService.buildSummary3Report(summary3Report); String xmlString = summaryReportService.generateXML(summary3Report); File file = new File("testReport.xml"); FileWriter fileWriter = new FileWriter(file); fileWriter.write(xmlString);/*from ww w. jav a 2 s . co m*/ fileWriter.flush(); fileWriter.close(); System.out.println(xmlString); // creating the workbook and the spreadsheet try { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); InputStream inputStream = new FileInputStream(file); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls"); FileOutputStream output = new FileOutputStream(outputFile); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java
License:BSD License
public void generateExcel(String summary3ReportXml, String file) throws Exception { // creating the workbook and the spreadsheet try {//from www . ja va2s .c o m DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); String fileName = ""; fileName = file; InputStream inputStream = new FileInputStream((fileName + ".xml")); Document document = builder.parse(inputStream); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report"); PrintSetup printSetup = spreadSheet.getPrintSetup(); printSetup.setLandscape(true); spreadSheet.setFitToPage(true); spreadSheet.setHorizontallyCenter(true); spreadSheet.setColumnWidth((short) 0, (short) (60 * 256)); spreadSheet.setColumnWidth((short) 1, (short) (15 * 256)); spreadSheet.setColumnWidth((short) 2, (short) (30 * 256)); HSSFRow titleRow = spreadSheet.createRow(0); HSSFCell titleCell = titleRow.createCell((short) 0); titleRow.setHeightInPoints(40); HSSFCellStyle titleCellStyle = wb.createCellStyle(); titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER); titleCell.setCellStyle(titleCellStyle); String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null) ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue() : ""; titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols" + " " + nullSafeGrantNumber); HSSFRow orgRow = spreadSheet.createRow(1); orgRow.setHeightInPoints(30); HSSFCell organizationCell = orgRow.createCell((short) 0); HSSFCellStyle orgCellStyle = wb.createCellStyle(); orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); orgCellStyle.setFont(font); organizationCell.setCellStyle(orgCellStyle); organizationCell .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0))) .getElementsByTagName("name").item(0).getFirstChild().getNodeValue()); HSSFRow reportingPeriodRow = spreadSheet.createRow(2); HSSFFont reportingPeriodFont = wb.createFont(); reportingPeriodFont.setFontHeightInPoints((short) 9); HSSFCellStyle reportingPeriodStyle = wb.createCellStyle(); reportingPeriodStyle.setFont(reportingPeriodFont); reportingPeriodRow.setHeightInPoints(20); HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0); reportingPeriodCell.setCellStyle(titleCellStyle); reportingPeriodCell.setCellValue("Reporting Period " + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - " + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue())); // creating the first row of table the table header HSSFRow row = spreadSheet.createRow(3); HSSFCell tableHeaderCell1 = row.createCell((short) 0); HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle(); tableHeaderCellStyle1.setWrapText(true); tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle1.setFont(font); tableHeaderCell1.setCellStyle(tableHeaderCellStyle1); tableHeaderCell1.setCellValue("Disease Site"); // creating table header 2nd & 3rd cells HSSFCell tableHeaderCell2 = row.createCell((short) 1); HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle(); tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle2.setWrapText(true); tableHeaderCell2.setCellStyle(tableHeaderCellStyle2); tableHeaderCell2.setCellValue("Newly Registered Patients"); HSSFCell tableHeaderCell3 = row.createCell((short) 2); HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle(); tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); tableHeaderCellStyle3.setWrapText(true); tableHeaderCell3.setCellStyle(tableHeaderCellStyle3); tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols"); NodeList nodeList = document.getElementsByTagName("reportData"); HSSFCellStyle tableCellStyle = wb.createCellStyle(); tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2")); spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3")); for (int i = 4; i < nodeList.getLength() + 4; i++) { row = spreadSheet.createRow(i); HSSFCell cell = row.createCell((short) 0); if (i == (4 + nodeList.getLength() - 1)) { HSSFCellStyle totalCellStyle = wb.createCellStyle(); totalCellStyle.setFont(font); totalCellStyle.setRightBorderColor((short) 10); totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); cell.setCellStyle(totalCellStyle); } else { cell.setCellStyle(tableCellStyle); } cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0)) .getAttribute("name")); cell = row.createCell((short) 1); cell.setCellStyle(tableCellStyle); cell.setCellValue(""); cell = row.createCell((short) 2); cell.setCellStyle(tableCellStyle); cell.setCellValue( ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3))) .getFirstChild().getNodeValue()); } FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel"))); wb.write(output); output.flush(); output.close(); } catch (IOException e) { e.printStackTrace(); } catch (ParserConfigurationException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); } }
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 ww . ja va 2 s . 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 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. 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) {// ww w . j ava 2 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);// ww w.j ava 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. j a va 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 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.j a v a2s.c o 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);//from ww w . j a v a 2 s. 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 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; } }