List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
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;/*from ww w. j a v a 2s. c o m*/ try { 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 {/*from w w w .j a v a 2s . 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 ww w. j a v a 2s. co 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); } }
From source file:com.hp.excelhandle.GetData.java
public void readExcel() { try {/* ww w. j a v a 2 s.com*/ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("database/customer.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns (max) int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int 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(int r = 0; r < rows; r++) { // row = sheet.getRow(r); // if(row != null) { // for(int c = 0; c < cols; c++) { // cell = row.getCell((short)c); // if(cell != null) { // // Your code here // } // } // } // } row = sheet.getRow(8); if (row != null) { cell = row.getCell(1); if (cell != null) System.out.println("Row: " + 9 + ", Data: " + cell.getStringCellValue()); } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:com.hrr3.services.DayStarFileImportService.java
License:Apache License
public ImportDayStarData parseFileToObjects(String fileName, int hotelId) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); ImportDayStarData dayStarData = null; //Container to save glance/response/summary SSRSnapshotDayStar glance = null; //To save glance data List<SSRSnapshotDayStarHotel> responses = null; //To save response data List<SSRSnapshotDayStarData> summaries = null; //To be implemented while reading excel in future version //1. Validate Glance SheetName at tab#2 if (wb.getNumberOfSheets() == 0 || wb.getSheet("Glance") == null) throw new ApplicationException("Excel file must contain a valid sheet called 'Glance'"); //2. Validate Glance SheetName at tab#7 or tab#12 if (wb.getNumberOfSheets() == 0 || wb.getSheet("Response") == null) throw new ApplicationException("Excel file must contain a valid sheet called 'Response'"); //3. Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet glanceSheet = wb.getSheet("Glance"); HSSFSheet responseSheet = wb.getSheet("Response"); //3.1 Instantiate main container dayStarData = new ImportDayStarData(); //4. Fill out glance Data glance = new SSRSnapshotDayStar(); glance.setHotelId(hotelId);/*from w ww . jav a2 s .c o m*/ glance.setCapHotel(getStringCellValue(glanceSheet.getRow(1).getCell(1)));//2B glance.setCapHotel2(getStringCellValue(glanceSheet.getRow(2).getCell(1)));//3B glance.setDateFrom(null); glance.setDateTo(null); glance.setCapWeek(getStringCellValue(glanceSheet.getRow(3).getCell(1)));//4B System.out.println(glance); //4.1 Fill out Summary Data (part of the same Glance sheet) summaries = new ArrayList<SSRSnapshotDayStarData>(); for (int iIndex = 1; iIndex <= 16; iIndex++) //16 is equals SUN - SAT by two weeks in the Glance sheet { String sType = ""; String sDateCurrYr = ""; String sDateLastYr = ""; String sDateRange = ""; String sYearCurr = ""; String sYearLast = ""; String sOCCProp = ""; String sOCCPropPC = ""; String sOCCCompSet = ""; String sOCCCompSetPC = ""; String sOCCIndex = ""; String sOCCIndexPC = ""; String sARRProp = ""; String sARRPropPC = ""; String sARRCompSet = ""; String sARRCompSetPC = ""; String sARRIndex = ""; String sARRIndexPC = ""; String sRPProp = ""; String sRPPropPC = ""; String sRPCompSet = ""; String sRPCompSetPC = ""; String sRPIndex = ""; String sRPIndexPC = ""; int sCol = 0;//Index for left value int sColPC = 0;//Index for right value switch (iIndex) { case 1: case 9: sType = "SUN"; sCol = 4; sColPC = 5; break; case 2: case 10: sType = "MON"; sCol = 7; sColPC = 8; break; case 3: case 11: sType = "TUE"; sCol = 10; sColPC = 11; break; case 4: case 12: sType = "WED"; sCol = 13; sColPC = 14; break; case 5: case 13: sType = "THU"; sCol = 16; sColPC = 17; break; case 6: case 14: sType = "FRI"; sCol = 19; sColPC = 20; break; case 7: case 15: sType = "SAT"; sCol = 22; sColPC = 23; break; case 8: case 16: sType = "SUBTOT"; sCol = 25; sColPC = 26; break; } //Read Weekly section if (iIndex >= 1 && iIndex <= 8) { sType = "CURR" + sType; sDateRange = getStringCellValue(glanceSheet.getRow(5).getCell(1)).trim();//Read 6B sOCCProp = getStringCellValue(glanceSheet.getRow(9).getCell(sCol)).trim(); sOCCPropPC = getStringCellValue(glanceSheet.getRow(9).getCell(sColPC)).trim(); sOCCCompSet = getStringCellValue(glanceSheet.getRow(10).getCell(sCol)).trim(); sOCCCompSetPC = getStringCellValue(glanceSheet.getRow(10).getCell(sColPC)).trim(); sOCCIndex = getStringCellValue(glanceSheet.getRow(11).getCell(sCol)).trim(); sOCCIndexPC = getStringCellValue(glanceSheet.getRow(11).getCell(sColPC)).trim(); sARRProp = getStringCellValue(glanceSheet.getRow(13).getCell(sCol)).trim(); sARRPropPC = getStringCellValue(glanceSheet.getRow(13).getCell(sColPC)).trim(); sARRCompSet = getStringCellValue(glanceSheet.getRow(14).getCell(sCol)).trim(); sARRCompSetPC = getStringCellValue(glanceSheet.getRow(14).getCell(sColPC)).trim(); sARRIndex = getStringCellValue(glanceSheet.getRow(15).getCell(sCol)).trim(); sARRIndexPC = getStringCellValue(glanceSheet.getRow(15).getCell(sColPC)).trim(); sRPProp = getStringCellValue(glanceSheet.getRow(17).getCell(sCol)).trim(); sRPPropPC = getStringCellValue(glanceSheet.getRow(17).getCell(sColPC)).trim(); sRPCompSet = getStringCellValue(glanceSheet.getRow(18).getCell(sCol)).trim(); sRPCompSetPC = getStringCellValue(glanceSheet.getRow(18).getCell(sColPC)).trim(); sRPIndex = getStringCellValue(glanceSheet.getRow(19).getCell(sCol)).trim(); sRPIndexPC = getStringCellValue(glanceSheet.getRow(19).getCell(sColPC)).trim(); } //Read Running section else if (iIndex > 8) { sType = "RUN" + sType; sDateRange = getStringCellValue(glanceSheet.getRow(22).getCell(1)).trim();//Read 23B sOCCProp = getStringCellValue(glanceSheet.getRow(26).getCell(sCol)).trim(); sOCCPropPC = getStringCellValue(glanceSheet.getRow(26).getCell(sColPC)).trim(); sOCCCompSet = getStringCellValue(glanceSheet.getRow(27).getCell(sCol)).trim(); sOCCCompSetPC = getStringCellValue(glanceSheet.getRow(27).getCell(sColPC)).trim(); sOCCIndex = getStringCellValue(glanceSheet.getRow(28).getCell(sCol)).trim(); sOCCIndexPC = getStringCellValue(glanceSheet.getRow(28).getCell(sColPC)).trim(); sARRProp = getStringCellValue(glanceSheet.getRow(30).getCell(sCol)).trim(); sARRPropPC = getStringCellValue(glanceSheet.getRow(30).getCell(sColPC)).trim(); sARRCompSet = getStringCellValue(glanceSheet.getRow(31).getCell(sCol)).trim(); sARRCompSetPC = getStringCellValue(glanceSheet.getRow(31).getCell(sColPC)).trim(); sARRIndex = getStringCellValue(glanceSheet.getRow(32).getCell(sCol)).trim(); sARRIndexPC = getStringCellValue(glanceSheet.getRow(32).getCell(sColPC)).trim(); sRPProp = getStringCellValue(glanceSheet.getRow(34).getCell(sCol)).trim(); sRPPropPC = getStringCellValue(glanceSheet.getRow(34).getCell(sColPC)).trim(); sRPCompSet = getStringCellValue(glanceSheet.getRow(35).getCell(sCol)).trim(); sRPCompSetPC = getStringCellValue(glanceSheet.getRow(35).getCell(sColPC)).trim(); sRPIndex = getStringCellValue(glanceSheet.getRow(36).getCell(sCol)).trim(); sRPIndexPC = getStringCellValue(glanceSheet.getRow(36).getCell(sColPC)).trim(); } SSRSnapshotDayStarData summary = new SSRSnapshotDayStarData(); summary.setType(sType); summary.setTab(2); summary.setSequence(iIndex); summary.setDatethisyr(sDateCurrYr); summary.setDatelastyr(sDateLastYr); summary.setCurrentyr(sYearCurr); summary.setLastyr(sYearLast); summary.setDaterange(sDateRange); summary.setOccProp(sOCCProp); summary.setOccPropPc(sOCCPropPC); summary.setOccCompset(sOCCCompSet); summary.setOccCompsetPc(sOCCCompSetPC); summary.setOccIndex(sOCCIndex); summary.setOccIndexPc(sOCCIndexPC); summary.setArrProp(sARRProp); summary.setArrPropPc(sARRPropPC); summary.setArrCompset(sARRCompSet); summary.setArrCompsetPc(sARRCompSetPC); summary.setArrIndex(sARRIndex); summary.setArrIndexPc(sARRIndexPC); summary.setRevparProp(sRPProp); summary.setRevparPropPc(sRPPropPC); summary.setRevparCompset(sRPCompSet); summary.setRevparCompsetPc(sRPCompSetPC); summary.setRevparIndex(sRPIndex); summary.setRevparIndexPc(sRPIndexPC); System.out.println(summary); summaries.add(summary); } //5. Find STR ID value on C (index=2) column int responseTotalRows = responseSheet.getPhysicalNumberOfRows(); int strIDHeaderRow = -1; int currentRow = -1; int currentCol = -1; int strIDHeaderCol = 2; // Column C String strIDHeaderVal = "STR ID"; String strHotelName = "Name"; String columnNameForHotelName = "N/A"; for (int i = 0; i < responseTotalRows; i++) { String cellValue = getStringCellValue(responseSheet.getRow(i).getCell(2)); if (cellValue.equalsIgnoreCase(strIDHeaderVal)) { strIDHeaderRow = i; break; } } //Validate of Name is on column D or E if (getStringCellValue(responseSheet.getRow(strIDHeaderRow).getCell(3)).equalsIgnoreCase(strHotelName)) columnNameForHotelName = "D"; else if (getStringCellValue(responseSheet.getRow(strIDHeaderRow).getCell(4)).equalsIgnoreCase(strHotelName)) columnNameForHotelName = "E"; else throw new ApplicationException("Column for Hotel Name not found in row[" + strIDHeaderRow + "]"); //6. If STRID could not be found, throws an error if (strIDHeaderRow == -1) throw new ApplicationException("Response Sheet must contain STR ID Header in Column C."); //7. Once STR ID was found, we look for QRSTUVW columns at index-> strIDHeaderRow String sDate1 = responseSheet.getRow(strIDHeaderRow).getCell(16).toString(); String sDate2 = responseSheet.getRow(strIDHeaderRow).getCell(17).toString(); String sDate3 = responseSheet.getRow(strIDHeaderRow).getCell(18).toString(); String sDate4 = responseSheet.getRow(strIDHeaderRow).getCell(19).toString(); String sDate5 = responseSheet.getRow(strIDHeaderRow).getCell(20).toString(); String sDate6 = responseSheet.getRow(strIDHeaderRow).getCell(21).toString(); String sDate7 = responseSheet.getRow(strIDHeaderRow).getCell(22).toString(); //8. Move cursor to next row below to start reading Hotel compsets currentRow = ++strIDHeaderRow; currentCol = strIDHeaderCol; //9. Fill out response Data responses = new ArrayList<SSRSnapshotDayStarHotel>(); //9.1 Process response Rows while cell value is not 0 or empty while (currentRow < responseTotalRows && (!getStringCellValue(responseSheet.getRow(currentRow).getCell(2)).equalsIgnoreCase("0") && !getStringCellValue(responseSheet.getRow(currentRow).getCell(2)).isEmpty())) { SSRSnapshotDayStarHotel currentResponse = new SSRSnapshotDayStarHotel(); //Date Data currentResponse.setDate1(sDate1);//sDate1 currentResponse.setDate2(sDate2);//sDate2 currentResponse.setDate3(sDate3);//sDate3 currentResponse.setDate4(sDate4);//sDate4 currentResponse.setDate5(sDate5);//sDate5 currentResponse.setDate6(sDate6);//sDate6 currentResponse.setDate7(sDate7);//sDate7 //Str Data currentResponse.setStrId(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol = columnNameForHotelName.equalsIgnoreCase("D") ? currentCol + 1 : currentCol + 2;//Sum 1 if "Name" found in Column D, or Sum 2 if found in E currentResponse.setHotel(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//E currentResponse.setCity(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//F currentResponse.setZip(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//G currentResponse.setPhone(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//H currentResponse.setRooms(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//I currentResponse.setOpendate(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol += 7;//J //DayStar Data currentResponse .setDataDaystar1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//Q currentResponse .setDataDaystar2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//R currentResponse .setDataDaystar3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//S currentResponse .setDataDaystar4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//T currentResponse .setDataDaystar5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//U currentResponse .setDataDaystar6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//V currentResponse .setDataDaystar7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol += 3;//W //Segmented Data currentResponse.setDataSeg1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//Z currentResponse.setDataSeg2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AA currentResponse.setDataSeg3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AB currentResponse.setDataSeg4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AC currentResponse.setDataSeg5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AD currentResponse.setDataSeg6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AE currentResponse.setDataSeg7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol))); currentCol += 3;//AF //F&B Data currentResponse.setDataFb1(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AI currentResponse.setDataFb2(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AJ currentResponse.setDataFb3(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AK currentResponse.setDataFb4(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AL currentResponse.setDataFb5(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AM currentResponse.setDataFb6(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AN currentResponse.setDataFb7(getStringCellValue(responseSheet.getRow(currentRow).getCell(currentCol++)));//AO System.out.println(currentResponse); responses.add(currentResponse); currentRow++; currentCol = strIDHeaderCol; } dayStarData.setGlance(glance); dayStarData.setSummaries(summaries); dayStarData.setResponse(responses); return dayStarData; }
From source file:com.hrr3.services.FileImportService.java
License:Apache License
public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); List<ImportSSRLRRData> rowDataList = null; //1. Validate SheetName if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR")) throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'"); //2. Validate row 3 (index 2) contains at least 14 columns if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14) throw new ApplicationException( "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template."); //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet lrrSheet = wb.getSheetAt(0); int totalRows = lrrSheet.getPhysicalNumberOfRows(); int startIndex = 3;//DataIndex to start reading srr info int startDateIndex = -1; //To save row index for startdate int endDateIndex = -1;//To save row inxed for enddate int dateColumnIndex = 1; //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) { HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex); //3.1 If row is not latest one, validate Date and Format if (rowIndex < totalRows) { if (currentDateCell == null || currentDateCell.getDateCellValue() == null) throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty."); else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell)) throw new ApplicationException( "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format."); }// ww w . j av a 2s. c o m //3.2 Get Start and End indexes according to date range requested by user if (currentDateCell.getDateCellValue().compareTo(startDate) == 0) startDateIndex = rowIndex; if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) { endDateIndex = rowIndex; break; } } System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************"); System.out.println("******** StartDate INDEX: " + startDateIndex + " *************"); System.out.println("******** EndDate INDEX: " + endDateIndex + " *************"); //Create RowDataList to contain each row rowDataList = new ArrayList<ImportSSRLRRData>(); //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects. for (int i = startDateIndex; i <= endDateIndex; i++) { HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex); Date statdateCol = currentDateCell.getDateCellValue(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); String statdate = dateFormat.format(statdateCol); String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3)); String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4)); String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5)); String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6)); String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7)); String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8)); String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9)); String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10)); String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11)); String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12)); String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13)); //Fill rowData object ImportSSRLRRData rowData = new ImportSSRLRRData(); rowData.setStatdate(statdate); rowData.setLrr1(new BigDecimal(lrr1)); rowData.setLrr2(new BigDecimal(lrr2)); rowData.setLrr3(new BigDecimal(lrr3)); rowData.setLrr4(new BigDecimal(lrr4)); rowData.setLrr5(new BigDecimal(lrr5)); rowData.setLrr6(new BigDecimal(lrr6)); rowData.setLrr7(new BigDecimal(lrr7)); rowData.setLrr8(new BigDecimal(lrr8)); rowData.setLrr9(new BigDecimal(lrr9)); rowData.setLrrHp1(new BigDecimal(hp1)); rowData.setLrrHp2(new BigDecimal(hp2)); //Add row to the list rowDataList.add(rowData); System.out.println(rowData); } System.out.println("******** Rows to return => " + rowDataList.size() + " ********"); return rowDataList; }
From source file:com.hrr3.services.LRRFileImportService.java
License:Apache License
public List<ImportSSRLRRData> parseFileToObjects(String fileName, Date startDate, Date endDate) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); List<ImportSSRLRRData> rowDataList = null; //1. Validate SheetName if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("LRR")) throw new ApplicationException("Excel file must contain a valid sheet called 'LRR'"); //2. Validate row 3 (index 2) contains at least 14 columns if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 14) throw new ApplicationException( "'LRR setup sheet must contains at least 14 columns at Row#3 to identify SSR-LRR template."); //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet lrrSheet = wb.getSheetAt(0); int totalRows = lrrSheet.getPhysicalNumberOfRows(); int startIndex = 3;//DataIndex to start reading srr info int startDateIndex = -1; //To save row index for startdate int endDateIndex = -1;//To save row inxed for enddate int dateColumnIndex = 1; //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) { HSSFCell currentDateCell = lrrSheet.getRow(rowIndex).getCell(dateColumnIndex); //3.1 If row is not latest one, validate Date and Format if (rowIndex < totalRows) { if (currentDateCell == null || currentDateCell.getDateCellValue() == null) throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty."); else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell)) throw new ApplicationException( "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format."); }//from w w w . j a v a 2 s .co m //3.2 Get Start and End indexes according to date range requested by user if (currentDateCell.getDateCellValue().compareTo(startDate) == 0) startDateIndex = rowIndex; if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) { endDateIndex = rowIndex; break; } } System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************"); System.out.println("******** StartDate INDEX: " + startDateIndex + " *************"); System.out.println("******** EndDate INDEX: " + endDateIndex + " *************"); if (startDateIndex == -1 || endDateIndex == -1) throw new ApplicationException( "Sheet does not contain data for the date range. Please provide a valid one."); //Create RowDataList to contain each row rowDataList = new ArrayList<ImportSSRLRRData>(); //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects. for (int i = startDateIndex; i <= endDateIndex; i++) { HSSFCell currentDateCell = lrrSheet.getRow(i).getCell(dateColumnIndex); Date statdateCol = currentDateCell.getDateCellValue(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); String statdate = dateFormat.format(statdateCol); String lrr1 = getStringCellValue(lrrSheet.getRow(i).getCell(3)); String lrr2 = getStringCellValue(lrrSheet.getRow(i).getCell(4)); String lrr3 = getStringCellValue(lrrSheet.getRow(i).getCell(5)); String lrr4 = getStringCellValue(lrrSheet.getRow(i).getCell(6)); String lrr5 = getStringCellValue(lrrSheet.getRow(i).getCell(7)); String lrr6 = getStringCellValue(lrrSheet.getRow(i).getCell(8)); String lrr7 = getStringCellValue(lrrSheet.getRow(i).getCell(9)); String lrr8 = getStringCellValue(lrrSheet.getRow(i).getCell(10)); String lrr9 = getStringCellValue(lrrSheet.getRow(i).getCell(11)); String hp1 = getStringCellValue(lrrSheet.getRow(i).getCell(12)); String hp2 = getStringCellValue(lrrSheet.getRow(i).getCell(13)); //Fill rowData object ImportSSRLRRData rowData = new ImportSSRLRRData(); rowData.setStatdate(statdate); rowData.setLrr1(new BigDecimal(lrr1)); rowData.setLrr2(new BigDecimal(lrr2)); rowData.setLrr3(new BigDecimal(lrr3)); rowData.setLrr4(new BigDecimal(lrr4)); rowData.setLrr5(new BigDecimal(lrr5)); rowData.setLrr6(new BigDecimal(lrr6)); rowData.setLrr7(new BigDecimal(lrr7)); rowData.setLrr8(new BigDecimal(lrr8)); rowData.setLrr9(new BigDecimal(lrr9)); rowData.setLrrHp1(new BigDecimal(hp1)); rowData.setLrrHp2(new BigDecimal(hp2)); //Add row to the list rowDataList.add(rowData); System.out.println(rowData); } System.out.println("******** Rows to return => " + rowDataList.size() + " ********"); return rowDataList; }
From source file:com.hrr3.services.SSRFileImportService.java
License:Apache License
public List<ImportSSRData> parseFileToObjects(String fileName, Date startDate, Date endDate) throws ApplicationException, IOException { HSSFWorkbook wb = this.readFile(fileName); List<ImportSSRData> rowDataList = null; //1. Validate SheetName if (wb.getNumberOfSheets() == 0 || !wb.getSheetAt(0).getSheetName().equals("SellStrat")) throw new ApplicationException("Excel file must contain a valid sheet called 'SellStrat'"); //2. Validate row 3 (index 2) contains at least 22 columns if (wb.getSheetAt(0).getRow(2).getPhysicalNumberOfCells() < 22) throw new ApplicationException( "'SellStrat sheet must contains at least 22 columns at Row#3 to identify SSR template."); //Once 1 and 2 validation is done, assign proper objects for future usage in more specific validations HSSFSheet sellStrat = wb.getSheetAt(0); int totalRows = sellStrat.getPhysicalNumberOfRows(); int startIndex = 3;//DataIndex to start reading srr info int startDateIndex = -1; //To save row index for startdate int endDateIndex = -1;//To save row inxed for enddate //3.Validate date range exists (ie. 1/1/2014 to 1/20/2014) and get start and end indexes containing the data for (int rowIndex = startIndex; rowIndex < totalRows; rowIndex++) { HSSFCell currentDateCell = sellStrat.getRow(rowIndex).getCell(2); //3.1 If row is not latest one, validate Date and Format if (rowIndex < totalRows) { if (currentDateCell == null || currentDateCell.getDateCellValue() == null) throw new ApplicationException("Column[Date] at row[" + (rowIndex + 1) + "] can't be empty."); else if (!HSSFDateUtil.isCellDateFormatted(currentDateCell)) throw new ApplicationException( "Column[Date] at row[" + (rowIndex + 1) + "] must be Date Format."); }/*from w w w .j a v a 2s. co m*/ //3.2 Get Start and End indexes according to date range requested by user if (currentDateCell.getDateCellValue().compareTo(startDate) == 0) startDateIndex = rowIndex; if (currentDateCell.getDateCellValue().compareTo(endDate) == 0) { endDateIndex = rowIndex; break; } } System.out.println("******** RANGE [" + startDate + "-" + endDate + "] Found *************"); System.out.println("******** StartDate INDEX: " + startDateIndex + " *************"); System.out.println("******** EndDate INDEX: " + endDateIndex + " *************"); if (startDateIndex == -1 || endDateIndex == -1) throw new ApplicationException( "Sheet does not contain data for the date range. Please provide a valid one."); //Create RowDataList to contain each row rowDataList = new ArrayList<ImportSSRData>(); //4. Now having start and end date, get the range of rows and fill out a list of ImportSSRData objects. for (int i = startDateIndex; i <= endDateIndex; i++) { HSSFCell currentDateCell = sellStrat.getRow(i).getCell(2); Date statdateCol = currentDateCell.getDateCellValue(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); String comments = sellStrat.getRow(i).getCell(0) != null ? sellStrat.getRow(i).getCell(0).getStringCellValue() : ""; String statdate = dateFormat.format(statdateCol); Calendar c = Calendar.getInstance(); try { c.setTime(dateFormat.parse(statdate)); } catch (ParseException e) { // TODO Auto-generated catch block e.printStackTrace(); } String[] strDays = new String[] { "Sunday", "Monday", "Tuesday", "Wednesday", "Thusday", "Friday", "Saturday" }; String down = strDays[c.get(Calendar.DAY_OF_WEEK) - 1]; //sellStrat.getRow(i).getCell(1).getStringCellValue(); String a1 = sellStrat.getRow(i).getCell(3).getStringCellValue(); String b2 = sellStrat.getRow(i).getCell(4).getStringCellValue(); String c3 = sellStrat.getRow(i).getCell(5).getStringCellValue(); String d4 = sellStrat.getRow(i).getCell(6).getStringCellValue(); String e5 = sellStrat.getRow(i).getCell(7).getStringCellValue(); String f6 = sellStrat.getRow(i).getCell(8).getStringCellValue(); String g7 = sellStrat.getRow(i).getCell(9).getStringCellValue(); String h8 = sellStrat.getRow(i).getCell(10).getStringCellValue(); String i9 = sellStrat.getRow(i).getCell(11).getStringCellValue(); String hp1 = getStringCellValue(sellStrat.getRow(i).getCell(12)); String hp2 = getStringCellValue(sellStrat.getRow(i).getCell(13)); String ssrTransient = getStringCellValue(sellStrat.getRow(i).getCell(14)); String ssrGrpblock = getStringCellValue(sellStrat.getRow(i).getCell(15)); String ssrContract = getStringCellValue(sellStrat.getRow(i).getCell(16)); String ssrGrppu = getStringCellValue(sellStrat.getRow(i).getCell(18)); String ssrGrprem = getStringCellValue(sellStrat.getRow(i).getCell(19)); String ssrDemandtd = getStringCellValue(sellStrat.getRow(i).getCell(20)); String ssrPricetd = getStringCellValue(sellStrat.getRow(i).getCell(21)); //Fill rowData object ImportSSRData rowData = new ImportSSRData(); rowData.setComment(comments); rowData.setDow(down); rowData.setStatdate(statdate); rowData.setA1(a1); rowData.setB2(b2); rowData.setC3(c3); rowData.setD4(d4); rowData.setE5(e5); rowData.setF6(f6); rowData.setG7(g7); rowData.setH8(h8); rowData.setI9(i9); rowData.setHp1(hp1); rowData.setHp2(hp2); rowData.setSsrTransient(new BigDecimal(ssrTransient)); rowData.setSsrGrpblock(new BigDecimal(ssrGrpblock)); rowData.setSsrContract(new BigDecimal(ssrContract)); rowData.setSsrGrppu(new BigDecimal(ssrGrppu)); rowData.setSsrGrprem(new BigDecimal(ssrGrprem)); rowData.setSsrDemandtd(new BigDecimal(ssrDemandtd)); rowData.setSsrPricetd(new BigDecimal(ssrPricetd)); //Add row to the list rowDataList.add(rowData); System.out.println(rowData); } System.out.println("******** Rows to return => " + rowDataList.size() + " ********"); return rowDataList; }
From source file:com.isotrol.impe3.idx.oc.extractors.ExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * // w w w . j a v a 2s . c om * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring * @deprecated */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (excelWb.getSheetName(i) != null && !excelWb.getSheetName(i).trim().equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator<?> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator<?> it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && (text.length() != 0)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:com.lacreacion.remates.FrameMiembros.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed try {/*from w w w.ja va 2 s . c o m*/ JFileChooser fc = new JFileChooser(); int returnVal = fc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { getDatabaseIP(); 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; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int 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 (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { entityManager.getTransaction().commit(); entityManager.getTransaction().begin(); TblMiembros miembro = new TblMiembros(); miembro.setNombre(row.getCell(0).getStringCellValue()); miembro.setCtacte( Integer.valueOf(row.getCell(1).getStringCellValue().replaceAll("[^\\d.]", ""))); if (row.getCell(2) != null) { miembro.setDomicilio(row.getCell(2).getStringCellValue()); } if (row.getCell(3) != null) { miembro.setBox((int) row.getCell(3).getNumericCellValue()); } entityManager.persist(miembro); entityManager.flush(); java.util.Collection data = query.getResultList(); list.clear(); list.addAll(data); } } } } catch (Exception ex) { JOptionPane.showMessageDialog(null, Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage()); ex.printStackTrace(); } }