最近在学习中遇到了excel的导入和导出操作，上网查了很长时间，没有一个特别适合小白的项目，因此自己做了一个简单的可以实现导入导出功能的项目，自己做了好长时间，特此记录一下，废话不多说，看代码。
1工程目录结构
2.在maven项目中导入相关的jar包依赖
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- 使用poi进行导入导出excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
3.创建一个工具类Excelutils
package com.itheima.crm.utils;
import java.beans.IntrospectionException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
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.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
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.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 com.itheima.crm.pojo.ExcelBean;
public class ExcelUtils {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 描述：获取IO流中的数据，组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws IOException
*/
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = this.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(this.getValue(cell));
}
list.add(li);
}
}
return list;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public 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;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
//解决excel类型问题，获得数值
public String getValue(Cell cell) {
String value = "";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
//数值型
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//如果是date类型则 ，获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
value = format.format(date);;
}else {// 纯数字
BigDecimal big=new BigDecimal(cell.getNumericCellValue());
value = big.toString();
//解决1234.0 去掉后面的.0
if(null!=value&&!"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length&&"0".equals(item[1])){
value=item[0];
}
}
}
break;
//字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case Cell.CELL_TYPE_FORMULA:
//读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
value = " "+ cell.getBooleanCellValue();
break;
default:
value = cell.getStringCellValue().toString();
}
if("null".endsWith(value.trim())){
value="";
}
return value;
}
/**
* 导出Excel表
* @param clazz 数据源model类型
* @param objs excel标题以及对应的model字段
* @param map 标题行数以及cell字体样式
* @param sheetName 工作簿名称
* @return
* @throws IntrospectionException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public static XSSFWorkbook createExcelFile(
Class<?> clazz,
List<Map<String,Object>> objs,
Map<Integer,List<ExcelBean>> map,
String sheetName) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, IntrospectionException{
//创建新的工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = workbook.createSheet(sheetName);
//设置excel的字体样式以及标题与内容的创建
createFont(workbook);//字体样式
createTableHeader(sheet,map);//创建标题
createTableRows(sheet,map,objs,clazz);//创建内容
System.out.println(workbook);
return workbook;
}
private static XSSFCellStyle fontStyle;
private static XSSFCellStyle fontStyle2;
private static void createFont(XSSFWorkbook workbook) {
//表头
fontStyle = workbook.createCellStyle();
XSSFFont font1 = workbook.createFont();
font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font1.setFontName("黑体");
font1.setFontHeightInPoints((short) 12);//字体大小
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 每行每个单元格对应的列头信息
*/
private static 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构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));
startIndex += excels.get(x).getCols();
}else{
endIndex += excels.get(x).getCols();
sheet.addMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));
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);
}
}
}
}
/**
* 为excel表中循环添加数据
* @param sheet
* @param map 字段名
* @param objs 查询的数据
* @param clazz 无用
*/
private static void createTableRows(
XSSFSheet sheet,
Map<Integer,List<ExcelBean>> map,
List<Map<String,Object>> objs,
Class<?> clazz)
throws IntrospectionException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
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(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);
}
}
}
4.创建实体类EXcelBean
package com.itheima.crm.pojo;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
public class ExcelBean implements java.io.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;
}
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;
}
}
5 PeopleServiceservice层方法
@Service
public interface PeopleService {
String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response);
XSSFWorkbook exportExcelInfo() throws Exception;
}
6PeopleServiceImpl方法
package com.itheima.crm.service.serviceImpl;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.itheima.crm.pojo.ExcelBean;
import com.itheima.crm.pojo.people;
import com.itheima.crm.service.PeopleService;
import com.itheima.crm.utils.ExcelUtils;
@Service
public class PeopleServiceImpl implements PeopleService{
@Autowired
private com.itheima.crm.mapper.peopleMapper peopleMapper;
public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response){
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("upfile");
if(file.isEmpty()){
try {
throw new Exception("文件不存在！");
} catch (Exception e) {
e.printStackTrace();
}
}
InputStream in =null;
try {
in = file.getInputStream();
} catch (IOException e) {
e.printStackTrace();
}
List<List<Object>> listob = null;
try {
listob = new ExcelUtils().getBankListByExcel(in,file.getOriginalFilename());
} catch (Exception e) {
e.printStackTrace();
}
//该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
people vo = new people();
people j = null;
try {
j = peopleMapper.selectByPrimaryKey(Integer.valueOf(String.valueOf(lo.get(0))));
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
System.out.println("没有新增");
}
vo.setId(Integer.valueOf(String.valueOf(lo.get(0))));
vo.setUserName(String.valueOf(lo.get(1)));
vo.setPassword(String.valueOf(lo.get(2)));
vo.setAge(Integer.valueOf(String.valueOf(lo.get(3))));
vo.setDate(Date.valueOf(String.valueOf(lo.get(4))));
if(j == null)
{
peopleMapper.insert(vo);
}
else
{
peopleMapper.updateByPrimaryKey(vo);
}
}
return "文件导入成功！";
}
public XSSFWorkbook exportExcelInfo() throws Exception {
List<Map<String,Object>> list = peopleMapper.findUserObject();
//System.out.println(list);
List<ExcelBean> excel = new ArrayList<ExcelBean>();
Map<Integer,List<ExcelBean>> map = new LinkedHashMap<Integer, List<ExcelBean>>();
//设置标题栏
excel.add(new ExcelBean("序号","id",0));
excel.add(new ExcelBean("姓名","user_name",0));
excel.add(new ExcelBean("密码","password", 0));
excel.add(new ExcelBean("年龄","age",0));
excel.add(new ExcelBean("时间","date",0));
map.put(0,excel);
String sheetName = "用户信息表";
//调用ExcelUtil方法
XSSFWorkbook xssfWorkbook = ExcelUtils.createExcelFile(people.class, list, map, sheetName);
System.out.println(xssfWorkbook);
return xssfWorkbook;
}
}
6 peopleMapper方法
public interface peopleMapper {
int deleteByPrimaryKey(Integer id);
int insert(people record);
int insertSelective(people record);
people selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(people record);
int updateByPrimaryKey(people record);
List<Map<String,Object>> findUserObject();
}
7peopleMapper.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.itheima.crm.mapper.peopleMapper" >
<resultMap id="BaseResultMap" type="com.itheima.crm.pojo.people" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="date" property="date" jdbcType="DATE" />
</resultMap>
<sql id="Base_Column_List" >
id, user_name, password, age, date
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from people
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from people
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.itheima.crm.pojo.people" >
insert into people (id, user_name, password,
age, date)
values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{age,jdbcType=INTEGER}, #{date,jdbcType=DATE})
</insert>
<insert id="insertSelective" parameterType="com.itheima.crm.pojo.people" >
insert into people
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="userName != null" >
user_name,
</if>
<if test="password != null" >
password,
</if>
<if test="age != null" >
age,
</if>
<if test="date != null" >
date,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="userName != null" >
#{userName,jdbcType=VARCHAR},
</if>
<if test="password != null" >
#{password,jdbcType=VARCHAR},
</if>
<if test="age != null" >
#{age,jdbcType=INTEGER},
</if>
<if test="date != null" >
#{date,jdbcType=DATE},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.itheima.crm.pojo.people" >
update people
<set >
<if test="userName != null" >
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="password != null" >
password = #{password,jdbcType=VARCHAR},
</if>
<if test="age != null" >
age = #{age,jdbcType=INTEGER},
</if>
<if test="date != null" >
date = #{date,jdbcType=DATE},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.itheima.crm.pojo.people" >
update people
set user_name = #{userName,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
age = #{age,jdbcType=INTEGER},
date = #{date,jdbcType=DATE}
where id = #{id,jdbcType=INTEGER}
</update>
<select id="findUserObject" resultType="map">
select * from people
</select>
</mapper>
8 ExcelController层实现方法
@Controller
@RequestMapping("/uploadExcel/*")
public class ExcelController {
@Autowired
private PeopleService peopleService;
/**
* 描述：通过 jquery.form.js 插件提供的ajax方式上传文件
* @param request
* @param response
* @throws Exception
*/
@ResponseBody
@RequestMapping(value="ajaxUpload.action",method={RequestMethod.GET,RequestMethod.POST})
public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
return peopleService.ajaxUploadExcel(request, response);
}
@RequestMapping(value = "download.action")
public void export(HttpServletRequest request,HttpServletResponse response) throws Exception{
response.reset(); //清除buffer缓存
//Map<String,Object> map=new HashMap<String,Object>();
// 指定下载的文件名
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+new String("用户表.xlsx".getBytes(),"iso-8859-1"));
//导出Excel对象
XSSFWorkbook workbook = peopleService.exportExcelInfo();
OutputStream output;
try {
output = response.getOutputStream();
BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
bufferedOutput.flush();
workbook.write(bufferedOutput);
bufferedOutput.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
9 前段界面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<html>
<head>
<base href="<%=basePath%>">
<script type="text/javascript" src="js/jquery-1.11.0.js"></script>
<script type="text/javascript" src="js/jquery-form.js"></script>
<title>My JSP 'index.jsp' starting page</title>
<script type="text/javascript">
//ajax 方式上传文件操作
$(document).ready(function(){
$('#btn').click(function(){
if(checkData()){
$('#form1').ajaxSubmit({
url:'uploadExcel/ajaxUpload.action',
dataType: 'text',
success: resutlMsg,
error: errorMsg
});
function resutlMsg(msg){
alert(msg);
$("#upfile").val("");
}
function errorMsg(){
alert("导入excel出错！");
}
}
});
});
//JS校验form表单信息
function checkData(){
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if("" == fileDir){
alert("选择需要导入的Excel文件！");
return false;
}
if(".xls" != suffix && ".xlsx" != suffix ){
alert("选择Excel格式的文件导入！");
return false;
}
return true;
}
</script>
</head>
<body>
<div>1.通过简单的form表单提交方式，进行文件的上</br> 2.通过jquery.form.js插件提供的form表单一步提交功能 </div></br>
<form method="POST" enctype="multipart/form-data" id="form1" action="uploadExcel/upload.action">
<table>
<tr>
<td>上传文件: </td>
<td> <input id="upfile" type="file" name="upfile"></td>
</tr>
<tr>
<td><input type="submit" value="提交" οnclick="checkData()"></td>
<td><input type="button" value="ajax方式提交" id="btn" name="btn" ></td>
</tr>
</table>
</form>
<br>
<form action="uploadExcel/download.action">
<input type="submit" value="导出excel信息">
</form>
</body>
10.功能结果实现图
前段界面
下载文件
| 
11源代码获取
链接：https://pan.baidu.com/s/1tVdWU_TMGqoAUGuZfRpF9w
提取码：2rje
复制这段内容后打开百度网盘手机App，操作更方便哦
+++++++++++++++++++++=============================================
————————————————————————————————————_——————————————————————————————————————
Java后端小白一枚，有错误希望大家相互指出，共同进步。
2860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
