在SSM下基于POI实现Excel表的导入(多sheet)/导出
学习Excel编写项目时的一些心得，适合小白观看。项目中实现了Excel中多个sheet的导入和单数表的导出功能，对于多数据表指定导出可自行添加if或者for循环来实现。Excel表的结构，简单的分成 Sheet，Cell，Row 三部分，即页，列，行。因此，想要获取到内容，可以先sheet，然后再边一个sheet中的行数，在遍历列，从而实现导入功能。-----------本文在SSM环境下基于Java的POI实现Excel的导入导出功能。
本次需要导入的 主要 jar包如下：
poi-ooxml-schemas-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-3.7-20101029.jar
httpclient-4.5.3.jar
xmlbeans-2.3.0.jar
Maven项目配置可见其他博主的博客，以下各代码块import * 部分为不占太多空间看着方便不予显示，此项目成功运行后的页面如下。
一、工程目录结构：
二、创建两个封装实例
表格实体类
package com.yang.entity;public class ExcelBean implements Serializable{private String headTextName; //列头（标题）名private String propertyName; //对应字段名private Integer cols; //合并单元格数private XSSFCellStyle cellStyle;public ExcelBean(){}public ExcelBean(String headTextName, String propertyName){this.headTextName = headTextName;this.propertyName = propertyName;}public ExcelBean(String headTextName, String propertyName, Integer cols) {super();this.headTextName = headTextName;this.propertyName = propertyName;this.cols = cols;}//省略了set，get方法................
}表格内容实体类
package com.yang.entity;public class ImpT {private Integer id;private String name;private String sex;private String email;private Integer deptId;//省略了set，get方法..........
}三、创建一个工具类Excelutil
package com.yang.util;public class ExcelUtil {/*** Excel导入*/public static List<List<Object>> getBankListByExcel(InputStream in, Workbook work,Sheet sheet) {List<List<Object>> list = null;Row row = null;Cell cell = null;list = new ArrayList<List<Object>>();// 遍历当前sheet中的所有行// 包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {// 读取一行row = sheet.getRow(j);// 去掉空行和表头if (row == null || row.getFirstCellNum() == j) {continue;}// 遍历所有的列List<Object> li = new ArrayList<Object>();for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {cell = row.getCell(y);li.add(getCellValue(cell));}list.add(li);}return list;}/*** 描述：对表格中数值进行格式化*/public static Object getCellValue(Cell cell) {Object value = null;DecimalFormat df = new DecimalFormat("0"); // 格式化字符类型的数字SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字switch (cell.getCellType()) {case Cell.CELL_TYPE_STRING:value = cell.getRichStringCellValue().getString();break;case Cell.CELL_TYPE_NUMERIC:if ("General".equals(cell.getCellStyle().getDataFormatString())) {value = df.format(cell.getNumericCellValue());} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {value = sdf.format(cell.getDateCellValue());} else {value = df2.format(cell.getNumericCellValue());}break;case Cell.CELL_TYPE_BOOLEAN:value = cell.getBooleanCellValue();break;case Cell.CELL_TYPE_BLANK:value = "";break;default:break;}return value;}/*** 开始导出Excel表* @param sheetName 工作簿名称* @param clazz 数据源model类型* @param objs excel标题列以及对应model字段名* @param map 标题列行数以及cell字体样式*/public static XSSFWorkbook createExcelFile(Class<?> clazz, List<Map<String,Object>> objs, Map<Integer, List<ExcelBean>> map, String sheetName) throws
IllegalArgumentException,IllegalAccessException,InvocationTargetException,
ClassNotFoundException, IntrospectionException, ParseException {// 创建新的Excel工作簿XSSFWorkbook workbook = new XSSFWorkbook();// 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称XSSFSheet sheet = workbook.createSheet(sheetName);// 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;createFont(workbook); //字体样式createTableHeader(sheet, map); //创建标题（头）createTableRows(sheet, map, objs, clazz); //创建内容return workbook;}private static XSSFCellStyle fontStyle;private static XSSFCellStyle fontStyle2;//字体样式public static void createFont(XSSFWorkbook workbook) {// 表头fontStyle = workbook.createCellStyle();XSSFFont font1 = workbook.createFont();
// font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);font1.setFontName("黑体");font1.setFontHeightInPoints((short) 14);// 设置字体大小fontStyle.setFont(font1);fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中// 内容fontStyle2=workbook.createCellStyle();XSSFFont font2 = workbook.createFont();font2.setFontName("宋体");font2.setFontHeightInPoints((short) 10);// 设置字体大小fontStyle2.setFont(font2);fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中}/*** 根据ExcelMapping 生成列头（多行列头）** @param sheet 工作簿* @param map 每行每个单元格对应的列头信息*/public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) {int startIndex=0;//cell起始位置int endIndex=0;//cell终止位置for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {XSSFRow row = sheet.createRow(entry.getKey());List<ExcelBean> excels = entry.getValue();for (int x = 0; x < excels.size(); x++) {//合并单元格if(excels.get(x).getCols()>1){if(x==0){endIndex+=excels.get(x).getCols()-1;CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);sheet.addMergedRegion(range);startIndex+=excels.get(x).getCols();}else{endIndex+=excels.get(x).getCols();CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);sheet.addMergedRegion(range);startIndex+=excels.get(x).getCols();}XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容if (excels.get(x).getCellStyle() != null) {cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式}cell.setCellStyle(fontStyle);}else{XSSFCell cell = row.createCell(x);cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容if (excels.get(x).getCellStyle() != null) {cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式}cell.setCellStyle(fontStyle);}}}}//创建内容public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List<Map<String,Object>> objs, Class<?> clazz)throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,ClassNotFoundException, ParseException {int rowindex = map.size();int maxKey = 0;List<ExcelBean> ems = new ArrayList<>();for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {if (entry.getKey() > maxKey) {maxKey = entry.getKey();}}ems = map.get(maxKey);List<Integer> widths = new ArrayList<Integer>(ems.size());for(Map<String,Object> obj : objs){XSSFRow row = sheet.createRow(rowindex);for(int i=0;i<ems.size();i++){ExcelBean em = (ExcelBean)ems.get(i);String propertyName = em.getPropertyName();Object value = obj.get(propertyName);XSSFCell cell = row.createCell(i);String cellValue = "";if("valid".equals(propertyName)){cellValue = value.equals(1)?"启用":"禁用";}else if(value==null){cellValue = "";}else if(value instanceof Date){cellValue = new SimpleDateFormat("yyyy-MM-dd").format(value);}else{cellValue = value.toString();}cell.setCellValue(cellValue);cell.setCellType(XSSFCell.CELL_TYPE_STRING);cell.setCellStyle(fontStyle2);sheet.autoSizeColumn(i);}rowindex++;}// 设置列宽for (int index = 0; index < widths.size(); index++) {Integer width = widths.get(index);width = width < 2500 ? 2500 : width + 300;width = width > 10000 ? 10000 + 300 : width + 300;sheet.setColumnWidth(index, width);}}}导入
ImportExcelController.java
package com.yang.controller;import com.yang.service.impl.ImportExcelServiceImpl;@Controller
@RequestMapping("/excel")
public class ImportExcelController {@Resourceprivate ImportExcelServiceImpl importExcelInfo;@RequestMapping(value = "/InputExcel.do")@ResponseBodypublic String impotr(@RequestParam("file") MultipartFile file,HttpServletRequest request) throws Exception {String flag = "02";// 上传标志// 获取上传的文件InputStream in = file.getInputStream();// 数据导入flag = importExcelInfo.importExcelInfo(in, file);in.close();return flag;}
}
ImportExcelServiceImpl.java
package com.yang.service.impl;@Service
public class ImportExcelServiceImpl implements ImportExcelService {private final static String excel2003L = ".xls"; // 2003- 版本的excelprivate final static String excel2007U = ".xlsx"; // 2007+ 版本的excel@Resourceprivate ImportExcelMapper importexcelmapper;public String importExcelInfo(InputStream in, MultipartFile file) throws Exception {Sheet sheet = null;String fileName = file.getOriginalFilename();// 创建Excel工作薄Workbook work = getWorkbook(in, fileName);if (null == work) {throw new Exception("创建Excel工作薄为空！");}// 遍历Excel中所有的sheetfor (int j = 0; j < work.getNumberOfSheets(); j++) {if(j==0){// 获取第一个sheet工作簿sheet = work.getSheetAt(j);if (sheet == null) {continue;}List<List<Object>> list = ExcelUtil.getBankListByExcel(in, work,sheet);Map<String, Object> map = new HashMap<String, Object>();// 遍历list数据，把数据放到map中for (int i = 0; i < list.size(); i++) {List<Object> row = list.get(i);map.put("name", row.get(0).toString());map.put("sex", row.get(1).toString());map.put("email", row.get(2).toString());map.put("dept_id", row.get(3).toString());importexcelmapper.insertInfoBatch(map);}}if(j==1){// 获取第二个sheet工作簿sheet = work.getSheetAt(j);if (sheet == null) {continue;}List<List<Object>> list = ExcelUtil.getBankListByExcel(in, work,sheet);Map<String, Object> map = new HashMap<String, Object>();// 遍历list数据，把数据放到map中for (int i = 0; i < list.size(); i++) {List<Object> row = list.get(i);map.put("name", row.get(0).toString());map.put("zhin", row.get(1).toString());importexcelmapper.insertInfoBatch2(map);}}}return "01";}/*** 描述：根据文件后缀，自适应上传文件的版本*/public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {Workbook wb = null;String fileType = fileName.substring(fileName.lastIndexOf("."));if (excel2003L.equals(fileType)) {wb = new HSSFWorkbook(inStr); // 2003-} else if (excel2007U.equals(fileType)) {wb = new XSSFWorkbook(inStr); // 2007+} else {throw new Exception("解析的文件格式有误！");}return wb;}
}ImportExcelMapper.java
package com.yang.dao;
public interface ImportExcelMapper {void insertInfoBatch(Map<String, Object> map);void insertInfoBatch2(Map<String, Object> map);
}ImportExcelMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yang.dao.ImportExcelMapper"><resultMap id="BaseResultMap" type="com.yang.entity.ImpT"><id column="id" jdbcType="INTEGER" property="id" /><result column="name" jdbcType="VARCHAR" property="name" /><result column="sex" jdbcType="CHAR" property="sex" /><result column="email" jdbcType="VARCHAR" property="email" /><result column="dept_id" jdbcType="INTEGER" property="deptId" /></resultMap><insert id="insertInfoBatch">insert into test (name,sex,email,dept_id) values(#{name },#{sex },#{email },#{dept_id })</insert><insert id="insertInfoBatch2">insert into test2 (name,zhin) values(#{name },#{zhin })</insert>
</mapper>
导出
ExportExcelController.java
package com.yang.controller;
@Controller
@RequestMapping("/excel")
public class ExportExcelController {@Resourceprivate ExportExcelServiceImpl exportExcelServiceImpl;@RequestMapping("/exportExcel.do")public @ResponseBody void export(HttpServletRequest request, HttpServletResponse response) throws Exception {response.reset(); // 清除buffer缓存response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Content-Disposition","attachment;filename="+new String("用户表.xlsx".getBytes(),"iso-8859-1"));XSSFWorkbook workbook = null;// 导出Excel对象workbook = exportExcelServiceImpl.exportExcelInfo();OutputStream output;try {output = response.getOutputStream();BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);bufferedOutPut.flush();workbook.write(bufferedOutPut);bufferedOutPut.close();} catch (IOException e) {e.printStackTrace();}}
}ExportExcelServiceImpl.java
package com.yang.service.impl;
@Service
public class ExportExcelServiceImpl implements ExportExcelService {@Resourceprivate ExportExcelMapper exportExcelMapper;public XSSFWorkbook exportExcelInfo() throws Exception {List<Map<String,Object>> list = exportExcelMapper.selectInfoBatch();//System.out.println(list);List<ExcelBean> excel = new ArrayList<ExcelBean>();Map<Integer,List<ExcelBean>> map = new LinkedHashMap<Integer, List<ExcelBean>>();//设置标题栏excel.add(new ExcelBean("姓名","name",0));excel.add(new ExcelBean("性别","sex",0));excel.add(new ExcelBean("邮箱","email", 0));excel.add(new ExcelBean("部门号","dept_id",0));map.put(0,excel);String sheetName = "用户信息表";//调用ExcelUtil方法XSSFWorkbook xssfWorkbook = ExcelUtil.createExcelFile(ImpT.class, list, map, sheetName);System.out.println(xssfWorkbook);return xssfWorkbook;}
}ExportExcelMapper.java
package com.yang.dao;public interface ExportExcelMapper {List<Map<String,Object>> selectInfoBatch();}ExportExcelMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yang.dao.ExportExcelMapper"><resultMap id="BaseResultMap" type="com.yang.entity.ImpT"><id column="id" jdbcType="INTEGER" property="id" /><result column="name" jdbcType="VARCHAR" property="name" /><result column="sex" jdbcType="CHAR" property="sex" /><result column="email" jdbcType="VARCHAR" property="email" /><result column="dept_id" jdbcType="INTEGER" property="deptId" /></resultMap><select id="selectInfoBatch" resultType="map">SELECT * FROM test</select></mapper>
JSP页面代码
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%String path = request.getContextPath();String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()+ path + "/";
%>
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>SSM框架Excel文件操作</title><link rel="stylesheet" href="css/style.css" type="text/css" />
<script type="text/javascript" src="js/jquery-1.10.2.js"></script>
<script type="text/javascript" src="js/jquery-2.1.1.min.js"></script></head><body><div id="container"><div class="header"><p>Excel</p></div><div class="form-body"><div class="form-group"><input type="file" class="form-input" id="upload" name="upload"value="" /><!--<label for="">选 择</label>--></div><div class="btn"><input type="button" onclick="uploadFile()" value="上传" class="form-btn form-btn-primary"><form action="excel/exportExcel.do"><input type="submit" value="下载" class="form-btn form-btn-warning"></form></div></div></div><script type="text/javascript">function uploadFile() {var file = $("#upload").val();file = file.substring(file.lastIndexOf('.'), file.length);if (file == '') {alert("上传文件不能为空！");} else if (file != '.xlsx' && file != '.xls') {alert("请选择正确的excel类型文件！");} else {ajaxFileUpload();}}function ajaxFileUpload() {var formData = new FormData();var name = $("#upload").val();formData.append("file", $("#upload")[0].files[0]);formData.append("name", name);$.ajax({url : "excel/InputExcel.do",type : "POST",async : false,data : formData,processData : false,contentType : false,beforeSend : function() {console.log("正在进行，请稍候");},success : function(e) {if (e == "01") {alert("导入成功");} else {alert("导入失败");}}});}</script><script>obj = document.getElementById('container');cWidth = window.innerWidth;cHeight = window.innerHeight;obj.style.marginLeft = (cWidth - 380) / 2 + "px";obj.style.marginTop = (cHeight - 300) / 2 + "px";window.onresize = function() {cWidth = window.innerWidth;cHeight = window.innerHeight;obj.style.marginLeft = (cWidth - 380) / 2 + "px";obj.style.marginTop = (cHeight - 300) / 2 + "px";}</script></body>
</html>
Java后端新手，以上便是在SSM下基于POI实现Excel表的导入(多sheet)/导出，主要的导入和导出的核心方法都封装在ExcelUtil这个工具类中，所以要学的小伙伴这个类希望能够重点看一下，书写顺序就是思路的顺序，项目编写顺序建议从mapper往前写。
希望在这里能够帮到大家。