摘要：積分消費(fèi)明細(xì)對(duì)賬單其中，有四個(gè)參數(shù)，分別是，，，。導(dǎo)出讀取數(shù)據(jù)庫的信息，轉(zhuǎn)成。

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 + "積分消費(fèi)明細(xì)對(duì)賬單.xlsx");
    
    ExcelHelper excelHelper = SpringContextHolder.getBean(ExcelHelper.class);
    excelHelper.exportExcelFile("zjPointsConsumeDetailExcel", modelFile, outputFile, zjPointsConsumeDetails);
    renderFile(outputFile);
}

其中，exportExcelFile有四個(gè)參數(shù)，分別是String mapping，F(xiàn)ile modelFile，F(xiàn)ile outputFile，List<> dataList。mapping對(duì)應(yīng)ZjPointsConsumeDetailExcel 類，ZjPointsConsumeDetailExcel 的作用是將數(shù)據(jù)庫中的字段與excel中展示的字段一一對(duì)應(yīng)，并可進(jìn)行特殊字段的轉(zhuǎn)換，代碼如下：

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 = "序號(hào)", type = ExcelField.CELL_TYPE_NUMERIC)
    private Long id;
    
    @ExcelField(fieldName = "consume_kind", index = "1", title = "消費(fèi)分類", type = ExcelField.CELL_TYPE_STRING)
    private String consumeKind;
    
    @ExcelField(fieldName = "consume_abstract", index = "2", title = "消費(fèi)摘要", 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 = "消費(fèi)分值", type = ExcelField.CELL_TYPE_NUMERIC)
    private Long consumePoints;
    
    @ExcelField(fieldName = "consume_time", index = "5", title = "消費(fèi)時(shí)間", type = ExcelField.CELL_TYPE_STRING)
    private String consumeTime;
    
    @ExcelField(fieldName = "related_order", index = "6", title = "關(guān)聯(lián)訂單號(hào)", type = ExcelField.CELL_TYPE_STRING)
    private String relatedOrder;
    
}

以上僅是如何使用，有空補(bǔ)上源碼。

總結(jié)

導(dǎo)入：讀取Sheet信息，并且保存至數(shù)據(jù)庫。
導(dǎo)出：讀取數(shù)據(jù)庫的信息，轉(zhuǎn)成Sheet。

使用poi導(dǎo)出excel

參考博客：使用poi實(shí)現(xiàn)導(dǎo)入導(dǎo)出

    /**
     * 導(dǎo)出數(shù)據(jù)至Excel文件
     * @param excelColumns  報(bào)表頭信息
     * @param excelHeadConvertMap   需要對(duì)數(shù)據(jù)進(jìn)行特殊轉(zhuǎn)換的列
     * @param modelFile  模板Excel文件
     * @param outputFile 導(dǎo)出文件
     * @param dataList  導(dǎo)入excel報(bào)表的數(shù)據(jù)來源
     * @return void
     * 2012-4-19 上午10:04:30
     */
    public void exportExcelFile(ExcelHead head, File modelFile, File outputFile, List dataList) {
        // 讀取導(dǎo)出excel模板
        InputStream inp = null;
        Workbook wb = null;
        try {
            inp = new FileInputStream(modelFile);
            wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            // 生成導(dǎo)出數(shù)據(jù)
            buildExcelData(sheet, head, dataList);
             
            // 導(dǎo)出到文件中
            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();
        }
    }
    
     /**
     * 生成導(dǎo)出至Excel文件的數(shù)據(jù)
     * @param sheet 工作區(qū)間
     * @param excelColumns  excel表頭
     * @param excelHeadMap  excel表頭對(duì)應(yīng)實(shí)體屬性
     * @param excelHeadConvertMap   需要對(duì)數(shù)據(jù)進(jìn)行特殊轉(zhuǎn)換的列
     * @param dataList      導(dǎo)入excel報(bào)表的數(shù)據(jù)來源
     * @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();
         
        // 將表結(jié)構(gòu)轉(zhuǎn)換成Map
        Map excelHeadMap = convertExcelHeadToMap(excelColumns);
         
        // 從第幾行開始插入數(shù)據(jù)
        int startRow = head.getRowCount();
        int order = 1;
        //數(shù)據(jù)循環(huán)
        for (Object obj : dataList) {
            Row row = sheet.createRow(startRow++);
            ////字段循環(huán)（通過字段名，拿到對(duì)象該字段的值）
            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);
                     
                    // 如果存在需要轉(zhuǎn)換的字段信息，則進(jìn)行轉(zhuǎn)換
                    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.創(chuàng)建工作簿 (WORKBOOK)  

HSSFWorkbook wb = new HSSFWorkbook();  

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

wb.write(fileOut);  

fileOut.close();  

2.創(chuàng)建工作表（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.創(chuàng)建單元格(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();