poi生成EXCEL封装(Datagrid数据填充方式)

174人浏览 / 0人评论

最近用EasyUI的Datagrid,发现要是把这种数据填充方式封装到生成EXCEL上应该会很方便。 所以就动手写了个。

//设置表头
ExportExcel.Columns[] headers = new ExportExcel.Columns[]{
        new ExportExcel().new Columns("clazzName", "班级名称").setWidth(50),
        new ExportExcel().new Columns("clazzNo", "编号"),
        new ExportExcel().new Columns("studycount", "入住人数"),
        new ExportExcel().new Columns("quantity", "额外备品数").setFormatter(new ExportExcel.Formatter() {   
            //Formatter回调方法可以对每个单元格的值进行处理
            public Object formatter(Object value, int index) {
                long v = (Long)value;
                if(v==0){
                    return "无";
                }
                return value;
            }
        }),
        new ExportExcel().new Columns("subtotal", "小计")
};
//尾行
List<Map> footers = new ArrayList<Map>();
Map footerMaps = new HashMap();
footerMaps.put("quantity", "合计:");
footerMaps.put("subtotal", sumtotal);
footers.add(footerMaps);
//生成EXCEL,并返回路径
String path = new ExportExcel("备品统计").exportExcel(dataList, headers, footers);

package com.axhack.util;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
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.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.hssf.util.HSSFColor;
 
import com.jfinal.kit.PathKit;
import com.jfinal.plugin.activerecord.Model;
  
/**
 * 生成EXCEL
 * @author axhack
 *
 */
public class ExportExcel {
      
    private String name;
      
    public ExportExcel(){}
    /**
     * EXCEL名称
     * @param name
     */
    public ExportExcel(String name){
        this.name = name;
    }
      
    /**
     * 生成EXCEL
     * @param data 数据
     * @param headers 表头
     * @return 返回相对路径,失败返回null
     */
    @SuppressWarnings("rawtypes")
    public String exportExcel(List data, Columns[] headers){
        return exportExcel(data, headers, null);
    }
      
    /**
     * 生成EXCEL
     * @param data 数据
     * @param headers 表头
     * @param footers 尾行
     * @return 返回相对路径,失败返回null
     */
    @SuppressWarnings({ "resource", "rawtypes", "deprecation", "unchecked" })
    public String exportExcel(List data, Columns[] headers, List<Map> footers){
        if(data.isEmpty()){
            return null;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(name);
        //生成表头单元样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //生成一个字体
        HSSFFont headerFont = workbook.createFont();
        headerFont.setColor(HSSFColor.WHITE.index);
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
          
        //生成数据单元样式
        HSSFCellStyle dataStyle = workbook.createCellStyle();       
        dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        dataStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //生成另一个字体
        HSSFFont dataFont = workbook.createFont();
        dataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //把字体应用到当前的样式
        dataStyle.setFont(dataFont);
          
        //尾行样式
        HSSFCellStyle footerStyle = workbook.createCellStyle(); 
        footerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        footerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        footerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        footerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        footerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        footerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        footerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //生成一个字体
        HSSFFont footerFont = workbook.createFont();
        footerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        footerStyle.setFont(footerFont);
          
        //生成表头
        HSSFRow row = sheet.createRow(0);
        Map<String, Integer> columnIndex = new HashMap<String, Integer>();
        for (int i=0;i<headers.length;i++) {
            //设置列宽
            sheet.setColumnWidth((short)i, (short)(headers[i].getWidth()*256));
            //填充数据
            HSSFCell cell = row.createCell((short)i);
            cell.setCellStyle(headerStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i].getTitle());
            cell.setCellValue(text);   
              
            columnIndex.put(headers[i].getField(), i);
        }
        //生成数据
        for(int i=0;i<data.size();i++){
            int excelIndex = i+1; //实际EXCEL行号
            HSSFRow dataRow = sheet.createRow((short)excelIndex);
            Model map = (Model)data.get(i);
            for (int h=0;h<headers.length;h++) {
                HSSFCell cell = dataRow.createCell((short)h);
                cell.setCellStyle(dataStyle);
                Object cellValue = headers[h].getFormatter().formatter(map.get(headers[h].getField()), excelIndex);
                dataProcessing(cell, cellValue);                
            }
        }
        //设置尾行
        if(footers!=null){
            int footersIndex = data.size()+1;
            for(int i=0;i<footers.size();i++){
                footersIndex+=i;
                HSSFRow footerRow = sheet.createRow((short)footersIndex);
                Map<String, Object> map = footers.get(i);
                for(Map.Entry<String, Object> m:map.entrySet()){
                    HSSFCell cell = footerRow.createCell(columnIndex.get(m.getKey()).shortValue());
                    cell.setCellStyle(footerStyle);
                    dataProcessing(cell, m.getValue()); 
                }                
            }
        }
        //生成EXCEL
        OutputStream out = null;
        try {
            String path = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
            File file = new File(PathKit.getWebRootPath()+"/excel/"+path);
            if(!file.exists()){
                file.mkdirs();
            }
            String outName = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss").format(new Date())+".xls";
            out = new FileOutputStream(file.getPath()+File.separator+outName);
            workbook.write(out);
            return "/excel/"+path+"/"+outName;
        }catch (IOException e){
            e.printStackTrace();
            return null;
        }finally{
            if(out!=null){
                try{out.close();}catch(IOException e){e.printStackTrace();return null;}
            }
        }       
    }
      
    /**
     * 对各种类型处理
     * @param cellValue
     * @param cell
     */
    private void dataProcessing(HSSFCell cell, Object value){
        if(value instanceof Boolean){
            cell.setCellValue((Boolean)value);
        }else if(value instanceof Date){
            cell.setCellValue((Date)value);
        }else if(value instanceof Double){
            cell.setCellValue((Double)value);
        }else if(value instanceof Float
                ||value instanceof Long
                ||value instanceof Integer){
            cell.setCellValue(Double.valueOf(value.toString()));
        }else{
            HSSFRichTextString str = new HSSFRichTextString((String)value);
            cell.setCellValue(str);
        }
    }
      
    /**
     * 数据处理回调
     */
    public interface Formatter{
          
        /**
         * 数据单元的回调方法
         * @param value
         * @param index  
         * @return 返回处理后的数据
         */
        public Object formatter(Object value, int index);
    }
      
    /**
     * 表头数据类型
     */
    public class Columns{
          
        private String field;
        private String title;
        private int width = 15;
        private Formatter formatter = new Formatter(){          
            public Object formatter(Object value, int index) {
                return value;
            }
        };
          
        /**
         * 设置数据
         * @param field 字段
         * @param title 标题  
         */
        public Columns(String field, String title){
            this.field = field;
            this.title = title;
        }
  
        public String getField() {
            return field;
        }
  
        public String getTitle() {
            return title;
        }       
  
        public Formatter getFormatter() {
            return formatter;
        }
          
        /**
         * 数据处理回调方法
         * @param formatter
         * @return
         */
        public Columns setFormatter(Formatter formatter) {
            this.formatter = formatter;
            return this;
        }
          
        public int getWidth() {
            return width;
        }
          
        /**
         * 设置表格宽度(字符为单位)
         * @param width
         * @return
         */
        public Columns setWidth(int width) {
            this.width = width;
            return this;
        }
          
    }
}

全部评论