spring mvc中导入导出excel

POI介绍

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能

导入jar包：poi-4.1.2.jar，xwork-core-2.3.15.3.jar

1、写excel

package excel; 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.ss.usermodel.CellType; import java.io.File; import java.util.HashMap; import java.util.Map; import java.util.Set; public class ExcelTest { public static void main(String[] args) throws Exception{ HSSFWorkbook workbook =new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("服务报表"); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellType(CellType.STRING); cell.setCellValue("类型"); HSSFCell cell1 = row.createCell(1); cell1.setCellType(CellType.STRING); cell1.setCellValue("次数"); Map<String,Integer> dbs = new HashMap<>(); dbs.put("咨询",10); dbs.put("建议",20); Set<String> typs = dbs.keySet(); int i = 1; for(String type:typs) { HSSFRow row2 = sheet.createRow(i++); HSSFCell cel2 = row2.createCell(0); cel2.setCellType(CellType.STRING); cel2.setCellValue(type); HSSFCell cell3 = row2.createCell(1); cell3.setCellType(CellType.NUMERIC); cell3.setCellValue(dbs.get(type)); } File file = new File("E:\\a.xls"); workbook.write(file); } }

2、读取excel

package excel; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import java.io.File; import java.io.FileInputStream; public class ExcelTest2 { public static void main(String[] args) throws Exception{ File file = new File("E:\\a.xls"); HSSFWorkbook workbook =new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheetAt = workbook.getSheetAt(0); for(Row r:sheetAt) { for(Cell c:r) { c.setCellType(CellType.STRING); System.out.print(c.getStringCellValue() +"\t"); } System.out.println(); } } }

3、准备form表单

<form enctype="multipart/form-data" method="post" action="${basePath}/product/upload"> <input name="file" type="file"/> <input name="上传" type="submit"/> </form >

4、Spring-web.xml

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>

5、在controlller中的编写的方法

package com.cc.web.controller; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.multipart.MultipartFile; @Controller @RequestMapping("/product") public class ProductController { @RequestMapping("/list") public String listSeviceReport() { return "~basd/product"; } //到入excel的方法 @RequestMapping("/upload") public String uploadExcel(MultipartFile file) throws Exception { HSSFWorkbook workbook =new HSSFWorkbook(file.getInputStream()); HSSFSheet sheetAt = workbook.getSheetAt(0); for(Row r:sheetAt) { for(Cell c:r) { c.setCellType(CellType.STRING); System.out.print(c.getStringCellValue() +"\t"); } System.out.println(); } return null; } }