Java导出CSV文件的方法
时间：2022-12-07 09:38:00 | 栏目：JAVA代码 | 点击：次
本文实例为大家分享了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<Map<String, Object>> downLoadHistoryStockInfo(HttpServletRequest request,
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<String, Object> 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<String, Object> 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<Map<String, Object>> 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量,","返回量,","曝光量,", "点击量,", "理论库存量,", "付费填充量,", "付费实际曝光量,", "配送填充量,",
"配送实际曝光量,", "理论库存余量,", "系统异常损失,", "用户退出损失,","收入,", "付费使用率,", "配送使用率,", "库存使用率,",
"库存余量占比,"};
导出效果：
