/** * 采用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); } } } }
private void createLabel(WritableSheet sheet) throws WriteException { WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); // Define the cell format times = new WritableCellFormat(times10pt); // Lets automatically wrap the cells times.setWrap(true); // create create a bold font with unterlines WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE); timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline); // Lets automatically wrap the cells timesBoldUnderline.setWrap(true); CellView cv = new CellView(); cv.setFormat(times); cv.setFormat(timesBoldUnderline); // Write a few headers addCaption(sheet, 0, 0, "Adı"); addCaption(sheet, 1, 0, "Miktar"); addCaption(sheet, 2, 0, "Birim"); addCaption(sheet, 3, 0, "Fiyat"); addCaption(sheet, 4, 0, "Etiketler"); }
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; }
/** * 单元格左对齐 * * @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; }
/** * 添加单元格&内容. * * @param ws * @param cel * @param row * @param valObject */ private void addCell(WritableSheet ws, int cel, int row, Object valObj) throws WriteException { if (valObj == null) { Label cr = new Label(cel, row, ""); ws.addCell(cr); return; } @SuppressWarnings("rawtypes") Class clazz2 = valObj.getClass(); if (clazz2 == String.class) { Label cr = new Label(cel, row, (String) valObj); ws.addCell(cr); return; } else if (clazz2 == int.class || clazz2 == Integer.class) { Number cr = new Number(cel, row, Double.parseDouble(((Integer) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == long.class || clazz2 == Long.class) { Number cr = new Number(cel, row, Double.parseDouble(((Long) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == float.class || clazz2 == Float.class) { Number cr = new Number(cel, row, Double.parseDouble(((Float) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == double.class || clazz2 == Double.class) { Number cr = new Number(cel, row, Double.parseDouble(((Double) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == Date.class) { DateTime cr = new DateTime(cel, row, (Date) valObj); ws.addCell(cr); return; } else if (clazz2 == boolean.class || clazz2 == Boolean.class) { jxl.write.Boolean cr = new jxl.write.Boolean(cel, row, (Boolean) valObj); ws.addCell(cr); return; } else if (clazz2 == BigDecimal.class) { NumberFormat nf = new NumberFormat("0.00"); WritableCellFormat wcf = new WritableCellFormat(nf); wcf.setFont(new WritableFont(WritableFont.createFont("宋体"), PS)); Number cr = new Number(cel, row, ((BigDecimal) valObj).doubleValue(), wcf); ws.addCell(cr); return; } else if (clazz2 == Short.class) { Number cr = new Number(cel, row, Double.parseDouble(((Short) valObj).toString())); ws.addCell(cr); return; } }
/** * 锁定单元格 * * @param colNum 列号 * @param rowNum 行号 * @param bln true:锁定 ,false :取消 */ public void setLock(int colNum, int rowNum, Boolean bln) { WritableCellFormat format_unlock = new WritableCellFormat(); try { // 锁定单元格 if (bln == true) { format_unlock.setLocked(true); // 解锁单元格 } else { format_unlock.setLocked(false); } wrCurrentSheet.getWritableCell(colNum, rowNum).setCellFormat(format_unlock); } catch (WriteException e) { logger.error(e.getMessage()); throw new BaseException(e); } }
public void setNumber(int x, int y, double n, Colour background) { WritableCellFormat wcf = new WritableCellFormat(); try { // Colour colour = Colour.getInternalColour( background.getRGB() ); // wcf.setBackground( colour ); wcf.setBackground(background); } catch (WriteException e1) { e1.printStackTrace(); } Number number = new Number(x, y, n, wcf); try { excelPage.addCell(number); } catch (Exception e) { System.err.println( "Error while writing Xls data (XlsManager.java) File Already open by an other app ?"); } }
public void setLabel(int x, int y, String texte, Colour background) // public void setLabel(int x, int y, String texte, Color background) { WritableCellFormat wcf = new WritableCellFormat(); try { // Colour colour = Colour.getInternalColour( background.getRGB() ); wcf.setBackground(background); } catch (WriteException e1) { e1.printStackTrace(); } Label label = new Label(x, y, texte, wcf); try { excelPage.addCell(label); } catch (Exception e) { System.err.println( "Error while writing Xls data (XlsManager.java) File Already open by an other app ?"); } }
private void createLabel(WritableSheet sheet, List<String> labels) throws WriteException { WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); times = new WritableCellFormat(times10pt); times.setWrap(true); WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE); timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline); timesBoldUnderline.setWrap(true); CellView cv = new CellView(); cv.setFormat(times); cv.setFormat(timesBoldUnderline); cv.setAutosize(true); // add one label for (int i = 0; i < labels.size(); i++) addCaption(sheet, i, 0, labels.get(i)); }
/** * 生成固定格式的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(); } } }
public static void addColumn( WritableSheet sheet, int startCol, int startRow, CellValue[] cellValues) throws WriteException { WritableCellFormat cellFormat = new WritableCellFormat(); cellFormat.setWrap(true); cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); NumberFormat towdps = new NumberFormat("#,##0"); WritableCellFormat towdpsFormat = new WritableCellFormat(towdps); towdpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN); NumberFormat towdps2 = new NumberFormat("#,##0.00"); WritableCellFormat towdpsFormat2 = new WritableCellFormat(towdps2); towdpsFormat2.setBorder(Border.ALL, BorderLineStyle.THIN); DateFormat customDateFormat = new DateFormat("dd MMM yyyy"); WritableCellFormat dateFormat = new WritableCellFormat(customDateFormat); dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN); for (int i = startRow; i < cellValues.length; i++) { if (cellValues[i] != null && cellValues[i].getValue() != null) { if (cellValues[i].getType().equals(CellDataType.STRING)) { Label label = new Label(startCol, i, String.valueOf(cellValues[i].getValue()), cellFormat); sheet.addCell(label); } else if (cellValues[i].getType().equals(CellDataType.INT)) { Number number = new Number(startCol, i, (Integer) cellValues[i].getValue(), towdpsFormat); sheet.addCell(number); } else if (cellValues[i].getType().equals(CellDataType.DOUBLE)) { Number number = new Number(startCol, i, (Double) cellValues[i].getValue(), towdpsFormat); sheet.addCell(number); } else if (cellValues[i].getType().equals(CellDataType.FLOAT)) { Number number = new Number(startCol, i, (Float) cellValues[i].getValue(), towdpsFormat); sheet.addCell(number); } else if (cellValues[i].getType().equals(CellDataType.DATE)) { Date now = new Date(((Timestamp) cellValues[i].getValue()).getTime()); DateTime dateCell = new DateTime(startCol, i, now, dateFormat); sheet.addCell(dateCell); } } else { Label label = new Label(startCol, i, "", cellFormat); sheet.addCell(label); } } }
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); }
/** * 生成固定格式的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 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; }
public static void export( String field, String value, String start, String end, TaodianApi api, OutputStream os) { try { WritableWorkbook wbook = Workbook.createWorkbook(os); WritableSheet wsheet = wbook.createSheet("访问详情", 0); WritableFont wfont = new WritableFont( WritableFont.ARIAL, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat wcfFC = new WritableCellFormat(wfont); wcfFC.setBackground(Colour.AQUA); // wsheet.addCell(new Label(1, 0, tmptitle, wcfFC)); wfont = new jxl.write.WritableFont( WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); wcfFC = new WritableCellFormat(wfont); int row = 0; wsheet.addCell(new Label(0, row, "短网址")); wsheet.setColumnView(0, 10); wsheet.addCell(new Label(1, row, "商家")); wsheet.setColumnView(1, 12); wsheet.addCell(new Label(2, row, "商品")); wsheet.setColumnView(2, 12); wsheet.addCell(new Label(3, row, "推广者")); wsheet.setColumnView(3, 6); wsheet.addCell(new Label(4, row, "点击单价")); wsheet.setColumnView(4, 7); wsheet.addCell(new Label(5, row, "访问IP")); wsheet.setColumnView(5, 14); wsheet.addCell(new Label(6, row, "访问设备")); wsheet.setColumnView(6, 8); wsheet.addCell(new Label(7, row, "浏览器")); wsheet.setColumnView(7, 8); wsheet.addCell(new Label(8, row, "访问时间")); wsheet.setColumnView(8, 20); wsheet.addCell(new Label(9, row, "访问者ID")); wsheet.setColumnView(9, 12); wsheet.addCell(new Label(10, row, "访问来源")); wsheet.setColumnView(10, 80); wsheet.addCell(new Label(11, row, "Agent")); wsheet.setColumnView(11, 80); DataIterator data = new DataIterator(field, value, start, end, api); row++; while (data.hasNext()) { writeRow(wsheet, data.next(), row++); } wbook.write(); // 写入文件 wbook.close(); } catch (Exception e) { log.error(e, 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; }
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); } }
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 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 jButton3ActionPerformed( java.awt.event.ActionEvent evt) { // GEN-FIRST:event_jButton3ActionPerformed try { JFileChooser caja = new JFileChooser(); caja.showSaveDialog(null); File arch = caja.getSelectedFile(); if (arch == null) { return; } DataOutputStream out = new DataOutputStream(new FileOutputStream(arch + ".xls")); WritableWorkbook w = Workbook.createWorkbook(out); WritableSheet s = w.createSheet("reporte", 0); WritableFont headersfont = new WritableFont( WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.WHITE); WritableCellFormat headersformat = new WritableCellFormat(headersfont); headersformat.setBackground(Colour.ORANGE); s.addCell(new Label(0, 0, String.valueOf("F_fproceso"), headersformat)); s.addCell(new Label(1, 0, String.valueOf("C_codcent"), headersformat)); s.addCell(new Label(2, 0, String.valueOf("Contrato"), headersformat)); s.addCell(new Label(3, 0, String.valueOf("Tipo docu."), headersformat)); s.addCell(new Label(4, 0, String.valueOf("nro_docu"), headersformat)); s.addCell(new Label(5, 0, String.valueOf("Nombre"), headersformat)); s.addCell(new Label(6, 0, String.valueOf("C_nomsubpro_car"), headersformat)); s.addCell(new Label(7, 0, String.valueOf("C_nomsubpro"), headersformat)); s.addCell(new Label(8, 0, String.valueOf("C_dessubpro"), headersformat)); s.addCell(new Label(9, 0, String.valueOf("C_divisa_car"), headersformat)); s.addCell(new Label(10, 0, String.valueOf("C_divisa"), headersformat)); s.addCell(new Label(11, 0, String.valueOf("N_saldohoy"), headersformat)); s.addCell(new Label(12, 0, String.valueOf("N_diasvenc"), headersformat)); s.addCell(new Label(13, 0, String.valueOf("C_flgdir"), headersformat)); s.addCell(new Label(14, 0, String.valueOf("C_dirimp"), headersformat)); s.addCell(new Label(15, 0, String.valueOf("C_disimp"), headersformat)); s.addCell(new Label(16, 0, String.valueOf("F_fproceso_ges"), headersformat)); s.addCell(new Label(17, 0, String.valueOf("F_fecimp"), headersformat)); s.addCell(new Label(18, 0, String.valueOf("C_desrspta"), headersformat)); s.addCell(new Label(19, 0, String.valueOf("C_desrspta_sub"), headersformat)); s.addCell(new Label(20, 0, String.valueOf("F_fecasig_ult"), headersformat)); s.addCell(new Label(21, 0, String.valueOf("Id_codtcon"), headersformat)); s.addCell(new Label(22, 0, String.valueOf("C_destcon"), headersformat)); s.addCell(new Label(23, 0, String.valueOf("Id_codtres"), headersformat)); s.addCell(new Label(24, 0, String.valueOf("C_destres"), headersformat)); for (int i = 0; i < jTable2.getRowCount(); i++) { for (int j = 0; j < jTable2.getColumnCount(); j++) { Object objeto = jTable2.getValueAt(i, j); if (objeto != null) { if (j == 12) { s.addCell(new jxl.write.Number(j, i + 1, Integer.parseInt(objeto + ""))); } else { s.addCell(new Label(j, i + 1, String.valueOf(objeto))); } } else { s.addCell(new Label(j, i + 1, "")); } } } w.write(); w.close(); out.close(); JOptionPane.showMessageDialog(null, "Reporte Terminado"); } catch (Exception e) { JOptionPane.showMessageDialog(null, e.getMessage()); } } // GEN-LAST:event_jButton3ActionPerformed
/** * * * <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("MS ゴシック"); 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", "入力桁数", "入力桁数(小数桁)", "入力属性地", "メンテフラグ", "コントロールフラグ", "変更可否フラグ", "登録ユーザー名", "登録PC名", "登録日付", "登録時刻", "最終更新ユーザー名", "最終更新PC名", "最終更新日付", "最終更新時刻" }; 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; } }
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(); }
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); } }
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; }
/** * 获得默认的xls单元风格 * * @return * @throws WriteException */ private static WritableCellFormat GetCellFormat() throws Exception { WritableCellFormat cformat = new WritableCellFormat(); cformat.setBackground(TITLE_DEFAULT_COLOR); return cformat; }
public void ExportExcel(HttpServletResponse response, String filename, List<MemberLevel> items) { boolean b = false; OutputStream os = null; WritableWorkbook wbook = null; try { os = response.getOutputStream(); // 取得输出流 response.reset(); // 清空输出流 response.setHeader( "Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode(filename, "UTF-8") + ".xls"); // 设定输出文件头 response.setContentType("application/msexcel"); // 定义输出类型 wbook = Workbook.createWorkbook(os); // 建立excel文件 String tmptitle = "会员级别"; // 标题 WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称 // 设置excel标题 WritableFont wfont = new WritableFont( WritableFont.ARIAL, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat wcfFC = new WritableCellFormat(wfont); wcfFC.setBackground(Colour.AQUA); wsheet.addCell(new Label(1, 0, tmptitle, wcfFC)); wfont = new jxl.write.WritableFont( WritableFont.ARIAL, 15, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); wcfFC = new WritableCellFormat(wfont); int r = 0; wsheet.setColumnView(r, 10); // 设置列的宽度 wsheet.setColumnView(r + 1, 13); // 设置列的宽度 wsheet.setColumnView(r + 2, 12); // 设置列的宽度 wsheet.setColumnView(r + 3, 12); // 设置列的宽度 wsheet.setColumnView(r + 4, 15); // 设置列的宽度 wsheet.setColumnView(r + 5, 15); // 设置列的宽度 wsheet.setColumnView(r + 6, 12); // 设置列的宽度 wsheet.setColumnView(r + 7, 12); // 设置列的宽度 wsheet.setColumnView(r + 8, 12); // 设置列的宽度 wsheet.setColumnView(r + 9, 12); // 设置列的宽度 wsheet.setColumnView(r + 10, 12); // 设置列的宽度 wsheet.setColumnView(r + 11, 12); // 设置列的宽度 wsheet.setColumnView(r + 12, 12); // 设置列的宽度 int f = 1; // from this row write data r = 0; // 开始生成主体内容 会员号 姓名 联系电话 会员等级 降级期? 总积分 产品积分 变更时间 前一 后一 后二 后三 后四 后五 wsheet.addCell(new Label(r, f, "姓名")); wsheet.addCell(new Label(r + 1, f, "会员号")); // wsheet.addCell(new Label(r+2, f, "性别")); wsheet.addCell(new Label(r + 2, f, "联系电话")); wsheet.addCell(new Label(r + 3, f, "会员等级")); wsheet.addCell(new Label(r + 4, f, "总积分")); wsheet.addCell(new Label(r + 5, f, "产品积分")); wsheet.addCell(new Label(r + 6, f, "变更时间")); wsheet.addCell(new Label(r + 7, f, "前一")); wsheet.addCell(new Label(r + 8, f, "后一")); wsheet.addCell(new Label(r + 9, f, "后二")); wsheet.addCell(new Label(r + 10, f, "后三")); wsheet.addCell(new Label(r + 11, f, "后四")); wsheet.addCell(new Label(r + 12, f, "后五")); f++; r = 0; int row = 0; for (MemberLevel item : items) { wsheet.addCell(new Label(r, row + f, item.getRealName())); // 数据库的城市名字段 wsheet.addCell(new Label(r + 1, row + f, item.getMemberId())); // 数据库的城市名字段 wsheet.addCell(new Label(r + 2, row + f, item.getPhoneNo())); // 数据库的城市名字段 wsheet.addCell(new Label(r + 3, row + f, item.getLevelName())); wsheet.addCell(new Label(r + 4, row + f, item.getTotalScore() + "")); wsheet.addCell(new Label(r + 5, row + f, item.getSnScore() + "")); wsheet.addCell(new Label(r + 6, row + f, item.getUpdateTime())); wsheet.addCell(new Label(r + 7, row + f, item.getScore1() + "")); wsheet.addCell(new Label(r + 8, row + f, item.getScore2() + "")); wsheet.addCell(new Label(r + 9, row + f, item.getScore3() + "")); wsheet.addCell(new Label(r + 10, row + f, item.getScore4() + "")); wsheet.addCell(new Label(r + 11, row + f, item.getScore5() + "")); wsheet.addCell(new Label(r + 12, row + f, item.getScore6() + "")); row++; } // 主体内容生成结束 wbook.write(); // 写入文件 wbook.close(); os.close(); // 关闭流 b = true; } catch (java.net.SocketException exc) { exc.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (wbook != null) wbook.close(); if (os != null) os.close(); // 关闭流 } catch (Exception ex) { } } }