java POI的简单操作
先引入pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
导出简单操作(如果结合实际场景,那么对应的值应该是从数据库获取):
public static void main(String[] args) {
// 创建一个工作簿
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
try {
FileOutputStream fileOutputStream = new FileOutputStream("D:\\用poi导出的表格.xls");
HSSFSheet one = hssfWorkbook.createSheet("第一页");
HSSFRow row = one.createRow(0); //创建第一行
HSSFCell cell = row.createCell(0); // 创建一个单元格,第一列
cell.setCellValue(1);
row.createCell(1).setCellValue("第二个值");
row.createCell(2).setCellValue("第三个值");
row.createCell(3).setCellValue(true);
// 显示时间格式
HSSFCreationHelper creationHelper = hssfWorkbook.getCreationHelper();
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); // 单元格样式类
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd HH:mm:ss"));
HSSFCell cell4 = row.createCell(4);
cell4.setCellValue(new Date());
cell4.setCellStyle(cellStyle);
// 时间格式第二种方法
HSSFCell cell5 = row.createCell(5);
cell5.setCellValue(Calendar.getInstance());
cell5.setCellStyle(cellStyle);
hssfWorkbook.createSheet("第二页");
hssfWorkbook.createSheet();
hssfWorkbook.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("出错");
}
}
读取简单操作:
public static void main(String[] args) {
try {
// 先转换文件流
FileInputStream fileInputStream = new FileInputStream("D:\\用poi导出的表格.xls");
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fileInputStream);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0); // 获取第一个sheet页
if (StringUtils.isEmpty(sheetAt)) {
return;
}
// 遍历行
for (int i = 0; i <= sheetAt.getLastRowNum(); i++) {
// 拿到每一行
HSSFRow row = sheetAt.getRow(i);
if (row == null) {
continue;
}
// 遍历列 ,根据每行拿到每一列
for (int j = 0; j <= row.getLastCellNum(); j++) {
// 拿到每行中的每列
HSSFCell cell = row.getCell(j);
if (StringUtils.isEmpty(cell)) {
continue;
}
System.out.println(getValue(cell));
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 这就是一个转换打印格式的方法,根据实际业务不同而改变
private static String getValue(HSSFCell cell) {
// 先拿到类型对应的值,再判断
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_BOOLEAN :
return String.valueOf(cell.getBooleanCellValue());
case HSSFCell.CELL_TYPE_NUMERIC :
return String.valueOf(cell.getNumericCellValue());
default:
return cell.getStringCellValue();
}
}