List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell
@Override public XSSFCell getCell(int cellnum)
From source file:achmad.rifai.admin.ui.Opener.java
private void tugas() { try {//from w w w . ja v a 2s. com achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("Tugas"); List<achmad.rifai.erp1.entity.Tugas> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.Tugas t = new achmad.rifai.erp1.entity.Tugas(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.Petugas> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.Petugas p = new achmad.rifai.erp1.entity.Petugas(); p.setKaryawan(r2.getCell(3).getStringCellValue()); p.setDiambil(Boolean.parseBoolean(r2.getCell(4).getStringCellValue())); p.setSedang(Boolean.parseBoolean(r2.getCell(5).getStringCellValue())); p.setTerlaksana(Boolean.parseBoolean(r2.getCell(6).getStringCellValue())); l1.add(p); y++; r2 = s.getRow(y); trus = false; } t.setL(l1); t.setKode(r1.getCell(0).getStringCellValue()); t.setNo(Integer.parseInt(r1.getCell(1).getStringCellValue())); t.setTgl(java.sql.Date.valueOf(r1.getCell(2).getStringCellValue())); t.setKet(r1.getCell(7).getStringCellValue()); t.setBatal(Boolean.parseBoolean(r1.getCell(8).getStringCellValue())); t.setPending(Boolean.parseBoolean(r1.getCell(9).getStringCellValue())); t.setDeleted(Boolean.parseBoolean(r1.getCell(10).getStringCellValue())); x = y + 1; l.add(t); st = s.getRow(x).getCell(0).getStringCellValue(); } progTugas.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOTugas(d).insert(l.get(c)); progTugas.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progTugas.setValue(100); }
From source file:achmad.rifai.admin.ui.Opener.java
private void bonus() { try {/* w w w . ja va2 s .c o m*/ achmad.rifai.erp1.util.Db d = achmad.rifai.erp1.util.Work.loadDB(); org.apache.poi.xssf.usermodel.XSSFWorkbook w = new org.apache.poi.xssf.usermodel.XSSFWorkbook(f); org.apache.poi.xssf.usermodel.XSSFSheet s = w.getSheet("bonus"); List<achmad.rifai.erp1.entity.BulanBonus> l = new java.util.LinkedList<>(); int x = 2; String st = s.getRow(x).getCell(0).getStringCellValue(); while (!st.isEmpty()) { achmad.rifai.erp1.entity.BulanBonus b = new achmad.rifai.erp1.entity.BulanBonus(); int y = x; org.apache.poi.xssf.usermodel.XSSFRow r1 = s.getRow(x), r2 = s.getRow(y); List<achmad.rifai.erp1.entity.Bonusan> l1 = new java.util.LinkedList<>(); boolean trus = true; while (trus || null == r2.getCell(0)) { achmad.rifai.erp1.entity.Bonusan bo = new achmad.rifai.erp1.entity.Bonusan(); bo.setNomer(Integer.parseInt(r2.getCell(2).getStringCellValue())); bo.setJumlah(org.joda.money.Money.parse(r2.getCell(3).getStringCellValue())); l1.add(bo); y++; r2 = s.getRow(y); trus = false; } b.setL(l1); b.setKode(r1.getCell(0).getStringCellValue()); b.setPeg(r1.getCell(1).getStringCellValue()); b.setBln(Month.valueOf(r1.getCell(4).getStringCellValue())); b.setThn(java.time.Year.parse(r1.getCell(5).getStringCellValue())); b.setDeleted(Boolean.parseBoolean(r1.getCell(6).getStringCellValue())); l.add(b); x = y + 1; st = s.getRow(x).getCell(0).getStringCellValue(); } progBonus.setValue(50); for (int c = 0; c < l.size(); c++) { new achmad.rifai.erp1.entity.dao.DAOBulanBonus(d).insert(l.get(c)); progBonus.setValue((((1 + c) * 50) / l.size()) + 50); } d.close(); } catch (Exception ex) { achmad.rifai.erp1.util.Db.hindar(ex); } progBonus.setValue(100); }
From source file:be.thomasmore.service.FileUploadServiceImp1.java
@Override public void handleFileUpload(FileUploadEvent event) { clearVars();//from www . j a va 2 s. c o m InputStream file; XSSFWorkbook workbook = null; try { //geupload excel bestand inlezen file = event.getFile().getInputstream(); workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); //de excell overlopen en de gegevens eruit halen //klas ophalen int i = 0; XSSFRow row = sheet.getRow(i++); klas.setNaam(row.getCell(1).getStringCellValue());// =>naam klas (bv 5a) //vak ophalen row = sheet.getRow(i++); vak.setNaam(row.getCell(1).getStringCellValue());// => naam vak (bv java) //test ophalen row = sheet.getRow(i++); test.setNaam(row.getCell(1).getStringCellValue()); // => naam test row = sheet.getRow(i++); test.setTotaal((int) row.getCell(1).getNumericCellValue());// => totaal test //vak.setTesten(new ArrayList<Test>(test)); //test.setVak(vak); i += 2; //studenten +scores ophalen while (i <= sheet.getLastRowNum()) { row = sheet.getRow(i++); Student student = new Student(); student.setStudentennummer(String.valueOf((int) row.getCell(0).getNumericCellValue())); student.setNaam(row.getCell(1).getStringCellValue()); Score score = new Score(); score.setKlas(klas); score.setStudent(student); score.setTest(test); score.setScore((int) row.getCell(2).getNumericCellValue()); scores.add(score); } test.setScores(scores); klas.setScores(scores); // klas.setStudenten(studenten); } catch (IOException e) { // facesContext // .addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error reading file" + e, null)); e.printStackTrace(); } }
From source file:businessCharts.readExcell.java
public TreeMap<Double, String> getcontentList(String colName, String colName2) throws Exception { TreeMap<Double, String> treemap = new TreeMap<Double, String>(); initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int setColno = getColNo(colName, row); int setColno2 = getColNo(colName2, row); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(setColno); Cell cell2 = row.getCell(setColno2); try {//from ww w .j ava2s . c om treemap.put(cell.getNumericCellValue(), new SimpleDateFormat("yyyy-MM-dd").format(cell2.getDateCellValue())); } catch (Exception ex) { treemap.put(cell.getNumericCellValue(), "nodate"); } } return treemap; }
From source file:businessCharts.readExcell.java
public String[] queryByRowKey(String[] keyStringArray, String colName, String resultColName) throws Exception { String[] resultStringArray = new String[keyStringArray.length]; initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int getcellNo = getColNo(colName, row); int getReturncellKey = getColNo(resultColName, row); System.out.println(// www . j a va 2s .c om "Inside queryByRowKey" + " CellNocolname" + getcellNo + "ReturnCellColName" + getReturncellKey); for (int i = 0; i < keyStringArray.length; i++) { System.out.println("resultstring array elements" + keyStringArray[i]); rowIterator = spreadsheet.iterator(); row = (XSSFRow) rowIterator.next(); int flag = 0; while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(getcellNo); Cell returnCell = row.getCell(getReturncellKey); // System.out.println("Date is " + returnCell.getDateCellValue()); // try{ if (cell.getNumericCellValue() == Double.parseDouble(keyStringArray[i])) { flag = 1; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // System.out.println("Date is " + sdf.format(returnCell.getDateCellValue())); try { if (returnCell.getDateCellValue() != null) resultStringArray[i] = sdf.format(returnCell.getDateCellValue()); } catch (Exception ex) { resultStringArray[i] = "No Deadline Available"; } } // } // catch(Exception ex){ // continue; //} } if (flag == 0) { resultStringArray[i] = "Bug Not Available/Resolved"; flag = 0; } else flag = 0; } return resultStringArray; }
From source file:businessCharts.readExcell.java
public Pair<Integer, Integer> countColKeywordClone(String keyword, String colName) throws Exception { initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int getcellNo = getColNo(colName, row); int getcellNokey = getColNo("keywords", row); int countCells = 0; int notCloned = 0; System.out.println("CellNokey" + getcellNokey + "getqurycell" + getcellNo); while (rowIterator.hasNext()) { totalrow++;//w ww . j a v a 2 s.co m row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(getcellNo); Cell cellkey = row.getCell(getcellNokey); try { if (isContain(cell, keyword)) notCloned++; if (isContain(cell, keyword) && isContain(cellkey, "clone")) countCells++; } //System.out.println(); catch (NullPointerException ex) { continue; } } Pair<Integer, Integer> pair = new Pair<Integer, Integer>(countCells, notCloned - countCells); System.out.println(totalrow); return pair; }
From source file:businessCharts.readExcell.java
public TreeMap<String, TreeMap<String, Pair<Integer, Integer>>> groupBycount(String par1, String par2) throws Exception { //so that mapping could be done in O(n*log n*log n) initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow tempRow = (XSSFRow) rowIterator.next(); int cell_branch = getColNo(par1, tempRow); int cell_assignee = getColNo(par2, tempRow); int cell_keyword = getColNo("Keywords", tempRow); System.out.println("columns no" + cell_assignee + " " + cell_branch + " " + cell_keyword); TreeMap<String, TreeMap<String, Pair<Integer, Integer>>> list = new TreeMap<>(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); Cell Branchcell = row.getCell(cell_branch); Cell Assigneecell = row.getCell(cell_assignee); if (list.containsKey(Branchcell.getStringCellValue())) { if (list.get(Branchcell.getStringCellValue()).containsKey(Assigneecell.getStringCellValue())) { try { Cell keycell = row.getCell(cell_keyword); if (isContain(keycell, "clone")) { int key = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getKey(); int value = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getValue(); //System.out.println("key->" + key); key++;/* w w w.ja v a 2 s . c om*/ list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(key, value)); } else { int key = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getKey(); int value = list.get(Branchcell.getStringCellValue()) .get(Assigneecell.getStringCellValue()).getValue(); //System.out.println("value->" + value); value++; list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(key, value)); } } catch (NoSuchElementException ex) { int key = list.get(Branchcell.getStringCellValue()).get(Assigneecell.getStringCellValue()) .getKey(); int value = list.get(Branchcell.getStringCellValue()).get(Assigneecell.getStringCellValue()) .getValue(); value++; list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(key, value)); } } else { try { Cell keycell = row.getCell(cell_keyword); if (isContain(keycell, "clone")) list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(1, 0)); else list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(0, 1)); } catch (NoSuchElementException ex) { list.get(Branchcell.getStringCellValue()).put(Assigneecell.getStringCellValue(), new Pair<>(0, 1)); } } } else { if (isContain(row.getCell(cell_keyword), "clone")) { TreeMap<String, Pair<Integer, Integer>> tp = new TreeMap<>(); tp.put(Assigneecell.getStringCellValue(), new Pair<>(1, 0)); list.put(Branchcell.getStringCellValue(), tp); } else { TreeMap<String, Pair<Integer, Integer>> tp = new TreeMap<>(); tp.put(Assigneecell.getStringCellValue(), new Pair<>(0, 1)); list.put(Branchcell.getStringCellValue(), tp); } } } return list; }
From source file:CDatos.Excel.java
/** * Lee una hoja de un excel y devuelve una matriz con los datos * @return Matriz con los datos del excel *//*ww w . ja va 2 s .c o m*/ public ArrayList getDatosHoja() { ArrayList<ArrayList> filas = new ArrayList(); XSSFSheet sheet = workbook.getSheetAt(hoja); int numColumnas = -1; // Recorremos fila a fila for (int r = 0; r <= sheet.getLastRowNum(); r++) { ArrayList<String> celdas = new ArrayList(); XSSFRow row = sheet.getRow(r); if (row == null) break; else { // En la primera fila se leen las cabeceras, por lo que aprovechamos para // guardar el nmero de columnas porque cuando una fila tiene celdas vacas el tamao // de la lista es menor if (numColumnas == -1) numColumnas = row.getLastCellNum(); // Recorremos celda a celda for (int c = 0; c < numColumnas; c++) { XSSFCell cell = row.getCell(c); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formateador = new SimpleDateFormat("yyyy-MM-dd"); //cellValue = cell.getDateCellValue().toString(); cellValue = formateador.format(cell.getDateCellValue()); } else { cellValue = (Integer.toString((int) cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; } } celdas.add(cellValue); } filas.add(celdas); } } return filas; }
From source file:cn.comgroup.tzmedia.server.report.CustomerOrderReport.java
public CustomerOrderReportResult runOrderReport(String deployPath, List<CustomerOrder> orders) throws ParseException, FileNotFoundException, IOException { String reportTemplate = deployPath + File.separator + "template" + File.separator + "CustomerOrderReport.xlsx"; final XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportTemplate)); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); XSSFSheet sheet = workbook.getSheetAt(0); int startRow = 1; for (CustomerOrder co : orders) { XSSFRow row = sheet.getRow(startRow); if (row == null) { row = sheet.createRow(startRow); }//from w w w . j a v a 2 s. c o m XSSFCell cellOrderNumber = row.getCell(0); if (cellOrderNumber == null) { cellOrderNumber = row.createCell(0); } XSSFCell cellOrderDate = row.getCell(1); if (cellOrderDate == null) { cellOrderDate = row.createCell(1); } XSSFCell cellOrderType = row.getCell(2); if (cellOrderType == null) { cellOrderType = row.createCell(2); } XSSFCell cellOrderStatus = row.getCell(3); if (cellOrderStatus == null) { cellOrderStatus = row.createCell(3); } XSSFCell cellETN = row.getCell(4); if (cellETN == null) { cellETN = row.createCell(4); } XSSFCell cellShop = row.getCell(5); if (cellShop == null) { cellShop = row.createCell(5); } XSSFCell cellUserId = row.getCell(6); if (cellUserId == null) { cellUserId = row.createCell(6); } XSSFCell cellUserName = row.getCell(7); if (cellUserName == null) { cellUserName = row.createCell(7); } XSSFCell cellPaymentTerm = row.getCell(8); if (cellPaymentTerm == null) { cellPaymentTerm = row.createCell(8); } XSSFCell cellOrderAmount = row.getCell(9); if (cellOrderAmount == null) { cellOrderAmount = row.createCell(9); } XSSFCell cellCouponAmount = row.getCell(10); if (cellCouponAmount == null) { cellCouponAmount = row.createCell(10); } cellOrderNumber.setCellValue(co.getOrderNumber()); if (co.getOrderDate() != null) { cellOrderDate.setCellValue(dateFormat.format(co.getOrderDate().getTime())); } else { cellOrderDate.setCellValue(dateFormat.format(co.getOrderTime().getTime())); } cellOrderType.setCellValue(co.getOrderType().toString()); cellOrderStatus.setCellValue(co.getOrderStatus().toString()); if (co.getExternalTransactionNumber() != null) { cellETN.setCellValue(co.getExternalTransactionNumber()); } cellShop.setCellValue(co.getShopName()); cellUserId.setCellValue(co.getUserId()); cellUserName.setCellValue(co.getUserName()); cellPaymentTerm.setCellValue(co.getPaymentTerm().toString()); cellOrderAmount.setCellValue(co.getOrderAmount()); cellCouponAmount.setCellValue(co.getCouponAmount()); startRow++; System.out.println(startRow + " orders in the report"); } DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd-HHMMSS"); String reportName = "CustomerOrderReport" + dateTimeFormat.format(new Date()) + ".xlsx"; String reportPath = deployPath + File.separator + reportName; FileOutputStream fos = new FileOutputStream(reportPath); try (BufferedOutputStream bout = new BufferedOutputStream(fos)) { workbook.write(bout); bout.flush(); } return new CustomerOrderReportResult(reportName); }
From source file:com.accounting.accountMBean.DifferentAccReports.java
public void simplePostProcessXLSX(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); XSSFWorkbook wb = (XSSFWorkbook) document; XSSFCellStyle headerCellStyle = wb.createCellStyle(); XSSFCellStyle headerCellStyle1 = wb.createCellStyle(); XSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); XSSFSheet sheet = wb.getSheetAt(0);/* w w w.j a v a2 s. c om*/ int noOfColumns = sheet.getRow(2).getLastCellNum(); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); } sheet.shiftRows(0, sheet.getLastRowNum(), 4); XSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue("SALES AGEING REPORT"); firstRow.getCell(0).setCellStyle(headerCellStyle); XSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); // XSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); XSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); }