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

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

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

Introduction

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

Prototype

@Override
public void setColumnHidden(int columnIndex, boolean hidden)

Source Link

Document

Get the visibility state for a given column.

Usage

From source file:coverageqc.functions.MyExcelEditor.java

public static void excelFormator(XSSFSheet currentSheet, File variantTsvFile, String tsvHeadingLine)
        throws IOException {
    String[] headingsArray = tsvHeadingLine.split("\t");
    HashMap<String, Integer> headings = new HashMap<String, Integer>();
    for (int x = 0; x < headingsArray.length; x++) {
        headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x);
    }/*from   w  w  w  . java  2 s .  co m*/

    XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup();

    File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx");
    currentSheet.getHeader().setLeft(xslxTempFile.getName());
    currentSheet.getHeader().setRight("DO NOT DISCARD!!!  Keep with patient folder.");
    //in Dr. Carter's VBA was set at points 18 which is .25 inches
    currentSheet.setMargin(Sheet.RightMargin, .25);
    currentSheet.setMargin(Sheet.LeftMargin, .25);

    printSetup.setOrientation(PrintOrientation.LANDSCAPE);

    //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this 
    //is the best workaround I can do, it will only looked cramped for those with a lot of calls
    printSetup.setFitWidth((short) 1);
    printSetup.setFitHeight((short) 3);
    currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1"));
    currentSheet.setFitToPage(true);
    //making it by default not print the fellow's interp
    currentSheet.getWorkbook().setPrintArea(0, 1, currentSheet.getRow(0).getPhysicalNumberOfCells(), 0,
            currentSheet.getLastRowNum());

    for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) {
        currentSheet.autoSizeColumn(x);
    }
    currentSheet.setColumnWidth(0, 10000);
    currentSheet.setColumnWidth(1, 10000);

    currentSheet.setColumnWidth(headings.get("Consequence").intValue() + 2, 3500);
    currentSheet.setColumnHidden(headings.get("Classification").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Inherited From").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Allelic Depths").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Custom Annotation").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Custom Gene Annotation").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Num Transcripts").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Transcript").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("cDNA Position").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("CDS Position").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Protein Position").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Amino Acids").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Codons").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("HGNC").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Transcript HGNC").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Canonical").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Sift").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("PolyPhen").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("ENSP").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("HGVSc").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("HGVSp").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("dbSNP ID").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Ancestral Allele").intValue() + 2, true);
    currentSheet.setColumnHidden(headings.get("Allele Freq").intValue() + 2, true);
    //everything beyond this is hidden
    for (int x = headings.get("Global Minor Allele").intValue() + 2; x < currentSheet.getRow(0)
            .getPhysicalNumberOfCells(); x++) {
        currentSheet.setColumnHidden(x, true);
    }

}

From source file:coverageqc.functions.MyExcelGenerator.java

public void excelFormator(String sheetName, File variantTsvFile) throws IOException {
    // String[] headingsArray = tsvHeadingLine.split("\t");
    // HashMap<String, Integer> headings = new HashMap<String, Integer>();
    // for(int x = 0; x < headingsArray.length; x++) {
    //     headings.put(headingsArray[x].substring(0, headingsArray[x].indexOf("_")), x);
    // }/*  ww w. j a  v  a  2 s.  c o m*/
    XSSFSheet currentSheet = this.workbookcopy.getSheet(sheetName);

    XSSFPrintSetup printSetup = (XSSFPrintSetup) currentSheet.getPrintSetup();

    File xslxTempFile = new File(variantTsvFile.getCanonicalPath() + ".coverage_qc.xlsx");
    currentSheet.getHeader().setLeft(xslxTempFile.getName());
    currentSheet.getHeader().setRight("DO NOT DISCARD!!!  Keep with patient folder.");
    //in Dr. Carter's VBA was set at points 18 which is .25 inches
    currentSheet.setMargin(Sheet.RightMargin, .25);
    currentSheet.setMargin(Sheet.LeftMargin, .25);

    printSetup.setOrientation(PrintOrientation.LANDSCAPE);

    //NOTE: setFitWidth doesn't work for columns, ie can't setFitToPageColumns, this 
    //is the best workaround I can do, it will only looked cramped for those with a lot of calls
    printSetup.setFitWidth((short) 1);
    printSetup.setFitHeight((short) 3);
    currentSheet.setRepeatingRows(CellRangeAddress.valueOf("1"));
    currentSheet.setFitToPage(true);
    //making it by default not print the fellow's interp
    currentSheet.getWorkbook().setPrintArea(0, 2, 20, 0, currentSheet.getLastRowNum());

    for (int x = 0; x < currentSheet.getRow(0).getPhysicalNumberOfCells(); x++) {
        currentSheet.autoSizeColumn(x);
        if (x > 33) {
            currentSheet.setColumnHidden(x, true);
        }
    }
    currentSheet.setColumnWidth(0, 10000);
    currentSheet.setColumnWidth(1, 10000);
    currentSheet.setColumnWidth(2, 10000);

    //                 currentSheet.setColumnWidth(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, 3500);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Gene"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Variant"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Chr"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Coordinate"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Type"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Genotype"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Exonic"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Filters"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Quality"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("GQX"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Variant Freq"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Read Depth"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alt Read Depth"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Consequence"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Global Minor"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Classification"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Inherited From"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allelic Depths"))+3, true); 
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Annotation"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Custom Gene Annotation"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Num Transcripts"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("cDNA Position"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("CDS Position"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Protein Position"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Amino Acids"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Codons"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGNC"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Transcript HGNC"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Canonical"))+3, true);
    //                 //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Sift"))+3, false);
    //                 //currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PolyPhen"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ENSP"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSc"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("HGVSp"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("dbSNP ID"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Ancestral Allele"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Global Minor Allele"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Amr"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Asn"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Af"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Eur"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Allele Freq Evs"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Coverage"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("EVS Samples"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Conserved Sequence"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC ID"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Wildtype"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Allele"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Gene"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Primary Site"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("COSMIC Histology"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Accession"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Ref"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Alleles"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Allele Type"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Significance"))+3, false);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Alternate Alleles"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("Google Scholar"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("PubMed"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("UCSC Browser"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar RS"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Disease Name"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar MedGen"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar OMIM"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar Orphanet"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar GeneReviews"))+3, true);
    //                 currentSheet.setColumnHidden(this.tsvRearrangeConversion.get(this.originalHeadings.get("ClinVar SnoMedCt ID"))+3, true);

}