教你如何使用JAVA POI

所需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:zphtemp员工信息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:zphtemp员工信息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:zphtemp// 多态 抛异常//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

到此这篇关于教你如何使用JAVA POI的文章就介绍到这了,更多相关JAVA POI内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！