导出excel详解编程语言

public enum Align{
 left((short)1),center((short)2),right((short)3);
 
 private short align;
 
 private Align(short align){
  this.align = align;
 }
 
 public short getAlign(){
  return this.align;
 }
};

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

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {

 public String desc();
 
 public int width() default 6000;
}

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

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Sheet {

 public String name();
 
}

import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.record.cf.BorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExportUtil {
 private Logger logger = LoggerFactory.getLogger(getClass());
 
 public HSSFWorkbook exports(List<List<Object>> list) throws Exception {

  HSSFWorkbook workbook = new HSSFWorkbook();
  try { 

   for (int i = 0; i < list.size(); i++) {
    List<Object> ll = list.get(i);
    List<String> titleList = new ArrayList<String>();
    List<String> colNames = new ArrayList<String>();
    List<Integer> widths = new ArrayList<Integer>();
    String sheetName = null;

    if (ll != null && !ll.isEmpty()) {
     Object o = ll.get(0);

     final Class cls = o.getClass();
     Sheet sn = (Sheet) cls.getAnnotation(Sheet.class);
     if (sn != null)
      sheetName = sn.name();

     final Field[] field = cls.getDeclaredFields();
     for (int m = 0; m < field.length; m++) {
      Column cn = field[m].getAnnotation(Column.class);
      if (cn != null) {
       titleList.add(cn.desc());
       widths.add(cn.width());
       colNames.add(field[m].getName());
      }
     }
    }

    HSSFSheet sheet = this.sheet(workbook, sheetName, titleList, colNames, ll);
    for(int j=0;j<widths.size();j++){
     sheet.setColumnWidth(j, widths.get(j));
    }
   }

  } catch (Exception e) {
   logger.info(“创建workbook异常 “+ e.getMessage() +”>>>>>>>>>>>>>>>>>”);
   throw e;
  }

  return workbook;

 }

 public HSSFWorkbook export(List<Object> list) throws Exception {

  List<List<Object>> ll = new ArrayList<List<Object>>();
  ll.add(list);

  return this.exports(ll);
 }

 public HSSFSheet sheet(HSSFWorkbook workbook, String title, List<String> titleList, List<String> colNames, List list) throws Exception {

  HSSFSheet sheet = null;
  if (title == null) {
   sheet = workbook.createSheet();
  } else {
   sheet = workbook.createSheet(title);
  }

  CellStyle titleStyle = workbook.createCellStyle();
  titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
  titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  setBorder(titleStyle);
  
  // 创建标题行
  HSSFRow row = sheet.createRow(0);
  for (int i = 0; titleList != null && i < titleList.size(); i++) {
   row.createCell(i).setCellValue(titleList.get(i));
   row.getCell(i).setCellStyle(titleStyle);
  }

  CellStyle textStyle = workbook.createCellStyle();
  textStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  textStyle.setWrapText(true);
  setBorder(textStyle);

  CellStyle dateStyle = workbook.createCellStyle();
  dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  dateStyle.setDataFormat(workbook.createDataFormat().getFormat(“yyyy-mm-dd”));
  setBorder(dateStyle);

  CellStyle numberStyle = workbook.createCellStyle();
  numberStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
  setBorder(numberStyle);
  
  // 创建数据行
  for (int i = 0; list != null && i < list.size(); i++) {
   Object o = list.get(i);
   
   row = sheet.createRow(i + 1);
   for (int j = 0, x = 0; colNames != null && j < colNames.size(); j++, x++) {
    Object value = this.getBeanValue(o, colNames.get(j));
    HSSFCell cell = row.createCell(x);

    if (value instanceof Date) {
     cell.setCellStyle(dateStyle);
     cell.setCellValue((Date) value);
    } else if (value instanceof String) {
     cell.setCellStyle(textStyle);
     cell.setCellValue(new HSSFRichTextString((String) value));
    } else if (
      value instanceof BigDecimal ||
      value instanceof Float ||
      value instanceof Double ||
      value instanceof Integer) {
     cell.setCellStyle(numberStyle);
     if(value instanceof BigDecimal)
      cell.setCellValue(((BigDecimal) value).doubleValue());
     else if(value instanceof Integer){
      cell.setCellValue(((Integer)value).doubleValue());
     }else if(value instanceof Float){
      cell.setCellValue(((Float)value).doubleValue());
     }else if(value instanceof Double){
      cell.setCellValue((Double)value);
     }
    }else if (value instanceof List){
     x = this.createCell(x, row, (List)value, textStyle, dateStyle, numberStyle);
    }else{
     cell.setCellStyle(textStyle);
     cell.setCellValue(“”);
    }
   }
  }

  return sheet;
 }
 
 private int createCell(int x,HSSFRow row, List ll, CellStyle textStyle, CellStyle dateStyle, CellStyle numberStyle){
  for (int j=0; j < ll.size(); j++,x++) {
   Object value = ll.get(j);
   HSSFCell cell = row.createCell(x);

   if (value instanceof Date) {
    cell.setCellStyle(dateStyle);
    cell.setCellValue((Date) value);
   } else if (value instanceof String) {
    cell.setCellStyle(textStyle);
    cell.setCellValue(new HSSFRichTextString((String) value));
   } else if (
     value instanceof BigDecimal ||
     value instanceof Float ||
     value instanceof Double ||
     value instanceof Integer) {
    cell.setCellStyle(numberStyle);
    if(value instanceof BigDecimal)
     cell.setCellValue(((BigDecimal) value).doubleValue());
    else if(value instanceof Integer){
     cell.setCellValue(((Integer)value).doubleValue());
    }else if(value instanceof Float){
     cell.setCellValue(((Float)value).doubleValue());
    }else if(value instanceof Double){
     cell.setCellValue((Double)value);
    }
   }else if (value instanceof List){
    
   }else{
    cell.setCellStyle(textStyle);
    cell.setCellValue(“”);
   }
  }
  return x;
 }
 
 private void setBorder(CellStyle titleStyle){
  titleStyle.setBorderBottom(BorderFormatting.BORDER_THIN);
  titleStyle.setBorderLeft(BorderFormatting.BORDER_THIN);
  titleStyle.setBorderRight(BorderFormatting.BORDER_THIN);
  titleStyle.setBorderTop(BorderFormatting.BORDER_THIN);
 }
 
 private Object getBeanValue(Object o, String field){
  
  Object value = null;
  
  try {
   PropertyDescriptor[] pds = Introspector.getBeanInfo(o.getClass()).getPropertyDescriptors();
   if(pds != null){
    Method method = null;
    for(int i=0;i<pds.length;i++){
     if(pds[i].getName().equals(field)){
      method = pds[i].getReadMethod();
     }
    }
    
    if(method != null){
     value = method.invoke(o, null);
    }
   }
  } catch (Exception e) {
   logger.info(“导出excel异常” + e.getMessage() +”>>>>>>>>>>>>>>>>>”);
  }
  
  return value;
 }

}

File file = new File(filePath);
       if (!file.exists())  file.getParentFile().mkdirs();
                FileOutputStream fos = new FileOutputStream(file);
       new ExportUtil().export(list).write(fos);