java实现导出Excel的功能

导出excel是咱java开发的必备技能啦，之前项目有这个功能，现在将其独立出来，分享一下。

所用技术就是springboot，然后是mvc架构模式。

废话不多说，直接上代码了，源码点末尾链接就可以下载。

（1）新建一个springboot项目（可以官网https://start.spring.io/直接生成下载，然后导入eclipse），项目结构如下：

（2）修改pom文件，添加依赖；

<dependency>
 <groupid>org.springframework.boot</groupid>
 <artifactid>spring-boot-starter-web</artifactid>
 </dependency>

 <dependency>
 <groupid>org.springframework.boot</groupid>
 <artifactid>spring-boot-starter-test</artifactid>
 <scope>test</scope>
 </dependency>

 <!-- 导出excel -->
 <dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi</artifactid>
 <version>3.14</version>
 </dependency>

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
 <dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi-ooxml</artifactid>
 <version>3.14</version>
 </dependency>

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-contrib -->
 <dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi-contrib</artifactid>
 <version>3.6</version>
 </dependency>
 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
 <dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi-ooxml-schemas</artifactid>
 <version>3.17</version>
 </dependency>
 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
 <dependency>
 <groupid>org.apache.poi</groupid>
 <artifactid>poi-scratchpad</artifactid>
 <version>3.17</version>
 </dependency>

（3）新建一个实体类，命名为user.java；

package com.twf.springcloud.exportexcel.po;
import java.io.serializable;
public class user implements serializable{
 private static final long serialversionuid = -9180229310895087286l;
 private string name; // 姓名
 private string sex; // 性别
 private integer age; // 年龄
 private string phoneno; // 手机号
 private string address; // 地址
 private string hobby; // 爱好
 public user(string name, string sex, integer age, string phoneno, string address, string hobby) {
 super();
 this.name = name;
 this.sex = sex;
 this.age = age;
 this.phoneno = phoneno;
 this.address = address;
 this.hobby = hobby;
 }
 public string getname() {
 return name;
 }
 public void setname(string name) {
 this.name = name;
 }
 public string getsex() {
 return sex;
 }
 public void setsex(string sex) {
 this.sex = sex;
 }
 public integer getage() {
 return age;
 }
 public void setage(integer age) {
 this.age = age;
 }
 public string getphoneno() {
 return phoneno;
 }
 public void setphoneno(string phoneno) {
 this.phoneno = phoneno;
 }
 public string getaddress() {
 return address;
 }
 public void setaddress(string address) {
 this.address = address;
 }
 public string gethobby() {
 return hobby;
 }
 public void sethobby(string hobby) {
 this.hobby = hobby;
 }
 @override
 public string tostring() {
 return "user [name=" + name + ", sex=" + sex + ", age=" + age + ", phoneno=" + phoneno + ", address=" + address
 + ", hobby=" + hobby + "]";
 }
}

（4）新建一个excel样式工具类；

package com.twf.springcloud.exportexcel.utils;
import org.apache.poi.hssf.usermodel.hssfcellstyle;
import org.apache.poi.hssf.usermodel.hssffont;
import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.hssf.util.hssfcolor;
import org.apache.poi.ss.usermodel.cellstyle;
import org.apache.poi.ss.usermodel.font;
import org.apache.poi.xssf.streaming.sxssfcell;
import org.apache.poi.xssf.streaming.sxssfrow;
import org.apache.poi.xssf.streaming.sxssfsheet;
import org.apache.poi.xssf.streaming.sxssfworkbook;
/**
 * excle样式工具类
 */
public class excelformatutil {
 /**
 * 设置报表头样式
 * @param workbook
 * @return
 */
 public static cellstyle headsytle(sxssfworkbook workbook){
 // 设置style1的样式，此样式运用在第二行
 cellstyle style1 = workbook.createcellstyle();// cell样式
 // 设置单元格背景色，设置单元格背景色以下两句必须同时设置
 style1.setfillpattern(hssfcellstyle.solid_foreground);// 设置填充样式
 style1.setfillforegroundcolor(hssfcolor.grey_25_percent.index);// 设置填充色
 // 设置单元格上、下、左、右的边框线
 style1.setborderbottom(hssfcellstyle.border_thin);
 style1.setborderleft(hssfcellstyle.border_thin);
 style1.setborderright(hssfcellstyle.border_thin);
 style1.setbordertop(hssfcellstyle.border_thin);
 font font1 = workbook.createfont();// 创建一个字体对象
 font1.setboldweight((short) 10);// 设置字体的宽度
 font1.setfontheightinpoints((short) 10);// 设置字体的高度
 font1.setboldweight(hssffont.boldweight_bold);// 粗体显示
 style1.setfont(font1);// 设置style1的字体
 style1.setwraptext(true);// 设置自动换行
 style1.setalignment(hssfcellstyle.align_center);// 设置单元格字体显示居中（左右方向）
 style1.setverticalalignment(hssfcellstyle.vertical_center);// 设置单元格字体显示居中(上下方向)
 return style1;
 }
 /**
 * 设置报表体样式
 * @param wb
 * @return
 */
 public static cellstyle contentstyle(sxssfworkbook wb){
 // 设置style1的样式，此样式运用在第二行
 cellstyle style1 = wb.createcellstyle();// cell样式
 // 设置单元格上、下、左、右的边框线
 style1.setborderbottom(hssfcellstyle.border_thin);
 style1.setborderleft(hssfcellstyle.border_thin);
 style1.setborderright(hssfcellstyle.border_thin);
 style1.setbordertop(hssfcellstyle.border_thin);
 style1.setwraptext(true);// 设置自动换行
 style1.setalignment(hssfcellstyle.align_left);// 设置单元格字体显示居中（左右方向）
 style1.setverticalalignment(hssfcellstyle.vertical_center);// 设置单元格字体显示居中(上下方向)
 return style1;
 }
 /**
 * 设置报表标题样式
 * @param workbook
 * @return
 */
 public static hssfcellstyle titlesytle(hssfworkbook workbook,short color,short fontsize){
 // 设置style1的样式，此样式运用在第二行
 hssfcellstyle style1 = workbook.createcellstyle();// cell样式
 // 设置单元格背景色，设置单元格背景色以下两句必须同时设置
 //style1.setfillpattern(hssfcellstyle.solid_foreground);// 设置填充样式
 //short fcolor = color;
 if(color != hssfcolor.white.index){
 style1.setfillforegroundcolor(color);// 设置填充色 
 }
 // 设置单元格上、下、左、右的边框线
 style1.setborderbottom(hssfcellstyle.border_thin);
 style1.setborderleft(hssfcellstyle.border_thin);
 style1.setborderright(hssfcellstyle.border_thin);
 style1.setbordertop(hssfcellstyle.border_thin);
 hssffont font1 = workbook.createfont();// 创建一个字体对象
 font1.setboldweight(fontsize);// 设置字体的宽度
 font1.setfontheightinpoints(fontsize);// 设置字体的高度
 font1.setboldweight(hssffont.boldweight_bold);// 粗体显示
 style1.setfont(font1);// 设置style1的字体
 style1.setwraptext(true);// 设置自动换行
 style1.setalignment(hssfcellstyle.align_center);// 设置单元格字体显示居中（左右方向）
 style1.setverticalalignment(hssfcellstyle.vertical_center);// 设置单元格字体显示居中(上下方向)
 return style1;
 }
 /**
 *设置表头 
 * @param sheet
 */
 public static void inittitleex(sxssfsheet sheet, cellstyle header,string title[],int titlelength[]) {
 sxssfrow row0 = sheet.createrow(0);
 row0.setheight((short) 800); 
 for(int j = 0;j<title.length; j++) {
 sxssfcell cell = row0.createcell(j);
 //设置每一列的字段名
 cell.setcellvalue(title[j]);
 cell.setcellstyle(header);
 sheet.setcolumnwidth(j, titlelength[j]);
 }
 }
}

（5）新建一个service接口；

package com.twf.springcloud.exportexcel.sevice;
import javax.servlet.http.httpservletrequest;
import javax.servlet.http.httpservletresponse;
import org.springframework.http.responseentity;
public interface exportservice {
 responseentity<byte[]> exportexcel(httpservletrequest request, httpservletresponse response);
}

（6）新建一个service接口实现类；

package com.twf.springcloud.exportexcel.sevice.impl;

import java.io.bytearrayinputstream;
import java.io.bytearrayoutputstream;
import java.io.ioexception;
import java.io.inputstream;
import java.util.arraylist;
import java.util.list;

import javax.servlet.http.httpservletrequest;
import javax.servlet.http.httpservletresponse;

import org.apache.poi.ss.usermodel.cellstyle;
import org.apache.poi.xssf.streaming.sxssfcell;
import org.apache.poi.xssf.streaming.sxssfrow;
import org.apache.poi.xssf.streaming.sxssfsheet;
import org.apache.poi.xssf.streaming.sxssfworkbook;
import org.slf4j.logger;
import org.slf4j.loggerfactory;
import org.springframework.http.responseentity;
import org.springframework.stereotype.service;

import com.twf.springcloud.exportexcel.controller.basefrontcontroller;
import com.twf.springcloud.exportexcel.po.user;
import com.twf.springcloud.exportexcel.sevice.exportservice;
import com.twf.springcloud.exportexcel.utils.excelformatutil;

@service
public class exportserviceimpl implements exportservice{
 logger logger = loggerfactory.getlogger(exportserviceimpl.class);

 @override
 public responseentity<byte[]> exportexcel(httpservletrequest request, httpservletresponse response) {
 try {
 logger.info(">>>>>>>>>>开始导出excel>>>>>>>>>>");
 
 // 造几条数据
 list<user> list = new arraylist<>();
 list.add(new user("唐三藏", "男", 30, "13411111111", "东土大唐", "取西经"));
 list.add(new user("孙悟空", "男", 29, "13411111112", "菩提院", "打妖怪"));
 list.add(new user("猪八戒", "男", 28, "13411111113", "高老庄", "偷懒"));
 list.add(new user("沙悟净", "男", 27, "13411111114", "流沙河", "挑担子"));
 
 basefrontcontroller basefrontcontroller = new basefrontcontroller();
 return basefrontcontroller.buildresponseentity(export((list<user>) list), "用户表.xls");
 } catch (exception e) {
 e.printstacktrace();
 logger.error(">>>>>>>>>>导出excel 异常，原因为：" + e.getmessage());
 }
 return null;
 }

 private inputstream export(list<user> list) {
 logger.info(">>>>>>>>>>>>>>>>>>>>开始进入导出方法>>>>>>>>>>");
 bytearrayoutputstream output = null;
 inputstream inputstream1 = null;
 sxssfworkbook wb = new sxssfworkbook(1000);// 保留1000条数据在内存中
 sxssfsheet sheet = wb.createsheet();
 // 设置报表头样式
 cellstyle header = excelformatutil.headsytle(wb);// cell样式
 cellstyle content = excelformatutil.contentstyle(wb);// 报表体样式
 
 // 每一列字段名
 string[] strs = new string[] { "姓名", "性别", "年龄", "手机号", "地址","爱好" };
 
 // 字段名所在表格的宽度
 int[] ints = new int[] { 5000, 5000, 5000, 5000, 5000, 5000 };
 
 // 设置表头样式
 excelformatutil.inittitleex(sheet, header, strs, ints);
 logger.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");
 
 if (list != null && list.size() > 0) {
 logger.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>");
 for (int i = 0; i < list.size(); i++) {
 user user = list.get(i);
 sxssfrow row = sheet.createrow(i + 1);
 int j = 0;

 sxssfcell cell = row.createcell(j++);
 cell.setcellvalue(user.getname()); // 姓名
 cell.setcellstyle(content);

 cell = row.createcell(j++);
 cell.setcellvalue(user.getsex()); // 性别
 cell.setcellstyle(content);

 cell = row.createcell(j++);
 cell.setcellvalue(user.getage()); // 年龄
 cell.setcellstyle(content);

 cell = row.createcell(j++);
 cell.setcellvalue(user.getphoneno()); // 手机号
 cell.setcellstyle(content);

 cell = row.createcell(j++);
 cell.setcellvalue(user.getaddress()); // 地址
 cell.setcellstyle(content);
 
 cell = row.createcell(j++);
 cell.setcellvalue(user.gethobby()); // 爱好
 cell.setcellstyle(content);
 }
 logger.info(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>");
 }
 try {
 output = new bytearrayoutputstream();
 wb.write(output);
 inputstream1 = new bytearrayinputstream(output.tobytearray());
 output.flush();
 } catch (exception e) {
 e.printstacktrace();
 } finally {
 try {
 if (output != null) {
 output.close();
 if (inputstream1 != null)
 inputstream1.close();
 }
 } catch (ioexception e) {
 e.printstacktrace();
 }
 }
 return inputstream1;
 }
}

（7）新建一个下载文件的通用controller；

package com.twf.springcloud.exportexcel.controller;

import java.io.inputstream; 
import java.net.urlencoder;
import java.util.hashmap;
import java.util.map;

import org.apache.poi.util.ioutils;
import org.slf4j.logger;
import org.slf4j.loggerfactory;
import org.springframework.http.httpheaders;
import org.springframework.http.httpstatus;
import org.springframework.http.responseentity;
import org.springframework.util.stringutils;
import org.springframework.validation.annotation.validated;

@validated
public class basefrontcontroller {
 
 /**
 * slf4j 日志 logger
 */
 protected final logger logger = loggerfactory.getlogger(this.getclass());

 /**
 * 下载文件,纯springmvc的api来完成
 *
 * @param is 文件输入流
 * @param name 文件名称,带后缀名
 *
 * @throws exception
 */
 public responseentity<byte[]> buildresponseentity(inputstream is, string name) throws exception {
 logger.info(">>>>>>>>>>>>>>>>>>>>开始下载文件>>>>>>>>>>");
 if (this.logger.isdebugenabled())
 this.logger.debug("download: " + name);
 httpheaders header = new httpheaders();
 string filesuffix = name.substring(name.lastindexof('.') + 1);
 filesuffix = filesuffix.tolowercase();
 
 map<string, string> arguments = new hashmap<string, string>();
 arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 arguments.put("xls", "application/vnd.ms-excel");
 
 string contenttype = arguments.get(filesuffix);
 header.add("content-type", (stringutils.hastext(contenttype) ? contenttype : "application/x-download"));
 if(is!=null && is.available()!=0){
 header.add("content-length", string.valueof(is.available()));
 header.add("content-disposition", "attachment;filename*=utf-8'zh_cn'" + urlencoder.encode(name, "utf-8"));
 byte[] bs = ioutils.tobytearray(is);
 logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-有记录>>>>>>>>>>");
 logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
 return new responseentity<>(bs, header, httpstatus.ok);
 }else{
 string string="数据为空";
 header.add("content-length", "0");
 header.add("content-disposition", "attachment;filename*=utf-8'zh_cn'" + urlencoder.encode(name, "utf-8"));
 logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-无记录>>>>>>>>>>");
 logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
 return new responseentity<>(string.getbytes(), header, httpstatus.ok);
 }
 }
}

（8）新建一个controller，作为请求的入口；

package com.twf.springcloud.exportexcel.controller;

import javax.servlet.http.httpservletrequest;
import javax.servlet.http.httpservletresponse;

import org.springframework.beans.factory.annotation.autowired;
import org.springframework.http.responseentity;
import org.springframework.web.bind.annotation.requestmapping;
import org.springframework.web.bind.annotation.restcontroller;

import com.twf.springcloud.exportexcel.sevice.exportservice;

@restcontroller
@requestmapping("/exportexcel/")
public class exportcontroller {
 
 @autowired
 private exportservice exportservice;

 // 导出excel
 @requestmapping("exportexcel")
 public responseentity<byte[]> exportexcel(httpservletrequest request, httpservletresponse response) {
 return exportservice.exportexcel(request,response);
 }
}

（9）运行exportexcelapplication，浏览器访问http://localhost:8080/exportexcel/exportexcel，可以下载excel，打开如下：

（10）

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。