List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook()
From source file:com.hp.action.TakeOrderAction.java
public String exportOrders() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize/*from w ww . j av a 2 s .c o m*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } staffList = staffDAO.getListUser(null); String fileInput = ServletActionContext.getServletContext().getRealPath("/database/"); System.out.println("Result: " + staff + " from date: " + fromDate + " , to date: " + toDate); takeOrdersList = takeOrderDAO.getTakeOrdersList(staff, fromDate, toDate); if (takeOrdersList.isEmpty()) return INPUT; Map<String, List<TakeOrderDetail>> data = new HashMap<String, List<TakeOrderDetail>>(); for (int i = 0; i < takeOrdersList.size(); i++) { data.put(i + "", takeOrderDetailDAO.getDetailTakeOrdersList(takeOrdersList.get(i).getId())); } int line = 0; Set<String> keyset = data.keySet(); for (String key : keyset) { line += data.get(key).size(); } line += keyset.size(); //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Order"); //TakeOrder title for (int i = 0; i < 1; i++) { Row row = sheet.createRow(i); int cellnum = 0; for (Object obj : takeOrdersList.get(i).toTitleArray()) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < takeOrdersList.size(); i++) { Row row = sheet.createRow(i + 1); int cellnum = 0; for (Object obj : takeOrdersList.get(i).toArray()) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrderDetail title for (String key : keyset) { for (int i = 0; i < 1; i++) { Row row = sheet.createRow(i + takeOrdersList.size() + 3); int cellnum = 0; for (Object obj : data.get(key).get(i).toTitleArray()) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } } } //Write TakeOrderDetail for (String key : keyset) { for (int i = 0; i < data.get(key).size(); i++) { Row row = sheet.createRow(i + takeOrdersList.size() + 4); int cellnum = 0; for (Object obj : data.get(key).get(i).toArray()) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } } } //Write to xls try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\test.xls")); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\test.xls")); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.TimeKeeperAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize//from w ww .j a v a 2 s. c om if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA timeKeeperList = (List<TimeKeeper>) session.getAttribute("timeKeeperList"); if (timeKeeperList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Chm cng"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co chm cng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < timeKeeperList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(timeKeeperList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoChamCong" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.idc.common.upload.JSONToExcelServlet.java
License:Open Source License
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("application/xml;charset=gbk"); response.setCharacterEncoding("gbk"); request.setCharacterEncoding("gbk"); String JSONStr = request.getParameter("JSONStr"); JSONObject jsonObj = null;/*w w w .j a v a2 s .c o m*/ try { jsonObj = new JSONObject(JSONStr); String fileName = null; JSONArray headja = null; JSONArray valja = null; JSONArray sheetja = null; fileName = jsonObj.getString("fileName"); if ((fileName == null) || (fileName.equals(""))) fileName = "data.xls"; //fileName = URLEncoder.encode(fileName, "utf-8"); fileName = StringUtil.getEncodeStr( fileName + DateTimeUtil.formatDate(new Date(), "yyyy-MM-dd") + ".xls", "GB2312", "ISO8859-1"); response.reset(); response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName); Workbook wb = new HSSFWorkbook(); if (jsonObj.has("sheet")) sheetja = jsonObj.getJSONArray("sheet"); else { sheetja = new JSONArray("[{name:'sheet',_sys_create:true}]"); } for (int sheetIndex = 0; sheetIndex < sheetja.length(); sheetIndex++) { JSONObject sheetjo = sheetja.getJSONObject(sheetIndex); String sheetName = "sheet" + sheetIndex; if (sheetjo.has("sheetName")) sheetName = sheetjo.getString("sheetName"); Sheet sheet = wb.createSheet(sheetName); if ((sheetjo.has("_sys_create")) && (sheetjo.getBoolean("_sys_create"))) { headja = jsonObj.getJSONArray("head"); valja = jsonObj.getJSONArray("value"); } else { headja = sheetjo.getJSONArray("head"); valja = sheetjo.getJSONArray("value"); } try { JSONArray names = new JSONArray(); Row row = sheet.createRow(0); if (headja != null) { for (int i = 0; i < headja.length(); i++) { JSONObject jo = headja.getJSONObject(i); if (jo != null) names.put(jo.getString("id")); writeHead(jo, row, i); } } if (valja != null) for (int i = 0; i < valja.length(); i++) { JSONObject jo = valja.getJSONObject(i); writeValue(names, jo, sheet, i + 1); } } catch (Exception e) { e.printStackTrace(); } } OutputStream out = response.getOutputStream(); out.flush(); wb.write(out); out.close(); } catch (JSONException e2) { e2.printStackTrace(); } }
From source file:com.hp.idc.resm.util.ExcelUtil.java
License:Open Source License
/** * Excel/*from w w w . j av a 2 s . c o m*/ * * @param id * ID * @return Excel */ public String getModelExcel(String id) { List<ModelAttribute> list = ServiceManager.getModelService().getModelAttributesByModelId(id); // String[] IGNORATTR = new String[] { "id", "create_time", "contract_start", "searchcode", "last_update_time", "contract_end", "task_link", "order_id", "customer_id", "status", "last_update_by" }; List<String> l = new ArrayList<String>(Arrays.asList(IGNORATTR)); Workbook wb = new HSSFWorkbook(); // Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(id); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); int i = 0; Cell cell = null; HSSFRichTextString textString; for (ModelAttribute ma : list) { if (l.contains(ma.getAttrId())) continue; cell = row.createCell(i); textString = new HSSFRichTextString(ma.getName() + "/" + ma.getAttrId()); cell.setCellValue(textString); if (!ma.isNullable()) cell.setCellStyle(style); sheet.autoSizeColumn(i); i++; } for (int k = 0; k < list.size(); k++) { sheet.autoSizeColumn(k); } // Write the output to a file FileOutputStream fileOut; String file; try { file = System.getProperty("user.dir") + "/../temp/" + id + new Random().nextLong() + ".xls"; fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return ""; } catch (IOException e) { e.printStackTrace(); return ""; } return file; }
From source file:com.hp.idc.resm.util.ExcelUtil.java
License:Open Source License
/** * , Excel/* w ww . j a v a2 s .c o m*/ * * @param modelId * Id * @return Excel */ public String getResouceDataToExcel(String modelId) { Model m = ServiceManager.getModelService().getModelById(modelId); List<ResourceObject> l = ServiceManager.getResourceService().getResourcesByModelId(modelId, 1); List<ModelAttribute> mas = m.getAttributes(); Workbook wb = new HSSFWorkbook(); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); font.setFontName(""); style.setFont(font); Sheet sheet = wb.createSheet(modelId); Row row = sheet.createRow(0); int i = 0; HSSFRichTextString textString; for (ModelAttribute ma : mas) { Cell cell = row.createCell(i); textString = new HSSFRichTextString(ma.getDefine().getName()); cell.setCellStyle(style); cell.setCellValue(textString); i++; } i = 1; for (ResourceObject ro : l) { row = sheet.createRow(i); int j = 0; for (ModelAttribute ma : mas) { textString = new HSSFRichTextString(ro.getAttributeValue(ma.getAttrId())); row.createCell(j).setCellValue(textString); j++; } i++; } for (int k = 0; k < mas.size(); k++) { sheet.autoSizeColumn(k); } // Write the output to a file FileOutputStream fileOut; String file; try { file = System.getProperty("user.dir") + "/../temp/" + modelId + new Random().nextLong() + "_data.xls"; fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return ""; } catch (IOException e) { e.printStackTrace(); return ""; } return file; }
From source file:com.hris.payroll.thirteenthmonth.ExportDataGridToExcel.java
public void workSheet() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); int rownum = 1; for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { System.out.println("itemId: " + itemId); }//ww w.ja v a 2s. com for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { Row row = sheet.createRow(rownum); int cellcount = 0; if ((rownum - 1) == 0) { Row rowHeader = sheet.createRow(rownum - 1); for (Object propertyId : getDataGrid().getContainerDataSource().getContainerPropertyIds()) { Cell cell = rowHeader.createCell(cellcount); if (propertyId.toString().contains("salary")) { if (!propertyId.toString().equals("salary grand total")) { cell.setCellValue(propertyId.toString().replace("salary", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("tax")) { if (!propertyId.toString().equals("tax grand total")) { cell.setCellValue(propertyId.toString().replace("tax", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("sss")) { if (!propertyId.toString().equals("sss grand total")) { cell.setCellValue(propertyId.toString().replace("sss", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("phic")) { if (!propertyId.toString().equals("phic grand total")) { cell.setCellValue(propertyId.toString().replace("phic", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("hdmf")) { if (!propertyId.toString().equals("hdmf grand total")) { cell.setCellValue(propertyId.toString().replace("hdmf", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else { cell.setCellValue(propertyId.toString().toUpperCase()); } sheet.autoSizeColumn(cellcount); cellcount++; } } Item item = getDataGrid().getContainerDataSource().getItem(itemId); int cellnum = 0; for (Object propertyId : item.getItemPropertyIds()) { Cell cell = row.createCell(cellnum); if (propertyId.equals("employee")) { cell.setCellValue(item.getItemProperty(propertyId).getValue().toString().toUpperCase()); } else { cell.setCellValue((item.getItemProperty(propertyId).getValue() == null) ? " " : item.getItemProperty(propertyId).getValue().toString()); } sheet.autoSizeColumn(cellnum); cellnum++; } rownum++; } FileOutputStream fos; try { Date date = new Date(); path = "C:/payroll-files/format-" + date.getTime() + ".xls"; file = new File(path); fos = new FileOutputStream(path); workbook.write(fos); fos.flush(); fos.close(); } catch (FileNotFoundException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } StreamResource.StreamSource source = () -> { try { File f = new File(path); FileInputStream fis = new FileInputStream(f); return fis; } catch (Exception e) { e.getMessage(); return null; } }; StreamResource resource = new StreamResource(source, "ThirteenthMonth.xls"); resource.setMIMEType("application/vnd.ms-office"); }
From source file:com.huateng.struts.query.action.T50202Action.java
License:Open Source License
public String download() { String brhBelow = InformationUtil.getBrhGroupString(brhId); System.out.println(brhBelow); String thisMonStart = date + "01"; String thisMonEnd = date + "31"; String lastYear = String.valueOf(Integer.parseInt(date.substring(0, 4)) - 1) + "1231"; String lastMon = thisMonStart;//start? String thisYearStart = date.substring(0, 4) + "0101"; String thisYearEnd = date.substring(0, 4) + "1231"; try {/* ww w . j a v a 2 s . c o m*/ //? String[][] data = new String[40][9]; //?data for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { if (j < 4) { data[i][j] = ""; } else { data[i][j] = "0"; } } } Iterator it = null; //??? //?data data[0][0] = "1"; data[0][1] = "?"; data[0][3] = ""; data[1][1] = ""; data[1][2] = "?"; data[1][3] = ""; data[2][2] = "?"; data[2][3] = ""; String sql = "select CONN_TYPE, " + "sum(case when apply_date <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when apply_date < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; List list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 1; } else { index = 2; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[0][4] = String.valueOf(Integer.valueOf(data[1][4]) + Integer.valueOf(data[2][4])); data[0][5] = String.valueOf(Integer.valueOf(data[1][5]) + Integer.valueOf(data[2][5])); data[0][6] = String.valueOf(Integer.valueOf(data[1][6]) + Integer.valueOf(data[2][6])); data[0][7] = String.valueOf(Integer.valueOf(data[1][7]) + Integer.valueOf(data[2][7])); data[0][8] = String.valueOf(Integer.valueOf(data[1][8]) + Integer.valueOf(data[2][8])); } //??? //?data data[3][0] = "2"; data[3][1] = "?POS"; data[3][3] = "?"; data[4][1] = ""; data[4][2] = "?"; data[4][3] = "?"; data[5][2] = "?"; data[5][3] = "?"; sql = "select CONN_TYPE ," + "sum(case when t.REC_CRT_TS <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when t.REC_CRT_TS < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_TERM_INF t,TBL_MCHT_BASE_INF m where t.MCHT_CD = m.MCHT_NO " + "and m.ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 4; } else { index = 5; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[3][4] = String.valueOf(Integer.valueOf(data[4][4]) + Integer.valueOf(data[5][4])); data[3][5] = String.valueOf(Integer.valueOf(data[4][5]) + Integer.valueOf(data[5][5])); data[3][6] = String.valueOf(Integer.valueOf(data[4][6]) + Integer.valueOf(data[5][6])); data[3][7] = String.valueOf(Integer.valueOf(data[4][7]) + Integer.valueOf(data[5][7])); data[3][8] = String.valueOf(Integer.valueOf(data[4][8]) + Integer.valueOf(data[5][8])); } //??? //?data data[6][0] = "3"; data[6][1] = "?"; sql = "SELECT DESCR,nvl(mchnt1.c,0),nvl(mchnt2.c,0),nvl(mchnt3.c,0),nvl(mchnt4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date <= '" + lastYear + "' group by MCHT_GRP) mchnt1 on (grp.MCHNT_TP_GRP = mchnt1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date < '" + lastMon + "' group by MCHT_GRP) mchnt2 on (grp.MCHNT_TP_GRP = mchnt2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "' group by MCHT_GRP) mchnt3 on (grp.MCHNT_TP_GRP = mchnt3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "' group by MCHT_GRP) mchnt4 on (grp.MCHNT_TP_GRP = mchnt4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); int index = 6; int len = 0; if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = ""; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; len++; } } data[index][0] = "4"; data[index][1] = "?POS"; sql = "SELECT DESCR,nvl(term1.c,0),nvl(term2.c,0),nvl(term3.c,0),nvl(term4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS <= '" + lastYear + "' group by MCHT_GRP) term1 on (grp.MCHNT_TP_GRP = term1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS < '" + lastMon + "' group by MCHT_GRP) term2 on (grp.MCHNT_TP_GRP = term2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "' group by MCHT_GRP) term3 on (grp.MCHNT_TP_GRP = term3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "' group by MCHT_GRP) term4 on (grp.MCHNT_TP_GRP = term4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = "?"; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = null; HSSFCell cell = null; sheet.setDefaultColumnWidth(10); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 6000); //? HSSFCellStyle styleTitle = ExcelUtil.createStyleTitle(workbook); HSSFCellStyle styleBold = ExcelUtil.createStyleBold(workbook); HSSFCellStyle styleCenter = ExcelUtil.createStyleCenter(workbook); HSSFCellStyle styleRight = ExcelUtil.createStyleRight(workbook); HSSFCellStyle styleThin = ExcelUtil.createStyleThinCenter(workbook); short rowIndex = 0;// // row = sheet.createRow(rowIndex++); row.setHeight((short) 800); cell = row.createCell(0); cell.setCellStyle(styleTitle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("?"); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));// ?? rowIndex++; row = sheet.createRow(rowIndex++); cell = row.createCell(0); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(1); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(date.substring(0, 4) + "" + date.substring(4) + ""); cell = row.createCell(3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(InformationUtil.getBrhName(brhId)); String[] titles = { "??", " ", "", "??", "", "", "", "", "" }; row = sheet.createRow(rowIndex++); for (int i = 0; i < titles.length; i++) { cell = row.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titles[i]); cell.setCellStyle(styleBold); } for (int i = 0; i < index; i++) { row = sheet.createRow(rowIndex++); for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); if (j < 4) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleCenter); cell.setCellValue(data[i][j]); } else { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle(styleRight); cell.setCellValue(Double.valueOf(data[i][j])); } } } int start = 1; sheet.addMergedRegion(new CellRangeAddress(start + 1, start + 1, 4, 8)); sheet.addMergedRegion(new CellRangeAddress(start + 2, start + 2, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 3, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 5, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 4, start + 5, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 6, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 8, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 7, start + 8, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 1, 1)); String path = ExcelUtil.writeFiles(workbook, "RN50202RN_" + brhId + "_" + CommonFunction.getCurrentDateTime() + ".xls"); return returnService(Constants.SUCCESS_CODE_CUSTOMIZE + path); } catch (Exception e) { e.printStackTrace(); return returnService("?,?", e); } }
From source file:com.ibm.asset.trails.form.ReportDownload.java
@Override public void doGet(HttpServletRequest pHttpServletRequest, HttpServletResponse pHttpServletResponse) throws ServletException, IOException { String lsName = this.getParameter(pHttpServletRequest, "name"); String lsCode = this.getParameter(pHttpServletRequest, "code"); UserSession lUserSession = ((UserSession) pHttpServletRequest.getSession().getAttribute("userSession")); if (lUserSession != null || lsName.equalsIgnoreCase(REPORT_NAME_NON_WORKSTATION_ACCOUNTS) || lsName.equalsIgnoreCase(REPORT_NAME_WORKSTATION_ACCOUNTS)) { Account lAccount = null;//from w ww. j av a2 s . c om String remoteUser = null; ReportBase lReportBase = null; ServletContext lServletContext = getServletContext(); WebApplicationContext lWebApplicationContext = WebApplicationContextUtils .getRequiredWebApplicationContext(lServletContext); ReportService lReportService = (ReportService) lWebApplicationContext.getBean("reportService"); if (lUserSession != null) { lAccount = ((UserSession) pHttpServletRequest.getSession().getAttribute("userSession")) .getAccount(); remoteUser = ((UserSession) pHttpServletRequest.getSession().getAttribute("userSession")) .getRemoteUser(); } try { pHttpServletResponse.setContentType("application/vnd.ms-excel"); HSSFWorkbook hwb = new HSSFWorkbook(); if (lsCode != null) { pHttpServletResponse.setHeader("Content-Disposition", new StringBuffer("filename=").append(lsName).append(lsCode) .append(lAccount != null ? lAccount.getAccount().toString() : "").append(".xls") .toString()); } else { pHttpServletResponse.setHeader("Content-Disposition", new StringBuffer("filename=").append(lsName) .append(lAccount != null ? lAccount.getAccount().toString() : "").append(".xls") .toString()); } lReportBase = getReport(lsName, lsCode, lReportService, pHttpServletResponse.getOutputStream(), pHttpServletRequest, hwb); if (lReportBase != null) { lReportBase.execute(pHttpServletRequest, lAccount, remoteUser, lsName); } } catch (Exception e) { e.printStackTrace(System.out); } } else { pHttpServletResponse.sendRedirect("/TRAILS"); } }
From source file:com.ibm.asset.trails.ws.AlertIbmSwInstancesReviewedServiceEndpoint.java
@GET @Path("/download/{accountId}") public Response download(@PathParam("accountId") Long accountId, @Context HttpServletRequest request, @Context HttpServletResponse response) throws IOException { try {/*from w w w. j a va2 s . c o m*/ HSSFWorkbook hwb = new HSSFWorkbook(); Account account = accountService.getAccount(accountId); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=alertUnlicensedIbmSw" + account.getAccount() + ".xls"); reportService.getAlertUnlicensed(account, request.getRemoteUser(), null, hwb, response.getOutputStream(), "SWIBM", "SWIBM", "SOM4a: IBM SW INSTANCES REVIEWED", "IBM"); //story 35896 response.flushBuffer(); } catch (Exception e) { e.printStackTrace(); } ResponseBuilder responseBuilder = Response.ok(response.getOutputStream()); return responseBuilder.build(); }
From source file:com.ibm.asset.trails.ws.AlertIsvSwInstancesReviewedServiceEndpoint.java
@GET @Path("/download/{accountId}") public Response download(@PathParam("accountId") Long accountId, @Context HttpServletRequest request, @Context HttpServletResponse response) throws IOException { try {/* w ww . jav a 2s . co m*/ HSSFWorkbook hwb = new HSSFWorkbook(); Account account = accountService.getAccount(accountId); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Isv" + account.getAccount() + ".xls"); reportService.getAlertUnlicensed(account, request.getRemoteUser(), null, hwb, response.getOutputStream(), "SWISVNPR", "SWISVNPR", "SOM4c: ISV SW INSTANCES REVIEWED", "ISV"); //story 35896 response.flushBuffer(); } catch (Exception e) { e.printStackTrace(); } ResponseBuilder responseBuilder = Response.ok(response.getOutputStream()); return responseBuilder.build(); }