poi导出数据为一个excel表

<properties>
        <poi.version>3.17</poi.version>
    </properties>
<!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>

 导出数据需要一个模板表格,可以使用占位符动态的添加值{0}这就是占位符

工具类,需要时可以自己修改里面的代码. 

package com.buba.utils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class TemplateExcelUtil<T> {

    /**
     * 导出excel
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出
     * temp         模板路径
     * target       存储目标地址
     * params       表头里的参数数组
     * headersId    表格属性列名对应的javaBean字段---你需要导出的字段名（为了更灵活控制你想要导出的字段）
     *  dtoList     需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象
     * 返回响应体
     */
    public  void exportExcel(String temp,String target, String[] params,List<String> headersId,
                            List<T> dtoList) {
    	
        /*（二）字段*/
        Map<Integer, String> titleFieldMap = new HashMap<>();
        int value = 0;
        for (int i = 0; i < headersId.size(); i++) {
            if (!headersId.get(i).equals(null)) {
                titleFieldMap.put(value, headersId.get(i));
                value++;
            }
        }
        
        /* （三）读取模板*/
        XSSFWorkbook wb = null;
        File file = new File(temp);
        try {  
              wb = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(file));        
            } catch (FileNotFoundException e) {  
              e.printStackTrace();  
            } catch (InvalidFormatException e) {  
              e.printStackTrace();  
            } catch (IOException e) {  
              e.printStackTrace();  
            }  

        /** 得到第一个sheet */
        XSSFSheet sheet = wb.getSheetAt(0);
        sheet.setDefaultColumnWidth((short)20); //设置默认表格宽度

        //补充标题头的参数   下面的代码就是获取占位符所在位置,这个得自己根据自己模板占位符的位置修改下面的参数值
        XSSFRow row0 = sheet.getRow(0);
        XSSFCell cell00 = row0.getCell(0);
        String string00 = MessageFormat.format(cell00.getStringCellValue(),params);
        cell00.setCellValue(string00);


        //补充标题头的参数
        XSSFRow row1 = sheet.getRow(1);
        XSSFCell cell11 = row1.getCell(1);
        String string11 = MessageFormat.format(cell11.getStringCellValue(), params);
        cell11.setCellValue(string11);
        
        XSSFRow row2 = sheet.getRow(2);
        XSSFCell cell20 = row2.getCell(0);
        String string20 = MessageFormat.format(cell20.getStringCellValue(), params);
        cell20.setCellValue(string20);
        
        //得到表格的样式
        XSSFCellStyle cellStyle = sheet.getRow(4).getCell(0).getCellStyle();
        
        XSSFRow row=null;
        XSSFCell cell=null;
        //表格标题一行的字段的集合
        Collection zdC = titleFieldMap.values();
        Iterator<T> labIt = dtoList.iterator();//总记录的迭代器
        int zdRow =3;//列序号
        while (labIt.hasNext()) {//记录的迭代器，遍历总记录
            //int zdCell = 0;
            zdRow++;
            row = sheet.createRow(zdRow);
            T l = (T) labIt.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = l.getClass().getDeclaredFields();//获得JavaBean全部属性
            for (short i = 0; i < fields.length; i++) {//遍历属性，比对
                int zdCell = 0;
                Field field = fields[i];
                String fieldName = field.getName();//属性名
                Iterator<String> zdIt = zdC.iterator();//一条字段的集合的迭代器
                while (zdIt.hasNext()) {//遍历要导出的字段集合
                    if (zdIt.next().equals(fieldName)) {//比对JavaBean的属性名，一致就写入，不一致就丢弃
                        String getMethodName = "get"
                                + fieldName.substring(0, 1).toUpperCase()
                                + fieldName.substring(1);//拿到属性的get方法
                        Class tCls = l.getClass();//拿到JavaBean对象
                        try {
                            Method getMethod = tCls.getMethod(getMethodName,
                                    new Class[] {});//通过JavaBean对象拿到该属性的get方法，从而进行操控
                            Object val = getMethod.invoke(l, new Object[] {});//操控该对象属性的get方法，从而拿到属性值
                            Class t = getMethod.getReturnType();
                            cell = row.createCell((short) zdCell);
                            cell.setCellStyle(cellStyle);//单元格样式
                            //根据值的类型设置单元格的值
                            switch (t.getName()) {
							case "java.lang.Integer":
							    if(null!=val){
							    	cell.setCellValue((Integer)val);
							    }
							    break;
							case "java.util.Date":
								if(null!=val){
									Date date=(Date)val;
									SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
								    cell.setCellValue(sdf.format(date));
								}
							    break;
							default:
								cell.setCellValue(String.valueOf(val));
								break;
							}
                            //zdCell++;
                        } catch (SecurityException e) {
                            e.printStackTrace();
                        } catch (IllegalArgumentException e) {
                            e.printStackTrace();
                        } catch (NoSuchMethodException e) {
                            e.printStackTrace();
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        } catch (InvocationTargetException e) {
                            e.printStackTrace();
                        }
                    }
                    zdCell++;
                }
            }
        }
      //4.存储文件
        try {
			FileOutputStream out=new FileOutputStream(target);
		    wb.write(out);
		    out.flush();
		    out.close();
        } catch (FileNotFoundException e) {

           e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }

    /**
     * Excel读取 操作
     */
    public static List<List<String>> readExcel(InputStream is)
            throws IOException {
        Workbook wb = null;
        try {  
              wb = WorkbookFactory.create(is);        
            } catch (FileNotFoundException e) {  
              e.printStackTrace();  
            } catch (InvalidFormatException e) {  
              e.printStackTrace();  
            } catch (IOException e) {  
              e.printStackTrace();  
            }  

        /** 得到第一个sheet */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();

        /** 得到Excel的列数 */
        int totalCells = 0;
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        List<List<String>> dataLst = new ArrayList<List<String>>();
        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                     /*HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                     cellValue= hSSFDataFormatter.formatCellValue(cell);*/

                   // 以下是判断数据的类型
                	CellType type = cell.getCellTypeEnum();

                    switch (type) {
                    case NUMERIC: // 数字
                        cellValue = cell.getNumericCellValue() + "";
                        break;
                    case STRING: // 字符串
                        cellValue = cell.getStringCellValue();
                        break;
                    case BOOLEAN: // Boolean
                        cellValue = cell.getBooleanCellValue() + "";
                        break;
                    case FORMULA: // 公式
                        cellValue = cell.getCellFormula() + "";
                        break;
                    case BLANK: // 空值
                        cellValue = "";
                        break;
                    case _NONE: // 故障
                        cellValue = "非法字符";
                        break;
                    default:
                        cellValue = "未知类型";
                        break;
                    }
                }
                rowLst.add(cellValue);
            }
            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }
        return dataLst;
    }

}

 实体类

package com.buba.pojo;

import com.buba.utils.ExcellFiled;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;


public class ItemData {
    private String organizeName;//机构名称
    @DateTimeFormat(pattern="yyyy-MM-dd")
    private Date createtime;//机构id号码
    private Integer organizeId;//机构id号码
	private Integer numActivities;//计划召开桌数
    private Integer numActual;//实际召开桌数
    private Float implRate;//执行率
    private Integer implRanking;//执行排名
    private Integer newClient;//新客户量
    private Integer oldClient;//老客户量
    private Integer yxClient;//意向客户
    private Float money;//预估保费
	public String getOrganizeName() {
		return organizeName;
	}
	public void setOrganizeName(String organizeName) {
		this.organizeName = organizeName;
	}
	public Integer getOrganizeId() {
		return organizeId;
	}
	public void setOrganizeId(Integer organizeId) {
		this.organizeId = organizeId;
	}
	public Integer getNumActivities() {
		return numActivities;
	}
	public void setNumActivities(Integer numActivities) {
		this.numActivities = numActivities;
	}
	public Integer getNumActual() {
		return numActual;
	}
	public void setNumActual(Integer numActual) {
		this.numActual = numActual;
	}
	public Float getImplRate() {
		return  implRate;
	}
	public void setImplRate(Float implRate) {
		this.implRate = implRate;
	}
	public Integer getImplRanking() {
		return implRanking;
	}
	public void setImplRanking(Integer implRanking) {
		this.implRanking = implRanking;
	}
	public Integer getNewClient() {
		return newClient;
	}
	public void setNewClient(Integer newClient) {
		this.newClient = newClient;
	}
	public Integer getOldClient() {
		return oldClient;
	}
	public void setOldClient(Integer oldClient) {
		this.oldClient = oldClient;
	}
	public Integer getYxClient() {
		return yxClient;
	}
	public void setYxClient(Integer yxClient) {
		this.yxClient = yxClient;
	}
	public Float getMoney() {
		return money;
	}
	public void setMoney(Float money) {
		this.money = money;
	}
	public Date getCreatetime() {
		return createtime;
	}
	public void setCreatetime(Date createtime) {
		this.createtime = createtime;
	}

	public ItemData() {

	}

	public ItemData(String organizeName, Date createtime, Integer organizeId, Integer numActivities, Integer numActual, Float implRate, Integer implRanking, Integer newClient, Integer oldClient, Integer yxClient, Float money) {
		this.organizeName = organizeName;
		this.createtime = createtime;
		this.organizeId = organizeId;
		this.numActivities = numActivities;
		this.numActual = numActual;
		this.implRate = implRate;
		this.implRanking = implRanking;
		this.newClient = newClient;
		this.oldClient = oldClient;
		this.yxClient = yxClient;
		this.money = money;
	}


}

导出的controller ,自己打断点看看程序怎么走的从而修改代码里的值,很简单的.

   @RequestMapping("/exportTempExcel")
    @ResponseBody
    public String exportTempExcel(){

        //文件输出的位置
        String target = "F:/temp/exportTempExcel.xlsx";
        //模板名字
        String temp = "F:/temp/hebeiTemp.xlsx";
        //参数列表
        String[] params = new String[5];
        params[0] = "占位符0";
        params[1] = "占位符1";
        params[2] = "占位符2";
        params[3] = "占位符3";
        params[4] = "占位符4";
        List<ItemData> list = new ArrayList<>();
        ItemData itemData = new ItemData("机构名称",new Date(),1,1,1,1f,1,1,1,1,1f);
        list.add(itemData);
        list.add(itemData);
        list.add(itemData);
        list.add(itemData);
        list.add(itemData);
        list.add(itemData);
        list.add(itemData);

        //excel表中对应的javaBean属性的集合
        List<String> propertyList = createPropertyList();
        TemplateExcelUtil ex = new TemplateExcelUtil();
        ex.exportExcel(temp,target,params,propertyList,list);
        return "ok";
    }

    private List<String> createPropertyList() {
        List<String> propertyList = new ArrayList<String>();
        propertyList.add("organizeName");
        propertyList.add("createtime");
        propertyList.add("organizeId");
        propertyList.add("numActivities");
        propertyList.add("numActual");
        propertyList.add("implRate");
        propertyList.add("newClient");
        propertyList.add("oldClient");
        propertyList.add("yxClient");
        propertyList.add("money");
        return propertyList;
    }

导出结果.