基于注解的全自动POI导出excel
/**
?* @Application name:行政区划信息管理系统
?* @Copyright:Copyright 2011 北京人和创建信息技术
?* @Company:RHCJ
?*/
package com.renhesoft.business.datasearch.countsearch.action;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
/**
?* @ClassName: FileUtil.java
?* @Description: 文档处理类
?* @Author 李晓浩
?* @Date 2013-6-14
?* @Version V1.0（版本）
?*/
public class FileInfoUtil {
?/**
? * 1: 属性存放在list当中循环读取 ，内容是Map对象。 map对象中英文名称对应字段，之对应要到出的
? * ?? Excel中的中文
? * 2：新建excel导出Model
? *???
? * 2:
? */
?private String excelName = null; // 文件名称
?
?private String excelPath = null ; //文件路径
?
?private Class<?> obj = null ; // VO类
?
?private HSSFWorkbook xssfWorkbook = null;
?
?private HSSFSheet ? xssfSheet = null ;
?
?private String?? num = "";? //
?
?private List<?> modelLs ; // model列表内容类
?
?private int booMerge = 0; //表头合并行数
?
?private List<ArrayList<String>> mergeRule ;
?
?
?/**
? * @author: 李晓浩
? * @param excelName 导出的Excel文件名称
? * @param excelPath 导出的Excel路径,如果路径为空,则直接数据流
? * @param sheetName 导出的Excel中sheet的名称
? * @param titleLs?? 导出的Excle中表头列表,存放map,map键对应Model中的字段,值对应Excel中的中文字符
? * @param modelLs?? 导出的Excel中内容
? * @param obj??导出的Excel中Model的表识
? */
?public FileInfoUtil(String excelName , String excelPath ,String sheetName ,List<?> modelLs,Class<?> obj){
??this.excelName = excelName ;
??this.excelPath = excelPath ;
??this.obj ??? = obj ;
??this.modelLs?? = modelLs;
??xssfWorkbook?? = new HSSFWorkbook();
??xssfSheet????? = xssfWorkbook.createSheet(sheetName);
?}
?/**
? * @author: 李晓浩
? * @param excelName 导出的Excel文件名称
? * @param excelPath 导出的Excel路径,如果路径为空,则直接数据流
? * @param sheetName 导出的Excel中sheet的名称
? * @param titleLs?? 导出的Excle中表头列表,存放map,map键对应Model中的字段,值对应Excel中的中文字符
? * @param modelLs?? 导出的Excel中内容
? * @param obj??导出的Excel中Model的表识
? * @param booMerge? 导出的Excel中表头合并行数
? */
?public FileInfoUtil(String excelName , String excelPath ,String sheetName ,List<?> modelLs,Class<?> obj,int titleBooMerge ,List<ArrayList<String>> mergeRule){
??xssfWorkbook?? = new HSSFWorkbook();
??xssfSheet????? = xssfWorkbook.createSheet(sheetName);
??this.excelName = excelName ;
??this.excelPath = excelPath ;
??this.obj ??? = obj ;
??this.modelLs?? = modelLs;
??this.booMerge? = titleBooMerge;
??this.mergeRule = mergeRule ;
?}
?/**
? * @author: 李晓浩
? * @param sheetName 导出的Excel中sheet的名称
? * @param modelLs?? 导出的Excel中内容
? * @param obj??导出的Excel中Model的表识
? * @param num?????? 很成代码的位数
? */
?public FileInfoUtil(String sheetName ,List<?> modelLs,Class<?> obj , String Num){
??this.obj ??? = obj ;
??this.modelLs?? = modelLs;
??this.num ??? = Num ;
??xssfWorkbook?? = new HSSFWorkbook();
??xssfSheet????? = xssfWorkbook.createSheet(sheetName);
?}
?/**
? * @author: 李晓浩
? * @Title: exportExcel2007
? * @time : 2013-6-14上午09:41:37
? * @Description: 导出Excel到指定文件中
? */
?public void exportExcel2007(){
??File f = new File(excelPath+"\"+this.excelName+".xlsx");
??try {
???FileOutputStream fos = new FileOutputStream(f);
???try {
????this.productTitle();
????this.loadExcelContents();
????xssfWorkbook.write(fos);
???} catch (IOException e) {
????e.printStackTrace();
???}
??} catch (FileNotFoundException e) {
???e.printStackTrace();
??}
?}
?/**
? * @author: 李晓浩
? * @Title: exportExcel2007
? * @time : 2013-6-14上午09:41:37
? * @Description: 导出联合码Excel2007
? */
?public ByteArrayInputStream exportUnionExcel2007(){
??ByteArrayInputStream bas? = null ;
??try {
???ByteArrayOutputStream bos = new ByteArrayOutputStream();
???try {
????this.productTitle();
????this.loadExcelContents();
????xssfWorkbook.write(bos);
????bos.flush();
????bos.close();
????byte[] btes? = bos.toByteArray();
????bas = new ByteArrayInputStream(btes);
???} catch (IOException e) {
????// TODO Auto-generated catch block
????e.printStackTrace();
???}
??} catch (Exception e) {
???e.printStackTrace();
??}
??return bas;
?}
?/**
? * @author: 李晓浩
? * @Title: printExcel2007
? * @time : 2013-6-14下午09:32:34
? * @Description: 返回Excel流文件
? */
?public ByteArrayInputStream printExcel2007(){
??ByteArrayInputStream bas? = null ;
??try {
???ByteArrayOutputStream bos = new ByteArrayOutputStream();
???try {
????this.productTitle();
????this.loadExcelContents();
????xssfWorkbook.write(bos);
????bos.flush();
????bos.close();
????byte[] btes? = bos.toByteArray();
????bas = new ByteArrayInputStream(btes);
???} catch (IOException e) {
????// TODO Auto-generated catch block
????e.printStackTrace();
???}
??} catch (Exception e) {
???e.printStackTrace();
??}
??return bas;
?}
?/**
? * @author: 李晓浩
? * @Title: specialProductTitle
? * @time : 2013-6-28上午10:38:51
? * @Description: 表头合并处理,暂时考虑到两行中多列的处理
? */
?public void specialProductTitle(List<ArrayList<String>> mergeRule){
??HSSFCellStyle style = this.getExcelTitleStyle();
??int mergeRow = 0;
??HSSFRow x1 = null ;
??for(ArrayList<String> tt : mergeRule){
???CellRangeAddress? cra = new CellRangeAddress(Integer.parseInt(tt.get(0)),Integer.parseInt(tt.get(1)),Integer.parseInt(tt.get(2)),Integer.parseInt(tt.get(3)));
???xssfSheet.addMergedRegion(cra);
???if(mergeRow == 0 ){
????x1 =? xssfSheet.createRow(Integer.parseInt(tt.get(0)));
???}
???HSSFCell sc = x1.createCell(Integer.parseInt(tt.get(2)));
???System.out.println(tt.get(4)+"==================");
???sc.setCellValue(tt.get(4));
???sc.setCellStyle(style);
???mergeRow ++;
??}
?}
?/**
? * @author: 李晓浩
? * @Title: productTitle
? * @time : 2013-6-14上午10:01:35
? * @param: 判断联合查询的一个参数,如果联合查询,此参数则不能为空
? * @Description: 生成Excel表头
? */
?public void? productTitle(){
??HSSFRow xssfRow? = null;
??for(int i = 0 ; i < booMerge ; i++){
???xssfRow?= xssfSheet.createRow(i);
??}
??if(null == mergeRule || mergeRule.size() == 0){//是否进行合并处理
???
??}else{
???this.specialProductTitle(mergeRule);
??}
??HSSFCellStyle style = this.getExcelTitleStyle();
??boolean booComm = (boolean)obj.isAnnotationPresent(ClassAnnotation.class);
??if(booComm){
???ClassAnnotation claAna??? ?=?? (ClassAnnotation)obj.getAnnotation(ClassAnnotation.class);
???if(claAna.funKind()&&(num!=null&&!num.equals(""))){
????// 相同属性需要拼接的情况下
????Field[] fields = obj.getDeclaredFields();
????int i = 0 ;
????for(Field? fiett : fields){
?????boolean? booCommA= (boolean)fiett.isAnnotationPresent(PropertyAnnotation.class);
?????if(booCommA){
??????PropertyAnnotation proAna??? ?=?? (PropertyAnnotation)fiett.getAnnotation(PropertyAnnotation.class);
??????if(proAna.booTitle() == 1 && proAna.funType().equals("1")){
????????? String propertyInfoValue ?=?? proAna.titleName(); //中文标题
????????? HSSFCell xssfCell ???=?? xssfRow.createCell(i);
???????? ?xssfCell.setCellValue(propertyInfoValue);
???????? ?//设置每列的宽度
???????? ?xssfSheet.setColumnWidth(i, proAna.columnWidth()*256);
???????? ?//设置表格样式
???????? ?xssfCell.setCellStyle(style);
???????? ?i++;
??????}
?????}
????}
???}else{
????Field[] fields = obj.getDeclaredFields();
????int i = 0 ;
????for(Field? fiett : fields){
?????boolean? booCommA= (boolean)fiett.isAnnotationPresent(PropertyAnnotation.class);
?????if(booCommA){
??????PropertyAnnotation proAna??? ?=?? (PropertyAnnotation)fiett.getAnnotation(PropertyAnnotation.class);
??????if(proAna.booTitle() == 1&&proAna.reduceTitle() == 1){
????????? String propertyInfoValue ?=?? proAna.titleName(); //中文标题
????????? HSSFCell xssfCell ???=?? xssfRow.createCell(i);
???????? ?xssfCell.setCellValue(propertyInfoValue);
????????? //设置每列的宽度
???????? ?xssfSheet.setColumnWidth(i, proAna.columnWidth()*256);
????????? //设置表格样式
???????? ?xssfCell.setCellStyle(style);
???????i++;
??????}
?????}
????}
???}
??}else{
???try {
????throw new Exception("export error.....");
???} catch (Exception e) {
????e.printStackTrace();
???}
??}
?}
?/**
? * @author: 李晓浩
? * @Title: loadExcelContents
? * @time : 2013-6-14上午11:36:09
? * @Description: 加载model内容到Excel
? */
?public? void loadExcelContents(){
??HSSFCellStyle hcs = getExcelContentStyle();
??for(int i = 0 ; i < modelLs.size() ; i++){
???HSSFRow? xssfRow = xssfSheet.createRow(i+booMerge);
???Field[] fields = obj.getDeclaredFields();
???String propertyInfoValue="";
???String propertyInfoValuea="";
???int j = 0 ;
???int b = 0 ;
???for(Field? fiett : fields){
????boolean booComm = (boolean)fiett.isAnnotationPresent(PropertyAnnotation.class);
????if(booComm){
?????PropertyAnnotation proAna??? ?=?? (PropertyAnnotation)fiett.getAnnotation(PropertyAnnotation.class);
?????if(num!=null&&!num.equals("")){ //
??????if(proAna.booTitle() == 1 && proAna.funType().equals("2")){
???????try {
????????propertyInfoValue = propertyInfoValue+? (String)fiett.get(modelLs.get(i));
????????j++;
???????} catch (IllegalArgumentException e1) {
????????e1.printStackTrace();
???????} catch (IllegalAccessException e1) {
????????e1.printStackTrace();
???????}
???????if(Integer.parseInt(num) == j){
????????try {
?????????HSSFCell xssfCell =?? xssfRow.createCell(0);
?????????xssfCell.setCellValue(propertyInfoValue);
?????????xssfCell.setCellStyle(hcs);
?????????b++;
????????} catch (IllegalArgumentException e) {
?????????e.printStackTrace();
????????}
???????}
???????
??????}
??????if(proAna.booTitle() == 1 && proAna.funType().equals("1") && proAna.loadContents().equals("1")){
????????try {
?????????propertyInfoValuea = (String)fiett.get(modelLs.get(i));
????????} catch (IllegalArgumentException e1) {
?????????e1.printStackTrace();
????????} catch (IllegalAccessException e1) {
?????????e1.printStackTrace();
????????}
????????try {
?????????HSSFCell xssfCell =?? xssfRow.createCell(b);
?????????xssfCell.setCellValue(propertyInfoValuea);
?????????xssfCell.setCellStyle(hcs);
?????????b++;
????????} catch (IllegalArgumentException e) {
?????????e.printStackTrace();
????????}
??????}
?????}else{
??????if(proAna.booTitle() == 1 && proAna.reduceTitle() == 1){
???????try {
????????propertyInfoValue =? (String)(fiett.get(modelLs.get(i))+"");
????????HSSFCell xssfCell =?? xssfRow.createCell(j);
????????xssfCell.setCellValue(propertyInfoValue);
????????xssfCell.setCellStyle(hcs);
???????} catch (IllegalArgumentException e) {
????????e.printStackTrace();
???????} catch (IllegalAccessException e) {
????????e.printStackTrace();
???????}
???????j++;
??????}
?????}
????}
???}
??}
?}
?/**
? * @author: 李晓浩
? * @Title: getExcelTitleStyle
? * @time : 2013-6-15下午05:52:23
? * @Description: 设置标题样式
? */
?public HSSFCellStyle getExcelTitleStyle(){
??HSSFCellStyle style = xssfWorkbook.createCellStyle();?
??????? style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);?
??????? style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
??????? style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
??????? style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
??????? style.setBorderRight(HSSFCellStyle.BORDER_THIN);
??????? style.setBorderTop(HSSFCellStyle.BORDER_THIN);
??????? style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
??????? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
??????? HSSFFont font=xssfWorkbook.createFont();
??????? font.setFontHeightInPoints((short)11);
??????? font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
??????? style.setFont(font);
??return style ;
?}
?/**
? * @author: 李晓浩
? * @Title: getExcelContentStyle
? * @time : 2013-6-15下午05:52:07
? * @Description:设置内容样式
? */
?public HSSFCellStyle getExcelContentStyle(){
??HSSFCellStyle style = xssfWorkbook.createCellStyle();?
??????? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
??????? style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
??????? style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
??????? return style ;
?}
}
