List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet(String sheetname)
From source file:com.conecta.sat.utils.BuildXls.java
@Override protected void buildExcelDocument(Map<String, Object> map, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook, HttpServletRequest hsr, HttpServletResponse hsr1) throws Exception { hsr1.setContentType("application/vnd.ms-excel"); DateFormat name = new SimpleDateFormat("ddMMyyyyhhmmss"); hsr1.setHeader("Content-disposition", "attachment; filename=Reporte" + name.format(new Date()) + ".xls"); HSSFSheet sheet = workbook.createSheet("Inventario"); sheet.setDefaultColumnWidth(30);/* ww w . j av a 2 s.com*/ CellStyle style = workbook.createCellStyle(); List<PdfDTO> list = (List<PdfDTO>) map.get("list"); HSSFRow header = sheet.createRow(0); for (int i = 0; i < sColumnas.length; i++) { header.createCell(i).setCellValue(sColumnas[i]); header.getCell(i).setCellStyle(style); } int rowCount = 1; HSSFCellStyle my_style_0 = workbook.createCellStyle(); for (PdfDTO pdfDto : list) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(pdfDto.getSerial()); aRow.createCell(1).setCellValue(pdfDto.getEntrega()); aRow.createCell(2).setCellValue(pdfDto.getEntrega()); aRow.createCell(3).setCellValue(pdfDto.getActivacion()); aRow.createCell(4).setCellValue(pdfDto.getCentro()); aRow.createCell(5).setCellValue(pdfDto.getTipo()); aRow.createCell(6).setCellValue(pdfDto.getCliente()); aRow.createCell(7).setCellValue(pdfDto.getFolioPivotal()); // aRow.createCell(8).setCellValue( pdfDto.getIdUnico() ); aRow.createCell(8).setCellValue(pdfDto.getLastUpdate()); } }
From source file:com.crucialticketing.util.ReportView.java
@Override protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { List<Ticket> ticketList = (List<Ticket>) model.get("ticketList"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z"); sdf.setTimeZone(TimeZone.getTimeZone("GMT+1")); //create a wordsheet HSSFSheet sheet = workbook.createSheet("Ticket Report"); HSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Ticket ID"); header.createCell(1).setCellValue("Ticket Type"); header.createCell(2).setCellValue("Short Description"); header.createCell(3).setCellValue("Application"); header.createCell(4).setCellValue("Severity"); header.createCell(5).setCellValue("Workflow"); header.createCell(6).setCellValue("Role"); header.createCell(7).setCellValue("Workflow Status"); header.createCell(8).setCellValue("Queue"); header.createCell(9).setCellValue("SLA Clock"); header.createCell(10).setCellValue("SLA Elapsed"); header.createCell(11).setCellValue("SLA Status"); header.createCell(12).setCellValue("Created By"); header.createCell(13).setCellValue("Created On"); header.createCell(14).setCellValue("Reported By"); header.createCell(15).setCellValue("Reported On"); header.createCell(16).setCellValue("Last Updated By"); header.createCell(17).setCellValue("Last Updated On"); int rowNum = 1; for (Ticket ticket : ticketList) { //create the row data HSSFRow row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(ticket.getTicketId()); row.createCell(1).setCellValue(ticket.getApplicationControl().getTicketType().getTicketTypeName()); row.createCell(2).setCellValue(ticket.getShortDescription()); row.createCell(3).setCellValue(ticket.getApplicationControl().getApplication().getApplicationName()); row.createCell(4).setCellValue(ticket.getApplicationControl().getSeverity().getSeverityLevel() + ":" + ticket.getApplicationControl().getSeverity().getSeverityName()); row.createCell(5).setCellValue(ticket.getApplicationControl().getWorkflow().getWorkflowName()); row.createCell(6).setCellValue(ticket.getApplicationControl().getRole().getRoleName()); row.createCell(7)// w w w .j ava 2s . c o m .setCellValue(ticket.getCurrentWorkflowStep().getWorkflowStatus().getWorkflowStatusName()); row.createCell(8).setCellValue(ticket.getCurrentWorkflowStep().getQueue().getQueueName()); row.createCell(9).setCellValue(ticket.getApplicationControl().getSlaClock()); row.createCell(10).setCellValue(ticket.getChangeLog().getTimeElapsed()); if (ticket.getApplicationControl().getSlaClock() >= ticket.getChangeLog().getTimeElapsed()) { row.createCell(11).setCellValue("Not Violated"); } else { row.createCell(11).setCellValue("Violated"); } row.createCell(12).setCellValue(ticket.getChangeLog().getChangeLog().get(0).getUser().getUsername()); row.createCell(13) .setCellValue(Timestamp.convTimestamp(ticket.getChangeLog().getChangeLog().get(0).getStamp())); row.createCell(14).setCellValue(ticket.getChangeLog().getChangeLog().get(0).getUser().getUsername()); row.createCell(15) .setCellValue(Timestamp.convTimestamp(ticket.getChangeLog().getChangeLog().get(0).getStamp())); row.createCell(16).setCellValue(ticket.getChangeLog().getChangeLog() .get(ticket.getChangeLog().getChangeLog().size() - 1).getUser().getUsername()); row.createCell(17).setCellValue(Timestamp.convTimestamp(ticket.getChangeLog().getChangeLog() .get(ticket.getChangeLog().getChangeLog().size() - 1).getStamp())); } }
From source file:com.crunchify.jsp.servlet.HSSFCreate.java
/** * Processes requests for both HTTP GET and POST methods. * * @param request servlet request/*from w w w. j a v a 2s.c om*/ * @param response servlet response */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { DepartamentoDAO d = new DepartamentoDAO(); response.setContentType("application/vnd.ms-excel"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("new sheet"); Map<String, Object[]> data = new HashMap<String, Object[]>(); data.put("1", new Object[] { "Emp No.", "Name" }); for (int i = 0; i < d.findAll().size(); i++) { data.put("2", new Object[] { d.findAll().get(i).getNom_departamento(), d.findAll().get(i).getNom_departamento() }); } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } } } // Write the output OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }
From source file:com.dayuan.action.BusFileAction.java
/**???Excel*/ @RequestMapping("/exportExcel") public ModelAndView exportExcel(HttpServletRequest request, HttpServletResponse response) { Map<String, Object> context = getRootMap(); SysUser user = SessionUtils.getUser(request); BusFileModel busFileModel = new BusFileModel(); if (SuperAdmin.YES.key != user.getSuperAdmin() && user.getExcelAuth() == 0) { //busFileModel.setlUserName(user.getNickName()); busFileModel.setlUId(user.getId().toString()); }/*from w w w.java 2 s . c om*/ busFileModel.setRows(500); try { List<BusFiles> list = busFileService.queryByList(busFileModel); if (list != null && list.size() > 0) { // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("?"); HSSFSheet sheetLoan = wb.createSheet("?"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); HSSFRow rowLoan = sheetLoan.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); // ? style.setAlignment(HSSFCellStyle.ALIGN_CENTER); /**sheet1 ?*/ HSSFCell cell = row.createCell((short) 0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("??");//busfiles.lUserName cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("??");//busLoanInfo.applicationName cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("?"); //busfiles.lStatus cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue(""); //busfiles.createTime cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("??"); //busLoanInfo.channel cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("?"); //buslending.loanAmount cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("?"); //buslending.openingQuota cell.setCellStyle(style); cell = row.createCell((short) 8); cell.setCellValue(""); //busBiling.creditEndDate cell.setCellStyle(style); cell = row.createCell((short) 9); cell.setCellValue("???"); //busBiling.loanAccount cell.setCellStyle(style); cell = row.createCell((short) 10); cell.setCellValue("?"); //legal.deliveryAddress cell.setCellStyle(style); /**sheet2 ?*/ HSSFCell cellLoan = rowLoan.createCell((short) 0); cellLoan.setCellValue("??"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 1); cellLoan.setCellValue("??");//busLoanInfo.applicationName cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 2); cellLoan.setCellValue("?"); //busLoanInfo.urgentCont cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 3); cellLoan.setCellValue("");//busLoanInfo.relationship cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 4); cellLoan.setCellValue("?");//busLoanInfo.urgentContPhone cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 5); cellLoan.setCellValue("?");//busLoanInfo.urgentContAddress cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 6); cellLoan.setCellValue("??"); //biling.loanCardNumber cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 7); cellLoan.setCellValue("1"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 8); cellLoan.setCellValue("?1"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 9); cellLoan.setCellValue("???1"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 10); cellLoan.setCellValue("?1"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 11); cellLoan.setCellValue("2"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 12); cellLoan.setCellValue("?2"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 13); cellLoan.setCellValue("???2"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 14); cellLoan.setCellValue("?2"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 15); cellLoan.setCellValue(""); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 16); cellLoan.setCellValue("??"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 17); cellLoan.setCellValue("????"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 18); cellLoan.setCellValue("??"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 19); cellLoan.setCellValue("??"); cellLoan.setCellStyle(style); cellLoan = rowLoan.createCell((short) 20); cellLoan.setCellValue("?"); cellLoan.setCellStyle(style); for (int i = 0; i < list.size(); i++) { BusFiles busFiles = list.get(i); Integer lId = busFiles.getId(); BusLoanInfo busLoanInfo = busLoanInfoService.queryByLId(lId); BusLending busLending = busLendingService.queryByBId(lId); BusBiling busBiling = busBilingService.queryByBId(lId); BusLoanInfoLegal legal = busLoanInfoLegalService.getBusLoanInfoLegal(lId); List<BusLoanInfoShop> shopList = busLoanInfoShopService.queryListByBId(lId); /**rowsheet1*/ row = sheet.createRow((int) 1 + i); row.createCell((short) 0).setCellValue(i + 1); row.createCell((short) 1).setCellValue(busFiles.getlUserName()); row.createCell((short) 3).setCellValue(busFiles.getlStatus()); row.createCell((short) 4).setCellValue( DateUtil.getFormattedDateUtil((Date) busFiles.getCreateTime(), "yyyy-MM-dd HH:mm:ss")); /**rowLoansheet2*/ rowLoan = sheetLoan.createRow((int) 1 + i); rowLoan.createCell((short) 0).setCellValue(i + 1); if (busLoanInfo != null) { row.createCell((short) 2).setCellValue(busLoanInfo.getApplicationName()); row.createCell((short) 5).setCellValue(busLoanInfo.getChannel()); rowLoan.createCell((short) 1).setCellValue(busLoanInfo.getApplicationName()); rowLoan.createCell((short) 2).setCellValue(busLoanInfo.getUrgentCont()); rowLoan.createCell((short) 3).setCellValue(busLoanInfo.getRelationship()); rowLoan.createCell((short) 4).setCellValue(busLoanInfo.getUrgentContPhone()); rowLoan.createCell((short) 5).setCellValue(busLoanInfo.getUrgentContAddress()); } else { row.createCell((short) 2).setCellValue(""); row.createCell((short) 5).setCellValue(""); rowLoan.createCell((short) 1).setCellValue(""); rowLoan.createCell((short) 2).setCellValue(""); rowLoan.createCell((short) 3).setCellValue(""); rowLoan.createCell((short) 4).setCellValue(""); rowLoan.createCell((short) 5).setCellValue(""); } if (busLending != null) { row.createCell((short) 6).setCellValue(busLending.getLoanAmount()); row.createCell((short) 7).setCellValue(busLending.getOpeningQuota()); } else { row.createCell((short) 6).setCellValue(""); row.createCell((short) 7).setCellValue(""); } if (busBiling != null) { row.createCell((short) 8).setCellValue(busBiling.getCreditEndDate()); row.createCell((short) 9).setCellValue(busBiling.getLoanAccount()); rowLoan.createCell((short) 6).setCellValue(busBiling.getLoanCardNumber());//?? rowLoan.createCell((short) 15).setCellValue(DateUtil .getFormattedDateUtil((Date) busBiling.getCheckDate(), "yyyy-MM-dd HH:mm:ss"));// rowLoan.createCell((short) 16).setCellValue(busBiling.getCreditorIfNormal());//?? rowLoan.createCell((short) 17).setCellValue(busBiling.getGuarantorIfNormal());//???? rowLoan.createCell((short) 18).setCellValue(busBiling.getCloudLoanIfWarning());//?? rowLoan.createCell((short) 19).setCellValue(busBiling.getShopOperation());//?? rowLoan.createCell((short) 20).setCellValue(busBiling.getOtherNeedToExplained());//? } else { row.createCell((short) 8).setCellValue(""); row.createCell((short) 9).setCellValue(""); rowLoan.createCell((short) 6).setCellValue("");//?? rowLoan.createCell((short) 15).setCellValue("");// rowLoan.createCell((short) 16).setCellValue("");//?? rowLoan.createCell((short) 17).setCellValue("");//???? rowLoan.createCell((short) 18).setCellValue("");//?? rowLoan.createCell((short) 19).setCellValue("");//?? rowLoan.createCell((short) 20).setCellValue("");//? } if (legal != null) { row.createCell((short) 10).setCellValue(legal.getDeliveryAddress()); } else { row.createCell((short) 10).setCellValue(""); } if (shopList != null && shopList.size() > 0) { if (shopList.size() == 1) { BusLoanInfoShop shop = shopList.get(0); rowLoan.createCell((short) 7).setCellValue(shop.getShopName());// rowLoan.createCell((short) 8).setCellValue(shop.getPlatformName());//? rowLoan.createCell((short) 9).setCellValue(shop.getSubAccount());//??? rowLoan.createCell((short) 10).setCellValue(shop.getSbuPassword());//? rowLoan.createCell((short) 11).setCellValue("");// rowLoan.createCell((short) 12).setCellValue("");//? rowLoan.createCell((short) 13).setCellValue("");//??? rowLoan.createCell((short) 14).setCellValue("");//? } else if (shopList.size() == 2) { BusLoanInfoShop shop = shopList.get(0); rowLoan.createCell((short) 7).setCellValue(shop.getShopName());// rowLoan.createCell((short) 8).setCellValue(shop.getPlatformName());//? rowLoan.createCell((short) 9).setCellValue(shop.getSubAccount());//??? rowLoan.createCell((short) 10).setCellValue(shop.getSbuPassword());//? BusLoanInfoShop shop1 = shopList.get(1); rowLoan.createCell((short) 11).setCellValue(shop1.getShopName());// rowLoan.createCell((short) 12).setCellValue(shop1.getPlatformName());//? rowLoan.createCell((short) 13).setCellValue(shop1.getSubAccount());//??? rowLoan.createCell((short) 14).setCellValue(shop1.getSbuPassword());//? } } else { rowLoan.createCell((short) 7).setCellValue(""); rowLoan.createCell((short) 8).setCellValue(""); rowLoan.createCell((short) 9).setCellValue(""); rowLoan.createCell((short) 10).setCellValue(""); rowLoan.createCell((short) 11).setCellValue(""); rowLoan.createCell((short) 12).setCellValue(""); rowLoan.createCell((short) 13).setCellValue(""); rowLoan.createCell((short) 14).setCellValue(""); } } String savePath = request.getSession().getServletContext().getRealPath( File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,?? File savePathFile = new File(savePath); if (!savePathFile.exists()) { savePathFile.mkdirs(); } savePath = savePath + File.separator + UUID.randomUUID();//? File fileSavePath = new File(savePath); /**??*/ if (fileSavePath.exists()) { if (fileSavePath.isDirectory()) { File[] files = fileSavePath.listFiles(); for (File file : files) { file.delete(); } fileSavePath.delete(); } else { fileSavePath.delete(); } fileSavePath.mkdirs(); } else { fileSavePath.mkdirs(); } String excel = "????" + DateUtil.getNowLongTime() + ".xls";//eccel?? BufferedOutputStream fout = new BufferedOutputStream( new FileOutputStream(savePath + File.separator + excel)); //excel wb.write(fout); //excel? fout.flush(); fout.close(); //MIME response.setContentType(request.getSession().getServletContext().getMimeType(excel)); //Content-Disposition response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8")); BufferedInputStream in = new BufferedInputStream( new FileInputStream(savePath + File.separator + excel));//,io? OutputStream out = new BufferedOutputStream(response.getOutputStream()); byte buffer[] = new byte[1024]; int len = 0; while ((len = in.read(buffer)) > 0) { out.write(buffer, 0, len);//?response? } in.close(); out.flush(); out.close(); /***/ File file = new File(savePath + File.separator + excel); if (file != null) { if (file.exists()) { file.delete();// } file = null; } /***/ if (fileSavePath != null) { if (fileSavePath.exists()) { fileSavePath.delete(); } fileSavePath = null; } if (savePathFile != null) { savePathFile = null; } log.info("Excel?"); } } catch (Exception e) { //e.printStackTrace(); log.error("exportExcel" + e.getMessage()); context.put("message", "??"); return forword("message/message", context); } return null; }
From source file:com.dayuan.action.BusinessLoanAction.java
/** * ?excel//w w w. j a v a 2s .c o m * */ @RequestMapping("/exportAllExcel") public void exportAllExcel(HttpServletRequest request, HttpServletResponse response) throws Exception { SysUser user = SessionUtils.getUser(request); BusLoanInfoModel busLoanInfoModel = new BusLoanInfoModel(); busLoanInfoModel.setuId(user.getId().toString()); busLoanInfoModel.setuName(user.getNickName()); List<BusLoanInfo> list = busLoanInfoService.queryList(busLoanInfoModel); if (list != null && list.size() > 0) { // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("???"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); // ? style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("??"); //applicationName cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("?");//urgentCont cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue(""); // relationship cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue("?"); //legalPhone cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("?"); //houseAddress cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("?"); //companyName cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("?");// ?platformName cell.setCellStyle(style); cell = row.createCell((short) 8); cell.setCellValue(""); //?shopName cell.setCellStyle(style); cell = row.createCell((short) 9); cell.setCellValue("???"); //?,subAccount cell.setCellStyle(style); cell = row.createCell((short) 10); cell.setCellValue("?"); //?,sbuPassword cell.setCellStyle(style); for (int i = 0; i < list.size(); i++) { BusLoanInfo busLoanInfo = list.get(i); Integer bid = busLoanInfo.getId(); BusLoanInfoLegal busLoanInfoLegal = this.busLoanInfoLegalService.getBusLoanInfoLegal(bid); BusLoanInfoShop busLoanInfoShop = this.busLoanInfoShopService.getBusLoanInfoShop(bid); row = sheet.createRow((int) 1 + i); row.createCell((short) 0).setCellValue(i + 1); row.createCell((short) 1).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getApplicationName())); row.createCell((short) 2).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getUrgentCont())); row.createCell((short) 3).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getRelationship())); if (busLoanInfoLegal != null) { row.createCell((short) 4) .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getLegalPhone())); row.createCell((short) 5) .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getHouseAddress())); row.createCell((short) 6) .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getCompanyName())); } else { row.createCell((short) 4).setCellValue(""); row.createCell((short) 5).setCellValue(""); row.createCell((short) 6).setCellValue(""); } if (busLoanInfoShop != null) { row.createCell((short) 7) .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getPlatformName())); row.createCell((short) 8).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getShopName())); row.createCell((short) 9) .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSubAccount())); row.createCell((short) 10) .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSbuPassword())); } else { row.createCell((short) 7).setCellValue(""); row.createCell((short) 8).setCellValue(""); row.createCell((short) 9).setCellValue(""); row.createCell((short) 10).setCellValue(""); } } String savePath = request.getSession().getServletContext().getRealPath( File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,?? savePath = savePath + File.separator + UUID.randomUUID();//? File fileSavePath = new File(savePath); /**??*/ if (fileSavePath.exists()) { if (fileSavePath.isDirectory()) { File[] files = fileSavePath.listFiles(); for (File file : files) { file.delete(); } fileSavePath.delete(); } else { fileSavePath.delete(); } fileSavePath.mkdirs(); } else { fileSavePath.mkdirs(); } String excel = "????" + DateUtil.getNowPlusTimeMill() + ".xls";//eccel?? BufferedOutputStream fout = new BufferedOutputStream( new FileOutputStream(savePath + File.separator + excel)); // wb.write(fout); //excel? fout.flush(); fout.close(); //MIME response.setContentType(request.getSession().getServletContext().getMimeType(excel)); //Content-Disposition response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8")); BufferedInputStream in = new BufferedInputStream( new FileInputStream(savePath + File.separator + excel));//,io? OutputStream out = new BufferedOutputStream(response.getOutputStream()); byte buffer[] = new byte[1024]; int len = 0; while ((len = in.read(buffer)) > 0) { out.write(buffer, 0, len);//?response? } in.close(); out.flush(); out.close(); /***/ File file = new File(savePath + File.separator + excel); if (file != null) { if (file.exists()) { file.delete();// } file = null; } /***/ if (fileSavePath != null) { if (fileSavePath.exists()) { fileSavePath.delete(); } fileSavePath = null; } log.info("excel?"); } }
From source file:com.dayuan.action.BusinessLoanAction.java
/** * excel/*from w w w .j a va 2 s. c o m*/ * * */ @RequestMapping("/exportExcel") public void exportExcel(Integer id, HttpServletRequest request, HttpServletResponse response) throws Exception { if (id == null || id.equals("")) { log.error("?"); return; } BusLoanInfo busLoanInfo = this.busLoanInfoService.queryById(id); if (busLoanInfo == null) { log.error("busLoanInfo?"); return; } Integer bid = busLoanInfo.getId(); if (bid == null) { log.error("busLoanInfo?"); return; } BusLoanInfoLegal busLoanInfoLegal = this.busLoanInfoLegalService.getBusLoanInfoLegal(bid); BusLoanInfoShop busLoanInfoShop = this.busLoanInfoShopService.getBusLoanInfoShop(bid); // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("???"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); // ? style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("??"); //applicationName cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("?");//urgentCont cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue(""); // relationship cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue("?"); //legalPhone cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("?"); //houseAddress cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("?"); //companyName cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("?");// ?platformName cell.setCellStyle(style); cell = row.createCell((short) 8); cell.setCellValue(""); //?shopName cell.setCellStyle(style); cell = row.createCell((short) 9); cell.setCellValue("???"); //?,subAccount cell.setCellStyle(style); cell = row.createCell((short) 10); cell.setCellValue("?"); //?,sbuPassword cell.setCellStyle(style); // ? ?? try { row = sheet.createRow((int) 0 + 1); row.createCell((short) 0).setCellValue(1); row.createCell((short) 1).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getApplicationName())); row.createCell((short) 2).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getUrgentCont())); row.createCell((short) 3).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getRelationship())); if (busLoanInfoLegal != null) { row.createCell((short) 4).setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getLegalPhone())); row.createCell((short) 5) .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getHouseAddress())); row.createCell((short) 6).setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getCompanyName())); } else { row.createCell((short) 4).setCellValue(""); row.createCell((short) 5).setCellValue(""); row.createCell((short) 6).setCellValue(""); } if (busLoanInfoShop != null) { row.createCell((short) 7).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getPlatformName())); row.createCell((short) 8).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getShopName())); row.createCell((short) 9).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSubAccount())); row.createCell((short) 10).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSbuPassword())); } else { row.createCell((short) 7).setCellValue(""); row.createCell((short) 8).setCellValue(""); row.createCell((short) 9).setCellValue(""); row.createCell((short) 10).setCellValue(""); } // Student stu = (Student) list.get(i); // ? // cell = row.createCell((short) 3); // cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBirth())); // row.createCell((short) 10).setCellValue(busLoanInfoShop.getSbuPassword());? String savePath = request.getSession().getServletContext().getRealPath( File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,?? savePath = savePath + File.separator + UUID.randomUUID();//? File fileSavePath = new File(savePath); /**??*/ if (fileSavePath.exists()) { if (fileSavePath.isDirectory()) { File[] files = fileSavePath.listFiles(); for (File file : files) { file.delete(); } fileSavePath.delete(); } else { fileSavePath.delete(); } fileSavePath.mkdirs(); } else { fileSavePath.mkdirs(); } String excel = "????" + DateUtil.getNowPlusTimeMill() + ".xls";//eccel?? BufferedOutputStream fout = new BufferedOutputStream( new FileOutputStream(savePath + File.separator + excel)); // wb.write(fout); //excel? fout.flush(); fout.close(); //MIME response.setContentType(request.getSession().getServletContext().getMimeType(excel)); //Content-Disposition response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8")); //,io? BufferedInputStream in = new BufferedInputStream( new FileInputStream(savePath + File.separator + excel)); OutputStream out = new BufferedOutputStream(response.getOutputStream()); byte buffer[] = new byte[1024]; int len = 0; while ((len = in.read(buffer)) > 0) { out.write(buffer, 0, len);//?response? } in.close(); out.flush(); out.close(); /***/ File file = new File(savePath + File.separator + excel); if (file != null) { if (file.exists()) { file.delete();// } file = null; } /***/ if (fileSavePath != null) { if (fileSavePath.exists()) { fileSavePath.delete(); } fileSavePath = null; } log.info("???excel?"); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.dp2345.ExcelView.java
License:Open Source License
/** * ?Excel//from w w w .j a v a 2 s .c o m * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "D" + "P" + "2" + "3" + "4" + "5")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.dv.util.DVExcelIO.java
License:Open Source License
public static boolean exportIntoExcel(String fullExcelFileName, String sheetName, Vector cols, Vector rows) { boolean isExportFine = true; HSSFWorkbook hsswb = null; HSSFSheet hsssh = null;//from w ww . j a va2 s . c om HSSFRow row = null; try { File excel = new File(fullExcelFileName); if (!excel.exists()) { hsswb = new HSSFWorkbook(); hsssh = hsswb.createSheet(sheetName); hsssh.setDefaultRowHeight((short) 10); hsssh.setDefaultColumnWidth(20); } else { hsswb = new HSSFWorkbook(new FileInputStream(excel)); hsssh = hsswb.createSheet(sheetName); hsssh.setDefaultRowHeight((short) 10); hsssh.setDefaultColumnWidth(20); } row = hsssh.createRow((short) 2); HSSFCellStyle style = hsswb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setTopBorderColor(HSSFColor.BLACK.index); style.setRightBorderColor(HSSFColor.BLACK.index); for (int i = 0; i < cols.size(); i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(cols.get(i).toString()); cell.setCellStyle(style); } HSSFCellStyle style1 = hsswb.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setBottomBorderColor(HSSFColor.BLACK.index); style1.setLeftBorderColor(HSSFColor.BLACK.index); style1.setTopBorderColor(HSSFColor.BLACK.index); style1.setRightBorderColor(HSSFColor.BLACK.index); for (int i = 3; i <= rows.size() + 2; i++) { row = hsssh.createRow((short) i); for (int j = 0; j < cols.size(); j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(((Vector) rows.elementAt(i - 3)).get(j).toString()); cell.setCellStyle(style1); } } FileOutputStream fOut = new FileOutputStream(excel); hsswb.write(fOut); fOut.flush(); fOut.close(); } catch (IOException e) { DVLOG.setErrorLog(DVExcelIO.class.getName(), e); return false; } catch (IllegalArgumentException e) { DVLOG.setErrorLog(DVExcelIO.class.getName(), e); return false; } catch (Exception e) { DVLOG.setErrorLog(DVExcelIO.class.getName(), e); return false; } return isExportFine; }
From source file:com.dv.util.DVExcelIO.java
License:Open Source License
public static boolean exportBatchResultIntoExcel(String fullExcelFileName, String sheetName, HashMap<String, Vector> colsMap, HashMap<String, Vector> rowsMap) { HSSFWorkbook hsswb = null; HSSFSheet hsssh = null;// w w w. ja v a 2s. c o m HSSFRow row = null; Vector cols = new Vector(); Vector rows = new Vector(); try { File excel = new File(fullExcelFileName); if (!excel.exists()) { hsswb = new HSSFWorkbook(); hsssh = hsswb.createSheet(sheetName); hsssh.setDefaultRowHeight((short) 10); hsssh.setDefaultColumnWidth(20); } else { hsswb = new HSSFWorkbook(new FileInputStream(excel)); hsssh = hsswb.createSheet(sheetName); hsssh.setDefaultRowHeight((short) 10); hsssh.setDefaultColumnWidth(20); } int rowCount = 1; for (int k = 0; k < colsMap.size(); k++) { cols = colsMap.get(String.valueOf(k)); rows = rowsMap.get(String.valueOf(k)); rowCount = rowCount + 1; row = hsssh.createRow((short) (rowCount)); HSSFCellStyle style = hsswb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setTopBorderColor(HSSFColor.BLACK.index); style.setRightBorderColor(HSSFColor.BLACK.index); for (int i = 0; i < cols.size(); i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(cols.get(i).toString()); cell.setCellStyle(style); } HSSFCellStyle style1 = hsswb.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setBottomBorderColor(HSSFColor.BLACK.index); style1.setLeftBorderColor(HSSFColor.BLACK.index); style1.setTopBorderColor(HSSFColor.BLACK.index); style1.setRightBorderColor(HSSFColor.BLACK.index); int loop = rowCount; for (int i = 1 + loop; i <= rows.size() + loop; i++) { row = hsssh.createRow((short) i); for (int j = 0; j < cols.size(); j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(((Vector) rows.elementAt(i - (1 + loop))).get(j).toString()); cell.setCellStyle(style1); } rowCount++; } } FileOutputStream fOut = new FileOutputStream(excel); hsswb.write(fOut); fOut.flush(); fOut.close(); } catch (IOException e) { DVLOG.setErrorLog(DVExcelIO.class.getName(), e); return false; } catch (IllegalArgumentException e) { DVLOG.setErrorLog(DVExcelIO.class.getName(), e); return false; } catch (Exception e) { DVLOG.setErrorLog(DVExcelIO.class.getName(), e); return false; } return true; }
From source file:com.eastsoft.ui.MainUI.java
License:Open Source License
void savePrintInfoToExcel(recordInfo recordinfor, int printFormat) { if (printFormat != 0) { HSSFWorkbook wb = null; File printRecord = new File("?.xls"); if (!printRecord.exists()) { wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("sheet1"); HSSFRow row = sheet1.createRow(0); row.createCell((short) 0).setCellValue("? "); row.createCell((short) 1).setCellValue(" "); row.createCell((short) 2).setCellValue("?? "); row.createCell((short) 3).setCellValue("?? "); row.createCell((short) 4).setCellValue("Aid "); row.createCell((short) 5).setCellValue("? "); HSSFRow row1 = sheet1.createRow(sheet1.getLastRowNum() + 1); row1.createCell((short) 0).setCellValue(recordinfor.getDate()); row1.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO()); row1.createCell((short) 2).setCellValue(recordinfor.getDevice()); row1.createCell((short) 3).setCellValue(recordinfor.getProduct()); row1.createCell((short) 4).setCellValue(recordinfor.getAid()); row1.createCell((short) 5).setCellValue(recordinfor.getPasswd()); } else {//from w ww. j a v a 2 s.c o m FileInputStream fs = null; try { fs = new FileInputStream(printRecord); } catch (FileNotFoundException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } POIFSFileSystem ps = null; try { ps = new POIFSFileSystem(fs); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { wb = new HSSFWorkbook(ps); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } HSSFSheet sheet = wb.getSheetAt(0); // ?cell,Rows 0(Create a row and put some cells // in // it. Rows are 0 based.) HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1); row.createCell((short) 0).setCellValue(recordinfor.getDate()); row.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO()); row.createCell((short) 2).setCellValue(recordinfor.getDevice()); row.createCell((short) 3).setCellValue(recordinfor.getProduct()); row.createCell((short) 4).setCellValue(recordinfor.getAid()); row.createCell((short) 5).setCellValue(recordinfor.getPasswd()); } FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(printRecord); } catch (FileNotFoundException e) { // TODO Auto-generated catch block appendTextareaText(jTextArea_status, "\n?.xls ??"); JOptionPane.showMessageDialog(getParent(), "?.xls ??", "?.xls", JOptionPane.WARNING_MESSAGE); e.printStackTrace(); } try { wb.write(fileOut); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { fileOut.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }