List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook()
From source file:com.AllenBarr.CallSheetGenerator.Generator.java
License:Open Source License
public int generateSheet(File file, Contributor contrib) { //create workbook file final String fileName = file.toString(); final Workbook wb; if (fileName.endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else if (fileName.endsWith(".xls")) { wb = new HSSFWorkbook(); } else {/*from w w w. j av a 2 s. c om*/ return 1; } //create sheet final Sheet sheet = wb.createSheet("Call Sheet"); final Header header = sheet.getHeader(); header.setCenter("Anderson for Iowa Call Sheet"); //add empty cells final Row[] row = new Row[22 + contrib.getDonationsLength()]; final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()]; for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) { row[i] = sheet.createRow((short) i); for (int j = 0; j < 6; j++) { cell[j][i] = row[i].createCell(j); } } //populate cells with data //column 1 cell[0][0].setCellValue(contrib.getName()); cell[0][3].setCellValue("Sex:"); cell[0][4].setCellValue("Party:"); cell[0][5].setCellValue("Phone #:"); cell[0][6].setCellValue("Home #:"); cell[0][7].setCellValue("Cell #:"); cell[0][8].setCellValue("Work #:"); cell[0][10].setCellValue("Email:"); cell[0][12].setCellValue("Employer:"); cell[0][13].setCellValue("Occupation:"); cell[0][15].setCellValue("Past Contact:"); cell[0][17].setCellValue("Notes:"); cell[0][21].setCellValue("Contribution History:"); //column 2 cell[1][3].setCellValue(contrib.getSex()); cell[1][4].setCellValue(contrib.getParty()); cell[1][5].setCellValue(contrib.getPhone()); cell[1][6].setCellValue(contrib.getHomePhone()); cell[1][7].setCellValue(contrib.getCellPhone()); cell[1][8].setCellValue(contrib.getWorkPhone()); cell[1][9].setCellValue("x" + contrib.getWorkExtension()); cell[1][10].setCellValue(contrib.getEmail()); cell[1][12].setCellValue(contrib.getEmployer()); cell[1][13].setCellValue(contrib.getOccupation()); cell[1][17].setCellValue(contrib.getNotes()); //column 4 cell[3][3].setCellValue("Salutation:"); cell[3][4].setCellValue("Age:"); cell[3][5].setCellValue("Spouse:"); cell[3][7].setCellValue("Address:"); cell[3][10].setCellValue("TARGET:"); //column 5 cell[4][0].setCellValue("VANID:"); cell[4][3].setCellValue(contrib.getSalutation()); cell[4][4].setCellValue(contrib.getAge()); cell[4][5].setCellValue(contrib.getSpouse()); cell[4][7].setCellValue(contrib.getStreetAddress()); cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip()); //column 6 cell[5][0].setCellValue(contrib.getVANID()); //contribution cells for (int i = 0; i < contrib.getDonationsLength(); i++) { cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate()); cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient()); cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount()); } //format cells //Name cell sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); final CellStyle leftBoldUnderline14Style = wb.createCellStyle(); final Font boldUnderline14Font = wb.createFont(); boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldUnderline14Font.setUnderline(Font.U_SINGLE); boldUnderline14Font.setFontHeightInPoints((short) 14); boldUnderline14Font.setFontName("Garamond"); leftBoldUnderline14Style.setFont(boldUnderline14Font); leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT); cell[0][0].setCellStyle(leftBoldUnderline14Style); //field name cells final CellStyle rightBold10Style = wb.createCellStyle(); final Font bold10Font = wb.createFont(); bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD); bold10Font.setFontHeightInPoints((short) 10); bold10Font.setFontName("Garamond"); rightBold10Style.setFont(bold10Font); rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 3; i < 22; i++) { cell[0][i].setCellStyle(rightBold10Style); } sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1)); for (int i = 3; i < 11; i++) { cell[3][i].setCellStyle(rightBold10Style); } cell[4][0].setCellStyle(rightBold10Style); //field content cells final CellStyle left10Style = wb.createCellStyle(); final Font garamond10Font = wb.createFont(); garamond10Font.setFontHeightInPoints((short) 10); garamond10Font.setFontName("Garamond"); left10Style.setFont(garamond10Font); left10Style.setAlignment(CellStyle.ALIGN_LEFT); for (int i = 3; i < 5; i++) { cell[1][i].setCellStyle(left10Style); } //phone number cells final CellStyle phoneStyle = wb.createCellStyle(); phoneStyle.setFont(garamond10Font); phoneStyle.setAlignment(CellStyle.ALIGN_LEFT); final CreationHelper createHelper = wb.getCreationHelper(); phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####")); for (int i = 5; i < 9; i++) { cell[1][i].setCellStyle(phoneStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2)); } cell[1][9].setCellStyle(left10Style); //email through past contact for (int i = 10; i < 16; i++) { cell[1][i].setCellStyle(left10Style); } //notes CellStyle noteStyle = wb.createCellStyle(); noteStyle.cloneStyleFrom(left10Style); noteStyle.setWrapText(true); cell[1][17].setCellStyle(noteStyle); //column E for (int i = 3; i < 11; i++) { cell[4][i].setCellStyle(left10Style); } //VanID Cell final CellStyle right10Style = wb.createCellStyle(); right10Style.setFont(garamond10Font); right10Style.setAlignment(CellStyle.ALIGN_RIGHT); cell[5][0].setCellStyle(right10Style); //Notes cell sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5)); //contribution cells final CellStyle date10Style = wb.createCellStyle(); date10Style.setFont(garamond10Font); date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); date10Style.setBorderBottom(CellStyle.BORDER_THIN); date10Style.setBorderTop(CellStyle.BORDER_THIN); date10Style.setBorderLeft(CellStyle.BORDER_THIN); date10Style.setBorderRight(CellStyle.BORDER_THIN); final CellStyle contributionStyle = wb.createCellStyle(); contributionStyle.cloneStyleFrom(left10Style); contributionStyle.setBorderBottom(CellStyle.BORDER_THIN); contributionStyle.setBorderTop(CellStyle.BORDER_THIN); contributionStyle.setBorderLeft(CellStyle.BORDER_THIN); contributionStyle.setBorderRight(CellStyle.BORDER_THIN); final CellStyle money10Style = wb.createCellStyle(); money10Style.setFont(garamond10Font); money10Style.setDataFormat( createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); money10Style.setBorderBottom(CellStyle.BORDER_THIN); money10Style.setBorderTop(CellStyle.BORDER_THIN); money10Style.setBorderLeft(CellStyle.BORDER_THIN); money10Style.setBorderRight(CellStyle.BORDER_THIN); for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) { cell[0][i].setCellStyle(date10Style); cell[1][i].setCellStyle(contributionStyle); cell[2][i].setCellStyle(contributionStyle); cell[3][i].setCellStyle(contributionStyle); cell[4][i].setCellStyle(contributionStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4)); cell[5][i].setCellStyle(money10Style); } //resize columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); try { FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { return 1; } catch (IOException ex) { return 1; } return 0; }
From source file:com.anphat.list.controller.ListStaffController.java
public File exportFile(List<StaffDTO> lstStaffInfo) { try {//from www . jav a 2s. co 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.asakusafw.testdata.generator.excel.WorkbookGenerator.java
License:Apache License
/** * Creates a new empty workbook for the target version. * @param version the target version// ww w.j a v a 2s .com * @return the created workbook * @throws IOException if failed to create the workbook * @since 0.5.3 */ public static Workbook createEmptyWorkbook(SpreadsheetVersion version) throws IOException { if (version == null) { throw new IllegalArgumentException("version must not be null"); //$NON-NLS-1$ } switch (version) { case EXCEL97: return new HSSFWorkbook(); case EXCEL2007: return new XSSFWorkbook(); default: throw new IOException( MessageFormat.format(Messages.getString("WorkbookGenerator.errorUnsupportedWorkbookVersion"), //$NON-NLS-1$ version)); } }
From source file:com.asakusafw.testdriver.excel.Util.java
License:Apache License
static Workbook createEmptyWorkbookFor(String path) { if (isHssf(path)) { return new HSSFWorkbook(); } else if (isXssf(path)) { return new XSSFWorkbook(); } else {//from ww w . j a va 2s . c om return new HSSFWorkbook(); } }
From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java
License:Apache License
/** * Excel??/*from w w w.j ava 2 s . co m*/ * @param outputDirectory ? * @throws IOException ?????????? * @throws SQLException ?????? */ public void build(File outputDirectory) throws IOException, SQLException { // ?? columnInfos = DatabaseSchema.collectColumns(conn, databaseName, tableName); // ? workbook = new HSSFWorkbook(); // ? configureColumnStyle(); // ???? HSSFSheet inputSheet = createInputDataSheet(Constants.INPUT_DATA_SHEET_NAME); int inputSheetIndex = workbook.getSheetIndex(inputSheet); HSSFSheet outputSheet = workbook.cloneSheet(inputSheetIndex); int outputSheetIndex = workbook.getSheetIndex(outputSheet); workbook.setSheetName(outputSheetIndex, Constants.OUTPUT_DATA_SHEET_NAME); // ??? createTestConditionSheet(Constants.TEST_CONDITION_SHEET_NAME); // ?? String bookName = tableName + ".xls"; File outputFile = new File(outputDirectory, bookName); OutputStream os = new FileOutputStream(outputFile); try { workbook.write(os); } finally { DbUtils.closeQuietly(os); } }
From source file:com.assentisk.util.FilesService.java
public void generatePositionProfileXls(HttpServletRequest req, String Doc_name, List catList) throws SQLException, IOException { Integer key = 1;/* w ww . j a va2s.c om*/ 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 ww w.j a va 2 s .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;// w w w . j a va 2 s .c o 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 .ja v a2 s. co m*/ 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.automaster.autoview.server.servlet.TesteImagemPOI.java
public static void main(String[] args) throws FileNotFoundException, IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); byte data[] = new byte[8000]; try {//ww w . j a v a2s . co m new DataInputStream(new FileInputStream( "D:\\Users\\Adriano\\Documents\\NetBeansProjects\\JRGWT\\web\\imagens\\logo.jpg")).read(data); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } int index = wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG); HSSFClientAnchor ancora = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 5, (short) 10, 10); ancora.setAnchorType(2); sheet.createDrawingPatriarch().createPicture(ancora, index); //Write the Excel file FileOutputStream fileOut = null; fileOut = new FileOutputStream("C://myFile.xls"); wb.write(fileOut); fileOut.close(); }