/**
* テンプレートワークブックのシートを、帳票出力単位に変換する。
*
* @param workbook テンプレートワークブック
* @param reportBook 帳票ワークブック情報
* @return 削除が必要なテンプレートシートインデックス
*/
private Set<Integer> expandTemplate(Workbook workbook, ReportBook reportBook) {
Set<Integer> delTemplateIndexs = new TreeSet<Integer>(Collections.reverseOrder());
Set<Integer> useTemplateIndexs = new HashSet<Integer>();
// 出力シート単位にコピーする
for (ReportSheet reportSheet : reportBook.getReportSheets()) {
if (reportSheet != null) {
if (reportSheet.getSheetName().equals(reportSheet.getTemplateName())) {
// テンプレート名＝出力シート名
int lastSheetIndex = workbook.getNumberOfSheets() - 1;
workbook.setSheetOrder(reportSheet.getSheetName(), lastSheetIndex);
useTemplateIndexs.add(lastSheetIndex);
} else {
int tempIdx = workbook.getSheetIndex(reportSheet.getTemplateName());
Sheet sheet = workbook.cloneSheet(tempIdx);
workbook.setSheetName(workbook.getSheetIndex(sheet), reportSheet.getSheetName());
delTemplateIndexs.add(tempIdx);
}
}
}
// 出力対象外シートを削除インデックスに追加
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
if (!isOutputSheet(sheet, reportBook)) {
delTemplateIndexs.add(workbook.getSheetIndex(sheet));
}
}
delTemplateIndexs.removeAll(useTemplateIndexs);
return delTemplateIndexs;
}
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();
}
}
/** @see ReportRenderer#render(ReportData, String, OutputStream) */
public void render(ReportData reportData, String argument, OutputStream out)
throws IOException, RenderingException {
try {
log.debug("Attempting to render report with ExcelTemplateRenderer");
ReportDesign design = getDesign(argument);
Workbook wb = getExcelTemplate(design);
if (wb == null) {
XlsReportRenderer xlsRenderer = new XlsReportRenderer();
xlsRenderer.render(reportData, argument, out);
} else {
Map<String, String> repeatSections = getRepeatingSections(design);
// Put together base set of replacements. Any dataSet with only one row is included.
Map<String, Object> replacements = getBaseReplacementData(reportData, design);
// Iterate across all of the sheets in the workbook, and configure all those that need to be
// added/cloned
List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();
Set<String> usedSheetNames = new HashSet<String>();
int numberOfSheets = wb.getNumberOfSheets();
for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {
Sheet currentSheet = wb.getSheetAt(sheetNum);
String originalSheetName = wb.getSheetName(sheetNum);
String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
if (dataSetName != null) {
DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
int dataSetRowNum = 0;
for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator();
rowIterator.hasNext(); ) {
DataSetRow dataSetRow = rowIterator.next();
dataSetRowNum++;
Map<String, Object> newReplacements =
getReplacementData(
replacements, reportData, design, dataSetName, dataSetRow, dataSetRowNum);
Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
sheetsToAdd.add(
new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
}
} else {
sheetsToAdd.add(
new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
}
}
// Then iterate across all of these and add them in
for (int i = 0; i < sheetsToAdd.size(); i++) {
addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
}
wb.write(out);
}
} catch (Exception e) {
throw new RenderingException("Unable to render results due to: " + e, e);
}
}