/**
* 取报表标题单元格格式
*
* @return
*/
public WritableCellFormat getFt_title() {
try {
ft_title = new WritableCellFormat();
ft_title.setFont(new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD));
ft_title.setAlignment(Alignment.CENTRE);
ft_title.setVerticalAlignment(VerticalAlignment.CENTRE);
ft_title.setBorder(Border.ALL, BorderLineStyle.THIN);
} catch (Exception e) {
log.info(e);
}
return ft_title;
}
/**
* 单元格右对齐且黑体
*
* @return
*/
public WritableCellFormat getFt_item_right_bold() {
try {
ft_item_right_bold = new WritableCellFormat();
ft_item_right_bold.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD));
ft_item_right_bold.setAlignment(Alignment.RIGHT);
ft_item_right_bold.setVerticalAlignment(VerticalAlignment.CENTRE);
ft_item_right_bold.setBorder(Border.ALL, BorderLineStyle.THIN);
} catch (Exception e) {
log.info(e);
}
return ft_item_right_bold;
}
/**
* 单元格左对齐
*
* @return
*/
public WritableCellFormat getFt_item_left() {
try {
ft_item_left = new WritableCellFormat();
ft_item_left.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD));
ft_item_left.setAlignment(Alignment.LEFT);
ft_item_left.setVerticalAlignment(VerticalAlignment.CENTRE);
ft_item_left.setBorder(Border.ALL, BorderLineStyle.THIN);
} catch (Exception e) {
log.info(e);
}
return ft_item_left;
}
/**
* 单元格右对齐并底色为灰色
*
* @return
*/
public WritableCellFormat getFt_item_rightgray() {
try {
ft_item_rightgray = new WritableCellFormat();
ft_item_rightgray.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD));
ft_item_rightgray.setAlignment(Alignment.RIGHT);
ft_item_rightgray.setVerticalAlignment(VerticalAlignment.CENTRE);
ft_item_rightgray.setBorder(Border.ALL, BorderLineStyle.THIN);
ft_item_rightgray.setBackground(jxl.format.Colour.GRAY_25);
} catch (Exception e) {
log.info(e);
}
return ft_item_rightgray;
}
/**
* 添加单元格&内容.
*
* @param ws
* @param cel
* @param row
* @param valObject
*/
private void addCell(WritableSheet ws, int cel, int row, Object valObj) throws WriteException {
if (valObj == null) {
Label cr = new Label(cel, row, "");
ws.addCell(cr);
return;
}
@SuppressWarnings("rawtypes")
Class clazz2 = valObj.getClass();
if (clazz2 == String.class) {
Label cr = new Label(cel, row, (String) valObj);
ws.addCell(cr);
return;
} else if (clazz2 == int.class || clazz2 == Integer.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Integer) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == long.class || clazz2 == Long.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Long) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == float.class || clazz2 == Float.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Float) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == double.class || clazz2 == Double.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Double) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == Date.class) {
DateTime cr = new DateTime(cel, row, (Date) valObj);
ws.addCell(cr);
return;
} else if (clazz2 == boolean.class || clazz2 == Boolean.class) {
jxl.write.Boolean cr = new jxl.write.Boolean(cel, row, (Boolean) valObj);
ws.addCell(cr);
return;
} else if (clazz2 == BigDecimal.class) {
NumberFormat nf = new NumberFormat("0.00");
WritableCellFormat wcf = new WritableCellFormat(nf);
wcf.setFont(new WritableFont(WritableFont.createFont("宋体"), PS));
Number cr = new Number(cel, row, ((BigDecimal) valObj).doubleValue(), wcf);
ws.addCell(cr);
return;
} else if (clazz2 == Short.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Short) valObj).toString()));
ws.addCell(cr);
return;
}
}
private void generateEmployeePerformanceReport(WritableWorkbook workbook, String key)
throws Exception {
if (key.length() > 2) {
WritableFont headerfont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
WritableCellFormat headerFormat = new WritableCellFormat(headerfont);
headerFormat.setAlignment(Alignment.CENTRE);
WritableFont feemergefont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD);
WritableCellFormat feemergeFormat = new WritableCellFormat(feemergefont);
feemergeFormat.setAlignment(Alignment.CENTRE);
feemergeFormat.setBackground(Colour.GRAY_25);
feemergeFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat numbercellformat = new WritableCellFormat(NumberFormats.FLOAT);
numbercellformat.setFont(font);
numbercellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
key = key.substring(1, key.length() - 1);
WritableSheet sheet = workbook.createSheet("Report For Selected Employees", 0);
JSONArray employeeTourRecords = getEmployeeTourRecords(key);
HashMap<Integer, HashMap<Integer, Object[]>> employeeTourDetailsMap =
new HashMap<Integer, HashMap<Integer, Object[]>>();
HashMap<Integer, HashMap<Integer, Object[]>> employeeTourRequestAndDetailsMap =
new HashMap<Integer, HashMap<Integer, Object[]>>();
putRecordsIntoMap(
employeeTourDetailsMap, employeeTourRecords, employeeTourRequestAndDetailsMap);
int row = 5;
int column = 0;
sheet.mergeCells(column + 2, row - 4, column + 8, row - 4);
putHeaderDefault(
feemergeFormat,
sheet,
"Employee Tour Expense Sheet For Selected Employees",
row - 4,
column + 2);
putHeaderDefault(feemergeFormat, sheet, "S. No.", row, column++);
putHeaderDefault(feemergeFormat, sheet, "Employee Code", row, column++);
putHeaderDefault(feemergeFormat, sheet, "Employee Name", row, column++);
putHeaderDefault(feemergeFormat, sheet, "Tour Code", row, column++);
putHeaderDefault(feemergeFormat, sheet, "Advance", row, column++);
putHeaderDefault(feemergeFormat, sheet, "City", row, column++);
putHeaderDefault(feemergeFormat, sheet, "From Date", row, column++);
putHeaderDefault(feemergeFormat, sheet, "To Date", row, column++);
putHeaderDefault(feemergeFormat, sheet, "Expected Amount", row, column++);
putHeaderDefault(feemergeFormat, sheet, "Actual Amount", row, column++);
int serNo = 0;
for (Integer employeeId : employeeTourRequestAndDetailsMap.keySet()) {
HashMap<Integer, Object[]> tourRequestDetailsMap =
employeeTourRequestAndDetailsMap.get(employeeId);
for (Integer tourId : tourRequestDetailsMap.keySet()) {
Object[] details = tourRequestDetailsMap.get(tourId);
if (details != null) {
column = 0;
serNo++;
row++;
// int tourId = Translator.integerValue(details[0]);
Object employeeName = details[1];
Object employeeCode = details[2];
Object advance = details[3];
Object departOn = details[4];
Object ariveOn = details[5];
Object tourCode = details[6];
Object totalEstimatedAmount = details[7];
Object totalActualAmount = details[8];
putHeaderDefault(headerFormat, sheet, serNo, row, column++);
putHeaderDefault(headerFormat, sheet, employeeCode, row, column++);
putHeaderDefault(headerFormat, sheet, employeeName, row, column++);
putHeaderDefault(headerFormat, sheet, tourCode, row, column++);
putHeader(numbercellformat, sheet, Translator.doubleValue(advance), row, column++);
putHeaderDefault(headerFormat, sheet, "", row, column++);
putHeaderDefault(headerFormat, sheet, departOn, row, column++);
putHeaderDefault(headerFormat, sheet, ariveOn, row, column++);
putHeaderDefault(headerFormat, sheet, "", row, column++);
putHeaderDefault(headerFormat, sheet, "", row, column++);
HashMap<Integer, Object[]> tourDetailsMap = employeeTourDetailsMap.get(tourId);
if (tourDetailsMap != null) {
for (Integer tourDetailKeys : tourDetailsMap.keySet()) {
details = tourDetailsMap.get(tourDetailKeys);
// details[0] = fromDate;
// details[1] = toDate;
// details[2] = estmateAmount;
// details[3] = actualAmount;
// details[4] = cityName;
column = 5;
row++;
putHeaderDefault(headerFormat, sheet, details[4], row, column++);
putHeaderDefault(headerFormat, sheet, details[0], row, column++);
putHeaderDefault(headerFormat, sheet, details[1], row, column++);
putHeader(
numbercellformat, sheet, Translator.doubleValue(details[2]), row, column++);
putHeader(
numbercellformat, sheet, Translator.doubleValue(details[3]), row, column++);
}
column = 7;
row++;
putHeaderDefault(headerFormat, sheet, "Total", row, column++);
putHeader(
numbercellformat,
sheet,
Translator.doubleValue(totalEstimatedAmount),
row,
column++);
putHeader(
numbercellformat,
sheet,
Translator.doubleValue(totalActualAmount),
row,
column++);
}
}
}
}
workbook.write();
workbook.close();
}
}