java
程序员文章站
2022-03-27 07:56:59
...
最近,因为工作上的需要自己封装了poi处理excel接口,根据实体上的注解生成excel文件.
如果觉得写得不错,记得点赞.
本人原创
poi.jar下载地址:http://poi.apache.org/download.html
报表常量类
package com.zcj.poi;
public class ConstantsReport {
public static final int FORMAT_NO = 0;
public static final int FORMAT_CURRENCY = 1;
public static final int FORMAT_PERCENT = 2;
}
注解类
package com.zcj.poi;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* excel报表注解
* @author MrZhou
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD,ElementType.METHOD})
public @interface ReportAnnotation {
/**
* 列名
* @return
*/
public String name();
/**
* 排序
* @return
*/
public int order() default 0;
/**
* 格式化
* @return
*/
public int format() default ConstantsReport.FORMAT_NO;
/**
* 代理商等级
* @return
*/
public int agent_level() default ConstantsReport.AGENT_LEVEL_NO;
}
工作表参数类
package com.zcj.poi;
import java.util.ArrayList;
import java.util.List;
/**
* 工作表
* @author MrZhou
*
*/
public class SheetArgs {
/**工作表*/
public String sheet_name;
/**标题名*/
public String title_name;
/**class类型*/
public Class clazz;
/**数据源*/
public List source;
/**统计数据*/
public List<String> statistical_datas = new ArrayList<>();
/**
* 加入统计数据
* @param statistical_data
*/
public void addStatistical_data(String statistical_data){
if(statistical_datas==null){
statistical_datas = new ArrayList<>();
}
statistical_datas.add(statistical_data);
}
}
工作簿参数类
package com.zcj.poi;
import java.io.File;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
/**
* excel参数
* @author MrZhou
*
*/
public class ExcelArgs {
/** 工作表 */
public List<SheetArgs> sheets = new ArrayList<>();
/** 文件名 */
private String excel_name;
/**父级目录*/
private String parent_path;
/**文件绝对路径*/
private String file_name;
public Integer agent_level = ConstantsReport.AGENT_LEVEL_NO;
public void addSheet(SheetArgs sheet){
if(sheets==null){
sheets = new ArrayList<>();
}
sheets.add(sheet);
}
public List<SheetArgs> getSheets() {
return sheets;
}
public void setSheets(List<SheetArgs> sheets) {
this.sheets = sheets;
}
public String getExcel_name() {
return excel_name;
}
public void setExcel_name(String excel_name) {
this.excel_name = excel_name;
}
public String getParent_path() {
return parent_path;
}
public void setParent_path(String parent_path) {
this.parent_path = parent_path;
}
public String getFile_name() {
return file_name;
}
public Integer getAgent_level() {
return agent_level;
}
public void setAgent_level(Integer agent_level) {
this.agent_level = agent_level;
}
public ExcelArgs(String excel_name) {
super();
this.excel_name = excel_name;
}
public ExcelArgs(String excel_name, String parent_path) {
super();
this.excel_name = excel_name;
this.parent_path = parent_path;
}
/**
* 初始化excel文件的路径
*/
public void init() {
if (!(excel_name != null && !excel_name.trim().equals(""))) {
throw new RuntimeException("excel 文件名不能为空");
}
excel_name = excel_name.trim();
if (!(excel_name.endsWith(".xls") || excel_name.endsWith(".xlsx"))) {
excel_name = excel_name + ".xls";
}
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
excel_name = format.format(new Date()) + "-" + excel_name;
if (!(parent_path != null && !parent_path.trim().equals(""))) {
parent_path = "C:\\excel";//文件目录
}
if (!(parent_path.endsWith(File.separator))) {
parent_path += File.separator;
}
File parentDir = new File(parent_path);
if (!parentDir.exists()) {
parentDir.mkdirs();
}
file_name = parent_path + UUID.randomUUID() + "_" + excel_name;
}
}
工具参数类
package com.zcj.poi;
/**
* excel报表参数
* @author MrZhou
*
*/
public class ReportArgs {
/**方法名*/
public String method;
/**属性名*/
public String field;
/**列名*/
public String name;
/**顺序*/
public int order;
/**格式化*/
public int format;
public ReportArgs(String field, String name, int order, int format, int agent_level) {
super();
this.field = field;
this.name = name;
this.order = order;
this.format = format;
}
}
excel工具类
package com.zcj.poi;
import java.io.FileOutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Locale;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.IndexedColors;
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.ss.util.WorkbookUtil;
/**
* 根据类上的属性和方法上的注解生成excel文件工具
*
* @author MrZhou
*
*/
public class ExeclAnnotationUtils {
/**
* 生成excel文件
*
* @param excel
* @throws Exception
*/
public static void createExcel(ExcelArgs excel) throws Exception {
excel.init();
System.err.println(excel.getFile_name());
Workbook wb = new HSSFWorkbook();// 创建excel文件
List<SheetArgs> sheets = excel.getSheets();
if (sheets != null && !sheets.isEmpty()) {
for (SheetArgs sheet : sheets) {
createSheet(wb, sheet);
}
}
FileOutputStream execl = new FileOutputStream(excel.getFile_name());// 创建一个文件流
wb.write(execl);// 把内容写入流
execl.close();
}
/**
* 创建工作簿
*
* @param workbook
* 工作簿
* @param mySheet
* 工资表参数
* @param agent_level
* 代理商等级
* @throws Exception
*/
private static void createSheet(Workbook workbook, SheetArgs mySheet) throws Exception {
Field[] fields = mySheet.clazz.getDeclaredFields();// 获取所有的属性
List<ReportArgs> Reports = new ArrayList<>();
for (Field field : fields) {
Annotation annotation = field.getAnnotation(ReportAnnotation.class);
if (annotation instanceof ReportAnnotation) {
ReportAnnotation myAnnotation = (ReportAnnotation) annotation;
ReportArgs report = new ReportArgs(field.getName(), myAnnotation.name(), myAnnotation.order(),
myAnnotation.format(), myAnnotation.agent_level());
Reports.add(report);
}
}
Method[] methods = mySheet.clazz.getDeclaredMethods();// 获取所有的方法
for (Method field : methods) {
Annotation annotation = field.getAnnotation(ReportAnnotation.class);
if (annotation instanceof ReportAnnotation) {
ReportAnnotation myAnnotation = (ReportAnnotation) annotation;
ReportArgs report = new ReportArgs(null, myAnnotation.name(), myAnnotation.order(),
myAnnotation.format(), myAnnotation.agent_level());
report.method = field.getName();
Reports.add(report);
}
}
Collections.sort(Reports, new Comparator<ReportArgs>() {// 列排序
@Override
public int compare(ReportArgs o1, ReportArgs o2) {
return o1.order - o2.order;
}
});
// 设置数据样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
// 设置标题样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font font = workbook.createFont();// 设置字体
font.setFontHeightInPoints((short) 24);// 字体大小
titleStyle.setFont(font);
// 设置统计样式
CellStyle statisticalSecondStyle = workbook.createCellStyle();
statisticalSecondStyle.setAlignment(CellStyle.ALIGN_LEFT);
statisticalSecondStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font statisticalFont = workbook.createFont();// 设置字体
statisticalFont.setFontHeightInPoints((short) 16);// 字体大小
statisticalSecondStyle.setFont(statisticalFont);
// 获取列数
int columnlength = Reports.size();
String safeName = WorkbookUtil.createSafeSheetName(mySheet.sheet_name);// 创建安全的工作表名
Sheet sheet = workbook.createSheet(safeName);// 创建工作表
sheet.setDefaultColumnWidth(25);// 设置列的宽度
Row row = null;
Cell cell = null;
Integer rowNumber = -1;
if (mySheet.title_name != null && !mySheet.title_name.equals("")) {
// 设置总标题
row = sheet.createRow(++rowNumber);
row.setHeightInPoints(40);
cell = row.createCell(0);
cell.setCellValue(mySheet.title_name);
cell.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));// 合并单元格
}
// 设置列标题
row = sheet.createRow(++rowNumber);
row.setHeightInPoints(30);
for (int i = 0; i < columnlength; i++) {// 权限控制
ReportArgs report = Reports.get(i);
cell = row.createCell(i);
cell.setCellValue(report.name);// 设置列名
}
List datas = mySheet.source;// 获取数据
if (datas != null && !datas.isEmpty()) {// 数据遍历
for (Object object : datas) {
row = sheet.createRow(++rowNumber);
for (int i = 0; i < columnlength; i++) {
ReportArgs report = Reports.get(i);
cell = row.createCell(i);
String data = "\t";
String field = report.field;
Object show = null;
if (field != null && !"".equals(field)) {
Field myField = mySheet.clazz.getDeclaredField(report.field);// 从属性获取数据
myField.setAccessible(true);
show = myField.get(object);
} else {
Method method = mySheet.clazz.getDeclaredMethod(report.method);// 从方法获取数据
method.setAccessible(true);
show = method.invoke(object);
}
if (show == null) {
continue;
} else if (Reports.get(i).format == ConstantsReport.FORMAT_CURRENCY) {// 对金额格式化
NumberFormat currency = NumberFormat.getCurrencyInstance(Locale.CHINA);
data += currency.format(show);
} else if (Reports.get(i).format == ConstantsReport.FORMAT_PERCENT) {// 数字百分比格式化
NumberFormat percent = NumberFormat.getPercentInstance(Locale.CHINA);
percent.setMinimumFractionDigits(4);// 保留百分比小数点后4位
data += percent.format(show);
} else {
data += show.toString();
}
cell.setCellValue(data);
cell.setCellStyle(cellStyle);
}
}
}
// 设置统计数据
if (mySheet.statistical_datas != null && !mySheet.statistical_datas.isEmpty()) {
for (String statistical_data : mySheet.statistical_datas) {
++rowNumber;// 跳过一行
// 统计数据
row = sheet.createRow(++rowNumber);
row.setHeightInPoints(30);
cell = row.createCell(1);
cell.setCellValue(statistical_data);
cell.setCellStyle(statisticalSecondStyle);
sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 1, 7));// 合并单元格
}
}
}
}
测试类
package com.zcj.poi;
public class Student {
@ReportAnnotation(name = "姓名", order = 10)
private String name;
@ReportAnnotation(name = "年龄", order = 20)
private Integer age;
@ReportAnnotation(name = "地址", order = 30)
private String address;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Student(String name, Integer age, String address) {
this.name = name;
this.age = age;
this.address = address;
}
public Student() {
}
}
package com.zcj.poi;
import java.util.ArrayList;
import java.util.List;
public class TestExcel {
public static void main(String[] args) throws Exception {
//数据
Student student1 = new Student("唐三藏",30,"大唐长安");
Student student2 = new Student("孙悟空",20,"花果山");
List<Student> students = new ArrayList<>();
students.add(student1);
students.add(student2);
//工作表
SheetArgs sheet = new SheetArgs();
sheet.sheet_name = "学生信息报表统计";
sheet.title_name = "学生信息";
sheet.clazz = Student.class;
sheet.source = students;
//excel
ExcelArgs excel = new ExcelArgs("学生信息报表统计");
excel.addSheet(sheet);
//生成excel文件
ExeclAnnotationUtils.createExcel(excel);
}
}
上一篇： 【赵强老师】Oracle的PGA与数据库连接建立的过程
下一篇： uc缓存分段视频合并