前两篇文章都是创建Excel文档,那么如何将Excel文档读取出来进行操作呢?
一、加载解析Excel文档
比如说当前有一个Excel文档里,如何将这些内容读取出来呢?
1.我们需要获取到这个Excel文档
2.获取需要操作的sheet表,从0下标开始代表为第一页
3.获取sheet表里的每一行、从0下标开始代表为第一行
4.获取sheet表里的每一列、从0下标开始代表为第一列
//单元格样式
public static void main(String[] args) throws Exception {
//1.创建workbook工作簿
Workbook wb = new XSSFWorkbook("E:\\demo.xlsx");
//2.获取sheet 从0开始
Sheet sheet = wb.getSheetAt(0);
//循环所有行getLastRowNum指的是sheet表里的最后一行
for (int rowNum = 0; rowNum <sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);//获取行对象
//循环每行中的所有单元格
for(int cellNum = 0; cellNum < row.getLastCellNum();cellNum++) {
Cell cell = row.getCell(cellNum);//获取单元格列对象
}
}
}
如图发现,其实单元格第一列、第二列是没有内容的,其实可以从第三列开始
那么我们在操作excel单元格的时候呢,会有不同的属性类型的
我们读取的时候,要根据当前单元格的属性,赋值不同的数据类型
//获取数据
private static Object getValue(Cell cell) {
Object value = null;
switch (cell.getCellType()) {
case STRING: //字符串类型
value = cell.getStringCellValue();
break;
case BOOLEAN: //boolean类型
value = cell.getBooleanCellValue();
break;
case NUMERIC: //数字类型(包含日期和普通数字)
if(DateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
}else{
value = cell.getNumericCellValue();
}
break;
case FORMULA: //公式类型
value = cell.getCellFormula();
break;
default:
break;
}
return value;
}
我们可以打印输出校验获取的数据是否与Excel文档里的一致
//单元格样式
public static void main(String[] args) throws Exception {
//1.创建workbook工作簿
Workbook wb = new XSSFWorkbook("E:\\demo.xlsx");
//2.获取sheet 从0开始
Sheet sheet = wb.getSheetAt(0);
//3.创建行对象、列对象 避免重复浪费
Row row = null;
Cell cell = null;
//循环所有行getLastRowNum指的是sheet表里的最后一行
for (int rowNum = 0; rowNum <sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);//获取行对象
StringBuilder sb = new StringBuilder();
//循环每行中的所有单元格
for(int cellNum = 2; cellNum < row.getLastCellNum();cellNum++) {
Cell cell = row.getCell(cellNum);//获取单元格列对象
}
System.out.println(sb.toString());
}
}
运行结果如下:
序号-姓名-年龄-家庭住址-出生日期
1-张三-18-北京-北京-Thu Nov 01 00:00:00 CST 2001-
2-李四-20-上海-北京-Thu Nov 01 00:00:00 CST 2001-
3-王五-18-广州-北京-Thu Nov 01 00:00:00 CST 2001-
4-赵六-20-深圳-北京-Thu Nov 01 00:00:00 CST 2001-
二、需求报表导入
实现批量导入员工功能
页面端上传excel表格,服务端解析表格获取数据,批量新增用户
我们在使用导入的时候,要根据提供的模板填写相应的数据信息
比如说我们当前有一个模板,里面的信息是
那么我们如何将这些信息读取到,并且将它们保存到服务器数据库中?
第一步:搭建环境、引入JAR包或者MAVEN坐标
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
第二步:根据解析Excel文档思路获取上传文件
1.我们需要获取到这个Excel文档
2.获取需要操作的sheet表,从0下标开始代表为第一页
3.获取sheet表里的每一行、从0下标开始代表为第一行
4.获取sheet表里的每一列、从0下标开始代表为第一列
5.同时我们读取的时候,要根据当前单元格的属性,赋值不同的数据类型
//获取数据
private static Object getValue(Cell cell) {
Object value = null;
switch (cell.getCellType()) {
case STRING: //字符串类型
value = cell.getStringCellValue();
break;
case BOOLEAN: //boolean类型
value = cell.getBooleanCellValue();
break;
case NUMERIC: //数字类型(包含日期和普通数字)
if(DateUtil.isCellDateFormatted(cell)) {
value = cell.getDateCellValue();
}else{
value = cell.getNumericCellValue();
}
break;
case FORMULA: //公式类型
value = cell.getCellFormula();
break;
default:
break;
}
return value;
}
- 根据思路我们创建对应的用户类,以及测试
class User{
private String name;
private String tel;
private String code;
private String deptCode;
public User(Object[] obj) {
this.name = obj[0].toString();//用户名
this.tel = obj[1].toString();//手机号
this.code = obj[2].toString();//工号
this.deptCode = obj[5].toString();//部门编码
}
//省略get、set方法
}
@RequestMapping(value="/user/import", method = RequestMethod.POST)
public Result importExcel(@RequestParam(name = "file") MultipartFile attachment)throws Exception {
//1.根据上传流信息创建工作簿
Workbook workbook = WorkbookFactory.create(attachment.getInputStream());
//2.获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
List<User> users = new ArrayList<>();
//3.从第二行开始获取数据
for (int rowNum = 1; rowNum <sheet.getLastRowNum(); rowNum++) {
//获取行对象
Row row = sheet.getRow(rowNum);
//获取该行的所有列单元格数量
Object objs[] = new Object[row.getLastCellNum()];
//从第二列获取数据
for(int cellNum = 0; cellNum < row.getLastCellNum();cellNum++) {
Cell cell = row.getCell(cellNum);
objs[cellNum] = getValue(cell);
}
//根据每一列构造用户对象
User user = new User(objs);
users.add(user);
}
//第一个参数:用户列表,第二个参数:部门编码
userService.save(users);
return Result.SUCCESS();
}
第三步:批量保存用户信息到数据库中
@Transactional
public void save(List<User> users) throws Exception {
for (User user : users) {
userDao.save(user);
}
}
若对于Excel文档中,数字有时会携带.0小数点,手机号会存在科学记数法问题
这样的情况我们要怎么转化处理呢?
public User(Object[] obj) {
this.name = obj[0].toString();//用户名
this.tel = new DecimalFormat("#").format(obj[1].toString());//手机号
this.code = new DecimalFormat("#").format(obj[2].toString());//工号
this.deptCode = obj[5].toString();//部门编码
}
这样我们就不会出现转换的问题了
三、需求文档导出
完成当月人事报表的导出:包含当月入职员工信息,离职员工信息
主要思路分为:获取报表数据、创建excel、输出下载
@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
//1.构造数据
List<User> list = userService.findByReport(companyId,month+"%");
//2.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//3.构造sheet
String[] titles = {"编号", "姓名", "手机"};
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
int titleInext = 0;
//4.写入标题
for (String title : titles) {
Cell cell = row.createCell(titleInext++);
cell.setCellValue(title);
}
Cell cell = null;
//5.写入单元格
for (user report : list) {
Row dataRow = sheet.createRow(titleInext++);
//编号
cell = dataRow.createCell(0);
cell.setCellValue(report.getUserId());
//姓名
cell = dataRow.createCell(1);
cell.setCellValue(report.getUsername());
//手机
cell = dataRow.createCell(2);
cell.setCellValue(report.getMobile());
}
//6.输出文件下载
String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + new
String(fileName.getBytes("ISO8859-1")));
response.setHeader("filename", fileName);
workbook.write(response.getOutputStream());
}
参考资料
黑马程序员:基于SaaS平台的iHRM刷脸登录实战开发(报表相关视频)