> resultList = historyStockReportService
.queryDownLoadHistoryStockInfo(condition);
dataLength = resultList.size();
String[] columns = Constant.PP_DETAIL_COLUMN.split(",");
for (int i = 0; i < dataLength; i++) {
out.print(ExportUtils.handleExportData(resultList.get(i), columns));
out.flush();
pageNumber++;
} catch (IOException e) {
LOGGER.error("导出" + fileName + "发生错误:", e);
} finally {
if (out != null) {
out.close();
备注：这里查询list集合数据是按照分页查询，pageNo=1,pageSize=1000,这样支持大数据量导出，比如导出10万条数据，分页查询是为了防止把库查询挂了，数据量过大会发生导出OOM
@Service
public class HistoryStockReportServiceImpl extends BaseImpl implements HistoryStockReportService {
private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportServiceImpl.class);
//定义数据库查询字段
private String[] columnArray = Constant.CHECK_PP_INDEX_COLUMN.split(",");
@Autowired
private DalClient dalClient;
* 下载历史库存报表
* @param condition
* @return
public List<Map<String, Object>> queryDownLoadHistoryStockInfo(Map<String, Object> condition) {
List<Map<String, Object>> resultList = dalClient
.queryForList("historyStockData.queryDownLoadHistoryStockInfo", condition);
if (!CollectionUtil.isEmptyList(resultList)) {
IndexDataFormatUtils.coverPpInfo(resultList, columnArray);
return resultList;
查询集合处理工具类：IndexDataFormatUtils
public class IndexDataFormatUtils {
* 统一处理PP视频历史库存、特殊渠道指标报表的衍生指标数据
* @param list
* @param columnArray
public static void coverPpInfo(List<Map<String, Object>> list, String[] columnArray) {
for (Map<String, Object> map : list) {
// 组装处理rate参数
calculateRate(map, Constant.FEE_PRACTICAL_SHOW_NUM, Constant.THEORY_STOCK_NUM, Constant.FILLFEE_RATE);
calculateRate(map, Constant.DELIVERY_PRACTICAL_SHOW_NUM, Constant.THEORY_STOCK_NUM,
Constant.DELIVERY_FILL_RATE);
calculateRate(map, Constant.SHOW_NUM, Constant.THEORY_STOCK_NUM, Constant.THEORY_STOCK_RATE);
calculateRate(map, Constant.THEORY_STOCK_REMAINED_NUM, Constant.THEORY_STOCK_NUM,
Constant.THEORY_STOCK_REMAINED_RATE);
// 处理数据值为null的单一指标
coverIndexInfoFromNull(map, columnArray);
* 处理占比参数
* @param map
* @param dividendKey
* @param divisorKey
* @param quotientKey
public static void calculateRate(Map<String, Object> map, String dividendKey, String divisorKey, String quotientKey) {
if (StringUtils.isBlank(MapUtils.getString(map,dividendKey)) || StringUtils.isBlank(MapUtils.getString(map,divisorKey))) {
map.put(quotientKey,"-");
return;
BigDecimal dividend = BigDecimal.valueOf(MapUtils.getDoubleValue(map, dividendKey)); // 被除数
BigDecimal divisor = BigDecimal.valueOf(MapUtils.getDoubleValue(map, divisorKey)); // 除数
BigDecimal quotient = BigDecimal.valueOf(0.00);
// =0 相等 >0前者大于后者 ，反之 <0 前者小于后者
if(dividend.compareTo(BigDecimal.ZERO) != 0 && divisor.compareTo(BigDecimal.ZERO) != 0){
quotient = dividend.multiply(BigDecimal.valueOf(100)).divide(divisor,2,BigDecimal.ROUND_HALF_UP);
map.put(quotientKey, quotient.setScale(2) + "");
* 处理数据值为null的单一指标
* @param map
* @param columnArray
public static void coverIndexInfoFromNull(Map<String, Object> map, String[] columnArray) {
for (String columnName : columnArray) {
String columnValue = MapUtils.getString(map,columnName);
if (StringUtils.isBlank(columnValue)) {
map.put(columnName,"-");
}else {
map.put(columnName,columnValue);
导出数据处理工具类：ExportUtils
public class ExportUtils {
* 处理下载指标
public static String handleExportData(Map<String,Object> reportData, String[] columns){
StringBuilder sb = new StringBuilder();
for (String columnName:columns) {
addStringBuffer(sb,reportData,columnName);
sb.append("\n");
return sb.toString();
public static void addStringBuffer(StringBuilder sb, Map<String, Object> map,String name){
if(map.get(name) == null ){
sb.append("-,");
}else{
String value = String.valueOf(map.get(name));
String temp = value.replaceAll("\r", "").replaceAll("\n", "");
if(temp.contains(",")){
if(temp.contains("\"")){
temp=temp.replace("\"", "\"\"");
//将逗号转义
temp="\""+temp+"\"";
sb.append("\t").append(temp).append(",");
//导出默认分页
public static final int PAGE_NO = 1;
public static final int PAGE_SIZE = 1000;
* PP视频
* 历史存储、特殊渠道数据库查询字段
public static final String CHECK_PP_INDEX_COLUMN =
"requestNum,advertiserVvNum,responseNum,showNum,clickNum,theoryStockNum,fillFeeNum,"
+ "feePracticalShowNum,deliveryFillNum,deliveryPracticalShowNum,theoryStockRemainedNum,"
+ "systemExceptionLost,userExitLost,income";
* 20190509
* pp视频历史库存、特殊渠道日志数据报表
* 报表下载模板头部(英文)
public static final String PP_DETAIL_COLUMN = "countDate,pointLocationCode,pointLocationName,positionScreenTypeCode,positionScreenTypeName," +
"requestNum,advertiserVvNum,responseNum,showNum,clickNum,theoryStockNum,fillFeeNum,feePracticalShowNum,deliveryFillNum," +
"deliveryPracticalShowNum,theoryStockRemainedNum,systemExceptionLost,userExitLost,income,fillFeeRate,deliveryFillRate," +
"theoryStockRate,theoryStockRemainedRate";
* pp视频历史库存日志数据报表
* 报表下载模板头部(中文)
public static final String[] PP_INDEX_DETAIL_HEAD_NAME_LIST = {"统计时间,","点位/终端编码,","点位/终端名称,","广告位编码,", "广告位名称,",
"请求量,", "广告vv量,","返回量,","曝光量,", "点击量,", "理论库存量,", "付费填充量,", "付费实际曝光量,", "配送填充量,",
"配送实际曝光量,", "理论库存余量,", "系统异常损失,", "用户退出损失,","收入,", "付费使用率,", "配送使用率,", "库存使用率,",
"库存余量占比,"};
导出效果：
Java导出csv文件：控制层：@Controller@RequestMapping("/historyReport/")public class HistoryStockReportController { private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportContr...
import pandas as pd
list = [[1,2,3], [4,5,6], [7,8,9], [10,11,12]]
title = [‘col1’, ‘col2’, ‘col3’]
try = pd.DataFrame(columes=title, data=list)
try.to_csv(‘dir’)
/// <typeparam name="T">类型T</typeparam>
/// <param name="lst">T的集合</param>
/// <param name="filePath">保存路径</param>
/// <returns></returns
采用Mybatis持久化框架，查询数据采用List模式返回结果，这种数据返回模式，能满足大多数的在少量返回结果集，或分页结果集的应用场景。
针对要大量返回结果集，特别是需要把超大查询结果集返回或导出的时候，List模式由于需要在应用服务器中构造对象。这不但需要消耗大量内存，而且会引起频繁的垃圾回收，造成性能降低。甚至因为内存无法容纳过大的结果集List，造成OOM异常。
有人采用分页模式，逐页导出的方式来解决。这种解决方式，针对超大结果集，比如数百万数据行时，由于可能会产生上万个分页查询，造成数据库上万
