CSV文件怎么利用Java導出？很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
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量,","返回量,","曝光量,", "點擊量,", "理論庫存量,", "付費填充量,", "付費實際曝光量,", "配送填充量,",
"配送實際曝光量,", "理論庫存余量,", "系統異常損失,", "用戶退出損失,","收入,", "付費使用率,", "配送使用率,", "庫存使用率,",
"庫存余量占比,"};導出效果：
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
