poi导出excel

摘要：积分消费明细对账单其中，有四个参数，分别是，，，。导出读取数据库的信息，转成。

public void detailExport() {
    
    String sourceSystem = getPara("source_system");
    String dataDate = getPara("data_date");
    Integer pointsType = getParaToInt("points_type");
    
    List zjPointsConsumeDetails = zjPointsConsumeDetailService.findByDate(sourceSystem, dataDate, pointsType);
    
    File modelFile = new File(PathKit.getWebRootPath() + File.separator + "excel" + File.separator +"pointsConsumeDetails.xlsx");
    File outputFile = new File(PathKit.getWebRootPath() + File.separator + "temp" + File.separator + "积分消费明细对账单.xlsx");
    
    ExcelHelper excelHelper = SpringContextHolder.getBean(ExcelHelper.class);
    excelHelper.exportExcelFile("zjPointsConsumeDetailExcel", modelFile, outputFile, zjPointsConsumeDetails);
    renderFile(outputFile);
}

其中，exportExcelFile有四个参数，分别是String mapping，File modelFile，File outputFile，List<> dataList。mapping对应ZjPointsConsumeDetailExcel 类，ZjPointsConsumeDetailExcel 的作用是将数据库中的字段与excel中展示的字段一一对应，并可进行特殊字段的转换，代码如下：

package com.cwl.excel;

import com.cwl.plugin.poi.ExcelField;
import com.cwl.plugin.poi.ExcelModel;

@ExcelModel(name="zjPointsConsumeDetailExcel", rowCount="4")
public class ZjPointsConsumeDetailExcel {

    @ExcelField(fieldName = "id", index = "0", title = "序号", type = ExcelField.CELL_TYPE_NUMERIC)
    private Long id;
    
    @ExcelField(fieldName = "consume_kind", index = "1", title = "消费分类", type = ExcelField.CELL_TYPE_STRING)
    private String consumeKind;
    
    @ExcelField(fieldName = "consume_abstract", index = "2", title = "消费摘要", type = ExcelField.CELL_TYPE_STRING)
    private String consumeAbstract;
    
    @ExcelField(fieldName = "points_type", index = "3", title = "积分类型", type = ExcelField.CELL_TYPE_NUMERIC, convert = {"0:普通积分", "1:白金积分"})
    private String pointsTypeName;
    
    @ExcelField(fieldName = "consume_points", index = "4", title = "消费分值", type = ExcelField.CELL_TYPE_NUMERIC)
    private Long consumePoints;
    
    @ExcelField(fieldName = "consume_time", index = "5", title = "消费时间", type = ExcelField.CELL_TYPE_STRING)
    private String consumeTime;
    
    @ExcelField(fieldName = "related_order", index = "6", title = "关联订单号", type = ExcelField.CELL_TYPE_STRING)
    private String relatedOrder;
    
}

以上仅是如何使用，有空补上源码。

总结

导入：读取Sheet信息，并且保存至数据库。
导出：读取数据库的信息，转成Sheet。

使用poi导出excel

参考博客：使用poi实现导入导出

    /**
     * 导出数据至Excel文件
     * @param excelColumns  报表头信息
     * @param excelHeadConvertMap   需要对数据进行特殊转换的列
     * @param modelFile  模板Excel文件
     * @param outputFile 导出文件
     * @param dataList  导入excel报表的数据来源
     * @return void
     * 2012-4-19 上午10:04:30
     */
    public void exportExcelFile(ExcelHead head, File modelFile, File outputFile, List dataList) {
        // 读取导出excel模板
        InputStream inp = null;
        Workbook wb = null;
        try {
            inp = new FileInputStream(modelFile);
            wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            // 生成导出数据
            buildExcelData(sheet, head, dataList);
             
            // 导出到文件中
            FileOutputStream fileOut = new FileOutputStream(outputFile);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (InvalidFormatException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
    
     /**
     * 生成导出至Excel文件的数据
     * @param sheet 工作区间
     * @param excelColumns  excel表头
     * @param excelHeadMap  excel表头对应实体属性
     * @param excelHeadConvertMap   需要对数据进行特殊转换的列
     * @param dataList      导入excel报表的数据来源
     * @auther hubo
     * @return void
     * 2012-4-19 上午09:36:37
     */
    private void buildExcelData(Sheet sheet, ExcelHead head, List dataList) {
        List excelColumns = head.getColumns(); 
        Map excelHeadConvertMap = head.getColumnsConvertMap();
         
        // 将表结构转换成Map
        Map excelHeadMap = convertExcelHeadToMap(excelColumns);
         
        // 从第几行开始插入数据
        int startRow = head.getRowCount();
        int order = 1;
        //数据循环
        for (Object obj : dataList) {
            Row row = sheet.createRow(startRow++);
            ////字段循环（通过字段名，拿到对象该字段的值）
            for (int j = 0; j < excelColumns.size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellType(excelColumns.get(j).getType());
                String fieldName = excelHeadMap.get(j);
                if(fieldName != null) {
                    Object valueObject = BeanUtil.getProperty(obj, fieldName);
                     
                    // 如果存在需要转换的字段信息，则进行转换
                    if(excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) {
                        valueObject = excelHeadConvertMap.get(fieldName).get(valueObject);
                    }
                     
                    if(valueObject == null) {
                        cell.setCellValue("");
                    } else if (valueObject instanceof Integer) {
                        cell.setCellValue((Integer)valueObject);
                    } else if (valueObject instanceof String) {
                        cell.setCellValue((String)valueObject);
                    } else if (valueObject instanceof Date) {
                        cell.setCellValue(new JDateTime((Date)valueObject).toString("YYYY-MM-DD"));
                    } else {
                        cell.setCellValue(valueObject.toString());
                    }
                } else {
                    cell.setCellValue(order++);
                }
            }
        }
    }

poi的使用及简单介绍

1.创建工作簿 (WORKBOOK)  

HSSFWorkbook wb = new HSSFWorkbook();  

FileOutputStream fileOut = new FileOutputStream("workbook.xls");  

wb.write(fileOut);  

fileOut.close();  

2.创建工作表（SHEET）  

HSSFWorkbook wb = new HSSFWorkbook();  

HSSFSheet sheet1 = wb.createSheet("new sheet");  

HSSFSheet sheet2 = wb.createSheet("second sheet");  

FileOutputStream fileOut = new FileOutputStream("workbook.xls");  

wb.write(fileOut);  

fileOut.close();  

3.创建单元格(CELL)  

HSSFWorkbook wb = new HSSFWorkbook();  

HSSFSheet sheet = wb.createSheet("new sheet");  

// Create a row and put some cells in it. Rows are 0 based.  

HSSFRow row = sheet.createRow((short)0);  

// Create a cell and put a value in it.  

HSSFCell cell = row.createCell((short)0);  

cell.setCellValue(1);  

// Or do it on one line.  

row.createCell((short)1).setCellValue(1.2);  

row.createCell((short)2).setCellValue("This is a string");  

row.createCell((short)3).setCellValue(true);  

// Write the output to a file  

FileOutputStream fileOut = new FileOutputStream("workbook.xls");  

wb.write(fileOut);  

fileOut.close();