需要达到的效果图如下:
上代码:
?
class="java">/** * 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; } }
?
?