CreateExcel - genscript-lz/manufacturing GitHub Wiki

private File createExcel(List list) throws Exception { String shortName = "/tmp/ExcelForOperation/"; String separator = System.getProperty("file.separator"); if (separator.equals("\")) {// windows shortName = "C:" + shortName; } else {// linux etc.

    }
    String random = SessionUtil.generateTempId();
    File xmlFile = new File(shortName + "workOrderOperationList_" + random + ".xls");
    if (!xmlFile.getParentFile().exists()) {
        if (!xmlFile.getParentFile().mkdirs()) {
            logger.info("create parent directory fail");
            return null;
        }
    }
    FileOutputStream out = new FileOutputStream(xmlFile);
    WritableWorkbook wbook = Workbook.createWorkbook(out);
    WritableSheet wsheet = wbook.createSheet("Work order operation list", 0); // 工作表名称
    WritableSheet wsheet2 = wbook.createSheet("Gene part", 1); // 工作表名称
    // 设置Excel字体
    WritableFont wfont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    titleFormat = new WritableCellFormat(wfont);
    contentFormat = new WritableCellFormat();
    titleFormat.setShrinkToFit(true);
    contentFormat.setShrinkToFit(true);

    String workCenterId = Struts2Util.getParameter("filter_EQI_workCenterId");
    boolean geneCenter = false;
    if(StringUtil.isNumeric(workCenterId)) {
        WorkCenter wc = this.setupService.getWorkCenter(Integer.parseInt(workCenterId));
        if(WorkCenterNameConstant.Gene.key().equals(wc.getCenterType())) {
            geneCenter = true;
        }
    }

    String[] title = { "Work_NO", "Order_NO", "OrderNumber_no", "Description", "Supply Confirm Date", "Sch Prod Complete Date", "Manager", "Length", "TPrice", "TAM", "CloningSite1", "CloningSite2", "Quantity", "VectorName", "Internal/Sales", "Attachment", "Priority", "Catalog No", "Shippable", "target construct name", "on demand/In store", "CloneId", "order/item complete","VIP Tag" };
    String[] title2 = { "Work_NO", "Order_NO", "OrderNumber_no","Org Name","Cust Name", "Supply Confirm Date", "Sch Prod Complete Date","Gene Name","Sequence","Length","Protective bases","Quantity","Test Complexity","Nonstandard info","TPrice", "TAM", "Internal/Sales","Attachment","Priority", "Catalog No", "Shippable","Manager","VIP Tag"};

    // 设置Excel表头
    for (int i = 0; i < title.length; i++) {
        Label excelTitle = new Label(i, 0, title[i], titleFormat);
        wsheet.addCell(excelTitle);
    }
    // 设置Excel表头
    for (int i = 0; i < title2.length; i++) {
        Label excelTitle = new Label(i, 0, title2[i], titleFormat);
        wsheet2.addCell(excelTitle);
    }

    // 如果是基因 Worker Center
    if (geneCenter) {
        Label excelTitle = new Label(title2.length, 0, "Simple And Small Gene Flag", titleFormat);
        wsheet.addCell(excelTitle);
        Label excelTitle2 = new Label(title2.length+1, 0, "Primer Generate Flag", titleFormat);
        wsheet.addCell(excelTitle2);
    }

    int c = 1; // 用于循环时Excel的行号
    int c2 = 1;
    if (list != null) {
        for (WorkOrderExcelDTO workOrder : list) {
            if("SC1869".equals(workOrder.getCatalogNo()) || "SC1930".equals(workOrder.getCatalogNo())) {
                createGenepart(workOrder,c2,wsheet2,false);
                c2++;
            } else {
                createNoramlWo(workOrder,c,wsheet,geneCenter);
                c++;
            }
        }
    }

    wsheet.setColumnView(0, 12);
    wsheet.setColumnView(1, 12);
    wsheet.setColumnView(2, 12);
    wsheet.setColumnView(3, 120);
    wsheet.setColumnView(4, 25);
    wsheet.setColumnView(5, 20);
    wsheet.setColumnView(19, 50);
    wsheet.setColumnView(20, 50);
    
    wsheet2.setColumnView(0, 12);
    wsheet2.setColumnView(1, 12);
    wsheet2.setColumnView(2, 12);
    wsheet2.setColumnView(8, 120);
    wsheet2.setColumnView(17, 50);
    wsheet2.setColumnView(19, 50);

    if (geneCenter) {
        wsheet.setColumnView(21, 30);
        wsheet.setColumnView(22, 30);
    }

    wbook.write(); // 写入文件
    wbook.close();
    out.close();
    return xmlFile;
}