/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName sheet名称,默认为sheet1
* @param content Map,要生成excel的数据来源
* @param os excel输出流
*/
public void exportFormatExcel(
Map<String, String[][]> content,
String[] salary_name_array,
String sheetName,
OutputStream os) {
if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
int index = 0;
for (int k = 0; k < salary_name_array.length; k++) {
String[][] value = (String[][]) content.get(salary_name_array[k]);
if (value != null && value.length > 0) {
if (index != 0) {
index++;
}
WritableCellFormat format1 = new WritableCellFormat();
format1.setAlignment(jxl.format.Alignment.LEFT);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label1 = new Label(0, index, salary_name_array[k], format1);
sheet.addCell(label1);
for (int i = 0; i < value.length; i++) {
index++;
for (int j = 0; j < value[i].length; j++) {
if (value[i][j] == null) {
value[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, index, value[i][j], format);
sheet.addCell(label);
}
}
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void addRowTop(WritableSheet ws) throws Exception {
WritableFont Bwf =
new WritableFont(
WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体：Arial,大小为18号,粗体，非斜体
Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色
// 创建单元格格式：
jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf);
CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐
CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐
CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色－－也可以设置其他颜色)
CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线
CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线
List<Label> labelList = new ArrayList<Label>();
labelList.add(new Label(0, 0, "商品名称", CwcfF)); //
labelList.add(new Label(1, 0, "单位", CwcfF)); //
labelList.add(new Label(2, 0, "销售数量", CwcfF)); //
labelList.add(new Label(3, 0, "销售成本", CwcfF));
labelList.add(new Label(4, 0, "销售金额", CwcfF));
labelList.add(new Label(5, 0, "利润", CwcfF));
labelList.add(new Label(6, 0, "利润率", CwcfF));
for (int j = 0; j < labelList.size(); j++) {
ws.addCell(labelList.get(j));
}
for (int i = 0; i < ws.getColumns(); i++) {
Cell cell = ws.getCell(i, 0);
ws.setColumnView(i, cell.getContents().length() * 4);
}
// ws.setRowView(0, 80*4);
}
/**
* 采用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 WritableCellFormat getCellFormat2() throws Exception {
WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
WritableCellFormat wCellformat = new WritableCellFormat(arialBold);
wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat.setAlignment(Alignment.CENTRE);
wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
return wCellformat;
}
/**
* 取报表标题单元格格式
*
* @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;
}
public WritableCellFormat getCellFormat1() throws Exception {
WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat wCellformat = new WritableCellFormat(arialBold);
wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat.setAlignment(Alignment.CENTRE);
wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
wCellformat.setBackground(Colour.GRAY_50);
wCellformat.setWrap(true);
return wCellformat;
}
/**
* 单元格左对齐
*
* @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_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_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;
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName sheet名称,默认为sheet1
* @param content 二维数组,要生成excel的数据来源
* @param os excel输出流
*/
public void exportFormatExcel(String[][] content, String sheetName, OutputStream os) {
if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j], format);
sheet.addCell(label);
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void exportReport2Excel(
ExportMaterialReportBean bean,
ExportMaterialReportDTO results,
HttpServletRequest request,
HttpServletResponse response) {
try {
String outputFileName =
"/files/temp/BaoCaoNhapXuatTonNPL" + System.currentTimeMillis() + ".xls";
String reportTemplate =
request
.getSession()
.getServletContext()
.getRealPath("/files/export/ExportMaterialReport.xls");
String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName);
Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate));
WritableWorkbook workbook =
Workbook.createWorkbook(new File(export2FileName), templateWorkbook);
WritableSheet sheet = workbook.getSheet(0);
WritableFont normalFont =
new WritableFont(
WritableFont.TIMES,
12,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
normalFormat.setAlignment(Alignment.LEFT);
normalFormat.setWrap(true);
normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
WritableFont boldFont =
new WritableFont(
WritableFont.TIMES,
12,
WritableFont.BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat headerFormat = new WritableCellFormat(boldFont);
headerFormat.setAlignment(Alignment.CENTRE);
headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
headerFormat.setWrap(true);
headerFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
headerFormat.setBackground(jxl.format.Colour.GRAY_25);
WritableCellFormat boldFormat = new WritableCellFormat(boldFont);
boldFormat.setAlignment(Alignment.CENTRE);
boldFormat.setWrap(true);
boldFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
DecimalFormat decimalFormat = new DecimalFormat("###,###.##");
Date from = bean.getFromDate();
Label fromCell =
new Label(
1, 1, from != null ? DateUtils.date2String(from, "dd/MM/yyyy") : "", boldFormat);
sheet.addCell(fromCell);
Date to = bean.getToDate();
Label toCell =
new Label(1, 2, to != null ? DateUtils.date2String(to, "dd/MM/yyyy") : "", boldFormat);
sheet.addCell(toCell);
int startRow = 5;
List<ExportMaterialReportDetailDTO> initialValue = results.getInitialValue();
Map<String, Double> mapImportValue = results.getMapImportValue();
Map<String, Double> mapExportUtilDateValue = results.getMapExportUtilDateValue();
Map<String, Double> mapExportDuringDateValue = results.getMapExportDuringDateValue();
int index;
CellValue[] resValue;
Double iVal;
Double exportToVal;
Double importVal;
Double exportVal;
Double remainVal;
String key, origin;
for (ExportMaterialReportDetailDTO initVal : initialValue) {
origin = initVal.getOrigin() != null ? initVal.getOrigin().getOriginID().toString() : "";
key = initVal.getMaterial().getMaterialID() + "_" + origin;
exportToVal =
mapExportUtilDateValue.get(key) != null ? mapExportUtilDateValue.get(key) : 0d;
iVal =
initVal.getQuantity() != null ? initVal.getQuantity() - exportToVal : 0 - exportToVal;
importVal = mapImportValue.get(key) != null ? mapImportValue.get(key) : 0d;
exportVal =
mapExportDuringDateValue.get(key) != null ? mapExportDuringDateValue.get(key) : 0d;
remainVal = iVal + importVal - exportVal;
index = 0;
resValue = new CellValue[10];
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getOrigin() != null ? initVal.getOrigin().getName() : "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getMaterial() != null ? initVal.getMaterial().getName() : "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
StringUtils.isNotBlank(initVal.getCode()) ? initVal.getCode() : "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getMaterial() != null
? initVal.getMaterial().getUnit() != null
? initVal.getMaterial().getUnit().getName()
: ""
: "");
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(iVal));
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(importVal));
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(exportVal));
resValue[index++] = new CellValue(CellDataType.STRING, decimalFormat.format(remainVal));
resValue[index++] = new CellValue(CellDataType.STRING, "");
resValue[index++] =
new CellValue(
CellDataType.STRING,
initVal.getImportDate() != null
? DateUtils.date2String(initVal.getImportDate(), "dd/MM/yyyy")
: "");
ExcelUtil.addRow(
sheet, startRow++, resValue, normalFormat, normalFormat, normalFormat, normalFormat);
}
workbook.write();
workbook.close();
response.sendRedirect(
request.getSession().getServletContext().getContextPath() + outputFileName);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
}
/**
*
*
* <DL>
* <DT>メソッド記述：
* <DD><br>
* </DL>
*
* @param MCtlListVo , String
* @return int
* @throws BizException
*/
public boolean exportEXCEL(String strFilePath) throws BizException {
try {
List<MCtlVo> lstMCtlVo = null;
/** データある場合、データ取得 */
lstMCtlVo = dao.exportEXCEL();
if (lstMCtlVo == null || lstMCtlVo.size() == 0) {
return false;
}
WritableWorkbook workbook = Workbook.createWorkbook(new File(strFilePath));
WritableSheet s1 = workbook.createSheet("M_CTL", 0);
FontName fontName = WritableFont.createFont("ＭＳ ゴシック");
WritableFont fontFormat = new WritableFont(fontName, 12);
// header format (color, padding, border)
WritableCellFormat headFormat = new WritableCellFormat(fontFormat);
headFormat.setAlignment(Alignment.CENTRE);
headFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
headFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
headFormat.setBackground(Colour.VERY_LIGHT_YELLOW);
// left format
WritableCellFormat leftCellFormat = new WritableCellFormat(fontFormat);
leftCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
leftCellFormat.setAlignment(Alignment.LEFT);
// center format
WritableCellFormat centerCellFormat = new WritableCellFormat(fontFormat);
centerCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
centerCellFormat.setAlignment(Alignment.CENTRE);
// right format
WritableCellFormat rightCellFormat = new WritableCellFormat(fontFormat);
rightCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
rightCellFormat.setAlignment(Alignment.RIGHT);
final String[] excelHeader = {
"USERID",
"KEY",
"NAME",
"DATA",
"HELP",
"入力桁数",
"入力桁数(小数桁)",
"入力属性地",
"メンテフラグ",
"コントロールフラグ",
"変更可否フラグ",
"登録ユーザー名",
"登録ＰＣ名",
"登録日付",
"登録時刻",
"最終更新ユーザー名",
"最終更新ＰＣ名",
"最終更新日付",
"最終更新時刻"
};
int columnCount = excelHeader.length;
for (int i = 0; i < columnCount; i++) {
Label lbHeader = new Label(i, 0, excelHeader[i], headFormat);
s1.addCell(lbHeader);
if (i == 2 || i == 3) s1.setColumnView(i, 60);
else if (i == 4) s1.setColumnView(i, 150);
else s1.setColumnView(i, 20);
}
for (int i = 0; i < lstMCtlVo.size(); i++) {
int column = 0;
MCtlVo mCtlVo = (MCtlVo) lstMCtlVo.get(i);
s1.addCell(new Label(column++, i + 1, mCtlVo.getUserid(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCKey(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCName(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCData(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCHelp(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCBm(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCDecbm(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCAttr(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getMtnFlg(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getCntFlg(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getUpdFlg(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getAddUserView(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getAddPc(), leftCellFormat));
s1.addCell(
new Label(
column++,
i + 1,
DateUtils.getDateWithSplitYobi(mCtlVo.getAddDate()),
centerCellFormat));
s1.addCell(
new Label(
column++,
i + 1,
DateUtils.getTimeWithSplit(mCtlVo.getAddTime()),
centerCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupUserView(), leftCellFormat));
s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupPc(), leftCellFormat));
s1.addCell(
new Label(
column++,
i + 1,
DateUtils.getDateWithSplitYobi(mCtlVo.getLastupDate()),
centerCellFormat));
s1.addCell(
new Label(
column++,
i + 1,
DateUtils.getTimeWithSplit(mCtlVo.getLastupTime()),
centerCellFormat));
}
workbook.write();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
return true;
}
// -------------------------------------------------------------------------
// 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;
}
}
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();
}
}
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;
}
/**
* 生成具有一定格式excel
*
* @param sheetName sheet名称,默认为sheet1
* @param nf 数字类型的格式 如:jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");默认无格式
* @param content 二维数组,要生成excel的数据来源
* @param 合并项 每一项的数据格式为0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
* @param os excel输出流
* @param row 需要水平居中的行,默认居左。以逗号分隔的字符串
* @param col 需要水平居中的列,默认居左。以逗号分隔的字符串
*/
public void export(
String sheetName,
NumberFormat nf,
String[][] content,
String[] mergeInfo,
OutputStream os,
String row,
String col) {
if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
Set<Integer> rows = this.getInfo(row);
Set<Integer> cols = this.getInfo(col);
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if (content[i][j] == null) {
content[i][j] = "";
}
if (isNumber(content[i][j]) && !rows.contains(i) && !cols.contains(j)) { // 处理数字
Number number = null;
if (VerifyUtil.isNullObject(nf)) { // 数字无格式
number = new Number(j, i, Double.valueOf(content[i][j]));
} else { // 如果有格式,按格式生成
jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(nf);
number = new Number(j, i, Double.valueOf(content[i][j]), wcfn);
}
sheet.addCell(number);
} else { // 处理非数字
WritableCellFormat format = new WritableCellFormat();
if (rows.contains(i) || cols.contains(j)) {
format.setAlignment(jxl.format.Alignment.CENTRE);
} else {
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j], format);
sheet.addCell(label);
}
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
os.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public void doFinish(IProgressMonitor monitor) {
monitor.beginTask("Exporting ", rowCount);
try {
switch (ComboFile) {
case 0:
WritableWorkbook myWorkbook = null;
String str = null;
try {
str = command.Command_Int("SELECT COUNT(*) FROM " + sCollection);
} catch (Exception e) {
e.printStackTrace();
}
if (Integer.parseInt(str) > 50000) {
int j = 0;
int k = 0;
int m = 0;
while (k * 50000 < Integer.parseInt(str)) {
if (Integer.parseInt(str) > 100000)
myWorkbook = Workbook.createWorkbook(new File(sFullPath + "(" + m + ")" + ".xls"));
else {
myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls"));
}
int l = 0;
for (; k * 50000 < Integer.parseInt(str); k++) {
myWorkbook.createSheet("Sheet" + k, k);
WritableSheet mySheet = myWorkbook.getSheet(l);
String sImsi = "";
for (int i = 0; i < oColumns.size(); i++) {
sImsi = oColumns.get(i);
WritableCellFormat ColumnFormat = new WritableCellFormat();
ColumnFormat.setAlignment(Alignment.CENTRE);
ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
ColumnFormat.setBackground(Colour.GRAY_25);
mySheet.setColumnView(i, (sImsi.trim().length() * 2));
jxl.write.Label oColumnLabel = null;
oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat);
mySheet.addCell(oColumnLabel);
}
sQuery = "selectattr " + sAttr + "'limit " + 50000 + "offset " + (j) * 50000 + "'";
ArrayList<String> oExcel = command.Command_Excel(sQuery);
int nTotal = oExcel.size() / oColumns.size();
int nColumn = oColumns.size();
int nCount = 0;
for (int nRow = 1; nRow <= nTotal; nRow++) {
for (int nCol = 0; nCol < nColumn; nCol++) {
jxl.write.Label numberLabels = null;
numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++));
mySheet.addCell(numberLabels);
}
monitor.worked(1);
}
j++;
l++;
if (l == 2) {
k++;
break;
}
}
m++;
myWorkbook.write();
myWorkbook.close();
}
} else if (Integer.parseInt(str) < 50000) {
myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls"));
WritableSheet mySheet = myWorkbook.createSheet(sCollection.replaceAll("/", "_"), 0);
String sImsi = "";
for (int i = 0; i < oColumns.size(); i++) {
sImsi = oColumns.get(i);
WritableCellFormat ColumnFormat = new WritableCellFormat();
ColumnFormat.setAlignment(Alignment.CENTRE);
ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
ColumnFormat.setBackground(Colour.GRAY_25);
mySheet.setColumnView(i, (sImsi.trim().length() * 2));
jxl.write.Label oColumnLabel = null;
oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat);
mySheet.addCell(oColumnLabel);
}
ArrayList<String> oExcel = command.Command_Excel(sQuery);
int nTotal = oExcel.size() / oColumns.size();
int nColumn = oColumns.size();
int nCount = 0;
for (int nRow = 1; nRow <= nTotal; nRow++) {
for (int nCol = 0; nCol < nColumn; nCol++) {
jxl.write.Label numberLabels = null;
numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++));
mySheet.addCell(numberLabels);
}
monitor.worked(1);
}
myWorkbook.write();
myWorkbook.close();
}
final int sheet = lblsheet;
final int File;
if ((lblsheet / 2) == 0) {
File = 1;
} else {
File = (lblsheet / 2);
}
if (istrue == true) {
getShell()
.getDisplay()
.syncExec(
new Runnable() {
public void run() {
MessageDialog.openInformation(
getShell(),
"AMGA_Mangaer",
"Success : Files have been saved["
+ sFullPath
+ (page2.getComboFile() == 0 ? ".xls" : ".txt")
+ "]"
+ "\r\n"
+ "File : "
+ File
+ " Sheet : "
+ sheet);
}
});
}
break;
case 1:
// Txt
String sDelimited = "";
switch (ComboString) {
case 0:
sDelimited = "\t";
break;
case 1:
sDelimited = " ";
break;
case 2:
sDelimited = ",";
break;
default:
break;
}
String sTitle = "";
if (p1btnselection == true) {
sQuery = "selectattr " + sAttr + " ' '";
for (int i = 0; i < oColumns.size(); i++) {
sTitle = sTitle + oColumns.get(i) + sDelimited;
}
} else {
sQuery = p1_1text;
String Query = sQuery;
ArrayList<String> ooColumn = new ArrayList<String>();
int nQuery = 0;
// selectattr
for (int i = 0; i < Query.length(); i++) {
if (Query.charAt(i) == '\'') {
nQuery = i;
break;
}
}
StringTokenizer st1 = new StringTokenizer(Query.substring(0, nQuery), " ");
while (st1.hasMoreElements()) {
String sTemp = (String) st1.nextElement();
if (sTemp.trim().charAt(0) == '/') {
ooColumn.add(sTemp);
}
}
for (int i = 0; i < ooColumn.size(); i++) {
sTitle = sTitle + ooColumn.get(i).substring(page1.str.length() + 1) + sDelimited;
}
nColumnCount = ooColumn.size();
}
ArrayList<String> oTxt = null;
try {
oTxt = command.Command_Txt(sQuery, sDelimited, nColumnCount);
} catch (Exception e) {
final String str1 = e.getLocalizedMessage();
getShell()
.getDisplay()
.syncExec(
new Runnable() {
public void run() {
MessageDialog.openError(getShell(), "AMGA_Mangaer", str1);
}
});
istrue = false;
}
oTxt.add(0, sTitle);
BufferedWriter out = new BufferedWriter(new FileWriter(sFullPath + ".txt"));
for (int i = 0; i < oTxt.size(); i++) {
out.write(oTxt.get(i));
out.newLine();
monitor.worked(1);
}
out.close();
if (istrue == true) {
getShell()
.getDisplay()
.syncExec(
new Runnable() {
public void run() {
MessageDialog.openInformation(
getShell(),
"AMGA_Mangaer",
"Success : Files have been saved["
+ sFullPath
+ (page2.getComboFile() == 0 ? ".xls" : ".txt")
+ "]");
}
});
}
break;
default:
break;
}
} catch (Exception e) {
final String str = e.getLocalizedMessage();
getShell()
.getDisplay()
.syncExec(
new Runnable() {
public void run() {
MessageDialog.openError(getShell(), "AMGA_Mangaer", str);
}
});
}
}
private void exportUsedMaterial2Excel(
SearchUsedMaterialBean bean,
SummaryUsedMaterialDTO result,
HttpServletRequest request,
HttpServletResponse response) {
try {
String outputFileName = "/files/temp/VatTuSuDung_" + System.currentTimeMillis() + ".xls";
String reportTemplate =
request.getSession().getServletContext().getRealPath("/files/export/UsedMaterial.xls");
String export2FileName = request.getSession().getServletContext().getRealPath(outputFileName);
Workbook templateWorkbook = Workbook.getWorkbook(new File(reportTemplate));
WritableWorkbook workbook =
Workbook.createWorkbook(new File(export2FileName), templateWorkbook);
WritableSheet sheet = workbook.getSheet(0);
WritableFont normalFont =
new WritableFont(
WritableFont.TIMES,
12,
WritableFont.NO_BOLD,
false,
UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
normalFormat.setAlignment(Alignment.CENTRE);
normalFormat.setWrap(true);
normalFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
DecimalFormat decimalFormat = new DecimalFormat("###,###.##");
DecimalFormat decimalFormat2 = new DecimalFormat("###,###.####");
int startRow = 4;
int stt = 1;
List<UsedMaterialDTO> shareUsedMaterials = result.getShareUsedMaterials();
List<UsedMaterialDTO> usedProducts = result.getUsedProducts();
List<UsedMaterialDTO> usedMeasurementMaterials = result.getUsedMeasurementMaterials();
Double totalMainProductKg = 0d;
Double totalMainProductMet2 = 0d;
Integer counter = 0;
Double totalMet2 = 0d;
Double totalKg = 0d;
Double kgMet;
Double kgTan;
Double materialUsed;
if (usedProducts != null && usedProducts.size() > 0) {
for (UsedMaterialDTO usedProduct : usedProducts) {
totalKg = usedProduct.getTotalKgUsed();
totalMet2 = usedProduct.getTotalMUsed() * Integer.valueOf(usedProduct.getWidth()) / 1000;
counter++;
totalMainProductKg += totalKg;
totalMainProductMet2 += totalMet2;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedProduct.getProductName().getName(),
"Kg",
totalKg,
null,
null);
if (usedProduct.getUsedMaterialDTOs() != null
&& usedProduct.getUsedMaterialDTOs().size() > 0) {
for (UsedMaterialDTO usedMaterial : usedProduct.getUsedMaterialDTOs()) {
counter++;
materialUsed = usedMaterial.getTotalUsed();
kgMet = totalMet2 > 0 ? materialUsed / totalMet2 : null;
kgTan = totalKg > 0 ? materialUsed * 1000 / totalKg : null;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedMaterial.getMaterial().getName(),
usedMaterial.getMaterial().getUnit().getName(),
materialUsed,
kgMet,
kgTan);
}
}
}
}
if (usedMeasurementMaterials != null && usedMeasurementMaterials.size() > 0) {
for (UsedMaterialDTO usedMaterial : usedMeasurementMaterials) {
counter++;
materialUsed = usedMaterial.getTotalUsed();
kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null;
kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedMaterial.getMaterial().getName(),
usedMaterial.getMaterial().getUnit().getName(),
materialUsed,
kgMet,
kgTan);
}
}
if (shareUsedMaterials != null && shareUsedMaterials.size() > 0) {
for (UsedMaterialDTO usedMaterial : shareUsedMaterials) {
counter++;
materialUsed = usedMaterial.getTotalUsed();
kgMet = totalMet2 > 0 ? materialUsed / totalMainProductMet2 : null;
kgTan = totalKg > 0 ? materialUsed * 1000 / totalMainProductKg : null;
addUsedMaterialRow(
sheet,
normalFormat,
decimalFormat,
decimalFormat2,
startRow++,
counter,
usedMaterial.getMaterial().getName(),
usedMaterial.getMaterial().getUnit().getName(),
materialUsed,
kgMet,
kgTan);
}
}
workbook.write();
workbook.close();
response.sendRedirect(
request.getSession().getServletContext().getContextPath() + outputFileName);
} catch (Exception ex) {
logger.error(ex.getMessage(), 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);
}
}
// -------------------------------------------------------------------------
// Action implementation
// -------------------------------------------------------------------------
public String execute() throws Exception {
statementManager.initialise();
// Initialization
raFolderName = reportService.getRAFolderName();
String colArray[] = {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ",
"AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY",
"AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN",
"BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"
};
Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList));
// OrgUnit Info
OrganisationUnit currentOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB);
System.out.println(
currentOrgUnit.getName()
+ " : "
+ selReportObj.getName()
+ " : Report Generation Start Time is : "
+ new Date());
List<OrganisationUnit> childOrgUnitTree =
new ArrayList<OrganisationUnit>(
organisationUnitService.getOrganisationUnitWithChildren(ouIDTB));
List<Integer> childOrgUnitTreeIds =
new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree));
String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds);
// Report Info
String deCodesXMLFileName = selReportObj.getXmlTemplateName();
String reportModelTB = selReportObj.getModel();
String reportFileNameTB = selReportObj.getExcelTemplateName();
String inputTemplatePath =
System.getenv("DHIS2_HOME")
+ File.separator
+ raFolderName
+ File.separator
+ "template"
+ File.separator
+ reportFileNameTB;
// 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";
Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath));
WritableWorkbook outputReportWorkbook =
Workbook.createWorkbook(new File(outputReportPath), templateWorkbook);
WritableCellFormat wCellformat = new WritableCellFormat();
wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
wCellformat.setAlignment(Alignment.CENTRE);
wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
wCellformat.setWrap(true);
// Period Info
selectedPeriod = periodService.getPeriod(availablePeriods);
selectedEndPeriod = periodService.getPeriod(availablePeriodsto);
sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate()));
eDate = format.parseDate(String.valueOf(selectedEndPeriod.getEndDate()));
PeriodType periodType = periodService.getPeriodTypeByName(periodTypeId);
List<Period> periodList =
new ArrayList<Period>(periodService.getPeriodsBetweenDates(periodType, sDate, eDate));
// List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriods( sDate,
// eDate ) );
Collections.sort(periodList, new PeriodStartDateComparator());
if (periodTypeId.equalsIgnoreCase("monthly")) {
simpleDateFormat = new SimpleDateFormat("MMM-yyyy");
} else if (periodTypeId.equalsIgnoreCase("yearly")) {
simpleDateFormat = new SimpleDateFormat("yyyy");
} else {
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
}
// To get Aggregation Data
List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName);
// String dataElmentIdsByComma = reportService.getDataelementIds( reportDesignList );
String dataElmentIdsByComma =
reportService.getDataelementIdsByStype(reportDesignList, Report_inDesign.ST_DATAELEMENT);
String nonNumberDataElementIdsByComma =
reportService.getDataelementIdsByStype(
reportDesignList, Report_inDesign.ST_NON_NUMBER_DATAELEMENT);
// Collection<Integer> periodIds1 = new ArrayList<Integer>( getIdentifiers(Period.class,
// periodList ) );
String periodsByComma = "";
// getCommaDelimitedString( periodIds1 );
int colCount = 0;
for (Period period : periodList) {
if (periodTypeId.equalsIgnoreCase("daily")) {
periodsByComma = "" + period.getId();
} else {
Collection<Integer> periodIds =
new ArrayList<Integer>(
getIdentifiers(
Period.class,
periodService.getIntersectingPeriods(
period.getStartDate(), period.getEndDate())));
periodsByComma = getCommaDelimitedString(periodIds);
}
Map<String, String> aggDeMap = new HashMap<String, String>();
if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
aggDeMap.putAll(
reportService.getResultDataValueFromAggregateTable(
currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma));
} else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
aggDeMap.putAll(
reportService.getAggDataFromDataValueTable(
childOrgUnitsByComma, dataElmentIdsByComma, periodsByComma));
aggDeMap.putAll(
reportService.getAggNonNumberDataFromDataValueTable(
childOrgUnitsByComma, nonNumberDataElementIdsByComma, periodsByComma));
System.out.println(
childOrgUnitsByComma + " \n " + dataElmentIdsByComma + " \n " + periodsByComma);
} else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
aggDeMap.putAll(
reportService.getAggDataFromDataValueTable(
"" + currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma));
aggDeMap.putAll(
reportService.getAggNonNumberDataFromDataValueTable(
"" + currentOrgUnit.getId(), nonNumberDataElementIdsByComma, periodsByComma));
}
System.out.println("aggDeMap size : " + aggDeMap.size());
Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator();
while (reportDesignIterator.hasNext()) {
Report_inDesign reportDesign = reportDesignIterator.next();
String deCodeString = reportDesign.getExpression();
String sType = reportDesign.getStype();
String tempStr = "";
tempRowNo = reportDesign.getRowno();
tempColNo = reportDesign.getColno();
sheetNo = reportDesign.getSheetno();
if (deCodeString.equalsIgnoreCase("FACILITY")) {
tempStr = currentOrgUnit.getName();
} else if (deCodeString.equalsIgnoreCase("PERIOD-RANGE")) {
tempStr =
simpleDateFormat.format(selectedPeriod.getStartDate())
+ " To "
+ simpleDateFormat.format(selectedEndPeriod.getEndDate());
} else if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) {
tempStr = simpleDateFormat.format(period.getStartDate());
} else if (deCodeString.equalsIgnoreCase("NA")) {
tempStr = " ";
} else {
if (sType.equalsIgnoreCase("dataelement")) {
if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) {
// tempStr = reportService.getIndividualResultDataValue( deCodeString,
// period.getStartDate(), period.getEndDate(), currentOrgUnit, reportModelTB );
tempStr = getAggVal(deCodeString, aggDeMap);
} else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) {
// tempStr = reportService.getResultDataValue( deCodeString, period.getStartDate(),
// period.getEndDate(), currentOrgUnit, reportModelTB );
tempStr = getAggVal(deCodeString, aggDeMap);
} else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) {
tempStr = getAggVal(deCodeString, aggDeMap);
}
} else if (sType.equalsIgnoreCase(Report_inDesign.ST_DATAELEMENT_NO_REPEAT)) {
deCodeString = deCodeString.replaceAll(":", "\\.");
deCodeString = deCodeString.replaceAll("[", "");
deCodeString = deCodeString.replaceAll("]", "");
System.out.println("deCodeString : " + deCodeString);
tempStr = aggDeMap.get(deCodeString);
}
}
if (tempStr == null || tempStr.equals(" ")) {
tempColNo += colCount;
WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo);
sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat));
} else {
if (reportModelTB.equalsIgnoreCase("PROGRESSIVE-PERIOD")) {
if (deCodeString.equalsIgnoreCase("FACILITY")
|| deCodeString.equalsIgnoreCase("PERIOD-RANGE")) {
} else {
tempColNo += colCount;
}
WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo);
try {
try {
sheet0.addCell(
new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat));
} catch (Exception e) {
sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat));
}
} catch (Exception e) {
System.out.println("Cannot write to Excel");
}
}
}
} // inner while loop end
colCount++;
} // outer while loop end
// ---------------------------------------------------------------------
// Writing Total Values
// ---------------------------------------------------------------------
Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator();
while (reportDesignIterator.hasNext()) {
Report_inDesign reportDesign = reportDesignIterator.next();
String deCodeString = reportDesign.getExpression();
if (deCodeString.equalsIgnoreCase("FACILITY")
|| deCodeString.equalsIgnoreCase("PERIOD-RANGE")) {
continue;
}
tempRowNo = reportDesign.getRowno();
tempColNo = reportDesign.getColno();
sheetNo = reportDesign.getSheetno();
String colStart = "" + colArray[tempColNo];
String colEnd = "" + colArray[tempColNo + colCount - 1];
String tempFormula =
"SUM(" + colStart + (tempRowNo + 1) + ":" + colEnd + (tempRowNo + 1) + ")";
WritableSheet totalSheet = outputReportWorkbook.getSheet(sheetNo);
WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat totalCellformat = new WritableCellFormat(arialBold);
totalCellformat.setBorder(Border.ALL, BorderLineStyle.THIN);
totalCellformat.setAlignment(Alignment.CENTRE);
totalCellformat.setVerticalAlignment(VerticalAlignment.CENTRE);
totalCellformat.setWrap(true);
if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) {
totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, "Total", totalCellformat));
} else if (deCodeString.equalsIgnoreCase("NA")) {
totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, " ", totalCellformat));
} else {
totalSheet.addCell(
new Formula(tempColNo + colCount, tempRowNo, tempFormula, totalCellformat));
}
}
outputReportWorkbook.write();
outputReportWorkbook.close();
fileName = reportFileNameTB.replace(".xls", "");
fileName += "_" + currentOrgUnit.getShortName();
fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls";
File outputReportFile = new File(outputReportPath);
inputStream = new BufferedInputStream(new FileInputStream(outputReportFile));
System.out.println(
currentOrgUnit.getName()
+ " : "
+ selReportObj.getName()
+ " : Report Generation End Time is : "
+ new Date());
outputReportFile.deleteOnExit();
statementManager.destroy();
return SUCCESS;
}