用到的jar有以下幾個
poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
commons-collections4-4.1.jar
xmlbeans-2.6.0.jarimport org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
/**
* @see <a href="https://poi.apache.org/components/spreadsheet/index.html">POI-HSSF and POI-XSSF/SXSSF - Java API To Access Microsoft Excel Format Files</a>
*/
public class Excel {
private boolean isExcel2003 = false;
private Workbook workbook;
private Sheet sheet;
public Excel() throws IOException {
this(Excel.class.getResource("test.xlsx").getPath());
}
public Excel(String path) throws IOException {
this.isExcel2003 = (path.matches("^.+\\.(?i)(xls)$")) ? true : false;
FileInputStream fileInputStream = new FileInputStream(path);
if (this.isExcel2003) {
this.workbook = new HSSFWorkbook(fileInputStream);
} else {
//2007
this.workbook = new XSSFWorkbook(fileInputStream);
}
this.selectSheet(0);
}
public Sheet selectSheet(int index) {
if (index < 0 || index > (this.getSheetsCount() - 1)) return null;
this.sheet = this.workbook.getSheetAt(index);
return this.sheet;
}
public int getSheetsCount() {
return this.workbook.getNumberOfSheets();
}
public void readAllCell(int index) {
this.selectSheet(index);
Iterator<Row> rowIterator = this.sheet.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellTypeEnum()) {
case FORMULA:
System.out.println(cell.getCellFormula());
break;
case STRING:
System.out.println(cell.getStringCellValue());
break;
case NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
default:
break;
}
}
}
}
public Object getCalculateRule(CellReference reference) {
FormulaEvaluator evaluator = this.workbook.getCreationHelper().createFormulaEvaluator();
Row row = this.sheet.getRow(reference.getRow());
Cell cell = row.getCell(reference.getCol());
CellValue value = evaluator.evaluate(cell);
switch (value.getCellTypeEnum()) {
case STRING:
return value.getStringValue();
case NUMERIC:
return value.getNumberValue();
case BOOLEAN:
return value.getBooleanValue();
default:
return null;
}
}
public void setValue(CellReference reference, Object v) {
Row row = this.sheet.getRow(reference.getRow());
if (row == null)
row = sheet.createRow(reference.getRow());
Cell cell = row.getCell(reference.getCol());
if (v instanceof String)
cell.setCellValue((String) v);
else if (v instanceof Double)
cell.setCellValue((Double) v);
else if (v instanceof Boolean)
cell.setCellValue((Boolean) v);
else if (v instanceof Date)
cell.setCellValue((Date) v);
else if (v instanceof Calendar)
cell.setCellValue((Calendar) v);
else if (v instanceof RichTextString)
cell.setCellValue((RichTextString) v);
}
}
