使用范围
该工具类要求 jdk 1.8以上，org.apache.poi 4.0 以上。由于我项目用的是gradle 就不贴导包的路径信息了，此工具类应该可以满足日常开发绝大部分需求，分享给大家，有错误之处，还望大神指教。
导出对象
@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = “导出对象”)
public class PoolExportDTO {
@ApiModelProperty(value = "给前端人员看的字段名称1")
@Excel(name = "字段名称1",order = 1)
private String invoiceType;
@ApiModelProperty(value = "给前端人员看的字段名称2")
@Excel(name = "字段名称2",order = 2)
private String typeName;
@ApiModelProperty(value = "给前端人员看的字段名称3")
@Excel(name = "字段名称3",order = 3)
private String code;
@ApiModelProperty(value = "给前端人员看的字段名称4")
@Excel(name = "字段名称4",order = 4)
private String printingNumber;
@ApiModelProperty(value = "给前端人员看的字段名称5")
@Excel(name = "字段名称5",order = 5)
private LocalDateTime invoiceDate;
@ApiModelProperty(value = "给前端人员看的字段名称6")
@Excel(name = "字段名称6",order = 6)
private String pretaxAmount;
@ApiModelProperty(value = "给前端人员看的字段名称7")
@Excel(name = "字段名称7",order = 7)
private String tax;
@ApiModelProperty(value = "给前端人员看的字段名称8")
@Excel(name = "字段名称8",order = 8)
private String total;
}
控制器
@ApiOperation(value = "导出excel", notes = "导出excel")
@GetMapping("ExcelExport")
public ResponseEntity<?> ExcelExport(InvoicePoolVo vo, HttpServletResponse response) {
String fileName = "aaa" + DateUtil.GetFormatTime("yyyyMMddHHmmss") + ".xlsx";
List<T> queryList = invoicePoolService.fiandByInvoicePool(vo);
queryList .forEach(item->{
int j = RedisUtils.get(SCANS + ICON + NUMBER_OF_SCANS + ICON + "_" + item.getCode() + "_" + item.getPrintingNumber(), int.class);
item.setNumberOfScans(j);
});
return getResponseEntity(response, fileName, invoicePoolList, PoolExportDTO.class);
}
private ResponseEntity<?> getResponseEntity(HttpServletResponse response, String fileName, List<?> list, Class<?> clazz) {
try {
ExcelUtil.listToExcel(list, clazz, “Sheet1”, fileName, response);
return new ResponseEntity(HttpStatus.OK);
} catch (Exception e) {
e.printStackTrace();
}
return new ResponseEntity<>(OperateResult.ok(“下载失败”), HttpStatus.INTERNAL_SERVER_ERROR);
}
ExcelUtil
import com.shsc.saas.invoice.configuration.Excel;
import com.shsc.saas.invoice.entity.enums.ExcelTypeEnum;
import com.shsc.saas.invoice.entity.enums.MatchTypeEnum;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.;
public class ExcelUtil {
/**
* 忽略需要转换的字段
*/
private final static String IGNORE_CONVERT = "MATNR,VKORG,KUNAG,KUNNR,YEAR,MONTH";
/**
* 导出Excel
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param requiredFildMap 必填字段
* @param sheetName sheet名称
* @param cellWidth 列宽
* @param outputStream 输出流
* @param <T> 实体类对象
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, Integer cellWidth,
OutputStream outputStream) throws ExcelException {
Workbook workbook = null;
try {
if (list == null || list.size() <= 0) {
workbook = new XSSFWorkbook();
} else {
if (list.get(0) instanceof HashMap) {
workbook = new SXSSFWorkbook(1000);
} else {
workbook = new XSSFWorkbook();
}
}
Sheet sheet = workbook.createSheet(sheetName);
if (requiredFildMap != null && requiredFildMap.size() > 0) {
List<String> tmp = new ArrayList<>();
requiredFildMap.forEach((item) -> {
tmp.add(item.concat("-Required"));
});
requiredFildMap = tmp;
}
fillSheet(sheet, list, fieldMap, requiredFildMap, 0, list.size() - 1, cellWidth, workbook);
workbook.write(outputStream);
} catch (Exception ex) {
ex.printStackTrace();
if (ex instanceof ExcelException) {
throw (ExcelException) ex;
} else {
throw new ExcelException("导出Excel失败！");
}
} finally {
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static <T> void writeToFile(List<T> list, Class<?> clazz, String sheetName, String filePath, String fileName) throws ExcelException {
List<String> requiredFieldMap = new ArrayList();
LinkedHashMap<String, String> fieldMap = toHashMap(clazz, requiredFieldMap, ExcelTypeEnum.EXPORT);
Workbook workbook = null;
try {
if (list != null && list.size() > 0) {
if (list.get(0) instanceof HashMap) {
workbook = new SXSSFWorkbook(1000);
} else {
workbook = new XSSFWorkbook();
}
} else {
workbook = new XSSFWorkbook();
}
Sheet sheet = workbook.createSheet(sheetName);
fillSheet(sheet, list, fieldMap, null, 0, list.size() - 1, 20, workbook);
File savefile = new File(filePath);
if (!savefile.exists()) {
boolean result = savefile.mkdirs();
}
String fileFullPath = filePath + "/" + fileName;
FileOutputStream fos = new FileOutputStream(fileFullPath);
workbook.write(fos);
fos.close();
} catch (Exception ex) {
ex.printStackTrace();
if (ex instanceof ExcelException) {
throw (ExcelException) ex;
} else {
throw new ExcelException("导出Excel失败！");
}
} finally {
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 导出Excel
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param sheetName sheet名称
* @param outputStream 输出流
* @param <T> 实体类对象
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, OutputStream outputStream) throws ExcelException {
listToExcel(list, fieldMap, null, sheetName, 20, outputStream);
}
/**
* 导出Excel
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param sheetName sheet名称
* @param outputStream 输出流
* @param <T> 实体类对象
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, OutputStream outputStream) throws ExcelException {
listToExcel(list, fieldMap, requiredFildMap, sheetName, 20, outputStream);
}
/**
* 导出Excel（浏览器）
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param sheetName sheet名称
* @param fileName 文件名称
* @param cellWidth 列宽
* @param response 请求
* @param <T> 对象实体
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, String fileName, Integer cellWidth,
HttpServletResponse response) throws ExcelException {
listToExcelCommon(list, fieldMap, requiredFildMap, sheetName, fileName, cellWidth, response);
}
/**
* 导出Excel（浏览器）
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param sheetName sheet名称
* @param fileName 文件名称
* @param cellWidth 列宽
* @param response 请求
* @param <T> 对象实体
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, String fileName, Integer cellWidth,
HttpServletResponse response) throws ExcelException {
listToExcelCommon(list, fieldMap, null, sheetName, fileName, cellWidth, response);
}
/**
* 导出公共方法
*
* @param list
* @param fieldMap
* @param requiredFildMap
* @param sheetName
* @param fileName
* @param cellWidth
* @param response
* @param <T>
* @throws ExcelException
*/
private static <T> void listToExcelCommon(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName,
String fileName, Integer cellWidth, HttpServletResponse response) throws ExcelException {
fileName = fileName == null ? DateUtil.GetFormatTime("yyyyMMddHHmmss") + ".xlsx" : fileName;
String headStr = "";
try {
headStr = "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"";
} catch (Exception ex1) {
headStr = "attachment;filename=\"" + fileName + "\"";
}
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("content-disposition", headStr);
try {
OutputStream outputStream = response.getOutputStream();
listToExcel(list, fieldMap, requiredFildMap, sheetName, cellWidth, outputStream);
} catch (Exception ex) {
ex.printStackTrace();
if (ex instanceof ExcelException) {
throw (ExcelException) ex;
} else {
throw new ExcelException("导出Excel失败！");
}
}
}
/**
* 导出Excel（浏览器）
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param sheetName sheet名称
* @param fileName 文件名称
* @param response 请求
* @param <T> 对象实体
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, String fileName, HttpServletResponse response) throws ExcelException {
listToExcel(list, fieldMap, null, sheetName, fileName, 20, response);
}
/**
* 导出Excel（浏览器）
*
* @param list 数据源
* @param fieldMap 字段（实体对象对应的字段名称为Key，中文名称为值）
* @param sheetName sheet名称
* @param fileName 文件名称
* @param response 请求
* @param <T> 对象实体
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, String fileName, HttpServletResponse response) throws ExcelException {
listToExcel(list, fieldMap, requiredFildMap, sheetName, fileName, 20, response);
}
/**
* 导出Excel
*
* @param list
* @param clazz
* @param sheetName
* @param fileName
* @param response
* @param <T>
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, Class<?> clazz, String sheetName, String fileName, HttpServletResponse response) throws ExcelException {
List<String> requiredFieldMap = new ArrayList<>();
LinkedHashMap<String, String> fieldMap = toHashMap(clazz, requiredFieldMap, ExcelTypeEnum.EXPORT);
listToExcel(list, fieldMap, requiredFieldMap, sheetName, fileName, response);
}
/**
* 导出Excel
*
* @param list
* @param clazz
* @param sheetName
* @param fileName
* @param cellWidth
* @param response
* @param <T>
* @throws ExcelException
*/
public static <T> void listToExcel(List<T> list, Class<?> clazz, String sheetName, String fileName, Integer cellWidth, HttpServletResponse response) throws ExcelException {
List<String> requiredFieldMap = new ArrayList<>();
LinkedHashMap<String, String> fieldMap = toHashMap(clazz, requiredFieldMap, ExcelTypeEnum.EXPORT);
listToExcel(list, fieldMap, requiredFieldMap, sheetName, fileName, cellWidth, response);
}
/**
* 导入Excel数据
*
* @param inputStream 文件输入流
* @param sheetName sheet名称
* @param clazz 实体类对象
* @param fieldMap 字段（中文名称为Key，实体对象对应的字段名为值）
* @param <T> 实体对象
* @return
* @throws ExcelException
*/
public static <T> List<T> excelToList(InputStream inputStream, String sheetName, Class<T> clazz, LinkedHashMap<String, String> fieldMap) throws ExcelException {
List<T> resultList = new ArrayList<>();
try {
ZipSecureFile.setMinInflateRatio(-1.0D);
Workbook workbook = WorkbookFactory.create(inputStream);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Sheet sheet;
if (StringUtil.isEmpty(sheetName)) {
sheet = workbook.getSheetAt(0);
} else {
sheet = workbook.getSheet(sheetName);
}
int realRows = 0;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
int nullCells = 0;
Row row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < row.getLastCellNum(); ++j) {
Cell cell = row.getCell(j);
if (cell == null || "".equals(getCellValue(cell, evaluator))) {
++nullCells;
}
}
if (nullCells == row.getLastCellNum()) {
break;
}
++realRows;
}
}
if (realRows < 1) {
throw new ExcelException("Excel中暂无数据！");
}
Row row = sheet.getRow(0);
String[] excelFieldNames = new String[row.getLastCellNum()];
for (int i = 0; i < row.getLastCellNum(); i++) {
excelFieldNames[i] = row.getCell(i).getStringCellValue().trim();
}
boolean isxist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fieldMap.keySet()) {
if (!excelFieldList.contains(cnName)) {
isxist = false;
break;
}
}
if (!isxist) {
throw new ExcelException("Excel中缺少必要的字段或字段名称错误！");
}
LinkedHashMap<String, Integer> cellMap = new LinkedHashMap<>();
for (int i = 0; i < excelFieldNames.length; i++) {
cellMap.put(excelFieldNames[i], row.getCell(i).getColumnIndex());
}
for (int i = 1; i < realRows; i++) {
T entity = clazz.newInstance();
row = sheet.getRow(i);
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
String cnName = entry.getKey();
String enName = entry.getValue();
int cellIndex = cellMap.get(cnName);
Cell cell = row.getCell(cellIndex);
String content = getCellValue(cell, evaluator);
setFieldValueByName(enName, content, entity);
}
resultList.add(entity);
}
} catch (Exception ex) {
ex.printStackTrace();
if (ex instanceof ExcelException) {
throw (ExcelException) ex;
} else {
throw new ExcelException("导入Excel失败！");
}
}
return resultList;
}
/**
* 导入Excel数据
*
* @param inputStream 文件输入流
* @param clazz 实体类对象
* @param fieldMap 字段（中文名称为Key，实体对象对应的字段名为值）
* @param <T> 实体对象
* @return
* @throws ExcelException
*/
public static <T> List<T> excelToList(InputStream inputStream, Class<T> clazz, LinkedHashMap<String, String> fieldMap) throws ExcelException {
return excelToList(inputStream, null, clazz, fieldMap);
}
/**
* 导入Excel数据
*
* @param inputStream
* @param clazz
* @return
* @throws ExcelException
*/
public static <T> List<T> excelToList(InputStream inputStream, Class<T> clazz) throws ExcelException {
return excelToList(inputStream, clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
}
/**
* 导入Excel数据
*
* @param inputStream
* @param sheetName
* @param clazz
* @return
* @throws ExcelException
*/
public static <T> List<T> excelToList(InputStream inputStream, String sheetName, Class<T> clazz) throws ExcelException {
return excelToList(inputStream, sheetName, clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
}
/**
* 导入Excel数据
*
* @param file
* @param clazz
* @return
* @throws ExcelException, IOException
*/
public static <T> List<T> excelToList(MultipartFile file, Class<T> clazz) throws ExcelException, IOException {
String fileName = file.getOriginalFilename();
if (MatcheUtil.matchExcel(fileName, MatchTypeEnum.EXCEL)) {
throw new ExcelException("上传文件格式不正确！");
}
return excelToList(file.getInputStream(), clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
}
/**
* 导入Excel数据
*
* @param file
* @param sheetName
* @param clazz
* @return
* @throws ExcelException, IOException
*/
public static <T> List<T> excelToList(MultipartFile file, String sheetName, Class<T> clazz) throws ExcelException, IOException {
String fileName = file.getOriginalFilename();
if (MatcheUtil.matchExcel(fileName, MatchTypeEnum.EXCEL)) {
throw new ExcelException("上传文件格式不正确！");
}
return excelToList(file.getInputStream(), sheetName, clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
}
/**
* 通过反射获取Excel字段
*
* @param clazz
* @param excelTypeEnum
* @return
*/
private static LinkedHashMap<String, String> toHashMap(Class<?> clazz, List<String> requiredFildMap, ExcelTypeEnum excelTypeEnum) {
LinkedHashMap<String, String> linkedHashMap = new LinkedHashMap<>();
Field[] fields = clazz.getDeclaredFields();
List<Excel> list = new ArrayList<>();
Map<String, String> map = new HashMap<>();
for (Field field : fields) {
Excel excel = field.getDeclaredAnnotation(Excel.class);
if (excel != null) {
map.put(excel.name(), field.getName());
list.add(excel);
}
}
list.stream().sorted(Comparator.comparing(Excel::order)).forEach(t -> {
String fieldName = map.get(t.name());
if (excelTypeEnum == ExcelTypeEnum.IMPORT) {
linkedHashMap.put(t.name(), fieldName);
} else {
if (requiredFildMap != null && t.required()) {
requiredFildMap.add(t.name());
}
linkedHashMap.put(fieldName, t.name());
}
});
return linkedHashMap;
}
/**
* 根据字段名称获取字段值
*
* @param fieldName 字段名称
* @param object 类对象
* @return
* @throws Exception
*/
private static Object getFieldValueByName(String fieldName, Object object) throws Exception {
Object value = null;
Field field = getFieldByName(fieldName, object.getClass());
if (field != null) {
field.setAccessible(true);
value = field.get(object);
} else {
throw new ExcelException(object.getClass().getSimpleName() + "类中不存在字段名" + fieldName);
}
return value;
}
/**
* 根据字段名称获取类中的字段数据
*
* @param fieldName 字段名称
* @param clazz 字段类
* @return
*/
private static Field getFieldByName(String fieldName, Class<?> clazz) {
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (field.getName().equalsIgnoreCase(fieldName)) {
return field;
}
}
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
}
return null;
}
/**
* 根据带路径或不带路径的属性名获取属性值
*
* @param filedNameSequence 带路径或不带路径的属性值
* @param object 类对象
* @return
* @throws Exception
*/
private static Object getFieldValueByNameSequence(String filedNameSequence, Object object) throws Exception {
Object value = null;
String[] attributs = filedNameSequence.split("\\.");
if (attributs.length == 1) {
value = getFieldValueByName(filedNameSequence, object);
} else {
Object fieldObj = getFieldValueByName(attributs[0], object);
String subFieldNameSequence = filedNameSequence.substring(filedNameSequence.indexOf(".") + 1);
value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}
return value;
}
/**
* 根据字段名称设置字段的值
*
* @param fieldName 字段名称
* @param fieldValue 字段值
* @param object 类对象
* @throws Exception
*/
private static void setFieldValueByName(String fieldName, Object fieldValue, Object object) throws Exception {
Field field = getFieldByName(fieldName, object.getClass());
if (field != null) {
field.setAccessible(true);
Class<?> fieldType = field.getType();
if (fieldType == String.class) {
try {
//判断字符串数值是否带有.0小数
if (String.valueOf(fieldValue).contains(".0")) {
field.set(object, String.valueOf(Double.valueOf(String.valueOf(fieldValue)).intValue()));
} else {
field.set(object, String.valueOf(fieldValue));
}
} catch (Exception ex) {
field.set(object, String.valueOf(fieldValue));
}
} else if (fieldType == Integer.class || fieldType == Integer.TYPE) {
if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, Integer.parseInt(fieldValue.toString().trim()));
} else {
field.set(object, 0);
}
} else if (fieldType == Long.class || fieldType == Long.TYPE) {
if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, Long.valueOf(fieldValue.toString().trim()));
} else {
field.set(object, Long.valueOf("0"));
}
} else if (fieldType == Float.class || fieldType == Float.TYPE) {
if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, Float.valueOf(fieldValue.toString().trim()));
} else {
field.set(object, Float.valueOf(0));
}
} else if (fieldType == Short.class || fieldType == Short.TYPE) {
if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, Short.valueOf(fieldValue.toString().trim()));
} else {
field.set(object, Short.valueOf("0"));
}
} else if (fieldType == Double.class || fieldType == Double.TYPE) {
if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, Double.valueOf(fieldValue.toString().trim()));
} else {
field.set(object, Double.valueOf(0));
}
} else if (fieldType == Character.class || fieldType == Character.TYPE) {
if (fieldValue != null && fieldValue.toString().trim().length() > 0) {
field.set(object, Character.valueOf(fieldValue.toString().trim().charAt(0)));
}
} else if (fieldType == Date.class) {
if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString().trim()));
} else {
field.set(object, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(DateUtil.GetFormatTime()));
}
} else if (fieldType == BigDecimal.class) {
if (StringUtil.isEmpty(fieldValue.toString().trim())) {
field.set(object, new BigDecimal(0));
} else {
field.set(object, new BigDecimal(fieldValue.toString().trim()));
}
} else {
field.set(object, fieldValue);
}
} else {
throw new ExcelException(object.getClass().getSimpleName() + "类中不存在字段名" + fieldName);
}
}
/**
* 设置自动列宽
*
* @param sheet
* @param width
*/
private static void setColumnAutoSize(Sheet sheet, Integer width) {
Row row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
//sheet.setColumnWidth(i, width * 256);
sheet.autoSizeColumn(i);
}
}
// 自适应宽度(中文支持)
private static void setSizeColumn(Sheet sheet, int size) {
for (int columnNum = 0; columnNum < size; columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
Row currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(columnNum) != null) {
Cell currentCell = currentRow.getCell(columnNum);
if (currentCell.getCellType() == CellType.STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
int colWidth = columnWidth * 256;
if (colWidth < 255 * 256) {
sheet.setColumnWidth(columnNum, colWidth < 3000 ? 3000 : colWidth);
} else {
sheet.setColumnWidth(columnNum, 6000);
}
}
}
/**
* 设置自动列宽
*
* @param sheet
* @param headerLength
* @param width
*/
private static void setColumnAutoSize(Sheet sheet, int headerLength, int width) {
for (int i = 0; i < headerLength; i++) {
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * width / 10);
}
}
/**
* 设置字段列类型
*
* @param cell
* @param fieldName
* @param object
*/
private static void setColumnType(Cell cell, CellStyle cellStyle, String fieldName, Object object) throws Exception {
if (object instanceof HashMap) {
HashMap tmpMap = (HashMap) object;
if (StringUtil.isEmpty(tmpMap.get(fieldName))) {
cell.setCellValue("");
} else {
String val = tmpMap.get(fieldName).toString();
if (CustomUtil.isDecimal(val) && !IGNORE_CONVERT.contains(fieldName.toUpperCase())) {
cell.setCellType(CellType.NUMERIC);
cell.setCellStyle(cellStyle);
cell.setCellValue(Double.parseDouble(val));
} else {
cell.setCellType(CellType.STRING);
cell.setCellValue(val);
}
}
} else {
Object objectValue = getFieldValueByNameSequence(fieldName, object);
String fieldValue = objectValue == null ? "" : objectValue.toString();
Field field = getFieldByName(fieldName, object.getClass());
if (field != null) {
Class<?> fieldType = field.getType();
if ((fieldType == Integer.class || fieldType == Integer.TYPE)
|| (fieldType == Long.class || fieldType == Long.TYPE)
|| (fieldType == Float.class || fieldType == Float.TYPE)
|| (fieldType == Short.class || fieldType == Short.TYPE)
|| (fieldType == Double.class || fieldType == Double.TYPE)
|| (fieldType == BigDecimal.class)) {
cell.setCellType(CellType.NUMERIC);
if ((fieldType == Double.class || fieldType == Double.TYPE)
|| (fieldType == BigDecimal.class)) {
if (!StringUtil.isEmpty(fieldValue)) {
cell.setCellStyle(cellStyle);
cell.setCellValue(Double.parseDouble(fieldValue));
} else {
cell.setCellValue(fieldValue);
}
} else {
cell.setCellValue(fieldValue);
}
} else {
if (fieldValue.startsWith("@") && fieldValue.endsWith("@")) {
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
} else {
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
}
/* cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());*/
cell.setCellStyle(cellStyle);
cell.setCellType(CellType.STRING);
cell.setCellValue(fieldValue);
}
}
}
}
/**
* 填充数据
*
* @param sheet Excel中的Sheet页
* @param list 数据源
* @param fieldMap 字段
* @param firstIndex 开始索引数
* @param lastIndex 最后索引数
* @param cellWidth 列宽
* @param <T> 实体对象
* @throws Exception
*/
private static <T> void fillSheet(Sheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, Integer firstIndex, Integer lastIndex,
Integer cellWidth, Workbook workbook) throws Exception {
String[] enFields = new String[fieldMap.size()];
String[] cnFields = new String[fieldMap.size()];
int count = 0;
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
Row row = sheet.createRow(0);
row.setHeightInPoints(30);
Cell cell;
CellStyle style = null;
boolean flag = false;
Font font = null;
for (int i = 0; i < cnFields.length; i++) {
cell = row.createCell(i);
cell.setCellValue(cnFields[i]);
style = workbook.createCellStyle();
font = workbook.createFont();
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
if (requiredFildMap != null && requiredFildMap.size() > 0) {
if (requiredFildMap.contains(cnFields[i].concat("-Required"))) {
font.setColor(IndexedColors.WHITE.getIndex());
style.setFillForegroundColor(IndexedColors.RED.getIndex());
style.setFont(font);
flag = true;
} else {
flag = false;
}
} else {
flag = false;
}
if (!flag) {
font.setColor(IndexedColors.BLACK.getIndex());
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFont(font);
flag = true;
}
cell.setCellStyle(style);
}
int rowIndex = 1;
if (list != null && list.size() > 0) {
// 修复填充数据时，循环创建样式序列引发的问题
CellStyle cellStyle = workbook.createCellStyle();
DataFormat dataFormat = workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
for (int index = firstIndex; index <= lastIndex; index++) {
T item = list.get(index);
row = sheet.createRow(rowIndex);
for (int j = 0; j < enFields.length; j++) {
cell = row.createCell(j);
setColumnType(cell, cellStyle, enFields[j], item);
}
rowIndex++;
}
}
/*setColumnAutoSize(sheet, enFields.length, cellWidth);*/
if (list == null || list.size() <= 0 || !(list.get(0) instanceof HashMap)) {
setColumnAutoSize(sheet, cellWidth);
setSizeColumn(sheet, enFields.length);
}
}
/**
* 填充数据
*
* @param sheet Excel中的Sheet页
* @param list 数据源
* @param fieldMap 字段
* @param firstIndex 开始索引数
* @param lastIndex 最后索引数
* @param <T> 实体对象
* @throws Exception
*/
private static <T> void fillSheet(XSSFSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, Integer firstIndex, Integer lastIndex) throws Exception {
fillSheet(sheet, list, fieldMap, null, firstIndex, lastIndex, 20, null);
}
/**
* 获取列值
*
* @param cell
* @return
*/
private static String getCellValue(Cell cell, FormulaEvaluator evaluator) {
String content = "";
if (cell != null) {
CellType cellType = cell.getCellType();
switch (cellType) {
case ERROR:
content = String.valueOf(cell.getErrorCellValue());
break;
case BOOLEAN:
content = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
content = getFormulaValue(cell, evaluator);
break;
case NUMERIC:
content = String.valueOf(cell.getNumericCellValue());
break;
default:
content = cell.getStringCellValue();
break;
}
}
return content;
}
/**
* 获取公式中的值
*
* @param cell
* @param evaluator
* @return
*/
private static String getFormulaValue(Cell cell, FormulaEvaluator evaluator) {
CellValue cellValue = evaluator.evaluate(cell);
String content = "";
switch (cellValue.getCellType()) {
case NUMERIC:
content = String.valueOf(cellValue.getNumberValue());
break;
case BOOLEAN:
content = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
content = String.valueOf(cellValue.getErrorValue());
break;
default:
content = cellValue.getStringValue();
break;
}
return content;
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("content-disposition",
"attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
}
excel
import java.lang.annotation.*;
@Documented
@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
/**
* 名字
*
* @return
*/
String name() default "";
/**
* 值
*
* @return
*/
String value() default "";
/**
* 是否必填项
*
* @return
*/
boolean required() default false;
/**
* 显示顺序
*
* @return
*/
int order() default 0;
}
规则类型枚举
public enum MatchTypeEnum {
/**
* EXCEL
*/
EXCEL("Excel", 1),
/**
* WORD
*/
WORD("Word", 2),
/**
* TXT
*/
TXT("txt", 3);
private String name;
private int index;
MatchTypeEnum() {
}
MatchTypeEnum(String name, int index) {
this.name = name;
this.index = index;
}
public static String getName(int index) {
for (MatchTypeEnum info : MatchTypeEnum.values()) {
if (info.getIndex() == index) {
return info.getName();
}
}
return null;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
}
ExcelException
public class ExcelException extends Exception {
public ExcelException() {
}
public ExcelException(String message) {
super(message);
}
public ExcelException(Throwable cause) {
super(cause);
}
public ExcelException(String mesage, Throwable cause) {
super(mesage, cause);
}
}
结束语
至此基于poi的导入导出就已经好了，在ExcelUtil 中因遇到了一些场景问题，所以导致写的比较长，如觉得比较麻烦的话请移步 我的另一篇 文章 Excel 工具类Ⅱ https://blog.csdn.net/qq_34901859/article/details/115702423 。
678
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
