Java导出CSV文件的方法

Java导出CSV文件的方法

本文实例为大家分享了java导出CSV文件的具体代码，供大家参考，具体内容如下

Java导出csv文件：

控制层：

@Controller

@RequestMapping("/historyReport/")

public class HistoryStockReportController {

private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportController.class);

@Autowired

private HistoryStockReportService historyStockReportService;

/**

* 下载历史库存报表

* @param request

* @param response

*/

@RequestMapping("new/downLoadHistoryStockInfo.htm")

@ResponseBody

public ResultMsg

HttpServletResponse response) {

String reportName = "PP视频_历史库存效果概况数据_" + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");

String[] header = Constant.PP_INDEX_DETAIL_HEAD_NAME_LIST;

try {

//点位/终端

String pointLocation = request.getParameter(Constant.POINT_LOCATION_CODE);

//广告位

String positionScreenType = request.getParameter(Constant.POSITION_SCREEN_TYPE_CODE);

String startDate = request.getParameter(Constant.START_DATE);

String endDate = request.getParameter(Constant.END_DATE);

// 判断接口参数

if (!DateUtils.isDate(startDate) || !DateUtils.isDate(endDate)) {

return ResultMsg.buildErrorMsg(Constant.DATE_ERROR_MSG);

}

//封装查询参数

Map condition = new HashMap<>();

condition.put(Constant.POINT_LOCATION_CODE, pointLocation);

condition.put(Constant.POSITION_SCREEN_TYPE_CODE, positionScreenType);

condition.put(Constant.START_DATE, startDate);

condition.put(Constant.END_DATE, endDate);

//导出csv

exportBatch(response, condition, header, reportName);

} catch (Exception e) {

LOGGER.error("导出" + reportName + "发生错误:", e);

}

return null;

}

/**

* 导出报表

* @param response

* @param header

* @param fileName

* @throws IOException

*/

private void exportBatch(HttpServletResponse response, Map condition, String[] header,

String fileName) throws IOException {

response.setContentType("application/vnd.ms-excel;charset=GBK");

response.setHeader("Content-Disposition",

"attachment;filename=" + new String((fileName).getBytes("GBK"), "ISO8859-1") + "." + "csv");

StringBuilder sb = new StringBuilder();

for (String s : header) {

sb.append(s);

}

sb.append("\n");

PrintWriter out = null;

try {

out = response.getWriter();

out.print(sb.toString());

int pageNumber = Constant.PAGE_NO;

int pageSize = Constant.PAGE_SIZE;

int dataLength = pageSize;

while (dataLength == pageSize) {

int startIndex = (pageNumber - 1) * pageSize;

condition.put("startIndex", startIndex);

condition.put("maxCount", pageSize);

List

.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

List

.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

for (Map 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 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 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 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 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量,","返回量,","曝光量,", "点击量,", "理论库存量,", "付费填充量,", "付费实际曝光量,", "配送填充量,",

"配送实际曝光量,", "理论库存余量,", "系统异常损失,", "用户退出损失,","收入,", "付费使用率,", "配送使用率,", "库存使用率,",

"库存余量占比,"};

导出效果：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。