List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.sigueme.frontend.controller.ExportController.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*from www .j ava2 s.c o m*/ CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); for (Row row : sheet) { for (Cell cell : row) { cell.setCellValue(cell.getStringCellValue().toUpperCase()); cell.setCellStyle(style); } } }
From source file:com.softtek.mdm.web.institution.UserController.java
/** * //from w w w . java2 s . c o m * * @param request * @param response * @return * @throws Exception */ @Log(operateType = "logs.usercontroller.member.type.import", operateContent = "logs.usercontroller.content.member.import") @RequestMapping(value = "/importusers", method = RequestMethod.POST) @ResponseBody public Map<String, Object> importvirmember(MultipartFile files, HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception { Map<String, Object> messages = new HashMap<String, Object>(); // xlsxlsx Integer filetype = 0; if (files.getOriginalFilename().endsWith("xls")) { filetype = 03; } if (files.getOriginalFilename().endsWith("xlsx")) { filetype = 07; } // ?id OrganizationModel organization = (OrganizationModel) session .getAttribute(SessionStatus.SOFTTEK_ORGANIZATION.toString()); Integer orgid = organization.getId(); // ??? ManagerModel managerModel = (ManagerModel) session.getAttribute(SessionStatus.SOFTTEK_MANAGER.toString()); List<Integer> idlist = new ArrayList<Integer>(); // createById Integer managerId = managerModel.getId(); @SuppressWarnings("unchecked") List<StructureModel> list = (List<StructureModel>) session .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString()); NodeModel nodes = treeManager.bulidTreeContainUser(list, organization.getId(), managerModel.getUser()); // ?? if (managerModel.getUser() != null) { @SuppressWarnings("unchecked") List<StructureModel> list1 = (List<StructureModel>) session .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString()); for (StructureModel s : list1) { idlist.add(s.getId()); } } else { idlist = null; } InputStream ins = files.getInputStream(); Workbook wb = null; wb = WorkbookFactory.create(ins); ins.close(); Sheet sheet = null; // 3.Excel if (filetype == 03) { sheet = (HSSFSheet) wb.getSheetAt(0); } if (filetype == 07) { sheet = (XSSFSheet) wb.getSheetAt(0); } // int trLength = sheet.getLastRowNum(); Row rowtest = sheet.getRow(0); Cell celltest1 = rowtest.getCell(0); Cell celltest2 = rowtest.getCell(1); Cell celltest3 = rowtest.getCell(2); if (celltest1 != null && celltest2 != null && celltest3 != null) { celltest1.setCellType(Cell.CELL_TYPE_STRING); celltest2.setCellType(Cell.CELL_TYPE_STRING); celltest3.setCellType(Cell.CELL_TYPE_STRING); if (trLength > 0) { if (celltest1.getStringCellValue() .equals(messageSource.getMessage("web.institution.usercontroller.export.excel.label1", null, LocaleContextHolder.getLocale())) && celltest2.getStringCellValue() .equals(messageSource.getMessage( "web.institution.usercontroller.export.excel.label2", null, LocaleContextHolder.getLocale())) && celltest3.getStringCellValue() .equals(messageSource.getMessage( "web.institution.usercontroller.export.excel.label3", null, LocaleContextHolder.getLocale()))) { List<ExcelInsertUserModel> excelList = new ArrayList<ExcelInsertUserModel>(); List<String> usertlist = (List<String>) userService.findAllMember(orgid); String erromessages = ""; int rownumber = 0; for (int i = 1; i <= trLength; i++) { // Excel Row row = sheet.getRow(i); ExcelInsertUserModel excelInsertUserModel = new ExcelInsertUserModel(); for (int j = 0; j <= 10; j++) { // Excel? if (row != null) { Cell newcell = row.getCell(j); String cell = ""; if (newcell != null) { newcell.setCellType(Cell.CELL_TYPE_STRING); if (StringUtils.isNotBlank(StringUtils.trim(newcell.getStringCellValue()))) { // cell = // StringUtils.trim(newcell.getStringCellValue()); cell = newcell.getStringCellValue().replaceAll(" ", ""); } } if (j == 0) { // ??? if (StringUtils.isNotBlank(cell)) { String groupname[] = cell.split("/"); if (null != groupname) { /* */ int deep = 0; List<NodeModel> nodeList = null; StructureModel structure = null; NodeModel temp = nodes; do { if (temp.getTags().getParent() == null) { nodeList = temp.getNodes(); for (NodeModel n : nodeList) { if (n.getTags().getName().equals(groupname[deep])) { deep++; temp = n; break; } } if (deep == 0) { break; } } else { int deep1 = deep; for (NodeModel n : nodeList) { if (n.getTags().getName().equals(groupname[deep])) { deep++; temp = n; break; } } if (deep1 == deep) { break; } } if (deep == groupname.length) { structure = temp.getTags(); break; } } while ((nodeList = temp.getNodes()) != null); if (structure == null) { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.nullgroup", args, LocaleContextHolder.getLocale()); } else { if (idlist != null) { int idlistsize1 = idlist.size(); idlist.remove(structure.getId()); int idlistsize2 = idlist.size(); if (idlistsize1 != idlistsize2) { excelInsertUserModel.setGroup_id(structure.getId()); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.group2", args, LocaleContextHolder.getLocale()); } idlist.add(structure.getId()); } else { excelInsertUserModel.setGroup_id(structure.getId()); // excelList.add(excelInsertUserModel); } } } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.group3", args, LocaleContextHolder.getLocale()); } } // ?? if (j == 1) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 20) { boolean reuser = usertlist.remove(cell); if (reuser == true) { excelInsertUserModel.setUser_name(cell); rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username", args, LocaleContextHolder.getLocale()); usertlist.add(cell); } else { excelInsertUserModel.setUser_name(cell); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username1", args, LocaleContextHolder.getLocale()); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.username2", args, LocaleContextHolder.getLocale()); } } // ?? if (j == 2) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 20) { excelInsertUserModel.setReal_name(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.realname", args, LocaleContextHolder.getLocale()); } } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.realname1", args, LocaleContextHolder.getLocale()); } } // if (j == 3) { if (StringUtils.isNotBlank(cell)) { String regex = "^\\s*\\w+(?:\\.{0,1}[\\w-]+)*@[a-zA-Z0-9]+(?:[-.][a-zA-Z0-9]+)*\\.[a-zA-Z]+\\s*$"; if (cell.matches(regex) == true) { excelInsertUserModel.setEmail(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.email", args, LocaleContextHolder.getLocale()); } } } // if (j == 4) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setMark(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.mark", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 5) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setPhone((cell)); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.phone", args, LocaleContextHolder.getLocale()); } } } // if (j == 6) { if (StringUtils.isNotBlank(cell)) { if (cell.equals( messageSource.getMessage("web.institution.usercontroller.sex.woman", null, LocaleContextHolder.getLocale()))) { excelInsertUserModel.setGender(0); } else { excelInsertUserModel.setGender(1); } } else { excelInsertUserModel.setGender(1); } } // ?? if (j == 7) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setSign(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.sign", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 8) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setAddress(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.address", args, LocaleContextHolder.getLocale()); } } } // ? if (j == 9) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setOffice_phone(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.office_phone", args, LocaleContextHolder.getLocale()); } } } // ?? if (j == 10) { if (StringUtils.isNotBlank(cell)) { if (cell.length() < 30) { excelInsertUserModel.setPosition(cell); } else { rownumber = i + 1; Object[] args = { rownumber }; erromessages = erromessages + messageSource.getMessage( "web.institution.usercontroller.excel.erro.position", args, LocaleContextHolder.getLocale()); } } } } } excelList.add(excelInsertUserModel); } // ?? ???? List<String> yqs = new ArrayList<String>(); Set<String> testreusername = new HashSet<String>(yqs); for (int k = 0; k < excelList.size(); k++) { if (excelList.get(k).getUser_name() != null) { testreusername.add(excelList.get(k).getUser_name()); yqs.add(excelList.get(k).getUser_name()); } } if (testreusername.size() < yqs.size()) { erromessages = messageSource.getMessage( "web.institution.usercontroller.excel.erro.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", erromessages); } else { // ? if (erromessages.length() > 0) { erromessages.substring(0, erromessages.length() - 1); messages.put("messages", erromessages); } else { Md5PasswordEncoder md5PasswordEncoder = new Md5PasswordEncoder(); String password = md5PasswordEncoder.encodePassword("123456", null); if (excelList.size() > 0) { for (int q = 0; q < excelList.size(); q++) { Integer policyId = structureService .queryPolicyIdById(excelList.get(q).getGroup_id()); excelList.get(q).setPolicy_id(policyId); excelList.get(q).setOrgid(orgid); excelList.get(q).setPassword(password); excelList.get(q).setCreate_by(managerId); } userService.importUsers(excelList); messages.put("success", "success"); } } } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erromodel.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erro.nullmessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } } else { String nullmessages = messageSource.getMessage( "web.institution.usercontroller.excel.erromodel.erromessages", null, LocaleContextHolder.getLocale()); messages.put("messages", nullmessages); } return messages; }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private List<Cell> findCell(String key) { List<Cell> listCell = new ArrayList<Cell>(); Integer sheetNumber = wb.getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = wb.getSheetAt(i); for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(key)) { listCell.add(cell); }/*from w ww. j a v a 2 s . c o m*/ } } } } return listCell; }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void replaceStringCell(String key, String value) { List<Cell> listCells = this.findCell(key); for (Cell cell : listCells) { String strReplace = cell.getStringCellValue().replace(key, value); cell.setCellValue(strReplace);/* w w w . jav a2 s .c o m*/ } }
From source file:com.stam.excellatin.ExcelLatin.java
public static void main(String[] args) { List<String> options = new ArrayList<>(); int startIndex = 0; for (String arg : args) { if (validOptions.contains(arg)) { options.add(arg);//w w w . j a v a2 s.c o m startIndex++; } } if (args[0].equals("-h") || args.length < 3) { System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames..."); System.out.println("options:"); System.out.println("\t-L\tto Latin (default)"); System.out.println("\t-G\tto Greek"); System.out.println("\t-d\tdon't deaccent"); System.out.println("\t-h\thelp"); } else { boolean greekToLatin = false; boolean latinToGreek = false; Transliterator transliterator = null; if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) { transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN"); System.out.println("\nTransliterating Greek to Latin"); greekToLatin = true; } else if (options.contains("-G")) { transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN"); System.out.println("\nTransliterating Latin to Greek"); latinToGreek = true; } if (transliterator == null) { System.out.println("Not a valid option for the transliteration language"); return; } boolean deAccent = true; if (options.contains("-d")) { deAccent = false; System.out.println("Will not deaccent"); } String fileNameIn = args[startIndex]; String fileNameOut = args[startIndex + 1]; List<String> columnNames = new ArrayList<>(); System.out.println("\nColumns to transliterate\n---------------------------"); for (int i = startIndex + 2; i < args.length; i++) { columnNames.add(args[i]); System.out.println(args[i]); } System.out.println("\n"); try { File file = new File(fileNameIn); if (!file.exists()) { System.out.println("The file " + fileNameIn + " was not found"); return; } Map<String, String> mapTransformations = new HashMap<>(); Scanner sc = new Scanner(new FileReader("map.txt")); while (sc.hasNextLine()) { String greekEntry = sc.next(); String latinEntry = sc.next(); if (greekToLatin) { mapTransformations.put(greekEntry, latinEntry); } else if (latinToGreek) { mapTransformations.put(latinEntry, greekEntry); } } DataFormatter formatter = new DataFormatter(); Workbook wb = WorkbookFactory.create(file); Workbook newWb = null; if (wb instanceof HSSFWorkbook) { newWb = new HSSFWorkbook(); } else if (wb instanceof XSSFWorkbook) { newWb = new XSSFWorkbook(); } FileOutputStream fileOut = new FileOutputStream(fileNameOut); if (newWb != null) { Sheet sheetOut = newWb.createSheet(); Sheet sheet = wb.getSheetAt(0); List<Integer> idxs = new ArrayList<>(); Row row = sheet.getRow(0); for (Cell cell : row) { String cellVal = formatter.formatCellValue(cell); if (cellVal == null || cellVal.trim().equals("")) { break; } if (columnNames.contains(cell.getStringCellValue())) { idxs.add(cell.getColumnIndex()); } } for (Row rowIn : sheet) { Row rowOut = sheetOut.createRow(rowIn.getRowNum()); if (rowIn.getRowNum() == 0) { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cell.getStringCellValue()); } } else { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); String cellVal = formatter.formatCellValue(cell); String cellNewVal = cellVal; if (idxs.contains(cell.getColumnIndex()) && cellVal != null) { if (mapTransformations.containsKey(cellVal)) { cellNewVal = mapTransformations.get(cellVal); } else { if (deAccent) { cellNewVal = deAccent(transliterator.transform(cellVal)); } else { cellNewVal = transliterator.transform(cellVal); } } } Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cellNewVal); } } } System.out.println("Finished!"); newWb.write(fileOut); fileOut.close(); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex); } } }
From source file:com.streamsets.pipeline.lib.parser.excel.Cells.java
License:Apache License
static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException { CellType cellType = cell.getCellTypeEnum(); // set the cellType of a formula cell to its cached formula result type in order to process it as its result type boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA); if (isFormula) { cellType = cell.getCachedFormulaResultTypeEnum(); }/* w ww .j a v a 2 s. c o m*/ switch (cellType) { case STRING: return Field.create(cell.getStringCellValue()); case NUMERIC: Double rawValue = cell.getNumericCellValue(); // resolves formulas automatically and gets value without cell formatting String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString() : dataFormatter.formatCellValue(cell); boolean numericallyEquivalent = false; try { numericallyEquivalent = Double.parseDouble(displayValue) == rawValue; } catch (NumberFormatException e) { } if (DateUtil.isCellDateFormatted(cell)) { // It's a date, not a number java.util.Date dt = cell.getDateCellValue(); // if raw number is < 1 then it's a time component only, otherwise date. return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt); } // some machinations to handle integer values going in without decimal vs. with .0 for rawValue return Field .create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue)); case BOOLEAN: return Field.create(cell.getBooleanCellValue()); case BLANK: return Field.create(""); default: throw new ExcelUnsupportedCellTypeException(cell, cellType); } }
From source file:com.surenpi.autotest.suite.parser.ExcelSuiteParser.java
License:Apache License
/** * @param sheet//from ww w . j a v a2 s .c om * @param suitePage * @return */ private boolean sheetParse(Sheet sheet, SuitePage suitePage) { if (sheet.getSheetName().equals("SuiteConfig")) { for (int i = 0; i < maxRows; i++) { Row row = sheet.getRow(i); if (row == null) { break; } Cell keyCell = row.getCell(0); Cell valCell = row.getCell(1); if (keyCell == null || valCell == null) { continue; } String keyCellVal = keyCell.getStringCellValue(); String valCellVal = valCell.getStringCellValue(); if (keyCellVal.equals("PageConfig")) { suite.setXmlConfPath(valCellVal); } else if (keyCellVal.equals("PagePackage")) { suite.setPagePackage(valCellVal); } else if (keyCellVal.equals("AfterSleep")) { try { suite.setAfterSleep(Long.parseLong(valCellVal)); } catch (NumberFormatException e) { e.printStackTrace(); } } } return false; } suitePage.setPage(sheet.getSheetName()); suitePage.setActionList(new ArrayList<SuiteAction>()); for (int i = 0; i < maxRows; i++) { Row row = sheet.getRow(i); if (row == null) { break; } SuiteAction suiteAction = new SuiteAction(); suiteAction.setRepeat(1); rowParse(row, suiteAction); suitePage.getActionList().add(suiteAction); } return true; }
From source file:com.surenpi.autotest.suite.parser.ExcelSuiteParser.java
License:Apache License
/** * @param row/* ww w .jav a2 s . com*/ * @param suiteAction */ private void rowParse(Row row, SuiteAction suiteAction) { Cell nameCell = row.getCell(0); Cell actionCell = row.getCell(1); if (nameCell == null || actionCell == null) { return; } suiteAction.setField(nameCell.getStringCellValue()); suiteAction.setName(actionCell.getStringCellValue()); }
From source file:com.svi.uzabase.logic.ExtractData.java
private List<String> extractCompanyNames() { List<String> companyList = new ArrayList<>(); try {/*from w ww.ja v a 2 s . c om*/ FileInputStream inputStream = new FileInputStream(new File(COMPANY_EXCEL_PATH)); Workbook workbook = new HSSFWorkbook(inputStream); Row row; Cell cell; Sheet sheet; sheet = workbook.getSheetAt(0); for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) { cell = row.getCell(colIndex); cell.setCellType(Cell.CELL_TYPE_STRING); if (colIndex == 1) { companyList.add(cell.getStringCellValue().toUpperCase()); } } } //Closes opened documents inputStream.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex); } return companyList; }
From source file:com.taobao.tddl.common.SQLPreParserTest.java
License:Open Source License
public static void main1(String[] args) throws IOException { String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/test.xls"; Workbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(fileName))); Sheet sheet = wb.getSheetAt(0);/*w w w . ja v a 2s . c o m*/ for (Row row : sheet) { Cell cell = row.getCell(2); System.out.println(cell.getStringCellValue()); } }