下面列出了org.apache.poi.ss.usermodel.Workbook#getSheet ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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();
}
}
/**
* 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);
}
/**
* 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();
}
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;
}
@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");
}
}
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));
}
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 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;
}
@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();
}
@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);
}
@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"));
}
}
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;
}
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 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;
}
/**
* 正常 - 式を直接指定
*/
@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"));
}
}
/**
* 通用读取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(""));
}
}
/**
* 获取Sheet页面(按名称)
*
* @param wb
* @param sheetName
* @return
*/
public static final Sheet getSheet(Workbook wb, String sheetName) {
return wb.getSheet(sheetName);
}