好用的工具类
hutool工具类 hutool操作excel 这篇文章使用hutool的excel包封装项目中使用的excel工具类;
引入包
可以简单直接引入hutool-all,但是如果不用其他的话建议单独引入excel包
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.10</version>
</dependency>
------ 单独引入excel,里面含有core
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-poi</artifactId>
<version>5.8.10</version>
</dependency>
-- 这个poi是必须引入的
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
读取excel,我们通常两种方式,一种是读取url,一种是直接上传然后读取
直接上传
springmvc上传文件,举个例子,就是黑名单导入,就是读取第一列的手机号
@RequestMapping("upload")
public Response upload(@RequestParam("file") MultipartFile file,String source) throws IOException {
Set<String> firstColumnList = MyExcelUtil.getMobileByStream(file.getInputStream());
...dosomething
return Response.success();
}
进行读取操作,我这边只读取第一列的数据,可以将读取数据转为对应的类:
public static Set<String> getMobileByStream(InputStream in) {
ExcelReader reader = ExcelUtil.getReader(in);
List<List<Object>> dataList = reader.read();
HashSet<String> firstColumnList = Sets.newHashSet();
for (List<Object> row : dataList) {
Object firstColumnValue = row.get(0);
if (firstColumnValue != null) {
String mobile = firstColumnValue.toString().trim();
if (StringUtils.isNotBlank(mobile) && NumberUtil.isNumber(mobile)) {
firstColumnList.add(mobile);
}
}
}
return firstColumnList;
}
通过url读取Excel
创建链接,通过上面读取流的方法读取:
public static Set<String> getMobileByUrl(String url) {
HttpGet httpGet = new HttpGet(url);
CloseableHttpClient httpClient = HttpClients.createDefault();
Set<String> mobiles = new HashSet<>();
try {
CloseableHttpResponse response = httpClient.execute(httpGet);
HttpEntity entity = response.getEntity();
if (entity != null) {
mobiles = getMobileByStream(entity.getContent());
}
} catch (
IOException e) {
log.error("解析异常:", e);
throw new RuntimeException(e);
}
return mobiles;
}
导出excel,通过HttpServletResponse进行导出
第一个参数为数据,每一条一条的数据,第二个参数就是response,第三个参数是请求别名,key就是data参数的值,value是对应的标题
/**
* 导出为excel
*
* @param data
* @param response
* @param headerAlias
*/
public static void exportGeneral(Collection<?> data, HttpServletResponse response, Map<String, String> headerAlias) {
BigExcelWriter writer = ExcelUtil.getBigWriter();
if (Objects.isNull(headerAlias) || headerAlias.isEmpty()) {
writer.setOnlyAlias(false);
} else {
writer.setOnlyAlias(true);
writer.setHeaderAlias(headerAlias);
}
writer.write(data, true);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + RandomUtil.randomString(5) + ".xlsx");
try {
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
writer.close();
IoUtil.close(out);
} catch (Exception e) {
e.printStackTrace();
}
}
总的工具类
package com.study.springbootplus.util;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.http.HttpEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
/**
* @ClassName ExcelUtil
* @Author yida
* @Date 2022-12-22 13:46
* @Description ExcelUtil
*/
@Slf4j
public class MyExcelUtil {
public static Set<String> getMobileByUrl(String url) {
HttpGet httpGet = new HttpGet(url);
CloseableHttpClient httpClient = HttpClients.createDefault();
Set<String> mobiles = new HashSet<>();
try {
CloseableHttpResponse response = httpClient.execute(httpGet);
HttpEntity entity = response.getEntity();
if (entity != null) {
mobiles = getMobileByStream(entity.getContent());
}
} catch (
IOException e) {
log.error("解析异常:", e);
throw new RuntimeException(e);
}
return mobiles;
}
/**
* 只接受excel,第一列为手机号的流
*
* @param in
* @return
*/
public static Set<String> getMobileByStream(InputStream in) {
ExcelReader reader = ExcelUtil.getReader(in);
List<List<Object>> dataList = reader.read();
HashSet<String> firstColumnList = Sets.newHashSet();
for (List<Object> row : dataList) {
Object firstColumnValue = row.get(0);
if (firstColumnValue != null) {
String mobile = firstColumnValue.toString().trim();
if (StringUtils.isNotBlank(mobile) && NumberUtil.isNumber(mobile)) {
firstColumnList.add(mobile);
}
}
}
return firstColumnList;
}
/**
* 导出为excel
*
* @param data
* @param response
* @param headerAlias
*/
public static void exportGeneral(Collection<?> data, HttpServletResponse response, Map<String, String> headerAlias) {
BigExcelWriter writer = ExcelUtil.getBigWriter();
if (Objects.isNull(headerAlias) || headerAlias.isEmpty()) {
writer.setOnlyAlias(false);
} else {
writer.setOnlyAlias(true);
writer.setHeaderAlias(headerAlias);
}
writer.write(data, true);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + RandomUtil.randomString(5) + ".xlsx");
try {
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
writer.close();
IoUtil.close(out);
} catch (Exception e) {
e.printStackTrace();
}
}
}