List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet()
From source file:com.sfy.controller.SiteManageController.java
@RequestMapping(value = "/exportExcel", method = RequestMethod.POST) public void exportExcelForPage(HttpServletRequest request, HttpServletResponse response) { SiteBaseInfo siteBaseInfo;/*from ww w . jav a 2s . com*/ OutputStream outputStream = null; try { response.setContentType("application/msexcel;charset=GBK"); response.setHeader("Content-Disposition", "attachment;filename=" .concat(String.valueOf(URLEncoder.encode("JDHMS-EXPORT-SITE.xls", "UTF-8")))); response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/vnd.ms-excel"); siteBaseInfo = assumeSiteBaseInfoEntity(request); siteBaseInfo.setErp(request.getParameter("loginUser")); siteBaseInfo = changeParamForMutilOrg(siteBaseInfo); // Result<List<SiteBaseInfo>> result = storeService.querySiteBaseInfoForPageByMultiOrg(siteBaseInfo); if (result.isSuccess()) { List<SiteBaseInfo> siteBaseInfoList = result.getResult(); if (siteBaseInfoList.size() > 0) { /* ?? */ List<String> querySyncList = new ArrayList<String>(); for (SiteBaseInfo siteBaseInfoQuery : siteBaseInfoList) { querySyncList.add(String.valueOf(siteBaseInfoQuery.getSiteNo())); } Map<String, List<SyncSiteInfoMq>> compositeMap = storeService .queryBunchSyncSiteInfo(querySyncList); /* EXCEL */ HSSFWorkbook workbook = new HSSFWorkbook(); List<String> listCol = new ArrayList<String>(); listCol.add(""); listCol.add("??"); listCol.add("??"); listCol.add("?"); listCol.add(""); listCol.add("?"); listCol.add(""); listCol.add(""); listCol.add(""); listCol.add("?"); listCol.add("??"); listCol.add("?"); listCol.add(""); listCol.add(""); listCol.add("?"); listCol.add(""); listCol.add("?"); listCol.add(""); listCol.add(""); listCol.add("??"); listCol.add("??"); listCol.add("?"); HSSFSheet sheet = workbook.createSheet(); HSSFRow headRow = sheet.createRow(0); for (int j = 0; j < listCol.size(); j++) { HSSFCell cell = headRow.createCell(j); cell.setCellValue(listCol.get(j)); } for (int i = 1; i <= siteBaseInfoList.size(); i++) { HSSFRow row = sheet.createRow(i); for (int j = 0; j < listCol.size(); j++) { HSSFCell cell = row.createCell(j); if (j == 0) { cell.setCellValue(siteBaseInfoList.get(i - 1).getOrgName()); } else if (j == 1) { cell.setCellValue(siteBaseInfoList.get(i - 1).getDistributeName()); } else if (j == 2) { cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteName()); } else if (j == 3) { cell.setCellValue(siteBaseInfoList.get(i - 1).getBussinessSiteNo()); } else if (j == 4) { cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteType()); } else if (j == 5) { cell.setCellValue(siteBaseInfoList.get(i - 1).getProvinceName()); } else if (j == 6) { cell.setCellValue(siteBaseInfoList.get(i - 1).getCityName()); } else if (j == 7) { cell.setCellValue(siteBaseInfoList.get(i - 1).getCountryName()); } else if (j == 8) { cell.setCellValue(siteBaseInfoList.get(i - 1).getTownName()); } else if (j == 9) { cell.setCellValue(siteBaseInfoList.get(i - 1).getAddress()); } else if (j == 10) { cell.setCellValue(siteBaseInfoList.get(i - 1).getGpsLongitude()); } else if (j == 11) { cell.setCellValue(siteBaseInfoList.get(i - 1).getGpsLatitude()); } else if (j == 12) { cell.setCellValue(siteBaseInfoList.get(i - 1).getManagerName()); } else if (j == 13) { cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteManager()); } else if (j == 14) { cell.setCellValue(siteBaseInfoList.get(i - 1).getJdAccount()); } else if (j == 15) { cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteLevel()); } else if (j == 16) { cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteStatus()); } else if (j == 17) { Date openTime = siteBaseInfoList.get(i - 1).getOpenTime(); if (openTime != null) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String formater = sdf.format(openTime); cell.setCellValue(formater); } else { cell.setCellValue(""); } } else if (j == 18) { Date createTime = siteBaseInfoList.get(i - 1).getCreateTime(); if (createTime != null) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String formater = sdf.format(createTime); cell.setCellValue(formater); } else { cell.setCellValue(""); } } else if (j == 19) { cell.setCellValue(siteBaseInfoList.get(i - 1).getTelephone()); } else if (j == 20) { if (compositeMap .containsKey(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo()))) { List<SyncSiteInfoMq> syncSiteInfoMqList = compositeMap .get(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo())); StringBuilder sb = new StringBuilder(); for (int ii = 0; ii < syncSiteInfoMqList.size(); ii++) { int systemSource = syncSiteInfoMqList.get(ii).getSystemSource(); if (systemSource == 0) { sb.append(syncSiteInfoMqList.get(ii).getWebsiteNo()); sb.append(","); } } String[] splitArray = sb.toString().split(","); String str = ""; for (int jj = 0; jj < splitArray.length; jj++) { str = str + splitArray[jj]; if (jj + 1 != splitArray.length) { str = str + ","; } } cell.setCellValue(str); } } else if (j == 21) { if (compositeMap .containsKey(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo()))) { List<SyncSiteInfoMq> syncSiteInfoMqList = compositeMap .get(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo())); StringBuilder sb = new StringBuilder(); for (int ii = 0; ii < syncSiteInfoMqList.size(); ii++) { int systemSource = syncSiteInfoMqList.get(ii).getSystemSource(); if (systemSource == 1) { sb.append(syncSiteInfoMqList.get(ii).getWebsiteNo()); sb.append(","); } } String[] splitArray = sb.toString().split(","); String str = ""; for (int jj = 0; jj < splitArray.length; jj++) { str = str + splitArray[jj]; if (jj + 1 != splitArray.length) { str = str + ","; } } cell.setCellValue(str); } } } } outputStream = response.getOutputStream(); workbook.write(outputStream); /* EXCEL? */ } } } catch (Exception e) { logger.error(e.toString()); } finally { try { if (outputStream != null) { outputStream.flush(); outputStream.close(); } } catch (Exception e) { e.printStackTrace(); } } }
From source file:com.smi.travel.datalayer.view.dao.impl.APNirvanaImpl.java
private String genReport(List<APNirvana> apDataList, String fullFileName, List<APNirvana> APList) { SimpleDateFormat df = new SimpleDateFormat(); df.applyPattern("dd/MM/yyyy"); String status = ""; UtilityFunction util = new UtilityFunction(); try {// w w w . j a va 2s. c o m HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); int rownum = 0; for (APNirvana ap : apDataList) { HSSFRow dataRow = sheet.createRow(rownum++); int cellnum = 0; HSSFCell cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getRefinvoiceno()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getIntreference()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVendorid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVendorname()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getDivisionid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getProjectid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getTranscode()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getTransdate() == null ? "" : util.ConvertString( df.format(util.convertStringToDate(String.valueOf(ap.getTransdate()))))); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getDuedate() == null ? "" : util.ConvertString(df.format(util.convertStringToDate(String.valueOf(ap.getDuedate()))))); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getCurrencyid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getHomerate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getForeignrate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevatamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevathmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getVatamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getVathmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getTransamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getTranshmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVatflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVatid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getWhtflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getWhtid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhthmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getWhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getWhthmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getYear() == null ? "" : ap.getYear().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPeriod() == null ? "" : ap.getPeriod().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getNote()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount4()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision4()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject4()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt4())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt4())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount5()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision5()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject5()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt5())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt5())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount6()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision6()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject6()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt6())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt6())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount7()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision7()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject7()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt7())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt7())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount8()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision8()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject8()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt8())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt8())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount9()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision9()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject9()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt9())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt9())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPuraccount10()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurdivision10()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPurproject10()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt10())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt10())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getService()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getApaccount()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getPrefix()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVoucherno()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getTaxid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getVendor_branch() == null ? "0" : ap.getVendor_branch().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ap.getCompany_branch()); // for(int j =0;j<100;j++){ // sheet.autoSizeColumn(j); // } } FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls")); workbook.write(out); out.close(); status = "success"; } catch (Exception e) { e.printStackTrace(); for (APNirvana ap : APList) { if (!"".equals(status)) { status += ", "; } status += ap.getPayment_detail_id(); } } return status; }
From source file:com.smi.travel.datalayer.view.dao.impl.ARNirvanaImpl.java
private String genReport(List<ARNirvana> arDataList, String fullFileName, List<ARNirvana> ARList) { UtilityFunction util = new UtilityFunction(); String status = ""; SimpleDateFormat df = new SimpleDateFormat(); df.applyPattern("dd/MM/yyyy"); try {//w ww.ja v a2s . c om HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); int rownum = 0; for (ARNirvana ar : arDataList) { HSSFRow dataRow = sheet.createRow(rownum++); int cellnum = 0; HSSFCell cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getIntreference()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesmanid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCustomerid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCustomername()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getDivisionid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getProjectid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getTranscode()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getTransdate() == null ? "" : util.ConvertString( df.format(util.convertStringToDate(String.valueOf(ar.getTransdate()))))); cell = dataRow.createCell(cellnum++); if (ar.getDuedate() == null) { cell.setCellValue(""); } else { cell.setCellValue(util .ConvertString(df.format(util.convertStringToDate(String.valueOf(ar.getDuedate()))))); } cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCurrencyid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getHomerate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getForeignrate())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getVatamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getVathmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getAramt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getArhmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getVatflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getVatid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getWhtflag()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getWhtid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getBasewhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getBasewhthmamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getWhtamt())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getWhthmamt())); cell = dataRow.createCell(cellnum++); if (ar.getYear() == null) { cell.setCellValue(""); } else { cell.setCellValue(ar.getYear()); } cell = dataRow.createCell(cellnum++); if (ar.getPeriod() == null) { cell.setCellValue(""); } else { cell.setCellValue(ar.getPeriod()); } cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getNote()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesaccount1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesdivision1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesproject1()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt1())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesaccount2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesdivision2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesproject2()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt2())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesaccount3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesdivision3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getSalesproject3()); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSalesamt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(UtilityFunction.getObjectString(ar.getSaleshmamt3())); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getService()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getAraccount()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getPrefix()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getDocumentno()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getArtrans()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCust_taxid()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCust_branch() == null ? "" : ar.getCust_branch().toString()); cell = dataRow.createCell(cellnum++); cell.setCellValue(ar.getCompany_branch()); // for(int j =0;j<100;j++){ // sheet.autoSizeColumn(j); // } } FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls")); workbook.write(out); out.close(); status = "success"; } catch (Exception e) { e.printStackTrace(); for (ARNirvana ar : ARList) { if (!"".equals(status)) { status += ", "; } status += ar.getReceive_detail_id(); } } return status; }
From source file:Controlador.jControlador.java
public boolean imprimirExcel(File archivo, JTable table) { try {/*from w w w . j a va 2 s.c o m*/ HSSFWorkbook libro = new HSSFWorkbook(); HSSFSheet hoja = libro.createSheet(); for (int i = 0; i <= table.getRowCount(); i++) { HSSFRow fila = hoja.createRow(i); if (i == 0) { for (int j = 0; j < table.getColumnCount(); j++) { HSSFCell celda = fila.createCell(j); celda.setCellValue(new HSSFRichTextString( table.getColumnModel().getColumn(j).getHeaderValue().toString())); } } if (i != 0) { for (int j = 0; j < table.getColumnCount(); j++) { HSSFCell celda = fila.createCell(j); if (table.getValueAt(i - 1, j) != null) celda.setCellValue(new HSSFRichTextString(table.getValueAt(i - 1, j).toString())); } } } FileOutputStream elFichero = new FileOutputStream(archivo.getAbsolutePath() + ".xls"); libro.write(elFichero); elFichero.close(); return true; } catch (Exception e) { e.printStackTrace(); } return false; }
From source file:cuenta.CuentaAhorros.java
@Override public void toExcel() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = sheet.createRow(0);/*from w ww .j av a2 s .c om*/ HSSFCell cell = row.createCell(0); cell.setCellValue(this.toString()); try { OutputStream out = new FileOutputStream("CuentaAhorros.xls"); workbook.write(out); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Se gener el documento EXCEL de la CUENTA DE AHORROS en la raz del proyecto"); }
From source file:cuenta.CuentaCorriente.java
@Override public void toExcel() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = sheet.createRow(0);/*from ww w. j av a 2 s . c o m*/ HSSFCell cell = row.createCell(0); cell.setCellValue(this.toString()); try { OutputStream out = new FileOutputStream("CuentaCorriente.xls"); workbook.write(out); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Se gener el documento EXCEL de la CUENTA CORRIENTE en la raz del proyecto"); }
From source file:dataQuality.checkDuplicates.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w w w . j a v a 2s . c om*/ dbConn conn = new dbConn(); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 7000); shet1.setColumnWidth(5, 5300); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5200); shet1.setColumnWidth(8, 5200); shet1.setColumnWidth(9, 5200); shet1.setColumnWidth(10, 5800); shet1.setColumnWidth(11, 5000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); // styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); // styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell3 = rw4.createCell(2); cell4 = rw4.createCell(3); cell5 = rw4.createCell(4); cell6 = rw4.createCell(5); cell7 = rw4.createCell(6); cell8 = rw4.createCell(7); cell9 = rw4.createCell(8); cell10 = rw4.createCell(9); cell11 = rw4.createCell(10); cell12 = rw4.createCell(11); cell13 = rw4.createCell(12); cell14 = rw4.createCell(13); cell1.setCellValue("COUNTY NAME"); cell2.setCellValue("PARTNER NAME"); cell3.setCellValue("DISTRICT"); cell4.setCellValue("FACILITY"); cell5.setCellValue("GROUP NAME"); cell6.setCellValue("CLIENT NAME"); cell7.setCellValue("AGE"); cell8.setCellValue("GENDER"); cell9.setCellValue("YEAR"); cell10.setCellValue("SESSION ATTENDED"); cell11.setCellValue("No. of duplicates"); cell11.setCellValue("SERVICE PROVIDER"); cell12.setCellValue("START DATE"); cell13.setCellValue("END DATE"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); //cell14.setCellStyle(stylex); i = 1; String getClients = "SELECT * FROM clients ORDER BY fname"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; sess = val = cds = duplicate = 0; startdate = enddate = ""; // System.out.println("here : "+i); HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; cell1x = rw4x.createCell(0); cell2x = rw4x.createCell(1); cell3x = rw4x.createCell(2); cell4x = rw4x.createCell(3); cell5x = rw4x.createCell(4); cell6x = rw4x.createCell(5); cell7x = rw4x.createCell(6); cell8x = rw4x.createCell(7); cell9x = rw4x.createCell(8); cell10x = rw4x.createCell(9); cell11x = rw4x.createCell(10); cell12x = rw4x.createCell(11); cell13x = rw4x.createCell(12); // cell14x=rw4x.createCell(13); providername = ""; clientid = conn.rs.getString(1); clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); age = conn.rs.getString(5); gender = conn.rs.getString(6); groupid = conn.rs.getString(7); groupings = conn.rs.getString(8); districtid = conn.rs.getString(9); partnerid = conn.rs.getString(10); year = conn.rs.getString(13); providerid = conn.rs.getString(14); if (conn.rs.getString(3).equals(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } cds = conn.rs.getInt("lessons_attended"); start_date = end_date = ""; String serviceprov = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + providerid + "'"; conn.rs1 = conn.st1.executeQuery(serviceprov); if (conn.rs1.next()) { if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs.getString(3); } else { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs.getString(3); } } String checker = "SELECT COUNT(client_id) FROM clients WHERE fname=? && lname=? && client_id!=?"; conn.pst = conn.conn.prepareStatement(checker); conn.pst.setString(1, conn.rs.getString(2)); conn.pst.setString(2, conn.rs.getString(3)); // conn.pst.setString(3, conn.rs.getString(4)); conn.pst.setString(3, clientid); // conn.pst.setString(3, conn.rs.getString(2)); // conn.pst.setString(4, conn.rs.getString(3)); // conn.pst.setString(5, groupid); // conn.pst.setString(6, conn.rs.getString(2)); // conn.pst.setString(7, age); // conn.pst.setString(8, clientid); // conn.pst.setString(8, age); conn.rs1 = conn.pst.executeQuery(); found = 0; if (conn.rs1.next() == true) { found = conn.rs1.getInt(1); } System.out.println("found : " + found); if (found > 0) { duplicate = found; } if (found == 0) { duplicate = 0; } String getCnt = "SELECT district.district_name,county.county_name FROM district JOIN county ON district.county_id=county.county_id WHERE district.district_id='" + districtid + "'"; conn.rs1 = conn.st1.executeQuery(getCnt); if (conn.rs1.next() == true) { district = conn.rs1.getString(1); county = conn.rs1.getString(2); } String getPart = "SELECT partner_name FROM partner WHERE partner_id='" + partnerid + "'"; conn.rs1 = conn.st1.executeQuery(getPart); if (conn.rs1.next() == true) { partner = conn.rs1.getString(1); } if (!groupid.equals("0")) { String getgrp = "SELECT groups.group_name,health_facility.hf_name FROM groups JOIN health_facility ON groups.nhf_id=health_facility.hf_id" + " WHERE groups.group_id='" + groupid + "'"; conn.rs1 = conn.st1.executeQuery(getgrp); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); hf = conn.rs1.getString(2); // SELECT START END DATE DATE FOR GROUP INDIVIDUALS---------------------------------------------- String getDates = "SELECT MAX(session_date),MIN(session_date) FROM sessions WHERE group_id='" + groupid + "' && session_date!=''"; conn.rs1 = conn.st1.executeQuery(getDates); if (conn.rs1.next() == true) { start_date = conn.rs1.getString(2); end_date = conn.rs1.getString(1); } } } if (groupid.equals("0")) { String getgrp1 = "SELECT health_facility.hf_name FROM no_group JOIN health_facility ON no_group.nhf_id=health_facility.hf_id" + " WHERE no_group.name='" + groupings + "'"; conn.rs1 = conn.st1.executeQuery(getgrp1); if (conn.rs1.next() == true) { groupname = "INDIVIDUAL"; hf = conn.rs1.getString(1); } String getDates = "SELECT MAX(session_date),MIN(session_date) FROM sessions WHERE groupings='" + groupings + "' && session_date!=''"; conn.rs1 = conn.st1.executeQuery(getDates); if (conn.rs1.next() == true) { start_date = conn.rs1.getString(2); end_date = conn.rs1.getString(1); } } // OUTPUT ATTENDED-------------------------------- if (duplicate > 0) { // OUTPUT SERVICES PROVIDED================================ cell1x.setCellValue(county); cell2x.setCellValue(partner); cell3x.setCellValue(district); cell4x.setCellValue(hf); cell5x.setCellValue(groupname); cell6x.setCellValue(clientname); cell7x.setCellValue(age); cell8x.setCellValue(gender); cell9x.setCellValue(year); cell10x.setCellValue(cds); // cell11x.setCellValue(duplicate); cell11x.setCellValue(providername); cell12x.setCellValue(start_date); cell13x.setCellValue(end_date); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); cell5x.setCellStyle(styleBorder); cell6x.setCellStyle(styleBorder); cell7x.setCellStyle(styleBorder); cell8x.setCellStyle(styleBorder); cell9x.setCellStyle(styleBorder); cell10x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell12x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); //cell14x.setCellStyle(styleBorder); i++; System.out.println("here : " + i); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_DUPLICATE_REPORT.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:dbedit.actions.ExportExcelAction.java
License:Open Source License
@Override protected void performThreaded(ActionEvent e) throws Exception { boolean selection = false; JTable table = ResultSetTable.getInstance(); if (table.getSelectedRowCount() > 0 && table.getSelectedRowCount() != table.getRowCount()) { Object option = Dialog.show("Excel", "Export", Dialog.QUESTION_MESSAGE, new Object[] { "Everything", "Selection" }, "Everything"); if (option == null || "-1".equals(option.toString())) { return; }//from w w w . ja v a 2 s . co m selection = "Selection".equals(option); } List list = ((DefaultTableModel) table.getModel()).getDataVector(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = sheet.createRow(0); HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.WHITE.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); for (int i = 0; i < table.getColumnCount(); i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(new HSSFRichTextString(table.getColumnName(i))); cell.setCellStyle(style); sheet.setColumnWidth(i, (table.getColumnModel().getColumn(i).getPreferredWidth() * 45)); } int count = 1; for (int i = 0; i < list.size(); i++) { if (!selection || table.isRowSelected(i)) { List data = (List) list.get(i); row = sheet.createRow(count++); for (int j = 0; j < data.size(); j++) { Object o = data.get(j); HSSFCell cell = row.createCell(j); if (o instanceof Number) { cell.setCellValue(((Number) o).doubleValue()); } else if (o != null) { if (ResultSetTable.isLob(j)) { cell.setCellValue( new HSSFRichTextString(Context.getInstance().getColumnTypeNames()[j])); } else { cell.setCellValue(new HSSFRichTextString(o.toString())); } } } } } sheet.createFreezePane(0, 1); ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); workbook.write(byteArrayOutputStream); FileIO.saveAndOpenFile("export.xls", byteArrayOutputStream.toByteArray()); }
From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelImportServiceAttributesIntegrationTest.java
License:Open Source License
/** * Tests if importing Building Block the attribute values will be not deleted. This case is tested with * {@link InformationSystemRelease}, but it is actual to all Building blocks. * //from w w w . java 2 s .c o m * @throws Exception if an exception occurs */ @Test public void testAttributesExistAfterISRImport() { InformationSystem is = testDataHelper.createInformationSystem("I"); TypeOfStatus typeOfStatus = InformationSystemRelease.TypeOfStatus.CURRENT; InformationSystemRelease isr = testDataHelper.createInformationSystemRelease(is, "i1", TEST_DESCRIPTION, "1.1.2005", "31.12.2005", typeOfStatus); TextAV textAV1 = createTextAttribute("1"); TextAV textAV2 = createTextAttribute("2"); TextAV textAV3 = createTextAttribute("3"); testDataHelper.createAVA(isr, textAV1); testDataHelper.createAVA(isr, textAV2); testDataHelper.createAVA(isr, textAV3); final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFSheet sheet = workbook.createSheet(); final HSSFRow row = sheet.createRow(0); final Cell nameCell = row.createCell(0); final Cell descriptionCell = row.createCell(1); BuildingBlockHolder buildingBlockHolder = new BuildingBlockHolder(isr, nameCell, descriptionCell); buildingBlockHolder.setClone(BuildingBlockUtil.clone(isr)); LandscapeData landscapeData = new LandscapeData(); landscapeData.addBuildingBlock(buildingBlockHolder); final Map<String, CellValueHolder> attributesMap = Maps.newHashMap(); final Cell cell = row.createCell(2); cell.setCellValue(UPDATED_TEXT_AV1); CellValueHolder cellValueHolder = new CellValueHolder(cell); attributesMap.put("TextAT1", cellValueHolder); landscapeData.getAttributes().add(new BuildingBlockAttributes(isr, attributesMap)); landscapeData.setLocale(Locale.GERMAN); excelImportService.importLandscapeData(landscapeData); InformationSystemRelease isrLoadedFromDb = isrService.loadObjectById(isr.getId()); Set<AttributeValueAssignment> attributeValueAssignments = isrLoadedFromDb.getAttributeValueAssignments(); assertEquals("Some attribute value assignments were deleted.", 3, attributeValueAssignments.size()); checkAttributeValues(attributeValueAssignments, textAV1.getAbstractAttributeType(), UPDATED_TEXT_AV1); checkAttributeValues(attributeValueAssignments, textAV2.getAbstractAttributeType(), "Text Value2"); checkAttributeValues(attributeValueAssignments, textAV3.getAbstractAttributeType(), "Text Value3"); }
From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelImportServiceAttributesIntegrationTest.java
License:Open Source License
/** * Tests if the attributes of the {@link InformationSystemInterface} will be imported and updated. * //from w w w .j a v a 2s . c o m * @throws Exception if an exception occurs */ @Test public void testAttributesExistAfterInterfaceImport() { InformationSystem isA = testDataHelper.createInformationSystem("I1"); InformationSystemRelease isrA = testDataHelper.createInformationSystemRelease(isA, "i1", TEST_DESCRIPTION, "1.1.2005", "31.12.2005", InformationSystemRelease.TypeOfStatus.CURRENT); InformationSystem isB = testDataHelper.createInformationSystem("I2"); InformationSystemRelease isrB = testDataHelper.createInformationSystemRelease(isB, "i2", TEST_DESCRIPTION, "1.1.2006", "31.12.2006", InformationSystemRelease.TypeOfStatus.CURRENT); InformationSystemInterface informationSystemInterface = testDataHelper .createInformationSystemInterfaceWithNameDirection("", "-", "Interface description", isrA, isrB, null); assertNotNull(informationSystemInterface); TextAV textAV1 = createTextAttribute("1"); TextAV textAV2 = createTextAttribute("2"); TextAV textAV3 = createTextAttribute("3"); testDataHelper.createAVA(informationSystemInterface, textAV1); testDataHelper.createAVA(informationSystemInterface, textAV2); testDataHelper.createAVA(informationSystemInterface, textAV3); final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFSheet sheet = workbook.createSheet(); final HSSFRow row = sheet.createRow(0); final Cell cell1 = row.createCell(0); final Cell cell2 = row.createCell(0); final Cell cell3 = row.createCell(0); cell1.setCellValue(UPDATED_TEXT_AV1); cell2.setCellValue(isrA.getNonHierarchicalName()); cell3.setCellValue(isrB.getNonHierarchicalName()); final Map<String, Cell> attributesMap = Maps.newHashMap(); attributesMap.put("TextAT1", cell1); final Map<String, Cell> contentMap = Maps.newHashMap(); contentMap.put("Informationssystem A", cell2); contentMap.put("Informationssystem B", cell3); LandscapeData landscapeData = new LandscapeData(); landscapeData.addRelation(informationSystemInterface, contentMap, attributesMap); landscapeData.setLocale(Locale.GERMAN); excelImportService.importLandscapeData(landscapeData); InformationSystemInterface isiLoadedFromDb = isiService.loadObjectById(informationSystemInterface.getId()); Set<AttributeValueAssignment> attributeValueAssignments = isiLoadedFromDb.getAttributeValueAssignments(); assertEquals("Some attribute value assignments were deleted.", 3, attributeValueAssignments.size()); checkAttributeValues(attributeValueAssignments, textAV1.getAbstractAttributeType(), UPDATED_TEXT_AV1); checkAttributeValues(attributeValueAssignments, textAV2.getAbstractAttributeType(), "Text Value2"); checkAttributeValues(attributeValueAssignments, textAV3.getAbstractAttributeType(), "Text Value3"); }