Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package ua.com.ecotep.unianalysis.export; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.time.LocalDate; import java.util.List; import javafx.collections.ObservableList; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPrintSetup; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; import ua.com.ecotep.unianalysis.analysis.AnProperties; import ua.com.ecotep.unianalysis.dao.model.ClientBean; import ua.com.ecotep.unianalysis.utils.DateConverters; /** * * @author Andrey */ public class XLSDataExport implements DataExport { @Override public void exportData(String selectedFile, AnProperties props, ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception { if (selectedFile == null) { return; } System.setProperty("java.awt.headless", "true"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); ps.setLandscape(true); HSSFCellStyle cellStyleT = wb.createCellStyle(); HSSFFont font1 = wb.createFont(); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 8); cellStyleT.setFont(font1); int rnumber = 0; HSSFRow row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); HSSFCell cell = row.createCell(0); cell.setCellValue( new HSSFRichTextString(" ??? ??? - " + DateConverters.getDateToStr(LocalDate.now()))); cell.setCellStyle(cellStyleT); rnumber++; if (props != null) { String val = " : " + DateConverters.getDateToStr(props.getDateFrom()) + " " + DateConverters.getDateToStr(props.getDateTo()); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; //---------- if (props.isSalMode()) { String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE ? " " : " "; val = "? " + type + "| " + props.getSaler().getNameSaler(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) { type = props.isSalDirectSales() ? "? " : " "; val = " : " + type; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.isSalFixedDepartment()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.isGoodMode()) { if (props.getGoodClassLev0() != null) { val = "? 1: " + props.getGoodClassLev0().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev1() != null) { val = "? 2: " + props.getGoodClassLev1().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev2() != null) { val = "? 3: " + props.getGoodClassLev2().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev3() != null) { val = "? 4: " + props.getGoodClassLev3().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) { val = " : " + props.getGoodCustomSearch(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) { val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) { val = "+: "; for (LocalDate ld : props.getGoodsIndateLst()) { val += DateConverters.getDateToStr(ld) + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (!props.getClLst().isEmpty()) { if (props.isClIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = "+: "; for (ClientBean cb : props.getClLst()) { val += cb.getClientCl() + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.getVLst().isEmpty()) { if (props.isVIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = ""; for (String v : props.getVLst()) { val += v + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } //---------- HSSFCellStyle cellStyleH = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleH.setFont(font); cellStyleH.setWrapText(true); cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setBorderLeft((short) 1); cellStyleH.setBorderRight((short) 1); cellStyleH.setBorderTop((short) 1); cellStyleH.setBorderBottom((short) 1); HSSFCellStyle cellStyleHh = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleHh.setFont(font); cellStyleHh.setWrapText(true); cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleHh.setBorderLeft((short) 1); cellStyleHh.setBorderRight((short) 1); cellStyleHh.setBorderTop((short) 1); cellStyleHh.setBorderBottom((short) 1); //filling table HSSFCellStyle cellStyleN = wb.createCellStyle(); cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleN.setBorderLeft((short) 1); cellStyleN.setBorderRight((short) 1); cellStyleN.setBorderTop((short) 1); cellStyleN.setBorderBottom((short) 1); HSSFCellStyle cellStyleI = wb.createCellStyle(); cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setBorderLeft((short) 1); cellStyleI.setBorderRight((short) 1); cellStyleI.setBorderTop((short) 1); cellStyleI.setBorderBottom((short) 1); HSSFCellStyle cellStyleD = wb.createCellStyle(); cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); HSSFDataFormat df = wb.createDataFormat(); cellStyleD.setDataFormat(df.getFormat("#,##0.0")); cellStyleD.setBorderLeft((short) 1); cellStyleD.setBorderRight((short) 1); cellStyleD.setBorderTop((short) 1); cellStyleD.setBorderBottom((short) 1); HSSFCellStyle cellStyleP = wb.createCellStyle(); cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setDataFormat(df.getFormat("0.0\\%")); cellStyleP.setBorderLeft((short) 1); cellStyleP.setBorderRight((short) 1); cellStyleP.setBorderTop((short) 1); cellStyleP.setBorderBottom((short) 1); // filling column headers row = sheet.createRow(rnumber); String rowTitle = null; row = sheet.createRow(rnumber); row.setHeightInPoints(40); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(cellStyleH); for (int t = 0; t < columnTitles.size(); t++) { cell = row.createCell(t + 1); cell.setCellValue(new HSSFRichTextString(columnTitles.get(t))); cell.setCellStyle(cellStyleH); } // filling table with data rnumber++; for (ObservableList<Object> line : exportData) { row = sheet.createRow(rnumber); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString((String) line.get(0))); cell.setCellStyle(cellStyleN); for (int i = 1; i < line.size(); i++) { Double val = (Double) line.get(i); cell = row.createCell(i); cell.setCellStyle(cellStyleD); cell.setCellValue(val); } rnumber++; } for (int t = 0; t < columnTitles.size(); t++) { sheet.autoSizeColumn((short) t); } saveWorkBook(wb, selectedFile); execute(selectedFile); } private void createHeaderCell(HSSFSheet sheet, int rnumber, String val, HSSFCellStyle cellStyleT) { HSSFRow row; HSSFCell cell; row = sheet.createRow(rnumber); sheet.addMergedRegion(new CellRangeAddress(rnumber, rnumber, 0, 10)); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(val)); cell.setCellStyle(cellStyleT); } private void saveWorkBook(HSSFWorkbook wb, String filePath) throws IOException { if (filePath == null) { throw new NullPointerException("Can't write file, file path is null !"); } FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(new File(filePath)); wb.write(fileOut); } finally { try { fileOut.close(); } catch (IOException ex) { } } } private void execute(String filePath) throws IOException { String[] commands = { "cmd", "/c", "start", "\"XLSTitle\"", filePath }; Runtime.getRuntime().exec(commands); } }