List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
/** * ?Excel?MapList?Excel??Java.// w w w .jav a 2 s . c om * * @param file * @return * @throws IOException */ public static List<Map<String, Object>> parseHSSFMapList(MultipartFile file) throws IOException {// POIFSFileSystem poiFs = new POIFSFileSystem(file.getInputStream()); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum(); HSSFRow titleRow = sheet.getRow(0); int colNum = titleRow.getLastCellNum(); //?17 List<String> titleList = new ArrayList<String>(); for (int i = 0; i < colNum; i++) { String title = titleRow.getCell(i).getStringCellValue(); titleList.add(trimTitle(title)); } List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>(); for (int i = 1; i <= rowNum; i++) { HSSFRow row = sheet.getRow(i); Map<String, Object> map = new LinkedHashMap<String, Object>(); for (int j = 0; j < colNum; j++) { HSSFCell cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: double d = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); //? if (HSSFDateUtil.isCellDateFormatted(cell) || (style != null && (style.getDataFormat() == 57 || style.getDataFormat() == 58))) { map.put(titleList.get(j), HSSFDateUtil.getJavaDate(d)); } else { map.put(titleList.get(j), d); } break; default: cell.setCellType(HSSFCell.CELL_TYPE_STRING); map.put(titleList.get(j), row.getCell(j).getStringCellValue()); break; } } else { map.put(titleList.get(j), null); } } mapList.add(map); } return mapList; }
From source file:com.frameworkset.platform.util.POIExcelUtil.java
License:Open Source License
/** * ?/*from w w w .j av a 2s. co m*/ * * @param uploadFileName * @return * 2015112 */ public static List<String> getTitlesList(InputStream inputStream) throws Exception { // ?excel2003 POIFSFileSystem poiFs = new POIFSFileSystem(inputStream); HSSFWorkbook wb = new HSSFWorkbook(poiFs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow titleRow = sheet.getRow(0); int colNum = titleRow.getLastCellNum(); // ? List<String> titleList = new ArrayList<String>(); for (int i = 0; i < colNum; i++) { String title = titleRow.getCell(i).getStringCellValue(); titleList.add(trimTitle(title)); } return titleList; }
From source file:com.fufang.bi.controllers.ChainReportController.java
/*** * ?excel// w ww. j a va2 s. com * @param orderType * @param list * @param inputStream * @return */ public HSSFWorkbook createUploadSplitExcel(List<?> list, InputStream inputStream, Integer select, Object obj) { try { HSSFWorkbook workbook = null; workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = null;// sheet = workbook.getSheetAt(0);// HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setLocked(false); HSSFRow row = null; HSSFCell cell = null; if (list != null && list.size() > 0) { for (int i = 0, size = list.size(); i < size; i++) { HSSFRow rows = null; rows = sheet.createRow(i + 5); if (select == 3) { row = sheet.createRow(i + 3); } else { row = sheet.createRow(i + 2); } if (select == 1) { StorageTotal data = (StorageTotal) list.get(i); Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle); Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle); Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle); Converter.setCellText(row, cell, 3, Converter.toBlank(data.getQckc()), cellStyle); Converter.setCellText(row, cell, 4, Converter.toBlank(data.getQckcje()), cellStyle); Converter.setCellText(row, cell, 5, Converter.toBlank(data.getQjrk()), cellStyle); Converter.setCellText(row, cell, 6, Converter.toBlank(data.getQjrkje()), cellStyle); Converter.setCellText(row, cell, 7, Converter.toBlank(data.getQjck()), cellStyle); Converter.setCellText(row, cell, 8, Converter.toBlank(data.getQjckje()), cellStyle); Converter.setCellText(row, cell, 9, Converter.toBlank(data.getQmkc()), cellStyle); Converter.setCellText(row, cell, 10, Converter.toBlank(data.getQmkcje()), cellStyle); } if (select == 2) { StorageMilde data = (StorageMilde) list.get(i); Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle); Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle); Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle); Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle); Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle); Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle); Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle); Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle); Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle); Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle); Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle); Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle); Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle); Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle); Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle); Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle); Converter.setCellText(row, cell, 16, Converter.toBlank(data.getQjrk()), cellStyle); Converter.setCellText(row, cell, 17, Converter.toBlank(data.getQjrkje()), cellStyle); Converter.setCellText(row, cell, 18, Converter.toBlank(data.getQjck()), cellStyle); Converter.setCellText(row, cell, 19, Converter.toBlank(data.getQjckje()), cellStyle); Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQmkc()), cellStyle); Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQmkcje()), cellStyle); } if (select == 3) { StorageDetail data = (StorageDetail) list.get(i); Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle); Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle); Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle); Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle); Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle); Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle); Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle); Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle); Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle); Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle); Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle); Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle); Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle); Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle); Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle); Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle); Converter.setCellText(row, cell, 16, Converter.toBlank(data.getCg()), cellStyle); Converter.setCellText(row, cell, 17, Converter.toBlank(data.getCgje()), cellStyle); Converter.setCellText(row, cell, 18, Converter.toBlank(data.getPyrk()), cellStyle); Converter.setCellText(row, cell, 19, Converter.toBlank(data.getPyrkje()), cellStyle); Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQtrk()), cellStyle); Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQtrkje()), cellStyle); Converter.setCellText(row, cell, 22, Converter.toBlank(data.getClrk()), cellStyle); Converter.setCellText(row, cell, 23, Converter.toBlank(data.getClrkje()), cellStyle); Converter.setCellText(row, cell, 24, Converter.toBlank(data.getPsrk()), cellStyle); Converter.setCellText(row, cell, 25, Converter.toBlank(data.getPsrkje()), cellStyle); Converter.setCellText(row, cell, 26, Converter.toBlank(data.getQcrk()), cellStyle); Converter.setCellText(row, cell, 27, Converter.toBlank(data.getQcrkje()), cellStyle); Converter.setCellText(row, cell, 28, Converter.toBlank(data.getXsck()), cellStyle); Converter.setCellText(row, cell, 29, Converter.toBlank(data.getXsckje()), cellStyle); Converter.setCellText(row, cell, 30, Converter.toBlank(data.getPkck()), cellStyle); Converter.setCellText(row, cell, 31, Converter.toBlank(data.getPkckje()), cellStyle); Converter.setCellText(row, cell, 32, Converter.toBlank(data.getQtck()), cellStyle); Converter.setCellText(row, cell, 33, Converter.toBlank(data.getQtckje()), cellStyle); Converter.setCellText(row, cell, 34, Converter.toBlank(data.getClck()), cellStyle); Converter.setCellText(row, cell, 35, Converter.toBlank(data.getClckje()), cellStyle); Converter.setCellText(row, cell, 36, Converter.toBlank(data.getPsck()), cellStyle); Converter.setCellText(row, cell, 37, Converter.toBlank(data.getPsckje()), cellStyle); Converter.setCellText(row, cell, 38, Converter.toBlank(data.getQdck()), cellStyle); Converter.setCellText(row, cell, 39, Converter.toBlank(data.getQdckje()), cellStyle); Converter.setCellText(row, cell, 40, Converter.toBlank(data.getXhck()), cellStyle); Converter.setCellText(row, cell, 41, Converter.toBlank(data.getXhckje()), cellStyle); Converter.setCellText(row, cell, 42, Converter.toBlank(data.getQmkc()), cellStyle); Converter.setCellText(row, cell, 43, Converter.toBlank(data.getQmkcje()), cellStyle); } if (i == size - 1) { if (select == 1) { StorageTotal sum = (StorageTotal) obj; Converter.setCellText(rows, cell, 0, "? ", cellStyle); Converter.setCellText(rows, cell, 3, Converter.toBlank(sum.getQckc()), cellStyle); Converter.setCellText(rows, cell, 4, Converter.toBlank(sum.getQckcje()), cellStyle); Converter.setCellText(rows, cell, 5, Converter.toBlank(sum.getQjrk()), cellStyle); Converter.setCellText(rows, cell, 6, Converter.toBlank(sum.getQjrkje()), cellStyle); Converter.setCellText(rows, cell, 7, Converter.toBlank(sum.getQjck()), cellStyle); Converter.setCellText(rows, cell, 8, Converter.toBlank(sum.getQjckje()), cellStyle); Converter.setCellText(rows, cell, 9, Converter.toBlank(sum.getQmkc()), cellStyle); Converter.setCellText(rows, cell, 10, Converter.toBlank(sum.getQmkcje()), cellStyle); } if (select == 2) { StorageMilde sum = (StorageMilde) obj; Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle); Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle); Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle); Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getQjrk()), cellStyle); Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getQjrkje()), cellStyle); Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getQjck()), cellStyle); Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getQjckje()), cellStyle); Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQmkc()), cellStyle); Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQmkcje()), cellStyle); } if (select == 3) { StorageDetail sum = (StorageDetail) obj; Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle); // Converter.setCellText(rows, cell, 1,Converter.toBlank(sum.getPharmacycode()), cellStyle); // Converter.setCellText(rows, cell, 2,Converter.toBlank(sum.getName()), cellStyle); // Converter.setCellText(rows, cell, 3,Converter.toBlank(sum.getMatcode()), cellStyle); // Converter.setCellText(rows, cell, 4,Converter.toBlank(sum.getBarcode()), cellStyle); // Converter.setCellText(rows, cell, 5,Converter.toBlank(sum.getMatname()), cellStyle); // Converter.setCellText(rows, cell, 6,Converter.toBlank(sum.getCommonname()), cellStyle); // Converter.setCellText(rows, cell, 7,Converter.toBlank(sum.getDosage()), cellStyle); // Converter.setCellText(rows, cell, 8,Converter.toBlank(sum.getSpec()), cellStyle); // Converter.setCellText(rows, cell, 9,Converter.toBlank(sum.getUnitname()), cellStyle); // Converter.setCellText(rows, cell, 10,Converter.toBlank(sum.getManufname()), cellStyle); // Converter.setCellText(rows, cell, 11,Converter.toBlank(sum.getProductarea()), cellStyle); // Converter.setCellText(rows, cell, 12,Converter.toBlank(sum.getLicensenum()), cellStyle); // Converter.setCellText(rows, cell, 13,Converter.toBlank(sum.getRetail()), cellStyle); Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle); Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle); Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getCg()), cellStyle); Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getCgje()), cellStyle); Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getPyrk()), cellStyle); Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getPyrkje()), cellStyle); Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQtrk()), cellStyle); Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQtrkje()), cellStyle); Converter.setCellText(rows, cell, 22, Converter.toBlank(sum.getClrk()), cellStyle); Converter.setCellText(rows, cell, 23, Converter.toBlank(sum.getClrkje()), cellStyle); Converter.setCellText(rows, cell, 24, Converter.toBlank(sum.getPsrk()), cellStyle); Converter.setCellText(rows, cell, 25, Converter.toBlank(sum.getPsrkje()), cellStyle); Converter.setCellText(rows, cell, 26, Converter.toBlank(sum.getQcrk()), cellStyle); Converter.setCellText(rows, cell, 27, Converter.toBlank(sum.getQcrkje()), cellStyle); Converter.setCellText(rows, cell, 28, Converter.toBlank(sum.getXsck()), cellStyle); Converter.setCellText(rows, cell, 29, Converter.toBlank(sum.getXsckje()), cellStyle); Converter.setCellText(rows, cell, 30, Converter.toBlank(sum.getPkck()), cellStyle); Converter.setCellText(rows, cell, 31, Converter.toBlank(sum.getPkckje()), cellStyle); Converter.setCellText(rows, cell, 32, Converter.toBlank(sum.getQtck()), cellStyle); Converter.setCellText(rows, cell, 33, Converter.toBlank(sum.getQtckje()), cellStyle); Converter.setCellText(rows, cell, 34, Converter.toBlank(sum.getClck()), cellStyle); Converter.setCellText(rows, cell, 35, Converter.toBlank(sum.getClckje()), cellStyle); Converter.setCellText(rows, cell, 36, Converter.toBlank(sum.getPsck()), cellStyle); Converter.setCellText(rows, cell, 37, Converter.toBlank(sum.getPsckje()), cellStyle); Converter.setCellText(rows, cell, 38, Converter.toBlank(sum.getQdck()), cellStyle); Converter.setCellText(rows, cell, 39, Converter.toBlank(sum.getQdckje()), cellStyle); Converter.setCellText(rows, cell, 40, Converter.toBlank(sum.getXhck()), cellStyle); Converter.setCellText(rows, cell, 41, Converter.toBlank(sum.getXhckje()), cellStyle); Converter.setCellText(rows, cell, 42, Converter.toBlank(sum.getQmkc()), cellStyle); Converter.setCellText(rows, cell, 43, Converter.toBlank(sum.getQmkcje()), cellStyle); } } } } return workbook; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:com.gestec.modelo.controladores.ArchivosView.java
public void HandleFileUpload(FileUploadEvent event) throws IOException { uploadedFile = event.getFile();/*from w ww . j ava 2 s .c om*/ InputStream file = uploadedFile.getInputstream(); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet miHoja = workbook.getSheetAt(0); Iterator filas = miHoja.rowIterator(); this.datosTabla.clear(); while (filas.hasNext()) { HSSFRow nuevaFila = (HSSFRow) filas.next(); Iterator celdas = nuevaFila.cellIterator(); String[] addFila = new String[7]; int pos = 0; while (celdas.hasNext()) { addFila[pos] = celdas.next().toString(); pos++; } addFila[6] = "" + efl.ingresarEquipoExcel(addFila); this.datosTabla.add(addFila); } FacesMessage fm = new FacesMessage("Exito", event.getFile().getFileName() + " Fue subido"); FacesContext.getCurrentInstance().addMessage(null, fm); }
From source file:com.github.camellabs.iot.cloudlet.geofencing.service.DefaultRouteServiceTest.java
License:Apache License
@Test public void shouldGenerateRoutesReport() throws URISyntaxException, IOException, InterruptedException { // Given/*w ww . j a v a2s .com*/ documentDriver.save(collectionName(point1.getClass()), pojoToMap(point1)); routeService.analyzeRoutes(client); String routeId = routeService.routes(client).get(0).getId(); RouteComment routeComment = new RouteComment(null, routeId, new Date(), "text"); documentDriver.save(collectionName(routeComment.getClass()), pojoToMap(routeComment)); URI clientsRequestUri = new URI(restApi + "routes/export/" + client + "/xls"); // When byte[] xls = IOUtils.toByteArray(clientsRequestUri); // Then HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new ByteArrayInputStream(xls))); String comment = wb.getSheetAt(0).getRow(0).getCell(2).getStringCellValue(); assertEquals(routeComment.getText(), comment); }
From source file:com.github.s4ke.worktimegen.Main.java
License:BEER-WARE LICENSE
public static void generateExcelSheet(int year, int month, List<Work> workObjs) throws IOException { try (InputStream is = Main.class.getResourceAsStream("/template_urlaub.xls")) { HSSFWorkbook workbook = new HSSFWorkbook(is); HSSFSheet sheet = workbook.getSheetAt(0); GregorianCalendar calendar = new GregorianCalendar(); calendar.set(year, month - 1, 1); sheet.getRow(7).getCell(2).setCellValue(DATE_FORMAT.format(calendar.getTime())); calendar.set(year, month - 1, calendar.getActualMaximum(Calendar.DAY_OF_MONTH)); sheet.getRow(7).getCell(4).setCellValue(DATE_FORMAT.format(calendar.getTime())); int startRow = 11; int endRow = 33; if (workObjs.size() > (endRow - startRow)) { throw new AssertionError("template has too few rows"); }// www. ja v a 2s.c o m int curRow = startRow; for (Work work : workObjs) { Row row = sheet.getRow(curRow); row.getCell(0).setCellValue(work.date); row.getCell(1).setCellValue(pad(work.startHours) + ":" + pad(work.startMinutes)); row.getCell(2).setCellValue(pad(work.endHours) + ":" + pad(work.endMinutes)); ++curRow; } try (FileOutputStream fos = new FileOutputStream( new File("zeiterfassung_braun_" + year + "_" + month + ".xls"))) { workbook.write(fos); } } }
From source file:com.glaf.base.modules.todo.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null; try {/* w ww. jav a2 s. c o m*/ wb = new HSSFWorkbook(inputStream); } catch (Exception ex) { throw new RuntimeException(ex); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); if ("id".equals(fieldName)) { cellValue = cellValue.toString().substring(0, cellValue.toString().indexOf(".")); dataMap.put(fieldName, cellValue); } } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); if (!keys.contains(ParamUtils.getString(dataMap, "code"))) { if (id != null && StringUtils.isNotEmpty(id)) { Todo model = new Todo(); Tools.populate(model, dataMap); if (ParamUtils.getInt(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } return todos; }
From source file:com.glaf.core.todo.util.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null; try {//from ww w.j av a 2 s . c om wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } int sortNo = 1; Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); // System.out.print("\t" + fieldName + "=" + cellValue); } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); Todo model = new Todo(); dataMap.remove("id"); Tools.populate(model, dataMap); if (!keys.contains(model.getCode())) { model.setSortNo(sortNo++); if (id != null) { model.setId(Long.parseLong(id)); } if (ParamUtils.getDouble(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } catch (Exception ex) { throw new RuntimeException(ex); } finally { if (wb != null) { try { wb.close(); wb = null; } catch (IOException e) { } } } return todos; }
From source file:com.gnadenheimer.mg.frames.admin.FrameEntidadesAdmin.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed try {// w w w . j ava 2 s .c o m JFileChooser fc = new JFileChooser(); Integer returnVal = fc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { File file = fc.getSelectedFile(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; Integer rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); Integer cols = 0; // No of columns Integer tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (Integer i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (Integer r = 1; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { if (!row.getCell(0).getStringCellValue().equals("")) { TblEntidades miembro = new TblEntidades(); miembro.setNombres(row.getCell(0).getStringCellValue()); if (row.getCell(1) != null) { miembro.setApellidos(row.getCell(1).getStringCellValue()); } else { miembro.setApellidos(""); } if (row.getCell(2).getCellType() == Cell.CELL_TYPE_STRING) { if (row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) { miembro.setCtacte(99999); } else { miembro.setCtacte(Integer.valueOf( row.getCell(2).getStringCellValue().replaceAll("[^\\d.]", ""))); } } else if (row.getCell(2).getCellType() == Cell.CELL_TYPE_NUMERIC) { miembro.setCtacte((int) (row.getCell(2).getNumericCellValue())); } List<TblEntidades> duplicadoList = entityManager.createQuery( "SELECT t FROM TblEntidades t where t.nombres = '" + miembro.getNombres() + "' and t.apellidos = '" + miembro.getApellidos() + "' and t.ctacte = " + miembro.getCtacte().toString(), TblEntidades.class).getResultList(); if (duplicadoList.size() > 0) { miembro = null; miembro = duplicadoList.get(0); } if (row.getCell(3) != null) { DecimalFormat df = new DecimalFormat("#0"); miembro.setRucSinDv(df.format(row.getCell(3).getNumericCellValue())); if (miembro.getRucSinDv().equals("0")) { miembro.setRucSinDv("44444401"); } } if (row.getCell(4) != null) { miembro.setDomicilio(row.getCell(4).getStringCellValue()); } if (row.getCell(5) != null) { miembro.setBox((int) row.getCell(5).getNumericCellValue()); } if (row.getCell(6) != null) { miembro.setFechaNacimiento( LocalDateTime.ofInstant(row.getCell(6).getDateCellValue().toInstant(), ZoneId.systemDefault()).toLocalDate()); } if (row.getCell(7) != null) { miembro.setFechaBautismo( LocalDateTime.ofInstant(row.getCell(7).getDateCellValue().toInstant(), ZoneId.systemDefault()).toLocalDate()); miembro.setIsMiembroActivo(true); } else { miembro.setIsMiembroActivo(false); } if (row.getCell(8) != null) { miembro.setFechaEntradaCongregacion( LocalDateTime.ofInstant(row.getCell(8).getDateCellValue().toInstant(), ZoneId.systemDefault()).toLocalDate()); } miembro.setAporteMensual(0); miembro.setIdFormaDePagoPreferida(listFormasDePago.get(0)); miembro.setIdUser(currentUser.getUser()); entityManager.persist(miembro); list.add(miembro); } } } } } catch (Exception ex) { JOptionPane.showMessageDialog(null, Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage()); LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex); } }
From source file:com.gnadenheimer.mg.frames.operaciones.ingresos.FrameColectasDetalle.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed try {/*from www .ja va 2 s . c o m*/ JFileChooser fc = new JFileChooser(); Integer returnVal = fc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { File file = fc.getSelectedFile(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; Integer rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); Integer cols = 0; // No of columns Integer tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (Integer i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (Integer r = 1; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { TblEventoDetalle t = new TblEventoDetalle(); TblEventos currEvento = (TblEventos) cboFechaColecta.getSelectedItem(); t.setFechahora(currEvento.getFecha().atStartOfDay()); t.setIdCategoriaArticulo(entityManager.find(TblCategoriasArticulos.class, 1)); t.setIdEvento(currEvento); t.setIdUser(currentUser.getUser()); Integer ctaCte = 0; if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { if (!row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", "").equals("")) { ctaCte = Integer .valueOf(row.getCell(0).getStringCellValue().replaceAll("[^\\d.]", "")); } } else if (row.getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) { ctaCte = (int) (row.getCell(0).getNumericCellValue()); } if (ctaCte != 0) { final Integer cc = ctaCte; List<TblEntidades> list = listMiembros; Optional<TblEntidades> value = list.stream().filter(a -> a.getCtacte().equals(cc)) .findFirst(); if (value.isPresent()) { t.setIdEntidad(value.get()); t.setIdFormaDePagoPreferida(tblFormasDePagoList.get(0)); if (row.getCell(1).getCellType() == Cell.CELL_TYPE_NUMERIC) { t.setMonto((int) (row.getCell(1).getNumericCellValue())); entityManager.persist(t); listEventoDetalle.add(t); } } else { JOptionPane.showMessageDialog(null, "No hay entidad registrada con Cta.Cte N " + FormatCtaCte.format(ctaCte)); } } } } } } catch (Exception ex) { JOptionPane.showMessageDialog(null, Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage()); LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex); } }