使用poi实现excel导入导出

最新推荐文章于 2022-07-22 09:15:01 发布

原创最新推荐文章于 2022-07-22 09:15:01 发布 · 489 阅读

0 ·

CC 4.0 BY-SA版权

工作日志专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何在Java项目中使用Apache POI库处理Excel文件的导入与导出。首先，在Maven中添加了POI依赖。接着，创建了`stuList.jsp`页面。然后，定义了两个工具类：`ExcelBean`用于数据封装，`ExcelUtil`实现了读取和写入Excel的核心功能。此外，还提供了一个辅助工具类。在控制层，导入操作接收文件，导出操作则通过`workBook`响应。在Service层，实现了数据的读取、保存和查询。最后，DAO层和Mapper接口及XML文件完成了数据持久化操作。

在maven中添加poi依赖：

<!-- poi依赖 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-examples</artifactId>
			<version>3.14</version>
		</dependency>

编写页面stuList.jsp：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/jquery/jquery-1.10.2.js"></script>
<script type="text/javascript">
  $(function(){
	  $("#imp").hide();
	 $("#in").bind("click",function(){
		 $("#excel").click();
	 }); 
	 $("#excel").bind("change",function(){
		 $("#submitFormDate").click();
	 });
	 
	 $("#out").bind("click",function(){
		window.location.href="exportData.action";
	 }); 
  });
  
</script>
</head>
<body>
学生信息<br>
<input type="button" id="in" value="导入"/>  <input type="button" id="out" value="导出"/>
<table width="60%" border="1px">
<thead>
<tr>
<th>Id</th>
<th>姓名</th>
<th>性别</th>
<th>出生年月</th>
<th>电话</th>
<th>毕业院校</th>
</tr>
</thead>
<tbody>
<c:forEach items="${stuList }" var="stu">
<tr>
<td>${stu.id }</td>
<td>${stu.name }</td>
<td>${stu.gender }</td>
<td>${stu.birthday }</td>
<td>${stu.tele }</td>
<td>${stu.collage }</td>
</tr>
</c:forEach>
</tbody>
</table>
<div id="imp">
<form action="importData.action" method="post" enctype="multipart/form-data">
<input  type="file" id="excel" name="excelFile"/>
<input type="submit" id="submitFormDate"/>
</form>
</div>
</body>
</html>

编写两个工具类，一个是ExcelBean:

package com.sl.linguan.util;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

//创建一个封装表格数据的bean
public class ExcelBean {
	
	private String headTextName; //表头(标题)名
	private String propertyName; //列名
	private Integer cols;//单元格数
	private XSSFCellStyle  cellStyle;
	public ExcelBean() {
		super();
	}
	public ExcelBean(String headTextName, String propertyName) {
		super();
		this.headTextName = headTextName;
		this.propertyName = propertyName;
	}
	public ExcelBean(String headTextName, String propertyName, Integer cols) {
		super();
		this.headTextName = headTextName;
		this.propertyName = propertyName;
		this.cols = cols;
	}
	public String getHeadTextName() {
		return headTextName;
	}
	public void setHeadTextName(String headTextName) {
		this.headTextName = headTextName;
	}
	public String getPropertyName() {
		return propertyName;
	}
	public void setPropertyName(String propertyName) {
		this.propertyName = propertyName;
	}
	public Integer getCols() {
		return cols;
	}
	public void setCols(Integer cols) {
		this.cols = cols;
	}
	public XSSFCellStyle getCellStyle() {
		return cellStyle;
	}
	public void setCellStyle(XSSFCellStyle cellStyle) {
		this.cellStyle = cellStyle;
	}
	
	

}

一个是ExcelUtil,这个工具类封装了poi读取excel文件和按照格式响应excel文件的核心方法:

package com.sl.linguan.util;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.regex.ParseException;


public class ExcelUtil {

	private final static String excel2003L = ".xls"; // 2003版的excel
	private final static String excel2007U = ".xlsx"; // 2007版的excel
    
	private static DateUtil dateUtil=new DateUtil();
	
	/*
	 * excel导入
	 */
	public static List<List<Object>> getStuListByExcel(InputStream in,
			String fileName) throws Exception {
		List<List<Object>> list = null;
		// 创建excel工作簿
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null; // 页
		Row row = null; // 行
		Cell cell = null; // 列
		list = new ArrayList<List<Object>>();

		// 遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			// 遍历当前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 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;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 */
	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;
	}

	public static XSSFWorkbook createExcelFile(Class clazz, List 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); // 居中
	}
	
	//生成列头
	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 objs, Class clazz)  
            throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,  
            ClassNotFoundException, ParseException {  
        int rowindex = map.size();  
        int maxKey = 0;  
        List<ExcelBean> ems = new ArrayList<ExcelBean>();  
        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 (Object obj : objs) {  
            XSSFRow row = sheet.createRow(rowindex);  
            for (int i = 0; i < ems.size(); i++) {  
                ExcelBean em = (ExcelBean) ems.get(i);  
                // 获得get方法  
                PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);  
                Method getMethod = pd.getReadMethod();  
                Object rtn = getMethod.invoke(obj);  
                String value = "";  
                // 如果是日期类型进行转换  
                if (rtn != null) {  
                    if (rtn instanceof Date) {  
                        value = dateUtil.dateToString((Date)rtn);  
                    } else if(rtn instanceof BigDecimal){  
                        NumberFormat nf = new DecimalFormat("#,##0.00");  
                        value=nf.format((BigDecimal)rtn).toString();  
                    } else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){  
                        value="--";  
                    }else {  
                        value = rtn.toString();  
                    }  
                }  
                XSSFCell cell = row.createCell(i);  
                cell.setCellValue(value);  
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);  
                cell.setCellStyle(fontStyle2);  
                // 获得最大列宽  
                int width = value.getBytes().length * 300;  
                // 还未设置，设置当前  
                if (widths.size() <= i) {  
                    widths.add(width);  
                    continue;  
                }  
                // 比原来大，更新数据  
                if (width > widths.get(i)) {  
                    widths.set(i, width);  
                }  
            }  
            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);  
        }  
    }  
	
	

}

还有另外一个需要的简单的工具类：

package com.sl.linguan.util;

import java.util.Date;

public class DateUtil {
	
	public String dateToString(Date date){
		
		return date.toString();
	}

}

控制层核心代码：

@Controller
public class StudentAction {
	
	@Resource
	private StudentService studentService;
	@RequestMapping("/importData.action")
	public String importData(HttpServletRequest request) throws IOException{
		MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;  
	    MultipartFile file = multipart.getFile("excelFile");
	    InputStream in = file.getInputStream(); 
	    studentService.importData(in,file);
	    in.close();
		return "redirect:stuList.action";
	}
	@RequestMapping("/exportData.action")
	public @ResponseBody void export(HttpServletRequest request, HttpServletResponse response){
	        // 指定下载的文件名  
	        response.setHeader("Content-Disposition", "attachment;filename=seseseses.xlsx");  
	        response.setContentType("application/vnd.ms-excel;charset=UTF-8");  
	        response.setHeader("Pragma", "no-cache");  
	        response.setHeader("Cache-Control", "no-cache");  
	        response.setDateHeader("Expires", 0);  
	        XSSFWorkbook workbook=null;  
	        //导出Excel对象  
	        workbook = studentService.exportExcelInfo();  
	        OutputStream output;  
	        try {  
	            output = response.getOutputStream();  
	            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);  
	            bufferedOutPut.flush();  
	            workbook.write(bufferedOutPut);  
	            bufferedOutPut.close();  
	        } catch (IOException e) {  
	            e.printStackTrace();  
	        }  
		
	}

}

在导入时，控制层负责接收文件，导出时负责通过workBook对象进行响应，具体对工具类的调用，我编写在service层

service接口：

public interface StudentService {

	List<Student> list();

	void importData(InputStream in, MultipartFile file);

	XSSFWorkbook exportExcelInfo();

}

实现类：

@Service
public class StudentServiceImpl implements StudentService{
    
	@Resource
	private StudentDao studentDao;
	
	public List<Student> list() {
		List<Student> list=studentDao.list();
		return list;
	}

	public void importData(InputStream in, MultipartFile file) {
		List<List<Object>> listob=null;
		try {
			listob = ExcelUtil.getStuListByExcel(in,file.getOriginalFilename());
		} catch (Exception e) {
			System.out.println("读取文件失败，，");
			e.printStackTrace();
		}  
	    List<Student> stuList = new ArrayList<Student>();  
	    //遍历listob数据，把数据放到List中  
	    for (int i = 0; i < listob.size(); i++) {  
	        List<Object> ob = listob.get(i);  
	        Student stu = new Student();  
	        //通过遍历实现把每一列封装成一个model中，再把所有的model用List集合装载
	        //列的下标从0开始
	        stu.setName(String.valueOf(ob.get(1)));
	        stu.setGender(String.valueOf(ob.get(2)));
	        stu.setBirthday(String.valueOf(ob.get(3)));
	        stu.setTele(String.valueOf(ob.get(4)));
	        stu.setCollage(String.valueOf(ob.get(5)));
	        
	        stuList.add(stu);  
	    }
	    
	    studentDao.insertInfoBatch(stuList);  
		
	}

	public XSSFWorkbook exportExcelInfo() {
		List<Student> list = studentDao.list();  
	    
	    List<ExcelBean> excel=new ArrayList<ExcelBean>();  
	    Map<Integer,List<ExcelBean>> map=new LinkedHashMap<Integer,List<ExcelBean>>();  
	    XSSFWorkbook xssfWorkbook=null;  
	    //设置标题栏  
	    excel.add(new ExcelBean("ID","id",0));  
	    excel.add(new ExcelBean("姓名","name",0));  
	    excel.add(new ExcelBean("性别","gender",0));  
	    excel.add(new ExcelBean("出生年月","birthday",0));  
	    excel.add(new ExcelBean("电话","tele",0));  
	    excel.add(new ExcelBean("毕业院校","collage",0));  
	    map.put(0, excel);  
	    String sheetName = "学生信息一";  
	    //调用ExcelUtil的方法  
			try {
				xssfWorkbook = ExcelUtil.createExcelFile(Student.class, list, map, sheetName);
			} catch (Exception e) {
				e.printStackTrace();
			}
	    return xssfWorkbook;  
	}

}

导入时就是读取文件中的数据，再调用dao对数据进行保存，导出时需要查询出来数据，下面是对dao的编写

public interface StudentDao extends BaseDao<Student>{

	List<Student> list();

	void insertInfoBatch(List<Student> stuList);

}

实现类

@Repository
public class StudentDaoImpl extends BaseDaoImpl<Student> implements StudentDao{
    
	@Resource
	private SqlSessionTemplate template;

	public List<Student> list() {
		List<Student> list=template.selectList("StudentDao.list");
		return list;
	}

	public void insertInfoBatch(List<Student> stuList) {
		template.insert("StudentDao.insertInfoBatch", stuList);
		
	}


}

mapper.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="StudentDao">
	
  	
  	<!-- 列表-->
	<select id="list" resultType="com.sl.linguan.sys.domain.Student">
	    select * from student
	</select>
	
	<insert id="insertInfoBatch" parameterType="java.util.List" useGeneratedKeys="true">  
    insert into student (name,gender,birthday,tele,collage)  
    values  
    <foreach collection="list" item="item" index="index" separator=",">  
      (#{item.name}, #{item.gender}, #{item.birthday},#{item.tele}, #{item.collage})  
    </foreach>  
    </insert> 
	
	
</mapper>

至此一个完整的导入导出就完成了