导出Excel工具类

时间:2023-02-15 19:42:57

 

 

package com.runxsoft.card.utils.excel.core;



import org.apache.log4j.Logger;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

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.xssf.usermodel.XSSFWorkbook;



import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.math.BigDecimal;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;



import com.runxsoft.card.utils.excel.constant.ExcelType;

import com.runxsoft.card.utils.excel.util.BeanUtils;



/**

 * 工具类入口

 * @author Niu Li

 * @since 2017/2/23

 */

public class ExcelFormat {



    private static Logger logger = Logger.getLogger(ExcelFormat.class);

    //该表格的工作本

    private Workbook workbook;



    /**

     * 控制表头,其中键为对应DTO的字段,值为表头显示内容

     */

    private LinkedHashMap<String,String> headers;

    /**

     * 具体表内容,只接受DTO

     */

    private List<?> contents;



    /**

     * 入口函数

     * @param headers 表单头部

     * @param content 表单内容DTO

     * @return this表单对象

     */

    public static ExcelFormat from(LinkedHashMap<String,String> headers,List<?> content){

        return new ExcelFormat(headers,content);

    }



    /**

     * 在此workbook中增加另一个sheet

     * @param headers 新sheet的表头

     * @param content 新sheet的内容

     * @return this

     */

    public ExcelFormat andForm(LinkedHashMap<String,String> headers,List<?> content){

        this.headers = headers;

        this.contents = content;

        return this;

    }





    /**

     * 端点方法,生成最终的表单

     * @return this

     */

    public ExcelFormat build(String sheetName){

        //创建字表

        Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);

        //创建表头

        int rowNum = 0;

        Row headerRow = sheet.createRow(rowNum++);

        List<String> headers = new ArrayList<>(this.headers.keySet());//表头

        List<String> values = new ArrayList<>(this.headers.values());//对应值

        for (int i = 0; i < headers.size(); i++) {

            Cell cell = headerRow.createCell(i);

            cell.setCellValue(values.get(i)==null?headers.get(i):values.get(i));

        }

        //构造表单内容

        try {

            for (Object content : contents) {

                Map<String,Object> contentMap = null;

                if(content instanceof Map){

                    contentMap = (Map<String, Object>) content;

                }else{

                    contentMap = BeanUtils.bean2Map(content);

                }

                Row current = sheet.createRow(rowNum++);

                for (int i = 0; i < headers.size(); i++) {

                    Cell cell = current.createCell(i);

                    Object obj = contentMap.get(headers.get(i));

                    if (obj == null) {

                        obj = "";

                    }

                    if(headers.get(i).equals("status")){

                        if(obj.equals("3")){

                            obj = "已销售";

                        }else if(obj.equals("1")){

                            obj = "已入库";

                        }else if(obj.equals("2")){

                            obj = "已发放";

                        }else if(obj.equals("5")){

                            obj = "已回收";

                        }else if(obj.equals("8")){

                            obj = "已进行过期处理";

                        }else if(obj.equals("6")){

                            obj = "已作废";

                        }else{

                            obj = "";

                        }

                    }

                    if (obj instanceof String) {

                        cell.setCellValue((String) obj);

                    } else if (obj instanceof Integer) {

                        cell.setCellValue((Integer) obj);

                    } else if (obj instanceof Float) {

                        cell.setCellValue((Float) obj);

                    } else if (obj instanceof Long) {

                        cell.setCellValue((Long) obj);

                    } else if (obj instanceof Double) {

                        cell.setCellValue((Double) obj);

                    } else if (obj instanceof BigDecimal) {

                         cell.setCellValue(((BigDecimal) obj).doubleValue());

                    } else if (obj instanceof Date) {

                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                        cell.setCellValue(formatter.format(obj));

                    } else if (obj instanceof Boolean) {

                        cell.setCellValue((Boolean) obj);

                    } else {

                        logger.info("异常,导出单元格字段类型不匹配:"+obj.getClass().getName());

                        throw new IllegalArgumentException("unsupported cell type");

                    }

                }

            }

            

            Row total = sheet.createRow(rowNum++);

        //    total.createCell(0).setCellValue("总计");

        } catch (IllegalAccessException e) {

            logger.error("parse excel fail ",e);

        }

        //设置样式



        return this;

    }

    /**

     * 私有化构造函数

     */

    private ExcelFormat(LinkedHashMap<String, String> headers, List<?> contents) {

        this.headers = headers;

        this.contents = contents;

    }



    /**

     * 调用该方法后,此workbook则写入关闭

     * @param dirIncludedFileName 最终生成文件名称

     */

    public void write(String dirIncludedFileName){

        try {

            File file = new File(dirIncludedFileName);

            FileOutputStream os = new FileOutputStream(file);

            workbook.write(os);

            os.close();

        } catch (IOException e) {

            logger.error("write excel fail ",e);

        }finally {

            try {

                if (workbook != null) workbook.close();

            } catch (IOException e) {

                logger.error("write excel fail ",e);

            }

        }

    }



    /**

     * 结果写到一个输出流中

     * @param os 目标流

     */

    public void write(OutputStream os) {

        try {

            workbook.write(os);

            workbook.close();

        } catch (IOException e) {

            logger.error("write excel fail ",e);

        }

    }





    /**

     * 链式调用设置生成文档格式

     * @param type 指定格式

     * @return this

     */

    public ExcelFormat excelType(ExcelType type){

        workbook = type == ExcelType.XLS?new HSSFWorkbook():new XSSFWorkbook();

        return this;

    }


}