Apache POI 创建Excel文件实例

78144666

　　需要达到的效果图如下：

　　上代码：

/**
* 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;
}
}