效果：
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>工具类：
/**
* 操作导出的公共部分代码
* @param excelName 导出的文件名
* @param data 导出的数据
* @param colWidth 数据列宽度
* @param colTitle 数据列名，即标题 fields
* @param fields 返回的数据中对应实体的字段名
* @param valueFormat 值转换，用于处理需要转换的值，例如：状态 性别 等用数字标识的字段，导出后转成指定文字方便查看
* 格式：{
* '字段名':{
* 转换前的值:转换后的值
* },.....
* }
* @Param picFields 存放图片字段
* */
private void export(
String excelName, List data,
int[] colWidth, String[] colTitle,
String[] fields, JSONObject valueFormat,String[] picFields
) {
if(colTitle.length!=fields.length) {
log.error("数据列名长度和字段名长度不一致");
return;
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = workbook.createSheet("sheet1");
HSSFRow row = sheet1.createRow(0);
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment((short) 24);//(HorizontalAlignment.CENTER);
style.setVerticalAlignment((short) 24);//(VerticalAlignment.CENTER);
// 设置字体
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 24);
font.setBoldweight((short) 24);//setBold(true);
row.setHeightInPoints(20);
for(int i=0;i<colWidth.length;i++) {
sheet1.setColumnWidth(i, colWidth[i] * 256);//设置每列单元格的宽度
}
for(int i=0;i<colTitle.length;i++) {
row.createCell(i).setCellValue(colTitle[i]);//设置列标题
}
//数据
try {
for (Object object : data) {
if (object != null) {
JSONObject jsonObject = (JSONObject) JSONObject.toJSON(object);
int lastRowNum = sheet1.getLastRowNum();
HSSFRow lastRow = sheet1.createRow(lastRowNum + 1);
lastRow.setHeightInPoints(20);//设置行高
URL urlObj = null;
InputStream inStream = null;
HttpURLConnection conn = null;
HSSFClientAnchor anchor = null;
ByteArrayOutputStream byteArrayOut = null;
//通过实体字段获取数据
for(int i=0;i<fields.length;i++) {
//判断转换值的json中是否存在该字段
if (valueFormat.containsKey(fields[i])) {
//存在，将值转换后再存入单元格
String valueOld = jsonObject.getString(fields[i]);//数据库中的值
JSONObject getJson = valueFormat.getJSONObject(fields[i]);
String getNewValue = getJson.getString(valueOld);//得到要转换的值
lastRow.createCell(i).setCellValue(getNewValue);
}else {
//不存在，则将数据库中的值直接加入单元格中
lastRow.createCell(i).setCellValue(jsonObject.getString(fields[i]));
}
//判断是否存在于图片字段
if(picFields != null) {
lastRow.setHeightInPoints(50);//设置行高
for (String picField : picFields) {
if (fields[i].equals(picField)) {
//当前字段属于图片
JSONArray picList = jsonObject.getJSONArray(fields[i]);
for (int item = 0; item < picList.size(); item++){
byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = null;
// anchor主要用于设置图片的属性
anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) i, lastRowNum +1, (short) i, lastRowNum + 1);
anchor.setAnchorType(3);
HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
System.out.println(picList.get(item));
//判断是否为 http开始或者https开始的图片路径，如果是，发起网络请求加载图片，否在视为本地图片
String url = String.valueOf(picList.get(item));
if(url.indexOf("https")==-1 && url.indexOf("http")==-1){ //path中不存在http或https，视为本地地址
bufferImg = ImageIO.read(new File(url));
ImageIO.write(bufferImg, "jpg", byteArrayOut);
//插入图片
patriarch.createPicture(anchor,workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}else {
log.info("发起网络请求加载图片");
//path中存在http或https，发起网络请求
urlObj = new URL(url);
conn = (HttpURLConnection)urlObj.openConnection();
conn.setRequestMethod("GET");
inStream = conn.getInputStream();
byte[] byteData = readInputStream(inStream);
//插入图片
patriarch.createPicture(anchor,workbook.addPicture(byteData, HSSFWorkbook.PICTURE_TYPE_JPEG));
}
}
}
}
}
}
}
}
}catch (Exception e){
e.printStackTrace();
log.error("exprot error："+e,e);
}
String filename = excelName + ".xls";
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = servletRequestAttributes.getResponse();
// 设置文件输出头
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
OutputStream out;
try {
out = response.getOutputStream();
workbook.write(out);
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}private static byte[] readInputStream(InputStream inStream) throws Exception{
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
//创建一个Buffer字符串
byte[] buffer = new byte[1024];
//每次读取的字符串长度，如果为-1，代表全部读取完毕
int len = 0;
//使用一个输入流从buffer里把数据读取出来
while( (len=inStream.read(buffer)) != -1 ){
//用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
outStream.write(buffer, 0, len);
}
//关闭输入流
inStream.close();
//把outStream里的数据写入内存
return outStream.toByteArray();
}工具类
StringUtil:
package com.fzh.com.utils;
import java.util.regex.Pattern;
public class StringUtil {
public static Boolean isEmpty(String str){
if (str==null || str.trim().isEmpty() || "".equals(str.trim()) || "null".equals(str.trim())){
return true;
}
return false;
}
public static Boolean isNoEmpty(String str){
return !isEmpty(str);
}
/***
* 判断是否为整数数字
*/
public static boolean isInteger(String str) {
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();
}
}使用方法：
注意：userVo实体中的图片为数组，存放的是图片的访问地址
List<UserVo> list = userService.exportMunicipal(startTime,endTime);//数据库中查到的数据
String excelName = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));//excel表名称
int[] colWidth= { 10, 20, 20, 15, 20, 30};//列宽
String[] colTitle= {"编号","姓名","账号","性别","时间","图片"};//表头
String[] fields= {"id","name","username","sex","time","userpic"};//对应数据库查询出的数据
String[] picFields= {"userpic"};//fields中的图片字段
String valueFormatStr="{sex:{1:'男',0:'女'}}";//需要转换的数据，例如数据库中存下的性别为：1和0 ,则导出后对应：男女
JSONObject valueFormat = JSONObject.parseObject(valueFormatStr);
this.export(excelName, list, colWidth, colTitle, fields,valueFormat,picFields);UserVo实体
/**
* 导出数据
*/
public class UserVo{
private Integer id;//编号
private String name;//姓名
private String username;//账号
private Integer sex;//性别
private Integer time;//时间戳
private List<String> userpic; //图片地址列表
//省略getter setter toString将以上代码封装成工具类：资源地址
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
