/**
* 导出数据日报
* @param request
* @param response
* @param string
* @param string2
* @param report
* @throws IOException
* @throws WriteException
*/
private void exportstatiscsDatily(HttpServletRequest request, HttpServletResponse response, String fileName,
String sheetName, Report report) throws IOException, WriteException {
// 根据传进来的文件路径、创建文件
File file = new File(fileName);
WritableWorkbook book = Workbook.createWorkbook(file);
WritableSheet sheet = book.createSheet(sheetName, 0);
WritableFont TitleFont = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat TitleStyle = new WritableCellFormat(TitleFont);
TitleStyle.setAlignment(Alignment.CENTRE);
TitleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
TitleStyle.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
//自适应宽度，换行显示
TitleStyle.setWrap(true);
WritableFont cellFont = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat cellStyle = new WritableCellFormat(cellFont);
cellStyle.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
// 获取统计数据
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("method", report.getMethod());
paramMap.put("startDate", report.getStartDate());
paramMap.put("endDate", report.getEndDate());
paramMap.put("offset", 0);
paramMap.put("pageSize", 20);
List<Map<Object, Object>> statisticsDatilyMapList = reportService.getAllStatisDailyList(paramMap);
if (statisticsDatilyMapList!=null && statisticsDatilyMapList.size()>0) {
//第一行：标题行
sheet.addCell(new Label(0, 0, "日期", TitleStyle));
sheet.setColumnView(0, 20);
sheet.addCell(new Label(1, 0, "总订购数", TitleStyle));
sheet.setColumnView(1, 20);
sheet.addCell(new Label(2, 0, "订购收益", TitleStyle));
sheet.setColumnView(2, 20);
sheet.addCell(new Label(3, 0, "退订数", TitleStyle));
sheet.setColumnView(3, 20);
sheet.addCell(new Label(4, 0, "点播用户数", TitleStyle));
sheet.setColumnView(4, 20);
sheet.addCell(new Label(5, 0, "点播次数", TitleStyle));
sheet.setColumnView(5, 20);
sheet.addCell(new Label(6, 0, "在线用户数", TitleStyle));
sheet.setColumnView(1, 20);
sheet.addCell(new Label(7, 0, "UV", TitleStyle));
sheet.setColumnView(7, 20);
sheet.addCell(new Label(8, 0, "PV", TitleStyle));
sheet.setColumnView(8, 20);
sheet.addCell(new Label(9, 0, "线上订购转化率", TitleStyle));
sheet.setColumnView(9, 20);
int num=9;
//查询导出pv管理，添加动态列
Report report2 =new Report();
report2.setStatus("1");
List<Report> pvList= reportService.getDailyPvListByPage(report2);
if(pvList!=null && pvList.size()>0){
for (Report report3 : pvList) {
sheet.addCell(new Label(++num, 0, report3.getName()+"PV", TitleStyle));
sheet.setColumnView(num, 20);
}
}
for(int i=0 ;i< statisticsDatilyMapList.size();i++) {// 行数
Map<Object, Object> statisticsMap = statisticsDatilyMapList.get(i);
for (Map.Entry<Object, Object> entry : statisticsMap.entrySet()) {
//标识列
num=9;
//日期
if("create_time".equals(entry.getKey()) ){
sheet.addCell(new Label(0, i + 1, String.valueOf(statisticsMap.get("create_time")), TitleStyle));
}
//总订购数
if("total_order_num".equals(entry.getKey()) ){
sheet.addCell(new Label(1, i + 1, String.valueOf(statisticsMap.get("total_order_num")), TitleStyle));
}
//订购收益
if("earnings".equals(entry.getKey()) ){
sheet.addCell(new Label(2, i + 1, String.valueOf(statisticsMap.get("earnings")), TitleStyle));
}
//退订数
if("unsub_order_num".equals(entry.getKey()) ){
sheet.addCell(new Label(3, i + 1, String.valueOf(statisticsMap.get("unsub_order_num")), TitleStyle));
}
//点播用户数
if("play_user_num".equals(entry.getKey()) ){
sheet.addCell(new Label(4, i + 1, String.valueOf(statisticsMap.get("play_user_num")), TitleStyle));
}
//点播次数
if("play_num".equals(entry.getKey()) ){
sheet.addCell(new Label(5, i + 1, String.valueOf(statisticsMap.get("play_num")), TitleStyle));
}
//在线用户数
if("active_num".equals(entry.getKey()) ){
sheet.addCell(new Label(6, i + 1, String.valueOf(statisticsMap.get("active_num")), TitleStyle));
}
//UV
if("uv_num".equals(entry.getKey()) ){
sheet.addCell(new Label(7, i + 1, String.valueOf(statisticsMap.get("uv_num")), TitleStyle));
}
//PV
if("pv_num".equals(entry.getKey()) ){
sheet.addCell(new Label(8, i + 1, String.valueOf(statisticsMap.get("pv_num")), TitleStyle));
}
//线上订购转化率
if("order_rate".equals(entry.getKey()) ){
double rate = Double.valueOf(String.valueOf(statisticsMap.get("order_rate")));
DecimalFormat df = new DecimalFormat("0.00%");
sheet.addCell(new Label(9,i+1, df.format(rate),TitleStyle));
}
//动态列
if(pvList!=null && pvList.size()>0){
for (Report report3 : pvList) {
num++;
if(report3.getName().equals(entry.getKey())){
sheet.addCell(new Label(num, i+1,String.valueOf( entry.getValue()), TitleStyle));
num=9;
}
}
}
}
}
book.write();
book.close();
// 声明一个file对象
File f = null;
try {
// 根据刚刚的文件地址、创建一个file对象
f = new File(fileName);
// 如果文件不存在
if (!f.exists()) {
response.sendError(404, "File not found!");
}
// 创建一个缓冲输入流对象
BufferedInputStream br = new BufferedInputStream(new FileInputStream(f));
byte[] buf = new byte[1024];
int len = 0;
response.reset(); // 非常重要
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + f.getName());
// 创建输出流对象
OutputStream outStream = response.getOutputStream();
// 开始输出
while ((len = br.read(buf)) > 0)
outStream.write(buf, 0, len);
// 关闭流对象
br.close();
outStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if (f.exists()) {// 下载完毕删除文件
f.delete();
}
}
}
