/** * @param wb * @param reviewerSheetName */ public static void create(Workbook wb, String sheetName) { int sheetNum = wb.getSheetIndex(sheetName); if (sheetNum >= 0) { wb.removeSheetAt(sheetNum); } Sheet sheet = wb.createSheet(sheetName); CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb); CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb); Row row = sheet.createRow(0); for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) { sheet.setColumnWidth(i, COL_WIDTH * 256); sheet.setDefaultColumnStyle(i, defaultStyle); Cell cell = row.createCell(i); cell.setCellStyle(headerStyle); } }
public static void create(Workbook wb, String sheetName) { int sheetNum = wb.getSheetIndex(sheetName); if (sheetNum >= 0) { wb.removeSheetAt(sheetNum); } Sheet sheet = wb.createSheet(sheetName); CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb); CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb); Row row = sheet.createRow(0); for (int i = 0; i < HEADER_TITLES.length; i++) { sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256); sheet.setDefaultColumnStyle(i, defaultStyle); Cell cell = row.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(HEADER_TITLES[i]); } }
public void doDownload() throws Throwable { if (StringUtils.isBlank(selectedDateFrom) || StringUtils.isBlank(selectedDateTo)) { MessageUtils.getInstance().addErrorMessage("msgDialogDL", "Please select date"); return; } if (selectedDateFrom.compareTo(selectedDateTo) < 0) { try { Date cycleFrom = DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, selectedDateFrom); Date cycleTo = DateUtil.convStringToDate(SIMPLE_DATE_PATTERN, selectedDateTo); DetachedCriteria criteria = DetachedCriteria.forClass(ConfirmationRecord.class); criteria.add(Restrictions.between("cycleFrom", cycleFrom, cycleTo)); criteria.add(Restrictions.between("cycleTo", cycleFrom, cycleTo)); criteria.addOrder(Order.asc("cycleFrom")); criteria.addOrder(Order.asc("id")); List<ConfirmationRecord> list = confirmationRecordService.findByCriteria(criteria); Workbook wb = createExcel(list); if (wb != null) { wb.removeSheetAt(0); FileTransfer ft = new FileTransfer(); String fileName = EXCEL_NAME_PATTERN + TEMPLATE_EXCEL_CONF_REPORT_PATH.substring( TEMPLATE_EXCEL_CONF_REPORT_PATH.lastIndexOf("."), TEMPLATE_EXCEL_CONF_REPORT_PATH.length()); fileName = fileName.replaceAll( "#fdd-MMM-yyyy", DateUtil.convDateToString(DISPLAY_DATE_PATTERN, maxCycleFrom)); fileName = fileName.replaceAll( "#tdd-MMM-yyyy", DateUtil.convDateToString(DISPLAY_DATE_PATTERN, maxCycleTo)); ft.fileDownload(fileName, wb); } init(); } catch (Exception e) { e.printStackTrace(); } } else { MessageUtils.getInstance().addErrorMessage("msgDialogDL", "Invalid date"); } }
public static void generate( String teacherName, String discipline, String groupName, String month, String year) throws Exception { ArrayList<Lesson> lessons = LessonManager.takeLessons(teacherName, discipline, groupName, month, year); try { FileInputStream sample = new FileInputStream("E:\\workspace\\excelgeneration\\report_sample.xls"); FileOutputStream output = new FileOutputStream("E:\\workspace\\excelgeneration\\report.xls"); // URL path1 = GenerateExcel.class.getResource("/"); // System.out.println(path1); Workbook teacherReport = new HSSFWorkbook(sample); Sheet timesheet = teacherReport.cloneSheet(0); Sheet sheet = teacherReport.cloneSheet(1); CellStyle cellStyle = teacherReport.createCellStyle(); cellStyle = teacherReport.createCellStyle(); int[] typesCount = new int[12]; // cout number of each type of lesson // 0 - Керівництво кафедрою // 1 - Лекції // 2 - Практичні заняття // 3 - Семінари // 4 - Лабораторні заняття // 5 - Розрахунково- графічні роботи // 6 - Курсове проектування // 7 - Заліки // 8 - Екзамени // 9 - Консультації // 10 - Дипломне проектування // 11 - Керівництво аспірантами timesheet.getRow(13).getCell(0).setCellValue(teacherName); // teacher name sheet.getRow(2).getCell(6).setCellValue(teacherName); Calendar current = Calendar.getInstance(); current.set( Integer.parseInt(year, 10), Integer.parseInt(month, 10) - 1, current.getActualMaximum(Calendar.DAY_OF_MONTH)); timesheet.getRow(15).getCell(3).setCellValue("01." + month + "." + year); timesheet .getRow(15) .getCell(6) .setCellValue(current.getActualMaximum(Calendar.DAY_OF_MONTH) + "." + month + "." + year); String substring = year.length() > 2 ? year.substring(year.length() - 2) : year; timesheet.getRow(15).getCell(9).setCellValue(substring + "р."); // timesheet.getRow(16).getCell(8).setCellValue(discipline); // System.out.println(discipline); int cl = 0, rw = 0; // lesson table row and cell counters for (int i = 0; i < lessons.size(); i++) { if (i > 19) { cl = 5; rw = 0; } timesheet .getRow(21 + rw) .getCell(cl) .setCellValue( lessons.get(i).getLessonDay() + "." + lessons.get(i).getLessonMonth() + "." + year); timesheet .getRow(21 + rw) .getCell(cl + 1) .setCellValue(getTimePair(lessons.get(i).getLessonNum())); timesheet.getRow(21 + rw).getCell(cl + 2).setCellValue(lessons.get(i).getType()); timesheet.getRow(21 + rw).getCell(cl + 3).setCellValue(groupName); timesheet.getRow(21 + rw).getCell(cl + 4).setCellValue(2); rw++; } typesCount = countNumLesTypes(lessons); int res = 0; for (int i = 0; i < typesCount.length; i++) { sheet.getRow(13).getCell(1 + i).setCellValue(typesCount[i]); res += typesCount[i]; } sheet.getRow(13).getCell(13).setCellValue(res); teacherReport.removeSheetAt(0); teacherReport.removeSheetAt(0); teacherReport.setSheetName(0, "timesheet"); teacherReport.setSheetName(1, "sheet"); teacherReport.write(output); output.close(); } catch (Exception e) { e.printStackTrace(); } }
/** * ワークブックの変換処理を実行する。 * * @param reportBook ワークブックの置換情報 */ private void processBook(ReportBook reportBook) { if (reportBook == null) { return; } Workbook workbook = getTemplateWorkbook(reportBook.getInputStream()); for (ReportProcessListener listener : listeners) { listener.preBookParse(workbook, reportBook); } checkReportBook(reportBook); // テンプレート展開 Set<Integer> delTemplateIndexs = expandTemplate(workbook, reportBook); // 出力ファイル取得 BookController controller = new BookController(workbook); // Parserの設定 for (ReportsTagParser<?> tagParser : parsers.values()) { controller.addTagParser(tagParser); } // リスナーの設定 controller.addSheetParseListener(new RemoveAdapter()); for (ReportProcessListener listener : listeners) { controller.addSheetParseListener(listener); } // Exporterの設定 for (ConvertConfiguration configuration : reportBook.getConfigurations()) { if (configuration == null) { continue; } for (ReportBookExporter reportExporter : exporters.values()) { if (configuration.getFormatType().equals(reportExporter.getFormatType())) { reportExporter.setConfiguration(configuration); reportExporter.setOutputStream(reportBook.getOutputStream()); controller.addBookExporter(reportExporter); } } } ReportsParserInfo reportsParserInfo = new ReportsParserInfo(); reportsParserInfo.setReportParsers(new ArrayList<ReportsTagParser<?>>(parsers.values())); reportsParserInfo.setReportBook(reportBook); BookData bookData = controller.getBookData(); bookData.clear(); for (String sheetName : controller.getSheetNames()) { if (sheetName.startsWith(BookController.COMMENT_PREFIX)) { continue; } ReportSheet reportSheet = ReportsUtil.getReportSheet(sheetName, reportBook); if (reportSheet != null) { reportsParserInfo.setParamInfo(reportSheet.getParamInfo()); // 解析の実行 SheetData sheetData = controller.parseSheet(sheetName, reportsParserInfo); // 結果の追加 controller.getBookData().putSheetData(sheetName, sheetData); } } // 不要テンプレート削除 for (Integer deleteSheetIndex : delTemplateIndexs) { workbook.removeSheetAt(deleteSheetIndex); } // 出力処理前にリスナー呼び出し for (ReportProcessListener listener : listeners) { listener.postBookParse(workbook, reportBook); } // 出力処理の実行 for (BookExporter exporter : controller.getExporter()) { if (exporter != null) { exporter.setup(); try { exporter.export(workbook, bookData); } finally { exporter.tearDown(); } } } }
public void removeSheetAt(int sheetNo) { hssfWorkbook.removeSheetAt(sheetNo); sheets.remove(sheetNo); }