/**
* @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);
}