Java Code Examples for org.apache.poi.ss.usermodel.Workbook#getSheet()

@Test (description = "Test the conversion of XLS to JSON.")
public void testConvertXLSToJSON() throws IOException {
    InputStream inputStream = getInputStreamForFile(XLS_FILENAME);
    Workbook testWorkBook = new HSSFWorkbook(inputStream);
    Sheet sheet = testWorkBook.getSheet(testWorkBook.getSheetName(0));

    String usersJson = jsonConverter.xlsToJSON(sheet);
    Assert.assertEquals(usersJson, USERS_LIST_JSON);
}
 

/**
 * Determine the version of an existing workbook
 * @param workbook
 * @param originSheetName
 * @return
 * @throws SpreadsheetException 
 */
private String readVersion(Workbook workbook, String originSheetName) throws SpreadsheetException {
	Sheet sheet = workbook.getSheet(originSheetName);
	if (sheet == null) {
		throw new SpreadsheetException("Invalid SPDX spreadsheet.  Sheet "+originSheetName+" does not exist.");
	}
	int firstRowNum = sheet.getFirstRowNum();
	Row dataRow = sheet.getRow(firstRowNum + DocumentInfoSheet.DATA_ROW_NUM);
	if (dataRow == null) {
		return UNKNOWN_VERSION;
	}
	Cell versionCell = dataRow.getCell(DocumentInfoSheet.SPREADSHEET_VERSION_COL);
	if (versionCell == null) {
		return UNKNOWN_VERSION;
	}
	return versionCell.getStringCellValue();
}
 

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

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

private int executeSQL() throws SQLException {
    Map<Integer, DataRow> result;
    if (getCondition().getLhs() == null && getCondition().getRhs() == null) {
        result = getTargetTable().getRows();
    } else {
        result = getCondition().process(getTargetTable());
    }

    if (!(getConnection() instanceof TExcelConnection)) {
        throw new SQLException("Connection does not refer to a Excel connection");
    }
    TExcelConnection excelConnection = (TExcelConnection) this.getConnection();
    //begin transaction,
    excelConnection.beginExcelTransaction();
    Workbook workbook = excelConnection.getWorkbook();
    Sheet sheet = workbook.getSheet(getTargetTableName());
    if (sheet == null) {
        throw new SQLException("Excel sheet named '" + this.getTargetTableName() +
                "' does not exist");
    }

    ColumnInfo[] headers = TDriverUtil.getHeaders(getConnection(), getTargetTableName());
    for (Map.Entry<Integer, DataRow> row : result.entrySet()) {
        Row updatedRow = sheet.getRow(row.getKey() + 1);
        for (ColumnInfo column : getTargetColumns()) {
            int columnId = findColumnId(headers, column.getName());
            updatedRow.getCell(columnId).setCellValue(column.getValue().toString());
        }
    }
    TDriverUtil.writeRecords(workbook, ((TExcelConnection) getConnection()).getPath());
    return 0;
}
 

public static Sheet getWorksheet(final Workbook excelDoc, final String sheetName) throws ExcelOperationException {
    final Sheet worksheet = excelDoc.getSheet(sheetName);
    if (worksheet == null) {
        throw new ExcelOperationException("Worksheet " + sheetName + " does not exist.");
    }
    return worksheet;
}
 

private Sheet retrieveSheet(Workbook wb, String name, int sheetIndex) throws ModuleException {
	Sheet sheet = null;
	if (name != null) {
		this.audit.addLog(AuditLogStatus.SUCCESS, "Accessing sheet " + name);
		sheet = wb.getSheet(name);	
		if (sheet == null) {
			throw new ModuleException("Sheet " + name + " not found");
		}
	} else {
		sheet = wb.getSheetAt(sheetIndex);
		this.audit.addLog(AuditLogStatus.SUCCESS, "Accessing sheet " + sheet.getSheetName() + " at index " + sheetIndex);			
	}
	return sheet;
}
 

@Test
void iteratorHeaderCaseSensitive() throws IOException {
  String fileName = "/case-sensitivity.xlsx";
  try (InputStream inputStream = getClass().getResourceAsStream(fileName)) {
    Workbook workbook = WorkbookFactory.create(inputStream);
    ExcelRepository excelRepository =
        new ExcelRepository(
            workbook.getSheet("case-sensitivity"), entityTypeFactory, attrMetaFactory);
    Entity entity = excelRepository.iterator().next();
    assertEquals("Value #0", entity.get("Header"));
    assertNull(entity.get("hEADER"));
  }
}
 

@Before
public void setupBefore() throws Exception {
     Workbook workbook = WorkbookFactory.create(new FileInputStream(new File("src/test/data/utils.xlsx")));
     this.sheet = workbook.getSheet("CellFinder");
     
     this.config = new Configuration();
}
 

private Sheet getSheetToProcess(Workbook workbook, PoijiOptions options, String sheetName) {
    int nonHiddenSheetIndex = 0;
    int requestedIndex = options.sheetIndex();
    Sheet sheet = null;
    if (options.ignoreHiddenSheets()) {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            if (!workbook.isSheetHidden(i) && !workbook.isSheetVeryHidden(i)) {
                if (sheetName == null) {
                    if (nonHiddenSheetIndex == requestedIndex) {
                        return workbook.getSheetAt(i);
                    }
                } else {
                    if (workbook.getSheetName(i).equalsIgnoreCase(sheetName)) {
                        return workbook.getSheetAt(i);
                    }
                }
                nonHiddenSheetIndex++;
            }
        }
    } else {
        if (sheetName == null) {
            sheet = workbook.getSheetAt(requestedIndex);
        } else {
            sheet = workbook.getSheet(sheetName);
        }
    }
    return sheet;
}
 

public Object runPreQuery(InternalParamCollection params, int queryLevel)
        throws DataServiceFault {
    try {
        Workbook wb = this.getConfig().createWorkbook();
        return wb.getSheet(this.getWorkbookName());
    } catch (Exception e) {
        throw new DataServiceFault(e, "Error in ExcelQuery.runQuery.");
    }
}
 

private String[] getHeader() throws Exception {
    if (!this.isHasHeader()) {
        return null;
    }
    Workbook wb = this.getConfig().createWorkbook();
    Sheet sheet = wb.getSheet(this.getWorkbookName());
    return this.extractRowData(sheet.getRow(this.getHeaderRow() - 1));
}
 

@SuppressWarnings("deprecation")
@Test
void iteratorDuplicateSheetHeader() throws IOException {
  String fileName = "/duplicate-sheet-header.xlsx";
  try (InputStream inputStream = getClass().getResourceAsStream(fileName)) {
    Workbook workbook = WorkbookFactory.create(inputStream);
    ExcelRepository excelRepository =
        new ExcelRepository(workbook.getSheet("attributes"), entityTypeFactory, attrMetaFactory);
    Exception exception = assertThrows(MolgenisDataException.class, excelRepository::iterator);
    assertThat(exception.getMessage())
        .containsPattern("Duplicate column header 'entity' in sheet 'attributes' not allowed");
  }
}
 

/**
 * 通用读取Excel
 *
 * @Title: readExcel
 * @Date : 2014-9-11 上午11:26:53
 * @param wb
 * @return
 */
public List<List<Row>> readExcel(Workbook wb) {
	List<List<Row>> list = new ArrayList<>();
	List<Row> rowList = new ArrayList<>();
	int sheetCount = 1;// 需要操作的sheet数量
	
	Sheet sheet = null;
	if (onlyReadOneSheet) { // 只操作一个sheet
		// 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
		sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
	} else { // 操作多个sheet
		sheetCount = wb.getNumberOfSheets();// 获取可以操作的总数量
	}
	
	// 获取sheet数目
	for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
		// 获取设定操作的sheet
		if (!onlyReadOneSheet) {
			sheet = wb.getSheetAt(t);
		}
		
		// 获取最后行号
		int lastRowNum = sheet.getLastRowNum();
		
		if (lastRowNum > 0) { // 如果>0，表示有数据
			out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
		}
		
		Row row = null;
		// 循环读取
		for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
			row = sheet.getRow(i);
			if (row != null) {
				rowList.add(row);
				out("第" + (i + 1) + "行：", false);
				// 获取每一单元格的值
				for (int j = 0; j < row.getLastCellNum(); j++) {
					String value = getCellValue(row.getCell(j));
					if (!value.equals("")) {
						out(value + " | ", false);
					}
				}
				out("");
			}
			
		}
		list.add(rowList);
	}
	return list;
}
 

/**
 * 正常 - メソッドで式を組み立て
 */
@Test
public void test_normal_method() throws Exception {

    // テストデータの作成
    final FormulaSheet outSheet = new FormulaSheet();

    // アノテーションの組み立て
    AnnotationMappingInfo xmlInfo = createXml()
            .classInfo(createClass(FormulaSheet.class)
                    .field(createField("c1")
                            .override(true)
                            .annotation(createAnnotation(XlsFormula.class)
                                    .attribute("methodName", "getC1Formula")
                                    .buildAnnotation())
                            .buildField())
                    .buildClass())
            .buildXml();


    // ファイルへの書き込み
    XlsMapper mapper = new XlsMapper();
    mapper.getConfiguration().setContinueTypeBindFailure(true)
        .setAnnotationMapping(xmlInfo);;

    File outFile = new File(OUT_DIR, outFilename);
    try(InputStream template = new FileInputStream(templateFile);
            OutputStream out = new FileOutputStream(outFile)) {

        mapper.save(template, out, outSheet);
    }

    // 書き込んだファイルを読み込み値の検証を行う。
    try(InputStream in = new FileInputStream(outFile)) {

        Workbook book = WorkbookFactory.create(in);
        Sheet sheet = book.getSheet("Formula(通常)");

        Cell cell = POIUtils.getCell(sheet, CellPosition.of("A2"));

        String formula = cell.getCellFormula();
        CellFormatter formatter = mapper.getConfiguration().getCellFormatter();
        String value = formatter.format(cell);

        assertThat(formula, is("SUM(D2:D3)"));
        assertThat(value, is("579"));

    }

}
 

/**
 * {@link XlsSheet} - 正規表現の場合のシートのコピー
 */
@Test
public void test_Sheet_regex_clone() throws Exception {

    // 正規表現による複数のシートを出力する場合。
    // 書き込み時に、シート名を設定して、一意に関連づけます。
    SheetRegexClone sheet1 = new SheetRegexClone();
    sheet1.sheetName = "Sheet_1"; // シート名の設定

    SheetRegexClone sheet2 = new SheetRegexClone();
    sheet2.sheetName = "Sheet_2"; // シート名の設定

    SheetRegexClone sheet3 = new SheetRegexClone();
    sheet3.sheetName = "Sheet_3"; // シート名の設定

    SheetRegexClone[] sheets = new SheetRegexClone[]{sheet1, sheet2, sheet3};

    // シートのクローン
    Workbook workbook = WorkbookFactory.create(new FileInputStream("src/test/data/sample_template.xlsx"));
    Sheet templateSheet = workbook.getSheet("XlsSheet(regexp)");
    for(SheetRegexClone sheetObj : sheets) {
        int sheetIndex = workbook.getSheetIndex(templateSheet);
        Sheet cloneSheet = workbook.cloneSheet(sheetIndex);
        workbook.setSheetName(workbook.getSheetIndex(cloneSheet), sheetObj.sheetName);
    }

    // コピー元のシートを削除する
    workbook.removeSheetAt(workbook.getSheetIndex(templateSheet));

    // クローンしたシートファイルを、一時ファイルに一旦出力する。
    File cloneTemplateFile = File.createTempFile("template", ".xlsx");
    workbook.write(new FileOutputStream(cloneTemplateFile));

    // 複数のシートの書き込み
    XlsMapper xlsMapper = new XlsMapper();
    xlsMapper.saveMultiple(
            new FileInputStream(cloneTemplateFile), // クローンしたシートを持つファイルを指定する
            new FileOutputStream(new File(OUT_DIR, "sample_out.xlsx")),
            sheets);

}
 

/**
 * 正常 - 空の数式を返す場合
 */
@Test
public void test_normal_empty_formula() throws Exception {

    // テストデータの作成
    final FormulaSheet outSheet = new FormulaSheet();

    // アノテーションの組み立て
    AnnotationMappingInfo xmlInfo = createXml()
            .classInfo(createClass(FormulaSheet.class)
                    .field(createField("c1")
                            .override(true)
                            .annotation(createAnnotation(XlsFormula.class)
                                    .attribute("methodName", "getEmptyFormula")
                                    .buildAnnotation())
                            .buildField())
                    .buildClass())
            .buildXml();


    // ファイルへの書き込み
    XlsMapper mapper = new XlsMapper();
    mapper.getConfiguration().setContinueTypeBindFailure(true)
        .setAnnotationMapping(xmlInfo);

    File outFile = new File(OUT_DIR, outFilename);
    try(InputStream template = new FileInputStream(templateFile);
            OutputStream out = new FileOutputStream(outFile)) {

        mapper.save(template, out, outSheet);
    }

    // 書き込んだファイルを読み込み値の検証を行う。
    try(InputStream in = new FileInputStream(outFile)) {

        Workbook book = WorkbookFactory.create(in);
        Sheet sheet = book.getSheet("Formula(通常)");

        Cell cell = POIUtils.getCell(sheet, CellPosition.of("A2"));
        assertThat(cell.getCellTypeEnum(), is(CellType.BLANK));

        CellFormatter formatter = mapper.getConfiguration().getCellFormatter();
        String value = formatter.format(cell);

        assertThat(value, is(""));

    }

}
 

/**
 * 正常 - 式を直接指定
 */
@Test
public void test_normal_formula() throws Exception {

    // テストデータの作成
    final FormulaSheet outSheet = new FormulaSheet();

    // アノテーションの組み立て
    AnnotationMappingInfo xmlInfo = createXml()
            .classInfo(createClass(FormulaSheet.class)
                    .field(createField("c1")
                            .override(true)
                            .annotation(createAnnotation(XlsFormula.class)
                                    .attribute("value", "SUM(C2:${x:colToAlpha(columnNumber+2)}3)")
                                    .buildAnnotation())
                            .buildField())
                    .buildClass())
            .buildXml();


    // ファイルへの書き込み
    XlsMapper mapper = new XlsMapper();
    mapper.getConfiguration().setContinueTypeBindFailure(true)
        .setAnnotationMapping(xmlInfo);

    File outFile = new File(OUT_DIR, outFilename);
    try(InputStream template = new FileInputStream(templateFile);
            OutputStream out = new FileOutputStream(outFile)) {

        mapper.save(template, out, outSheet);
    }

    // 書き込んだファイルを読み込み値の検証を行う。
    try(InputStream in = new FileInputStream(outFile)) {

        Workbook book = WorkbookFactory.create(in);
        Sheet sheet = book.getSheet("Formula(通常)");

        Cell cell = POIUtils.getCell(sheet, CellPosition.of("A2"));

        String formula = cell.getCellFormula();
        CellFormatter formatter = mapper.getConfiguration().getCellFormatter();
        String value = formatter.format(cell);

        assertThat(formula, is("SUM(C2:C3)"));
        assertThat(value, is("17.468"));

    }

}
 

private synchronized int executeSQL() throws SQLException {
    int rowCount = 0;
    if (!(getConnection() instanceof TExcelConnection)) {
        throw new SQLException("Connection does not refer to a Excel connection");
    }
    TExcelConnection excelConnection = (TExcelConnection) this.getConnection();
    //begin transaction,
    excelConnection.beginExcelTransaction();
    Workbook workbook = excelConnection.getWorkbook();
    Sheet sheet = workbook.getSheet(getTargetTableName());
    if (sheet == null) {
        throw new SQLException("Excel sheet named '" + this.getTargetTableName() +
                "' does not exist");
    }
    int lastRowNo = sheet.getLastRowNum();

    if (getParameters() != null) {
        Row row = sheet.createRow(lastRowNo + 1);
        for (ParamInfo param : getParameters()) {
            Cell cell = row.createCell(param.getOrdinal());
            switch (param.getSqlType()) {
                case Types.VARCHAR:
                    cell.setCellValue((String) param.getValue());
                    break;
                case Types.INTEGER:
                    cell.setCellValue((Integer) param.getValue());
                    break;
                case Types.DOUBLE:
                    cell.setCellValue((Double) param.getValue());
                    break;
                case Types.BOOLEAN:
                    cell.setCellValue((Boolean) param.getValue());
                    break;
                case Types.DATE:
                    cell.setCellValue((Date) param.getValue());
                    break;
                default:
                    cell.setCellValue((String) param.getValue());
                    break;
            }
        }
        rowCount++;
    }
    TDriverUtil.writeRecords(workbook, ((TExcelConnection) getConnection()).getPath());
    return rowCount;
}
 

/**
 * 获取Sheet页面(按名称)
 *
 * @param wb
 * @param sheetName
 * @return
 */
public static final Sheet getSheet(Workbook wb, String sheetName) {
    return wb.getSheet(sheetName);
}