一、实现逻辑
A、选择excel类型如 .xls .xlsx,然后选择对应POI类
B、确定导出的数据列,设置表头样式
C、根据表头列及导出的数据,遍历数据,一行一行地写入数据。
D、关闭流
代码如下
package com.krycai.report.servicepol;
import java.io.FileOutputStream;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet; //对Excel 97(-2007)文件操作的纯Java实现
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
/**
* 导出认证类型人流量统计表
* @author Allen
* @since: v1.0
*/
public class ReportForAuthenServicepoi {
private final static Logger logger = Logger.getLogger(ReportForAuthenService.class);
HSSFWorkbook wb; //创建工作薄对象
FileOutputStream fos;
/**
* 导出报表
*
* @param reportBean
* @throws Exception
*/
public String exportExcel(ReportBean reportBean ) throws Exception {
try {
//表单名称的处理过程
String fileName = "认证途径人流量统计表_";
if(StringUtils.stringNotEmpty(reportBean.getTimeType())){
fileName += DateUtils.toString(new Date(), "yyyyMMdd") + "-" + reportBean.getTimeType();
}else{
fileName += reportBean.getBeginTime().replace("-", "")+"_"+reportBean.getEndTime().replace("-", "");
}
if(StringUtils.stringNotEmpty(reportBean.getShopName())){
fileName +="_"+reportBean.getShopName();
}
if(reportBean.getAuthenType() != null){
fileName +="_"+reportBean.getAuthenType().getMessage();
}
if(StringUtils.stringNotEmpty(reportBean.getWlcName())){
fileName += "_" + reportBean.getWlcName();
}
fileName = fileName.replace(".", ",");
fileName +=".xls";
List<ReportForAuthenBean> reportForVisitorBeans = WifiUserReportService.reportAuthen(reportBean);
fos = new FileOutputStream(WifiUserReportService.REPORT_PATH + fileName);
wb= new HSSFWorkbook();
HSSFSheet sh = wb.createSheet();
sh.setColumnWidth( 0, 256*15+184);
sh.setColumnWidth( 1, 256*30+184);
sh.setColumnWidth( 2, 256*30+184);
sh.setColumnWidth( 3, 256*30+184);
sh.setColumnWidth( 4, 256*30+184);
// 第一行表头标题,CellRangeAddress 参数:起始行号,终止行号, 起始列号,终止列号
HSSFRow row = sh.createRow(0);
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(new HSSFRichTextString("认证途径人流量统计表"));
cell0.setCellStyle(HSSFFontUtils.headFont(wb));
sh.addMergedRegion(new CellRangeAddress(0, 0, 0,2));
// 第二行时间表示
HSSFRow row1 = sh.createRow(1);
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(new HSSFRichTextString("时间范围:"));
cell1.setCellStyle(HSSFFontUtils.cloFont(wb));
sh.addMergedRegion(new CellRangeAddress(1, 1, 0, 0));
if(StringUtils.stringNotEmpty(reportBean.getTimeType())){
HSSFCell cell2 = row1.createCell(1);
cell2.setCellValue(DateUtils.toString(new Date(), "yyyyMMdd") + "-" + reportBean.getTimeType());
cell2.setCellStyle(HSSFFontUtils.dateFont(wb));
sh.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
}else{
HSSFCell cell2 = row1.createCell(1);
cell2.setCellValue(reportBean.getBeginTime()+ "至" + reportBean.getEndTime());
cell2.setCellStyle(HSSFFontUtils.dateFont(wb));
sh.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
}
if(StringUtils.stringNotEmpty(reportBean.getShopName())){
HSSFCell cell3 = row1.createCell(2);
cell3.setCellValue(new HSSFRichTextString("店铺:" + reportBean.getShopName()));
cell3.setCellStyle(HSSFFontUtils.cloFont(wb));
sh.addMergedRegion(new CellRangeAddress(1, 1, 3, 4));
}
// 第三行表头列名
HSSFRow row3 = sh.createRow(2);
HSSFCell cell = row3.createCell(0);
cell.setCellValue(new HSSFRichTextString("序号"));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row3.createCell(1);
cell.setCellValue(new HSSFRichTextString("认证途径"));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row3.createCell(2);
cell.setCellValue(new HSSFRichTextString("人流量"));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row3.createCell(3);
cell.setCellValue(new HSSFRichTextString("已关注"));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row3.createCell(4);
cell.setCellValue(new HSSFRichTextString("未关注"));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
//填充数据的内容
int i = 3, j = 1, z = 0;
ReportForAuthenBean reportForVisitorBean=new ReportForAuthenBean();
while(z<reportForVisitorBeans.size()){
//ws.setRowView(i, 450);
reportForVisitorBean=reportForVisitorBeans.get(z);
row = sh.createRow(i);
cell = row.createCell(0);
cell.setCellValue(new HSSFRichTextString(String.valueOf(j)));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row.createCell(1);
cell.setCellValue(new HSSFRichTextString(reportForVisitorBean.getAuthenType().getMessage()));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row.createCell(2);
cell.setCellValue(Double.parseDouble(reportForVisitorBean.getCount()));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row.createCell(3);
cell.setCellValue(Double.parseDouble(reportForVisitorBean.getSubscribe()));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
cell = row.createCell(4);
cell.setCellValue(Double.parseDouble(reportForVisitorBean.getUnsubscribe()));
cell.setCellStyle(HSSFFontUtils.cloFont(wb));
i++;
j++;
z++;
}
wb.write(fos);
return WifiUserReportService.REPORT_PATH + fileName;
}catch (Exception e){
logger.error("[ReportForAuthenService]==> 生成认证途径人流量统计表失败:,异常信息:", e);
}finally {
try {
if(fos != null){
fos.close();
}
}
catch (Exception e) {
logger.error("[ReportForAuthenService]==> 关闭流失败,异常信息:", e);
}
}
return null;
}
}
二、单元样式实例
这里只是简单举例,详细单元样式,根据需求及官方文档确定
/**
* 设置第一行样式
* 设置表头样式 .xlsx
* @param xwb
* @param flag true 表示黑色字体, false 表示 红色字体格式
* @return
*/
public static CellStyle headStyle(SXSSFWorkbook xwb,boolean flag){
CellStyle style = xwb.createCellStyle();
Font headfont = xwb.createFont();
if(flag){
headfont.setFontHeightInPoints((short) 12);
headfont.setBold(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
}else{
headfont.setColor(HSSFColorPredefined.RED.getIndex());
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.TOP);
}
style.setFont(headfont);
style.setWrapText(true);
style.setLocked(true);
return style;
}
/**
* 设置第一行样式(提示语) .xls
* 设置表头样式
* @param wb
* @param flag true 表示黑色字体, false 表示 红色字体格式
* @return
*/
public static HSSFCellStyle explainStyle(HSSFWorkbook wb ,boolean flag){
HSSFCellStyle style = wb.createCellStyle();
Font explainfont = wb.createFont();
if(flag){
explainfont.setFontName("宋体");
explainfont.setBold(true);
explainfont.setFontHeightInPoints((short) 12);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
}else {
explainfont.setColor(HSSFColorPredefined.RED.getIndex());
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.TOP);
}
style.setFont(explainfont);
style.setWrapText(true);
style.setLocked(true);
return style;
}