List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getColumnIndex
@Override
public int getColumnIndex()
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixLeftBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) { if (columnIndex > 1) { fixLeftCell(sheet, resultCell.getRowIndex(), columnIndex - 1, cellStyle); // fix merged left border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); for (int leftIndex = firstRow; leftIndex <= lastRow; leftIndex++) { fixLeftCell(sheet, leftIndex, columnIndex - 1, cellStyle); }/*from w w w . j a v a2 s . c o m*/ break; } } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixRightBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) { fixRightCell(sheet, resultCell.getRowIndex(), columnIndex + 1, cellStyle); // fix merged right border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int regionWidth = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1; for (int rightIndex = firstRow; rightIndex <= lastRow; rightIndex++) { fixRightCell(sheet, rightIndex, columnIndex + regionWidth, cellStyle); }/*from www. j av a 2 s .co m*/ break; } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixUpBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) { if (rowIndex > 0) { // fix simple up border fixUpCell(sheet, rowIndex - 1, columnIndex, cellStyle); // fix merged up border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstColumn = mergedRegion.getFirstColumn(); int lastColumn = mergedRegion.getLastColumn(); for (int upIndex = firstColumn; upIndex <= lastColumn; upIndex++) { fixUpCell(sheet, rowIndex - 1, upIndex, cellStyle); }/*from w w w. j a va2s. co m*/ break; } } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixDownBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex, HSSFCell resultCell) { // fix simple down border fixDownCell(sheet, rowIndex + 1, columnIndex, cellStyle); // fix merged down border for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstColumn = mergedRegion.getFirstColumn(); int lastColumn = mergedRegion.getLastColumn(); int regionHeight = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1; for (int downIndex = firstColumn; downIndex <= lastColumn; downIndex++) { fixDownCell(sheet, rowIndex + regionHeight, downIndex, cellStyle); }//from w w w.ja v a2 s. c o m break; } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomWidthHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell resultCell = dataObject.resultCell; HSSFCell templateCell = dataObject.templateCell; String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); Integer width = (Integer) dataObject.bandData.getParameterValue(paramName); if (width != null) { resultCell.getSheet().setColumnWidth(resultCell.getColumnIndex(), width); }/*from w w w . ja v a2s.c o m*/ } } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes horizontal band//from ww w.j a v a 2 s. com * 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.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes vertical band/* w ww. j av a2s .co m*/ * Note: no child support for vertical band ;) * * @param band - band to write * @param templateSheet - template sheet * @param resultSheet - result sheet */ protected void writeVerticalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) { String rangeName = band.getName(); CellReference[] crefs = getRangeContent(templateWorkbook, rangeName); Set<Integer> addedRowNumbers = new HashSet<Integer>(); if (crefs != null) { addRangeBounds(band, crefs); Bounds thisBounds = templateBounds.get(band.getName()); Bounds parentBounds = templateBounds.get(band.getParentBand().getName()); Range parentRange = bandsToResultRanges.get(band.getParentBand()); int localRowNum = parentBounds != null && parentRange != null ? parentRange.getFirstRow() - 1 + thisBounds.row0 - parentBounds.row0 : rownum; colnum = colnum == 0 ? getCellFromReference(crefs[0], templateSheet).getColumnIndex() : colnum; copyMergeRegions(resultSheet, rangeName, localRowNum, colnum); int firstRow = crefs[0].getRow(); int firstColumn = crefs[0].getCol(); for (CellReference cref : crefs) {//create necessary rows int currentRow = cref.getRow(); final int rowOffset = currentRow - firstRow; if (!rowExists(resultSheet, localRowNum + rowOffset)) { HSSFRow resultRow = resultSheet.createRow(localRowNum + rowOffset); copyPageBreaks(templateSheet, resultSheet, cref.getRow(), resultRow.getRowNum()); } addedRowNumbers.add(cref.getRow()); } CellReference topLeft = null; CellReference bottomRight = null; for (CellReference cref : crefs) { int currentRow = cref.getRow(); int currentColumn = cref.getCol(); final int rowOffset = currentRow - firstRow; final int columnOffset = currentColumn - firstColumn; HSSFCell templateCell = getCellFromReference(cref, templateSheet); resultSheet.setColumnWidth(colnum + columnOffset, templateSheet.getColumnWidth(templateCell.getColumnIndex())); HSSFCell resultCell = copyCellFromTemplate(templateCell, resultSheet.getRow(localRowNum + rowOffset), colnum + columnOffset, band); if (topLeft == null) { topLeft = new CellReference(resultCell); } bottomRight = new CellReference(resultCell); } colnum += crefs[crefs.length - 1].getCol() - firstColumn + 1; AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName); AreaReference resultRange = new AreaReference(topLeft, bottomRight); areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.VERTICAL, templateRange), new Area(band.getName(), Area.AreaAlign.VERTICAL, 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 first level vertical bands we should increase rownum by number of rows added by vertical band //nested vertical bands do not add rows, they use parent space if (BandData.ROOT_BAND_NAME.equals(band.getParentBand().getName())) { List<BandData> sameBands = band.getParentBand().getChildrenByName(band.getName()); if (sameBands.size() > 0 && sameBands.get(sameBands.size() - 1) == band) {//check if this vertical band is last vertical band with same name rownum += addedRowNumbers.size(); // rowsAddedByVerticalBand = 0; } } }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * Method main/*from w ww . j a va2s . co m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out .println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s)."); for (int c = 0; c < row.getLastCellNum(); c++) { HSSFCell cell = row.getCell(c); String value; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; case BLANK: value = "<BLANK>"; break; case BOOLEAN: value = "BOOLEAN value-" + cell.getBooleanCellValue(); break; case ERROR: value = "ERROR value=" + cell.getErrorCellValue(); break; default: value = "UNKNOWN value of type " + cell.getCellTypeEnum(); } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
private Object getCellValue(HSSFCell cell, boolean resolveFormula) { if (cell == null) { return null; }/* ww w .ja va 2s . co m*/ Object result = null; int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; break; case HSSFCell.CELL_TYPE_ERROR: result = "ERROR: " + cell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case HSSFCell.CELL_TYPE_NUMERIC: /*System.out.println("Last evaluated as: " + cell.getNumericCellValue()); result = cell.getNumericCellValue(); break;*/ if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } System.out.println("Numeric cell value == " + result); break; case HSSFCell.CELL_TYPE_STRING: System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\""); result = cell.getRichStringCellValue(); break; } //result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle cellStyle = cell.getCellStyle(); short dataFormat = cellStyle.getDataFormat(); System.out.println("Data format for " + cell.getColumnIndex() + " = " + dataFormat); // assumption is made that dataFormat = 14, // when cellType is HSSFCell.CELL_TYPE_NUMERIC // is equal to a DATE format. //if (dataFormat == 165 || dataFormat == 164 || dataFormat == 14) { if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } if (dataFormat == 0) { // alternative way of getting value : can this be replaced for the entire block result = new HSSFDataFormatter().formatCellValue(cell); } System.out.println("Numeric cell value == " + result); break; case HSSFCell.CELL_TYPE_STRING: //result = cell.getStringCellValue(); result = cell.getRichStringCellValue(); System.out.println("String -> " + result); break; default: break; } if (result instanceof Integer) { return String.valueOf((Integer) result); } else if (result instanceof Double) { return String.valueOf(((Double) result)); //.longValue()); } if (result instanceof Date) { return result; } return result.toString(); }
From source file:Compras.ComparaCotizacion.java
void importaDatos(Workbook wb, int col) { Sheet hoja = wb.getSheetAt(0);/* w w w . j a v a 2s. c om*/ Iterator rowIterator = hoja.rowIterator(); List renglones = new ArrayList(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); List celdas = new ArrayList(); Iterator iterator = hssfRow.cellIterator(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); celdas.add(hssfCell); } renglones.add(celdas); } for (int r = 8; r < renglones.size(); r++) { List aux = (List) renglones.get(r); HSSFCell auxCell = (HSSFCell) aux.get(0); HSSFCell auxCell1 = (HSSFCell) aux.get(1); int res = busca(auxCell.getNumericCellValue(), auxCell1.getNumericCellValue()); if (res != -1) { for (int c = 0; c < aux.size(); c++) { HSSFCell valor = (HSSFCell) aux.get(c); switch (valor.getColumnIndex()) { case 4: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: t_datos.setValueAt("", res, col); break; default: t_datos.setValueAt("", res, col); break; } break; case 7: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt(valor.getNumericCellValue(), res, col + 1); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 1); break; default: t_datos.setValueAt("", res, col + 1); break; } if (t_datos.getValueAt(res, col + 1).toString().compareTo("") != 0) { model.setCeldaEditable(res, col + 3, true); model.setCeldaEditable(res, col + 4, true); } break; case 10: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col + 2); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 2); break; default: t_datos.setValueAt("", res, col + 2); break; } break; } t_datos.setValueAt(false, res, col + 3); t_datos.setValueAt(false, res, col + 4); } } } }