List of usage examples for org.apache.poi.ss.usermodel Sheet getWorkbook
Workbook getWorkbook();
From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java
License:Apache License
/** * ????//from w w w .j a v a 2 s.co m * @param sheet * @param rowIndex * @param style * @return */ public boolean setRowStyle(int rowIndex, CellStyle style) { Sheet sheet = excelSheet.sheet; if (sheet != null) { Row row = sheet.getRow(rowIndex); if (row != null) { short firstColumnIndex = row.getFirstCellNum(); short lastColumnIndex = row.getLastCellNum(); for (short colunmIndex = firstColumnIndex; colunmIndex < lastColumnIndex; colunmIndex++) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Cell cell = row.getCell(colunmIndex); if (cell != null) { cellStyle.cloneStyleFrom(cell.getCellStyle()); cellStyle.cloneStyleFrom(style); cell.setCellStyle(cellStyle); } } } } return true; }
From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetEditor.java
License:Apache License
/** * ???// ww w . j a v a 2s. c om * @param sheet * @param rowIndex * @param columnIndex * @param style * @return */ public boolean setCellStyle(int rowIndex, int columnIndex, CellStyle style) { Sheet sheet = excelSheet.sheet; if (sheet == null) return false; if (rowIndex < 0 || columnIndex <= 0) return false; Cell cell = sheet.getRow(rowIndex).getCell(columnIndex); if (cell == null) return false; CellStyle newCellStyle = sheet.getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(cell.getCellStyle()); newCellStyle.cloneStyleFrom(style); cell.setCellStyle(newCellStyle); return true; }
From source file:com.jeans.iservlet.action.asset.AssetExportAction.java
private void appendRow(Sheet sheet, Asset asset, int rowNumber) { // // w w w.ja va 2s.c o m DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMM)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMM")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); // 20 Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = null; if (asset instanceof Hardware) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getCode()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getFinancialCode()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getSn()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getConfiguration()); cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty())); cell = row.createCell(15, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getLocation()); cell = row.createCell(16, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getIp()); cell = row.createCell(17, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance())); cell = row.createCell(18, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Employee owner = ((Hardware) asset).getOwner(); if (null != owner) { cell.setCellValue(owner.getName()); } cell = row.createCell(19, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } else if (asset instanceof Software) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType())); cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Software) asset).getLicense()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date et = ((Software) asset).getExpiredTime(); if (null != et) { cell.setCellValue(et); } cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } }
From source file:com.jeans.iservlet.action.asset.AssetExportAction.java
private void generateSheetHeader(Sheet sheet, boolean hardware) { // //from ww w .j a v a 2 s .co m // ?10?? Font font = sheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; if (hardware) { for (int i = 0; i < 20; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(HARDWARE_HEADERS[i]); sheet.setColumnWidth(i, HARDWARE_HEADERS_WIDTH[i] * 256); } } else { for (int i = 0; i < 14; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(SOFTWARE_HEADERS[i]); sheet.setColumnWidth(i, SOFTWARE_HEADERS_WIDTH[i] * 256); } } }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
private void generateSheet(Sheet sheet, byte type, Company company) { // sheet/*from w w w .j ava 2s . c om*/ if (type == AssetConstants.HARDWARE_ASSET) { // generateSheetHeader(sheet, true); } else if (type == AssetConstants.SOFTWARE_ASSET) { // generateSheetHeader(sheet, false); } else if (type >= AssetConstants.NETWORK_EQUIPMENT && type <= AssetConstants.OTHER_EQUIPMENT) { // ? generateSheetHeader(sheet, true); } else if (type >= AssetConstants.OPERATING_SYSTEM_SOFTWARE && type <= AssetConstants.OTHER_SOFTWARE) { // ? generateSheetHeader(sheet, false); } List<Asset> assets = astService.listAssets(company, type); DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMM)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMM")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); int rowNumber = 1; for (Asset asset : assets) { appendRow(sheet, asset, rowNumber++, cellStyleString, cellStyleDate, cellStyleQuantity, cellStyleCost); } }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ?????//from w ww. j a va 2 s. c o m * * @param storedOnly * ??? * @return * @throws IOException */ @RequestMapping(method = RequestMethod.POST, value = "/accessories") public ResponseEntity<byte[]> exportAccessories(@RequestParam boolean storedOnly) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); Date n = new Date(); String today = (new SimpleDateFormat("yyyyMMdd")).format(n); String now = (new SimpleDateFormat("yyyy-MM-dd HHmmss")).format(n); Workbook wb = new XSSFWorkbook(); fn.append(" - ???(").append(today).append(").xlsx"); Sheet sheet = wb.createSheet(now); // // ?10?? Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; for (int i = 0; i < 7; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(ACS_HEADERS[i]); sheet.setColumnWidth(i, ACS_HEADERS_WIDTH[i] * 256); } List<Accessory> acs = acsService.listAccessories(getCurrentCompany(), storedOnly); Collections.sort(acs, new Comparator<Accessory>() { @Override public int compare(Accessory o1, Accessory o2) { int ret = o1.getType().compareTo(o2.getType()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getBrand(), o2.getBrand()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getModel(), o2.getModel()); } } } return ret; } }); // DataFormat df = wb.createDataFormat(); // ?10? Font dFont = wb.createFont(); dFont.setFontName(""); dFont.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = wb.createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(dFont); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(dFont); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); int rowNumber = 1; for (Accessory ac : acs) { // 20 Row dRow = sheet.createRow(rowNumber); dRow.setHeightInPoints(20); Cell dCell = null; dCell = dRow.createCell(0, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getType().getTitle()); dCell = dRow.createCell(1, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getName()); dCell = dRow.createCell(2, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getBrand()); dCell = dRow.createCell(3, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getModel()); dCell = dRow.createCell(4, Cell.CELL_TYPE_NUMERIC); dCell.setCellStyle(cellStyleQuantity); dCell.setCellValue(null == ac.getStorage() ? 0 : ac.getStorage().getQuantity()); dCell = dRow.createCell(5, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getUnit()); dCell = dRow.createCell(6, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getDescription()); rowNumber++; } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); return null; }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ???/*from w w w. j a v a2 s .c o m*/ * * @return * @throws IOException */ @RequestMapping(method = RequestMethod.POST, value = "/systems") public ResponseEntity<byte[]> exportITSystems() throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ??("); Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("?"); // ? // // ?10?? Font tFont = sheet.getWorkbook().createFont(); tFont.setBoldweight(Font.BOLDWEIGHT_BOLD); tFont.setFontName(""); tFont.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyleTitle = sheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleTitle.setFont(tFont); cellStyleTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleTitle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; for (int i = 0; i < ITSYSTEM_HEADERS.length; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleTitle); cell.setCellValue(ITSYSTEM_HEADERS[i]); sheet.setColumnWidth(i, ITSYSTEM_HEADERS_WIDTH[i] * 256); } // ?????->?->???? List<ITSystem> systems = new ArrayList<ITSystem>(systService.listSystems(getCurrentCompany(), null, null)); Collections.sort(systems, new Comparator<ITSystem>() { @Override public int compare(ITSystem o1, ITSystem o2) { int ret = o1.getType().ordinal() - o2.getType().ordinal(); if (ret == 0) { ret = Long.compare(o1.getOwner().getId(), o2.getOwner().getId()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName()); } } return ret; } }); // ?? DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMMdd)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMMdd")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_CENTER); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("0")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); // sheet int rowNumber = 1; for (ITSystem system : systems) { // 20 row = sheet.createRow(rowNumber); row.setHeightInPoints(20); // ? Set<SystemBranch> branches = system.getBranches(); SystemBranch localBranch = null; // ?? long localId = 0; if (getCurrentCompany().getLevel() == Company.BRANCH) { localId = getCurrentCompany().getSuperior().getId(); } else { localId = getCurrentCompany().getId(); } BigDecimal cost = new BigDecimal("0.0"); // for (SystemBranch branch : branches) { cost.add(branch.getCost()); if (branch.getCompany().getId() == localId) { localBranch = branch; } } boolean branched = (localBranch != null); // ? boolean owned = system.getOwner().getId() == getCurrentCompany().getId(); // ????? // // cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getType().getTitle()); // ?? cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getName()); // cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getAlias()); // /? cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getModelOrVersion()); // cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getBrief()); // ? cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(ITSYSTEM_sLevel[system.getSecurityLevel()]); // ??? cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getSecurityCode()); // ? cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getUsersBrief()); // cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(null == system.getProvider() ? "" : system.getProvider().getAlias()); // ? cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getOwner().getAlias()); // cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getScope().getTitle(system.getOwner().getLevel())); // cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getDeploy().getTitle()); // cell = row.createCell(12, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(branched ? "" : ""); if (branched) { // ?() cell = row.createCell(13, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(localBranch.getCost().doubleValue()); // ? cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(localBranch.getStage().getTitle()); // ? cell = row.createCell(15, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date ct = localBranch.getConstructedTime(); if (null != ct) { cell.setCellValue(ct); } // ? cell = row.createCell(16, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date at = localBranch.getAbandonedTime(); if (null != at) { cell.setCellValue(at); } } // ?? cell = row.createCell(17, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(system.getFreeMaintainMonths()); if (owned) { // cell = row.createCell(18, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(branches.size()); // ?) cell = row.createCell(19, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(cost.doubleValue()); // ?? cell = row.createCell(20, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getStage().getTitle()); // ? cell = row.createCell(21, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date ct = system.getConstructedTime(); if (null != ct) { cell.setCellValue(ct); } // ? cell = row.createCell(22, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date at = system.getAbandonedTime(); if (null != at) { cell.setCellValue(at); } } rowNumber++; } fn.append((new SimpleDateFormat("yyyyMMdd")).format(new Date())).append(").xlsx"); String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); return null; }
From source file:com.jkoolcloud.tnt4j.streams.parsers.ActivityExcelSheetParser.java
License:Apache License
/** * Gets field raw data value resolved by locator. * * @param locator//w ww . j a v a 2s . c o m * activity field locator * @param cData * MS Excel document sheet representing activity object data fields * @param formattingNeeded * flag to set if value formatting is not needed * @return raw value resolved by locator, or {@code null} if value is not resolved * * @throws ParseException * if exception occurs while resolving raw data value */ @Override protected Object resolveLocatorValue(ActivityFieldLocator locator, ActivityContext cData, AtomicBoolean formattingNeeded) throws ParseException { Object val = null; String locStr = locator.getLocator(); Sheet sheet = cData.getData(); if (StringUtils.isNotEmpty(locStr)) { CellReference ref = new CellReference(locStr); boolean cellFound = false; if (ref.getRow() < 0 || ref.getCol() < 0) { throw new ParseException( StreamsResources.getStringFormatted(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME, "ActivityExcelRowParser.unresolved.cell.reference", locStr), sheet.getWorkbook().getSheetIndex(sheet)); } Row row = sheet.getRow(ref.getRow()); if (row != null) { Cell cell = row.getCell(ref.getCol()); if (cell != null) { val = getCellValue(cell); cellFound = true; } else { val = row; } } logger().log(OpLevel.TRACE, StreamsResources.getString(MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME, "ActivityExcelRowParser.resolved.cell.value"), locStr, sheet.getWorkbook().getMissingCellPolicy(), toString(val)); } return val; }
From source file:com.oleke.facebookcrawler.App.java
License:Apache License
public static void main(String[] args) { ExcelAPI xcel = new ExcelAPI(); Sheet stats = xcel.initExcel(statFile, "Facebook Statistics"); Sheet posts = xcel.initExcel(postFile, "Facebook Posts"); if (stats.getPhysicalNumberOfRows() == 0) { xcel.F_Stats_Header(stats);/* ww w. ja v a 2 s.c o m*/ } if (posts.getPhysicalNumberOfRows() == 0) { xcel.F_Posts_Header(posts); } FcbkCrawler fb = new FcbkCrawler(accessToken, xcel.readFromFile(dbFile), xcel.readFromFile(crawledFile)); fb.getFriends("me"); System.out.println("Building Crawler Database..."); while (fb.getTGroup().activeCount() > 0) { } System.out.println("Finished"); xcel.writeToFile(dbFile, fb.getFriendsList()); fb.getFriendsList().remove(0); //fb.getCrawled().remove(0); System.out.println("Crawling User Data in Progress..."); int i = 0; for (String id : fb.getFriendsList()) { if (!fb.getCrawled().contains(id)) { System.out.println(i + 1 + " Crawling user " + id); try { User userDetails = fb.getUserDetails(id); int age = fb.getAge(userDetails.getBirthdayAsDate()); String ageRange = fb.getAgeRange(age); String educationLevel = fb.getEducationLevel(userDetails.getEducation()); int noAlbums = fb.getNoAlbums(id); int noGroups = fb.getNoGroups(id); int noPages = fb.getNoPages(id); int noPictures = fb.getNoPictures(id); int noPosts = fb.getNoPosts(id); int noTaggedPosts = fb.getNoTaggedPosts(id); List<Post> user_posts = fb.getPosts(id); Row u_stats = xcel.createRow(stats); xcel.addCell(u_stats, ExcelAPI.about, userDetails.getAbout()); xcel.addCell(u_stats, ExcelAPI.age_range, ageRange); xcel.addCell(u_stats, ExcelAPI.album_no, Integer.toString(noAlbums)); xcel.addCell(u_stats, ExcelAPI.bio, userDetails.getBio()); xcel.addCell(u_stats, ExcelAPI.education_level, educationLevel); xcel.addCell(u_stats, ExcelAPI.name, userDetails.getName()); xcel.addCell(u_stats, ExcelAPI.no_of_groups, Integer.toString(noGroups)); xcel.addCell(u_stats, ExcelAPI.no_pages, Integer.toString(noPages)); xcel.addCell(u_stats, ExcelAPI.no_pictures, Integer.toString(noPictures)); xcel.addCell(u_stats, ExcelAPI.no_posts, Integer.toString(noPosts)); xcel.addCell(u_stats, ExcelAPI.no_tagged_posts, Integer.toString(noTaggedPosts)); xcel.addCell(u_stats, ExcelAPI.political_view, userDetails.getPolitical()); xcel.addCell(u_stats, ExcelAPI.quotes, userDetails.getQuotes()); xcel.addCell(u_stats, ExcelAPI.relationship_status, userDetails.getRelationshipStatus()); xcel.addCell(u_stats, ExcelAPI.religion, userDetails.getReligion()); xcel.addCell(u_stats, ExcelAPI.s_gender, userDetails.getGender()); xcel.addCell(u_stats, ExcelAPI.s_id, userDetails.getId()); for (Post p : user_posts) { Row u_posts = xcel.createRow(posts); xcel.addCell(u_posts, ExcelAPI.p_id, userDetails.getId()); xcel.addCell(u_posts, ExcelAPI.post, p.getMessage()); if (!"status".equals(p.getType())) { xcel.addCell(u_posts, ExcelAPI.post, p.getDescription()); } xcel.addCell(u_posts, ExcelAPI.post_type, p.getType()); xcel.addCell(u_posts, ExcelAPI.p_gender, userDetails.getGender()); } fb.getCrawled().add(id); } catch (Exception ex) { } i++; } } System.out.println("Writing to File"); xcel.commitChanges(statFile, stats.getWorkbook()); xcel.commitChanges(postFile, posts.getWorkbook()); xcel.writeToFile(crawledFile, fb.getCrawled()); System.out.println("Completed!"); }
From source file:com.oleke.facebookcrawler.ExcelAPI.java
License:Apache License
/** * This method creates the headers for the FaceBook statistics file * * @param sh the sheet to write on//from w w w . j a v a2 s . c om */ public void F_Stats_Header(Sheet sh) { Row r = createRow(sh); addCell(r, s_id, "id"); addCell(r, about, "about"); addCell(r, bio, "bio"); addCell(r, education_level, "education_level"); addCell(r, name, "name"); addCell(r, age_range, "age_range"); addCell(r, political_view, "political_view"); addCell(r, quotes, "quotes"); addCell(r, religion, "religion"); addCell(r, relationship_status, "relationship_status"); addCell(r, album_no, "album_no"); addCell(r, no_pictures, "no_pictures"); addCell(r, no_of_groups, "no_of_groups"); addCell(r, no_posts, "no_posts"); addCell(r, no_tagged_posts, "no_tagged_posts"); addCell(r, no_pages, "no_pages"); addCell(r, s_gender, "gender"); commitChanges("stats", sh.getWorkbook()); }