Apache POI 创建Excel文件实例

需要达到的效果图如下：

上代码：

 

/**
 * Copyright (c) 2004-2014 All Rights Reserved.
 */

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.alibaba.common.lang.StringUtil;
import com.alibaba.common.lang.io.ByteArrayOutputStream;
import com.alibaba.common.logging.Logger;
import com.alibaba.common.logging.LoggerFactory;
import CacheUtil;
import UserDataCacheKeyUtil;
import DateUtilExt;
import IOUtil;
import LoggerUtil;
import OnDayKeepVO;
import UserKeepData;
import WithinDayKeepVO;
import SessionUtil;
import ResourceViewModelDescriptor;

/**
 * @author 
 * @version $Id: DownloadExcelController.java, v 0.1 2014-9-9 下午05:06:56 wb-jiatao Exp $
 */
@Controller
@RequestMapping("/rententionDownload.resource")
public class DownloadExcelController {

    private static final Logger logger                = LoggerFactory
                                                          .getLogger(DownloadExcelController.class);

    /**  */
    private static final String READABLE_DATE_FORMATE = "yyyy年MM月dd日";

    @RequestMapping(method = RequestMethod.GET)
    public void doGet(ModelMap modelMap, HttpServletRequest request) throws Exception {

        //1.创建excel
        HSSFWorkbook wb = new HSSFWorkbook();
        //2.指定颜色
        wb.getCustomPalette().setColorAtIndex(IndexedColors.DARK_BLUE.getIndex(), (byte) 224,
            (byte) 255, (byte) 255);
        //3.创建2个页
        Sheet onSheet = wb.createSheet("单日留存数据");
        Sheet wiSheet = wb.createSheet("累计留存数据");
        createHead(wb, onSheet);
        createHead(wb, wiSheet);

        //4.从缓存中获取数据
        UserKeepData keepData = (UserKeepData) CacheUtil.getCache(UserDataCacheKeyUtil
            .getKeepDataExcelCacheKey(SessionUtil.getAppId(request), SessionUtil.getUserId()));
        String startDate = null;
        String endDate = null;
        if (keepData != null) {
            //5.解析数据，填充excel
            List<OnDayKeepVO> onDatas = keepData.getOnDatas();
            List<WithinDayKeepVO> winthinDatas = keepData.getWinthinDatas();
            CreationHelper helper = wb.getCreationHelper();
            CellStyle dateStyle = UserKeepDataExcelHelper.createBodyStyleCell(wb);
            dateStyle.setDataFormat(helper.createDataFormat().getFormat(READABLE_DATE_FORMATE));
            CellStyle textStyle = UserKeepDataExcelHelper.createBodyStyleCell(wb);
            CellStyle doubleStyle = UserKeepDataExcelHelper.createBodyStyleCell(wb);
            doubleStyle.setDataFormat(helper.createDataFormat().getFormat("0.0%"));
            if (!CollectionUtils.isEmpty(onDatas)) {
                startDate = onDatas.get(0).getReportDate();
                endDate = onDatas.get(onDatas.size() - 1).getReportDate();
                fillOnDaySheetData(onSheet, onDatas, dateStyle, textStyle, doubleStyle);
            }
            if (!CollectionUtils.isEmpty(winthinDatas)) {
                fillWithinSheet(wiSheet, winthinDatas, dateStyle, textStyle, doubleStyle);
            }
        }
       
        ResourceViewModelDescriptor resDescriptor = new ResourceViewModelDescriptor();
        modelMap.addAttribute(ResourceViewModelDescriptor.RESOURCE_VIEW_MODEL_KEY, resDescriptor);

        startDate = StringUtil.isEmpty(startDate) ? "" : DateUtilExt.format(new SimpleDateFormat(
            "yyyyMMdd").parse(startDate), READABLE_DATE_FORMATE);
        endDate = StringUtil.isEmpty(endDate) ? "" : DateUtilExt.format(new SimpleDateFormat(
            "yyyyMMdd").parse(endDate), READABLE_DATE_FORMATE);
        ByteArrayOutputStream os = null;
        try {
            os = new ByteArrayOutputStream();
            wb.write(os);
            os.flush();
            resDescriptor.setSourceFileData(os.toByteArray().getBytes());
        } catch (Exception e) {
            LoggerUtil.error(logger, "DownloadExcelController#doGet下载数据异常", e);
        } finally {
            IOUtil.closeStreamSafely(os);
        }
        String outputFileName = startDate + "至" + endDate + "数据报表.xls";
        outputFileName = StringUtil.isEmpty(startDate) ? "无数据.xls" : outputFileName;
        resDescriptor.setOutputFileName(outputFileName);
    }

    /**
     * 
     * @param wiSheet
     * @param winthinDatas
     * @param dateStyle
     * @param textStyle
     * @param doubleStyle
     * @throws ParseException
     */
    private static void fillWithinSheet(Sheet wiSheet, List<WithinDayKeepVO> winthinDatas,
                                        CellStyle dateStyle, CellStyle textStyle,
                                        CellStyle doubleStyle) throws ParseException {
        int rowIndex = 2;
        for (WithinDayKeepVO wi : winthinDatas) {
            Row wiRow = wiSheet.createRow(rowIndex++);
            Map<String, Double> withinDayKeepRates = wi.getWithinDayKeepRates();
            for (int i = 0; i < 11; i++) {
                Cell aCell = wiRow.createCell(i);
                switch (i) {
                    case 0:
                        //style
                        aCell.setCellStyle(dateStyle);
                        //数据
                        String reportDate = wi.getReportDate();
                        Date date = new SimpleDateFormat("yyyyMMdd").parse(reportDate);
                        aCell.setCellValue(date);
                        break;
                    case 1:
                        //style
                        aCell.setCellStyle(textStyle);
                        //data
                        aCell.setCellValue(wi.getNewFollowCount());
                        break;
                    case 9:
                        //style
                        aCell.setCellStyle(doubleStyle);
                        //data
                        String key = "focus_15d_user_cnt";
                        Double v = withinDayKeepRates.get(key);
                        v = v == null ? 0L : v;
                        aCell.setCellValue(v.doubleValue());
                        break;
                    case 10:
                        //style
                        aCell.setCellStyle(doubleStyle);
                        //data
                        key = "focus_30d_user_cnt";
                        v = withinDayKeepRates.get(key);
                        v = v == null ? 0L : v;
                        aCell.setCellValue(v.doubleValue());
                        break;
                    default:
                        //style
                        aCell.setCellStyle(doubleStyle);
                        //data
                        key = "focus_" + (i - 1) + "d_user_cnt";
                        v = withinDayKeepRates.get(key);
                        v = v == null ? 0L : v;
                        aCell.setCellValue(v.doubleValue());
                }
            }
        }
    }

    /**
     * 
     * @param onSheet
     * @param onDatas
     * @param dateStyle
     * @param textStyle
     * @param doubleStyle
     * @throws ParseException
     */
    private static void fillOnDaySheetData(Sheet onSheet, List<OnDayKeepVO> onDatas,
                                           CellStyle dateStyle, CellStyle textStyle,
                                           CellStyle doubleStyle) throws ParseException {
        int rowIndex = 2;
        for (OnDayKeepVO on : onDatas) {
            Row onRow = onSheet.createRow(rowIndex++);
            Map<String, Double> onDayKeepRates = on.getOnDayKeepRates();
            for (int i = 0; i < 11; i++) {
                Cell aCell = onRow.createCell(i);
                switch (i) {
                    case 0:
                        //style
                        aCell.setCellStyle(dateStyle);
                        //数据
                        String reportDate = on.getReportDate();
                        Date date = new SimpleDateFormat("yyyyMMdd").parse(reportDate);
                        aCell.setCellValue(date);
                        break;
                    case 1:
                        //style
                        aCell.setCellStyle(textStyle);
                        //data
                        aCell.setCellValue(on.getNewFollowCount());
                        break;
                    case 9:
                        //style
                        aCell.setCellStyle(doubleStyle);
                        //data
                        String key = "focus_on_15d_user_cnt";
                        Double v = onDayKeepRates.get(key);
                        v = v == null ? 0L : v;
                        aCell.setCellValue(v.doubleValue());
                        break;
                    case 10:
                        //style
                        aCell.setCellStyle(doubleStyle);
                        //data
                        key = "focus_on_30d_user_cnt";
                        v = onDayKeepRates.get(key);
                        v = v == null ? 0L : v;
                        aCell.setCellValue(v.doubleValue());
                        break;
                    default:
                        //style
                        aCell.setCellStyle(doubleStyle);
                        //data
                        key = "focus_on_" + (i - 1) + "d_user_cnt";
                        v = onDayKeepRates.get(key);
                        v = v == null ? 0L : v;
                        aCell.setCellValue(v.doubleValue());
                }
            }
        }
    }

    /**
     * 
     * @param wb
     * @param keepSheet
     */
    private static void createHead(HSSFWorkbook wb, Sheet keepSheet) {
        //创建头部标题
        keepSheet.setColumnWidth(0, 17 * 256);
        keepSheet.setColumnWidth(1, 15 * 256);
        Row row1 = keepSheet.createRow(0);
        Row row2 = keepSheet.createRow(1);
        List<Cell> cellList1 = new ArrayList<Cell>();
        List<Cell> cellList2 = new ArrayList<Cell>();
        for (int i = 0; i < 11; i++) {
            Cell createCell = row1.createCell(i);
            CellStyle createHeadStyleCell = UserKeepDataExcelHelper.createHeadStyleCell(wb);
            createCell.setCellStyle(createHeadStyleCell);
            cellList1.add(createCell);
            Cell createCell2 = row2.createCell(i);
            createCell2.setCellStyle(createHeadStyleCell);
            cellList2.add(createCell2);
        }
        Cell cell = cellList1.get(0);
        cell.setCellValue("日期");
        cellList1.get(1).setCellValue("新增关注用户");
        cellList1.get(2).setCellValue("留存率");
        cellList2.get(2).setCellValue("第1天");
        cellList2.get(3).setCellValue("第2天");
        cellList2.get(4).setCellValue("第3天");
        cellList2.get(5).setCellValue("第4天");
        cellList2.get(6).setCellValue("第5天");
        cellList2.get(7).setCellValue("第6天");
        cellList2.get(8).setCellValue("第7天");
        cellList2.get(9).setCellValue("第15天");
        cellList2.get(10).setCellValue("第30天");
        keepSheet.addMergedRegion(CellRangeAddress.valueOf("A1:A2"));
        keepSheet.addMergedRegion(CellRangeAddress.valueOf("B1:B2"));
        keepSheet.addMergedRegion(CellRangeAddress.valueOf("C1:K1"));
    }

    @SuppressWarnings("unused")
    private static final void prepareData() throws Exception {
        UserKeepData keepData = new UserKeepData();
        OnDayKeepVO o1 = new OnDayKeepVO();
        o1.setNewFollowCount(100);
        o1.setReportDate("20140909");
        for (int i = 0; i < 9; i++) {
            o1.getOnDayKeepRates().put("focus_on_" + (i + 1) + "d_user_cnt", 0.011 * (i + 1));
        }
        OnDayKeepVO o2 = new OnDayKeepVO();
        BeanUtils.copyProperties(o2, o1);
        keepData.getOnDatas().add(o1);
        keepData.getOnDatas().add(o2);
        WithinDayKeepVO w1 = new WithinDayKeepVO();
        w1.setNewFollowCount(1000);
        w1.setReportDate("20140904");
        for (int i = 0; i < 9; i++) {
            w1.getWithinDayKeepRates().put("focus_" + (i + 1) + "d_user_cnt", 0.12 * (i + 1));
        }
        keepData.getWinthinDatas().add(w1);

    }

}

 

/**
 * Copyright (c) 2004-2014 All Rights Reserved.
 */

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * 用户留存数据excel帮助类
 * @author 
 * @version $Id: UserKeepDataExcelHelper.java, v 0.1 2014-9-10 上午11:01:50 wb-jiatao Exp $
 */
public abstract class UserKeepDataExcelHelper {

    /** 
     * 边框 
     * @param wb 
     * @return 
     */
    public static CellStyle createHeadStyleCell(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        //设置一个单元格边框颜色  
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        //设置一个单元格边框颜色  
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
        cellStyle.setFont(UserKeepDataExcelHelper.createHeadFonts(wb));
        cellStyle.setShrinkToFit(true);
        return cellStyle;
    }

    /** 
     * 边框 
     * @param wb 
     * @return 
     */
    public static CellStyle createBodyStyleCell(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        //设置一个单元格边框颜色  
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        //设置一个单元格边框颜色  
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setFont(createBodyFonts(wb));
        cellStyle.setShrinkToFit(true);
        return cellStyle;
    }

    /** 
     * 设置文字在单元格里面的位置 
     * CellStyle.ALIGN_CENTER 
     * CellStyle.VERTICAL_CENTER 
     * @param cellStyle 
     * @param halign 
     * @param valign 
     * @return 
     */
    public static CellStyle setCellStyleAlignment(CellStyle cellStyle, short halign, short valign) {
        //设置上下  
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        //设置左右  
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        return cellStyle;
    }

    /** 
     * 格式化单元格 
     * 如#,##0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找 
     * @param cellStyle 
     * @param fmt 
     * @return 
     */
    public static CellStyle setCellFormat(CreationHelper helper, CellStyle cellStyle, String fmt) {
        //还可以用其它方法创建format  
        cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));
        return cellStyle;
    }

    /** 
     * 前景和背景填充的着色 
     * @param cellStyle 
     * @param bg IndexedColors.ORANGE.getIndex(); 
     * @param fg IndexedColors.ORANGE.getIndex(); 
     * @param fp CellStyle.SOLID_FOREGROUND 
     * @return 
     */
    public static CellStyle setFillBackgroundColors(CellStyle cellStyle, short bg, short fg,
                                                    short fp) {
        //cellStyle.setFillBackgroundColor(bg);  
        cellStyle.setFillForegroundColor(fg);
        cellStyle.setFillPattern(fp);
        return cellStyle;
    }

    /** 
     * 设置字体 
     * @param wb 
     * @return 
     */
    public static Font createHeadFonts(Workbook wb) {
        //创建Font对象  
        Font font = wb.createFont();
        //设置字体  
        font.setFontName("黑体");
        //着色  
        font.setColor(HSSFColor.BLACK.index);
        //斜体  
        //        font.setItalic(true);
        //字体大小  
        font.setFontHeight((short) 250);
        return font;
    }

    /** 
     * 设置字体 
     * @param wb 
     * @return 
     */
    public static Font createBodyFonts(Workbook wb) {
        //创建Font对象  
        Font font = wb.createFont();
        //设置字体  
        font.setFontName("宋体");
        //着色  
        font.setColor(HSSFColor.BLACK.index);
        //斜体  
        //        font.setItalic(true);
        //字体大小  
        font.setFontHeight((short) 250);
        return font;
    }

}