在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 {
@Resource
private ImportExcelServiceImpl importExcelInfo;
@RequestMapping(value = "/InputExcel.do")
@ResponseBody
public 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- 版本的excel
private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
@Resource
private 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中所有的sheet
for (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 {
@Resource
private 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 {
@Resource
private 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往前写。
希望在这里能够帮到大家。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
