List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write
private void write(POIFSFileSystem fs) throws IOException
From source file:com.adonis.ui.print.PrintVehiclesUI.java
public static File createXLSVehicles(String fileName, List<Vehicle> vehicles) { try {/*from www . ja v a 2 s .co m*/ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Excel Sheet"); HSSFRow rowhead = sheet.createRow((short) 0); rowhead.createCell((short) 0).setCellValue("vehicleNmbr"); rowhead.createCell((short) 1).setCellValue("licenseNmbr"); rowhead.createCell((short) 2).setCellValue("make"); rowhead.createCell((short) 3).setCellValue("vehicleType"); rowhead.createCell((short) 4).setCellValue("model"); rowhead.createCell((short) 5).setCellValue("price"); rowhead.createCell((short) 6).setCellValue("location"); vehicles.forEach(rent -> { HSSFRow row = sheet.createRow((short) index); row.createCell((short) 0).setCellValue(rent.getVehicleNmbr()); row.createCell((short) 1).setCellValue(rent.getLicenseNmbr()); row.createCell((short) 2).setCellValue(rent.getMake()); row.createCell((short) 3).setCellValue(rent.getVehicleType()); row.createCell((short) 4).setCellValue(rent.getModel()); row.createCell((short) 5).setCellValue(rent.getPrice()); row.createCell((short) 6).setCellValue(rent.getLocation()); index++; }); FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); System.out.println("Data is saved in excel file."); } catch (Exception ex) { ex.printStackTrace(); } return new File(fileName); }
From source file:com.ah.ui.actions.admin.LicenseMgrAction.java
License:Open Source License
private void exportEntitleKeyInfo() { try {/*from w w w . j a v a 2 s. co m*/ // create a new file FileOutputStream out = new FileOutputStream(ORDERKEYINFO_EXPORT_FILE_PATH); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet("Sheet1"); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // row index int rowNum = 0; // cell count int cellcount = 0; if (NmsUtil.isHMForOEM()) { cellcount = 7; } else if (getIsInHomeDomain()) { cellcount = 8; } else if (NmsUtil.isHostedHMApplication()) { cellcount = 9; } if (cellcount == 0) { return; } else { for (int i = 0; i < cellcount; i++) { s.setColumnWidth(i, getColumnWidthByIndex(i) * 256); } } // create cell style HSSFCellStyle cs = wb.createCellStyle(); // create font object HSSFFont f = wb.createFont(); //set font to 12 point type f.setFontHeightInPoints((short) 12); f.setFontName("Calibri"); //set cell stlye cs.setFont(f); cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs1 = wb.createCellStyle(); // create font object HSSFFont f1 = wb.createFont(); //set font to 12 point type f1.setFontHeightInPoints((short) 12); f1.setFontName("Calibri"); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); //set cell stlye cs1.setFont(f1); cs1.setAlignment(CellStyle.ALIGN_RIGHT); cs1.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setFont(f1); cs2.setAlignment(CellStyle.ALIGN_CENTER); cs2.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs3 = wb.createCellStyle(); cs3.setFont(f); cs3.setAlignment(CellStyle.ALIGN_RIGHT); cs3.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs4 = wb.createCellStyle(); cs4.setFont(f1); cs4.setAlignment(CellStyle.ALIGN_LEFT); cs4.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs5 = wb.createCellStyle(); // create font object HSSFFont f2 = wb.createFont(); //set font to 12 point type f2.setFontHeightInPoints((short) 12); f2.setFontName("Calibri"); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setColor(Font.COLOR_RED); cs5.setFont(f2); cs5.setAlignment(CellStyle.ALIGN_LEFT); cs5.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs6 = wb.createCellStyle(); cs6.setFont(f); cs6.setAlignment(CellStyle.ALIGN_LEFT); cs6.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create a row // row 1 s.addMergedRegion(new CellRangeAddress(0, 0, 0, cellcount - 1)); r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.title")); c.setCellStyle(cs); // row 2 String sysInfo = ""; if (getIsInHomeDomain()) { sysInfo = "System ID: " + getSystemId(); // HiveManager Online user } else if (NmsUtil.isHostedHMApplication()) { sysInfo = "VHM ID: " + getDomain().getVhmID(); } s.addMergedRegion(new CellRangeAddress(1, 1, 0, cellcount - 1)); r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(sysInfo); c.setCellStyle(cs); // row 3 s.addMergedRegion(new CellRangeAddress(2, 2, 0, cellcount - 1)); r = s.createRow(rowNum++); c = r.createCell(0); List<?> userInfo = QueryUtil.executeQuery( "SELECT email, company FROM " + UserRegInfoForLs.class.getSimpleName(), null, new FilterParams("owner.domainName", getDomain().getDomainName())); if (!userInfo.isEmpty()) { Object[] userInfos = (Object[]) userInfo.get(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.email.company", new String[] { (String) userInfos[0], (String) userInfos[1] })); } c.setCellStyle(cs); // row 4 r = s.createRow(rowNum++); // row 5 r = s.createRow(rowNum++); // row 6 cell 1 c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.licensed")); c.setCellStyle(cs1); // row 6 cell 2 c = r.createCell(1); c.setCellValue(licenseInfo.getHiveAps()); c.setCellStyle(cs2); // device management info Map<String, Integer> apcount = HiveAPInfoFromeDatabase .getManagedDeviceTypeAndNumber(getDomain().getDomainName()); int vpnCount = 0; int totalCount = 0; if (null != apcount) { Object[] typeNames = apcount.keySet().toArray(); Arrays.sort(typeNames); for (Object typeName : typeNames) { // VPN Gateway VA does not belong to device if (MgrUtil.getEnumString("enum.hiveAp.model.10").equals(typeName)) { vpnCount = apcount.get(typeName); } else { r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue((String) typeName); c.setCellStyle(cs3); c = r.createCell(1); c.setCellValue(apcount.get(typeName)); c.setCellStyle(cs); totalCount += apcount.get(typeName); } } } // managed device total number cell1 r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.total")); c.setCellStyle(cs1); // managed device total number cell2 c = r.createCell(1); c.setCellValue(totalCount); c.setCellStyle(cs2); // blank row r = s.createRow(rowNum++); // licensed VPN Gateway VA cell1 r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.licensed")); c.setCellStyle(cs1); // licensed VPN Gateway VA cell2 c = r.createCell(1); c.setCellValue(licenseInfo.getCvgNumber()); c.setCellStyle(cs2); // managed VPN Gateway VA cell1 r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.total")); c.setCellStyle(cs1); // managed VPN Gateway VA cell 2 c = r.createCell(1); c.setCellValue(vpnCount); c.setCellStyle(cs2); // entitlement key information preparePage(); if (null != page && !page.isEmpty()) { r = s.createRow(rowNum++); List<OrderHistoryInfo> normalKey = new ArrayList<>(); List<OrderHistoryInfo> invalidKey = new ArrayList<>(); List<OrderHistoryInfo> expiredKey = new ArrayList<>(); for (Object obj : page) { OrderHistoryInfo orderInfo = (OrderHistoryInfo) obj; if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL && orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL) { normalKey.add(orderInfo); } else if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE || orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE) { invalidKey.add(orderInfo); } else { expiredKey.add(orderInfo); } } if (!normalKey.isEmpty()) { // normal entitle key title r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs4, null, r, null, MgrUtil.getUserMessage("order.key")); // normal entitle key info for (OrderHistoryInfo keyInfo : normalKey) { r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null); } } if (!invalidKey.isEmpty()) { if (!normalKey.isEmpty()) { r = s.createRow(rowNum++); r = s.createRow(rowNum++); } // invalid entitle key title r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs5, null, r, null, MgrUtil.getUserMessage("admin.license.orderkey.export.invalidkey.title")); // invalid entitle key info for (OrderHistoryInfo keyInfo : invalidKey) { r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null); } } if (!expiredKey.isEmpty()) { if (!normalKey.isEmpty() || !invalidKey.isEmpty()) { r = s.createRow(rowNum++); r = s.createRow(rowNum++); } // expired entitle key title r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs5, null, r, null, MgrUtil.getUserMessage("admin.license.orderkey.export.expiredkey.title")); // expired entitle key info for (OrderHistoryInfo keyInfo : expiredKey) { r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null); } } } // write the workbook to the output stream // close our file (don't blow out our file handles) wb.write(out); out.close(); generateAuditLog(HmAuditLog.STATUS_SUCCESS, MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key")); } catch (Exception ex) { generateAuditLog(HmAuditLog.STATUS_FAILURE, MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key")); log.error("exportEntitleKeyInfo()", ex.getMessage()); } }
From source file:com.aimluck.eip.modules.screens.ALXlsScreen.java
License:Open Source License
@Override protected void doOutput(RunData rundata) throws Exception { VelocityContext context = new VelocityContext(); try {// ww w .j av a 2 s . co m init(rundata, context); doCheckAclPermission(rundata, context, getDefineAclType()); // xls?? HSSFWorkbook wb = createXLSFile(rundata, context); HttpServletResponse response = rundata.getResponse(); // ???? response.setHeader("Content-disposition", "attachment; filename=\"" + getFileName() + "\""); response.setHeader("Cache-Control", "aipo"); response.setHeader("Pragma", "aipo"); if (wb != null) { wb.write(response.getOutputStream()); } } catch (ALPermissionException e) { ALEipUtils.redirectPermissionError(rundata); } catch (Exception e) { logger.error("ALXlsScreen.doOutput", e); } }
From source file:com.alibaba.differ.biz.TableExporter.java
License:Open Source License
public void export() throws IOException { fc.setFileFilter(new ExcelFileFilter()); fc.setFileHidingEnabled(true);/*from w w w . j ava 2 s .c om*/ fc.setAcceptAllFileFilterUsed(false); int returnValue = fc.showSaveDialog(null); if (returnValue != JFileChooser.APPROVE_OPTION) { return; } File file = fc.getSelectedFile(); if (file.exists()) { JOptionPane.showMessageDialog(null, ""); return; } FileOutputStream fos = new FileOutputStream(file + ".xls"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet hs = wb.createSheet(); TableModel tm = table.getModel(); int row = tm.getRowCount(); int cloumn = tm.getColumnCount(); HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFCellStyle style1 = wb.createCellStyle(); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style1.setBorderRight(HSSFCellStyle.BORDER_THIN); style1.setBorderTop(HSSFCellStyle.BORDER_THIN); style1.setFillForegroundColor(HSSFColor.ORANGE.index); style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 15); font1.setBoldweight((short) 700); style1.setFont(font); for (int i = 0; i < row + 1; i++) { HSSFRow hr = hs.createRow(i); for (int j = 0; j < cloumn; j++) { if (i == 0) { String value = tm.getColumnName(j); hs.setColumnWidth(j, UIConfig.EXCEL_COLUMN_WIDTH); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setCellStyle(style1); hc.setCellValue(srts); } else { if (tm.getValueAt(i - 1, j) != null) { String value = tm.getValueAt(i - 1, j).toString(); HSSFRichTextString srts = new HSSFRichTextString(value); HSSFCell hc = hr.createCell((short) j); hc.setCellStyle(style); if (value.equals("") || value == null) { hc.setCellValue(new HSSFRichTextString("")); } else { hc.setCellValue(srts); } } } } } wb.write(fos); fos.close(); JOptionPane.showMessageDialog(null, "Excel"); }
From source file:com.anphat.list.controller.ListStaffController.java
public File exportFile(List<StaffDTO> lstStaffInfo) { try {//from w ww . j a v a2 s . c o m FileOutputStream fileOut = new FileOutputStream(PATH_EXPORT + Constants.FILE_NAME.LIST_STAFF); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet("danhsachnhanvien"); HSSFCellStyle cellStyle; HSSFCellStyle rowStyle; HSSFCellStyle cellStyleLeft = null; HSSFCellStyle cellStyleRight = null; //style left cellStyleLeft = workbook.createCellStyle(); cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); //phai cellStyleRight = workbook.createCellStyle(); cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyleRight.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyleRight.setBorderTop(HSSFCellStyle.BORDER_THIN); // //header bang HSSFRow row5 = worksheet.createRow(0); HSSFCell cellA1 = row5.createCell(0); cellA1.setCellValue(BundleUtils.getString("STT")); cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setWrapText(true); //rowstyle rowStyle = workbook.createCellStyle(); rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); rowStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); rowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); rowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); rowStyle.setFillForegroundColor(HSSFColor.WHITE.index); rowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); rowStyle.setWrapText(true); cellA1.setCellStyle(cellStyle); HSSFCell cellB1 = row5.createCell(1); cellB1.setCellValue(BundleUtils.getString("cms.StaffInfo.code")); cellB1.setCellStyle(cellStyle); HSSFCell cellC1 = row5.createCell(2); cellC1.setCellValue(BundleUtils.getString("cms.StaffInfo.name")); cellC1.setCellStyle(cellStyle); HSSFCell cellD1 = row5.createCell(3); cellD1.setCellValue(BundleUtils.getString("cms.StaffInfo.email")); cellD1.setCellStyle(cellStyle); HSSFCell cellE1 = row5.createCell(4); cellE1.setCellValue(BundleUtils.getString("cms.StaffInfo.birthdate")); cellE1.setCellStyle(cellStyle); HSSFCell cellF1 = row5.createCell(5); cellF1.setCellValue(BundleUtils.getString("cms.StaffInfo.phoneNumber")); cellF1.setCellStyle(cellStyle); HSSFCell cellG1 = row5.createCell(6); cellG1.setCellValue(BundleUtils.getString("cms.StaffInfo.deptName")); cellG1.setCellStyle(cellStyle); HSSFCell cellH1 = row5.createCell(7); cellH1.setCellValue(BundleUtils.getString("cms.StaffInfo.staffType")); cellH1.setCellStyle(cellStyle); HSSFCell cellI1 = row5.createCell(8); cellI1.setCellValue(BundleUtils.getString("cms.StaffInfo.status")); cellI1.setCellStyle(cellStyle); //content bang if (!lstStaffInfo.isEmpty()) { int j = 0; for (StaffDTO staff : lstStaffInfo) { HSSFRow row = worksheet.createRow(j + 1); HSSFCell cellA = row.createCell(0); cellA.setCellValue(j + 1); cellA.setCellStyle(rowStyle); HSSFCell cellB = row.createCell(1); cellB.setCellValue((staff.getDeptId() == null) ? Constants.NULL : staff.getCode()); cellB.setCellStyle(cellStyleLeft); HSSFCell cellC = row.createCell(2); cellC.setCellValue((staff.getCode() == null) ? Constants.NULL : staff.getName()); cellC.setCellStyle(cellStyleLeft); HSSFCell cellD = row.createCell(3); cellD.setCellValue((staff.getName() == null) ? Constants.NULL : staff.getEmail()); cellD.setCellStyle(cellStyleLeft); HSSFCell cellE = row.createCell(4); // cellE.setCellValue((staff.getEmail() == null) ? Constants.NULL : staff.getBirthDate()); cellE.setCellStyle(cellStyleLeft); HSSFCell cellF = row.createCell(5); cellF.setCellValue((staff.getTelNumber() == null) ? Constants.NULL : staff.getTelNumber()); cellF.setCellStyle(cellStyleLeft); HSSFCell cellG = row.createCell(6); // cellG.setCellValue((staff.getDeptName() == null) ? Constants.NULL : staff.getDeptName()); cellG.setCellStyle(cellStyleLeft); HSSFCell cellH = row.createCell(7); cellH.setCellValue((staff.getStaffType() == null) ? Constants.NULL : BundleUtils.getString( "staff.type." + DataUtil.getStringEscapeHTML4(staff.getStaffType()))); cellH.setCellStyle(cellStyleLeft); HSSFCell cellI = row.createCell(8); cellI.setCellValue((staff.getStatus() == null) ? Constants.NULL : BundleUtils.getString( "common.status." + DataUtil.getStringEscapeHTML4(staff.getStatus()))); cellI.setCellStyle(cellStyleLeft); j++; } //Set Width for (int i = 0; i <= 0; i++) { worksheet.setColumnWidth(i, 2000); } for (int i = 1; i <= 7; i++) { worksheet.setColumnWidth(i, 5000); } for (int i = 8; i <= 10; i++) { worksheet.setColumnWidth(i, 3000); } } try { workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } File file = new File(PATH_EXPORT + Constants.FILE_NAME.LIST_STAFF); return file; }
From source file:com.assentisk.util.FilesService.java
public void generatePositionProfileXls(HttpServletRequest req, String Doc_name, List catList) throws SQLException, IOException { Integer key = 1;//from ww w . j av a 2s. co m try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(Doc_name); Map<String, Object[]> data = new LinkedHashMap<String, Object[]>(); data.put(key.toString(), new Object[] { "Control ID", "Risk", "Control Objective", "Control Description", "Test Steps", "Busines Process Name", "Control Category Name" }); if (catList != null) { for (int i = 0; i < catList.size(); i++) { Map map = (Map) catList.get(i); short colIndex = 0; data.put((++key).toString(), new Object[] { map.get("Control_ID"), map.get("Risk"), map.get("Control_Objective"), map.get("Control_Description"), map.get("Test_Steps"), map.get("businessProcess"), map.get("controlcategory") }); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 14, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); Object[] objArr = data.get(key2); short cellnum = 0; for (Object obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof Integer) row.createCell(cellnum++).setCellValue((Integer) obj); else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((Integer) obj); else if (obj instanceof Integer) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); } } } } FileOutputStream out = new FileOutputStream(new File("" + Doc_name + ".xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.assentisk.util.FilesService.java
public void generateEmployeDemoXls(HttpServletRequest req, String docName, List<LocationsBean> getLocationList, List<DepartmentBean> getDepartmnentList, List<LegalEntitiesBean> divisionList) throws SQLException, IOException { Integer key = 1;/*from www . j a v a2s . c o m*/ try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(docName); String[] str = new String[getLocationList.size()]; if (getLocationList.size() > 0) { for (int i = 0; i < getLocationList.size(); i++) { Map m = (Map) getLocationList.get(i); str[i] = m.get("Name").toString(); } } String[] strDprtmnt = new String[getDepartmnentList.size()]; if (getDepartmnentList.size() > 0) { for (int i = 0; i < getDepartmnentList.size(); i++) { Map m = (Map) getDepartmnentList.get(i); strDprtmnt[i] = m.get("DeptName").toString(); } } String[] strDivision = new String[divisionList.size()]; if (divisionList.size() > 0) { for (int i = 0; i < divisionList.size(); i++) { Map m = (Map) divisionList.get(i); strDivision[i] = m.get("LegalEntityName").toString(); } } /*for(int i=0;i<locationName.size();i++){ str[i]=locationName.get(i).toString(); }*/ // for(int i=1;i<50;i++){ // CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1); // DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str); // DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation); // // CellRangeAddressList departmnentList = new CellRangeAddressList(i, i, 0, 0); // DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strDprtmnt); // DataValidation dataValidation1 = new HSSFDataValidation(departmnentList, dvConstraint1); // dataValidation1.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation1); // // // CellRangeAddressList devisionList = new CellRangeAddressList(i, i, 9, 9); // DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strDivision); // DataValidation dataValidation2 = new HSSFDataValidation(devisionList, dvConstraint2); // dataValidation2.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation2); // // } // Map<String, String[]> data = new LinkedHashMap<String, String[]>(); String[] stringVal = { "EmpName", "Department", "Location", "location Country", "location State", "location City", "location Contact Name", "location Email", "Divisions", "address", "city", "zip", "phone1", "phone2", "Email1", "Email2" }; // data.put(key.toString(), { "DepartmentID","LocationID","EmpName","Email", "address","city","state","country","zip","phone1","phone2","divisions","email2","isLicense","isActive","abc"}); data.put(key.toString(), stringVal); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 17, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); String[] objArr = data.get(key2); short cellnum = 0; for (String obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); } } FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.assentisk.util.FilesService.java
public void generateAssestDemoXls(HttpServletRequest req, String docName, List<CategoriesBean> getCategoryList, List<CountryBean> getCountryList, List<ApplicationsBean> appList2, List<BusinessProcessesBean> getBusinessList) throws SQLException, IOException { Integer key = 1;//ww w . ja va 2s .co m try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(docName); // HSSFSheet hidden = workbook.createSheet("hidden"); // HSSFSheet hidden1 = workbook.createSheet("hidden1"); // HSSFSheet hidden2 = workbook.createSheet("hidden2"); // HSSFSheet hidden3 = workbook.createSheet("hidden3"); String[] strCategorymnt = new String[getCategoryList.size()]; // if(getCategoryList.size()>0) // { // for (int i = 0; i < getCategoryList.size(); i++) { // Map m = (Map)getCategoryList.get(i); // strCategorymnt[i] = m.get("cname").toString(); // String name = strCategorymnt[i]; // HSSFRow row = hidden3.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // } // } // // String [] strCountry = new String[getCountryList.size()]; // if(getCountryList.size()>0) // { // for (int i = 0; i < getCountryList.size(); i++) { // // Map m = (Map)getCountryList.get(i); // strCountry[i] = m.get("CountryName").toString(); // String name = strCountry[i]; // HSSFRow row = hidden.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // // } // } // String [] strApllication = new String[appList2.size()]; // if(appList2.size()>0) // { // for (int i = 0; i < appList2.size(); i++) { // // Map m = (Map)appList2.get(i); // strApllication[i] = m.get("ApplicationName").toString(); // String name = strApllication[i]; // HSSFRow row = hidden2.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // // } // } // String [] strBusinessList = new String[getBusinessList.size()]; // if(getBusinessList.size()>0) // { // for (int i = 0; i < getBusinessList.size(); i++) { // // Map m = (Map)getBusinessList.get(i); // strBusinessList[i] = m.get("BusinessProcessName").toString(); // String name = strBusinessList[i]; // HSSFRow row = hidden1.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // } // } // Name namedCell = workbook.createName(); // namedCell.setNameName("hidden"); // namedCell.setRefersToFormula("hidden!$A$1:$A$" + strCountry.length); // Name namedCell1 = workbook.createName(); // namedCell1.setNameName("hidden1"); // namedCell1.setRefersToFormula("hidden1!$A$1:$A$" + strBusinessList.length); // // Name namedCell2 = workbook.createName(); // namedCell2.setNameName("hidden2"); // namedCell2.setRefersToFormula("hidden2!$A$1:$A$" + strCategorymnt.length); // // Name namedCell3 = workbook.createName(); // namedCell3.setNameName("hidden3"); // namedCell3.setRefersToFormula("hidden3!$A$1:$A$" + strApllication.length); // for(int i=1;i<50;i++){ // DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"); // CellRangeAddressList country = new CellRangeAddressList(1, 1, 7, 7); // HSSFDataValidation validation = new HSSFDataValidation(country, constraint); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation); // DVConstraint constraint1 = DVConstraint.createFormulaListConstraint("hidden1"); // CellRangeAddressList businessList = new CellRangeAddressList(1, 1, 3,3); // HSSFDataValidation validation1 = new HSSFDataValidation(businessList, constraint1); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation1); // // DVConstraint constraint3 = DVConstraint.createFormulaListConstraint("hidden2"); // CellRangeAddressList appList = new CellRangeAddressList(1, 1, 2, 2); // HSSFDataValidation validation2 = new HSSFDataValidation(appList, constraint3); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation2); // // // DVConstraint constraint2 = DVConstraint.createFormulaListConstraint("hidden3"); // CellRangeAddressList catList = new CellRangeAddressList(1, 1, 1, 1); // HSSFDataValidation validation3 = new HSSFDataValidation(catList, constraint2); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation3); // CellRangeAddressList businessList = new CellRangeAddressList(i, i, 3, 3); // DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strBusinessList); // DataValidation dataValidation = new HSSFDataValidation(businessList, dvConstraint1); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation); // CellRangeAddressList appList = new CellRangeAddressList(i, i, 2, 2); // DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strApllication); // DataValidation dataValidation2 = new HSSFDataValidation(appList, dvConstraint2); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation2); // // CellRangeAddressList catList = new CellRangeAddressList(i, i, 1,1); // DVConstraint dvConstraint3 = DVConstraint.createExplicitListConstraint(strCategorymnt); // DataValidation dataValidation3 = new HSSFDataValidation(catList, dvConstraint3); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation3); // } Map<String, String[]> data = new LinkedHashMap<String, String[]>(); String[] stringVal = { "AssetName", "Category", "Application", "BusinessProcess", "InitialValue", "Quantity", "Location", "Location Country", "Location State", "Location City", "Location Contact Name", "Location Email" }; data.put(key.toString(), stringVal); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 12, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); String[] objArr = data.get(key2); short cellnum = 0; for (String obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } } } FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.astrocytes.core.statistics.LayerStatistics.java
License:Open Source License
public boolean saveLayerStatisticsToXls(Map<Integer, List<Point>> layers, List<Point> astrocyteCenters, List<Point> neuronsCenters, File fileToSave) { try {/* w w w.j ava2 s. c om*/ FileOutputStream outputStream = new FileOutputStream(fileToSave); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(CoreConstants.XLS_SPREADSHEET_TITLE); HSSFRow headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue(CoreConstants.XLS_LAYERS_TITLE); for (int i = 0; i < layers.size() - 1; i++) { HSSFRow row = sheet.createRow(i + 1); HSSFCell cell = row.createCell(0); cell.setCellType(CellType.STRING); cell.setCellValue(String.valueOf(i < 1 ? i + 1 : (i == 1 ? "2 - 3" : i + 2))); } if (astrocyteCenters != null) { headerRow.createCell(1).setCellValue(CoreConstants.XLS_ASTROCYTES_TITLE); for (Map.Entry<Integer, Integer> count : count(astrocyteCenters, layers).entrySet()) { HSSFRow row = sheet.getRow(count.getKey() + 1); HSSFCell cell = row.createCell(1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(count.getValue()); } } if (neuronsCenters != null) { int colIdx = astrocyteCenters != null ? 2 : 1; headerRow.createCell(colIdx).setCellValue(CoreConstants.XLS_NEURONS_TITLE); for (Map.Entry<Integer, Integer> count : count(neuronsCenters, layers).entrySet()) { HSSFRow row = sheet.getRow(count.getKey() + 1); HSSFCell cell = row.createCell(astrocyteCenters != null ? 2 : 1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(count.getValue()); } } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); workbook.write(outputStream); outputStream.close(); } catch (IOException e) { e.printStackTrace(); return false; } return true; }
From source file:com.b510.excel.client.HSSFReadWrite.java
License:Apache License
/** * Method main/*w w w .j a v a 2 s. c o m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the cell * values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet see * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second an * output filename (not write), attempts to fully read in the spreadsheet * and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". * If you take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { String[] name = new String[2]; name[0] = "HSSFReadWrite.xlsx"; name[1] = "write"; if (name.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = name[0]; try { if (name.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (name.length == 2) { if (name[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(name[1]); wb.write(stream); stream.close(); } } else if (name.length == 3 && name[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to // string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(name[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }