• java导出xlsx文件


    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);
            }
        }
    }
  • 相关阅读:
    jmeter接口测试二
    jmeter 插件入口
    Python正则匹配中的最小匹配和贪婪匹配
    python中的url编码和解码(encode与decode)乱码
    python2.7+pyqt+eric基本控件操作(制作界面化程序)
    python2.7+PyQt4+eric6 界面开发环境配置
    centos配置静态ip地址
    分片,步长,索引
    我看过的几本书籍
    软件测试工程师的成长之路(个人看法)
  • 原文地址:https://www.cnblogs.com/littlehoom/p/4704469.html
Copyright © 2020-2023  润新知