一、导入所需要的jar包
poi.jar包下载地址:
https://archive.apache.org/dist/poi/release/bin/
commons-lang3-3.1.jar包下载地址
http://www.java2s.com/Code/Jar/c/Downloadcommonslang331jar.htm
二、创建注解类Excel
package excel;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.math.BigDecimal;
/**
* 自定义导出Excel数据注解
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
/**
* 导出时在excel中排序
*/
public int sort() default Integer.MAX_VALUE;
/**
* 导出到Excel中的名字.
*/
public String name() default "";
/**
* 日期格式, 如: yyyy-MM-dd
*/
public String dateFormat() default "";
/**
* 读取内容转表达式(如: 0=男,1=女,2=未知)
*/
public String readConverterExp() default "";
/**
* 分隔符,读取字符串组内容
*/
public String separator() default ",";
/**
* 导出时在excel中每个列的高度 单位为字符
*/
public double height() default 14;
/**
* 导出时在excel中每个列的宽 单位为字符
*/
public double width() default 16;
/**
* 另一个类中的属性名称,支持多级获取,以小数点隔开
*/
public String targetAttr() default "";
/**
* 当值为空时,字段的默认值
*/
public String defaultValue() default "";
/**
* 文字后缀,如 % 90 变成90%
*/
public String suffix() default "";
/**
* 导出类型(0数字 1字符串)
*/
public ColumnType cellType() default ColumnType.STRING;
public enum ColumnType
{
NUMERIC(0), STRING(1), IMAGE(2);
private final int value;
ColumnType(int value)
{
this.value = value;
}
public int value()
{
return this.value;
}
}
/**
* 导出字段对齐方式(0:默认;1:靠左;2:居中;3:靠右)
*/
public Align align() default Align.AUTO;
public enum Align
{
AUTO(0), LEFT(1), CENTER(2), RIGHT(3);
private final int value;
Align(int value)
{
this.value = value;
}
public int value()
{
return this.value;
}
}
}
```c
package excel;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel注解集
*
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excels
{
Excel[] value();
}
三、创建实体类
package excel;
import java.util.Date;
public class User {
@Excel(name="用户姓名" ,align = Excel.Align.LEFT)
private String username;
@Excel(name="用户密码",align = Excel.Align.CENTER)
private String password;
@Excel(name="用户出生日期",dateFormat = "yyyy-MM-dd",align = Excel.Align.RIGHT)
private Date date;
@Excel(name="用户性别",readConverterExp = "0=男,1=女")
private String gender;
@Excel(name="用户年龄",cellType = Excel.ColumnType.NUMERIC)
private Integer age;
/** 部门对象 */
@Excels({
@Excel(name = "部门名称", targetAttr = "deptName"),
@Excel(name = "部门负责人", targetAttr = "leader")
})
private SysDept dept;
public User(String username, String password, Date date, String gender, Integer age, SysDept dept) {
this.username = username;
this.password = password;
this.date = date;
this.gender = gender;
this.age = age;
this.dept = dept;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public SysDept getDept() {
return dept;
}
public void setDept(SysDept dept) {
this.dept = dept;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
package excel;
public class SysDept {
/** 部门名称 */
private String deptName;
/** 负责人 */
private String leader;
public SysDept(String deptName, String leader) {
this.deptName = deptName;
this.leader = leader;
}
public String getLeader() {
return leader;
}
public void setLeader(String leader) {
this.leader = leader;
}
}
四、创建导出数据到excel表的工具类
package excel;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
/**
* Excel相关处理
*
*/
public class ExcelUtil<T>
{
/**
* Excel sheet最大行数,默认65536
*/
public static final int sheetSize = 65536;
/**
* 工作表名称
*/
private String sheetName;
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 导入导出数据列表
*/
private List<T> list;
/**
* 注解列表
*/
private List<Object[]> fields;
/**
* 最大高度
*/
private short maxHeight;
/**
* 数字格式
*/
private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
/**
* 实体对象
*/
public Class<T> clazz;
public ExcelUtil(Class<T> clazz)
{
this.clazz = clazz;
}
public void init(List<T> list, String sheetName)
{
if (list == null)
{
list = new ArrayList<T>();
}
this.list = list;
this.sheetName = sheetName;
createExcelField();
createWorkbook();
}
/**
* 得到所有定义字段
*/
private void createExcelField()
{
this.fields = new ArrayList<Object[]>();
List<Field> tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
for (Field field : tempFields)
{
if(field.isAnnotationPresent(Excel.class)){
putToField(field,field.getAnnotation(Excel.class));
}
//多注解
if(field.isAnnotationPresent(Excels.class)){
Excels attrs=field.getAnnotation(Excels.class);
Excel[] excels = attrs.value();
for(Excel excel:excels){
putToField(field,excel);
}
}
}
this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
this.maxHeight = getRowHeight();
}
/**
* 放到字段集合中
*/
private void putToField(Field field,Excel attr){
if(attr!=null){
this.fields.add(new Object[]{field,attr});
}
}
/**
* 根据注解获取最大行高
*/
public short getRowHeight()
{
double maxHeight = 0;
for (Object[] os : this.fields)
{
Excel excel = (Excel) os[1];
maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
}
return (short) (maxHeight * 20);
}
/**
* 创建一个工作簿
*/
public void createWorkbook()
{
this.wb = new SXSSFWorkbook(500);
}
/**
* 编码文件名
*/
public String encodingFilename(String filename)
{
filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
return filename;
}
/**
* 对list数据源将其里面的数据导入到excel表单
*
* @return 结果
*/
public void exportExcel()
{
OutputStream out = null;
try
{
writeSheet();
String filename = encodingFilename(sheetName);
out = new FileOutputStream("D:\\"+filename);
wb.write(out);
}
catch (Exception e)
{
System.out.println("导出数据失败");
}
finally
{
IOUtils.closeQuietly(wb);
IOUtils.closeQuietly(out);
}
}
/**
* 创建写入数据到Sheet
*/
public void writeSheet()
{
// 取出一共有多少个sheet.
double sheetNo = Math.ceil(list.size() / sheetSize);
for (int index = 0; index <= sheetNo; index++)
{
createSheet(sheetNo, index);
// 产生一行
Row row = sheet.createRow(0);
int column = 0;
// 写入各个字段的列头名称
for (Object[] os : fields)
{
Excel excel = (Excel) os[1];
this.createCell(excel, row, column++);
}
fillExcelData(index, row);
}
}
/**
* 填充excel数据
*
* @param index 序号
* @param row 单元格行
*/
public void fillExcelData(int index, Row row)
{
int startNo = index * sheetSize;
int endNo = Math.min(startNo + sheetSize, list.size());
for (int i = startNo; i < endNo; i++)
{
row = sheet.createRow(i + 1 - startNo);
// 得到导出对象.
T vo = (T) list.get(i);
int column = 0;
for (Object[] os : fields)
{
Field field = (Field) os[0];
Excel excel = (Excel) os[1];
// 设置实体类私有属性可访问
field.setAccessible(true);
this.addCell(excel, row, vo, field, column++);
}
}
}
/**
* 添加单元格
*/
public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
Cell cell = null;
try {
// 设置行高
row.setHeight(maxHeight);
// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
// 创建cell
cell = row.createCell(column);
int align = attr.align().value();
cell.setCellStyle(styles.get("data"+(align>=1&&align<=3?align:"")));
Object value = getTargetValue(vo, field, attr);
String dateFormat=attr.dateFormat();
String readConverterExp=attr.readConverterExp();
String separator=attr.separator();
if(isNotEmpty(dateFormat)&&isNotNull(value)){
cell.setCellValue(parseDateToStr(dateFormat,(Date)value));
}else if(isNotEmpty(readConverterExp)&&isNotNull(value)){
cell.setCellValue(convertByExp(toStr(value),readConverterExp,separator));
} else{
setCellVo(value,attr,cell);
}
}catch (Exception e){
System.out.println("导入出现错误");
}
return cell;
}
//解析导出值 0=男 1=女
public static String convertByExp(String propertyValue,String converterExp,String separator){
StringBuilder propertyString = new StringBuilder();
String[] convertSource=converterExp.split(",");
for(String item:convertSource){
String[] itemArray = item.split("=");
if(StringUtils.containsAny(separator,propertyValue)){
for(String value:propertyValue.split(separator)){
if (itemArray[0].equals(value))
{
propertyString.append(itemArray[1] + separator);
break;
}
}
}else{
if (itemArray[0].equals(propertyValue))
{
return itemArray[1];
}
}
}
return StringUtils.stripEnd(propertyString.toString(),separator);
}
//设置单元格信息
public void setCellVo(Object value,Excel attr,Cell cell){
if(Excel.ColumnType.STRING==attr.cellType()){
cell.setCellValue(isNull(value)?attr.defaultValue():value+attr.suffix());
}
else if(Excel.ColumnType.NUMERIC==attr.cellType()){
if(isNotNull(value)){
cell.setCellValue(StringUtils.contains(toStr(value),".")?toDouble(value):toInt(value));
}
}
}
//获得属性值
private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
Object o = field.get(vo);
if(isNotEmpty(excel.targetAttr())){
String target=excel.targetAttr();
if(target.indexOf(".")>-1){
String[] targets=target.split("[.]");
for(String name:targets){
o=getValue(o,name);
}
}else{
o=getValue(o,target);
}
}
return o;
}
/**
* 以类的属性的get方法形式获取值
*/
private Object getValue(Object o,String name) throws Exception{
if (isNotNull(o) && isNotEmpty(name))
{
Class<?> clazz = o.getClass();
Field field = clazz.getDeclaredField(name);
field.setAccessible(true);
o = field.get(o);
}
return o;
}
/**
* 创建单元格
*/
public void createCell(Excel attr, Row row, int column)
{
// 创建列
Cell cell = row.createCell(column);
// 写入列信息
cell.setCellValue(attr.name());
setDataValidation(attr,row,column);
cell.setCellStyle(styles.get("header"));
}
/**
* 创建单元格样式
*/
public void setDataValidation(Excel attr,Row row,int column){
//设置列宽
sheet.setColumnWidth(column,(int)((attr.width()+0.72)*256));
}
/**
* 创建工作表
*
* @param sheetNo sheet数量
* @param index 序号
*/
public void createSheet(double sheetNo, int index)
{
this.sheet = wb.createSheet();
this.styles = createStyles(wb);
// 设置工作表的名称.
if (sheetNo == 0)
{
wb.setSheetName(index, sheetName);
}
else
{
wb.setSheetName(index, sheetName + index);
}
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb)
{
// 写入各条记录,每条记录对应excel表中的一行
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font totalFont = wb.createFont();
totalFont.setFontName("Arial");
totalFont.setFontHeightInPoints((short) 10);
style.setFont(totalFont);
styles.put("total", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(HorizontalAlignment.RIGHT);
styles.put("data3", style);
return styles;
}
//转为string对象
public static String toStr(Object value){
return toStr(value,null);
}
public static String toStr(Object value,String defaultValue){
if (null == value)
{
return defaultValue;
}
if (value instanceof String)
{
return (String) value;
}
return value.toString();
}
//判断字符串是否为非空串
public static boolean isNotEmpty(String str){
return !isEmpty(str);
}
//判断一个字符串是否为空串
public static boolean isEmpty(String str){
return isNull(str)||"".equals(str.trim());
}
//判断一个对象是否为空
public static boolean isNull(Object object){
return object==null;
}
//判断一个对象是否为非空
public static boolean isNotNull(Object object)
{
return !isNull(object);
}
//转换日期格式
public static final String parseDateToStr(final String format, final Date date)
{
return new SimpleDateFormat(format).format(date);
}
//转换为double
public static Double toDouble(Object value){
return toDouble(value,null);
}
public static Double toDouble(Object value,Double defaultValue){
if (value == null)
{
return defaultValue;
}
if (value instanceof Double)
{
return (Double) value;
}
if (value instanceof Number)
{
return ((Number) value).doubleValue();
}
final String valueStr = toStr(value, null);
if (StringUtils.isEmpty(valueStr))
{
return defaultValue;
}
try
{
// 支持科学计数法
return new BigDecimal(valueStr.trim()).doubleValue();
}
catch (Exception e)
{
return defaultValue;
}
}
public static Integer toInt(Object value){
return toInt(value,null);
}
public static Integer toInt(Object value, Integer defaultValue)
{
if (value == null)
{
return defaultValue;
}
if (value instanceof Integer)
{
return (Integer) value;
}
if (value instanceof Number)
{
return ((Number) value).intValue();
}
final String valueStr = toStr(value, null);
if (StringUtils.isEmpty(valueStr))
{
return defaultValue;
}
try
{
return Integer.parseInt(valueStr.trim());
}
catch (Exception e)
{
return defaultValue;
}
}
}
五、创建主函数类
package excel;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class Demo {
public static void main(String[] args) {
ExcelUtil<User> excelUtil=new ExcelUtil<User>(User.class);
List<User> userList=new ArrayList<User>();
userList.add(new User("张三","123",new Date(),"1",19,new SysDept("管理部门","赵六")));
userList.add(new User("李四","456",new Date(),"0",17,new SysDept("系统部门","刘七")));
userList.add(new User("王五","789",new Date(),"1,0",18,new SysDept("销售部门","杨八")));
excelUtil.init(userList,"用户表单");
excelUtil.exportExcel();
}
}
六、运行结果