public CellRangeAddress copy() { return new CellRangeAddress(getFirstRow(), getLastRow(), getFirstColumn(), getLastColumn()); }
Sheet sheet = cell.getSheet(); Workbook wb = sheet.getWorkbook(); Row row = cell.getRow(); int column = cell.getColumnIndex(); for (CellRangeAddress region : sheet.getMergedRegions()) { if (region.isInRange(row.getRowNum(), column)) { if (!useMergedCells) { cell = row.getCell(region.getFirstColumn()); colspan = 1 + region.getLastColumn() - region.getFirstColumn(); cellType = cell.getCachedFormulaResultType(); Font font = wb.getFontAt(style.getFontIndexAsInt());
/** * 判断指定的单元格是否是合并单元格 * * @param sheet {@link Sheet} * @param row 行号 * @param column 列号 * @return 是否是合并单元格 */ public static boolean isMergedRegion(Sheet sheet, int row, int column) { final int sheetMergeCount = sheet.getNumMergedRegions(); CellRangeAddress ca; for (int i = 0; i < sheetMergeCount; i++) { ca = sheet.getMergedRegion(i); if (row >= ca.getFirstRow() && row <= ca.getLastRow() && column >= ca.getFirstColumn() && column <= ca.getLastColumn()) { return true; } } return false; }
protected CellValue getCellValueAt(int index) { if (index < 0 || index >= numOfCells) { throw new IndexOutOfBoundsException("Index must be between 0 and " + (numOfCells - 1) + " (inclusive), given: " + index); } int firstRow = cellRangeAddress.getFirstRow(); int firstCol = cellRangeAddress.getFirstColumn(); int lastCol = cellRangeAddress.getLastColumn(); int width = lastCol - firstCol + 1; int rowIndex = firstRow + index / width; int cellIndex = firstCol + index % width; Row row = sheet.getRow(rowIndex); return (row == null) ? null : evaluator.evaluate(row.getCell(cellIndex)); } }
sheet.setColumnWidth(i,(short)colWidth); org.apache.poi.ss.usermodel.Cell cell = row.getCell(i); if(cell!=null){ continue; cell=row.createCell(i); com.bstek.ureport.model.Cell cellInfo=colCell.get(col); if(cellInfo==null){ Cell cc=rr.getCell(c); if(cc==null){ cc=rr.createCell(c); cc.setCellStyle(style); colSpan--; CellRangeAddress cellRegion=new CellRangeAddress(rowNumber,(rowNumber+rowSpan),i,(i+colSpan)); sheet.addMergedRegion(cellRegion); colSpan--; CellRangeAddress cellRegion=new CellRangeAddress(rowNumber,(rowNumber+rowSpan),i,(i+colSpan)); sheet.addMergedRegion(cellRegion);
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue("This is a test of merging"); sheet.addMergedRegion(new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 2 //last column (0-based) )); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
private void copyRange(CellRangeAddress sourceRange, int deltaX, int deltaY, Sheet sourceClone) { //NOSONAR, it's a bit complex but monolith method, does not make much sense to divide it if(deltaX != 0) horizontalFormulaShifter = FormulaShifter.createForColumnCopy(sourceSheet.getWorkbook().getSheetIndex(sourceSheet), sourceSheet.getSheetName(), sourceRange.getFirstColumn(), sourceRange.getLastColumn(), deltaX, sourceSheet.getWorkbook().getSpreadsheetVersion()); if(deltaY != 0) verticalFormulaShifter = FormulaShifter.createForRowCopy(sourceSheet.getWorkbook().getSheetIndex(sourceSheet), sourceSheet.getSheetName(), sourceRange.getFirstRow(), sourceRange.getLastRow(), deltaY, sourceSheet.getWorkbook().getSpreadsheetVersion()); for(int rowNo = sourceRange.getFirstRow(); rowNo <= sourceRange.getLastRow(); rowNo++) { Row sourceRow = sourceClone.getRow(rowNo); // copy from source copy, original source might be overridden in process! for (int columnIndex = sourceRange.getFirstColumn(); columnIndex <= sourceRange.getLastColumn(); columnIndex++) { Cell sourceCell = sourceRow.getCell(columnIndex); if(sourceCell == null) continue; Row destRow = destSheet.getRow(rowNo + deltaY); if(destRow == null) destRow = destSheet.createRow(rowNo + deltaY); Cell newCell = destRow.getCell(columnIndex + deltaX); if(newCell != null) newCell.setCellType(sourceCell.getCellType()); else newCell = destRow.createCell(columnIndex + deltaX, sourceCell.getCellType()); cloneCellContent(sourceCell, newCell, null); if(newCell.getCellType() == CellType.FORMULA) adjustCellReferencesInsideFormula(newCell, destSheet, deltaX, deltaY); } } }
XSSFWorkbook wb = new XSSFWorkbook(); CellStyle borderStyle = wb.createCellStyle(); borderStyle.setBorderBottom(CellStyle.BORDER_THIN); borderStyle.setBorderLeft(CellStyle.BORDER_THIN); borderStyle.setBorderRight(CellStyle.BORDER_THIN); borderStyle.setBorderTop(CellStyle.BORDER_THIN); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); Sheet sheet = wb.createSheet("Test Sheet"); Row row = sheet.createRow(1); for (int i = 1; i <= 5; ++i) { Cell cell = row.createCell(i); cell.setCellStyle(borderStyle); if (i == 1) { cell.setCellValue("Centred Text"); } } sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));
/** * This should test to see if a cell is in a certain range or not. * If it is in a merged range, then it should return the top left cell. * @throws Exception */ @Test public void testCellMerge() throws Exception { ExcelParser parser = new ExcelParser((Map<String, List<DataListener>>) null); CellRangeAddress[] ranges = new CellRangeAddress[1]; Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); Cell cell = sheet.createRow(2).createCell(2); ranges[0] = new CellRangeAddress(2, 7, 2, 5); cell.setCellValue(FIRST_CELL_CONTENT); cell = sheet.createRow(7).createCell(5); cell.setCellValue(LAST_CELL_VALUE); cell = sheet.createRow(1).createCell(1); assertNull(parser.getRangeIfMerged(cell, ranges)); cell = sheet.getRow(2).createCell(5); cell.setCellValue("wrong"); CellRangeAddress rangeIfMerged = parser.getRangeIfMerged(cell, ranges); assertEquals(FIRST_CELL_CONTENT, sheet.getRow(rangeIfMerged.getFirstRow()).getCell(rangeIfMerged.getFirstColumn()).getStringCellValue()); }
/** * 给sheet加上标题 居中对齐 * @param sheet * @param row * @param length * @param data */ public static void addTitle(Sheet sheet,int row,int length,String data){ Row sheetRow = sheet.createRow(row); for(int i=0;i<length;i++){ sheetRow.createCell(i); } CellStyle style = sheet.getWorkbook().createCellStyle(); // 样式对象 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1); sheet.addMergedRegion(cellRangeAddress); Cell cell = sheetRow.getCell(0); cell.setCellStyle(style); cell.setCellValue(data); }
name = StringUtils.remove(name, ch); Sheet sheet = wb.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); Row headerRow = sheet.createRow(0); CellStyle headerStyle = createHeaderStyle(wb); CellStyle dateStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss")); Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells()); headerCell.setCellValue(columnName); headerCell.setCellStyle(headerStyle); Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); Cell c; c = row.createCell(0); c.setCellValue(error.time); c.setCellStyle(dateStyle); c = row.createCell(1); sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3)); return wb;
public void doExport(){ Collection<CellBean> cellBeans = tableBean.getCellBeans(); if(ObjectHelper.isNotEmpty(cellBeans)){ for(CellBean cellBean:cellBeans){ if(cellBean.getXSize()>1||cellBean.getYSize()>1){ log.debug("有合并单元格:{}", JsonUtil.toJSON(cellBean)); CellRangeAddress range=new CellRangeAddress(cellBean.getRowIndex(),cellBean.getRowIndex()+cellBean.getYSize()-1,cellBean.getColumnIndex(),cellBean.getColumnIndex()+cellBean.getXSize()-1); sheet.addMergedRegion(range); } log.debug("set row:{},column:{},content:{}",cellBean.getRowIndex(),cellBean.getColumnIndex(),cellBean.getContent()); Cell cell = sheet.getRow(cellBean.getRowIndex()).getCell(cellBean.getColumnIndex()); cell.setCellValue(cellBean.getContent()); CellStyle cellStyle = cell.getCellStyle(); if(cellStyle==null){ cellStyle=sheet.getWorkbook().createCellStyle(); } if(cellBean.isAlignCenter()){ cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中 } if(cellBean.isVerticalCenter()){ cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中 } cellStyle.setWrapText(cellBean.isWrapText()); cell.setCellStyle(cellStyle); } } } }
Sheet sheetMacros = wb.createSheet(sheetName); Row row = sheetMacros.createRow(rowNumber); Cell cell = row.createCell(0); cell.setCellValue(sheetName); cell.setCellStyle(h1Style); row = sheetMacros.createRow(rowNumber++); cell = row.createCell(columnIndex); cell.setCellValue(columnNames[columnIndex]); cell.setCellStyle(thStyle); sheetMacros.addMergedRegion(new CellRangeAddress(0, // first row (0-based) 0, // last row (0-based) 0, // first column (0-based)
name = StringUtils.remove(name, ch); Sheet sheet = wb.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); Row headerRow = sheet.createRow(0); CellStyle headerStyle = createHeaderStyle(wb); for (int c = 0; c < report.getColumnNames().size(); c++) { Cell headerCell = headerRow.createCell(c); headerCell.setCellValue(report.getColumnNames().get(c)); headerCell.setCellStyle(headerStyle); Row row = sheet.createRow(r+1); Cell cell = row.createCell(c); if (val instanceof Number) { Number n = (Number) val; cell.setCellValue(n.doubleValue()); } else { String sval = String.valueOf(val); sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(),0, lastColumnIndex - 1)); return wb;
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("linechart"); final int NUM_OF_ROWS = 3; final int NUM_OF_COLUMNS = 10; Cell cell; for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) { row = sheet.createRow((short) rowIndex); for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) { cell = row.createCell((short) colIndex); cell.setCellValue(colIndex * (rowIndex + 1)); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); wb.write(fileOut); fileOut.close();
public void addMarginsAndStylesForAssignmentToShift(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) { int margin = 3; int firstColumn = 0; int lastColumn = (numberOfDays + 1) * margin; for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) { if (sheet.getRow(rowNumber).getCell(columnNumber) == null) { sheet.getRow(rowNumber).createCell(columnNumber); } setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber)); } for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) { sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2)); } }
private Hashtable<String,String> fillMergedVal(Sheet sheet) { Hashtable<String,String> mergerVal = new Hashtable<String,String>(); //will iterate over the Merged cells for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress region = sheet.getMergedRegion(i); //Region of merged cells int colIndex = region.getFirstColumn(); //number of columns merged int rowNum = region.getFirstRow(); //number of rows merged Cell cell = sheet.getRow(rowNum).getCell(colIndex); String cellV= ""; if (cell == null) mergerVal.put(rowNum+":"+colIndex, cellV); else mergerVal.put(rowNum+":"+colIndex, cellV = cell.getStringCellValue()); //fill merged value for region for (rowNum = region.getFirstRow(); rowNum <= region.getLastRow(); rowNum++) for (colIndex = region.getFirstColumn(); colIndex <= region.getLastColumn(); colIndex++) mergerVal.put(rowNum+":"+colIndex, cellV); } return mergerVal; } }
for (int i = 0; i < b.getNumberOfSheets(); i++) { copySheets(book.createSheet(),b.getSheetAt(i)); CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow()+deltaRows, mergedRegion.getLastRow()+deltaRows, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn()); destSheet.addMergedRegion(wrapper.range); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress merged = sheet.getMergedRegion(i); if (merged.isInRange(rowNum, cellNum)) { return merged; if (range.getFirstColumn() < o.range.getFirstColumn() && range.getFirstRow() < o.range.getFirstRow()) { return -1; } else if (range.getFirstColumn() == o.range.getFirstColumn() && range.getFirstRow() == o.range.getFirstRow()) { return 0; } else {
if (c != null) return c; for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) { if (mergedRegion.isInRange(rowIx, colIx)) { Row r = sheet.getRow(mergedRegion.getFirstRow()); if (r != null) { return r.getCell(mergedRegion.getFirstColumn());
HSSFRow newRow = worksheet.getRow(destinationRowNum); HSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else { newRow = worksheet.createRow(destinationRowNum); for (int i = 0; i < sourceRow.getLastCellNum(); i++) { HSSFCell oldCell = sourceRow.getCell(i); HSSFCell newCell = newRow.createCell(i); for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress);