POI使用

• 导入jar包
• 导出
• 导出

导入jar包

所需jar包，在pom中添加如下坐标即可

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

注意：
操作Excel文件区分版本：
2003版本（包含2003）以前的扩展名为.xls需要用HSSFWorkbook类操作
2007版本（包含2007）以后的扩展名为.xlsx需要用XSSFWorkbook类操作

导出

2007版本（包含2007）以后的扩展名为.xlsx需要用XSSFWorkbook类操作
2003版本（包含2003）以前的扩展名为.xls需要用HSSFWorkbook类操作
和 07基本相似 就是把XSSFWorkbook换成HSSFWorkbook
后缀名改成 点xls

package com.zph.poi;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.core.io.ClassPathResource;import org.springframework.core.io.Resource;import org.springframework.stereotype.Service;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.net.URI;import java.net.URLEncoder;@Servicepublic class PoiServiceImpl {public void exportExcel2007() throws IOException {//创建工作簿 类似于创建Excel文件XSSFWorkbook workbook=new XSSFWorkbook();//创建 sheetname页名XSSFSheet sheet = workbook.createSheet("员工信息");sheet.setColumnWidth(3,20*256);//给第3列设置为20个字的宽度sheet.setColumnWidth(4,20*256);//给第4列设置为20个字的宽度//创建一行,下标从0开始XSSFRow row = sheet.createRow(0);//创建这行中的列,下标从0开始 （表头）XSSFCell cell = row.createCell(0);// 给cell 0下表赋值cell.setCellValue("姓名");//创建这行中的列,并给该列直接赋值row.createCell(1).setCellValue("年龄");row.createCell(2).setCellValue("性别");row.createCell(3).setCellValue("生日");row.createCell(4).setCellValue("手机号");// 设置表里内容row = sheet.createRow(1);row.createCell(0).setCellValue("T");row.createCell(1).setCellValue("保密");row.createCell(2).setCellValue("男");row.createCell(3).setCellValue("保密");row.createCell(4).setCellValue("12121212121");row = sheet.createRow(2);row.createCell(0).setCellValue("T");row.createCell(1).setCellValue("18");row.createCell(2).setCellValue("女");row.createCell(3).setCellValue("2000-01-01");row.createCell(4).setCellValue("12121212122");//设定 路径File file = new File("D:\\\\zph\\\\temp\\\\员工信息2007.xlsx");FileOutputStream stream = new FileOutputStream(file);// 需要抛异常workbook.write(stream);//关流stream.close();}public void exportExcel2003() throws IOException {//创建工作簿 类似于创建Excel文件HSSFWorkbook workbook=new HSSFWorkbook();//创建 sheetname页名HSSFSheet sheet = workbook.createSheet("员工信息");//创建一行,下标从0开始HSSFRow row = sheet.createRow(0);//创建这行中的列,下标从0开始 （表头）HSSFCell cell = row.createCell(0);// 给cell 0下表赋值cell.setCellValue("姓名");//创建这行中的列,并给该列直接赋值row.createCell(1).setCellValue("年龄");row.createCell(2).setCellValue("性别");row.createCell(3).setCellValue("生日");row.createCell(4).setCellValue("手机号");// 设置表里内容row = sheet.createRow(1);row.createCell(0).setCellValue("T");row.createCell(1).setCellValue("保密");row.createCell(2).setCellValue("男");row.createCell(3).setCellValue("保密");row.createCell(4).setCellValue("12121212121");row = sheet.createRow(2);row.createCell(0).setCellValue("T");row.createCell(1).setCellValue("18");row.createCell(2).setCellValue("女");row.createCell(3).setCellValue("2000-01-01");row.createCell(4).setCellValue("12121212122");//第一种导出 给定路径//1设定 路径 创建文件读进来在写内容File file = new File("D:\\\\zph\\\\temp\\\\员工信息2003.xls");FileOutputStream stream = new FileOutputStream(file);// 需要抛异常workbook.write(stream);//关流stream.close();}public void exportExcel2003(HttpServletRequest request, HttpServletResponse response) throws IOException {//第二种导出 从项目中获取模板//String realPath = request.getSession().getServletContext().getRealPath("/");Resource resource = new ClassPathResource("templates/员工信息2003Tem.xls");//jar包获取//创建工作簿 类似于创建Excel文件HSSFWorkbook workbookTem=new HSSFWorkbook(resource.getInputStream());//创建 sheetname页名HSSFSheet sheetTem = workbookTem.getSheet("员工信息");//HSSFSheet sheetTem = workbookTem.getSheetAt(0);HSSFRow rowTem = sheetTem.createRow(1);rowTem.createCell(0).setCellValue("xmtem");rowTem.createCell(1).setCellValue("nltem");rowTem.createCell(2).setCellValue("xbtem");rowTem.createCell(3).setCellValue("srtem");rowTem.createCell(4).setCellValue("sjhtem");ServletOutputStream outputStream = response.getOutputStream();response.reset();String fileName = URLEncoder.encode("员工信息TemOut.xls", "utf-8");response.setHeader("Content-disposition","attachment;filename="+fileName);response.setContentType("application/x-download;charset=UTF-8");// 对响应客户请求进行重新编码11//response.setCharacterEncoding("utf-8");workbookTem.write(outputStream);outputStream.close();}public String exportExcel2003(String s,HttpServletRequest request, HttpServletResponse response) throws IOException {//第三种直接导出//创建工作簿 类似于创建Excel文件HSSFWorkbook workbookTem=new HSSFWorkbook();//创建 sheetname页名HSSFSheet sheet = workbookTem.createSheet("员工信息");//创建一行,下标从0开始HSSFRow row = sheet.createRow(0);//创建这行中的列,下标从0开始 （表头）HSSFCell cell = row.createCell(0);// 给cell 0下表赋值cell.setCellValue("姓名");//创建这行中的列,并给该列直接赋值row.createCell(1).setCellValue("年龄");row.createCell(2).setCellValue("性别");row.createCell(3).setCellValue("生日");row.createCell(4).setCellValue("手机号");// 设置表里内容row = sheet.createRow(1);row.createCell(0).setCellValue("T");row.createCell(1).setCellValue("保密");row.createCell(2).setCellValue("男");row.createCell(3).setCellValue("保密");row.createCell(4).setCellValue("12121212121");row = sheet.createRow(2);row.createCell(0).setCellValue("T");row.createCell(1).setCellValue("18");row.createCell(2).setCellValue("女");row.createCell(3).setCellValue("2000-01-01");row.createCell(4).setCellValue("12121212122");ServletOutputStream outputStream = response.getOutputStream();response.reset();String fileName = URLEncoder.encode("员工信息TemOut.xls", "utf-8");response.setHeader("Content-disposition","attachment;filename="+fileName);//response.setContentType("application/x-download;charset=UTF-8");response.setContentType("application/vnd.ms-excel");//response.setContentType("application/msexcel");// 对响应客户请求进行重新编码11//response.setCharacterEncoding("utf-8");workbookTem.write(outputStream);outputStream.close();return s;}}

导出

@RequestMapping(value="/upload")public String  uploadExcel(@RequestParam("fileData") MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {InputStream in = file.getInputStream();String s = poiService.uploadExcel(file, request, response);return s;}public String  uploadExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {InputStream in = file.getInputStream();//D:\\zph\\temp// 多态  抛异常//Workbook sheets = new XSSFWorkbook(stream);HSSFWorkbook sheets = new HSSFWorkbook(in);//获取一个工作表(sheet页)，下标从0开始HSSFSheet sheet = sheets.getSheetAt(0);for (int i = 1; i<=sheet.getLastRowNum() ; i++) {// 获取行数Row row = sheet.getRow(i);// 获取单元格 取值String value1 = row.getCell(0).getStringCellValue();String value2 = row.getCell(1).getStringCellValue();String value3 = row.getCell(2).getStringCellValue();String value4 = row.getCell(3).getStringCellValue();String value5= row.getCell(4).getStringCellValue();System.out.println(value1);System.out.println(value2);System.out.println(value3);System.out.println(value4);System.out.println(value5);}//关流sheets.close();in.close();return "hha";}

postman 设置 post请求 请求头 Content-Type multipart/form-data