/**
* 替换xls模板中${vo.id}的字段
*
* @param book
* @param dataVO
* @param voName
* @throws Exception
*/
public static void createCellByOgnl(WritableSheet sheet, Object dataVO, String voName)
throws Exception {
// OGNL替换
String field;
for (int row = 0; row < 100; row++) {
for (int col = 0; col < 50; col++) {
WritableCell c = sheet.getWritableCell(col, row);
if (c.getClass() == EmptyCell.class || c.getClass() == Blank.class) continue;
Label label = (Label) c;
String label_str = GetTrimString(label.getString());
if (!isEmpty(label.getString()) && label_str.startsWith("${" + voName + ".")) {
String value = C.EMPTY_STRING;
try {
field =
label_str.replaceAll("\\$\\{vo\\.", C.EMPTY_STRING).replaceAll("}", C.EMPTY_STRING);
value = (String) UtilTool.GetFieldValue(dataVO, field);
} catch (Exception e) {
e.printStackTrace();
}
// ognl表达式的单元格必须被替换
label.setString(value);
}
}
}
}
/**
* 采用List<List<String>>方式写WritableSheet
*
* @param sheet
* @param xlsList
* @throws Exception
*/
private static void CreateXlsSheet(WritableSheet sheet, List<List<String>> xlsList)
throws Exception {
WritableCellFormat cf = new WritableCellFormat();
cf.setWrap(true);
cf.setAlignment(jxl.format.Alignment.LEFT);
cf.setVerticalAlignment(VerticalAlignment.TOP);
/** 填充数据 */
for (int row = 0; row < xlsList.size(); row++) {
List<String> rowList = xlsList.get(row);
// 如果该行为空,则跳过(一般跳过两行)
if (isEmpty(rowList)) continue;
// 单元格 Object.field
for (int col = 0; col < rowList.size(); col++) {
// 跳过无属性行
if (isEmpty(rowList.get(col))) continue;
// 写数据
String value = C.EMPTY_STRING;
value = StringValue(rowList.get(col));
WritableCell c = sheet.getWritableCell(col, row);
c.setCellFormat(cf);
if (CellType.EMPTY.equals(c.getType())) {
Label label = new Label(col, row, value);
label.setCellFormat(cf);
sheet.addCell(label);
if (row == 0) label.setCellFormat(GetCellFormat());
} else {
Label label = (Label) c;
label.setString(value);
}
}
}
}
public byte[] writeOutXlsFileToBytes() {
byte[] bytes = null;
WritableFont normalFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat cellFormat = null;
try {
ByteArrayOutputStream output = new ByteArrayOutputStream();
WritableWorkbook w = Workbook.createWorkbook(output);
WritableSheet writableSheet = w.createSheet("Sheet", 0);
for (int row = 0; row < getTemplateCells().length; row++) {
for (int col = 0; col < sheetMetaData.getCols(); col++) {
String cellData = getTemplateCells()[row][col];
jxl.write.Label label = new jxl.write.Label(col, row, cellData);
if (row == 0) {
// Header row in bold
cellFormat = new WritableCellFormat(boldFont);
} else {
cellFormat = new WritableCellFormat(normalFont);
}
label.setCellFormat(cellFormat);
writableSheet.addCell(label);
}
}
w.write();
w.close();
bytes = output.toByteArray();
output.close();
} catch (Exception e) {
e.printStackTrace();
}
return bytes;
}
// -------------------------------------------------------------------------
// Action Implementation
// -------------------------------------------------------------------------
public String execute() throws Exception {
statementManager.initialise();
// Initialization
raFolderName = reportService.getRAFolderName();
String deCodesXMLFileName = "";
simpleDateFormat = new SimpleDateFormat("MMM-yyyy");
monthFormat = new SimpleDateFormat("MMMM");
yearFormat = new SimpleDateFormat("yyyy");
simpleMonthFormat = new SimpleDateFormat("MMM");
String parentUnit = "";
Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList));
deCodesXMLFileName = selReportObj.getXmlTemplateName();
reportModelTB = selReportObj.getModel();
reportFileNameTB = selReportObj.getExcelTemplateName();
String inputTemplatePath =
System.getenv("DHIS2_HOME")
+ File.separator
+ raFolderName
+ File.separator
+ "template"
+ File.separator
+ reportFileNameTB;
// String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName
// + File.separator + "output" + File.separator + UUID.randomUUID().toString();
String outputReportFolderPath =
System.getenv("DHIS2_HOME")
+ File.separator
+ Configuration_IN.DEFAULT_TEMPFOLDER
+ File.separator
+ UUID.randomUUID().toString();
File newdir = new File(outputReportFolderPath);
if (!newdir.exists()) {
newdir.mkdirs();
}
if (reportModelTB.equalsIgnoreCase("STATIC")
|| reportModelTB.equalsIgnoreCase("STATIC-DATAELEMENTS")
|| reportModelTB.equalsIgnoreCase("STATIC-FINANCIAL")) {
orgUnitList =
new ArrayList<OrganisationUnit>(
organisationUnitService.getOrganisationUnitWithChildren(ouIDTB));
OrganisationUnitGroup orgUnitGroup = selReportObj.getOrgunitGroup();
orgUnitList.retainAll(orgUnitGroup.getMembers());
} else {
return INPUT;
}
// System.out.println( "---Size of Org Unit List ----: " + orgUnitList.size() + ",Report Group
// name is :---" + selReportObj.getOrgunitGroup().getName() + ", Size of Group member is ----:"
// + selReportObj.getOrgunitGroup().getMembers().size() );
System.out.println(" ---- Size of OrgUnit List is ---- " + orgUnitList.size());
OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB);
System.out.println(
selOrgUnit.getName()
+ " : "
+ selReportObj.getName()
+ " : Report Generation Start Time is : "
+ new Date());
selectedPeriod = periodService.getPeriod(availablePeriods);
sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate()));
eDate = format.parseDate(String.valueOf(selectedPeriod.getEndDate()));
Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath));
// collect periodId by commaSepareted
List<Period> tempPeriodList =
new ArrayList<Period>(periodService.getIntersectingPeriods(sDate, eDate));
Collection<Integer> tempPeriodIds =
new ArrayList<Integer>(getIdentifiers(Period.class, tempPeriodList));
String periodIdsByComma = getCommaDelimitedString(tempPeriodIds);
// Getting DataValues
List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName);
// collect dataElementIDs by commaSepareted
String dataElmentIdsByComma = reportService.getDataelementIds(reportDesignList);
int orgUnitCount = 0;
Iterator<OrganisationUnit> it = orgUnitList.iterator();
while (it.hasNext()) {
OrganisationUnit currentOrgUnit = (OrganisationUnit) it.next();
String outPutFileName = reportFileNameTB.replace(".xls", "");
outPutFileName += "_" + currentOrgUnit.getShortName();
outPutFileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls";
String outputReportPath = outputReportFolderPath + File.separator + outPutFileName;
WritableWorkbook outputReportWorkbook =
Workbook.createWorkbook(new File(outputReportPath), templateWorkbook);
Map<String, String> aggDeMap = new HashMap<String, String>();
if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
aggDeMap.putAll(
reportService.getResultDataValueFromAggregateTable(
currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma));
} else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
List<OrganisationUnit> childOrgUnitTree =
new ArrayList<OrganisationUnit>(
organisationUnitService.getOrganisationUnitWithChildren(currentOrgUnit.getId()));
List<Integer> childOrgUnitTreeIds =
new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree));
String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds);
aggDeMap.putAll(
reportService.getAggDataFromDataValueTable(
childOrgUnitsByComma, dataElmentIdsByComma, periodIdsByComma));
} else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
aggDeMap.putAll(
reportService.getAggDataFromDataValueTable(
"" + currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma));
}
int count1 = 0;
Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator();
while (reportDesignIterator.hasNext()) {
Report_inDesign report_inDesign = (Report_inDesign) reportDesignIterator.next();
String deType = report_inDesign.getPtype();
String sType = report_inDesign.getStype();
String deCodeString = report_inDesign.getExpression();
String tempStr = "";
Calendar tempStartDate = Calendar.getInstance();
Calendar tempEndDate = Calendar.getInstance();
List<Calendar> calendarList =
new ArrayList<Calendar>(reportService.getStartingEndingPeriods(deType, selectedPeriod));
if (calendarList == null || calendarList.isEmpty()) {
tempStartDate.setTime(selectedPeriod.getStartDate());
tempEndDate.setTime(selectedPeriod.getEndDate());
return SUCCESS;
} else {
tempStartDate = calendarList.get(0);
tempEndDate = calendarList.get(1);
}
if (deCodeString.equalsIgnoreCase("FACILITY")) {
tempStr = currentOrgUnit.getName();
} else if (deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT")) {
tempStr = parentUnit;
} else if (deCodeString.equalsIgnoreCase("FACILITYP")) {
tempStr = currentOrgUnit.getParent().getName();
} else if (deCodeString.equalsIgnoreCase("FACILITYPP")) {
tempStr = currentOrgUnit.getParent().getParent().getName();
} else if (deCodeString.equalsIgnoreCase("FACILITYPPP")) {
tempStr = currentOrgUnit.getParent().getParent().getParent().getName();
} else if (deCodeString.equalsIgnoreCase("FACILITYPPPP")) {
tempStr = currentOrgUnit.getParent().getParent().getParent().getParent().getName();
} else if (deCodeString.equalsIgnoreCase("PERIOD")
|| deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")) {
tempStr = simpleDateFormat.format(sDate);
} else if (deCodeString.equalsIgnoreCase("PERIOD-MONTH")) {
tempStr = monthFormat.format(sDate);
} else if (deCodeString.equalsIgnoreCase("PERIOD-YEAR")) {
tempStr = yearFormat.format(sDate);
} else if (deCodeString.equalsIgnoreCase("MONTH-START-SHORT")) {
tempStr = simpleMonthFormat.format(sDate);
} else if (deCodeString.equalsIgnoreCase("MONTH-END-SHORT")) {
tempStr = simpleMonthFormat.format(eDate);
} else if (deCodeString.equalsIgnoreCase("MONTH-START")) {
tempStr = monthFormat.format(sDate);
} else if (deCodeString.equalsIgnoreCase("MONTH-END")) {
tempStr = monthFormat.format(eDate);
} else if (deCodeString.equalsIgnoreCase("SLNO")) {
tempStr = "" + (orgUnitCount + 1);
} else if (deCodeString.equalsIgnoreCase("NA")) {
tempStr = " ";
} else {
if (sType.equalsIgnoreCase("dataelement")) {
if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
tempStr = getAggVal(deCodeString, aggDeMap);
// tempStr = reportService.getIndividualResultDataValue(deCodeString,
// tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB );
} else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
tempStr = getAggVal(deCodeString, aggDeMap);
// tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(),
// tempEndDate.getTime(), currentOrgUnit, reportModelTB );
} else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
tempStr = getAggVal(deCodeString, aggDeMap);
/*
List<Period> periodList = new ArrayList<Period>( periodService.getPeriodsBetweenDates( tempStartDate.getTime(), tempEndDate.getTime() ) );
Collection<Integer> periodIds = new ArrayList<Integer>( getIdentifiers(Period.class, periodList ) );
tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, currentOrgUnit, reportModelTB );
*/
}
} else if (sType.equalsIgnoreCase("dataelement-boolean")) {
if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
tempStr =
reportService.getBooleanDataValue(
deCodeString,
tempStartDate.getTime(),
tempEndDate.getTime(),
currentOrgUnit,
reportModelTB);
} else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
tempStr =
reportService.getBooleanDataValue(
deCodeString,
tempStartDate.getTime(),
tempEndDate.getTime(),
currentOrgUnit,
reportModelTB);
} else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
tempStr =
reportService.getBooleanDataValue(
deCodeString,
tempStartDate.getTime(),
tempEndDate.getTime(),
currentOrgUnit,
reportModelTB);
}
} else {
if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
tempStr =
reportService.getIndividualResultIndicatorValue(
deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit);
} else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
tempStr =
reportService.getResultIndicatorValue(
deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit);
} else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
// List<Period> periodList = new ArrayList<Period>(
// periodService.getPeriodsBetweenDates( tempStartDate.getTime(),
// tempEndDate.getTime() ) );
// Collection<Integer> periodIds = new ArrayList<Integer>(
// getIdentifiers(Period.class, periodList ) );
tempStr =
reportService.getResultIndicatorValue(
deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit);
}
}
}
int tempRowNo = report_inDesign.getRowno();
int tempColNo = report_inDesign.getColno();
int sheetNo = report_inDesign.getSheetno();
WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo);
if (tempStr == null || tempStr.equals(" ")) {
tempColNo += orgUnitCount;
WritableCellFormat wCellformat = new WritableCellFormat();
wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat.setWrap(true);
wCellformat.setAlignment(Alignment.CENTRE);
sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat));
} else {
if (reportModelTB.equalsIgnoreCase("DYNAMIC-ORGUNIT")) {
if (deCodeString.equalsIgnoreCase("FACILITYP")
|| deCodeString.equalsIgnoreCase("FACILITYPP")
|| deCodeString.equalsIgnoreCase("FACILITYPPP")
|| deCodeString.equalsIgnoreCase("FACILITYPPPP")) {
} else if (deCodeString.equalsIgnoreCase("PERIOD")
|| deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")
|| deCodeString.equalsIgnoreCase("PERIOD-WEEK")
|| deCodeString.equalsIgnoreCase("PERIOD-MONTH")
|| deCodeString.equalsIgnoreCase("PERIOD-QUARTER")
|| deCodeString.equalsIgnoreCase("PERIOD-YEAR")
|| deCodeString.equalsIgnoreCase("MONTH-START")
|| deCodeString.equalsIgnoreCase("MONTH-END")
|| deCodeString.equalsIgnoreCase("MONTH-START-SHORT")
|| deCodeString.equalsIgnoreCase("MONTH-END-SHORT")
|| deCodeString.equalsIgnoreCase("SIMPLE-QUARTER")
|| deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT")
|| deCodeString.equalsIgnoreCase("QUARTER-MONTHS")
|| deCodeString.equalsIgnoreCase("QUARTER-START-SHORT")
|| deCodeString.equalsIgnoreCase("QUARTER-END-SHORT")
|| deCodeString.equalsIgnoreCase("QUARTER-START")
|| deCodeString.equalsIgnoreCase("QUARTER-END")
|| deCodeString.equalsIgnoreCase("SIMPLE-YEAR")
|| deCodeString.equalsIgnoreCase("YEAR-END")
|| deCodeString.equalsIgnoreCase("YEAR-FROMTO")) {
} else {
tempColNo += orgUnitCount;
}
} else if (reportModelTB.equalsIgnoreCase("dynamicwithrootfacility")) {
if (deCodeString.equalsIgnoreCase("FACILITYP")
|| deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT")
|| deCodeString.equalsIgnoreCase("FACILITYPP")
|| deCodeString.equalsIgnoreCase("FACILITYPPP")
|| deCodeString.equalsIgnoreCase("FACILITYPPPP")) {
} else if (deCodeString.equalsIgnoreCase("PERIOD")
|| deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")
|| deCodeString.equalsIgnoreCase("PERIOD-WEEK")
|| deCodeString.equalsIgnoreCase("PERIOD-MONTH")
|| deCodeString.equalsIgnoreCase("PERIOD-QUARTER")
|| deCodeString.equalsIgnoreCase("PERIOD-YEAR")
|| deCodeString.equalsIgnoreCase("MONTH-START")
|| deCodeString.equalsIgnoreCase("MONTH-END")
|| deCodeString.equalsIgnoreCase("MONTH-START-SHORT")
|| deCodeString.equalsIgnoreCase("MONTH-END-SHORT")
|| deCodeString.equalsIgnoreCase("SIMPLE-QUARTER")
|| deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT")
|| deCodeString.equalsIgnoreCase("QUARTER-MONTHS")
|| deCodeString.equalsIgnoreCase("QUARTER-START-SHORT")
|| deCodeString.equalsIgnoreCase("QUARTER-END-SHORT")
|| deCodeString.equalsIgnoreCase("QUARTER-START")
|| deCodeString.equalsIgnoreCase("QUARTER-END")
|| deCodeString.equalsIgnoreCase("SIMPLE-YEAR")
|| deCodeString.equalsIgnoreCase("YEAR-END")
|| deCodeString.equalsIgnoreCase("YEAR-FROMTO")) {
} else {
tempRowNo += orgUnitCount;
}
}
WritableCell cell = sheet0.getWritableCell(tempColNo, tempRowNo);
CellFormat cellFormat = cell.getCellFormat();
WritableCellFormat wCellformat = new WritableCellFormat();
wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat.setWrap(true);
wCellformat.setAlignment(Alignment.CENTRE);
if (cell.getType() == CellType.LABEL) {
Label l = (Label) cell;
l.setString(tempStr);
l.setCellFormat(cellFormat);
} else {
try {
sheet0.addCell(
new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat));
} catch (Exception e) {
sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat));
}
}
}
count1++;
} // inner while loop end
outputReportWorkbook.write();
outputReportWorkbook.close();
orgUnitCount++;
} // outer while loop end
statementManager.destroy();
if (zipDirectory(outputReportFolderPath, outputReportFolderPath + ".zip")) {
System.out.println(
selOrgUnit.getName()
+ " : "
+ selReportObj.getName()
+ " Report Generation End Time is : "
+ new Date());
fileName = reportFileNameTB.replace(".xls", "");
fileName += "_" + selOrgUnit.getShortName();
fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".zip";
File outputReportFile = new File(outputReportFolderPath + ".zip");
inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));
return SUCCESS;
} else {
return INPUT;
}
}
public void createEmployeesFile(List<Employee> list, OutputStream os) throws Exception {
WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
WritableSheet wsheet = wbook.createSheet("Sheet1", 0); // 工作表名称
// 设置公司名
WritableFont companyfont =
new WritableFont(
WritableFont.createFont("宋体"),
18,
WritableFont.BOLD,
false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat companyFormat = new WritableCellFormat(companyfont);
companyFormat.setAlignment(jxl.format.Alignment.CENTRE);
companyFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
Label excelCompany = new Label(0, 0, "桐庐富伟针织有限公司员工花名册", companyFormat);
wsheet.addCell(excelCompany);
wsheet.mergeCells(0, 0, 13, 0);
wsheet.setRowView(0, 800);
// 设置Excel字体
WritableFont wfont =
new WritableFont(
WritableFont.createFont("宋体"),
10,
WritableFont.BOLD,
false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat titleFormat = new WritableCellFormat(wfont);
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
titleFormat.setBorder(
jxl.format.Border.ALL,
jxl.format.BorderLineStyle.THIN,
jxl.format.Colour.BLACK); // BorderLineStyle边框
WritableFont wfont2 =
new WritableFont(
WritableFont.createFont("宋体"),
10,
WritableFont.NO_BOLD,
false,
jxl.format.UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat titleFormat2 = new WritableCellFormat(wfont2);
titleFormat2.setAlignment(jxl.format.Alignment.CENTRE);
titleFormat2.setBorder(
jxl.format.Border.ALL,
jxl.format.BorderLineStyle.THIN,
jxl.format.Colour.BLACK); // BorderLineStyle边框
String[] title = {
"编号", "姓名", "性别", "入厂日期", "身份证号码", "联系方式", "岗位", "部门", "家庭住址", "现居住地", "合同期限", "用工形式", "离职时间",
"时薪"
};
// 设置Excel表头
int col = 0;
int merge_col = 0;
int columnBestWidth[] = new int[title.length + 1]; // 保存最佳列宽数据的数组
for (int i = 0; i < title.length; i++, col++) {
columnBestWidth[col] = title[i].getBytes().length;
Label excelTitle = new Label(col, 1, title[i], titleFormat);
if (title[i].equals("合同期限")) {
merge_col = col;
col++;
columnBestWidth[col] = title[i].getBytes().length;
}
wsheet.addCell(excelTitle);
}
wsheet.setRowView(1, 400);
wsheet.mergeCells(merge_col, 1, merge_col + 1, 1);
int c = 2; // 用于循环时Excel的行号
for (Employee employee : list) {
wsheet.setRowView(c, 400);
Label content1 = new Label(0, c, employee.getNumber(), titleFormat2);
Label content2 = new Label(1, c, employee.getName(), titleFormat2);
Label content3 = new Label(2, c, employee.getSex(), titleFormat2);
Label content4 =
new Label(3, c, DateTool.formatDateYMD(employee.getEnter_at()), titleFormat2);
Label content5 = new Label(4, c, employee.getId_card(), titleFormat2);
Label content6 = new Label(5, c, employee.getTel(), titleFormat2);
Label content7 = new Label(6, c, employee.getJob(), titleFormat2);
Label content8 =
new Label(7, c, SystemCache.getDepartmentName(employee.getDepartmentId()), titleFormat2);
Label content9 = new Label(8, c, employee.getAddress_home(), titleFormat2);
Label content10 = new Label(9, c, employee.getAddress(), titleFormat2);
Label content11 =
new Label(10, c, DateTool.formatDateYMD(employee.getAgreement_at()), titleFormat2);
Label content12 =
new Label(11, c, DateTool.formatDateYMD(employee.getAgreement_end()), titleFormat2);
Label content13 = new Label(12, c, employee.getEmployee_type(), titleFormat2);
Label content14 =
new Label(13, c, DateTool.formatDateYMD(employee.getLeave_at(), "/"), titleFormat2);
Label content15 =
new Label(
14,
c,
employee.getHour_salary() == null ? "" : String.valueOf(employee.getHour_salary()),
titleFormat2);
wsheet.addCell(content1);
wsheet.addCell(content2);
wsheet.addCell(content3);
wsheet.addCell(content4);
wsheet.addCell(content5);
wsheet.addCell(content6);
wsheet.addCell(content7);
wsheet.addCell(content8);
wsheet.addCell(content9);
wsheet.addCell(content10);
wsheet.addCell(content11);
wsheet.addCell(content12);
wsheet.addCell(content13);
wsheet.addCell(content14);
wsheet.addCell(content15);
int width1 = content1.getContents().getBytes().length;
int width2 = content2.getContents().getBytes().length;
int width3 = content3.getContents().getBytes().length;
int width4 = content4.getContents().getBytes().length;
int width5 = content5.getContents().getBytes().length;
int width6 = content6.getContents().getBytes().length;
int width7 = content7.getContents().getBytes().length;
int width8 = content8.getContents().getBytes().length;
int width9 = content9.getContents().getBytes().length;
int width10 = content10.getContents().getBytes().length;
int width11 = content11.getContents().getBytes().length;
int width12 = content12.getContents().getBytes().length;
int width13 = content13.getContents().getBytes().length;
int width14 = content14.getContents().getBytes().length;
int width15 = content15.getContents().getBytes().length;
if (columnBestWidth[0] < width1) {
columnBestWidth[0] = width1;
}
if (columnBestWidth[1] < width2) {
columnBestWidth[1] = width2;
}
if (columnBestWidth[2] < width3) {
columnBestWidth[2] = width3;
}
if (columnBestWidth[3] < width4) {
columnBestWidth[3] = width4;
}
if (columnBestWidth[4] < width5) {
columnBestWidth[4] = width5;
}
if (columnBestWidth[5] < width6) {
columnBestWidth[5] = width6;
}
if (columnBestWidth[6] < width7) {
columnBestWidth[6] = width7;
}
if (columnBestWidth[7] < width8) {
columnBestWidth[7] = width8;
}
if (columnBestWidth[8] < width9) {
columnBestWidth[8] = width9;
}
if (columnBestWidth[9] < width10) {
columnBestWidth[9] = width10;
}
if (columnBestWidth[10] < width11) {
columnBestWidth[10] = width11;
}
if (columnBestWidth[11] < width12) {
columnBestWidth[11] = width12;
}
if (columnBestWidth[12] < width13) {
columnBestWidth[12] = width13;
}
if (columnBestWidth[13] < width14) {
columnBestWidth[13] = width14;
}
if (columnBestWidth[14] < width15) {
columnBestWidth[14] = width15;
}
c++;
}
for (int p = 0; p < columnBestWidth.length; ++p) {
wsheet.setColumnView(p, columnBestWidth[p] + 1);
}
wbook.write(); // 写入文件
wbook.close();
os.close();
}
public String execute() throws Exception {
int tempCol1 = 0;
int tempRow1 = 1;
System.out.println("Export to Excel");
ActionContext ctx = ActionContext.getContext();
HttpServletRequest req = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
HttpSession session = req.getSession();
BufferedImage chartImage = (BufferedImage) session.getAttribute("chartImage");
PngEncoder encoder = new PngEncoder(chartImage, false, 0, 9);
byte[] encoderBytes = encoder.pngEncode();
Double[][] objData1 = (Double[][]) session.getAttribute("data1");
Double[][] objData2 = (Double[][]) session.getAttribute("data2");
String[] series1S = (String[]) session.getAttribute("series1");
String[] series2S = (String[]) session.getAttribute("series2");
String[] categories1S = (String[]) session.getAttribute("categories1");
String[] categories2S = (String[]) session.getAttribute("categories2");
initialzeAllLists(series1S, series2S, categories1S, categories2S);
data1 = convertDoubleTodouble(objData1);
data2 = convertDoubleTodouble(objData2);
if (chartDisplayOption == null || chartDisplayOption.equalsIgnoreCase("none")) {
} else if (chartDisplayOption.equalsIgnoreCase("ascend")) {
sortByAscending();
} else if (chartDisplayOption.equalsIgnoreCase("desend")) {
sortByDesscending();
} else if (chartDisplayOption.equalsIgnoreCase("alphabet")) {
sortByAlphabet();
}
String outputReportFile =
System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER;
File newdir = new File(outputReportFile);
if (!newdir.exists()) {
newdir.mkdirs();
}
outputReportFile += File.separator + UUID.randomUUID().toString() + ".xls";
WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportFile));
WritableSheet sheet0 = outputReportWorkbook.createSheet("ChartOutput", 0);
if (viewSummary.equals("no")) {
WritableImage writableImage = new WritableImage(0, 1, 10, 23, encoderBytes);
sheet0.addImage(writableImage);
tempRow1 = 24;
} else {
tempRow1 -= objData1.length;
}
int count1 = 0;
int count2 = 0;
int flag1 = 0;
while (count1 <= categories1.length) {
for (int j = 0; j < data1.length; j++) {
tempCol1 = 1;
tempRow1++;
WritableCellFormat wCellformat1 = new WritableCellFormat();
wCellformat1.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat1.setWrap(true);
WritableCellFormat wCellformat2 = new WritableCellFormat();
wCellformat2.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat2.setAlignment(Alignment.CENTRE);
wCellformat2.setBackground(Colour.GRAY_25);
wCellformat2.setWrap(true);
WritableCell cell1;
CellFormat cellFormat1;
for (int k = count2; k < count1; k++) {
if (k == count2 && j == 0) {
tempCol1 = 0;
tempRow1++;
cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
cellFormat1 = cell1.getCellFormat();
if (cell1.getType() == CellType.LABEL) {
Label l = (Label) cell1;
l.setString("Service");
l.setCellFormat(cellFormat1);
} else {
sheet0.addCell(new Label(tempCol1, tempRow1, "Service", wCellformat2));
}
tempCol1++;
for (int i = count2; i < count1; i++) {
cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
cellFormat1 = cell1.getCellFormat();
if (cell1.getType() == CellType.LABEL) {
Label l = (Label) cell1;
l.setString(categories1[i]);
l.setCellFormat(cellFormat1);
} else {
sheet0.addCell(new Label(tempCol1, tempRow1, categories1[i], wCellformat2));
}
tempCol1++;
}
tempRow1++;
tempCol1 = 1;
}
if (k == count2) {
tempCol1 = 0;
cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
cellFormat1 = cell1.getCellFormat();
if (cell1.getType() == CellType.LABEL) {
Label l = (Label) cell1;
l.setString(series1[j]);
l.setCellFormat(cellFormat1);
} else {
sheet0.addCell(new Label(tempCol1, tempRow1, series1[j], wCellformat2));
}
tempCol1++;
}
cell1 = sheet0.getWritableCell(tempCol1, tempRow1);
cellFormat1 = cell1.getCellFormat();
if (cell1.getType() == CellType.LABEL) {
Label l = (Label) cell1;
l.setString("" + data1[j][k]);
l.setCellFormat(cellFormat1);
} else {
sheet0.addCell(new Number(tempCol1, tempRow1, data1[j][k], wCellformat1));
}
tempCol1++;
}
}
if (flag1 == 1) break;
count2 = count1;
if ((count1 + 10 > categories1.length) && (categories1.length - count1 <= 10)) {
count1 += categories1.length - count1;
flag1 = 1;
} else count1 += 10;
}
outputReportWorkbook.write();
outputReportWorkbook.close();
fileName = "chartOutput.xls";
inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));
return SUCCESS;
}