java poi 导出xlsx_java导出xlsx文件

/** 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.*/

packagecom.ndkey.am.statistics.portal;importcom.ndkey.am.tenant.TenantId;importcom.ndkey.exception.DkRuntimeException;importcom.ndkey.utils.RandomString;importjava.io.File;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.util.Calendar;importjava.util.List;importorg.apache.commons.io.FileUtils;importorg.apache.commons.lang3.time.DateFormatUtils;importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;importorg.apache.poi.openxml4j.opc.OPCPackage;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.springframework.transaction.annotation.Transactional;/***

*@authortino*/@Transactionalpublic classUserPortalStatisticExportService {privateUserPortalStatisticService 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;privateString generateFileName() {return RandomString.getRandomString("qazwsxedcrfvtgbyhnujmikl1234567890", 6);

}

@Transactionalpublic void exportDailyRecords(TenantId tenantId, Calendar from, Calendar to, OutputStream os) throwsIOException {

List records = userPortalStatisticService.getUserDailyRecords(tenantId.getId(), from, to, 0, Integer.MAX_VALUE);this.exportDailyRecords(records, os);

}

@Transactionalpublic void exportDailyRecords(Calendar from, Calendar to, OutputStream os) throwsIOException {

List records = userPortalStatisticService.getUserDailyRecords(from, to, 0, Integer.MAX_VALUE);this.exportDailyRecords(records, os);

}public void exportDailyRecords(List records, OutputStream os) throwsIOException {

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= newXSSFWorkbook(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 newDkRuntimeException(ex);

}finally{

FileUtils.forceDelete(excelFile);

}

}

}public voidsetUserPortalStatisticService(UserPortalStatisticService userPortalStatisticService) {this.userPortalStatisticService =userPortalStatisticService;

}

}