private int pro1(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ОАО \"Калуганефтепродукт\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 11, row);
column = opt(column, row + 1, sheet, 1);
column = nadbavka(column, row + 1, sheet);
ave_k.add(column);
column = vill(column, row + 1, sheet, 250, "г. Калуга розница");
ave_obl_all.add(column);
column = vill(column, row + 1, sheet, 251, "Область розница");
column = vill(column, row + 1, sheet, 23, "г. Боровск");
column = vill(column, row + 1, sheet, 212, "г. Обнинск");
column = vill(column, row + 1, sheet, 58, "Жиздринский р-н");
column = vill(column, row + 1, sheet, 5, "Бабынинский р-н");
column = vill(column, row + 1, sheet, 40, "Дзержинский р-н");
column = vill(column, row + 1, sheet, 90, "Козельский р-н");
return column;
}
private int pro5(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ИП Палашичев", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 1, row);
ave_k.add(column);
column = vill(column, row + 1, sheet, 199, "г. Калуга");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 9, "Бабынинский р-н");
return column;
}
private int pro8(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ИП Пешков", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 2, row);
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 144, "Сухиничский р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 155, "Ульяновский р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 138, "Перемышельский р-н");
return column;
}
private int pro3(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО ТД \"Альфа-Трейд\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 1, row);
ave_k.add(column);
column = vill(column, row + 1, sheet, 195, "г. Калуга");
// sheet.addCell(new Label(column, row + 1, "в т.ч. мини АЗС",
// font.tahomaLabelTitle));
// column++;
column = vill(column, row + 1, sheet, 254, "в т.ч. мини АЗС");
return column;
}
private int pro4(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(
new Label(column, row, "ООО \"Луйкойл-Центрнефтепродукт\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 1, row);
ave_k.add(column);
column = vill(column, row + 1, sheet, 255, "г. Калуга розница");
ave_obl_all.add(column);
column = vill(column, row + 1, sheet, 256, "Область розница");
return column;
}
private int pro11(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО \"Восток-Ойл\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 3, row);
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 133, "г. Мосальск");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 123, "Малоярославецкий р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 130, "Мещовский р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 94, "Куйбышевский р-н");
return column;
}
/**
* 合并表格
*
* @param sheet 工作表
* @param mergeInfo 要合并的表格的信息
* @throws RowsExceededException
* @throws NumberFormatException
* @throws WriteException
*/
private void merge(WritableSheet sheet, String[] mergeInfo)
throws RowsExceededException, NumberFormatException, WriteException {
if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {
return;
} else if (!this.isMergeInfo(mergeInfo)) {
return;
} else {
for (String str : mergeInfo) {
String[] temp = str.split(",");
sheet.mergeCells(
Integer.parseInt(temp[1]),
Integer.parseInt(temp[0]),
Integer.parseInt(temp[3]),
Integer.parseInt(temp[2]));
}
}
}
private int pro2(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО \"Газпромнефть-Центр\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 9, row);
column = opt(column, row + 1, sheet, 2);
column = nadbavka(column, row + 1, sheet);
ave_k.add(column);
column = vill(column, row + 1, sheet, 252, "г. Калуга розница");
ave_obl_all.add(column);
column = vill(column, row + 1, sheet, 253, "Область розница");
column = vill(column, row + 1, sheet, 41, "Дзержинский р-н");
column = vill(column, row + 1, sheet, 85, "Износковский р-н");
column = vill(column, row + 1, sheet, 62, "Жиздринский р-н");
column = vill(column, row + 1, sheet, 14, "Бабынинский р-н");
return column;
}
@Override
public void writeExcelFile(HttpServletRequest request, WritableSheet sheet) {
try {
String start_date = StringUtils.nullToStr(request.getParameter("start_date"));
String end_date = StringUtils.nullToStr(request.getParameter("end_date"));
String dept_id = StringUtils.nullToStr(request.getParameter("dept_id"));
String clientId = StringUtils.nullToStr(request.getParameter("clientId"));
String client_name = StringUtils.nullToStr(request.getParameter("client_name"));
String product_name = StringUtils.nullToStr(request.getParameter("product_name"));
String product_kind = StringUtils.nullToStr(request.getParameter("product_kind"));
String xsry_name = StringUtils.nullToStr(request.getParameter("xsry_name"));
String con = "";
con = "日期：" + start_date + "至" + end_date;
if (!clientId.equals("")) {
con += " 客户名称：" + clientId;
}
if (!dept_id.equals("")) {
con += " 部门：" + StaticParamDo.getDeptNameById(dept_id);
}
if (!product_kind.equals("")) {
con += " 商品类别：" + StaticParamDo.getProductKindNameById(product_kind);
}
con += " 报表生成时间：" + DateComFunc.getCurTime();
Label label = null;
// 写统计表标题
sheet.mergeCells(0, 0, 7, 0);
label = new Label(0, 0, "商品序列号销售汇总", this.getFt_title());
sheet.addCell(label);
// 写统计条件
sheet.mergeCells(0, 1, 7, 1);
label = new Label(0, 1, con, this.getFt_item_center());
sheet.addCell(label);
// 写统计表头
label = new Label(0, 2, "日期", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(1, 2, "序列号", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(2, 2, "商品名称", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(3, 2, "商品规格", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(4, 2, "客户名称", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(5, 2, "客户电话", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(6, 2, "销售员", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(7, 2, "销售价格", this.getFt_item_center_bold());
sheet.addCell(label);
List resultList =
productSerialNumXsHzService.getSerialNumXsList(
start_date, end_date, product_kind, product_name, dept_id, xsry_name, clientId);
int hj_nums = 0;
double hj_je = 0;
int k = 3;
// 写统计内容
if (resultList != null && resultList.size() > 0) {
for (int i = 0; i < resultList.size(); i++) {
Map map = (Map) resultList.get(i);
double je = map.get("xsdj") == null ? 0 : ((Double) map.get("xsdj")).doubleValue();
label =
new Label(0, k, StringUtils.nullToStr(map.get("fs_date")), this.getFt_item_left());
sheet.addCell(label);
label =
new Label(1, k, StringUtils.nullToStr(map.get("serial_num")), this.getFt_item_left());
sheet.addCell(label);
label =
new Label(
2, k, StringUtils.nullToStr(map.get("product_name")), this.getFt_item_left());
sheet.addCell(label);
label =
new Label(3, k, StringUtils.nullToStr(map.get("product_xh")), this.getFt_item_left());
sheet.addCell(label);
label =
new Label(
4, k, StringUtils.nullToStr(map.get("client_name")), this.getFt_item_left());
sheet.addCell(label);
label = new Label(5, k, StringUtils.nullToStr(map.get("tel")), this.getFt_item_left());
sheet.addCell(label);
label = new Label(6, k, StringUtils.nullToStr(map.get("jsr")), this.getFt_item_left());
sheet.addCell(label);
label = new Label(7, k, JMath.round(je, 2), this.getFt_item_right());
sheet.addCell(label);
k++;
}
}
} catch (Exception ex) {
log.info(ex);
}
}
public OutputSvod() throws IOException, WriteException, ParseException, SQLException {
/** Формирование дат отчетности */
/** текущие время */
long current = System.currentTimeMillis();
// TODO Заглушка времени
// current = 1366920000000l + 86400000 - 1;
/** заполнение начинается с последнего элемента */
time[time.length - 1] = current;
for (int i = time.length - 2; i >= 0; i--) {
/**
* Если i-ая дата не является Сб или Вс записывается предыдущий день Если i-ая дата Сб или Вс
* - записывается Пятница
*/
/** коэффицент смещения дней */
int mod = 1;
/** День недели i-ого числа */
String day = new SimpleDateFormat("E", Locale.ENGLISH).format(time[i + 1] - 86400000);
/** Корректировка коэффицента смещения, если i-ый день Сб или Вс */
if (day.equals("Sat")) mod = 2;
else if (day.equals("Sun")) mod = 3;
/** Сохраняем нужную дату */
time[i] = time[i + 1] - 86400000 * mod;
}
/** Округление времени к 23:59:59 */
for (int i = 0; i < time.length; i++) {
SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");
Date date = formatter.parse(formatter.format(new Date(time[i])));
time[i] = date.getTime() + 86400000 - 1;
}
// for (long index : time)
// System.out.println(new Date(index));
SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");
WritableWorkbook workbook =
Workbook.createWorkbook(
new File(
"svod_data_" + formatter.format(new Date(System.currentTimeMillis())) + ".xls"));
WritableSheet sheet = workbook.createSheet("Мнониторинг цен", 0);
sheet.addCell(
new Label(
0,
0,
"Мониторинг цен на нефтепродукты, реализуемые на автозаправочных станциях Калуги и Калужской области ",
font.tahoma14ptBold));
sheet.mergeCells(0, 0, 22, 0);
sheet.setRowView(0, 700);
int column = 1;
int row = 4;
column = cape(column, row, sheet);
// калуганефтепродукт
column = pro1(column, row, sheet);
// газпром
column = pro2(column, row, sheet);
// Альфа-трейд
column = pro3(column, row, sheet);
column = cape(column, row, sheet);
// лукойл
column = pro4(column, row, sheet);
// ИП палашичев
column = pro5(column, row, sheet);
// тран азс сервис
column = pro6(column, row, sheet);
// октан
column = pro7(column, row, sheet);
// ПИ пешков
column = pro8(column, row, sheet);
// ИП журавлева
column = pro9(column, row, sheet);
// березка
column = pro10(column, row, sheet);
// Восток ойл
column = pro11(column, row, sheet);
// солид
column = pro12(column, row, sheet);
// экоресурс
column = pro13(column, row, sheet);
// Белова
column = pro14(column, row, sheet);
// Хвастовичи
column = pro15(column, row, sheet);
ave_result(5, 65, sheet);
workbook.write();
workbook.close();
JOptionPane.showMessageDialog(null, "Готово");
}
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();
}
}
/**
* Формирование таблицы "Изменение средней розничной цены"<br>
* <br>
* Номера колонок берутся из 3 массивов:<br>
* - ave_k - по городу Калуга<br>
* - ave_obl_all - розница в области по сетевым(Калуганефтепродукт, Газпромнефть и Лукойл)<br>
* - ave_obl_reg - розница в области по АЗС входяших в мониторинг<br>
* <br>
* <br>
* СРЗНАЧ по области берётся от СРЗНАЧ(СРЗНАЧ(мелких АЗС); Калуганефтепродукта; Газпромнефти;
* Лукойли)
*
* @param column - начальная колонка таблицы
* @param row - начальная строка таблицы
* @param sheet - в этот лист будет заносится таблица
* @throws RowsExceededException
* @throws WriteException
*/
private void ave_result(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException {
/** составление шапки */
sheet.addCell(new Label(column, row, "Изменение средней розничной цены", font.tahomaValue));
sheet.mergeCells(column, row, column + 6, row++);
sheet.addCell(new Label(column, row, "г. Калуга", font.tahomaValue));
sheet.mergeCells(column, row, column + 2, row);
sheet.addCell(new Label(column + 4, row, "Калужская область", font.tahomaValue));
sheet.mergeCells(column + 4, row, column + 6, row++);
/*
* обработка данных - формирование формул
*/
for (int i_toplivo = 0; i_toplivo < label_toplivo.length && i_toplivo < 4; i_toplivo++) {
/** Наименование топлива в описание строки */
for (int i = 0; i < 2; i++)
sheet.addCell(
new Label(column + i * 4, row + i_toplivo, label_toplivo[i_toplivo], font.tahomaValue));
/** Формирование формул СРЗНАЧ */
/** По Калуге */
// в процентах
String average_kaluga_per = getFormulaAVERAGE(getFormulaCells(50 + i_toplivo, ave_k));
// в рублях
String average_kaluga_val = getFormulaAVERAGE(getFormulaCells(58 + i_toplivo, ave_k));
/** по области */
// в процентах
sheet.addCell(
new Formula(
column + 9,
row + i_toplivo,
getFormulaCOUNT(getFormulaCells(50 + i_toplivo, ave_obl_reg)),
font.tahomaValue_white));
sheet.addCell(
new Formula(
column + 8,
row + i_toplivo,
getFormulaSUM(getFormulaCells(50 + i_toplivo, ave_obl_reg)),
font.tahomaValue_white));
String div =
toColumnExcel(column + 8)
+ (row + i_toplivo + 1)
+ "/"
+ toColumnExcel(column + 9)
+ (row + i_toplivo + 1);
sheet.addCell(
new Formula(
column + 7,
row + i_toplivo,
"IF(ISERROR(" + div + "),0," + div + ")",
font.tahomaValue_white));
Vector<String> ave_obl_all_per = getFormulaCells(50 + i_toplivo, ave_obl_all);
ave_obl_all_per.add(toColumnExcel(column + 7) + (row + i_toplivo + 1));
String average_obl_per = getFormulaAVERAGE(ave_obl_all_per);
// в рублях
sheet.addCell(
new Formula(
column + 12,
row + i_toplivo,
getFormulaCOUNT(getFormulaCells(58 + i_toplivo, ave_obl_reg)),
font.tahomaValue_white));
sheet.addCell(
new Formula(
column + 11,
row + i_toplivo,
getFormulaSUM(getFormulaCells(58 + i_toplivo, ave_obl_reg)),
font.tahomaValue_white));
String div2 =
toColumnExcel(column + 11)
+ (row + i_toplivo + 1)
+ "/"
+ toColumnExcel(column + 12)
+ (row + i_toplivo + 1);
sheet.addCell(
new Formula(
column + 10,
row + i_toplivo,
"IF(ISERROR(" + div2 + "),0," + div2 + ")",
font.tahomaValue_white));
Vector<String> ave_obl_all_val = getFormulaCells(58 + i_toplivo, ave_obl_all);
ave_obl_all_val.add(toColumnExcel(column + 10) + (row + i_toplivo + 1));
String average_obl_val = getFormulaAVERAGE(ave_obl_all_val);
/*
* Запись формул в лист
*/
/** Калуга */
sheet.addCell(
new Formula(
column + 1,
row + i_toplivo,
"IF(ISERROR(" + average_kaluga_per + "),0," + average_kaluga_per + ")",
font.tahomaValuePer));
sheet.addCell(
new Formula(
column + 2,
row + i_toplivo,
"IF(ISERROR(" + average_kaluga_val + "),0," + average_kaluga_val + ")",
font.tahomaValue));
/** Область */
sheet.addCell(
new Formula(
column + 5,
row + i_toplivo,
"IF(ISERROR(" + average_obl_per + "),0," + average_obl_per + ")",
font.tahomaValuePer));
sheet.addCell(
new Formula(
column + 6,
row + i_toplivo,
"IF(ISERROR(" + average_obl_val + "),0," + average_obl_val + ")",
font.tahomaValue));
}
}
/**
* Создание шапки с датами и марками топлива
*
* @param column - номер первой колонки
* @param row - номер первой строки
* @param sheet - лист, на котором будет записываться
* @throws RowsExceededException
* @throws WriteException
*/
private int cape(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException {
/**
* формат записи даты<br>
* Пример 26.04.2013 г.
*/
SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");
/** Список надписей под результатом */
String[] result = {"Изменение цен %", "Изменение цен руб."};
/*
* Поле "Дата"
*/
sheet.addCell(new Label(column, row, "Дата", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column, row + 1);
sheet.setColumnView(column, 15);
/** Поле "Марка бензина" */
sheet.addCell(new Label(column + 1, row, "Марка бензина", font.tahoma9ptBoldMedion));
sheet.mergeCells(column + 1, row, column + 1, row + 1);
sheet.setColumnView(column + 1, 10);
/** первая строчка под наименование поставщика */
sheet.setRowView(row, 900);
/** вторая строчка под пояснение выбранного значения */
sheet.setRowView(row + 1, 2000);
/** Запись даты и марки топлива */
for (int i = 0; i < time.length; i++) {
/** дата */
sheet.addCell(
new Label(
column,
row + 2 + i * 7,
formatter.format(new Date(time[i])) + " г.",
font.tahoma9ptBoldMedion));
/** склеивание со строчками марки топлива */
sheet.mergeCells(column, row + 2 + i * 7, column, row + 2 + i * 7 + 6);
/** запись марки топлива */
for (int p = 0; p < label_toplivo.length; p++) {
sheet.addCell(
new Label(column + 1, row + 2 + i * 7 + p, label_toplivo[p], font.tahomaLabelToplivo));
}
}
// для удобства
int nextRow = row + 2 + 7 * time.length;
/** Запись результата */
for (String res : result) {
/** Титульник */
sheet.addCell(new Label(column, nextRow, res, font.tahoma9ptBoldMedion));
sheet.mergeCells(column, nextRow, column + 1, nextRow);
/** марка топлива */
for (int p = 0; p < label_toplivo.length; p++) {
sheet.addCell(
new Label(column, nextRow + 1 + p, label_toplivo[p], font.tahoma9ptBoldMedion));
sheet.mergeCells(column, nextRow + 1 + p, column + 1, nextRow + 1 + p);
}
/** смещение к след пункту, без потери номера строки */
nextRow += label_toplivo.length + 1;
}
return column + 2;
}
/** 写Excel文件 */
public void writeExcelFile(HttpServletRequest request, WritableSheet sheet) {
try {
String start_date = StringUtils.nullToStr(request.getParameter("start_date"));
String end_date = StringUtils.nullToStr(request.getParameter("end_date"));
String product_kind = StringUtils.nullToStr(request.getParameter("product_kind"));
String product_name = StringUtils.nullToStr(request.getParameter("product_name"));
String store_id = StringUtils.nullToStr(request.getParameter("store_id"));
String isKc0 = StringUtils.nullToStr(request.getParameter("isKc0")); // 是否显示0库存商品
String isFse0 = StringUtils.nullToStr(request.getParameter("isFse0")); // 是否显示0发生额商品
String conStr = "";
conStr += "日期：" + start_date + "至" + end_date;
if (!store_id.equals("")) {
conStr += " 库房：" + StaticParamDo.getStoreNameById(store_id);
}
if (!product_kind.equals("")) {
conStr += " 商品类别：" + StaticParamDo.getProductKindNameById(product_kind);
}
Label label = null;
// 写统计表标题
sheet.mergeCells(0, 0, 7, 0);
label = new Label(0, 0, "库存数量汇总", this.getFt_title());
sheet.addCell(label);
// 写统计条件
sheet.mergeCells(0, 1, 7, 1);
label = new Label(0, 1, conStr, this.getFt_item_center());
sheet.addCell(label);
// 写统计表头
label = new Label(0, 2, "商品编码", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(1, 2, "商品名称", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(2, 2, "规格", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(3, 2, "单位", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(4, 2, "期初数量", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(5, 2, "收入数量", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(6, 2, "发出数量", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(7, 2, "期末结存", this.getFt_item_center_bold());
sheet.addCell(label);
List productKcList = kcMxReportService.getProductKcList(product_kind, product_name, store_id);
int hj_qc_nums = 0; // 期初数量合计
int hj_ck_nums = 0; // 出库数量合计
int hj_rk_nums = 0; // 入库数量合计
int hj_jc_nums = 0; // 合计结存数量（库存总商品数）
if (productKcList != null && productKcList.size() > 0) {
int xh = 0;
int k = 3;
for (int i = 0; i < productKcList.size(); i++) {
Map map = (Map) productKcList.get(i);
String product_id = StringUtils.nullToStr(map.get("product_id"));
String product_name2 = StringUtils.nullToStr(map.get("product_name"));
String product_xh = StringUtils.nullToStr(map.get("product_xh"));
String dw = StringUtils.nullToStr(map.get("dw"));
// 根据商品编号、开始时间、库房编号取库存期初情况
Map qcMap = kcMxReportService.getKcqcMxMap(product_id, start_date, store_id);
String strNums = "0"; // 期初数
if (qcMap != null) {
strNums = StringUtils.nullToStr(qcMap.get("nums"));
if (strNums.equals("")) {
strNums = "0";
}
}
int qc_nums = new Integer(strNums).intValue(); // 期初数
int rk_nums =
kcMxReportService.getRkNums(product_id, start_date, end_date, store_id); // 收入数量
int ck_nums =
kcMxReportService.getCkNums(product_id, start_date, end_date, store_id); // 发出数量
int jc_nums = qc_nums + rk_nums - ck_nums;
boolean is_kc = true;
if (isKc0.equals("否")) { // 显示0库存商品
if (jc_nums == 0) {
is_kc = false;
}
}
boolean is_fs = true;
if (isFse0.equals("否")) { // 显示0发生额商品
if (rk_nums == 0 && ck_nums == 0) {
is_fs = false;
}
}
if (is_kc && is_fs) {
hj_qc_nums += qc_nums;
hj_rk_nums += rk_nums;
hj_ck_nums += ck_nums;
hj_jc_nums += jc_nums;
xh++;
label = new Label(0, k, product_id, this.getFt_item_center());
sheet.addCell(label);
label = new Label(1, k, product_name2, this.getFt_item_left());
sheet.addCell(label);
label = new Label(2, k, product_xh, this.getFt_item_left());
sheet.addCell(label);
label = new Label(3, k, dw, this.getFt_item_center());
sheet.addCell(label);
label = new Label(4, k, qc_nums + "", this.getFt_item_right());
sheet.addCell(label);
label = new Label(5, k, rk_nums + "", this.getFt_item_right());
sheet.addCell(label);
label = new Label(6, k, ck_nums + "", this.getFt_item_right());
sheet.addCell(label);
label = new Label(7, k, jc_nums + "", this.getFt_item_right());
sheet.addCell(label);
k++;
}
}
label = new Label(0, k, "合计", this.getFt_item_center_bold());
sheet.addCell(label);
label = new Label(1, k, "", this.getFt_item_left());
sheet.addCell(label);
label = new Label(2, k, "", this.getFt_item_left());
sheet.addCell(label);
label = new Label(3, k, "", this.getFt_item_center());
sheet.addCell(label);
label = new Label(4, k, hj_qc_nums + "", this.getFt_item_right());
sheet.addCell(label);
label = new Label(5, k, hj_rk_nums + "", this.getFt_item_right());
sheet.addCell(label);
label = new Label(6, k, hj_ck_nums + "", this.getFt_item_right());
sheet.addCell(label);
label = new Label(7, k, hj_jc_nums + "", this.getFt_item_right());
sheet.addCell(label);
}
} catch (Exception ex) {
log.info(ex);
}
}
public static void main(String args[]) {
try {
// 打开文件
WritableWorkbook book = Workbook.createWorkbook(new File("/home/uc/local/sandy/测试.xls"));
// 生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheetOne = book.createSheet("第一页", 0);
/** 定义单元格样式 */
WritableFont wf_title =
new WritableFont(
WritableFont.ARIAL,
11,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableFont wf_head =
new WritableFont(
WritableFont.ARIAL,
11,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableFont wf_table =
new WritableFont(
WritableFont.ARIAL,
11,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义
wcf_title.setBackground(jxl.format.Colour.WHITE); // 设置单元格的背景颜色
wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
wcf_title.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义
wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色
wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
wcf_title1.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); // 单元格定义
wcf_title2.setBackground(jxl.format.Colour.YELLOW2); // 设置单元格的背景颜色
wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
wcf_title2.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head);
wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);
wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);
wcf_head1.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);
WritableCellFormat wcf_head2 = new WritableCellFormat(wf_head);
wcf_head2.setBackground(jxl.format.Colour.YELLOW2);
wcf_head2.setAlignment(jxl.format.Alignment.CENTRE);
wcf_head2.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);
WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table);
wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN);
wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table1.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);
WritableCellFormat wcf_table2 = new WritableCellFormat(wf_table);
wcf_table2.setBackground(jxl.format.Colour.YELLOW2);
wcf_table2.setAlignment(jxl.format.Alignment.CENTRE);
wcf_table2.setBorder(
jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);
sheetOne.setColumnView(0, 15); // 设置列的宽度
sheetOne.setColumnView(1, 15); // 设置列的宽度
sheetOne.setColumnView(2, 15); // 设置列的宽度
sheetOne.setColumnView(3, 15); // 设置列的宽度
sheetOne.setColumnView(4, 15); // 设置列的宽度
sheetOne.setColumnView(5, 15); // 设置列的宽度
sheetOne.setColumnView(6, 15); // 设置列的宽度
sheetOne.setColumnView(7, 15); // 设置列的宽度
sheetOne.setColumnView(8, 15); // 设置列的宽度
sheetOne.setColumnView(9, 15); // 设置列的宽度
sheetOne.setColumnView(10, 15); // 设置列的宽度
sheetOne.setColumnView(11, 15); // 设置列的宽度
sheetOne.setColumnView(12, 15); // 设置列的宽度
sheetOne.setColumnView(13, 15); // 设置列的宽度
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// 以及单元格内容为test
Label title = new Label(0, 0, "统计", wcf_title);
Label titleOne = new Label(0, 1, "统计1", wcf_title1);
Label titleTwo = new Label(2, 1, "统计2", wcf_title2);
Label column1 = new Label(0, 2, "姓名", wcf_head1);
Label column2 = new Label(1, 2, "所选课程", wcf_head1);
Label column3 = new Label(2, 2, "姓名", wcf_head2);
Label column4 = new Label(3, 2, "所选课程", wcf_head2);
// 或者WritableCell cell = new jxl.write.Number(column, row, value, wcf)
// 将定义好的单元格添加到工作表中
sheetOne.addCell(title);
sheetOne.addCell(titleOne);
sheetOne.addCell(titleTwo);
sheetOne.addCell(column1);
sheetOne.addCell(column2);
sheetOne.addCell(column3);
sheetOne.addCell(column4);
// 合： 第1列第1行 到 第13列第1行
sheetOne.mergeCells(0, 0, 3, 0);
sheetOne.mergeCells(0, 1, 1, 1);
sheetOne.mergeCells(2, 1, 3, 1);
/*动态数据 */
Label content1 = new Label(0, 3, "张三", wcf_table1);
Label content2 = new Label(0, 4, "张三", wcf_table1);
Label content3 = new Label(0, 5, "张三", wcf_table1);
Label kecheg1 = new Label(1, 3, "语文", wcf_table1);
Label kecheg2 = new Label(1, 4, "数学", wcf_table1);
Label kecheg3 = new Label(1, 5, "英语", wcf_table1);
sheetOne.addCell(content1);
sheetOne.addCell(content2);
sheetOne.addCell(content3);
sheetOne.addCell(kecheg1);
sheetOne.addCell(kecheg2);
sheetOne.addCell(kecheg3);
sheetOne.mergeCells(0, 3, 0, 2 + 3);
Label content11 = new Label(2, 3, "李四", wcf_table2);
Label content22 = new Label(2, 4, "李四", wcf_table2);
Label content33 = new Label(2, 5, "李四", wcf_table2);
Label kecheg11 = new Label(3, 3, "语文", wcf_table2);
Label kecheg22 = new Label(3, 4, "数学", wcf_table2);
Label kecheg33 = new Label(3, 5, "英语", wcf_table2);
sheetOne.addCell(content11);
sheetOne.addCell(content22);
sheetOne.addCell(content33);
sheetOne.addCell(kecheg11);
sheetOne.addCell(kecheg22);
sheetOne.addCell(kecheg33);
sheetOne.mergeCells(2, 3, 2, 2 + 3);
// 写入数据并关闭文件
book.write();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
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();
}
/** {@inheritDoc} */
@Override
public String exportData(String baseUri, List<SecrecyStatisticsDto> list, District district) {
boolean flag = false;
int currentRow = 2;
JExcelUtils excelUtils = new JExcelUtils();
String path = baseUri + "exportExcel/staticDatas_" + System.currentTimeMillis() + ".xls";
File file = new File(path);
String sheetName = district.getDistrictName() + "级机关单位数据录入情况一览表";
try {
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file));
WritableWorkbook workbook = Workbook.createWorkbook(bos);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
excelUtils.initialSheetSetting(sheet);
// 第一行 组装
Label labelIndex = new Label(0, 0, "单位名称", excelUtils.getTitleCellFormat());
sheet.addCell(labelIndex);
for (int i = 1; i < 5; i++) {
Label label = new Label(i, 0, "保密工作机构", excelUtils.getTitleCellFormat());
sheet.mergeCells(1, 0, 4, 0);
sheet.addCell(label);
}
for (int i = 5; i < 11; i++) {
Label label = new Label(i, 0, "保密业务", excelUtils.getTitleCellFormat());
sheet.mergeCells(5, 0, 10, 0);
sheet.addCell(label);
}
// 第二行 组装
Label label0 = new Label(0, 1, "单位名称", excelUtils.getTitleCellFormat());
sheet.addCell(label0);
for (int i = 1; i < 3; i++) {
Label label = new Label(i, 1, "机构信息", excelUtils.getTitleCellFormat());
sheet.mergeCells(1, 1, 2, 1);
sheet.addCell(label);
}
Label label3 = new Label(3, 1, "机构成员", excelUtils.getTitleCellFormat());
sheet.addCell(label3);
Label label4 = new Label(4, 1, "保密办成员", excelUtils.getTitleCellFormat());
sheet.addCell(label4);
for (int i = 5; i < 7; i++) {
Label label = new Label(i, 1, "要害部门", excelUtils.getTitleCellFormat());
sheet.mergeCells(5, 1, 6, 1);
sheet.addCell(label);
}
for (int i = 7; i < 9; i++) {
Label label = new Label(i, 1, "要害部位", excelUtils.getTitleCellFormat());
sheet.mergeCells(7, 1, 8, 1);
sheet.addCell(label);
}
for (int i = 9; i < 11; i++) {
Label label = new Label(i, 1, "涉密人员", excelUtils.getTitleCellFormat());
sheet.mergeCells(9, 1, 10, 1);
sheet.addCell(label);
}
// 第三行标题
for (int i = 0; i < dataTitles.length; i++) {
Label label = new Label(i, 2, dataTitles[i], excelUtils.getTitleCellFormat());
sheet.addCell(label);
}
// 统计数字
Integer num1 = 0;
Integer num2 = 0;
Integer num3 = 0;
Integer num4 = 0;
Integer num5 = 0;
Integer num6 = 0;
Integer num7 = 0;
Integer num8 = 0;
Integer num9 = 0;
Integer num10 = 0;
// 数据行
for (SecrecyStatisticsDto ob : list) {
currentRow += 1;
String[] data =
new String[] {
ob.getOrganName(),
ob.getNumGroupEntering() + "",
ob.getNumGroupReprot() + "",
ob.getNumGroupMember() + "",
ob.getNumSecrecyWorkOrganMember() + "",
ob.getNumKeysectionEntering() + "",
ob.getNumKeyPartReport() + "",
ob.getNumKeyPartEntering() + "",
ob.getNumKeyPartReport() + "",
ob.getNumSecrecyPersonEntering() + "",
ob.getNumSecrecyPersonReport() + ""
};
num1 += ob.getNumGroupEntering();
num2 += ob.getNumGroupReprot();
num3 += ob.getNumGroupMember();
num4 += ob.getNumSecrecyWorkOrganMember();
num5 += ob.getNumKeysectionEntering();
num6 += ob.getNumKeyPartReport();
num7 += ob.getNumKeyPartEntering();
num8 += ob.getNumKeyPartReport();
num9 += ob.getNumSecrecyPersonEntering();
num10 += ob.getNumSecrecyPersonReport();
excelUtils.insertRowData(
sheet, currentRow, data, excelUtils.getDataCellFormat(CellType.LABEL));
}
// 最后一行 组装
currentRow = currentRow + 1;
String[] dataLast =
new String[] {
"统计结果", num1 + "", num2 + "", num3 + "", num4 + "", num5 + "", num6 + "", num7 + "",
num8 + "", num9 + "", num10 + ""
};
excelUtils.insertRowData(
sheet, currentRow, dataLast, excelUtils.getDataCellFormat(CellType.LABEL));
workbook.write();
workbook.close();
flag = true;
} catch (FileNotFoundException e) {
LOGGER.error(e.getMessage(), e);
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
} catch (RowsExceededException e) {
LOGGER.error(e.getMessage(), e);
} catch (WriteException e) {
LOGGER.error(e.getMessage(), e);
}
return (flag ? path : null);
}
public void generateReport(
Program selProgram, List<OrganisationUnit> orgUnitList, Date sDate, Date eDate)
throws Exception {
String raFolderName = reportService.getRAFolderName();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String query = "";
int rowStart = 3;
int colStart = 1;
int rowCount = rowStart;
int colCount = colStart;
// String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName +
// File.separator + "output" + File.separator + UUID.randomUUID().toString() + ".xls";
String outputReportPath =
System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER;
File newdir = new File(outputReportPath);
if (!newdir.exists()) {
newdir.mkdirs();
}
outputReportPath += File.separator + UUID.randomUUID().toString() + ".xls";
WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath));
WritableSheet sheet0 = outputReportWorkbook.createSheet(selProgram.getName(), 0);
try {
List<PatientIdentifierType> patientIdentifierTypes =
new ArrayList<PatientIdentifierType>(
patientIdentifierTypeService.getAllPatientIdentifierTypes());
Collections.sort(patientIdentifierTypes, new PatientIdentifierTypeComparator());
List<PatientAttribute> patientAttributes =
new ArrayList<PatientAttribute>(patientAttributeService.getAllPatientAttributes());
Collections.sort(patientAttributes, new PatientAttributeComparator());
List<ProgramStage> programStages = new ArrayList<ProgramStage>(selProgram.getProgramStages());
Collections.sort(programStages, new ProgramStageOrderComparator());
Map<ProgramStage, List<DataElement>> programStageDataElementMap =
new HashMap<ProgramStage, List<DataElement>>();
for (ProgramStage programStage : programStages) {
List<ProgramStageDataElement> programStageDataElements =
new ArrayList<ProgramStageDataElement>(programStage.getProgramStageDataElements());
List<DataElement> dataElements = new ArrayList<DataElement>();
for (ProgramStageDataElement programStageDataElement : programStageDataElements) {
dataElements.add(programStageDataElement.getDataElement());
}
Collections.sort(dataElements, new IdentifiableObjectNameComparator());
programStageDataElementMap.put(programStage, dataElements);
}
// Printing Header Information
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "OrgUnit Hierarchy", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "OrgUnit", getCellFormat1()));
colCount++;
for (PatientIdentifierType patientIdentifierType : patientIdentifierTypes) {
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(
new Label(colCount, rowCount - 1, patientIdentifierType.getName(), getCellFormat1()));
colCount++;
}
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Benificiary ID", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Benificiary Name", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Gender", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Age", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Data of Birth", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Blood Group", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Registration Date", getCellFormat1()));
colCount++;
for (PatientAttribute patientAttribute : patientAttributes) {
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(
new Label(colCount, rowCount - 1, patientAttribute.getName(), getCellFormat1()));
colCount++;
}
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Incident Date", getCellFormat1()));
colCount++;
sheet0.mergeCells(colCount, rowCount - 1, colCount, rowCount);
sheet0.addCell(new Label(colCount, rowCount - 1, "Enrollment Date", getCellFormat1()));
colCount++;
for (ProgramStage programStage : programStages) {
List<DataElement> dataElementList =
new ArrayList<DataElement>(programStageDataElementMap.get(programStage));
sheet0.mergeCells(
colCount, rowCount - 1, colCount + dataElementList.size() + 1, rowCount - 1);
sheet0.addCell(new Label(colCount, rowCount - 1, programStage.getName(), getCellFormat1()));
sheet0.addCell(new Label(colCount, rowCount, "Due Date", getCellFormat1()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, "Execution Date", getCellFormat1()));
colCount++;
for (DataElement dataElement : dataElementList) {
sheet0.addCell(
new Label(
colCount,
rowCount,
dataElement.getName() + "--" + dataElement.getType(),
getCellFormat1()));
colCount++;
}
}
rowCount++;
for (OrganisationUnit orgUnit : orgUnitList) {
if (sDate != null && eDate != null) {
query =
"SELECT patient.patientid, programinstance.programinstanceid,programinstance.dateofincident,programinstance.enrollmentdate FROM programinstance INNER JOIN patient "
+ " ON programinstance.patientid = patient.patientid "
+ " WHERE patient.organisationunitid = "
+ orgUnit.getId()
+ " AND programinstance.programid = "
+ selProgram.getId()
+ " AND patient.registrationdate >= '"
+ startDate
+ "'"
+ " AND patient.registrationdate <= '"
+ endDate
+ "' "
+ " AND enddate IS NULL";
} else {
query =
"SELECT patient.patientid, programinstance.programinstanceid,programinstance.dateofincident,programinstance.enrollmentdate FROM programinstance INNER JOIN patient "
+ " ON programinstance.patientid = patient.patientid "
+ " WHERE patient.organisationunitid = "
+ orgUnit.getId()
+ " AND programinstance.programid = "
+ selProgram.getId()
+ " AND enddate IS NULL";
}
SqlRowSet sqlResultSet = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet != null) {
int count = 1;
String orgUnitBranch = "";
sqlResultSet.beforeFirst();
while (sqlResultSet.next()) {
colCount = colStart;
if (orgUnit.getParent() != null) {
orgUnitBranch = getOrgunitBranch(orgUnit.getParent());
} else {
orgUnitBranch = " ";
}
sheet0.addCell(new Label(colCount, rowCount, orgUnitBranch, getCellFormat2()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, orgUnit.getName(), getCellFormat2()));
colCount++;
int patientId = sqlResultSet.getInt(1);
int programInstanceId = sqlResultSet.getInt(2);
Date dateOfIncident = sqlResultSet.getDate(3);
Date dateOfEnrollment = sqlResultSet.getDate(4);
Patient patient = patientService.getPatient(patientId);
// Patient Identifier Details
for (PatientIdentifierType patientIdentifierType : patientIdentifierTypes) {
query =
"SELECT identifier from patientidentifier WHERE patientidentifiertypeid = "
+ patientIdentifierType.getId()
+ " AND patientid = "
+ patient.getId();
SqlRowSet sqlResultSet1 = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet1 != null && sqlResultSet1.next()) {
String value = sqlResultSet1.getString(1);
if (value != null && !value.trim().equalsIgnoreCase("")) {
sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
colCount++;
}
// Patient Properties
sheet0.addCell(
new Label(colCount, rowCount, patient.getId().toString(), getCellFormat2()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, patient.getFullName(), getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(colCount, rowCount, patient.getTextGender(), getCellFormat2()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, patient.getAge(), getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(
colCount,
rowCount,
simpleDateFormat.format(patient.getBirthDate()),
getCellFormat2()));
colCount++;
/**
* TODO BloodGroup is removed from Patient Object, so need to change this accordingly
*/
sheet0.addCell(
new Label(colCount, rowCount, "" /*patient.getBloodGroup()*/, getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(
colCount,
rowCount,
simpleDateFormat.format(patient.getRegistrationDate()),
getCellFormat2()));
colCount++;
// Patient Attribute Values
for (PatientAttribute patientAttribute : patientAttributes) {
query =
"SELECT value from patientattributevalue WHERE patientid = "
+ patient.getId()
+ " AND patientattributeid = "
+ patientAttribute.getId();
SqlRowSet sqlResultSet1 = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet1 != null && sqlResultSet1.next()) {
String value = sqlResultSet1.getString(1);
if (value != null && !value.trim().equalsIgnoreCase("")) {
sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
colCount++;
}
// Program Enrollment Details
sheet0.addCell(
new Label(
colCount, rowCount, simpleDateFormat.format(dateOfIncident), getCellFormat2()));
colCount++;
sheet0.addCell(
new Label(
colCount,
rowCount,
simpleDateFormat.format(dateOfEnrollment),
getCellFormat2()));
colCount++;
// ProgramStage Values
for (ProgramStage programStage : programStages) {
query =
"SELECT programstageinstanceid,duedate,executiondate from programstageinstance "
+ " WHERE programinstanceid = "
+ programInstanceId
+ " AND programstageid = "
+ programStage.getId();
SqlRowSet sqlResultSet2 = jdbcTemplate.queryForRowSet(query);
Integer programStageInstanceId = 0;
if (sqlResultSet2 != null && sqlResultSet2.next()) {
programStageInstanceId = sqlResultSet2.getInt(1);
// ProgramStage DueDate and Execution Date
Date dueDate = sqlResultSet2.getDate(2);
Date exeDate = sqlResultSet2.getDate(3);
if (dueDate != null) {
String dueDateStr = simpleDateFormat.format(dueDate);
sheet0.addCell(new Label(colCount, rowCount, dueDateStr, getCellFormat3()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
}
colCount++;
if (exeDate != null) {
String exeDateStr = simpleDateFormat.format(exeDate);
sheet0.addCell(new Label(colCount, rowCount, exeDateStr, getCellFormat3()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
}
colCount++;
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
colCount++;
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat3()));
colCount++;
}
for (DataElement dataElement : programStageDataElementMap.get(programStage)) {
query =
"SELECT value from patientdatavalue WHERE programstageinstanceid = "
+ programStageInstanceId
+ " AND dataelementid = "
+ dataElement.getId();
// " AND organisationunitid = " + orgUnit.getId();
SqlRowSet sqlResultSet3 = jdbcTemplate.queryForRowSet(query);
if (sqlResultSet3 != null && sqlResultSet3.next()) {
String value = sqlResultSet3.getString(1);
if (dataElement.getType().equalsIgnoreCase(DataElement.VALUE_TYPE_BOOL)) {
if (value.equalsIgnoreCase("false")) value = "No";
else value = "Yes";
}
if (value != null && !value.trim().equalsIgnoreCase("")) {
sheet0.addCell(new Label(colCount, rowCount, value, getCellFormat2()));
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
} else {
sheet0.addCell(new Label(colCount, rowCount, "-", getCellFormat2()));
}
colCount++;
}
}
rowCount++;
}
}
}
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
e.printStackTrace();
}
outputReportWorkbook.write();
outputReportWorkbook.close();
fileName = selProgram.getName() + ".xls";
File outputReportFile = new File(outputReportPath);
inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));
outputReportFile.deleteOnExit();
}
