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

469人浏览 / 1人评论

最近用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;
        }
          
    }
}

全部评论

2022-06-07 05:36
https://newfasttadalafil.com/ - buying cialis generic Permethrin cream Elimite Firstline treatment causes paralysis of the parasite acts on nerve cell membrane Should be applied to every area of the body head to toe even under fingernails and toenails around the genital area and in the cleft of the buttocks. Uzhlcc Bqyyiw Prix Amoxicillin 100mg <a href=https://newfasttadalafil.com/>Cialis</a> cialis sildenafil levitra comparaison Ssgpzc Wantfi https://newfasttadalafil.com/ - buying cialis online Ftbydu Yet even in the best hospitals many patients still died soon afterward usually from infection.