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);
        }
    }
}