private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) { //windows excel example: Sheet1!$C$3:$E$4 CellReference colRef = new CellReference(sheetName, startR, startC, true, true); CellReference colRef2 = new CellReference(sheetName, endR, endC, true, true); return "$" + colRef.getCellRefParts()[2] + "$" + colRef.getCellRefParts()[1] + ":$" + colRef2.getCellRefParts()[2] + "$" + colRef2.getCellRefParts()[1]; }
String col1 = colRef.getCellRefParts()[2]; String col2 = colRef2.getCellRefParts()[2]; c = escapedName + "!$" + col1 + ":$" + col2; String row1 = rowRef.getCellRefParts()[1]; String row2 = rowRef2.getCellRefParts()[1]; if (!row1.equals("0") && !row2.equals("0")) { r = escapedName + "!$" + row1 + ":$" + row2;
/** * Create a pivot table using the AreaReference range on sourceSheet, at the given position. * If the source reference contains a sheet name, it must match the sourceSheet * @param source location of pivot data * @param position A reference to the top left cell where the pivot table will start * @param sourceSheet The sheet containing the source data, if the source reference doesn't contain a sheet name * @throws IllegalArgumentException if source references a sheet different than sourceSheet * @return The pivot table */ @Beta public XSSFPivotTable createPivotTable(final AreaReference source, CellReference position, Sheet sourceSheet) { final String sourceSheetName = source.getFirstCell().getSheetName(); if(sourceSheetName != null && !sourceSheetName.equalsIgnoreCase(sourceSheet.getSheetName())) { throw new IllegalArgumentException("The area is referenced in another sheet than the " + "defined source sheet " + sourceSheet.getSheetName() + "."); } return createPivotTable(position, sourceSheet, wsSource -> { final String[] firstCell = source.getFirstCell().getCellRefParts(); final String firstRow = firstCell[1]; final String firstCol = firstCell[2]; final String[] lastCell = source.getLastCell().getCellRefParts(); final String lastRow = lastCell[1]; final String lastCol = lastCell[2]; final String ref = firstCol+firstRow+':'+lastCol+lastRow; //or just source.formatAsString() wsSource.setRef(ref); }); }
private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) { //windows excel example: Sheet1!$C$3:$E$4 CellReference colRef = new CellReference(sheetName, startR, startC, true, true); CellReference colRef2 = new CellReference(sheetName, endR, endC, true, true); return "$" + colRef.getCellRefParts()[2] + "$" + colRef.getCellRefParts()[1] + ":$" + colRef2.getCellRefParts()[2] + "$" + colRef2.getCellRefParts()[1]; }
private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) { //windows excel example: Sheet1!$C$3:$E$4 CellReference colRef = new CellReference(sheetName, startR, startC, true, true); CellReference colRef2 = new CellReference(sheetName, endR, endC, true, true); return "$" + colRef.getCellRefParts()[2] + "$" + colRef.getCellRefParts()[1] + ":$" + colRef2.getCellRefParts()[2] + "$" + colRef2.getCellRefParts()[1]; }
public static String getColumnName(int index) { CellReference ref = new CellReference(-1, index); return ref.getCellRefParts()[1]; }
public static String getColumnName(int index) { CellReference ref = new CellReference(-1, index); return ref.getCellRefParts()[1]; }
private static String getReferenceBuiltInRecord(String sheetName, int startC, int endC, int startR, int endR) { //windows excel example for built-in title: 'second sheet'!$E:$F,'second sheet'!$2:$3 CellReference colRef = new CellReference(sheetName, 0, startC, true, true); CellReference colRef2 = new CellReference(sheetName, 0, endC, true, true); String escapedName = SheetNameFormatter.format(sheetName); String c; if(startC == -1 && endC == -1) c= ""; else c = escapedName + "!$" + colRef.getCellRefParts()[2] + ":$" + colRef2.getCellRefParts()[2]; CellReference rowRef = new CellReference(sheetName, startR, 0, true, true); CellReference rowRef2 = new CellReference(sheetName, endR, 0, true, true); String r = ""; if(startR == -1 && endR == -1) r = ""; else { if (!rowRef.getCellRefParts()[1].equals("0") && !rowRef2.getCellRefParts()[1].equals("0")) { r = escapedName + "!$" + rowRef.getCellRefParts()[1] + ":$" + rowRef2.getCellRefParts()[1]; } } StringBuffer rng = new StringBuffer(); rng.append(c); if(rng.length() > 0 && r.length() > 0) rng.append(','); rng.append(r); return rng.toString(); }
String col1 = colRef.getCellRefParts()[2]; String col2 = colRef2.getCellRefParts()[2]; c = escapedName + "!$" + col1 + ":$" + col2; String row1 = rowRef.getCellRefParts()[1]; String row2 = rowRef2.getCellRefParts()[1]; if (!row1.equals("0") && !row2.equals("0")) { r = escapedName + "!$" + row1 + ":$" + row2;
titleMap.put(cellRef.getCellRefParts()[2], title.getRichStringCellValue().getString()); for (Cell data : dataRow) { CellReference cellRef = new CellReference(data); String cellTag = cellRef.getCellRefParts()[2]; String name = titleMap.get(cellTag); Field field = fieldMap.get(name);
titleMap.put(cellRef.getCellRefParts()[2], title.getRichStringCellValue().getString()); for (Cell data : dataRow) { CellReference cellRef = new CellReference(data); String cellTag = cellRef.getCellRefParts()[2]; String name = titleMap.get(cellTag); Field field = fieldMap.get(name);
for (Cell title : row) { CellReference cellRef = new CellReference(title); titleMap.put(cellRef.getCellRefParts()[2], title.getRichStringCellValue().getString()); for (Cell data : dataRow) { CellReference cellRef = new CellReference(data); String cellTag = cellRef.getCellRefParts()[2]; String name = titleMap.get(cellTag); ExcelFiledInfo info = infoMap.get(name);
/** * Create a pivot table using the AreaReference range on sourceSheet, at the given position. * If the source reference contains a sheet name, it must match the sourceSheet * @param source location of pivot data * @param position A reference to the top left cell where the pivot table will start * @param sourceSheet The sheet containing the source data, if the source reference doesn't contain a sheet name * @throws IllegalArgumentException if source references a sheet different than sourceSheet * @return The pivot table */ @Beta public XSSFPivotTable createPivotTable(final AreaReference source, CellReference position, Sheet sourceSheet) { final String sourceSheetName = source.getFirstCell().getSheetName(); if(sourceSheetName != null && !sourceSheetName.equalsIgnoreCase(sourceSheet.getSheetName())) { throw new IllegalArgumentException("The area is referenced in another sheet than the " + "defined source sheet " + sourceSheet.getSheetName() + "."); } return createPivotTable(position, sourceSheet, wsSource -> { final String[] firstCell = source.getFirstCell().getCellRefParts(); final String firstRow = firstCell[1]; final String firstCol = firstCell[2]; final String[] lastCell = source.getLastCell().getCellRefParts(); final String lastRow = lastCell[1]; final String lastCol = lastCell[2]; final String ref = firstCol+firstRow+':'+lastCol+lastRow; //or just source.formatAsString() wsSource.setRef(ref); }); }