需要达到的效果图如下:
上代码:
/** * 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; } }
相关推荐
NULL 博文链接:https://747017186.iteye.com/blog/2163242
解释:该方法相当于新建了一个excel文件,HSSFWorkbook : excel的文档对象。 2.//创建第一个sheet(页),命名为 page1 Sheet sheet = wb.createSheet("page1"); 解释:该方法相当于在excel文件里新建了一个工作页...
Apache POI 导出EXCEL的封装实现工具类,超简单的调用方式,有说明文档示例参考,一看便懂,将繁琐变为简单,是我们一直追求的脚步……
本篇文章主要介绍了java后台利用Apache poi 生成excel文档提供前台下载示例,非常具有实用价值,需要的朋友可以参考下
JAVA 生成Excel库Apache POI3.15,完整的库,已经测试过了,可以直接使用。 包含使用实例
Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能,下面这篇文章主要给大家介绍了关于利用POI生成EXCEL文件的相关资料,需要的朋友可以...
poi apache-poi导入实例 导入excel 利用java的apache-poi读取每一行每一列,导入excel数据,两个for循环出来的[ ][ ] 承装数据
Apache的POI组件是Java操作Microsoft Office办公套件的强大API,其中对Word,Excel和PowperPoint都有支持,当然使用较多的还是Excel,因为Word和PowerPoint用程序动态操作的应用较少。那么本文就结合POI来介绍一下...
本篇文章主要介绍了Java通过apache poi生成excel实例代码,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧
主要给大家介绍了关于Poi读取Excel引发内存溢出问题的解决方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面跟着小编来一起学习学习吧。
Java POI 导入导出Excel简单实例源代码,下载即可...操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI。这里我们用Apache POI!我们先去Apache的大本营下载POI的jar包:http://poi.apache.org/ 。
apache poi报表导入导出实例 基于服务器下载报表。导入项目既可以运行。
Apache POI是一个流行的Java库,用于读取和写入Microsoft Office格式的文件,特别是Excel。它是Apache软件基金会的一个开源项目,提供了多种API来处理Office文档,包括Word、Excel、PowerPoint等。Apache POI的主要...
我自己写的测试用例包括Excel的读写 还有jar包都放在压缩文件里
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程式对Microsoft Office格式档案读和写的功能。 结构: ... 示例1将演示如何利用Jakarta POI API 创建Excel 文档。 示例1程序如下:
主要介绍了java如何利用POI读取Excel行数,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
在此存储库中,我们有一个示例,确定如何使用apache poi读取Excel文件并将所有项目保存在mongoDB中 在此存储库中,我们有一些模块用于主要项目类,kafka服务,poi服务,最后保存在MongoDb中 excel文件上传 运行和...
POI库是Apache提供的用于在Windows下读写各类微软Office文档的Java库,这里我们就来看一下Java使用Apache POI库读取Excel表格文档的示例: