org.apache.poi.ss.usermodel.Sheet Java Examples

@Test
public void poi07() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xlsx");
    Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010 都是可以处理的
    Sheet sheet = workbook.getSheetAt(0);
    Row hssfRow = sheet.getRow(0);
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    DataFormatter formatter = new DataFormatter();
    System.out.println(hssfRow.getCell(0).getNumericCellValue());
    System.out.println(hssfRow.getCell(1).getNumericCellValue());
    System.out.println(hssfRow.getCell(2).getNumericCellValue());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormatString());
    isDate(hssfRow.getCell(0));
    isDate(hssfRow.getCell(1));
    isDate(hssfRow.getCell(2));
    isDate(hssfRow.getCell(3));

}
 

/**
 * 导出
 * 
 * @param templatePath
 *            模板地址，自定义比较好看 classPath下的路径
 * @param colums
 *            导出的数据列 反射用
 * @param clazz
 * @param data
 * @param out
 * @param startRow
 *            从几行开始写数据
 * @return
 * @throws IOException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
public static <T> void doExport(String templatePath, String[] columns, Class<T> clazz, List<T> data,
		OutputStream out, int startRow) throws IOException, IllegalArgumentException, IllegalAccessException {
	ClassPathResource classPathResource = new ClassPathResource(templatePath);
	InputStream inputStream = classPathResource.getInputStream();
	Workbook workbook = new XSSFWorkbook(inputStream);
	Sheet sheet = workbook.getSheetAt(0);
	int size = data.size() + startRow;
	int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
	for (int i = startRow; i < size; i++) {
		Row row = sheet.createRow(i);
		for (int j = 0; j < cellNum; j++) {
			Cell cell = row.createCell(j);
			Field field = ReflectionUtils.findField(clazz, columns[j]);
			if (null == field) {
				throw new ServiceException(columns[j] + " 配置错误");
			}
			ReflectionUtils.makeAccessible(field);
			Object obj = data.get(i - startRow);
			if (field.getType().getSimpleName().equalsIgnoreCase("double")) {
				cell.setCellValue((Double) field.get(obj));
			} else if (field.getType().getSimpleName().equalsIgnoreCase("date")) {
				CellStyle style = workbook.createCellStyle();
				style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
				cell.setCellStyle(style);
				cell.setCellValue((Date) field.get(obj));
			} else {
				cell.setCellValue(field.get(obj) == null ? "" : "" + field.get(obj));
			}
		}
	}
	workbook.write(out);
	workbook.close();
}
 

/**
 * 创建 表头改变
 * 
 * @param entity
 * @param sheet
 * @param workbook
 * @param feildWidth
 */
public int createHeaderRow(ExportParams entity, Sheet sheet, Workbook workbook, int feildWidth) {
	Row row = sheet.createRow(0);
	row.setHeight(entity.getTitleHeight());
	createStringCell(row, 0, entity.getTitle(), getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
	for (int i = 1; i <= feildWidth; i++) {
		createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
	}
	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildWidth));
	if (entity.getSecondTitle() != null) {
		row = sheet.createRow(1);
		row.setHeight(entity.getSecondTitleHeight());
		CellStyle style = workbook.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		createStringCell(row, 0, entity.getSecondTitle(), style, null);
		for (int i = 1; i <= feildWidth; i++) {
			createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, feildWidth));
		return 2;
	}
	return 1;
}
 

/**
 * Read the excel to get the Map of properties for supported locals
 * 
 * @param wb
 *            workbook which is the source
 * @param supportedLocales
 *            supported Locale Iterator
 * @param sheetName
 * @param defaultKeySet
 *            if this parameter is not null: if there is invalid key not
 *            in this set, TranslationImportException.KEY_NOT_FOUND will
 *            throw.
 * @return
 * @throws ValidationException
 * @throws TranslationImportException
 */
public static Map<String, Properties> readExcel(Workbook wb,
        Iterator<Locale> supportedLocales, String sheetName,
        Set<Object> defaultKeySet) throws ValidationException,
        TranslationImportException {
    Sheet sheet = null;
    try {
        sheet = wb
                .getSheet(getDefaultResourceBundle().getString(sheetName));
        if (sheet == null) {
            throw new TranslationImportException();
        }
    } catch (Exception e) {
        throw new TranslationImportException(
                TranslationImportException.Reason.SHEET_NAME_NOT_FOUND);
    }
    return readSheet(sheet, supportedLocales, sheetName, defaultKeySet);
}
 

@Test
public void forcesToRecalculate_Hssf() throws Exception {
  data.wb = new HSSFWorkbook();
  data.wb.createSheet( "sheet1" );
  data.wb.createSheet( "sheet2" );

  setupTransform();
  transform.recalculateAllWorkbookFormulas();

  if ( !data.wb.getForceFormulaRecalculation() ) {
    int sheets = data.wb.getNumberOfSheets();
    for ( int i = 0; i < sheets; i++ ) {
      Sheet sheet = data.wb.getSheetAt( i );
      assertTrue( "Sheet #" + i + ": " + sheet.getSheetName(), sheet.getForceFormulaRecalculation() );
    }
  }
}
 

/**
 * 指定した行の下に行を1行追加する
 * @param sheet
 * @param rowIndex 追加する行数
 * @return 追加した行を返す。
 */
public static Row insertRow(final Sheet sheet, final int rowIndex) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notMin(rowIndex, 0, "rowIndex");

    // 最終行を取得する
    int lastRow = sheet.getLastRowNum();
    if(lastRow < rowIndex) {
        // データが定義されている範囲害の場合は、行を新たに作成して返す。
        return sheet.createRow(rowIndex);
    }

    sheet.shiftRows(rowIndex, lastRow+1, 1);
    return sheet.createRow(rowIndex);
}
 

@Test
public void readSheet_ManageLanguage() throws Exception {
    // given

    Sheet sheet = prepareSheet(null, true, KEY1, "key2");
    FacesContext fc = prepareContext();
    HashSet<Object> defaultKeySet = new HashSet<Object>();
    defaultKeySet.add(KEY1);
    defaultKeySet.add("key2");

    // when
    Map<String, Properties> map = ExcelHandler.readSheet(sheet, fc
            .getApplication().getSupportedLocales(), SHEET_NAME,
            defaultKeySet);

    // then
    Properties props = map.get(DE);
    assertTrue(props.containsKey(KEY1));
    assertEquals(props.get(KEY1), "key1de ");
    props = map.get("de system");
    assertTrue(props.containsKey(KEY1));
    assertEquals(props.get(KEY1), "\nkey1de");

}
 

/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 

public void importExcel(File excelFile) throws IOException {
  List<Map> sheetList = excelToCSV.generateExcelSheets(excelFile);
  FileInputStream inputStream = new FileInputStream(excelFile);
  Workbook workBook = new XSSFWorkbook(inputStream);

  try {
    for (int i = 0; i < sheetList.size(); i++) {
      Sheet sheet = workBook.getSheet(sheetList.get(i).get("name").toString());
      File sheetFile =
          new File(
              excelFile.getParent() + "/" + sheetList.get(i).get("name").toString() + ".csv");
      excelToCSV.writeTOCSV(sheetFile, sheet, 0, 0);
    }

  } catch (Exception e) {
    e.printStackTrace();
  }
}
 

/**
 * Add styled sheet to a workbook.
 * @throws IOException IOException
 */
@Test
public void addsSheetWithStyleToWorkbook() throws IOException {
    try (final Workbook workbook = new XSSFWorkbook()) {
        final Sheet sheet = new XsSheet(new XsRow(new TextCell("text")))
            .with(
                new XsStyle(
                    new ForegroundColor(
                        IndexedColors.GOLD.getIndex()
                    )
                )
            )
            .attachTo(workbook);
        MatcherAssert.assertThat(
            sheet.getRow(0).getCell(0)
                .getCellStyle().getFillForegroundColor(),
            Matchers.equalTo(IndexedColors.GOLD.getIndex())
        );
    }
}
 

private void ensureColumnBounds(Sheet sheet) {
	if (gotBounds)
		return;

	Iterator<Row> iter = sheet.rowIterator();
	firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
	endColumn = 0;
	while (iter.hasNext()) {
		Row row = iter.next();
		short firstCell = row.getFirstCellNum();
		if (firstCell >= 0) {
			firstColumn = Math.min(firstColumn, firstCell);
			endColumn = Math.max(endColumn, row.getLastCellNum());
		}
	}
	gotBounds = true;
}
 

private void createHeader(final Table table, final Translator translator, final int cdcnt, final Sheet exportSheet) {

        Row headerRow = exportSheet.createRow(0);
        for (int c = 0; c < cdcnt; c++) {
            ColumnDescriptor cd = table.getColumnDescriptor(c);
            if (cd instanceof StaticColumnDescriptor) {
                // ignore static column descriptors - of no value in excel download!
                continue;
            }
            String headerKey = cd.getHeaderKey();
            String headerVal = cd.translateHeaderKey() ? translator.translate(headerKey) : headerKey;
            Cell cell = headerRow.createCell(c);
            cell.setCellValue(headerVal);
            cell.setCellStyle(headerCellStyle);
        }
    }
 

/**
 * Constructs a list of indexes where the data will be added in the worksheet
 *
 * @param index           A list of indexes
 * @param worksheet       The worksheet where the data will be added
 * @param rowData         Data that will be added to the worksheet
 * @param rowDelimiter    rowData row delimiter
 * @param columnDelimiter rowData column delimiter
 * @param isRow           true - if the index list (param) contains row indexes
 * @return List of indexes where data will be added in the worksheet
 */
public static List<Integer> processIndex(final String index, final Sheet worksheet, final String rowData, final String rowDelimiter,
                                         final String columnDelimiter, final boolean isRow, final boolean hasHeader) {
    final String[] rows = rowData.split(rowDelimiter);
    String[] indexArray = null;
    if (!StringUtils.isBlank(index)) {
        indexArray = index.split(",");
    }
    int sheetLastRowIndex = worksheet.getLastRowNum();
    if (sheetLastRowIndex > 0) {
        sheetLastRowIndex++;
    }
    final int dataRows = rows.length;
    final int dataColumns = rows[0].split(columnDelimiter).length;
    int headerOffset = 0;
    if (hasHeader) {
        headerOffset = 1;
    }
    if (isRow) {
        return processIndexWithOffset(indexArray, headerOffset, sheetLastRowIndex, sheetLastRowIndex + dataRows);
    } else {
        return processIndexWithOffset(indexArray, 0, 0, dataColumns);
    }
}
 

/**
 * 获取合并单元格的值
 *
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public void setMergedRegion(Sheet sheet) {
	int sheetMergeCount = sheet.getNumMergedRegions();
	
	for (int i = 0; i < sheetMergeCount; i++) {
		// 获取合并单元格位置
		CellRangeAddress ca = sheet.getMergedRegion(i);
		int firstRow = ca.getFirstRow();
		if (startReadPos - 1 > firstRow) {// 如果第一个合并单元格格式在正式数据的上面，则跳过。
			continue;
		}
		int lastRow = ca.getLastRow();
		int mergeRows = lastRow - firstRow;// 合并的行数
		int firstColumn = ca.getFirstColumn();
		int lastColumn = ca.getLastColumn();
		// 根据合并的单元格位置和大小，调整所有的数据行格式，
		for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
			// 设定合并单元格
			sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
			j = j + mergeRows;// 跳过已合并的行
		}
		
	}
}
 

/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
                            Workbook workbook) throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }
    // 获取表头数据
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 得到所有字段
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // 获取实体对象的导出数据
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // 根据表头进行筛选排序
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //下移数据,模拟插入
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
        sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // 合并同类项
    mergeCells(sheet, excelParams, titleHeight);
}
 

/**
 * アノテーション{@link XlsColumn}の属性columnNameで指定した値が、ヘッダーセルに存在するかチェックする。
 * @param sheet
 * @param recordClass
 * @param headers
 * @param reader
 * @param config
 * @throws CellNotFoundException セルが見つからない場合
 */
public static void checkColumns(final Sheet sheet, final Class<?> recordClass,
        final List<RecordHeader> headers, final AnnotationReader reader, final Configuration config)
                throws CellNotFoundException {
    
    List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(recordClass, reader, XlsColumn.class);
    
    for(FieldAccessor property : properties) {
        final XlsColumn column = property.getAnnotationNullable(XlsColumn.class);
        
        if(column.optional()){
            continue;
        }
        
        String columnName = column.columnName();
        boolean find = false;
        for(RecordHeader info: headers){
            if(Utils.matches(info.getLabel(), columnName, config)){
                find = true;
                break;
            }
        }
        if(!find){
            throw new CellNotFoundException(sheet.getSheetName(), columnName);
        }
    }
    
}
 

/**
 * Sets the left border color for a region of cells by manipulating the cell style of the individual
 * cells on the left
 * 
 * @param color The color of the border
 * @param region The region that should have the border
 * @param sheet The sheet that the region is on.
 * @since POI 3.15 beta 2
 */
public static void setLeftBorderColor(int color, CellRangeAddress region, Sheet sheet) {
    int rowStart = region.getFirstRow();
    int rowEnd = region.getLastRow();
    int column = region.getFirstColumn();

    CellPropertySetter cps = new CellPropertySetter(CellUtil.LEFT_BORDER_COLOR, color);
    for (int i = rowStart; i <= rowEnd; i++) {
        cps.setProperty(CellUtil.getRow(i, sheet), column);
    }
}
 

@Test
public void saveMapsTest_IgnoringErrors_File() throws InvalidFormatException, IOException {
	LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
	headerMap.put("primInt", "Primitive Int");
	headerMap.put("fake", "Not Real");

	Map<String, Object> record = new LinkedHashMap<>();
	record.put("primInt", 123);
	record.put("fake", "someValueAnyway");

	Collection<Map<String, Object>> records = Arrays.asList(record);
	File outputFile = createFile("saveMapsTest_IgnoringErrors_File");

	// save it
	Ssio.saveMaps(headerMap, records, outputFile);


	// then parse it
	byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
	Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

	/*** do assertions ***/
	Sheet sheet = workbook.getSheetAt(0);
	Row headerRow = sheet.getRow(0);
	Row dataRow = sheet.getRow(1);

	Cell cell00 = headerRow.getCell(0);
	Cell cell01 = headerRow.getCell(1);
	Cell cell10 = dataRow.getCell(0);
	Cell cell11 = dataRow.getCell(1);


	// texts
	Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
	Assert.assertEquals("Not Real", cell01.getStringCellValue());
	Assert.assertEquals("123", cell10.getStringCellValue());
	Assert.assertEquals("someValueAnyway", cell11.getStringCellValue());

}
 

private static <T> List<T> doParse(Map<String, String> reverseHeaderMap, InputStream inputStream,
								   Class<T> recordClassIfJavaBean, SepRecordType recordType, List<CellError> cellErrors)
		throws InvalidFormatException, InvalidHeaderRowException {

	validateReverseHeaderMap(reverseHeaderMap);

	Workbook workbook = toWorkbook(inputStream);
	if (workbook.getNumberOfSheets() <= 0) {
		return new ArrayList<T>();
	}

	Sheet sheet = workbook.getSheetAt(0);

	// key = columnIndex, value= {propName, headerText}
	Map<Short, ColumnMeta> columnMetaMap = parseHeader(reverseHeaderMap,
			sheet.getRow(0));
	if (columnMetaMap.isEmpty()) {
		throw new InvalidHeaderRowException();
	}

	// now do the data rows
	List<T> records = new ArrayList<T>();
	for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			continue;
		}

		T record = (T) parseDataRow(columnMetaMap, row, rowIndex, recordType, recordClassIfJavaBean,
				cellErrors);
		records.add(record);
	}
	return records;
}
 

public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

private void printSheetContent(Sheet sheet) {
    //printColumnHeads(sheet);
    MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
    CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
    out.format("<tbody>%n");
    Iterator<Row> rows = sheet.rowIterator();
    int rowIndex = 1;
    while (rows.hasNext()) {
        Row row = rows.next();
        out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
        //out.format("    <td class='%s'>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1);
        for (int i = firstColumn; i < endColumn; i++) {
            if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
                String content = "&nbsp;";
                CellStyle style = null;
                if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        style = cell.getCellStyle();
                        content = cellValueHelper.getHtmlValue(cell);
                    }
                }
                if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
                    Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
                    out.format("    <td rowspan='%s' colspan='%s' class='%s' >%s</td>%n",
                        rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
                } else {
                    out.format("    <td class='%s'>%s</td>%n", styleName(style), content);
                }
            }

        }
        out.format("  </tr>%n");
        rowIndex++;
    }
    out.format("</tbody>%n");
}
 

/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 縦方向に結合されている場合、結合情報のコピーする。（XlsArrayColumns用）
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getRowSize(mergedRegion);

    if(mergedSize >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                toCell.getColumnIndex(), mergedRegion.getFirstRow(), toCell.getColumnIndex(), mergedRegion.getLastRow());

        // 結合先のセルの書式も設定する
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, fromCell.getColumnIndex(), toCell.getRowIndex()+i);

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex(), toCell.getRowIndex()+i);
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }
    }

}
 

public ExcelRepository getSheet(int i) {
  Sheet poiSheet = workbook.getSheetAt(i);
  if (poiSheet == null) {
    return null;
  }

  return new ExcelRepository(poiSheet, entityTypeFactory, attributeFactory, cellProcessors);
}
 

public static void readExcelFormula(String fileName) throws IOException{
	
	FileInputStream fis = new FileInputStream(fileName);
	
	//assuming xlsx file
	Workbook workbook = new XSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Iterator<Row> rowIterator = sheet.iterator();
	while (rowIterator.hasNext()) 
       {
		Row row = rowIterator.next();
		Iterator<Cell> cellIterator = row.cellIterator();
           
           while (cellIterator.hasNext()) 
           {
           	Cell cell = cellIterator.next();
           	switch(cell.getCellType()){
           	case Cell.CELL_TYPE_NUMERIC:
           		System.out.println(cell.getNumericCellValue());
           		break;
           	case Cell.CELL_TYPE_FORMULA:
           		System.out.println("Cell Formula="+cell.getCellFormula());
           		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
           		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
           			System.out.println("Formula Value="+cell.getNumericCellValue());
           		}
           	}
           }
       }
}
 

public static Cell create() {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("New-Sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    return cell;
}
 

private Sheet getSheetOfInputStream(@NonNull InputStream fileInputStream, String password) throws IOException {
    if (StringUtil.isBlank(password)) {
        wb = WorkbookFactory.create(fileInputStream);
    } else {
        wb = WorkbookFactory.create(fileInputStream, password);
    }
    return getSheet();
}
 

/**
 * Sets the bottom border style for a region of cells by manipulating the cell style of the individual
 * cells on the bottom
 * 
 * @param border The new border
 * @param region The region that should have the border
 * @param sheet The sheet that the region is on.
 * @since POI 3.16 beta 1
 */
public static void setBorderBottom(BorderStyle border, CellRangeAddress region, Sheet sheet) {
    int colStart = region.getFirstColumn();
    int colEnd = region.getLastColumn();
    int rowIndex = region.getLastRow();
    CellPropertySetter cps = new CellPropertySetter(CellUtil.BORDER_BOTTOM, border);
    Row row = CellUtil.getRow(rowIndex, sheet);
    for (int i = colStart; i <= colEnd; i++) {
        cps.setProperty(row, i);
    }
}
 

@Test
public void testRunReport() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("Borders1.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Borders Test Report 1", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 12, firstNullRow(sheet));
		
		int i = 0;
		assertBorder( sheet, i++, 0, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_THICK, CellStyle.BORDER_THICK, CellStyle.BORDER_THICK, CellStyle.BORDER_THICK );
		
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED );
		
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DASHED, CellStyle.BORDER_DASHED, CellStyle.BORDER_DASHED, CellStyle.BORDER_DASHED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED );
		
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE );
		
	} finally {
		inputStream.close();
	}
}
 

/**
 * Gets the sheet with the specified index.
 * 
 * @param sheetIndex the sheet name
 * @return XceliteSheet object
 */
public XceliteSheet getSheet(String sheetName) {
  Sheet sheet = workbook.getSheet(sheetName);
  if (sheet == null) {
    throw new XceliteException(String.format("Could not find sheet named \"%s\"", sheetName));
  }
  return new XceliteSheetImpl(sheet, file);
}
 

/**
 * 创建表头
 * 
 * @param title
 * @param index
 */
private int createTitleRow(ExportParams title, Sheet sheet, Workbook workbook, int index, List<ExcelExportEntity> excelParams) {
	Row row = sheet.createRow(index);
	int rows = getRowNums(excelParams);
	row.setHeight((short) 450);
	Row listRow = null;
	if (rows == 2) {
		listRow = sheet.createRow(index + 1);
		listRow.setHeight((short) 450);
	}
	int cellIndex = 0;
	CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());
	for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
		ExcelExportEntity entity = excelParams.get(i);
		if (StringUtils.isNotBlank(entity.getName())) {
			createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
		}
		if (entity.getList() != null) {
			List<ExcelExportEntity> sTitel = entity.getList();
			if (StringUtils.isNotBlank(entity.getName())) {
				sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex, cellIndex + sTitel.size() - 1));
			}
			for (int j = 0, size = sTitel.size(); j < size; j++) {
				createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(), titleStyle, entity);
				cellIndex++;
			}
			cellIndex--;
		} else if (rows == 2) {
			createStringCell(listRow, cellIndex, "", titleStyle, entity);
			sheet.addMergedRegion(new CellRangeAddress(index, index + 1, cellIndex, cellIndex));
		}
		cellIndex++;
	}
	return rows;

}