目前导入导出的东西比较多所以想着写了 公共的导入 的 工具类 导入是后台去弄的 导出 是才用vue 前端 去实现的
因为代码 我也是在网上去 找的 所以不说明 就直接 上代码 代码 是可以直接复制粘贴 可以用的
后端 导入：
pom.xml
相关架包
<!-- polexcel 导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.4.0</version>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.24</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.24</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</version>
</dependency>
具体的类
比较重要 是一些注解
导出的实体类 ：
package com.lucky.xyt.utils.excelUtils.model;
import java.io.Serializable;
import java.math.BigDecimal;
import com.lucky.xyt.utils.excelUtils.annotation.util.ExcelImportField;
import com.lucky.xyt.utils.excelUtils.annotation.validate.NotNull;
import lombok.Data;
@Data
public class ExamQuestionExcel implements Serializable{
private static final long serialVersionUID = 4533380631120297389L;
@ExcelImportField(order=1)
private String classifyName;
@ExcelImportField(order=2)
@NotNull
private String name;
@ExcelImportField(order=3)
@NotNull
private String typeName;
@ExcelImportField(order=4)
private String options;
@ExcelImportField(order=5)
@NotNull
private String correctAnswers;
@ExcelImportField(order=5)
@NotNull
private String score;
}
目录结构
ImportTableParams.class
package com.lucky.xyt.utils.excelUtils.annotation.entity;
import java.util.List;
public class ImportTableParams {
/**
* 当前sheet
*/
private int currSheetIndex;
/**
* 从第几行开始读取
*/
private int startLine;
/**
* 最后几行不读
*/
private int endLine;
/**
* 标题行数 告诉标题行数，意味着就可以判断模板了。
*/
private Integer titleLine;
public int getCurrSheetIndex() {
return currSheetIndex;
}
public void setCurrSheetIndex(int currSheetIndex) {
this.currSheetIndex = currSheetIndex;
}
public int getStartLine() {
return startLine;
}
public void setStartLine(int startLine) {
this.startLine = startLine;
}
public int getEndLine() {
return endLine;
}
public void setEndLine(int endLine) {
this.endLine = endLine;
}
public Integer getTitleLine() {
return titleLine;
}
public void setTitleLine(Integer titleLine) {
this.titleLine = titleLine;
}
/**
* 需要校验模板
* @param currSheetIndex
* @param startLine
* @param endLine
* @param titleLine
*/
public ImportTableParams(int currSheetIndex, int startLine, int endLine, Integer titleLine) {
this.currSheetIndex = currSheetIndex;
this.startLine = startLine;
this.endLine = endLine;
this.titleLine = titleLine;
}
/**
* 无需校验模板
* @param currSheetIndex
* @param startLine
* @param endLine
*/
public ImportTableParams(int currSheetIndex, int startLine, int endLine) {
this.currSheetIndex = currSheetIndex;
this.startLine = startLine;
this.endLine = endLine;
}
}
TableParams.class
package com.lucky.xyt.utils.excelUtils.annotation.entity;
public class TableParams {
/*标题*/
private String title;
/*标题高度*/
private Integer titleHeight;
/*sheet名字*/
private String sheetName;
public TableParams(String title, Integer titleHeight, String sheetName) {
this.title = title;
this.titleHeight = titleHeight;
this.sheetName = sheetName;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Integer getTitleHeight() {
return titleHeight;
}
public void setTitleHeight(Integer titleHeight) {
this.titleHeight = titleHeight;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
}
ExcelException.class<异常类>
package com.lucky.xyt.utils.excelUtils.annotation.exception;
public class ExcelException extends Exception {
public ExcelException() {
super();
}
public ExcelException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
super(message, cause, enableSuppression, writableStackTrace);
}
public ExcelException(String message, Throwable cause) {
super(message, cause);
}
public ExcelException(String message) {
super(message);
}
public ExcelException(Throwable cause) {
super(cause);
}
}
ExcelField
package com.lucky.xyt.utils.excelUtils.annotation.util;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import org.apache.poi.hssf.util.HSSFColor;
/**
*
* 这里是导出对象的annoation
* 所有需要导出的字段需要使用该注解
*
* @author huangjinhui
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
/**
* 注释在属性上的title
* 用来设置标题
* @return
*/
String title();
/**
* 设置排序 可以手动调整标题的顺序
* @return
*/
int order() default 9999;
/**
* 列宽
* @Title: columnWidth
* @Description: TODO(这里用一句话描述这个方法的作用)
* @param @return 设定文件
* @author 【huangjinhui】
* @return int 返回类型
* @throws
*/
int columnWidth() default 0;
/**
* 对齐方式
* @return
*/
String align() default "left";
/**
* 字体颜色
* @return
*/
short color() default HSSFColor.BLACK.index;
}
ExcelImportField
package com.lucky.xyt.utils.excelUtils.annotation.util;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* 这里是导入对象的annoation
* 所有需要导入的字段需要使用该注解
*
* @author huangjinhui
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelImportField {
/**
* 设置排序 可以手动调整标题的顺序
* @return
*/
int order() default 9999;
}
Alias
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 别名：
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Alias {
/**
* 嵌套注解
* @return
*/
AliasItem[] values();
}
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface AliasItem {
/**
* 要转换的值
* @return
*/
String source();
/**
* 转换成的值
* @return
*/
String target();
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 自定义注解。regex为自定义正则表达式
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface CustomValidate {
/**
* 正则表达式
* @return
*/
String regex();
String message();
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 邮箱
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Email {
/**
* 邮箱格式(默认不校验)
* @return
*/
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 身份证
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface IdCard {
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 中文
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface IsChinese {
/**
* 提示信息
* @return
*/
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 数字
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface IsNum {
/**
* 提示信息
* @return
*/
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* url
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface IsUrl {
/**
* 提示信息
* @return
*/
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 最大值
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Max {
/**
* 数值
* @return
*/
int value();
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 最小值
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Min {
/**
* 数值
* @return
*/
int value();
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 非空
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface NotNull {
/**
* 提示信息
* @return
*/
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 手机号
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Phone {
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 范围
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Range {
/**
* 最小值
* @return
*/
int min();
/**
* 最大值
* @return
*/
int max();
String message() default "";
}
package com.lucky.xyt.utils.excelUtils.annotation.validate;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 电话
* @author huangjinhui
*
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Telephone {
String message() default "";
}
ExcelException.class
package com.lucky.xyt.utils.excelUtils.exception;
public class ExcelException extends Exception {
public ExcelException() {
super();
}
public ExcelException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
super(message, cause, enableSuppression, writableStackTrace);
}
public ExcelException(String message, Throwable cause) {
super(message, cause);
}
public ExcelException(String message) {
super(message);
}
public ExcelException(Throwable cause) {
super(cause);
}
}
ExamQuestionExcel.class
package com.lucky.xyt.utils.excelUtils.model;
import java.io.Serializable;
import java.math.BigDecimal;
import com.lucky.xyt.utils.excelUtils.annotation.util.ExcelImportField;
import com.lucky.xyt.utils.excelUtils.annotation.validate.NotNull;
import lombok.Data;
@Data
public class ExamQuestionExcel implements Serializable{
private static final long serialVersionUID = 4533380631120297389L;
@ExcelImportField(order=1)
private String classifyName;
@ExcelImportField(order=2)
@NotNull
private String name;
@ExcelImportField(order=3)
@NotNull
private String typeName;
@ExcelImportField(order=4)
private String options;
@ExcelImportField(order=5)
@NotNull
private String correctAnswers;
@ExcelImportField(order=5)
@NotNull
private String score;
}
ExcelHeader.class
package com.lucky.xyt.utils.excelUtils;
/**
* 用于保存使用注解类的相关信息
* title：用于记录标题
* order:用于记录标题在列的顺序
* fieldName：用于记录在哪个属性使用的注解方便反射赋值
*
* @author huangjinhui
*/
public class ExcelHeader implements Comparable<ExcelHeader>{
/*标题*/
private String title ;
/*排序*/
private int order;
/*属性名*/
private String fieldName;
/*列宽*/
private int columnWidth;
/*列方位*/
private String align;
/*字体颜色*/
private short color;
public int compareTo(ExcelHeader o) {
return order>o.order? 1:(order <o.order ? -1 :0 );
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public ExcelHeader(String title, int order, String fieldName, int columnWidth, String align, short color) {
this.title = title;
this.order = order;
this.fieldName = fieldName;
this.columnWidth = columnWidth;
this.align = align;
this.color = color;
}
public ExcelHeader(int order, String fieldName) {
this.order = order;
this.fieldName = fieldName;
}
public int getColumnWidth() {
return columnWidth;
}
public void setColumnWidth(int columnWidth) {
this.columnWidth = columnWidth;
}
public String getAlign() {
return align;
}
public void setAlign(String align) {
this.align = align;
}
public short getColor() {
return color;
}
public void setColor(short color) {
this.color = color;
}
}
FlagInfoExcelUtils.class
package com.lucky.xyt.utils.excelUtils;
import com.alibaba.fastjson.JSON;
import com.lucky.xyt.utils.excelUtils.annotation.entity.ImportTableParams;
import com.lucky.xyt.utils.excelUtils.annotation.entity.TableParams;
import com.lucky.xyt.utils.excelUtils.annotation.exception.ExcelException;
import com.lucky.xyt.utils.excelUtils.annotation.util.ExcelField;
import com.lucky.xyt.utils.excelUtils.annotation.util.ExcelImportField;
import com.lucky.xyt.utils.excelUtils.annotation.validate.*;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;
/**
* EXCEL操作工具类
* @author huangjinhui
*
*/
@SuppressWarnings("resource")
public class FlagInfoExcelUtils{
private static final org.slf4j.Logger LOGGER = LoggerFactory.getLogger(FlagInfoExcelUtils.class);
/*标题*/
private String title;
/*标题行高*/
private Integer titleRow;
/*当前行的下标*/
private int currRowIndex;
/*开始读取的行数*/
private int startLine;
/*结束行数*/
private int endLine;
/*当前列的下标*/
private int curCollIndex;
private Sheet sheet;
/*当前列*/
private Cell curCell;
/*当前工作簿下标*/
private int currSheetIndex;
/*当前行*/
private Row curRow;
public Workbook wb;
/*标题所在的行*/
private Integer titleLine;
@SuppressWarnings("rawtypes")
private static UploadDataResultDto uploadDataResultDto;
/*获取实例*/
@SuppressWarnings("rawtypes")
public static FlagInfoExcelUtils getInstance(){
uploadDataResultDto = new UploadDataResultDto();
return new FlagInfoExcelUtils();
}
//==========================<<<<<EXCEL 导出 START>>>>>=======================
/**
* 不带标题的导出
* @param clz 导出对象的class
* @param list 导出集合
* @param isXssf 是否是xlsx
* @param sheetName 工作簿名称
* @return
* @throws ExcelException
*/
@Deprecated
public Workbook exportByObj(Class clz, List list, boolean isXssf,String sheetName) throws ExcelException {
return this.exportByObj(clz, list, isXssf,null,null,sheetName);
}
/**
* 带标题的导出
* @param clz 导出对象的class
* @param list 导出集合
* @param isXssf 是否是xlsx
* @param tableparams 工作簿名称
* @return
* @throws ExcelException
*/
public Workbook exportByObj(Class clz, List list, boolean isXssf, TableParams tableparams) throws ExcelException {
return this.exportByObj(clz,list,isXssf,tableparams.getTitle(),tableparams.getTitleHeight(),tableparams.getSheetName());
}
@Deprecated
@SuppressWarnings("rawtypes")
public Workbook exportByObj( Class clz, List list, boolean isXssf,String title,Integer titleRow,String sheetName) throws ExcelException {
//判断是创建 xlsx 或者 xls
if(isXssf){
/*xlsx*/
wb = new XSSFWorkbook();
}else{
/*xls*/
wb = new HSSFWorkbook();
}
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
/*创建工作簿*/
Sheet sheet = wb.createSheet(sheetName);
sheet.autoSizeColumn(1);
//sheet.setColumnWidth(columnIndex, width);
/*处理标题 设置标题高度*/
if(!StringUtils.isEmpty(title)&& titleRow != null){
this.titleRow = titleRow;
this.title = title;
}
/*通过注解，对导出对象的标题，属性，排序进行设置*/
List<ExcelHeader> headers = getExcelHeaders(clz);
/*设置标题*/
setTitle(title,titleRow,sheet,headers,cellStyle,font);
/*设置导航标题*/
setHeader(sheet, headers);
List<CellStyle> cellStyles = null;
/*创建每一列的样式*/
if(!CollectionUtils.isEmpty(headers)){
cellStyles = getStyles(font, headers);
}
Row row;
if(list != null && list.size() >0){
for(int i = 0 ;i < list.size();i++){
row =sheet.createRow(i+currRowIndex);
/*开始给每一列进行赋值*/
for(int j = 0; j < headers.size(); j++){
String methodStr = getMethod(headers.get(j));
try {
Method method = clz.getMethod(methodStr,null);
Object value = method.invoke(list.get(i), null);
if (value != null){
Cell createCell = row.createCell(j);
//判断是否要加样式
if (cellStyles != null && !CollectionUtils.isEmpty(cellStyles) && cellStyles.get(j) != null){
createCell.setCellStyle(cellStyles.get(j));
}
createCell.setCellValue(value.toString());
}else{
}
} catch (Exception e) {
LOGGER.error("export Excel by Obj ====>>>> Exception：{}",e);
throw new ExcelException(e);
}
}
}
}
return wb;
}
private List<CellStyle> getStyles(Object o, List<ExcelHeader> headers) {
List<CellStyle> cellStyles = new ArrayList<CellStyle>();
for (ExcelHeader header : headers){
CellStyle style = wb.createCellStyle();
//设置样式
//字体颜色
Font font1 = wb.createFont();
font1.setFontName("仿宋_GB2312");
font1.setColor(header.getColor());
style.setFont(font1);//选择需要用到的字体格式
//align
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
if("left".equals(header.getAlign())){
style.setAlignment(CellStyle.ALIGN_LEFT); //
}else if("center".equals(header.getAlign())){
style.setAlignment(CellStyle.ALIGN_CENTER); // 居中
}else if("right".equals(header.getAlign())){
style.setAlignment(CellStyle.ALIGN_RIGHT); //
}
cellStyles.add(style);
}
return cellStyles;
}
/**
* 根据属性名获取get方法名称
* @param header
* @return
*/
private String getMethod(ExcelHeader header) {
String fieldName =header.getFieldName();
fieldName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
return fieldName;
}
private void setHeader(Sheet sheet, List<ExcelHeader> headers) {
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中
Row row;
row = sheet.createRow(currRowIndex);
for (int i = 0; i < headers.size(); i++) {
// //设置列宽
ExcelHeader excelHeader = headers.get(i);
if(excelHeader.getColumnWidth() != 0){
sheet.setColumnWidth(i, excelHeader.getColumnWidth()*256);
}
Cell createCell = row.createCell(i);
createCell.setCellStyle(cellStyle);
createCell.setCellValue(excelHeader.getTitle());
}
++currRowIndex;
}
/**
* 设置标题
* @param title
* @param titleRow
*/
private void setTitle(String title, Integer titleRow,Sheet sheet,List<ExcelHeader> headers,CellStyle cellStyle,Font font) {
if(!StringUtils.isEmpty(title)&& titleRow != null){
Row row =sheet.createRow(0);
//设置行高
row.setHeightInPoints(Float.valueOf(titleRow.toString()));
//行合并
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, headers.size()-1);
sheet.addMergedRegion(cra);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置字体
//font.setBold(true);
font.setFontHeightInPoints((short)(titleRow*0.5));
cellStyle.setFont(font);
//给标题赋值
Cell cell = row.createCell(0);
//设置字体居中
cell.setCellStyle(cellStyle);
cell.setCellValue(title);
++currRowIndex;
}
}
/**
* 根据传入的 导出对象，查询导出对象中注解的内容
* @param clz
* @return
* @throws Exception
*/
private List<ExcelHeader> getExcelHeaders(Class clz){
LOGGER.info("export Excel by Obj ====>>>> 导出的模板对象是：{}",clz);
List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
/*获得所有属性*/
Field[] declaredFields = clz.getDeclaredFields();
/*遍历所有属性*/
for(Field field : declaredFields){
//判断当前属性上面是否有ExcelField注解存在
if(field.isAnnotationPresent(ExcelField.class)){
ExcelField annotation = field.getAnnotation(ExcelField.class);
// String title, int order, String fieldName, int columnWidth
headers.add(new ExcelHeader(annotation.title(),annotation.order(),field.getName(),annotation.columnWidth(),annotation.align(),annotation.color()));
}
}
Collections.sort(headers);
LOGGER.info("export Excel by Obj ====>>>> 标题对象排序以后结果是：{}",JSON.toJSONString(headers));
return headers;
}
//#######################################################################################################
//==========================<<<<<EXCEL 到入 START>>>>>=======================
/**
*
* @return
* @throws Exception
*/
public <T>UploadDataResultDto<T> importDataByFile(File file,Class clz,int currSheetIndex,int startLine, int endLine) throws ExcelException{
LOGGER.info("impory Excel by importDataByClassPath ====>>>> start");
try {
try {
wb = WorkbookFactory.create(new FileInputStream(file));
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
e.printStackTrace();
}
this.startLine = startLine;
this.endLine = endLine;
this.currSheetIndex = currSheetIndex;
/*执行导入数据*/
try {
return importDatas(clz);
} catch (Exception e) {
throw new ExcelException(e);
}
} catch (IOException e) {
LOGGER.error("impory Excel ERROR BY========>>>>>:{}",e);
}
return null;
}
public <T>UploadDataResultDto<T> importDataByPath(String path,Class clz,int currSheetIndex,int startLine, int endLine) throws ExcelException{
return importDataByFile(new File(path),clz,currSheetIndex,startLine,endLine);
}
public <T>UploadDataResultDto<T> importDataByFileInputStream(InputStream inputStream, Class clz, ImportTableParams params) throws ExcelException {
this.titleLine = params.getTitleLine();
return this.importDataByFileInputStream(inputStream,clz,params.getCurrSheetIndex(),params.getStartLine(),params.getEndLine());
}
/**
*
* @Title: importDataByFileInputStream
* @Description: TODO(这里用一句话描述这个方法的作用)
* @param @param inputStream
* @param @param clz
* @param @param currSheetIndex
* @param @param startLine
* @param @param endLine
* @param @return
* @param @throws ExcelException 设定文件
* @author 【huangjinhui】
* @return UploadDataResultDto<T> 返回类型
* @throws
*/
@Deprecated
public <T>UploadDataResultDto<T> importDataByFileInputStream(InputStream inputStream,Class clz,int currSheetIndex,int startLine, int endLine) throws ExcelException{
LOGGER.info("impory Excel by importDataByClassPath ====>>>> start");
try {
try {
wb = WorkbookFactory.create(inputStream);
} catch (EncryptedDocumentException e) {
e.printStackTrace();
} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
e.printStackTrace();
}
this.startLine = startLine;
this.endLine = endLine;
this.currSheetIndex = currSheetIndex;
/*执行导入数据*/
try {
return importDatas(clz);
} catch (Exception e) {
throw new ExcelException(e);
}
} catch (IOException e) {
LOGGER.error("impory Excel ERROR BY========>>>>>:{}",e);
}
return null;
}
/**
* 读数据
* @param clz
* @return
* @throws Exception
*/
@SuppressWarnings("unchecked")
private <T>UploadDataResultDto<T> importDatas(Class clz) throws Exception {
StringBuffer errorMsg = new StringBuffer() ;
//最终返回结果
/*获取headers*/
List<ExcelHeader> headers = getImportExcelHeaders(clz);
if(headers == null || headers.size() == 0 ){
throw new ExcelException("传入的导入对象没有使用<<ExcelImportField>>注解");
}
/*读取工作簿*/
sheet = wb.getSheetAt(currSheetIndex);
//遍历列
T newInstance = null;
//是否需要判断模板是否正确，判断思路 1.列数是否和模板一致，2.标题内容是否一致
if (!checkIsTemplate(errorMsg, headers)){
errorMsg.append("该模板不是标准模板");
uploadDataResultDto.getResultMsg().add(errorMsg.toString());
uploadDataResultDto.setResultCode("500");
return uploadDataResultDto;
}
/*读取行*/
for(int i = currRowIndex;i <= sheet.getLastRowNum()-endLine;i++){
boolean resultflag = true;
curRow = sheet.getRow(i);
if(null == curRow) {
errorMsg.append("第"+ (currRowIndex+1) + "行不能为空");
uploadDataResultDto.getResultMsg().add(errorMsg.toString());
errorMsg.setLength(0);
continue;
}
/*读取列*/
int lastCellNum = (int)curRow.getLastCellNum();
if(lastCellNum != headers.size()){
errorMsg.append("第"+ (currRowIndex+1) + "行存在空值");
uploadDataResultDto.getResultMsg().add(errorMsg.toString());
errorMsg.setLength(0);
continue;
// throw new ExcelException("传入的导入对象 列数和 导入对象"+clz+"，不一致");
}
String fieldName = null;
Field field = null;
Object formatValue;
//创建对象实例
newInstance = (T) clz.newInstance();
for(int c = 0;c < lastCellNum;c ++ ){
//获得当前列
curCell = curRow.getCell(c);
//获得该列对应的属性名称
fieldName = headers.get(c).getFieldName();
//获得当前字段的 属性
field = clz.getDeclaredField(fieldName);
//获得属性的类型
Class<?> type = field.getType();
//获取这个数据
String setfieldName = "set"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
//获得方法
Method method = clz.getMethod(setfieldName, type);
//读取字段数据
formatValue = getFormatValue(curCell);
//电话校验
if(field.isAnnotationPresent(Telephone.class)){
Telephone annotationTelephone = field.getAnnotation(Telephone.class);
boolean isTelephone = HandleTelephone(formatValue,resultflag);
if(!isTelephone){
if(StringUtils.isEmpty(annotationTelephone.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是电话号"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationTelephone.message()));
}
resultflag = false;
}
}
//范围校验
if(field.isAnnotationPresent(Range.class)){
Range annotationRange = field.getAnnotation(Range.class);
boolean handelIsNum = handelIsNum(formatValue);
if(!handelIsNum){
resultflag = false;
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是数字类型"));
}else{
if(formatValue != null){
if(annotationRange.min() <= Integer.valueOf(formatValue.toString())&& Integer.valueOf(formatValue.toString()) <= annotationRange.max()){
}else{
if(StringUtils.isEmpty(annotationRange.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不在指定范围内"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationRange.message()));
}
resultflag = false;
}
}
}
}
//手机号校验
if(field.isAnnotationPresent(Phone.class)){
Phone annotationPhone = field.getAnnotation(Phone.class);
boolean isTelephone = HandlePhone(formatValue,resultflag);
if(!isTelephone){
if(StringUtils.isEmpty(annotationPhone.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是手机号"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationPhone.message()));
}
resultflag = false;
}
}
//非空校验
if(field.isAnnotationPresent(NotNull.class)){
NotNull annotationNotNull = field.getAnnotation(NotNull.class);
// if(formatValue == null || "".equals(formatValue.toString())){
if(StringUtils.isEmpty(String.valueOf(formatValue))){
if(StringUtils.isEmpty(annotationNotNull.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不能为空"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationNotNull.message()));
}
resultflag = false;
}
}
//最小值验
if(field.isAnnotationPresent(Min.class)){
Min annotationNotMin = field.getAnnotation(Min.class);
boolean handelIsNum = handelIsNum(formatValue);
if(!handelIsNum){
resultflag = false;
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是数字类型"));
}else{
if(formatValue != null){
if(Integer.valueOf(formatValue.toString()) < annotationNotMin.value()){
if(StringUtils.isEmpty(annotationNotMin.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"小于最小值"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationNotMin.message()));
}
resultflag = false;
}
}
}
}
//最大值校验
if(field.isAnnotationPresent(Max.class)){
Max annotationNotMax = field.getAnnotation(Max.class);
boolean handelIsNum = handelIsNum(formatValue);
if(!handelIsNum){
resultflag = false;
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是数字类型"));
}else{
if(formatValue != null){
if(Integer.valueOf(formatValue.toString()) > annotationNotMax.value()){
if(StringUtils.isEmpty(annotationNotMax.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"大于最大值"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationNotMax.message()));
}
resultflag = false;
}
}
}
}
//url校验
if(field.isAnnotationPresent(IsUrl.class)){
IsUrl annotationIsUrl = field.getAnnotation(IsUrl.class);
boolean handelIsUrl = handelIsUrl(formatValue);
if(!handelIsUrl){
if(StringUtils.isEmpty(annotationIsUrl.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是url格式"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationIsUrl.message()));
}
resultflag = false;
}
}
//数字校验
if(field.isAnnotationPresent(IsNum.class)){
IsNum annotationIsNum = field.getAnnotation(IsNum.class);
boolean handelIsNum = handelIsNum(formatValue);
if(!handelIsNum){
if(StringUtils.isEmpty(annotationIsNum.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是数字格式"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationIsNum.message()));
}
resultflag = false;
}
}
//中文校验
if(field.isAnnotationPresent(IsChinese.class)){
IsChinese annotationIsChinese = field.getAnnotation(IsChinese.class);
boolean handelChinese = handelChinese(formatValue);
if(!handelChinese){
if(StringUtils.isEmpty(annotationIsChinese.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是中文格式"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationIsChinese.message()));
}
resultflag = false;
}
}
//身份证校验
if(field.isAnnotationPresent(IdCard.class)){
IdCard annotationIdCard = field.getAnnotation(IdCard.class);
boolean handelIdCard = handelIdCard(formatValue);
if(!handelIdCard){
if(StringUtils.isEmpty(annotationIdCard.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是身份证格式"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationIdCard.message()));
}
resultflag = false;
}
}
//邮箱校验
if(field.isAnnotationPresent(Email.class)){
Email annotationEmail = field.getAnnotation(Email.class);
boolean handelEmail = handelEmail(formatValue);
if(!handelEmail){
if(StringUtils.isEmpty(annotationEmail.message())){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,"不是邮箱格式"));
}else{
errorMsg.append(addErrMsg(currRowIndex+1,c+1,annotationEmail.message()));
}
resultflag = false;
}
}
//自定义 正则表达式校验
if(field.isAnnotationPresent(CustomValidate.class)){
CustomValidate CustomValidate = field.getAnnotation(CustomValidate.class);
boolean handelCustomValidate = handelCustomValidate(formatValue,CustomValidate.regex());
if(!handelCustomValidate){
errorMsg.append(addErrMsg(currRowIndex+1,c+1,CustomValidate.message()));
resultflag = false;
}
}
//别名校验
if(field.isAnnotationPresent(Alias.class)){
Alias annotationAlias = field.getAnnotation(Alias.class);
AliasItem[] values = annotationAlias.values();
for(AliasItem alias : values){
String source = alias.source();
String target = alias.target();
if(source.equals(formatValue.toString())){
formatValue = target;
}
}
}
if(!StringUtils.isEmpty(errorMsg.toString())){
uploadDataResultDto.getResultMsg().add(errorMsg.toString());
errorMsg.setLength(0);
}
//该列的数据最终结果
if(resultflag){
if(type.getName().equals("java.lang.Integer")){
method.invoke(newInstance, Integer.valueOf(formatValue.toString()));continue;
}
if(type.getName().equals("java.lang.Double")){
method.invoke(newInstance, Double.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("java.lang.Float")){
method.invoke(newInstance, Float.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("java.lang.Long")){
method.invoke(newInstance,Long.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("java.lang.Short")){
method.invoke(newInstance, Short.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("java.lang.Byte")){
method.invoke(newInstance, Byte.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("java.lang.Boolean")){
method.invoke(newInstance, Boolean.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("java.lang.String")){
method.invoke(newInstance, String.valueOf(formatValue.toString())); continue;
}
if(type.getName().equals("int")){
method.invoke(newInstance,Integer.valueOf(formatValue.toString()));continue;
}
if(type.getName().equals("double")){
method.invoke(newInstance, Double.valueOf(formatValue.toString()));continue;
}
if(type.getName().equals("long")){
method.invoke(newInstance, Long.valueOf(formatValue.toString()));continue;
}
if(type.getName().equals("short")){
method.invoke(newInstance, Short.valueOf(formatValue.toString()));continue;
}
if(type.getName().equals("boolean")){
method.invoke(newInstance, Boolean.valueOf(formatValue.toString()));continue;
}
if(type.getName().equals("float")){
method.invoke(newInstance, Float.valueOf(formatValue.toString()));continue;
}
}
}
if(resultflag){
uploadDataResultDto.getDatas().add(newInstance);
}
currRowIndex++;
}
if(uploadDataResultDto.getDatas().size() != 0){
uploadDataResultDto.setResultCode("200");
}else{
uploadDataResultDto.setResultCode("500");
}
return uploadDataResultDto;
}
private boolean checkIsTemplate(StringBuffer errorMsg, List<ExcelHeader> headers) {
boolean flag = true;
if(titleLine != null){
curRow = sheet.getRow(titleLine);
if(null == curRow) {
flag = false;
return flag;
}
/*读取列*/
int lastCellNum = (int)curRow.getPhysicalNumberOfCells();
if(lastCellNum != headers.size()){
flag = false;
return flag;
}
}
return flag;
}
//====================================校验方法实现类===start===================================================
/**
* 自定义校验
* @param formatValue
* @return
*/
private boolean handelCustomValidate(Object formatValue,String regex) {
if(formatValue == null){return true;}
return Pattern.compile(regex).matcher(formatValue.toString()).matches();
}
/**
* 邮箱校验
* @param formatValue
* @return
*/
private boolean handelEmail(Object formatValue) {
if(formatValue == null){return true;}
return Pattern.compile("^\\w+((-\\w+)|(\\.\\w+))*\\@[A-Za-z0-9]+((\\.|-)[A-Za-z0-9]+)*\\.[A-Za-z0-9]+$").matcher(formatValue.toString()).matches();
}
/**
* 身份证校验
* @param formatValue
* @return
*/
private boolean handelIdCard(Object formatValue) {
if(formatValue == null){return true;}
return Pattern.compile("^\\d{15}|^\\d{17}([0-9]|X|x)$").matcher(formatValue.toString()).matches();
}
/**
* 判断是否是中文
* @param formatValue
* @return
*/
private boolean handelChinese(Object formatValue) {
if(formatValue == null){return true;}
return Pattern.compile("^[\\u4E00-\\u9FA5\\uF900-\\uFA2D]+$").matcher(formatValue.toString()).matches();
}
/**
* 判断是是否是url
* @param formatValue
* @return
*/
private boolean handelIsUrl(Object formatValue) {
if(formatValue == null){return true;}
return Pattern.compile("^http[s]?:\\/\\/([\\w-]+\\.)+[\\w-]+([\\w-./?%&=]*)?$").matcher(formatValue.toString()).matches();
}
private boolean handelIsNum(Object formatValue){
if(formatValue == null){return true;}
return Pattern.compile("^([+-]?)\\d*\\.?\\d+$").matcher(formatValue.toString()).matches();
}
/**
* 验证手机号
* @param formatValue
* @param resultflag
* @return
*/
private boolean HandlePhone(Object formatValue, boolean resultflag) {
if(formatValue == null){return true;}
return Pattern
.compile("^((13[0-9])|(15[^4,\\D])|(18[^1^4,\\D]))\\d{8}")
.matcher(formatValue.toString()).matches();
}
/**
* 校验字段是否是手机号
* @param formatValue
* @param resultflag
* @return
*/
private boolean HandleTelephone(Object formatValue, boolean resultflag) {
if(formatValue == null){return true;}
return Pattern.compile("^(([0\\+]\\d{2,3}-)?(0\\d{2,3})-)?(\\d{7,8})(-(\\d{3,}))?$").matcher(formatValue.toString()).matches();
}
//====================================校验方法实现类==end====================================================
/**
* 错误信息链接
* @param rowNum
* @param colNum
* @param mess
* @return
*/
private String addErrMsg(int rowNum,int colNum,String mess){
return "第"+rowNum+"行第"+colNum+"列存在异常，异常信息："+mess;
}
private List<ExcelHeader> getImportExcelHeaders(Class clz) throws ExcelException {
LOGGER.info("import Excel ====>>>> 导入的模板对象是：{}",clz);
List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
/*获得所有属性*/
Field[] declaredFields = clz.getDeclaredFields();
if(declaredFields == null || declaredFields.length ==0){
throw new ExcelException("传入的导入对象没有使用<<ExcelImportField>>注解");
}
/*遍历所有属性*/
for(Field field : declaredFields){
//判断当前属性上面是否有ExcelField注解存在@ExcelImportField
if(field.isAnnotationPresent(ExcelImportField.class)){
ExcelImportField annotation = field.getAnnotation(ExcelImportField.class);
headers.add(new ExcelHeader(annotation.order(),field.getName()));
}
}
Collections.sort(headers);
LOGGER.info("import Excel ====>>>> 标题对象排序以后结果是：{}",JSON.toJSONString(headers));
currRowIndex = startLine;
return headers;
}
/**
* 读取Excel数据
* @param cell
* @return
*/
private Object getFormatValue(Cell cell) {
//暂存单元格内容
Object value = "";
//匹配单元格内容
if(cell!=null){
switch (cell.getCellType()) {
//数据格式类型
case Cell.CELL_TYPE_NUMERIC:
//判断是否是日期类型
if(DateUtil.isCellDateFormatted(cell)){
Date date = (Date) cell.getDateCellValue();
if(date!=null){
//格式化日期
value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
}else{
value = "";
}
}else{
//格式化数据
value =new DecimalFormat("###.###").format(cell.getNumericCellValue());
}
break;
//字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
//公式生成类型
case Cell.CELL_TYPE_FORMULA:
//导入时如果为公式生成的数据则无值
if(!cell.getStringCellValue().equals("")){
value = cell.getStringCellValue();
}else{
value = cell.getNumericCellValue();
}
break;
//空白
case Cell.CELL_TYPE_BLANK:
break;
//布尔型
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
//错误格式
case Cell.CELL_TYPE_ERROR:
break;
//数字格式
default:
value = cell.toString();
}
}
return value;
}
}
UploadDataResultDto.class
package com.lucky.xyt.utils.excelUtils;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Excel导入工具类~~导入返回信息
* @author huangjinhui
*
*/
public class UploadDataResultDto<T> {
/*返回状态 */
private String resultCode;
/*返回错误信息*/
private List<String> resultMsg = new ArrayList<String>();
/*返回列表，里面会放入校验过的数据*/
private List<T> datas = new ArrayList<T>();
public String getResultCode() {
return resultCode;
}
public void setResultCode(String resultCode) {
this.resultCode = resultCode;
}
public List<String> getResultMsg() {
return resultMsg;
}
public void setResultMsg(List<String> resultMsg) {
this.resultMsg = resultMsg;
}
public List<T> getDatas() {
return datas;
}
public void setDatas(List<T> datas) {
this.datas = datas;
}
}
ExcelUtils.class
package com.lucky.xyt.utils;
import com.lucky.xyt.utils.excelUtils.FlagInfoExcelUtils;
import com.lucky.xyt.utils.excelUtils.UploadDataResultDto;
import com.lucky.xyt.utils.excelUtils.annotation.entity.ImportTableParams;
import com.lucky.xyt.utils.excelUtils.annotation.util.ExcelField;
import lombok.Data;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.Serializable;
import java.util.List;
/**
* User: Michael
* Date: 2019/8/20
* Time: 9:12
*
* @author
*/
public class ExcelUtils {
/**
* 导出
*
* @param clazz 导出对象的class
* @param list 导出集合
* @param sheetName 工作簿名称
*/
public static Workbook exportByObj(Class clazz, List list, String sheetName) throws Exception {
Workbook wb = FlagInfoExcelUtils.getInstance().exportByObj(clazz, list, true, sheetName);
return wb;
}
;
// 注意：
// 1）ExamUserExcel对象是：
@Data
public class ExamUserExcel implements Serializable {
/**
* 导入数据
* ImportTableParams导入表头设置
*
* @pram Class 类名
* @pram file 文件
*/
public static UploadDataResultDto importExcel(MultipartFile file, Class clazz) throws Exception {
UploadDataResultDto UploadDataResultDto = FlagInfoExcelUtils.getInstance().importDataByFileInputStream(file.getInputStream(), clazz, new ImportTableParams(0, 2, 0, 1));
return UploadDataResultDto;
}
}
前端导出
/**
* excel导出
*/
exportTable() {
// this.DefaultData.exportExcelMax限制一下导出的总条数
if (this.total <= this.exportExcelMax) {
this.$confirm('确定要导出当前<strong>' + this.total + '</strong>条数据？', '提示', {
dangerouslyUseHTMLString: true,
confirmButtonText: '确定',
cancelButtonText: '取消'
}).then(() => {
this.getExpportData()
}).catch(() => {
})
} else {
this.$confirm('当前要导出的<strong>' + this.total + '</strong>条数据，数据量过大，不能一次导出！<br/>建议分时间段导出所需数据。', '提示', {
dangerouslyUseHTMLString: true,
showCancelButton: false
}).then(() => {
}).catch(() => {
})
}
},
getExpportData: function() {
const _this = this
const loading = this.$loading({
lock: true,
text: '正在导出，请稍等......',
spinner: 'el-icon-loading',
background: 'rgba(0, 0, 0, 0.7)'
})
const excleParam = this.param
excleParam.pageSize = this.total
excleParam.currentPage = 1
request({ url: '/enterprise/getEnterpriseList', method: 'get', params: excleParam }).then(function(res) {
console.info(res)
// handleDataList这里可以对导出的数据根据需求做下处理
const handleDataList = res.data
console.info(handleDataList)
// handleDataList = _this.formatDict(handleDataList)
if (res.data.length > 0) {
require.ensure([], () => {
/* eslint-disable */
// 这里的径路要修改正确
const { export_json_to_excel } = require('../../vendor/Export2Excel')
/* eslint-enable */
// 导出的表头
const tHeader = ['企业简称', '企业全称', '企业编码', '类型', '企业法人', '省', '市', '区']
// const tHeader = ['姓名', '单位', '系统编号', '性别', '民族', '出生年月', '年龄', '政治面貌', '文化程度', '职工类型', '职务', '工种', '工种等级']
// 导出表头要对应的数据
const filterVal = ['simpleName', 'fullName', 'enterpriseNumber', 'enterpriseType', 'legalPerson', 'province', 'city', 'area']
// const filterVal = ['realName', 'deptCategory', 'systemNum', 'sex', 'ethnicity', 'birthDate', 'age', 'politicalStatus', 'education', 'workerType', 'duty', 'workerBranch', 'rank']
// 如果对导出的数据没有可处理的需求，把下面的handleDataList换成res.data.list即可，删掉上面相应的代码
const data = _this.formatJson(filterVal, handleDataList)
// this.DefaultData.formatLongDate.getNow()自己写的一个获取当前时间，方便查找导出后的文件。根据需求自行可处理。
export_json_to_excel(tHeader, data, '企业信息导出-')
_this.$message({
message: '导出成功',
duration: 2000,
type: 'success'
})
})
} else {
_this.$message({
message: '数据出錯，请联系管理员',
duration: 2000,
type: 'warning'
})
}
loading.close()
// eslint-disable-next-line handle-callback-err
}, error => {
loading.close()
})
},
/**
* 对导出数据格式处理
*/
formatJson(filterVal, jsonData) {
return jsonData.map(v => filterVal.map(j => v[j]))
}
}
需要在 图片所示的文件下加上
安装三个依赖包
npm install -S file-saver
npm install -S xlsx
npm install -D script-loader
因为下载的插件有点问题 所以 我就直接 把以前的 js 直接替换过来了
Export2excel.js
/* eslint-disable */
require('script-loader!file-saver');
require('script-loader!@/vendor/Blob');
require('script-loader!xlsx/dist/xlsx.core.min');
function generateArray (table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function (range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({ s: { r: R, c: outRow.length }, e: { r: R + rowspan - 1, c: outRow.length + colspan - 1 } });
}
;
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum (v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays (data, opts) {
var ws = {};
var range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = { v: data[R][C] };
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
}
else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook () {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function s2ab (s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
export function export_table_to_excel (id) {
var theTable = document.getElementById(id);
console.log('a')
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
console.log(data);
var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), "test.xlsx")
}
function formatJson (jsonData) {
console.log(jsonData)
}
export function export_json_to_excel (th, jsonData, defaultTitle) {
/* original data */
var data = jsonData;
data.unshift(th);
var ws_name = "SheetJS";
var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
var title = defaultTitle || '列表'
saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), title + ".xlsx")
}
具体的可以参考下面的一个博主写得文章
https://blog.csdn.net/qq_36410795/article/details/89403550
一位博主的博客前端导出的可以参考下
我这方法里面 有一些参数 就没有 展示出来如果需要是用的 将里面的相关参数 在上面初始化一下 就好了
1853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
