在SSM下基于POI实现Excel表的导入(多sheet)/导出

  • 时间:
  • 来源:互联网

在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="">&nbsp;&nbsp;&nbsp;</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往前写。

希望在这里能够帮到大家。

SS_Lionet
发布了2 篇原创文章 · 获赞 0 · 访问量 48
私信 关注

本文链接http://element-ui.cn/news/show-992.aspx