一、POI简介
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
HSSF解析Excel97 - 2007格式(.xls)
XSSF解析Excel2007以上格式(.xlsx)
二、引入Jar
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi-version}</version>
</dependency>
三、HSSF概况
HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。
四、POI EXCEL文档结构类
- HSSFWorkbook --- excel文档对象
- HSSFSheet --- excel的sheet HSSFRow excel的行
- HSSFCell --- excel的单元格 HSSFFont excel字体
- HSSFName --- 名称 HSSFDataFormat 日期格式
- HSSFHeader --- sheet头
- HSSFFooter --- sheet尾
- HSSFCellStyle --- cell样式
- HSSFDateUtil --- 日期
- HSSFPrintSetup --- 打印
- HSSFErrorConstants --- 错误信息表
五、文件上传示例
//接收上传的文件
private File file;
public void setFile(File file) {
this.file = file;
}
//批量区域数据导入
@Action(value = "area_batchImport")
public String batchImport() throws IOException {
List<Area> areas = new ArrayList<>();
//编写解析代码逻辑
//基于.xls格式解析文件
//1、加载Excel文件对象
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
//2、读取一个sheet
HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
//3、读取sheet中每一行
for (Row row : sheet){
//一行数据对应一个区域对象
Area area = new Area();
if (row.getRowNum() == 0){
//跳过第一行表头
continue;
}
//控制结束
if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).getStringCellValue())){
continue;
}
area.setId(row.getCell(0).getStringCellValue());
area.setProvince(row.getCell(1).getStringCellValue());
area.setCity(row.getCell(2).getStringCellValue());
area.setDistrict(row.getCell(3).getStringCellValue());
area.setPostcode(row.getCell(4).getStringCellValue());
//基于pinyin4j生成城市编码和简码
String province = area.getProvince();
String city = area.getCity();
String district = area.getDistrict();
province = province.substring(0,province.length() - 1);
city = city.substring(0,city.length() - 1);
district = district.substring(0,district.length() - 1);
//简码
String[] headArray = PinYin4jUtils.getHeadByString(province + city + district);
StringBuffer buffer = new StringBuffer();
for (String headStr : headArray) {
buffer.append(headStr);
}
String shortcode = buffer.toString();
area.setShortcode(shortcode);
//城市编码
String citycode = PinYin4jUtils.hanziToPinyin(city,"");
area.setCitycode(citycode);
areas.add(area);
}