Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow.

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:javaapp.generateAnomolies.java

public static void generateAnomolyReport() throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook();

    HashMap<String, List<String>> hmd = new HashMap<String, List<String>>();
    HashMap<String, List<String>> hmc = new HashMap<String, List<String>>();
    HashMap<String, List<String>> uhmd = new HashMap<String, List<String>>();
    HashMap<String, List<String>> uhmc = new HashMap<String, List<String>>();

    hmd = getAnomolies("transrec", "Debtors", 0);
    hmc = getAnomolies("ctransrec", "Creditors", 1);
    uhmd = getAnomoliesUninv("uninv", "Uninv_Debtors", 2);
    uhmc = getAnomoliesUninv("cuninv", "Uninv_Creditors", 3);

    List ls;//from www  . j  av a  2s.  c o  m
    List ls2, ls3, ls4;

    Font font = workbook.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setFontName("Cambria");

    Font hfont = workbook.createFont();
    hfont.setFontHeightInPoints((short) 12);
    hfont.setFontName("Cambria");

    CellStyle style = workbook.createCellStyle();
    CellStyle hstyle = workbook.createCellStyle();

    style.setFont(font);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);

    hstyle.setFont(font);
    hstyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    hstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    //System.out.println(hm);
    XSSFSheet sheet = workbook.createSheet("Debtors");
    XSSFSheet sheet2 = workbook.createSheet("Creditors");
    XSSFSheet sheet3 = workbook.createSheet("Uninv_Debtors");
    XSSFSheet sheet4 = workbook.createSheet("Uninv_Creditors");

    Iterator iterator = hmd.keySet().iterator();

    int rowCount = 0;
    Row row = sheet.createRow(0);
    Cell hcell = row.createCell(1);
    hcell.setCellValue((String) "RPPS");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(2);
    hcell.setCellValue((String) "SVC");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(3);
    hcell.setCellValue((String) "OPEN");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(4);
    hcell.setCellValue((String) "RINVOICE");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(5);
    hcell.setCellValue((String) "CORRECTION");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(6);
    hcell.setCellValue((String) "ADJUST");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(7);
    hcell.setCellValue((String) "O1C");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(8);
    hcell.setCellValue((String) "SETTLED");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(9);
    hcell.setCellValue((String) "ALLOC");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(10);
    hcell.setCellValue((String) "WRITEOFF");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(11);
    hcell.setCellValue((String) "CLOSE");
    hcell.setCellStyle(hstyle);
    hcell = row.createCell(12);
    hcell.setCellValue((String) "RECDIFF");
    hcell.setCellStyle(hstyle);

    while (iterator.hasNext()) {

        row = sheet.createRow(++rowCount);
        int columnCount = 0;
        String field = "";

        String key = iterator.next().toString();
        field = key;
        Cell cell = row.createCell(++columnCount);

        if (field instanceof String) {
            cell.setCellValue((String) field);
            cell.setCellStyle(style);
        }

        //System.out.println(hm.get(key));
        ls = (List<String>) hmd.get(key);
        System.out.println(ls);
        Iterator<String> ite = ls.iterator();
        while (ite.hasNext()) {
            field = ite.next();
            //System.out.println( ite.next() );
            cell = row.createCell(++columnCount);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            }

            cell.setCellStyle(style);

        }

    }

    Iterator iterator2 = hmc.keySet().iterator();
    rowCount = 0;

    while (iterator2.hasNext()) {

        row = sheet2.createRow(++rowCount);
        int columnCount = 0;
        String field = "";

        String key = iterator2.next().toString();
        field = key;
        Cell cell = row.createCell(++columnCount);
        if (field instanceof String) {
            cell.setCellValue((String) field);
            cell.setCellStyle(style);
        }

        //System.out.println(hm.get(key));
        ls2 = (List<String>) hmc.get(key);
        System.out.println(ls2);
        Iterator<String> ite = ls2.iterator();
        while (ite.hasNext()) {
            field = ite.next();
            //System.out.println( ite.next() );
            cell = row.createCell(++columnCount);
            if (field instanceof String) {
                cell.setCellValue((String) field);
                cell.setCellStyle(style);
            }

        }

    }

    // uninv debtors

    Iterator iterator3 = uhmd.keySet().iterator();
    rowCount = 0;

    while (iterator3.hasNext()) {

        row = sheet3.createRow(++rowCount);
        int columnCount = 0;
        String field = "";

        String key = iterator3.next().toString();
        field = key;
        Cell cell = row.createCell(++columnCount);
        if (field instanceof String) {
            cell.setCellValue((String) field);
            cell.setCellStyle(style);
        }

        //System.out.println(hm.get(key));
        ls3 = (List<String>) hmc.get(key);
        System.out.println(ls3);
        Iterator<String> ite = ls3.iterator();
        while (ite.hasNext()) {
            field = ite.next();
            //System.out.println( ite.next() );
            cell = row.createCell(++columnCount);
            if (field instanceof String) {
                cell.setCellValue((String) field);
                cell.setCellStyle(style);
            }

        }

    }

    // uninv creditors

    Iterator iterator4 = uhmc.keySet().iterator();
    rowCount = 0;

    while (iterator4.hasNext()) {

        row = sheet4.createRow(++rowCount);
        int columnCount = 0;
        String field = "";

        String key = iterator4.next().toString();
        field = key;
        Cell cell = row.createCell(++columnCount);
        if (field instanceof String) {
            cell.setCellValue((String) field);
            cell.setCellStyle(style);
        }

        //System.out.println(hm.get(key));
        ls4 = (List<String>) uhmc.get(key);
        System.out.println(ls4);
        Iterator<String> ite = ls4.iterator();
        while (ite.hasNext()) {
            field = ite.next();
            //System.out.println( ite.next() );
            cell = row.createCell(++columnCount);
            if (field instanceof String) {
                cell.setCellValue((String) field);
                cell.setCellStyle(style);
            }

        }

    }
    /*
    Object[][] bookData = {
        {"Head First Java", "Kathy Serria", 79},
        {"Effective Java", "Joshua Bloch", 36},
        {"Clean Code", "Robert martin", 42},
        {"Thinking in Java", "Bruce Eckel", 35},
    };
            
            
            
            
            
     //int rowCount = 0;
    for (Object[] aBook : bookData) {
    Row row = sheet.createRow(++rowCount);
             
    int columnCount = 0;
             
    for (Object field : aBook) {
        Cell cell = row.createCell(++columnCount);
        if (field instanceof String) {
            cell.setCellValue((String) field);
        } else if (field instanceof Integer) {
            cell.setCellValue((Integer) field);
        }
    }
             
    }
            
    */
    // workbook.setSheetName(n, WorkbookUtil.createSafeSheetName("Debtors"));
    try (FileOutputStream outputStream = new FileOutputStream("anomolies/GBRCN_Anomolies_27April.xlsx")) {
        workbook.write(outputStream);
    }

}

From source file:javaapplication1.Graph.java

void export() {

    XSSFWorkbook workbook = new XSSFWorkbook();
    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("data");
    //This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    for (int i = 1; i <= values.length; i++)
        data.put("1", new Object[] { values });
    //Iterate over data and write to sheet
    int rownum = 0;
    Row row = sheet.createRow(rownum++);

    int cellnum = 0;

    for (int i : values) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue((Integer) i);
    }/*from   ww w.j a  v a2s .  c  om*/

    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File("data.xlsx"));
        workbook.write(out);
        out.close();

        System.out.println("data.xlsx written successfully on disk.");

    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:jlib.tool.Writer.java

public static void write2Excel(List<List> input, String filename) {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Amazon");

    int rowCount = 0;
    for (List aBook : input) {
        Row row = sheet.createRow(rowCount++);

        int columnCount = 0;
        for (Object field : aBook) {
            Cell cell = row.createCell(columnCount++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }//from   ww  w.ja v a2  s.c o  m
        }
    }

    try (FileOutputStream outputStream = new FileOutputStream(filename)) {
        workbook.write(outputStream);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Writer.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Writer.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:jp.ac.utokyo.rcast.karkinos.summary.DataWriter.java

License:Apache License

public static void writeTr(DataReader dr, XSSFSheet sheet, List<String> sampleL) {

    List<String> title = new ArrayList<String>();
    title.add("sample");
    title.add("tumor rate");
    title.add("s.d.");
    title.add("source");
    title.add("number of hetro snp used");
    title.add("correl");
    title.add("ploidy");
    // title.addAll(sampleL);
    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);//from w w  w  . j a v  a2 s  . c o  m

    int col = 1;
    for (String sid : sampleL) {
        Row row1 = sheet.createRow(col);
        List data1 = new ArrayList();
        Filebean fb = dr.getBean(sid);

        TCBean tcbean = fb.getTCBean();
        float tr = fb.getTr();
        float sd = fb.getTCBean().sd;
        int nosnp = fb.getTCBean().nosnp;
        float correl = fb.getTCBean().correl;
        float ploidy = fb.getTCBean().ploidy;
        if (tr > 1) {
            tr = 0;
        }
        data1.add(sid);
        data1.add(tr);
        data1.add(sd);
        data1.add("n=" + fb.getTCBean().takefrom);
        data1.add(nosnp);
        data1.add(correl);
        data1.add(ploidy);
        setRow(data1, row1);
        col++;
    }

}

From source file:jp.ac.utokyo.rcast.karkinos.summary.DataWriter.java

License:Apache License

public static void writeCNV(DataReader dr, XSSFSheet sheet, List<String> sampleL, int flg) {

    List<String> title = new ArrayList<String>();
    title.add("sample id");
    title.add("copy number");
    title.add("chrom");
    title.add("start");
    title.add("end");

    if (flg == 1) {
        title.add("number of hetro SNP");
        title.add("AAF");
        title.add("BAF");
    }/*  w  w w  .j av a 2s  .  c  o  m*/

    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);

    int cnt = 1;
    for (String sid : sampleL) {
        Filebean fb = dr.getBean(sid);
        for (CNAInterval cintvl : fb.cnalist) {

            if (flg == 1) {
                if (!cintvl.type.contains("Depth")) {
                    continue;
                }
            }
            if (flg == 2) {
                if (!cintvl.type.contains("allelic")) {
                    continue;
                }
            }
            if (flg == 3) {
                boolean b = cintvl.type.contains("HD") || cintvl.type.contains("Amp");
                if (!b) {
                    continue;
                }
            }

            Row row = sheet.createRow(cnt);
            List data1 = new ArrayList();
            data1.add(sid);
            data1.add(cintvl.cn);
            data1.add(cintvl.chr);
            data1.add(cintvl.start);
            data1.add(cintvl.end);
            if (flg == 1) {
                data1.add(cintvl.noSNP);
                //data1.add(cintvl.varidated);
                data1.add(cintvl.aaf);
                data1.add(cintvl.baf);
            }
            setRow(data1, row);
            cnt++;
        }

    }

}

From source file:jp.ac.utokyo.rcast.karkinos.summary.DataWriter.java

License:Apache License

public static void writeReadsStats(DataReader dr, XSSFSheet sheet, List<String> sampleL) {
    List<String> title = new ArrayList<String>();
    title.add("type");
    //String anysample = "";

    Set<String> keyset = new LinkedHashSet<String>();

    for (String s : sampleL) {
        title.add(s + ":normal");
        title.add(s + ":tumor");
        Filebean fb = dr.getBean(s);//from   w  ww  .  ja  v  a  2s . com
        keyset.addAll(fb.datamap.keySet());
    }

    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);

    //Filebean fb = dr.getBean(anysample);
    //Iterator<String> ite = fb.datamap.keySet().iterator();

    int col = 1;
    for (String key : keyset) {
        //
        //String key = ite.next();
        List data = new ArrayList();
        data.add(key);
        for (String s : sampleL) {
            Filebean fba = dr.getBean(s);
            String[] adata = fba.datamap.get(key);
            if (adata != null) {
                data.add(toNumber(adata[1]));
                data.add(toNumber(adata[2]));
            } else {
                data.add(0);
                data.add(0);
            }
        }
        Row row = sheet.createRow(col);
        setRow(data, row);
        col++;
    }

}

From source file:jp.ac.utokyo.rcast.karkinos.summary.DataWriter.java

License:Apache License

public static void writeCNVCBAL(DataReader dr, XSSFSheet sheet, List<String> sampleL, ChromBand cband,
        CellStyle[] csa, boolean high) throws SQLException {

    List<String> title = new ArrayList<String>();
    title.add("chr");
    title.add("start");
    title.add("end");
    title.add("name");
    title.add("gieStain");
    title.add("key");
    title.add("mean");
    title.add("sd");
    title.addAll(sampleL);//from   w  w  w .j  a v a2 s  .  c  om

    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);
    int col = 1;
    for (CNAInterval band : cband.getList()) {

        List data = new ArrayList();
        data.add(band.chr);
        data.add(band.start);
        data.add(band.end);
        data.add(band.name);
        data.add(band.getGieStain());
        data.add(band.chr + ":" + band.name);
        SummaryStatistics ss = new SummaryStatistics();

        for (String sid : sampleL) {

            //
            Filebean fb = dr.getBean(sid);
            float intersectval = 1;
            if (high) {
                intersectval = intersect(band, fb, high);
            } else {
                intersectval = intersect(band, fb, high);
            }

            if (intersectval <= 1) {
                ss.addValue(intersectval);
            }
            if (intersectval >= 1) {
                ss.addValue(intersectval);
            }

        }
        data.add(ss.getMean());
        data.add(ss.getStandardDeviation());

        for (String sid : sampleL) {

            //
            Filebean fb = dr.getBean(sid);
            float intersectval = 1;
            if (high) {
                intersectval = intersect(band, fb, high);
            } else {
                intersectval = intersect(band, fb, high);
            }

            data.add(intersectval);

        }

        Row row = sheet.createRow(col);
        setRow(data, row);
        setCol(data, row, csa);
        col++;
    }

}

From source file:jp.ac.utokyo.rcast.karkinos.summary.DataWriter.java

License:Apache License

public static void writeCNVCB(DataReader dr, XSSFSheet sheet, List<String> sampleL, ChromBand cband,
        CellStyle[] csa, boolean focal) throws SQLException {

    List<String> title = new ArrayList<String>();
    title.add("chr");
    title.add("start");
    title.add("end");
    title.add("name");
    title.add("gieStain");
    title.add("key");
    title.add("highmean");
    title.add("sd");
    title.add("lowmean");
    title.add("sd");
    title.addAll(sampleL);//  ww w. j av  a  2  s  . c  om

    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);
    int col = 1;
    for (CNAInterval band : cband.getList()) {

        List data = new ArrayList();
        data.add(band.chr);
        data.add(band.start);
        data.add(band.end);
        data.add(band.name);
        data.add(band.getGieStain());
        data.add(band.chr + ":" + band.name);
        SummaryStatistics sshigh = new SummaryStatistics();
        SummaryStatistics sslow = new SummaryStatistics();
        for (String sid : sampleL) {

            //
            Filebean fb = dr.getBean(sid);
            float intersectval = intersect(band, fb, focal, fb.getTCBean().ploidy);
            if (intersectval <= 2) {
                sslow.addValue(intersectval);
            }
            if (intersectval >= 2) {
                sshigh.addValue(intersectval);
            }

        }
        data.add(sshigh.getMean());
        data.add(sshigh.getStandardDeviation());
        data.add(sslow.getMean());
        data.add(sslow.getStandardDeviation());

        for (String sid : sampleL) {

            //
            Filebean fb = dr.getBean(sid);
            float intersectval = intersect(band, fb, focal, fb.getTCBean().ploidy);
            data.add(intersectval);

        }

        Row row = sheet.createRow(col);
        setRow(data, row);
        setCol(data, row, csa);
        col++;
    }

}

From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryDB.java

License:Apache License

public void geneStat(DataReader dr, XSSFSheet sheet, boolean bfilter, CellStyle[] cs, boolean aaChange,
        XSSFWorkbook wb, long caplength, boolean HDAmp, int minrecurrent, boolean varidateOnly)
        throws SQLException {

    sampleL = getSAmpleL();//from w  w  w . j ava 2s  . c  om
    setBackGroundMutationRate(sampleL, backGroundMutationRate, bfilter, aaChange, caplength);

    List<String> title = new ArrayList<String>();

    title.add("chr");
    title.add("gene");
    title.add("cosmic");

    title.add("distinct pos");

    if (aaChange == false) {

        title.add("synonymous");
        title.add("splice site");
        title.add("AA change");

    }
    //
    title.add("pval");
    // title.add("pval2");
    title.add("log_likehood");
    if (aaChange) {
        title.add("gene cds length");
    } else {
        title.add("gene length");
    }
    title.add("num/kb");

    title.add("observed sample");
    title.add("%sample");
    title.add("Indel");
    title.add("total");
    // mutation
    title.add("LJB_PhyloP_Pred");
    title.add("LJB_SIFT_Pred");
    title.add("LJB_PolyPhen2_Pred");
    title.add("LJB_LRT_Pred");
    title.add("LJB_MutationTaster_Pred");
    int dsratidx = title.size();
    int dendidx = title.size() + sampleL.size();
    //
    title.addAll(sampleL);
    title.add("description");

    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);

    String sql = "select chrom,count(*) as n,group_concat(sample_ID) as m ,geneSymbol from csvdata ";

    if (aaChange) {
        sql = sql + " where ExonicFunc !='synonymous SNV' and length(ExonicFunc) > 0 "
                + "and Func in ('exonic','exonic;splicing','splicing') ";
    }
    if (bfilter) {
        sql = sql + getAndString(sql);
        sql = sql + "  Filter2='PASS'  ";
    }

    if (varidateOnly) {

        sql = sql + getAndString(sql);
        sql = sql + " Validated=TRUE  ";
    }

    sql = sql + " group by geneSymbol ,chrom order by n desc , geneSymbol ";
    PreparedStatement crs = memorycon.prepareStatement(sql);

    ResultSet rs = crs.executeQuery();
    List<List> pList = new ArrayList<List>();
    while (rs.next()) {

        List data = new ArrayList();
        String samples = rs.getString("m");
        String chrom = rs.getString("chrom");
        String gene = rs.getString("geneSymbol");

        int ncnt = rs.getInt("n");
        //System.out.println(gene + "\t"+ ncnt);
        if (aaChange) {
            if (ncnt < minrecurrent || lessthanP(0.02, ncnt, sampleL.size())) {
                continue;
            }
        } else {
            if (ncnt < minrecurrent || lessthanP(0.04, ncnt, sampleL.size())) {
                continue;
            }
        }

        // if(!gene.contains("TTN")){
        // continue;
        // }

        String gene2 = gene;
        if (gene2.contains(";")) {
            gene2 = gene2.substring(0, gene2.indexOf(";"));
        }

        data.add(chrom);
        data.add(gene);
        // cosmic
        String cosmicgene = getStr("select cosmic from generef where geneName ='" + gene2 + "'");
        // data.add(notEmpty(cosmicgene));
        if (cosmicgene == null) {
            cosmicgene = "0";
        }
        data.add(!cosmicgene.equals("0"));
        //
        int total = 0;
        int samplecnt = 0;

        // title.add("Indel");
        // title.add("distinct pos");
        //
        // if(aaChange==false){
        //
        // title.add("synonymous");
        // title.add("splice");
        // title.add("AA change");
        //
        // }

        data.add(getCountSQL(aaChange, bfilter,
                "select count(distinct pos) from csvdata where geneSymbol ='" + gene + "'"));

        if (aaChange == false) {
            data.add(getCountSQL(aaChange, bfilter, "select count(*) from csvdata where geneSymbol ='" + gene
                    + "'" + " and ExonicFunc ='synonymous SNV' "));

            data.add(getCountSQL(aaChange, bfilter, "select count(*) from csvdata where geneSymbol ='" + gene
                    + "'" + " and Func in ('splicing','exonic;splicing')"));

            data.add(getCountSQL(aaChange, bfilter,
                    "select count(*) from csvdata where geneSymbol ='" + gene + "'"
                            + " and Func in ('exonic','exonic;splicing') and "
                            + " ExonicFunc !='synonymous SNV'"));
        }

        // System.out.println("time1=" +
        // (Calendar.getInstance().getTime().getTime() - start.getTime()));
        // gene length,num/kb
        int genelength = getGenelength(gene2, aaChange);

        double pvaltotal = 0;
        List datatmp = new ArrayList();
        int allscnt = 0;

        double likehood = 0;
        //
        // if(gene2.contains("TGIF2LX")){
        // System.out.println("here");
        // }
        for (String sampleid : sampleL) {
            String tmp = "";
            int countMutation = count(samples, sampleid);
            allscnt++;
            if (countMutation > 0) {
                samplecnt++;
                total = total + countMutation;
                String dispStr = getDispStr(sampleid, gene, aaChange, bfilter);
                tmp = dispStr;
                datatmp.add(dispStr);

            } else {

                if (HDAmp) {
                    String hdamp = checkHDAmp(dr, sampleid, gene2);
                    if (hdamp != null && hdamp.length() > 0) {
                        countMutation = 1;
                        samplecnt++;
                        total = total + countMutation;
                        String dispStr = hdamp;
                        tmp = dispStr;
                        datatmp.add(dispStr);
                    } else {
                        datatmp.add("0");
                    }
                } else {
                    datatmp.add("0");
                }

            }

            double pvaleach = getPval(sampleid, backGroundMutationRate, gene, aaChange, bfilter, genelength,
                    tmp);
            // System.out.println(sampleid+"\t"+pvaleach+"\t"+likehood);
            likehood = likehood + (-1 * Math.log(pvaleach));

        }

        String kbratio = ratio((double) ((double) total / (double) sampleL.size()),
                ((double) genelength / (double) 1000));
        //

        double pval = 1;
        try {
            pval = ProbabilityUtils.getPValbySample(backGroundMutationRate, genelength, likehood);

            System.out.println("pval= " + pval + "\t" + gene + "\t" + genelength);

        } catch (MathException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // System.out.println("time5=" +
        // (Calendar.getInstance().getTime().getTime() - start.getTime()));
        data.add(pval);
        data.add(likehood);
        data.add(genelength);
        data.add(kbratio);

        //
        data.add(samplecnt);
        data.add(percent(samplecnt, allscnt));

        data.add(getCountSQL(aaChange, bfilter,
                "select count(*) from csvdata where geneSymbol ='" + gene2 + "'" + " and Indel ='Yes'"));
        data.add(total);

        // ("LJB_PhyloP_pred");
        data.add(getConutStrSQL(gene2, "LJB_PhyloP_Pred"));
        // ("LJB_SIFT_Pred");
        data.add(getConutStrSQL(gene2, "LJB_SIFT_Pred"));
        // ("LJB_PolyPhen2_Pred");
        data.add(getConutStrSQL(gene2, "LJB_PolyPhen2_Pred"));
        // ("LJB_LTR_Pred");
        data.add(getConutStrSQL(gene2, "LJB_LRT_Pred"));
        // ("LRT_MutationTaster_Pred");
        data.add(getConutStrSQL(gene2, "LJB_MutationTaster_Pred"));
        //
        data.addAll(datatmp);

        // ("description");
        String desc = getStr("select description from generef where geneName ='" + gene2 + "'");
        if (desc == null) {
            desc = "";
        }
        data.add(desc);

        String desccosmic = getStr("select cosmicdesc from generef where geneName ='" + gene2 + "'");
        if (desccosmic == null) {
            desccosmic = "";
        }
        data.add(desccosmic);

        pList.add(data);

        // System.out.println("time6=" +
        // (Calendar.getInstance().getTime().getTime() - start.getTime()));
        //

    }
    rs.close();

    sort(pList, aaChange);

    int colidx = 1;
    for (List data : pList) {

        int maxcolor = data.size();
        Row row = sheet.createRow(colidx);
        setRow(data, row, wb);
        setColor(data, row, cs, dsratidx, dendidx);
        colidx++;
    }

}

From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryDB.java

License:Apache License

public int substitution_Stat(XSSFSheet sheet, int colcount, boolean all, boolean bfilter) throws SQLException {

    sampleL = getSAmpleL();//from  ww  w  . j  a v a 2  s  . co m

    List<String> title = new ArrayList<String>();
    title.add("mutation type\\sample");
    title.addAll(sampleL);
    title.add("total");
    title.add("type");

    Row rowtitle = sheet.createRow(colcount);
    setRow(title, rowtitle);
    colcount++;

    String sql = "select ALT,REF,Indel,count(*) as c,concat(REF,'to',ALT) as m, sample_ID from csvdata ";
    if (!all) {
        sql = sql
                //               + " where ExonicFunc !='synonymous SNV' and length(ExonicFunc) > 0 "
                //               + "and Func in ('exonic','splicing','exonic;splicing','splicing') ";
                + " where ExonicFunc !='synonymous SNV'  "
                + "and Func in ('exonic','splicing','exonic;splicing','splicing') ";
    }
    if (bfilter) {
        sql = sql + getAndString(sql);
        sql = sql + " Filter2='PASS' ";
    }
    sql = sql + " group by m,sample_ID order by m,sample_ID ";
    PreparedStatement crs = memorycon.prepareStatement(sql);

    ResultSet rs = crs.executeQuery();
    Map<String, Integer> m = new LinkedHashMap<String, Integer>();

    while (rs.next()) {

        int cnt = rs.getInt("c");
        String ref = rs.getString("m");

        String Indel = rs.getString("Indel");
        String sample = rs.getString("sample_ID");
        String key = "";
        if (Indel == null) {
            key = GenotypeKeyUtils.aggrigateKeys(ref);
            // System.out.println(dispkey+"\t"+sample+ "\t"+cnt+"\t"+Indel);
        } else {

            boolean del = rs.getString("REF").length() > rs.getString("ALT").length();
            if (del) {
                key = "deletion";
            } else {
                key = "insersion";
            }

        }

        String mkey = sample + ":" + key;
        //
        if (m.containsKey(mkey)) {
            cnt = cnt + m.get(mkey);
            m.put(mkey, cnt);
        } else {
            m.put(mkey, cnt);
        }

    }
    rs.close();

    ArrayList<String> al = new ArrayList<String>();
    al.addAll(Arrays.asList(GenotypeKeyUtils.keys1));
    al.add("insersion");
    al.add("deletion");

    Map<String, Integer> totalbysample = new HashMap<String, Integer>();
    for (String skey : al) {
        ArrayList data = new ArrayList();
        String dispkey = skey;
        try {
            dispkey = GenotypeKeyUtils.toDispKey(skey);
        } catch (ArrayIndexOutOfBoundsException ex) {

        }
        // System.out.print(dispkey+"\t");
        data.add(dispkey);
        int typetotal = 0;
        for (String sample_ID : sampleL) {
            String mkey2 = sample_ID + ":" + skey;
            Integer cnt2 = m.get(mkey2);
            if (cnt2 == null) {
                cnt2 = 0;
            }
            // System.out.print(cnt2+"\t");
            data.add(cnt2);
            typetotal = typetotal + cnt2;
            if (totalbysample.containsKey(sample_ID)) {
                int ll = totalbysample.get(sample_ID);
                ll = ll + cnt2;
                totalbysample.put(sample_ID, ll);
            } else {
                totalbysample.put(sample_ID, cnt2);
            }
        }
        data.add(typetotal);
        if (all) {
            data.add("all pass filter");
        } else {
            data.add("AA alternation");
        }
        Row row = sheet.createRow(colcount);
        setRow(data, row);
        colcount++;
    }
    ArrayList data = new ArrayList();
    data.add("total");
    int nettotal = 0;
    for (String sample_ID : sampleL) {
        int nn = totalbysample.get(sample_ID);
        data.add(nn);
        nettotal = nettotal + nn;
    }
    data.add(nettotal);
    Row row = sheet.createRow(colcount);
    setRow(data, row);

    return colcount;

}