List of usage examples for org.apache.poi.ss.util CellReference CellReference
public CellReference(int pRow, short pCol)
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);/* w w w . j av a 2 s . c o m*/ Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
From source file:CreateTable.java
License:Apache License
public static void main(String[] args) throws FileNotFoundException, IOException { Workbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet) wb.createSheet(); //Create /*from w w w .j av a 2s . co m*/ XSSFTable table = sheet.createTable(); table.setDisplayName("Test"); CTTable cttable = table.getCTTable(); //Style configurations CTTableStyleInfo style = cttable.addNewTableStyleInfo(); style.setName("TableStyleMedium2"); style.setShowColumnStripes(false); style.setShowRowStripes(true); //Set which area the table should be placed in AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(3, 3)); cttable.setRef(reference.formatAsString()); cttable.setId(1); cttable.setName("Test"); cttable.setTotalsRowCount(1); CTTableColumns columns = cttable.addNewTableColumns(); columns.setCount(3); CTTableColumn column; XSSFRow row; XSSFCell cell; for (int i = 0; i < 3; i++) { //Create column column = columns.addNewTableColumn(); column.setName("Column"); column.setId(i + 1); //Create row row = sheet.createRow(i); for (int j = 0; j < 3; j++) { //Create cell cell = row.createCell(j); if (i == 0) { cell.setCellValue("Column" + j); } else { cell.setCellValue(i + j + 0.0); } } } FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:ExampleClass.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); // Iterator< Row> rowIterator = sheet1.iterator(); int colIndex = 0; int ex = 0;/*from ww w .j a v a 2 s . c o m*/ int lastCol = sheet1.getLastRowNum(); int i = 0; int idx = 0; CellRangeAddress add; // while (rowIterator.hasNext()) { // row = (XSSFRow) rowIterator.next(); // Iterator< Cell> cellIterator = row.cellIterator(); // //System.out.println("i = "+i+", ex:"+ex); // // if (row.getRowNum() > 53) { // break; // } //// if(lastCol-(ex+1) == i) break; // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // for (int f = 0; f < sheet1.getNumMergedRegions(); f++) { // add = sheet1.getMergedRegion(f); // // int col = add.getFirstColumn(); // int rowNum = add.getFirstRow(); // if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) { // System.out.println("col:"+col+" "+",row :"+rowNum); // String b = String.valueOf(sheet1.getRow(rowNum).getCell(col)); // System.out.println(b); // // } // // } // switch (cell.getCellType()) // { // case Cell.CELL_TYPE_FORMULA: // ex++; // switch (cell.getCachedFormulaResultType()) // { // case Cell.CELL_TYPE_NUMERIC: // i = (int)cell.getNumericCellValue(); // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // // // break; // } // break; // case Cell.CELL_TYPE_NUMERIC: // if (cell.getColumnIndex() >= 6) // { // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // } // break; // case Cell.CELL_TYPE_STRING: // add = sheet1.getMergedRegion(cell.getRowIndex()); // // if (cell.getStringCellValue().contentEquals("No.")) // { // colIndex = cell.getColumnIndex(); // } // if (cell.getColumnIndex() == 1) // { // System.out.print( // cell.getStringCellValue() + " \t\t " ); // } // break; // // } // } // // System.out.println(); // } for (int j = 0; j < sheet1.getLastRowNum(); j++) { row = sheet1.getRow(j); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); // if (cell.getColumnIndex() == 1) // { // System.out.println(cell.getStringCellValue()); // } FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == 0 && j > 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { System.exit(k); } if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print((int) cell.getNumericCellValue() + " "); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); System.out.print( cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet1.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { System.out.print( c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } } } System.out.println(""); } System.out.println(colIndex); System.out.println(idx); fis.close(); }
From source file:com.dataart.spreadsheetanalytics.model.A1Address.java
License:Apache License
public static A1Address fromRowColumn(int row, int column) { //check in pool A1Address address = A1AddressPool.get(row, column); if (address != null) { return address; }//from ww w. ja v a 2s .c o m //create A1Address a = new A1Address(); a.row = row; a.column = column; a.address = new CellReference(row, column).formatAsString(); return a; }
From source file:com.haulmont.yarg.formatters.impl.xls.Cell.java
License:Apache License
public CellReference toCellReference() { return new CellReference(row, col); }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes horizontal band//from w w w .j a va2 s .co m * Note: Only one band for row is supported. Now we think that many bands for row aren't usable. * * @param band - band to write * @param templateSheet - template sheet * @param resultSheet - result sheet */ protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) { String rangeName = band.getName(); AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName); if (templateRange == null) { throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName)); } CellReference[] crefs = templateRange.getAllReferencedCells(); CellReference topLeft, bottomRight; AreaReference resultRange; int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand; int rownumBackup = rownum; if (crefs != null) { addRangeBounds(band, crefs); ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>(); int currentRowNum = -1; int currentRowCount = -1; int currentColumnCount = 0; int offset = 0; topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0); // no child bands - merge regions now if (band.getChildrenList().isEmpty()) { copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand, getCellFromReference(crefs[0], templateSheet).getColumnIndex()); } for (CellReference cellRef : crefs) { HSSFCell templateCell = getCellFromReference(cellRef, templateSheet); HSSFRow resultRow; if (templateCell.getRowIndex() != currentRowNum) { //create new row resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand); copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum()); rowsAddedByHorizontalBand += 1; //todo move to options if (templateCell.getCellStyle().getParentStyle() != null && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null && templateCell.getCellStyle().getParentStyle().getUserStyleName() .equals(DYNAMIC_HEIGHT_STYLE)) { //resultRow.setHeight(templateCell.getRow().getHeight()); } else { resultRow.setHeight(templateCell.getRow().getHeight()); } resultRows.add(resultRow); currentRowNum = templateCell.getRowIndex(); currentRowCount++; currentColumnCount = 0; offset = templateCell.getColumnIndex(); } else { // or write cell to current row resultRow = resultRows.get(currentRowCount); currentColumnCount++; } copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band); } bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount); resultRange = new AreaReference(topLeft, bottomRight); areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange), new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange)); bandsToResultRanges.put(band, new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1, resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1, resultRange.getLastCell().getRow() + 1)); } for (BandData child : band.getChildrenList()) { writeBand(child); } // scheduled merge regions if (!band.getChildrenList().isEmpty() && crefs != null) { copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup, getCellFromReference(crefs[0], templateSheet).getColumnIndex()); } rownum += rowsAddedByHorizontalBand; rowsAddedByHorizontalBand = 0; rownum += rowsAddedByVerticalBand; rowsAddedByVerticalBand = 0; }
From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java
License:Open Source License
boolean isCellInsideSelection(int row, int column) { CellReference cellReference = new CellReference(row - 1, column - 1); boolean inside = cellReference.equals(selectedCellReference) || individualSelectedCells.contains(cellReference); if (!inside) { for (CellRangeAddress cra : cellRangeAddresses) { if (cra.isInRange(row - 1, column - 1)) { inside = true;/*from w w w . j a v a 2 s . co m*/ break; } } } return inside; }
From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java
License:Open Source License
/** * Reloads the current selection, but does not take non-coherent selection * into account - discards multiple cell ranges and individually selected * cells.//from www . j a va 2 s . c o m */ protected void reloadCurrentSelection() { cellRangeAddresses.clear(); individualSelectedCells.clear(); if (paintedCellRange != null) { if (selectedCellReference != null) { if (paintedCellRange.isInRange(selectedCellReference.getRow(), selectedCellReference.getCol())) { handleCellRangeSelection(selectedCellReference, paintedCellRange, true); } else { paintedCellRange = null; handleCellAddressChange(selectedCellReference.getRow() + 1, selectedCellReference.getCol() + 1, false); } } else { handleCellRangeSelection( new CellReference(paintedCellRange.getFirstRow(), paintedCellRange.getFirstColumn()), paintedCellRange, true); } } else if (selectedCellReference != null) { handleCellAddressChange(selectedCellReference.getRow() + 1, selectedCellReference.getCol() + 1, false); } else { handleCellAddressChange(1, 1, false); } }
From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java
License:Open Source License
/** * Sets/adds the cell at the given coordinates as/to the current selection. * /*from w w w .j ava 2 s .c o m*/ * @param row * Row index, 1-based * @param column * Column index, 1-based * @param discardOldRangeSelection * true to discard previous selections, false to add to the * current selection */ protected void onCellSelected(int row, int column, boolean discardOldRangeSelection) { CellReference cellReference = new CellReference(row - 1, column - 1); CellReference previousCellReference = selectedCellReference; if (!cellReference.equals(previousCellReference) || discardOldRangeSelection && (!cellRangeAddresses.isEmpty() || !individualSelectedCells.isEmpty())) { handleCellSelection(row, column); selectedCellReference = cellReference; spreadsheet.loadCustomEditorOnSelectedCell(); if (discardOldRangeSelection) { cellRangeAddresses.clear(); individualSelectedCells.clear(); paintedCellRange = spreadsheet.createCorrectCellRangeAddress(row, column, row, column); } ensureClientHasSelectionData(); fireNewSelectionChangeEvent(); } }
From source file:com.vaadin.addon.spreadsheet.CellSelectionManager.java
License:Open Source License
/** * This is called when the sheet's address field has been changed and the * sheet selection and function field must be updated. * /*w ww.j a v a2 s .c om*/ * @param value * New value of the address field */ protected void onSheetAddressChanged(String value, boolean initialSelection) { try { if (value.contains(":")) { CellRangeAddress cra = spreadsheet.createCorrectCellRangeAddress(value); // need to check the range for merged regions MergedRegion region = MergedRegionUtil.findIncreasingSelection( spreadsheet.getMergedRegionContainer(), cra.getFirstRow() + 1, cra.getLastRow() + 1, cra.getFirstColumn() + 1, cra.getLastColumn() + 1); if (region != null) { cra = new CellRangeAddress(region.row1 - 1, region.row2 - 1, region.col1 - 1, region.col2 - 1); } handleCellRangeSelection(cra); selectedCellReference = new CellReference(cra.getFirstRow(), cra.getFirstColumn()); paintedCellRange = cra; cellRangeAddresses.clear(); cellRangeAddresses.add(cra); } else { final CellReference cellReference = new CellReference(value); MergedRegion region = MergedRegionUtil.findIncreasingSelection( spreadsheet.getMergedRegionContainer(), cellReference.getRow() + 1, cellReference.getRow() + 1, cellReference.getCol() + 1, cellReference.getCol() + 1); if (region != null && (region.col1 != region.col2 || region.row1 != region.row2)) { CellRangeAddress cra = spreadsheet.createCorrectCellRangeAddress(region.row1, region.col1, region.row2, region.col2); handleCellRangeSelection(cra); selectedCellReference = new CellReference(cra.getFirstRow(), cra.getFirstColumn()); paintedCellRange = cra; cellRangeAddresses.clear(); cellRangeAddresses.add(cra); } else { handleCellAddressChange(cellReference.getRow() + 1, cellReference.getCol() + 1, initialSelection); paintedCellRange = spreadsheet.createCorrectCellRangeAddress(cellReference.getRow() + 1, cellReference.getCol() + 1, cellReference.getRow() + 1, cellReference.getCol() + 1); selectedCellReference = cellReference; cellRangeAddresses.clear(); } } individualSelectedCells.clear(); spreadsheet.loadCustomEditorOnSelectedCell(); ensureClientHasSelectionData(); fireNewSelectionChangeEvent(); } catch (Exception e) { spreadsheet.getRpcProxy().invalidCellAddress(); } }