POI导入导出

一、POI

1.1 简介

Apache POI 是基于 Office Open XML 标准（OOXML）和 Microsoft 的 OLE 2 复合文档格式（OLE2）处理各种文件格式的开源项目。简而言之，您可以使用 Java 读写 MS Excel 文件、 MS Word 和 MS PowerPoint 文件。

官网：Apache POI - the Java API for Microsoft Documents

模块

Excel格式

HSSF －提供读写 Microsoft Excel XLS 格式 (Microsoft Excel 97 (-2003)) 档案的功能。

XSSF －提供读写 Microsoft Excel OOXML XLSX 格式 (Microsoft Excel XML (2007+)) 档案的功能。

SXSSF －提供低内存占用量读写 Microsoft Excel OOXML XLSX 格式档案的功能。

Word格式

HWPF －提供读写 Microsoft Word DOC97 格式 (Microsoft Word 97 (-2003)) 档案的功能。

XWPF －提供读写 Microsoft Word DOC2003 格式 (WordprocessingML (2007+)) 档案的功能。

其他

HSLF/XSLF －提供读写 Microsoft PowerPoint 格式档案的功能。

HDGF/XDGF －提供读 Microsoft Visio 格式档案的功能。

HPBF －提供读 Microsoft Publisher 格式档案的功能。

HSMF －提供读 Microsoft Outlook 格式档案的功能。

1.2 03和07区别

03和07版本的写，就是对象不同，方法一样

最大行列得数量不同：

xls最大只有65536行、256列

xlsx可以有1048576行、16384列

1.3 依赖导入

poi 操作xls的 poi-ooxml 操作xlsx的

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version>
</dependency>

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version>
</dependency>

二、文件写入(导出)

2.1 文件写入-xls

public static void main(String[] args)  throws Exception{long begin = System.currentTimeMillis();Workbook workbook = new HSSFWorkbook();
//        Workbook workbook = new XSSFWorkbook();
//        Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("catalog");Row headerRow = sheet.createRow(0);headerRow.createCell(0).setCellValue("序号");headerRow.createCell(1).setCellValue("类别名称");headerRow.createCell(2).setCellValue("级别");headerRow.createCell(3).setCellValue("图片");headerRow.createCell(4).setCellValue("父类id");for (int rowNum = 1;rowNum<1000;rowNum++){//创建行Row row = sheet.createRow(rowNum);row.createCell(0).setCellValue(rowNum);row.createCell(1).setCellValue("分类"+rowNum/100);row.createCell(2).setCellValue(0);row.createCell(3).setCellValue(rowNum+".png");row.createCell(4).setCellValue(rowNum/100);}System.out.println("over");//获取io流FileOutputStream fos = new FileOutputStream("D://"+"1.xls");//生成一张表workbook.write(fos);fos.close();long end = System.currentTimeMillis();System.out.println("耗时："+(end-begin));}

根据当前的时间设置下载的文件名

 // Date获取当前日期SimpleDateFormat sdf = new SimpleDateFormat("YYYY年MM月dd日");String currentDate = sdf.format(new Date());String fileName = URLEncoder.encode(currentDate + ".xls","UTF-8");// 设置下载的文件名---将当前的日期设置为文件名response.setHeader("Content-Disposition", "attachment;filename=" + fileName);workbook.write(response.getOutputStream());

根据id选择部分导出

 // http://127.0.0.1:8080/student/explore/studentById?id=1&id=2@GetMapping("expore/studentById")public void explore2(@RequestParam("id") Integer [] ids) throws IOException {long begin = System.currentTimeMillis();
//        三种类型的导出格式Workbook workbook = new HSSFWorkbook();
//        Workbook workbook = new XSSFWorkbook();
//        Workbook workbook = new SXSSFWorkbook();Sheet sheet = workbook.createSheet("学生表");//sheet名Row rowTitle = sheet.createRow(0);Cell cellTitle1 = rowTitle.createCell(0);cellTitle1.setCellValue("序号");Cell cellTitle2 = rowTitle.createCell(1);cellTitle2.setCellValue("姓名");Cell cellTitle3 = rowTitle.createCell(2);cellTitle3.setCellValue("性别");// 设置cell的宽度sheet.setColumnWidth(3, 20*256);for (int i = 0; i < ids.length; i++) {Student student = studentService.findById(ids[i]);//创建行Row row1 = sheet.createRow(i+1);Cell cell4 = row1.createCell(0);cell4.setCellValue(student.getId());Cell cell5 = row1.createCell(1);cell5.setCellValue(student.getName());Cell cell6 = row1.createCell(2);cell6.setCellValue(student.getSex());}// 设置下载的文件名response.setHeader("Content-Disposition", "attachment;filename=student2.xls");workbook.write(response.getOutputStream());long end = System.currentTimeMillis();System.out.println("耗时："+(end-begin));}

2.2 文件下载

个人理解:POI文件下载就是可以使用Apache POI提供的类（如XSSFWorkbook或XWPFDocument）来创建一个新的Excel工作簿或Word文档对象等。首先将从数据库查询得到的结果集逐条转换为文档中的表格行或段落，并添加到文档对象中。然后将填充了数据的文档对象写入到HTTP响应的输出流中，以便客户端可以下载。这一步涉及使用HttpServletResponse的getOutputStream()方法。这在Web应用程序中常见的一种数据导出和文件下载的实现方式。

并且还可以通过HTTP响应返回文件

在Java Web应用中，通过Servlet或Spring MVC的Controller，可以将文档对象的内容输出到客户端，具体步骤如下：

设置响应头：在将文档写入输出流之前，需要设置HTTP响应的头部信息，包括Content-Type（指明文件类型，如application/vnd.openxmlformats-officedocument.spreadsheetml.sheet或application/vnd.openxmlformats-officedocument.wordprocessingml.document）和Content-Disposition（用于指定文件名和下载行为）。
获取输出流并写入文档内容：使用HttpServletResponse.getOutputStream()获取输出流，然后调用文档对象的write()方法，将文档内容写入到此输出流中。
关闭输出流：完成写入后，记得关闭输出流，以确保所有数据都被正确发送到客户端。

全部导出excel

 // http://127.0.0.1:8080/student/export@GetMapping("export")public void export() throws Exception{List<Student> students = studentService.findAll();Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("学生表");Row rowTitle = sheet.createRow(0);Cell cellTitle1 = rowTitle.createCell(0);cellTitle1.setCellValue("序号");Cell cellTitle2 = rowTitle.createCell(1);cellTitle2.setCellValue("姓名");Cell cellTitle3 = rowTitle.createCell(2);cellTitle3.setCellValue("性别");// 设置cell的宽度sheet.setColumnWidth(3, 20*256);for (int i = 0; i < students.size(); i++) {Student student = students.get(i);Row row = sheet.createRow(i+1);Cell cell1 = row.createCell(0);cell1.setCellValue(student.getId());Cell cell2 = row.createCell(1);cell2.setCellValue(student.getName());Cell cell3 = row.createCell(2);cell3.setCellValue(student.getSex());}// 设置下载的文件名response.setHeader("Content-Disposition", "attachment;filename=student1.xls");workbook.write(response.getOutputStream());}

根据当前的时间设置下载的文件名

 // Date获取当前日期SimpleDateFormat sdf = new SimpleDateFormat("YYYY年MM月dd日");String currentDate = sdf.format(new Date());String fileName = URLEncoder.encode(currentDate + ".xls","UTF-8");// 设置下载的文件名---将当前的日期设置为文件名response.setHeader("Content-Disposition", "attachment;filename=" + fileName);workbook.write(response.getOutputStream());

根据id选择部分导出

 // http://127.0.0.1:8080/student/explore/studentById?id=1&id=2@GetMapping("expore/studentById")public void explore2(@RequestParam("id") Integer [] ids) throws IOException {long begin = System.currentTimeMillis();
//        三种类型的导出格式Workbook workbook = new HSSFWorkbook();
//        Workbook workbook = new XSSFWorkbook();
//        Workbook workbook = new SXSSFWorkbook();Sheet sheet = workbook.createSheet("学生表");//sheet名Row rowTitle = sheet.createRow(0);Cell cellTitle1 = rowTitle.createCell(0);cellTitle1.setCellValue("序号");Cell cellTitle2 = rowTitle.createCell(1);cellTitle2.setCellValue("姓名");Cell cellTitle3 = rowTitle.createCell(2);cellTitle3.setCellValue("性别");// 设置cell的宽度sheet.setColumnWidth(3, 20*256);for (int i = 0; i < ids.length; i++) {Student student = studentService.findById(ids[i]);//创建行Row row1 = sheet.createRow(i+1);Cell cell4 = row1.createCell(0);cell4.setCellValue(student.getId());Cell cell5 = row1.createCell(1);cell5.setCellValue(student.getName());Cell cell6 = row1.createCell(2);cell6.setCellValue(student.getSex());}// 设置下载的文件名response.setHeader("Content-Disposition", "attachment;filename=student2.xls");workbook.write(response.getOutputStream());long end = System.currentTimeMillis();System.out.println("耗时："+(end-begin));}

 @GetMapping("/downLoadFile")public ResponseEntity<byte[]> downLoadFile(HttpServletRequest request, HttpServletResponse response) throws IOException {long begin = System.currentTimeMillis();Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("catalgo");Row headerRow = sheet.createRow(0);headerRow.createCell(0).setCellValue("序号");headerRow.createCell(1).setCellValue("类别名称");headerRow.createCell(2).setCellValue("级别");headerRow.createCell(3).setCellValue("图片");headerRow.createCell(4).setCellValue("父类名称");for (int rowNum = 1;rowNum<1000;rowNum++){Row row = sheet.createRow(rowNum);row.createCell(0).setCellValue(rowNum);row.createCell(1).setCellValue("分类"+rowNum/100);row.createCell(2).setCellValue(0);row.createCell(3).setCellValue(rowNum+".png");row.createCell(4).setCellValue(rowNum/100);}System.out.println("over");//生成一张表response.setHeader("Content-Type","application/octet-stream");response.setHeader("Content-Disposition","form-data; name=\"attachment\"; filename=\""+URLEncoder.encode("2023年05月08日","UTF-8")+"\".xls");workbook.write(response.getOutputStream());long end = System.currentTimeMillis();System.out.println("耗时："+(end-begin));return new ResponseEntity<>(HttpStatus.OK);}

三、文件读取(导入)

3.1 文件读取

 public static void main(String[] args) throws Exception{FileInputStream fis = new FileInputStream("D://1.xls");Workbook workbook = new HSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(0);System.out.println(sheet.getFirstRowNum()+":"+sheet.getLastRowNum());for(int i = 1 ; i <= sheet.getLastRowNum() ;i++){Row row1 = sheet.getRow(i);System.out.print(row1.getCell(0).getNumericCellValue()+"\t");System.out.print(row1.getCell(1).getStringCellValue()+"\t");System.out.print(row1.getCell(2).getNumericCellValue()+"\t");System.out.print(row1.getCell(3).getStringCellValue()+"\t");System.out.println(row1.getCell(4).getNumericCellValue());}fis.close();}

3.2 文件上传

个人理解:POI文件上传就是可以有效地利用Apache POI库从上传的Excel或Word文档中读取数据(通过http请求读取比如file.getInputStream())，然后通过SQL语句将其导入到数据库中，实现数据的批量导入和更新。这一过程不仅提高了数据处理的效率，也简化了数据录入和维护的复杂度，是现代Web应用中数据管理的重要组成部分。

利用Apache POI进行文件上传和数据导入

Apache POI库为从Office文档（如Excel和Word）中读取数据提供了强大的支持。在Web应用中，通过HTTP请求上传这些文件，随后解析其内容并将其导入数据库，是一种常见的数据处理方式。以下是这一过程的具体步骤：

接收文件上传：首先，Web应用需通过HTTP POST请求接收用户上传的Excel或Word文件。这通常涉及到配置前端表单以允许文件上传，并在后端（如Servlet或Spring MVC Controller）中处理multipart/form-data类型的请求。
读取文件内容：接收到文件后，使用Apache POI库的相关类（如XSSFWorkbook或XWPFDocument）打开并读取上传的文件。这一步骤涉及解析文档结构，提取表格或段落中的数据。
转换数据格式：读取到的数据通常以单元格或段落的形式存在，需要将其转换为适合进一步处理的Java数据类型，如字符串、数字或日期等。这可能需要额外的逻辑来识别和转换数据类型。
数据验证和预处理：在将数据导入数据库前，应进行必要的数据验证和预处理，以确保数据的完整性和准确性。这可能包括检查必填字段、格式化日期或数值、以及处理潜在的编码问题。
构建SQL语句并执行：使用从文档中提取并转换的数据，构建适当的SQL INSERT或UPDATE语句，然后通过JDBC连接执行这些语句，将数据持久化到数据库中。这一步可能需要事务管理，以保证数据操作的一致性和完整性。
错误处理和反馈：在整个过程中，应充分考虑错误处理，包括但不限于文件格式错误、数据转换异常或数据库操作失败。向用户反馈上传和导入的进度及结果，对于提高用户体验至关重要。

 @ResponseBody@PostMapping("upload")public Result upload( MultipartFile file, HttpServletRequest request) {if (file.isEmpty()){return Result.failed("文件不能为空");}if(file.getSize() > 1024 * 1024 * 10) {return Result.failed("文件大小不能超过10M！");}String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1, file.getOriginalFilename().length());if (!"xls".toUpperCase().contains(suffix.toUpperCase())) {return Result.failed("文件格式错误，请选择提供的xls模板！");}List<Catalog> catalogs = new ArrayList<>();try {Workbook workbook = new HSSFWorkbook(file.getInputStream());Sheet sheet = workbook.getSheetAt(0);for(int i = 1 ; i <= sheet.getLastRowNum() ;i++){Row row1 = sheet.getRow(i);Catalog catalog = new Catalog();catalog.setCid((int)row1.getCell(0).getNumericCellValue());catalog.setCname(row1.getCell(1).getStringCellValue());catalog.setLevel((int)row1.getCell(2).getNumericCellValue());catalog.setPic(row1.getCell(3).getStringCellValue());catalog.setParentId((int)row1.getCell(4).getNumericCellValue());catalogs.add(catalog);}
} catch (IOException e) {e.printStackTrace();return Result.failed("文件读取失败,请选择提供的xls模板!");}return Result.success("上传成功",catalogs);}

学生表示例

pojo

@Data
public class Student implements Serializable {private static final long serialVersionUID = 125236L;private int id;private String name;private String sex;
}

controller---必须要传MultipartFile file参数

@PostMapping("upload")public Result upload(@RequestParam("file") MultipartFile file) throws Exception {if(studentService.upload(file)){return Result.success("上传成功",null);}else {return Result.failed("上传失败");}}

service和impl

public interface StudentService {List<Student> findAll();Student findById(Integer id);boolean upload(MultipartFile file) throws Exception;
}

//    文件上传处理逻辑@Overridepublic boolean upload(MultipartFile file) throws Exception {if (file == null || file.isEmpty()){throw new ExportException("文件不能为空");}String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1, file.getOriginalFilename().length());if (!".xls".toUpperCase().contains(suffix.toUpperCase())) {throw new Exception("请使用.xls");}ArrayList<Student> students = new ArrayList<>();Workbook workbook = new HSSFWorkbook(file.getInputStream());Sheet sheet = workbook.getSheetAt(0);log.info(sheet.getLastRowNum()+"最后一行");for(int i = 1; i < sheet.getLastRowNum(); i++){
//            System.out.println(i+",");Row row = sheet.getRow(i);Cell c1 = row.getCell(0);String name = c1.getStringCellValue();
//            System.out.println(name);Cell c2 = row.getCell(1);String sex = c2.getStringCellValue();
//            System.out.println(sex);Student student = new Student();student.setName(name);student.setSex(sex);students.add(student);}return studentMapper.addmoreStudent(students);}

mapper和mapper.xml

public interface StudentMapper {@Select("select * from student")List<Student> findAllStudent();@Select("select * from student where id = #{id}")Student findStudentById(Integer id);boolean addmoreStudent(List<Student> students);
}

<mapper namespace="com.itqq.mapper.StudentMapper"><insert id="addmoreStudent">insert into student (name,sex)values<foreach collection = "students" item = "student" separator = ",">(#{student.name} , #{student.sex})</foreach></insert>
</mapper>

Employee表示例

(特殊在有其他两个表的id,但将上传的Excel表中直接是其他两个表id对应的name)

pojo

@Data
public class Employee implements Serializable {private static final long serialVersionUID = 12352634L;private int eid;private String ename;private Double salary;private Date birthday;private String gender;
//    这里可以改为String类型,对应数据库里面的charprivate Long tel;private String email;private String address;private String workPlace;private Date hireDate;private Integer jobId;private Integer mid;private Integer did;
}

controller(与student案例不同的是把有关的业务逻辑放在了service层,保持规范)

@RestController
@RequestMapping("employee")
public class EmployeeController {@Autowiredprivate EmployeeService employeeService;@PostMapping("upload")public Result upload(@RequestParam("file") MultipartFile file) throws Exception {if(employeeService.upload(file)){return Result.success("上传成功",null);}else {return Result.failed("上传失败");}}}

service

public interface EmployeeService {boolean upload(MultipartFile file) throws Exception;
}

创新点就是先获取到Excel表中的name属性,然后再根据name属性,调用Mapper层查询出对应的id,放到employee对象里面,进行添加到数据库的操作

@Service("employeeService")
@Slf4j
public class EmployeeServiceImpl implements EmployeeService {@Autowiredprivate EmployeeMapper employeeMapper;@Autowiredprivate JobMapper jobMapper;@Autowiredprivate DepartmentMapper departmentMapper;//    上传文件到数据库中public boolean upload(MultipartFile file) throws Exception {if(file.isEmpty() || file == null){throw new RuntimeException("文件不能为空");}String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1, file.getOriginalFilename().length());if (!".xlsx".toUpperCase().contains(suffix.toUpperCase())) {throw new Exception("请使用.xlsx");}ArrayList<Employee> employees = new ArrayList<>();Workbook workbook = new XSSFWorkbook(file.getInputStream());Sheet sheet = workbook.getSheetAt(0);log.info(sheet.getLastRowNum()+"最后一行");for (int i = 1; i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);Cell cell1 = row.getCell(0);String ename = cell1.getStringCellValue();Cell cell2 = row.getCell(1);Double salary = cell2.getNumericCellValue();Cell cell3 = row.getCell(2);Date birthday = cell3.getDateCellValue();log.info(String.valueOf(birthday));Cell cell4 = row.getCell(3);String sex = cell4.getStringCellValue();log.info(sex);Cell cell5 = row.getCell(4);cell5.setCellType(Cell.CELL_TYPE_STRING);String phone = cell5.getStringCellValue();log.info(phone);Cell cell6 = row.getCell(5);String email = cell6.getStringCellValue();log.info(email);Cell cell7 = row.getCell(6);String address = cell7.getStringCellValue();log.info(address);Cell cell8 = row.getCell(7);String workPlace = cell8.getStringCellValue();Cell cell9 = row.getCell(8);Date hireDate = cell9.getDateCellValue();// 这里读取的还是name , 技术总监	庞浩	研发部Cell cell10 = row.getCell(9);String job = cell10.getStringCellValue();Cell cell11 = row.getCell(10);String mName = cell11.getStringCellValue();Cell cell12 = row.getCell(11);String dName = cell12.getStringCellValue();Employee employee = new Employee();employee.setEname(ename);employee.setSalary(salary);employee.setBirthday(birthday);employee.setGender(sex);employee.setTel(Long.parseLong(phone));employee.setEmail(email);employee.setAddress(address);employee.setWorkPlace(workPlace);employee.setHireDate(hireDate);// 重点！！！！// 获取职位idemployee.setJobId(jobMapper.findByJobName(job));// 获取部门idemployee.setDid(departmentMapper.findByDepartmentName(dName));// 获取上级idemployee.setMid(employeeMapper.findByEmployeeName(mName));employees.add(employee);}return employeeMapper.addmore(employees);}
}

与之对应的Mapper

public interface EmployeeMapper {Integer findByEmployeeName(String ename);boolean addmore(List<Employee> employees);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itqq.mapper.EmployeeMapper"><insert id="addmore">insert into t_employee(ename,job_id,did,hiredate,birthday,gender,tel,email,mid,salary,address,work_place) values<foreach collection="employees" item="employee" separator=",">(#{employee.ename},#{employee.jobId},#{employee.did},#{employee.hireDate},#{employee.birthday},#{employee.gender},#{employee.tel},#{employee.email},#{employee.mid},#{employee.salary},#{employee.address},#{employee.workPlace})</foreach></insert><select id="findByEmployeeName" resultType="java.lang.Integer">select eid from t_employee where ename = #{ename}</select>
</mapper>

public interface DepartmentMapper {@Select("select did from t_department where dname = #{dname}")Integer findByDepartmentName(String dname);
}

public interface JobMapper {@Select("select jid from t_job where jname = #{jobName}")Integer findByJobName(String jobName);
}

总结完毕!!!