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

@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));

}
 

/**
 * 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);
    }
}
 

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);
        }
    }
 

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;
}
 

/**
 * 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())
        );
    }
}
 

/**
 * 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 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 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;
}
 

/**
 * @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]);
	}
}
 

/**
 * 指定した行の下に行を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 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() );
    }
  }
}
 

/**
 * 获取合并单元格的值
 *
 * @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;// 跳过已合并的行
		}
		
	}
}
 

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();
  }
}
 

public void populateData() throws SQLException {
    Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook();
    int noOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < noOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        ColumnInfo[] headers = this.extractColumnHeaders(sheet);
        DataTable dataTable = new FixedDataTable(sheetName, headers);

        Iterator<Row> rowItr = sheet.rowIterator();
        while (rowItr.hasNext()) {
            Row row = rowItr.next();
            if (row.getRowNum() != 0) {
                DataRow dataRow = new DataRow(row.getRowNum() - 1);
                Iterator<Cell> cellItr = row.cellIterator();
                int cellIndex = 0;
                while (cellItr.hasNext()) {
                    Cell cell = cellItr.next();
                    DataCell dataCell =
                            new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell));
                    dataRow.addCell(dataCell.getColumnId(), dataCell);
                    cellIndex++;
                }
                dataTable.addRow(dataRow);
            }
        }
        this.getData().put(dataTable.getTableName(), dataTable);
    }
}
 

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

	InputStream inputStream = runAndRenderReport("AutoRowHeight.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Auto RowHeight Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 7, this.firstNullRow(sheet));
		
		assertEquals( 275, sheet.getRow(0).getHeight() );
		assertEquals( 992, sheet.getRow(1).getHeight() );
		assertEquals( 826, sheet.getRow(2).getHeight() );
		assertEquals( 1405, sheet.getRow(3).getHeight() );
		assertEquals( 2988, sheet.getRow(4).getHeight() );
		assertEquals( 275, sheet.getRow(5).getHeight() );
		assertEquals( 4103, sheet.getRow(6).getHeight() );
		

	} finally {
		inputStream.close();
	}
}
 

/**
 * 获取小区
 *
 * @param workbook
 * @param floors
 */
private void getFloors(Workbook workbook, List<ImportFloor> floors) {
    Sheet sheet = null;
    sheet = ImportExcelUtils.getSheet(workbook, "楼栋单元");
    List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
    ImportFloor importFloor = null;
    for (int osIndex = 0; osIndex < oList.size(); osIndex++) {
        Object[] os = oList.get(osIndex);
        if (osIndex == 0) { // 第一行是 头部信息 直接跳过
            continue;
        }

        if (StringUtil.isNullOrNone(os[0])) {
            continue;
        }

        Assert.hasLength(os[0].toString(), "楼栋单元选项中" + (osIndex + 1) + "行楼栋号为空");
        Assert.hasLength(os[1].toString(), "楼栋单元选项中" + (osIndex + 1) + "行单元编号为空");
        Assert.hasLength(os[2].toString(), "楼栋单元选项中" + (osIndex + 1) + "行总楼层为空");
        Assert.hasLength(os[3].toString(), "楼栋单元选项中" + (osIndex + 1) + "行是否有电梯为空");
        importFloor = new ImportFloor();
        importFloor.setFloorNum(os[0].toString());
        importFloor.setUnitNum(os[1].toString());
        importFloor.setLayerCount(os[2].toString());
        importFloor.setLift("有".equals(os[3].toString()) ? "1010" : "2020");
        floors.add(importFloor);
    }
}
 

private void serializeColumns(Workbook workbook, JsonGenerator generator, Sheet sheet, List<ColumnMetadata> columns)
        throws IOException {

    for (int i = 0, size = sheet.getLastRowNum(); i <= size; i++) {
        if (limit > 0 && i > limit) {
            break;
        }
        // is header line?
        Row row = sheet.getRow(i);
        if (isHeaderLine(i, columns) || row == null) {
            continue;
        }

        generator.writeStartObject();
        for (ColumnMetadata columnMetadata : columns) {

            // do not write the values if this has been detected as an header
            if (i < columnMetadata.getHeaderSize()) {
                continue;
            }

            int colId = Integer.parseInt(columnMetadata.getId());
            String cellValue =
                    getCellValueAsString(row.getCell(colId), workbook.getCreationHelper().createFormulaEvaluator());
            LOG.trace("cellValue for {}/{}: {}", i, colId, cellValue);
            generator.writeFieldName(columnMetadata.getId());
            if (cellValue != null) {
                generator.writeString(cellValue);
            } else {
                generator.writeNull();
            }
        }
        generator.writeEndObject();
    }
}
 

/**
 * 解析表格
 * @param workbook
 * @return
 */
protected List<List<Object>> parseSheet(Workbook workbook) {
	List<List<Object>> data = new ArrayList<List<Object>>();
	Iterator<Sheet> sheetIterator = workbook.iterator();
	
	int sheetCount=0;
	List<Object> headerRowData=null;
	// 遍历excel多个sheet
	while (sheetIterator.hasNext()) {
		Sheet sheet = sheetIterator.next();
		Iterator<Row> rowIterator = sheet.iterator();
		int rowNumber=0;
		// 遍历行
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			List<Object> rowData = parseRow(row);

			//获取第一页 第一行数据
			if(sheetCount==0&&rowNumber==0){
				headerRowData=rowData;
			}
			//比較剩餘sheet第一行数据是否相等
			if(sheetCount>1&&rowNumber==0){
				if(compareRow(headerRowData,rowData)){
					continue;
				}
			}
			data.add(rowData);
			
			rowNumber++;
		}
		
		sheetCount++;
	}
	return data;
}
 

/**
 * {@inheritDoc}
 */
@Override
public Sheet getSheet(String name) {
  int index = getSheetIndex(name);
  if(index == -1) {
    throw new MissingSheetException("Sheet '" + name + "' does not exist");
  }
  return reader.getSheets().get(index);
}
 

private static void parseTemplate(Sheet sheet, Map<String, Object> map)
		throws Exception {
	Iterator<Row> rows = sheet.rowIterator();
	Row row;
	while (rows.hasNext()) {
		row = rows.next();
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			setValueForCellByMap(row.getCell(i), map);
		}
	}
}
 

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");
}
 

/**
 * Compute width of a column based on a subset of the rows and return the result
 *
 * @param sheet the sheet to calculate
 * @param column    0-based index of the column
 * @param useMergedCells    whether to use merged cells
 * @param firstRow  0-based index of the first row to consider (inclusive)
 * @param lastRow   0-based index of the last row to consider (inclusive)
 * @return  the width in pixels or -1 if cell is empty
 */
public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow){
    DataFormatter formatter = new DataFormatter();
    int defaultCharWidth = getDefaultCharWidth(sheet.getWorkbook());

    double width = -1;
    for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
        Row row = sheet.getRow(rowIdx);
        if( row != null ) {
            double cellWidth = getColumnWidthForRow(row, column, defaultCharWidth, formatter, useMergedCells);
            width = Math.max(width, cellWidth);
        }
    }
    return width;
}
 

public Object getCellValueByFetchingLastCachedValue(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);

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

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            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 writeRow(List<Object> values, Sheet sheet, int rowNr) {
  final Row row = sheet.createRow(rowNr);
  AtomicInteger counter = new AtomicInteger(0);
  values.forEach(
      record -> {
        int index = counter.getAndIncrement();
        if (record != null) {
          Cell cell = row.createCell(index);
          setCellValue(cell, record);
        }
      });
}
 

/**
 * 创建表头
 * 
 * @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;

}
 

/**
 * 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);
}
 

@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();
	}
}
 

/**
 * 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);
    }
}
 

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();
}
 

@Override
public List<String> mapTitleRow(Sheet sheet) {
	try {
		Row titleRow = sheet.getRow(getTitleRowIndex());
		return ExcelUtils.getRowValues(titleRow);
	} catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("mapTitleRow error" , e);
	}
}