UserPortalStatisticExportService.java
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.ndkey.am.statistics.portal; import com.ndkey.am.tenant.TenantId; import com.ndkey.exception.DkRuntimeException; import com.ndkey.utils.RandomString; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.Calendar; import java.util.List; import org.apache.commons.io.FileUtils; import org.apache.commons.lang3.time.DateFormatUtils; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.transaction.annotation.Transactional; /** * * @author tino */ @Transactional public class UserPortalStatisticExportService { private UserPortalStatisticService userPortalStatisticService; private static final String DATE_FORMAT = "yyyy-MM-dd' 'HH:mm:ss"; private final static int TIME_INDEX = 0; private final static int TENANT_NAME_INDEX = 1; private final static int LOGIN_NAME_INDEX = 2; private final static int MOBILE_INDEX = 3; private final static int WEIXIN_OPEN_ID_INDEX = 4; private final static int FIRST_LOGIN_TIME_INDEX = 5; private final static int LAST_LOGOUT_TIME_INDEX = 6; private final static int ACCESS_DURATION_INDEX = 7; private final static int STAY_DURATION_INDEX = 8; private final static int FLOW_IN_BYTES_INDEX = 9; private String generateFileName() { return RandomString.getRandomString("qazwsxedcrfvtgbyhnujmikl1234567890", 6); } @Transactional public void exportDailyRecords(TenantId tenantId, Calendar from, Calendar to, OutputStream os) throws IOException { List<UserDailyRecordInfo> records = userPortalStatisticService.getUserDailyRecords(tenantId.getId(), from, to, 0, Integer.MAX_VALUE); this.exportDailyRecords(records, os); } @Transactional public void exportDailyRecords(Calendar from, Calendar to, OutputStream os) throws IOException { List<UserDailyRecordInfo> records = userPortalStatisticService.getUserDailyRecords(from, to, 0, Integer.MAX_VALUE); this.exportDailyRecords(records, os); } public void exportDailyRecords(List<UserDailyRecordInfo> records, OutputStream os) throws IOException { String tempPath = FileUtils.getTempDirectoryPath(); String templatePath = "META-INF/com/ndkey/am/res/report/template_user_daily.xlsx"; File excelFile = new File(tempPath + "/" + generateFileName() + ".xlsx"); try (InputStream inStream = this.getClass().getClassLoader().getResourceAsStream(templatePath)) { FileUtils.copyInputStreamToFile(inStream, excelFile); XSSFWorkbook wb; try (OPCPackage opk = OPCPackage.open(excelFile)) { wb = new XSSFWorkbook(opk); XSSFSheet sheet = wb.getSheetAt(0); int maxRowNum = sheet.getLastRowNum(); XSSFRow row = null; for (int i = 3; i < maxRowNum; i++) { row = sheet.getRow(i); if (row != null) { sheet.removeRow(row); } } int i = 3; for (UserDailyRecordInfo record : records) { row = sheet.createRow(i); row.createCell(TIME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(DateFormatUtils.format(record.getTime(), DATE_FORMAT)); row.createCell(TENANT_NAME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getTenantName()); row.createCell(LOGIN_NAME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getLoginName()); row.createCell(MOBILE_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getMobile()); row.createCell(WEIXIN_OPEN_ID_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getWeixinOpenId()); row.createCell(FIRST_LOGIN_TIME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(DateFormatUtils.format(record.getFirstLoginTime(), DATE_FORMAT)); row.createCell(LAST_LOGOUT_TIME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(DateFormatUtils.format(record.getLastLogoutTime(), DATE_FORMAT)); row.createCell(ACCESS_DURATION_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getAccessDuration() / 60); row.createCell(STAY_DURATION_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getStayDuration() / 60); row.createCell(FLOW_IN_BYTES_INDEX, Cell.CELL_TYPE_STRING).setCellValue(record.getFlowInBytes() / (1024 * 1024)); i++; } wb.write(os); } catch (InvalidFormatException ex) { throw new DkRuntimeException(ex); } finally { FileUtils.forceDelete(excelFile); } } } public void setUserPortalStatisticService(UserPortalStatisticService userPortalStatisticService) { this.userPortalStatisticService = userPortalStatisticService; } }
ReportController.java
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.ndkey.am.web.controller.operator.v3.report; import com.ndkey.am.web.controller.tenant.v2.BaseController; import java.io.IOException; import java.io.OutputStream; import java.util.Calendar; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.ndkey.am.statistics.portal.UserPortalStatisticExportService; import org.apache.commons.io.IOUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * * @author zxf */ @RestController(value = "V3OperatorReportController") @RequestMapping(value = "/operator/api/3/report") public class ReportController extends BaseController { private final Logger _logger = LoggerFactory.getLogger(ReportController.class); @Autowired private UserPortalStatisticExportService userPortalStatisticsExportService; @RequestMapping(value = "/userDaily/export") public void exportUserDaily(long from, long to, HttpServletResponse response) { OutputStream os = null; Calendar fromTime = Calendar.getInstance(); fromTime.setTimeInMillis(from); Calendar toTime = Calendar.getInstance(); toTime.setTimeInMillis(to); try { response.reset(); response.setContentType("application/octet-stream;charset=UTF-8"); os = response.getOutputStream(); userPortalStatisticsExportService.exportDailyRecords(fromTime, toTime, os); } catch (IOException e) { _logger.error(e.getMessage(), e); } finally { IOUtils.closeQuietly(os); } } }