本文在[大哥的前提下小小修改(https://www.cnblogs.com/linjiqin/p/10975761.html),在此感谢!!
实现了基本的导入导出功能,导入功能没有怎么测试,导出功能目前支持简单样式,可根据自己需求更改比如列宽,行高等样式.话不多说,开干!!!
1.首先导入maven包,
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
2.自定义实体类所需要的bean(Excel属性标题、位置等)
import java.lang.annotation.*;
/**
* 自定义实体类所需要的bean(Excel属性标题、位置等)
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
/**
* Excel标题
*
* @return
* @author Lynch
*/
String value() default "";
/**
* Excel从左往右排列位置
*
* @return
* @author Lynch
*/
int col() default 0;
}
- ExcelUtils编写
package com.framework.utils;
import com.framework.bean.domain.ExcelColumn;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Encoder;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;
public class ExcelUtils {
private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
public static <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file) {
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
log.error("上传文件格式不正确");
}
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if (fileName.endsWith(EXCEL2007)) {
// FileInputStream is = new FileInputStream(new File(path));
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith(EXCEL2003)) {
// FileInputStream is = new FileInputStream(new File(path));
workbook = new HSSFWorkbook(is);
}
if (workbook != null) {
//类映射 注解 value-->bean columns
Map<String, List<Field>> classMap = new HashMap<>();
List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
fields.forEach(
field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
String value = annotation.value();
if (StringUtils.isBlank(value)) {
return;//return起到的作用和continue是相同的 语法
}
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
}
);
//索引-->columns
Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
//默认读取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
boolean firstRow = true;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
//首行 提取注解
if (firstRow) {
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (classMap.containsKey(cellValue)) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
firstRow = false;
} else {
//忽略空白行
if (row == null) {
continue;
}
try {
T t = cls.newInstance();
//判断是否为空白行
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
if (reflectionMap.containsKey(j)) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (StringUtils.isNotBlank(cellValue)) {
allBlank = false;
}
List<Field> fieldList = reflectionMap.get(j);
fieldList.forEach(
x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
}
}
);
}
}
if (!allBlank) {
dataList.add(t);
} else {
log.warn(String.format("row:%s is blank ignore!", i));
}
} catch (Exception e) {
log.error(String.format("parse row:%s exception!", i), e);
}
}
}
}
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
}
}
}
return dataList;
}
private static <T> void handleField(T t, String value, Field field) throws Exception {
Class<?> type = field.getType();
if (type == null || type == void.class || StringUtils.isBlank(value)) {
return;
}
if (type == Object.class) {
field.set(t, value);
//数字类型
} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
if (type == int.class || type == Integer.class) {
field.set(t, NumberUtils.toInt(value));
} else if (type == long.class || type == Long.class) {
field.set(t, NumberUtils.toLong(value));
} else if (type == byte.class || type == Byte.class) {
field.set(t, NumberUtils.toByte(value));
} else if (type == short.class || type == Short.class) {
field.set(t, NumberUtils.toShort(value));
} else if (type == double.class || type == Double.class) {
field.set(t, NumberUtils.toDouble(value));
} else if (type == float.class || type == Float.class) {
field.set(t, NumberUtils.toFloat(value));
} else if (type == char.class || type == Character.class) {
field.set(t, CharUtils.toChar(value));
} else if (type == boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == BigDecimal.class) {
field.set(t, new BigDecimal(value));
}
} else if (type == Boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == Date.class) {
//
field.set(t, value);
} else if (type == String.class) {
field.set(t, value);
} else {
Constructor<?> constructor = type.getConstructor(String.class);
field.set(t, constructor.newInstance(value));
}
}
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else {
return new BigDecimal(cell.getNumericCellValue()).toString();
}
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return StringUtils.trimToEmpty(cell.getStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return StringUtils.trimToEmpty(cell.getCellFormula());
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return "";
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return "ERROR";
} else {
return cell.toString().trim();
}
}
public static <T> void writeExcel(String fileName, HttpServletResponse response, List<T> dataList, Class<T> cls) throws UnsupportedEncodingException {
Field[] fields = cls.getDeclaredFields();
List<Field> fieldList = Arrays.stream(fields)
.filter(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null && annotation.col() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int col = 0;
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
col = annotation.col();
}
return col;
})).collect(Collectors.toList());
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
AtomicInteger ai = new AtomicInteger();
/**
* 第一行标题
*/
{
// 产生表格标题行
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String columnName = "";
if (annotation != null) {
columnName = annotation.value();
}
Cell cell = row.createCell(aj.getAndIncrement());
// 单元格样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
//设置列宽(列号数-1,坑)
int width = columnName.getBytes().length * 256 + 200;
sheet.setColumnWidth(aj.get() - 1, width);
//字体设置
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
/**
* 内容设置
*/
if (CollectionUtils.isNotEmpty(dataList)) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setLocked(false);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);
dataList.forEach(t -> {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
fieldList.forEach(field -> {
Class<?> type = field.getType();
Object value = "";
try {
value = field.get(t);
} catch (Exception e) {
e.printStackTrace();
}
//获得字段名字,根据最长的value值设置行高
// String name = field.getName();
// String dbname = "history";
// if (dbname.equals(name)) {
// int length = value.toString().split("\n").length;
// row1.setHeight((short) (length * 256 + 300));
// }
Cell cell = row1.createCell(aj.getAndIncrement());
if (value != null) {
cell.setCellStyle(cellStyle);
if (type == Date.class) {
cell.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
cell.setCellValue(value.toString());
}
});
});
}
//冻结窗格
wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
//浏览器下载excel
buildExcelDocument(fileName + ".xlsx", wb, response);
//生成excel文件
// buildExcelFile(".\\default.xlsx",wb);
}
/**
* 浏览器下载excel
*
* @param fileName
* @param wb
* @param response
*/
private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response, HttpServletRequest request) {
try {
//通过获取请求头中浏览器信息
String agent = request.getHeader("User-Agent");
//根据浏览器不同将文本进行编码
if (agent.contains("Firefox")) { // 火狐浏览器
fileName = "=?UTF-8?B?"
+ new BASE64Encoder().encode(fileName.getBytes("utf-8"))
+ "?=";
fileName = fileName.replaceAll("\r\n", "");
} else { // IE及其他浏览器
fileName = URLDecoder.decode(fileName, "utf-8");
fileName = fileName.replace("+", " ");
}
//设置头信息
response.setHeader("contentType", "application/vnd.ms-excel");
response.setHeader("content-disposition", "attachment;fileName=" + fileName);
OutputStream stream = response.getOutputStream();
wb.write(stream);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 浏览器下载excel
*
* @param fileName
* @param wb
* @param response
*/
private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
try {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
String encode = URLEncoder.encode(fileName, "utf-8");
String decode = URLDecoder.decode(fileName, "utf-8");
// response.setHeader("Content-Disposition", "attachment;filename=" + decode);
response.setHeader("Content-Disposition", "inline;filename=" + toUtf8String(fileName));
response.flushBuffer();
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
public static String toUtf8String(String s) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < s.length(); i++) {
char c = s.charAt(i);
if (c >= 0 && c <= 255) {
sb.append(c);
} else {
byte[] b;
try {
b = Character.toString(c).getBytes("utf-8");
} catch (Exception ex) {
System.out.println(ex);
b = new byte[0];
}
for (int j = 0; j < b.length; j++) {
int k = b[j];
if (k < 0) {
k += 256;
}
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}
/**
* 生成excel文件
*
* @param path 生成excel路径
* @param wb
*/
private static void buildExcelFile(String path, Workbook wb) {
File file = new File(path);
if (file.exists()) {
file.delete();
}
try {
wb.write(new FileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 把输入字符串的首字母改成大写
*
* @param str
* @return
*/
private static String upperStr(String str) {
char[] ch = str.toCharArray();
if (ch[0] >= 'a' && ch[0] <= 'z') {
ch[0] = (char) (ch[0] - 32);
}
return new String(ch);
}
}
- 定义导出实体类
package com.framework.bean.domain;
import lombok.Data;
/**
*
*/
@Data
public class BusClick {
@ExcelColumn(value = "WeChat ID(ユーザーID)", col = 1)
private String openId;
@ExcelColumn(value = "ニックネーム", col = 2)
private String nickName;
@ExcelColumn(value = "氏名", col = 3)
private String name;
@ExcelColumn(value = "登録時間", col = 4)
private String createTime;
@ExcelColumn(value = "性別", col = 5)
private String gender;
@ExcelColumn(value = "年齢", col = 6)
private Integer age;
@ExcelColumn(value = "電話番号", col = 7)
private String mobile;
@ExcelColumn(value = "活動拠点(エリア)", col = 8)
private String city;
@ExcelColumn(value = "メールアドレス", col = 9)
private String email;
@ExcelColumn(value = "自己紹介", col = 10)
private String introduce;
@ExcelColumn(value = "学歴", col = 11)
private String education;
@ExcelColumn(value = "企業名+役職", col = 12)
private String work;
@ExcelColumn(value = "エントリー履歴", col = 13)
private String match;
@ExcelColumn(value = " 受賞履歴 ", col = 14)
private String result;
}
5.Controller层代码编写
package com.example.controller.api;
import com.alibaba.fastjson.JSON;
import com.example.bean.parameterForm.setting.SettingForm;
import com.example.bean.parameterForm.setting.SettingUpdateForm;
import com.example.bean.tableVO.SettingVO;
import com.example.service.ISettingService;
import com.framework.bean.domain.BusClick;
import com.framework.bean.domain.ResponseData;
import com.framework.common.IdPM;
import com.framework.utils.ExcelUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.IOException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
/**
* <p>
* Setting前端控制器
* </p>
* setting
* setting
*/
@RestController
@Api(tags = "设置 Setting")
@RequestMapping("/setting")
public class SettingController {
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<BusClick> resultList = new ArrayList<BusClick>();
BusClick busClick = new BusClick();
busClick.setOpenId("OSFSADFSADF");
busClick.setNickName("HAHA");
busClick.setName("name");
busClick.setGender("男");
busClick.setAge(21);
busClick.setMobile("18885522233");
busClick.setCity("天津");
busClick.setEmail("163@csdf.com");
busClick.setIntroduce("");
busClick.setEducation("");
busClick.setWork("广告文案\\n\" +\n" +
" \"很多企业中,都有了的专职的文案人员,只有当需要搞一些大型推广活动、做商业策划案、写可行性分析报告等需求量大的项目时," +
"才需要对外寻求合作。以往一般企业都会找广告、文化传媒等公司合作。这些公司一般都有专业的文案、设计团队,经验也相对丰富,但因为业务量大,范围广泛," +
"在针对性方面会较为薄 弱。随着社会经济不断发展,对专业文案的要求更加严格,逐渐衍生了一些专注于文字服务的文案策划公司。这类企业发展速度很快,大多数都是从工" +
"作室形式转型而来,也有从文化传播机构独立出来的。\\n\" +\n" +
" \"随着中国广告业二十余年的迅猛发展,广告公司的经营范围,操作流程,工作方式都在变化,文案的角色由无闻转为配角,现正昂首阔步走向台面,成为主角");
busClick.setResult("广告文案\\n\" +\n" +
" \"很多企业中,都有了的专职的文案人员,只有当需要搞一些大型推广活动、做商业策划案、写可行性分析报告等需求量大的项目时,才需要对外寻求合作。" +
"以往一般企业都会找广告、文化传媒等公司合作。这些公司一般都有专业的文案、设计团队,经验也相对丰富,但因为业务量大,范围广泛,在针对性方面会较为薄 弱。随着社会经济不断发展," +
"对专业文案的要求更加严格,逐渐衍生了一些专注于文字服务的文案策划公司。这类企业发展速度很快,大多数都是从工作室形式转型而来,也有从文化传播机构独立出来的。\\n\" +\n" +
" \"随着中国广告业二十余年的迅猛发展,广告公司的经营范围,操作流程,工作方式都在变化,文案的角色由无闻转为配角,现正昂首阔步走向台面,成为主角");
busClick.setCreateTime("2008-09-01 00:00:00");
resultList.add(busClick);
busClick = new BusClick();
busClick.setOpenId("OSFSADFSADF");
busClick.setNickName("HAHA");
busClick.setName("name");
busClick.setGender("男");
busClick.setAge(21);
busClick.setMobile("18885522233");
busClick.setCity("天津");
busClick.setEmail("163@csdf.com");
busClick.setIntroduce("aaa\n" + "bbb");
busClick.setEducation("aaa\n" + "bbb");
busClick.setWork("案,这使得很多人误认为文案和策划就是一回事,甚至常常把策划与文案的工作会混淆在一起(这也和发源于中国的“策划学”发展不够成熟有关)。");
busClick.setResult("广告文案\\n\" +\n" +
" \"很多企业中,都有了的专职的文案人员,只有当需要搞一些大型推广活动、做商业策划案、写可行性分析报告等需求量大的项目时,才需要对外寻求合作。" +
"以往一般企业都会找广告、文化传媒等公司合作。这些公司一般都有专业的文案、设计团队,经验也相对丰富,但因为业务量大,范围广泛," +
" \"随着中国广告业二十余年的迅猛发展,广告公司的经营范围,操作流程,工作方式都在变化,文案的角色由无闻转为配角,现正昂首阔步走向台面,成为主角");
busClick.setCreateTime("2008-09-01 00:00:00");
resultList.add(busClick);
long t1 = System.currentTimeMillis();
LocalDate localDate = LocalDate.now();
String fileName = "嘿嘿「" + localDate + "」";
ExcelUtils.writeExcel(fileName, response, resultList, BusClick.class);
long t2 = System.currentTimeMillis();
System.out.println(String.format("write over! cost:%sms", (t2 - t1)));
}
@RequestMapping(value = "/readExcel", method = RequestMethod.POST)
public void readExcel(@RequestParam(value = "uploadFile", required = false) MultipartFile file) {
long t1 = System.currentTimeMillis();
List<BusClick> list = ExcelUtils.readExcel("", BusClick.class, file);
long t2 = System.currentTimeMillis();
System.out.println(String.format("read over! cost:%sms", (t2 - t1)));
list.forEach(
b -> System.out.println(JSON.toJSONString(b))
);
}
}
导出的样式还待优化,目前针对大部分的业务需求够用,一直更新中!!!