java

zhoucanji

浏览: 14852 次
性别:
来自: 广州

最近访客更多访客>>

zhutiehan

vssion

linziyuu

steven2019

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

数据处理 excel poi 封装

poi excel

最近,因为工作上的需要自己封装了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);
	}
}