report导出excl

package com.zte.eta.testcenter.resources;

import com.google.common.io.Files;
import com.zte.eta.testcenter.core.sceneEditor.BlueprintTestPhase;
import com.zte.eta.testcenter.db.BuildDao;
import com.zte.eta.testcenter.db.TestJobInfoDao;
import com.zte.eta.testcenter.db.sceneEditor.TestJobDao;
import com.zte.eta.testcenter.db.sceneEditor.BlueprintDao;
import io.dropwizard.hibernate.UnitOfWork;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import java.io.*;
import java.util.*;

import org.apache.poi.xssf.usermodel.*;
import org.json.JSONObject;

/**
 * Created by huangyan on 17-9-14.
 */
@Slf4j
@Path("/report")
@AllArgsConstructor
public class ExporterReportResource {
    private BuildDao buildDao;
    private TestJobInfoDao testJobInfoDao;
    private TestJobDao testJobDao;
    private  BlueprintDao blueprintDao;

    public ExporterReportResource(BuildDao buildDao, TestJobInfoDao testJobInfoDao, TestJobDao testJobDao, BlueprintDao blueprintDao) {
        this.buildDao = buildDao;
        this.testJobInfoDao = testJobInfoDao;
        this.testJobDao = testJobDao;
        this.blueprintDao=blueprintDao;
    }


    private String[] FIRST_ROW_OF_TESTJOB = {"测试任务Id", "测试蓝图名称", "测试蓝图Id", "测试任务状态", "失败原因", "开始时间", "结束时间"};
    private String[] FIRST_ROW_OF_BLUEPRINT = {"测试任务Id", "测试蓝图Id", "测试蓝图名称", "特性Id", "特性名称", "tfsId", "tfs名称", "测试套名称", "rf用例名称", "passed", "失败原因"};

    @Path("/exportExcelFromSql")
    @GET
    @UnitOfWork
    @Produces(MediaType.APPLICATION_OCTET_STREAM)
    public Response fetchTestJobFromSql(@QueryParam("startTime") String startTimeStr,
                                        @QueryParam("endTime") String endTimeStr,
                                        @QueryParam("bluePrintId") String bluePrintIdStr
                                        ) throws Exception {
        String startTime = startTimeStr;
        String endTime = endTimeStr;
        String bluePrintId = bluePrintIdStr;
        String[] bluePrintIds=bluePrintId.split(",");
        String reg = "(\\d{4})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})";
        startTime = startTime.replaceAll(reg, "$1-$2-$3 $4:$5:$6");
        endTime = endTime.replaceAll(reg, "$1-$2-$3 $4:$5:$6");
        File dir = Files.createTempDir();
        String filePath = dir.getAbsolutePath() + "/report.xlsx";
        XSSFWorkbook workBook = new XSSFWorkbook();
        List<Object[]> testJobList = testJobDao.findTestJobByBluePrint(startTime, endTime);
        List<List<String>> testJobValueList = preProgress(testJobList);
        exportDataFromSql(workBook, filePath, FIRST_ROW_OF_TESTJOB, "汇总", testJobValueList);
        for (int i = 0; i < bluePrintIds.length; i++){
            List<Object[]> testJobInfoList = testJobInfoDao.findBluePrintIdByBluePrint(startTime, endTime, bluePrintIds[i]);
            List<List<String>> testJobInfoValueList = preProgress(testJobInfoList);
            List<String> name = testJobDao.findNameByBluePrintId(startTime, endTime,bluePrintIds[i]);
                exportDataFromSql(workBook, filePath, FIRST_ROW_OF_BLUEPRINT, name.get(0), testJobInfoValueList);
                log.info("name={},bluePrintId={}", name, bluePrintIds[i]);
        }
        log.info("export end");
        return Response.ok(new File(filePath)).
                header("Content-Disposition",  "attachment; filename=\"report.xlsx\"")
                .build();
    }

    private void exportDataFromSql(XSSFWorkbook workbook, String filepath, String[] firstRows,
                                   String name, List<List<String>> valueList) throws Exception {
        log.info("sheetName={},size={}", name, valueList.size());
        XSSFSheet sheet = workbook.createSheet(name);
        XSSFRow row = sheet.createRow((short) 0);
        setCellValue(row, Arrays.asList(firstRows));
        for (int i = 1; i <= valueList.size(); i++) {
            row = sheet.createRow(i);
            List<String> value = valueList.get(i - 1);
            setCellValue(row, value);
        }
        FileOutputStream out = new FileOutputStream(filepath);
        workbook.write(out);
        out.flush();
        out.close();
    }

    private List<List<String>> preProgress(List<Object[]> results) {
        List<List<String>> strs = new ArrayList<>();
        for (int i = 0; i <= results.size() - 1; i++) {
            Object[] result = results.get(i);
            List<String> str = new ArrayList<>();
            for (int j = 0; j <= result.length - 1; j++) {
                str.add(convertObjToString(result[j]));
            }
            strs.add(str);
        }
        return strs;
    }

    private String convertObjToString(Object o) {
        if (o != null) {
            return String.valueOf(o);
        }
        return "";
    }

    private void setCellValue(XSSFRow row, List<String> value) {
        for (int i = 0; i <= value.size() - 1; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(value.get(i));
        }
    }

    @Path("/fetchBlueprintByTestPhase")
    @GET
    @UnitOfWork
    @Produces(MediaType.APPLICATION_JSON)
    public List<BlueprintTestPhase> fetchTestPhase(@QueryParam("startTime") String startTimeStr,
                                                   @QueryParam("endTime") String endTimeStr) throws Exception {
        String startTime = startTimeStr;
        String endTime = endTimeStr;
        String reg = "(\\d{4})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})";
        startTime = startTime.replaceAll(reg, "$1-$2-$3 $4:$5:$6");
        endTime = endTime.replaceAll(reg, "$1-$2-$3 $4:$5:$6");
        List<String> bluePrintIdList = buildDao.fetchBluePrintIdByBluePrint(startTime, endTime);
        List<BlueprintTestPhase>  blueprintTestPhaseList= new ArrayList<>();
        for (String bluePrintId : bluePrintIdList){
            BlueprintTestPhase blueprintTestPhase = new BlueprintTestPhase();
            List<String> blueprint= blueprintDao.selectBlueprintFromBlueprint(bluePrintId);
            if(blueprint.size()==0){
                continue;
            }
            JSONObject blueprintJson = new JSONObject(blueprint.get(0));
            String testPhaseValue=blueprintJson.get("testPhase").toString();
            List<String> name = testJobDao.findNameByBluePrintId(startTime, endTime,bluePrintId);
            blueprintTestPhase.setName(name.get(0));
            blueprintTestPhase.setBlueprintId(bluePrintId);
            blueprintTestPhase.setTestPhase(testPhaseValue);
            blueprintTestPhaseList.add(blueprintTestPhase);
        }
        return blueprintTestPhaseList;
    }
}

转载于:https://my.oschina.net/u/2822440/blog/1551479