使用POI进行Excel表格的基本导入导出
- 导包,采用maven添加依赖
<!-- poi支持的jar包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11</version>
</dependency>
- 导出为Execl
@Test
public void testWrite() throws Exception {
//创建工作簿,SXSSFWorkbook支持大数据量的导出
SXSSFWorkbook workbook = new SXSSFWorkbook();
//创建sheet表
Sheet sheet = workbook.createSheet("99乘法表");
for (int i = 1; i <= 9; i++) {
//创建行
Row row = sheet.createRow(i - 1);
for (int j = 1; j <= i; j++) {
Cell cell = row.createCell(j - 1);//创建单元格
cell.setCellValue(j + "*" + i + "=" + (i * j));
}
}
FileOutputStream os = new FileOutputStream("乘法表.xlsx");
BufferedOutputStream bos = new BufferedOutputStream(os);
workbook.write(bos);
bos.flush();
bos.close();
os.close();
}
- 读取Excel
@Test
public void testRead() throws Exception {
FileInputStream in = new FileInputStream("emp.xlsx");
BufferedInputStream bis = new BufferedInputStream(in);
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook(bis);//注意07的版本使用这个
//按索引读取sheet表
XSSFSheet sheet = workbook.getSheetAt(0);
//取得所有的行数
int rowNum = sheet.getLastRowNum();
for (int i = 2; i <= rowNum; i++) {
//读取1行
XSSFRow row = sheet.getRow(i);
//读取每行的列数
short cellNum = row.getLastCellNum();
for (int j = 0; j < cellNum; j++) {
//读取每个单元格
XSSFCell cell = row.getCell(j);
String value = getCellValue(cell);
System.out.print(value + "\t\t\t");
}
System.out.println();
}
}
private String getCellValue(Cell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
// 如果为时间格式的内容
if (DateUtil.isCellDateFormatted(cell)) {
//注:format格式 yyyy-MM-dd
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(cell.getDateCellValue());
} else {
//有小数保留两位,位数不够自动忽略
return new DecimalFormat("#.##").format(cell.getNumericCellValue());
}
case Cell.CELL_TYPE_STRING: // 字符串
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN: // Boolean
return cell.getBooleanCellValue() + "";
case Cell.CELL_TYPE_FORMULA: // 公式
return cell.getCellFormula() + "";
case Cell.CELL_TYPE_BLANK: // 空值
return "";
case Cell.CELL_TYPE_ERROR: // 故障
return "非法字符";
default:
return "未知类型";
}
}