SpringBoot使用POI实现Excel导入导出及模板下载

pom文件

<!--处理2003 excel-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.16</version>
</dependency>
<!--处理2007 excel-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.16</version>
</dependency>

导入Excel

excel数据导入主要涉及三个步骤:
1.文件上传
2.excel解析
3.数据插入

文件上传

@PostMapping("importexcel")
public Object importWatchExcel(@RequestParam("excelFile") MultipartFile xlsFile) {
	//...
}

excel解析
将上传到的MultipartFile转为输入流，然后交给POI去解析即可，第一步需要创建Workbook，HSSFWorkbook和XSSFWorkbook都实现了Workbook接口，也可以创建HSSFWorkbook或XSSFWorkbook，它们方法名基本一致，需要注意的是POI读取excel2003、excel2007存在兼容性问题，excel主要有两类：xls、xlsx，HSSFWorkbook：是操作Excel2003以前（包括2003）的版本，扩展名是.xls，XSSFWorkbook：是操作Excel2007的版本，扩展名是.xlsx，如果使用XSSFWorkbook类导入Excel2003版本的表就会出错，创建文档的方法可以写成如下所示：

Workbook wb = WorkbookFactory.create(xlsFile.getInputStream());

WorkbookFactory.create()函数对文件类型进行判断来决定创建哪种对象：

/**
 * Creates the appropriate HSSFWorkbook / XSSFWorkbook from
 *  the given InputStream.
 * Your input stream MUST either support mark/reset, or
 *  be wrapped as a {@link PushbackInputStream}!
 */
public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {

	// If clearly doesn't do mark/reset, wrap up
	if(! inp.markSupported()) {
		inp = new PushbackInputStream(inp, 8);
	}
	
	if(POIFSFileSystem.hasPOIFSHeader(inp)) {
		return new HSSFWorkbook(inp);
	}

	if(POIXMLDocument.hasOOXMLHeader(inp)) {
		return new XSSFWorkbook(OPCPackage.open(inp));
	}

	throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");

}

创建出对应的xxxxWorkbook对象后，获取sheet页并遍历：

int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
    HSSFSheet sheet = workbook.getSheetAt(i);
    //...
}

获取sheet中一共有多少行，遍历行（跳过标题）：

int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
Employee employee;
for (int j = 0; j < physicalNumberOfRows; j++) {
    if (j == 0) {
        continue;//如果第一行标题，跳过
    }
    //...
}

获取每一行有多少单元格，遍历单元格：

int physicalNumberOfCells = row.getPhysicalNumberOfCells();
employee = new Employee();
for (int k = 0; k < physicalNumberOfCells; k++) {
    HSSFCell cell = row.getCell(k);
    //...
}

数据插入

将遍历到的数据放入实体类中，每遍历一行，就将一个实体类放入集合中。

导入示例：

/**
 * excel导入数据
 */
@PostMapping("importexcel")
@Transactional(rollbackFor = Exception.class)
public Object importWatchExcel(@RequestParam("excelFile") MultipartFile xlsFile) {
	Map<String, Object> result = new HashMap<>();
	// contentType
	// String contentType = file.getContentType();
	// excel文件名
	// String fileName = file.getOriginalFilename();
	if (xlsFile.isEmpty()) {
		result.put("code", 500);
		result.put("message", "导入文件为空！");
		return result;
	}
	// 根据不同excel创建不同对象,Excel2003版本-->HSSFWorkbook,Excel2007版本-->XSSFWorkbook
	Workbook wb = null;
	InputStream im = null;
	try {
		im = xlsFile.getInputStream();
		wb = WorkbookFactory.create(im);
		// 根据页面index 获取sheet页
		Sheet sheet = wb.getSheetAt(0);
		Row row = null;
		// 循环sheet页中数据从第x行开始,例:第3行开始为导入数据
		for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
			// 获取每一行数据
			row = sheet.getRow(i);
			// 输出表格内容,此处可替换为数据插入操作
			// 日期,表格数字格式为日期
			if (null != row.getCell(0) && "" != row.getCell(0).toString()) {					
				System.out.println((new SimpleDateFormat("yyyy-MM-dd")).format(row.getCell(0).getDateCellValue()));					
			}
			// 内容,表格数字格式为常规
			if (null != row.getCell(1) && "" != row.getCell(1).toString()) {
				// 如果表格内容为数字,需要设置CellType为string，否则调用getStringCellValue()会出现获取类型错误
				row.getCell(1).setCellType(CellType.STRING);
				System.out.println(row.getCell(1).getStringCellValue());
			}
		}
	result.put("code", 200);
	result.put("message", "导入成功!");
	} catch (Exception e1) {
		// 回滚数据
		TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
		e1.printStackTrace();
	} finally {
		try {
			im.close();
			wb.close();
		} catch (IOException e2) {
			e2.printStackTrace();
		}
	}
	return result;
}

导出excel

以xls为例：

/**
 * excel导出
 */
@GetMapping("exportexcel")
public void exportPermMatrix(HttpServletRequest request, HttpServletResponse response) throws Exception {
	HSSFWorkbook workbook = new HSSFWorkbook();
	HSSFSheet sheet = workbook.createSheet("xxx信息表");
	//此处添加数据
	HSSFRow headerRow1 = sheet.createRow(0);
	headerRow1.createCell(0).setCellValue("编号");
	headerRow1.createCell(1).setCellValue("内容");
	HSSFRow headerRow2 = sheet.createRow(1);
	headerRow2.createCell(0).setCellValue("01");
	headerRow2.createCell(1).setCellValue("测试文本");
	//清空response
	response.reset();
	response.setContentType("multipart/form-data");
	response.setHeader("Content-Disposition",
			"attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xls");
	OutputStream os = new BufferedOutputStream(response.getOutputStream());  
	workbook.write(os);
	os.flush();
	os.close();
	workbook.close();
}

下载excel模板

示例：

/**
* 下载excel模板
*/
@GetMapping("downloadexcel")
public void downloadPermMatrix(HttpServletRequest request, HttpServletResponse response) throws Exception {
    Workbook wb;
    try {
        ClassPathResource resource = new ClassPathResource("excel.xlsx");
        InputStream inputStream = resource.getInputStream();
        // 根据不同excel创建不同对象,Excel2003版本-->HSSFWorkbook,Excel2007版本-->XSSFWorkbook
        wb = WorkbookFactory.create(inputStream);
        response.reset();
        response.setContentType("multipart/form-data");
        if (wb.getClass().getSimpleName() == "HSSFWorkbook") {
            response.setHeader("Content-Disposition",
                "attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xls");
        } else {
            response.setHeader("Content-Disposition",
                "attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xlsx");
        }
        wb.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
}