java excel导出(基于注解)

发布于:2021-12-03 02:39:46

  小白,做日志只是为了方便自己查看,能帮到别人当然更好,不喜勿喷。


  上代码


  依赖:




org.apache.poi
poi-ooxml
3.10-FINAL


 注解,使用了俩个注解,一个是sheet公用属性,以及单元格属性,只是简单的几个属性,可自行扩展。


 sheet公用注解:



package com.authorize.utils.excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
*
* @filename ExcelBookAnnotation.java
* @pakage com.authorize.utils.excel
* @descption TODO(用一句话表述类的作用)
* @author Pandong
* @date 2019年4月8日
*/
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelBookAnnotation {

/**
* 标题
* @return
*/
String title();

}

单元格注解:



package com.authorize.utils.excel;

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;

/**
*
* @filename ExcelAnnotation.java
* @pakage com.authorize.utils.excel
* @descption TODO(用一句话表述类的作用)
* @author Pandong
* @date 2019年4月8日
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColAnnotation {


/**
* 列名
* @return
*/
String text() default "";

/**
* 列宽
* @return
*/
int colWidth() default 6000;

/**
* 字体颜色,默认黑色
* @return
*/
short color() default HSSFColor.BLACK.index;

/**
* 导出是是否忽略该字段,默认不忽略
* @return
*/
int ignore() default 0;

}

实体类,其中使用了lombok,感兴趣可以百度一下,不用可以不相关注解删掉,自己写get/set等方法。



package com.authorize.utils.excel;

import java.io.Serializable;

import org.apache.poi.hssf.util.HSSFColor;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
*
* @filename ExcelBean.java
* @pakage com.authorize.utils.excel
* @descption TODO(用一句话表述类的作用)
* @author Pandong
* @date 2019年4月8日
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelBookAnnotation(title = "日常工作表")
public class ExcelBean implements Serializable{

@ExcelColAnnotation( ignore = 1 )
private static final long serialVersionUID = 4248622093488850427L;

@ExcelColAnnotation(colWidth = 8000, text = "姓名",color = HSSFColor.RED.index)
private String name;
@ExcelColAnnotation( text = "年龄", colWidth = 2000)
private int age;
@ExcelColAnnotation( text = "地址", colWidth = 12000)
private String addr;

}

最后就是excel导出的工具类了:






package com.authorize.utils.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.authorize.utils.CommonUtils;
import com.authorize.utils.exception.CustomException;

public class ExcelUtils {

private static final Log log = LogFactory.getLog(ExcelUtils.class);

/**
* 行高
*/
private int rowHeight = 400;
/**
* 列宽
*/
private int colWidth = 8500;
/**
* 起始位置
*/
private int rowIndex = 0;
/**
* 默认标题
*/
private String title = "defaultExcel";

private Workbook workbook;
private Sheet sheet;
/**
* 公共列样式
*/
private CellStyle cellStyle;
/**
* 操作的实体类
*/
private T obj;
/**
* 列属性集合
*/
private List> colList = new ArrayList<>();
/**
* 列样式集合
*/
private List styleList = new ArrayList<>();

private ClassUtils util = new ClassUtils();

public ExcelUtils( T obj ) {
this.obj = obj;
initWorkbook();
}

public ExcelUtils(T obj,int rowHeight, int colWidth, int rowIndex, String title) {
this(obj);
this.rowHeight = rowHeight;
this.colWidth = colWidth;
this.rowIndex = rowIndex;
this.title = title;
}

/**
* 默认文档设置文档
*/
private void initWorkbook() {
if ( CommonUtils.isEmpty(this.obj) ) {
throw new CustomException("未指定导出实体类,无法进行导出操作");
}
util.parseBookAnnotation();
workbook = new XSSFWorkbook();
sheet = workbook.createSheet(this.title); // 创建工作页
sheet.setDefaultColumnWidth(colWidth); // 设置默认列宽
cellStyle = createCellStyle();
cellStyle.setFont(createFont(null, (short)0, (short)0));
titleSetting();
}

/**
* 标题、列名相关设置
*/
private void titleSetting() {
Row topRow = createRow((short)600);
mergedRegion(0, 0, 0, this.colList.size()-1); // 合并标题行
Cell cell = createCell(topRow,0,cellStyle);
cell.setCellValue(this.title);
Row textRow = createRow((short)0);
for ( int i = 0; i < colList.size(); i++ ) {
Map fieldMap = colList.get(i);
sheet.setColumnWidth(i, Integer.parseInt(fieldMap.get("width").toString()));
Cell cell1 = createCell(textRow,i,cellStyle);
cell1.setCellValue(fieldMap.get("text").toString());
addColStlye(null, (short)0, Short.parseShort(fieldMap.get("color").toString()));
}
}

/**
* 创建字体对象
* @param fontName
* 字体库名称
* @param fontSize
* 字体大小-传0默认14
* @param color
* 字体颜色参考{@link HSSFColor.BLACK.index}
* @return
*/
private Font createFont(String fontName, short fontSize,short color) {
Font font = this.workbook.createFont();
if ( CommonUtils.isEmpty(fontName) ) {
fontName = "宋体";
}
if ( fontSize == 0 ) {
fontSize = (short)14;
}
if ( color == 0 ) {
color = HSSFColor.BLACK.index;
}
font.setFontName("宋体"); //设置为宋体字
font.setFontHeightInPoints(fontSize); //设置字体大小
font.setColor(color);
return font;
}



/**
* 创建列样式
* @param alignment
* @param vertical
* @return
*/
private CellStyle createCellStyle(short ...alignments) {
CellStyle style = this.workbook.createCellStyle();
short alignment = HSSFCellStyle.ALIGN_CENTER_SELECTION,vertical = HSSFCellStyle.VERTICAL_CENTER;
if ( alignments.length > 0 ) {
alignment = alignments[0];
if ( alignments.length > 1 ) {
vertical = alignments[1];
}
}
//水*居中
style.setAlignment(alignment);
//垂直居中
style.setVerticalAlignment(vertical);
return style;
}

/**
* 合并行、列
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
private void mergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
CellRangeAddress region = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol); // 合并行
sheet.addMergedRegion(region);
}

/**
* 创建单元格
* @param row
* @param index
* @param style
* @return
*/
private Cell createCell ( Row row,int index,CellStyle style ) {
Cell cell = row.createCell(index);
if ( CommonUtils.isNotEmpty(style) ) {
cell.setCellStyle(style);
}
return cell;
}

/**
* 创建行
* @return
*/
private Row createRow ( short rowHeight ) {
Row row = sheet.createRow(this.rowIndex);

if ( rowHeight == 0 ) {
rowHeight = (short)this.rowHeight;
}
row.setHeight(rowHeight);
this.rowIndex ++;
return row;
}

/**
* 创建每一列的样式
* @param fontName
* @param fontSize
* @param color
* @param alignments
*/
private void addColStlye(String fontName, short fontSize,short color,short ...alignments) {
Font ft = createFont(fontName, fontSize, color);
CellStyle style = createCellStyle(alignments);
style.setFont(ft);
styleList.add(style);
}

/**
* 生成Excel表
* @param list
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public void createExcel(List list,String parentPath) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
for ( T temp : list ) {
Row row = createRow((short)0);
for (int i = 0; i < colList.size(); i ++ ) {
Map cl = colList.get(i);
Cell cell = createCell(row,i,styleList.get(i));
String methodName = cl.get("methodName").toString();
Object obj = util.valueToGet(temp, methodName);
cell.setCellValue(obj.toString());
}
}
File file = new File(parentPath);
if (!file.exists()){
file.createNewFile();
}
FileOutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);
outputStream.close();
}


public List getListBean(){
List list = new ArrayList<>();
ExcelBean bean = null;
Random random = new Random();
for ( int i = 0; i < 100; i++ ) {
bean = new ExcelBean();
bean.setAddr(UUID.randomUUID().toString().substring(0, 15));
bean.setAge(random.nextInt(100));
bean.setName("张三"+(i + 1) +"号");
list.add(bean);
}
return list;
}

class ClassUtils{


private ClassUtils() {}


/**
* 反射获取value
* @param object
* @return
* @throws SecurityException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
private Object valueToGet( Object object, String methodName ) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
Method method = object.getClass().getDeclaredMethod(methodName);
return method.invoke(object);
}

/**
* 首字母大写
* @param fieldName
* @return
*/
public String convertMethodName( String fieldName ) {
String newField = fieldName.substring(1, fieldName.length());
return fieldName.substring(0,1).toUpperCase()+newField;
}

/**
* 通过注解获取导出sheet相关注解属性
*/
public void parseBookAnnotation( ) {
Annotation[] ans = ExcelUtils.this.obj.getClass().getAnnotations();
for ( Annotation temp : ans ) {
if ( temp instanceof ExcelBookAnnotation) {
String title = ((ExcelBookAnnotation) temp).title();
ExcelUtils.this.title = title;
parseFielAnnotation( );
}
}
}

/**
* 通过注解获取列相关注解属性
*/
public void parseFielAnnotation( ) {
Field [] fiels = ExcelUtils.this.obj.getClass().getDeclaredFields();
for ( Field temp : fiels ) {
Annotation[] ans = temp.getAnnotations();
for ( Annotation tempAn : ans ) {
if ( tempAn instanceof ExcelColAnnotation ) {
ExcelColAnnotation col = ((ExcelColAnnotation) tempAn);
int ignore = col.ignore();
if ( ignore == 0 ) {
Map fieldMap = new HashMap<>();
fieldMap.put("width", col.colWidth());
fieldMap.put("color", col.color());
fieldMap.put("text", col.text());
fieldMap.put("methodName", "get"+convertMethodName(temp.getName()));
ExcelUtils.this.colList.add(fieldMap);
}
}
}
}
}

}


public static void main(String[] args) throws IOException, ParseException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
ExcelUtils excel = new ExcelUtils( new ExcelBean());
List list = excel.getListBean();
excel.createExcel(list, "C:\Users\Administrator\Desktop\test_bak\test.xlsx");
}

}

View Code

?


到这里就算完了,有不好的地方可以提出。


?



转载于:https://www.cnblogs.com/xxpandong/p/10690400.html

相关推荐

最新更新

猜你喜欢