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:jp.ac.utokyo.rcast.karkinos.summary.SummaryDB.java

License:Apache License

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

    sampleL = getSAmpleL();//  www  . j  a v a 2 s .c om

    List<String> title = new ArrayList<String>();
    if (!adjusted) {
        title.add("orginal ratio");
    } else {
        title.add("adjusted ratio");
    }
    title.addAll(sampleL);
    title.add("total");
    title.add("type");

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

    String sql = "select count(*) as c, sample_ID, round((AlleleFreq),1) as m  from csvdata ";
    if (!adjusted) {
        sql = "select count(*) as c, sample_ID, round((AlleleFreqOrg),1) as m  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 sampleid = rs.getString("sample_ID");
        float dev = rs.getFloat("m");
        int devi = Math.round(dev * 100);
        if (devi >= 100) {
            devi = 90;
        }
        String key = sampleid + ":" + devi;
        if (m.containsKey(key)) {
            int n = m.get(key);
            cnt = cnt + n;
        }
        m.put(key, cnt);

    }
    rs.close();

    for (int n = 0; n < 100; n = n + 10) {

        ArrayList data = new ArrayList();
        data.add(n + " to " + (n + 10));
        int total = 0;
        for (String sample_ID : sampleL) {

            String key = sample_ID + ":" + n;
            Integer cnt = m.get(key);
            if (cnt == null) {
                cnt = 0;
            }
            total = total + cnt;
            data.add(cnt);
        }
        data.add(total);
        if (all) {
            data.add("all pass filter");
        } else {
            data.add("AA alternation");
        }

        Row row = sheet.createRow(colcount);
        setRow(data, row);
        colcount++;

    }
    return colcount;

}

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

License:Apache License

public void writeHDAMP(DataReader dr, XSSFSheet sheet, ChromBand cband) throws SQLException {

    List<String> title = new ArrayList<String>();
    title.add("sample id");
    title.add("copy number");
    title.add("chrom");
    title.add("start");
    title.add("end");
    title.add("cytoband");
    title.add("length");
    title.add("cosmic genes");
    title.add("#cosmic of genes");
    title.add("genes");
    title.add("#of genes");

    Row rowtitle = sheet.createRow(0);
    setRow(title, rowtitle);//from   w w w  .  ja va2 s  . c  o m

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

            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);
            data1.add(cband.getBand(cintvl.chr, cintvl.start, cintvl.end));
            data1.add(Math.abs(cintvl.end - cintvl.start));
            String cgenes = getStrs("select geneName from generef where cosmic is not null and chrom ='"
                    + cintvl.chr + "' and txstart < " + cintvl.end + " and " + cintvl.start + " < txend");

            String genes = getStrs("select geneName from generef where chrom ='" + cintvl.chr
                    + "' and txstart < " + cintvl.end + " and " + cintvl.start + " < txend");
            data1.add(cgenes);
            data1.add(cgenes.split(",").length);
            data1.add(genes);
            data1.add(genes.split(",").length);

            setRow(data1, row);
            cnt++;
        }

    }

}

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

License:Apache License

public void mutationSig(XSSFSheet sheet, String hg) throws SQLException {

    TwoBitGenomeReader tb = new TwoBitGenomeReader(new File(hg));
    tb.setCheckRepeat(false);/*  w  w  w  .j  a  v  a 2  s  .  co  m*/

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

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

    String sql = "select sample_ID,chrom,pos,ALT,REF,Indel,concat(REF,'to',ALT) as m from csvdata ";
    sql = sql + " where Filter2='PASS' order by sample_ID,chrom,pos";

    PreparedStatement crs = memorycon.prepareStatement(sql);

    ResultSet rs = crs.executeQuery();
    Map<String, Map<String, Integer>> datam = new LinkedHashMap<String, Map<String, Integer>>();
    Set<String> ts = new TreeSet<String>();

    while (rs.next()) {

        String ref = rs.getString("m");
        String sample = rs.getString("sample_ID");
        boolean reverse = ref.startsWith("G") || ref.startsWith("A");
        ref = GenotypeKeyUtils.aggrigateKeys(ref);

        String indel = rs.getString("Indel");
        if (indel != null && indel.equals("Yes"))
            continue;

        String chr = rs.getString("chrom");
        if (!chr.contains("chr")) {
            chr = "chr" + chr;
        }
        int pos = rs.getInt("pos");

        String subseq = "";
        try {
            subseq = tb.getGenomeNuc(chr, pos - 1, true) + "" + tb.getGenomeNuc(chr, pos + 1, true);
        } catch (Exception ex) {
            continue;
        }
        subseq = subtype(subseq, reverse);
        String id = ref + "-" + subseq;
        ts.add(id);
        Map<String, Integer> each = null;
        if (datam.containsKey(sample)) {
            each = datam.get(sample);
        } else {
            each = new HashMap<String, Integer>();
            datam.put(sample, each);
        }
        //
        if (each.containsKey(id)) {
            int nn = each.get(id) + 1;
            each.put(id, nn);
        } else {
            each.put(id, 1);
        }

    }
    rs.close();

    for (String muid : ts) {

        colcount++;
        Row row = sheet.createRow(colcount);

        List<String> rowdata = new ArrayList<String>();
        rowdata.add(muid);

        for (String sample : sampleL) {

            Map<String, Integer> each = datam.get(sample);
            Integer num = each.get(muid);
            if (num == null) {
                num = 0;
            }
            //
            rowdata.add(num + "");
        }

        setRow(rowdata, row);

    }

}

From source file:jpgtoxlsx.JPGtoXLSX.java

/**
 * @param args the command line arguments
 *//*w  ww .jav a2s  .  c  o  m*/
public static void main(String[] args) throws Exception {

    XSSFWorkbook myExcel = new XSSFWorkbook();
    XSSFSheet sheet = myExcel.createSheet("Image");

    BufferedImage image = null;

    int width, height;
    int xlrows;
    //int pixel;

    File fimg;
    //open image
    try {
        fimg = new File("C:\\excel\\Test.jpg");
        image = ImageIO.read(fimg);
    } catch (IOException e) {
        System.out.println(e);
    }

    width = image.getWidth();
    //System.out.println(width);
    height = image.getHeight();
    //System.out.println(height);

    xlrows = height * 3;

    //System.out.println(pixel);

    int r, g, b;

    int w = image.getWidth();
    int h = image.getHeight();
    System.out.println("Width: " + w + "Height: " + h);
    System.out.println("Generating RGB values..");

    XSSFCellStyle style1 = myExcel.createCellStyle();
    XSSFCellStyle style2 = myExcel.createCellStyle();
    XSSFCellStyle style3 = myExcel.createCellStyle();

    for (int i = 1; i < ++h; i++) {

        if (i == image.getHeight()) {
            break;
        }

        XSSFRow row1 = sheet.createRow((i * 3 - 3));
        XSSFRow row2 = sheet.createRow((i * 3 - 2));
        XSSFRow row3 = sheet.createRow((i * 3 - 1));

        for (int j = 0; j < ++w; j++) {

            if (j == image.getWidth()) {
                break;
            }

            //System.out.println("I: " + i);
            //System.out.println("J: " + j + "\n");
            int x = i;
            int y = j;

            //System.out.println("X: " + x + "Y: " + y + "\r");
            //System.out.println("Y: " + y);
            int pixel = image.getRGB(y, x);
            r = (pixel >> 16) & 0xff;
            g = (pixel >> 8) & 0xff;
            b = (pixel) & 0xff;

            XSSFCell cell1 = row1.createCell(y);
            XSSFCell cell2 = row2.createCell(y);
            XSSFCell cell3 = row3.createCell(y);

            cell1.setCellValue(Integer.toString(r));
            cell2.setCellValue(Integer.toString(g));
            cell3.setCellValue(Integer.toString(b));

            style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(r, 0, 0)));
            ;
            style1.setFillPattern(CellStyle.SOLID_FOREGROUND);

            style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, g, 0)));
            style2.setFillPattern(CellStyle.SOLID_FOREGROUND);

            style3.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 0, b)));
            style3.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell1.setCellStyle(style1);
            cell2.setCellStyle(style2);
            cell3.setCellStyle(style3);

            //System.out.println("x,y: " + j + ", " + i);
            //System.out.println("R: " + r + " G: " + g + " B: " + b + "\n");
        }
    }
    System.out.println("RGB values extracted.");
    System.out.println("Generating image");

    myExcel.write(new FileOutputStream("excel.xlsx"));
    myExcel.close();

}

From source file:ke.co.tawi.babblesms.server.servlet.export.excel.inbox.ExportExcel.java

License:Open Source License

/**
 * Returns MS Excel file of the data specified for exporting.
 * @param List<IncomingLog>/*from   w w w .ja v a  2 s  . c o m*/
 * Method create excelSheets and sends them
 ****/
public void createExcelSheets(List<IncomingLog> InLog) throws IOException {
    List<Phone> phoneList;
    //String cont = null;

    XSSFWorkbook xf = new XSSFWorkbook();
    XSSFCreationHelper ch = xf.getCreationHelper();

    XSSFSheet s = xf.createSheet();
    //create the first row
    XSSFRow r1 = s.createRow(0);
    XSSFCell c11 = r1.createCell(0);
    c11.setCellValue(ch.createRichTextString("*"));
    XSSFCell c12 = r1.createCell(1);
    c12.setCellValue(ch.createRichTextString("Message"));
    XSSFCell c13 = r1.createCell(2);
    c13.setCellValue(ch.createRichTextString("Source"));
    XSSFCell c14 = r1.createCell(3);
    c14.setCellValue(ch.createRichTextString("Destination"));
    XSSFCell c15 = r1.createCell(4);
    c15.setCellValue(ch.createRichTextString("Network"));
    XSSFCell c16 = r1.createCell(5);
    c16.setCellValue(ch.createRichTextString("Time (" + timezoneFormatter.format(new Date()) + ") Time Zone"));
    XSSFCell c17 = r1.createCell(6);
    c17.setCellValue(ch.createRichTextString("Message Id"));

    int i = 1;
    //create other rows
    for (IncomingLog log : InLog) {
        phoneList = phnDAO.getPhones(log.getOrigin());

        XSSFRow r = s.createRow(i);
        //row number
        XSSFCell c1 = r.createCell(0);
        c1.setCellValue(i + pageno);

        //get message  
        XSSFCell c2 = r.createCell(1);
        c2.setCellValue(ch.createRichTextString(log.getMessage()));

        //get phone numbers
        XSSFCell c3 = r.createCell(2);
        if (phoneList.size() > 0) {
            for (Phone phone : phoneList) {
                Contact contacts = ctDAO.getContact(phone.getContactUuid());
                c3.setCellValue(ch.createRichTextString(contacts.getName()));
            }
        } else {
            c3.setCellValue(ch.createRichTextString(log.getOrigin()));
        }

        //get destination   
        XSSFCell c4 = r.createCell(3);
        c4.setCellValue(ch.createRichTextString(log.getDestination()));

        //get network name    
        XSSFCell c5 = r.createCell(4);
        c5.setCellValue(ch.createRichTextString(networkHash.get(log.getNetworkUuid())));

        //get date 
        XSSFCell c6 = r.createCell(5);
        c6.setCellValue(ch.createRichTextString("" + dateFormatter.format(log.getLogTime())));

        //get message id
        XSSFCell c7 = r.createCell(6);
        c7.setCellValue(ch.createRichTextString(log.getUuid()));
        i++;

    }
    xf.write(out);
    out.flush();
    out.close();
}

From source file:kp.servlet.ExportRpt.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*w w w .ja  v  a  2  s.  c  o m*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE,
            "accRole :" + request.getParameter("accRole"));
    Logger.getLogger(ExportRpt.class.getName()).log(Level.SEVERE, "Unit :" + request.getParameter("unit"));
    ArrayList<MocWfTran> Mocstatus = new ArrayList<>();
    TranDao tdao = new TranDao();
    Mocstatus = tdao.getMocStatusList(request.getParameter("accRole"), request.getParameter("unit"),
            request.getParameter("user"));

    //Developing Metadata
    String rptName = "MOC Status Excel Report";
    ArrayList<String> colLabel = new ArrayList<>();
    colLabel.add("Case Id");
    colLabel.add("Moc NO");
    colLabel.add("Moc Title");
    colLabel.add("Moc Status");
    colLabel.add("Creation Date");
    colLabel.add("Owner's Name");
    colLabel.add("Unit");
    colLabel.add("Plant");
    colLabel.add("Current Stage");
    colLabel.add("Pending At");

    //Starting EXCEL Creating
    //XLS Variable
    XSSFSheet spreadsheet;
    XSSFWorkbook workbook;
    XSSFRow row;
    XSSFCell cell;
    XSSFFont xfont = null;
    XSSFCellStyle xstyle = null;

    //2.Create WorkBook and Sheet
    workbook = new XSSFWorkbook();
    spreadsheet = workbook.createSheet(rptName);

    //set header style
    xfont = workbook.createFont();
    xfont.setFontHeight(11);
    xfont.setFontName("Calibri");
    xfont.setBold(true);

    //Set font into style
    CellStyle borderStyle = workbook.createCellStyle();
    borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
    borderStyle.setFont(xfont);
    xstyle = workbook.createCellStyle();
    xstyle.setFont(xfont);

    //header
    row = spreadsheet.createRow(0);
    cell = row.createCell(0);
    cell.setCellValue(rptName);
    cell.setCellStyle(borderStyle);
    spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colLabel.size() - 1));

    //3.Get First Row and Set Headers
    row = spreadsheet.createRow(1);

    for (int i = 0; i < colLabel.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(colLabel.get(i));
        cell.setCellStyle(xstyle);
    }

    //Itrate or Database data and write
    int i = 2;
    for (MocWfTran bean : Mocstatus) {
        row = spreadsheet.createRow(i);
        cell = row.createCell(0);
        cell.setCellValue(bean.getCaseId());
        cell = row.createCell(1);
        cell.setCellValue(bean.getMocNo());
        cell = row.createCell(2);
        cell.setCellValue(bean.getCaseName());
        cell = row.createCell(3);
        cell.setCellValue(bean.getMocStatus());
        cell = row.createCell(4);
        cell.setCellValue(bean.getCrDateString());
        cell = row.createCell(5);
        cell.setCellValue(bean.getCaseOwnerName());
        cell = row.createCell(6);
        cell.setCellValue(bean.getUnitId());
        cell = row.createCell(7);
        cell.setCellValue(bean.getPlantId());
        cell = row.createCell(8);
        cell.setCellValue(bean.getStgNname());
        cell = row.createCell(9);
        cell.setCellValue(bean.getUserNname());
        i++;
    }

    //Export to Excel
    String file_name = "MocStatus";
    String path = getServletContext().getRealPath("/");
    String full_path = path + "/report/" + file_name + ".xlsx";
    //        FileOutputStream out = new FileOutputStream(new File("D://" + file_name + ".xlsx"));
    FileOutputStream out = new FileOutputStream(new File(full_path));
    workbook.write(out);

    //Download code 
    // reads input file from an absolute path
    File downloadFile = new File(full_path);
    OutputStream outStream;
    // obtains ServletContext
    try (FileInputStream inStream = new FileInputStream(downloadFile)) {
        //obtains ServletContext
        ServletContext context = getServletContext();
        // gets MIME type of the file
        String mimeType = context.getMimeType(full_path);
        if (mimeType == null) {
            // set to binary type if MIME mapping not found
            mimeType = "application/octet-stream";
        } // modifies response
        response.setContentType(mimeType);
        response.setContentLength((int) downloadFile.length());
        // forces download
        String headerKey = "Content-Disposition";
        String headerValue = String.format("attachment; filename=\"%s\"", downloadFile.getName());
        response.setHeader(headerKey, headerValue);
        // obtains response's output stream
        outStream = response.getOutputStream();
        byte[] buffer = new byte[4096];
        int bytesRead = -1;
        while ((bytesRead = inStream.read(buffer)) != -1) {
            outStream.write(buffer, 0, bytesRead);
        }
    }
    outStream.close();
    //        response.sendRedirect("mocstatus.jsp");
}

From source file:listfiles.ListFiles.java

private static Cell checkRowCellExists(XSSFSheet currentSheet, int rowIndex, int colIndex) {
    Row currentRow = currentSheet.getRow(rowIndex);
    if (currentRow == null) {
        currentRow = currentSheet.createRow(rowIndex);
    }// ww w . j  av a  2 s. c  om
    //Check if cell exists
    Cell currentCell = currentRow.getCell(colIndex);
    if (currentCell == null) {
        currentCell = currentRow.createCell(colIndex);
    }
    return currentCell;
}

From source file:lldval.LLDVal.java

/**
 * @param args the command line arguments
 *///from w w w. j ava2  s . c  o  m
public static void main(String[] args) throws IOException {

    // reading data from a csv file  
    System.out.println("Reading data from csv :");
    ReadCsv readCsv = new ReadCsv();
    readCsv.readCsv("./tunables.csv");
    writeXLSXFile("./PCAT_AnalysisFile.xlsx", "Info");
    Workbook wb = new XSSFWorkbook(new FileInputStream("./PCAT_AnalysisFile.xlsx"));
    XSSFSheet clusterSheet = (XSSFSheet) wb.createSheet("caaConfigurations");
    XSSFSheet hwSheet = (XSSFSheet) wb.createSheet("HWConfigurations");
    XSSFSheet vfcSheet = (XSSFSheet) wb.createSheet("VFC Mappings");
    // reading data from a csv file and convert to java object  
    System.out.println("Reading data from csv and convert to java object:");
    //  csvToTunables csvToJavaObject = new csvToTunables();  
    //  csvToJavaObject.convertCsvToJava();  
    pcatFolders pcatFolder = new pcatFolders("./systems");

    //    System.out.println(Arrays.toString(pcatFolder.pcatFolderList));
    String[] lparName = pcatFolder.pcatFolderList;
    System.out.println(Arrays.toString(lparName));
    //      caa CAA = new caa();
    //      readCaa readCaas = new readCaa("dx980");
    for (int j = 0; j < lparName.length - 1; j++) {
        pcatLparFolders lparFolders = new pcatLparFolders("./systems", lparName[j]);
        System.out.print("LparName : " + String.valueOf(lparName[j]) + "  : ");
        System.out.println(Arrays.toString(lparFolders.fileList));
        for (int t = 0; t < lparFolders.fileList.length; t++) {
            System.out.println(lparFolders.fileList[t]);
            switch (lparFolders.fileList[t]) {
            //        case "caa":
            //            readCaa caa = new readCaa(lparName[j]);
            //            caa.processingLineByLine();
            //            System.out.println("Cluster Configuration Populating");
            ////            System.out.println(caa.toString());
            ////            System.out.println(String.valueOf(CAA.CLUSTER_NAME+" : "+CAA.Cluster_shorthand_id_for_node+" : "+CAA.Mean_Deviation_in_network_rtt_to_node+" : "+CAA.Node_name+" : "+CAA.Number_of_clusters_node_is_a_member_in+" : "+CAA.Smoothed_rtt_to_node+" : "+CAA.State_of_node+" : "+CAA.UUID_for_node));
            //            int sheetLength = clusterSheet.getPhysicalNumberOfRows();
            //            if(sheetLength == 0){
            //                Row row = clusterSheet.createRow((short)sheetLength);
            //                Cell cell = row.createCell(0);
            //                cell.setCellValue("CLUSTER_NAME");
            //                Cell cell1 = row.createCell(1);
            //                cell1.setCellValue("Node_name");
            //                Cell cell2 = row.createCell(2);
            //                cell2.setCellValue("Number_of_clusters_node_is_a_member_in");
            //                Cell cell3 = row.createCell(3);
            //                cell3.setCellValue("State_of_node");
            //                Cell cell4 = row.createCell(4);
            //                cell4.setCellValue("UUID_for_node");
            //                Cell cell5 = row.createCell(5);
            //                cell5.setCellValue("Cluster_shorthand_id_for_node");
            //                Cell cell6 = row.createCell(6);
            //                cell6.setCellValue("Smoothed_rtt_to_node");
            //                Cell cell7 = row.createCell(7);
            //                cell7.setCellValue("Mean_Deviation_in_network_rtt_to_node");}else
            //              if (CAA.CLUSTER_NAME != null && !CAA.CLUSTER_NAME.isEmpty()){
            //                Row row = clusterSheet.createRow((short)sheetLength+1);
            //                Cell cell = row.createCell(0);
            //                cell.setCellValue(CAA.CLUSTER_NAME);
            //                Cell cell1 = row.createCell(1);
            //                cell1.setCellValue(CAA.Node_name);
            //                Cell cell2 = row.createCell(2);
            //                cell2.setCellValue(CAA.Number_of_clusters_node_is_a_member_in);
            //                Cell cell3 = row.createCell(3);
            //                cell3.setCellValue(CAA.State_of_node);
            //                Cell cell4 = row.createCell(4);
            //                cell4.setCellValue(CAA.UUID_for_node);
            //                Cell cell5 = row.createCell(5);
            //                cell5.setCellValue(CAA.Cluster_shorthand_id_for_node);
            //                Cell cell6 = row.createCell(6);
            //                cell6.setCellValue(CAA.Smoothed_rtt_to_node);
            //                Cell cell7 = row.createCell(7);
            //                cell7.setCellValue(CAA.Mean_Deviation_in_network_rtt_to_node);
            //              }
            //                    try {
            //        FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx");
            //        wb.write(out);
            //        out.close();
            //    } catch (Exception e) {
            //        e.printStackTrace();
            //    }
            //            break;
            case "hds":
                System.out.println("HDS Storage Configuration Populating");
                readHDL parser = new readHDL(lparName[j]);
                parser.processLineByLine(lparName[j]);
                //    System.out.println(String.valueOf(lpar.getLparName()+" : "+lpar.getLparID()+" : "+lpar.getHostname()+" : "+lpar.getEntCapacity()));
                log("Done.");
                break;
            //        case "hmc":
            //            System.out.println("HMC Configuration Populating");
            //            break;
            case "hw":
                System.out.println("Hardware  Configuration Populating");
                readHW hw = new readHW(lparName[j]);
                hw.processLineByLine();
                System.out.println(lpar.lparID + " : " + lpar.lparName + " : " + lpar.hostname + " : "
                        + lpar.entCapacity + " : " + lpar.maxCPU + " : " + lpar.desiredCPU + " : " + lpar.minCPU
                        + " : " + lpar.MaxVirtCPU + " : " + lpar.DesVirtCPU + " : " + lpar.minVirtCPU);
                int hwsheetLength = hwSheet.getPhysicalNumberOfRows();
                if (hwsheetLength == 0) {
                    Row row = hwSheet.createRow((short) hwsheetLength);
                    Cell cell = row.createCell(0);
                    cell.setCellValue("LparID");
                    Cell cell1 = row.createCell(1);
                    cell1.setCellValue("lparName");
                    Cell cell2 = row.createCell(2);
                    cell2.setCellValue("hostname");
                    Cell cell3 = row.createCell(3);
                    cell3.setCellValue("maxMem");
                    Cell cell4 = row.createCell(4);
                    cell4.setCellValue("desiredMem");
                    Cell cell5 = row.createCell(5);
                    cell5.setCellValue("maxMem");
                    Cell cell6 = row.createCell(6);
                    cell6.setCellValue("MemMode");
                    Cell cell7 = row.createCell(7);
                    cell7.setCellValue("maxCPU");
                    Cell cell8 = row.createCell(8);
                    cell8.setCellValue("desiredCPU");
                    Cell cell9 = row.createCell(9);
                    cell9.setCellValue("minCPU");
                    Cell cell10 = row.createCell(10);
                    cell10.setCellValue("MaxVirtCPU");
                    Cell cell11 = row.createCell(11);
                    cell11.setCellValue("DesVirtCPU");
                    Cell cell12 = row.createCell(12);
                    cell12.setCellValue("minVirtCPU");
                    Cell cell13 = row.createCell(13);
                    cell13.setCellValue("entCapacity");
                    Cell cell14 = row.createCell(14);
                    cell14.setCellValue("weight");
                    Cell cell15 = row.createCell(15);
                    cell15.setCellValue("SMTType");
                    Cell cell16 = row.createCell(16);
                    cell16.setCellValue("cpuMode");
                } else if (lpar.lparID != null && !lpar.lparID.isEmpty()) {
                    Row row = hwSheet.createRow((short) hwsheetLength + 1);
                    Cell cell = row.createCell(0);
                    cell.setCellValue(lpar.lparID);
                    Cell cell1 = row.createCell(1);
                    cell1.setCellValue(lpar.lparName);
                    Cell cell2 = row.createCell(2);
                    cell2.setCellValue(lpar.hostname);
                    Cell cell3 = row.createCell(3);
                    cell3.setCellValue(lpar.maxMem);
                    Cell cell4 = row.createCell(4);
                    cell4.setCellValue(lpar.desiredMem);
                    Cell cell5 = row.createCell(5);
                    cell5.setCellValue(lpar.maxMem);
                    Cell cell6 = row.createCell(6);
                    cell6.setCellValue(lpar.MemMode);
                    Cell cell7 = row.createCell(7);
                    cell7.setCellValue(lpar.maxCPU);
                    Cell cell8 = row.createCell(8);
                    cell8.setCellValue(lpar.desiredCPU);
                    Cell cell9 = row.createCell(9);
                    cell9.setCellValue(lpar.minCPU);
                    Cell cell10 = row.createCell(10);
                    cell10.setCellValue(lpar.MaxVirtCPU);
                    Cell cell11 = row.createCell(11);
                    cell11.setCellValue(lpar.DesVirtCPU);
                    Cell cell12 = row.createCell(12);
                    cell12.setCellValue(lpar.minVirtCPU);
                    Cell cell13 = row.createCell(13);
                    cell13.setCellValue(lpar.entCapacity);
                    Cell cell14 = row.createCell(14);
                    cell14.setCellValue(lpar.weight);
                    Cell cell15 = row.createCell(15);
                    cell15.setCellValue(lpar.SMTType);
                    Cell cell16 = row.createCell(16);
                    cell16.setCellValue(lpar.cpuMode);
                }
                try {
                    FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx");
                    wb.write(out);
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                break;
            //        case "network":
            //            System.out.println("Network Configuration Populating");
            //            break;
            //        case "os":
            //            System.out.println("Operating System Configuration Populating");
            //            break;
            //        case "perf":
            //            System.out.println("Performance Configuration Populating");
            //            break;
            //        case "secure":
            //            System.out.println("Security Configuration Populating");
            //            break;
            //        case "storage":
            //            System.out.println("Storage Configuration Populating");
            //            break;
            //        case "tsm":
            //            System.out.println("TSM Configuration Populating");
            //            break;
            case "vio":
                vfcs.clear();
                System.out.println("VIO Configuration Populating");
                readVFCMap vfcmapd = new readVFCMap(lparName[j]);
                vfcmapd.processPatern();
                vfcMap vfcmap = new vfcMap();
                System.out.println(vfcs.size());
                for (int vfcRows = 0; vfcRows <= vfcs.size() - 1; vfcRows++) {
                    System.out.println(vfcs.get(vfcRows).vfcName + " | " + vfcs.get(vfcRows).physLoc + " | "
                            + vfcs.get(vfcRows).clntId + " | " + vfcs.get(vfcRows).clntName + " | "
                            + vfcs.get(vfcRows).status + " | " + vfcs.get(vfcRows).fc_name + " | "
                            + vfcs.get(vfcRows).fc_loc_code + " | " + vfcs.get(vfcRows).flags + " | "
                            + vfcs.get(vfcRows).VFC_client_name + " | " + vfcs.get(vfcRows).VFC_client_DRC);
                    int vfcsheetLength = vfcSheet.getPhysicalNumberOfRows();
                    if (vfcsheetLength == 0) {
                        Row row = vfcSheet.createRow((short) vfcsheetLength);
                        int count = 0;
                        Cell cell = row.createCell(0);
                        cell.setCellValue("Lpar Name");
                        for (Field field : vfcmap.getClass().getDeclaredFields()) {
                            count++;
                            //                System.out.println(field.getName());
                            Cell nextcell = row.createCell(count);
                            nextcell.setCellValue(field.getName());

                        }
                        //                Row row1 = vfcSheet.createRow(1);
                        //                System.out.println("value row"+row1);
                        //                Cell valuecell = row1.createCell(0);
                        //                valuecell.setCellValue(lparName[j]);
                        //                Cell vfcNamecell = row1.createCell(vfcRows);
                        //                vfcNamecell.setCellValue(vfcs.get(vfcRows).vfcName);
                        //                Cell physLoccell = row1.createCell(vfcRows);
                        //                physLoccell.setCellValue(vfcs.get(vfcRows).physLoc);
                        //                Cell clntIdcell = row1.createCell(vfcRows);
                        //                clntIdcell.setCellValue(vfcs.get(vfcRows).clntId);
                        //                Cell clntNamecell = row1.createCell(vfcRows);
                        //                clntNamecell.setCellValue(vfcs.get(vfcRows).clntName);
                        //                Cell statuscell = row1.createCell(vfcRows);
                        //                statuscell.setCellValue(vfcs.get(vfcRows).status);
                        //                Cell fc_namecell = row1.createCell(vfcRows);
                        //                fc_namecell.setCellValue(vfcs.get(vfcRows).fc_name);
                        //                Cell fc_loc_codecell = row1.createCell(vfcRows);
                        //                fc_loc_codecell.setCellValue(vfcs.get(vfcRows).fc_loc_code);
                        //                Cell flagscell = row1.createCell(vfcRows);
                        //                flagscell.setCellValue(vfcs.get(vfcRows).flags);
                        //                Cell VFC_client_namecell = row1.createCell(vfcRows);
                        //                VFC_client_namecell.setCellValue(vfcs.get(vfcRows).VFC_client_name);
                        //                Cell VFC_client_DRCcell = row1.createCell(vfcRows);
                        //                VFC_client_DRCcell.setCellValue(vfcs.get(vfcRows).VFC_client_DRC);
                        //vfcs.get(vfcRows).vfcName+" | "+vfcs.get(vfcRows).physLoc+" | "+vfcs.get(vfcRows).clntId+" | "+vfcs.get(vfcRows).clntName+" | "+vfcs.get(vfcRows).status+" | "+vfcs.get(vfcRows).fc_name+" | "+vfcs.get(vfcRows).fc_loc_code+" | "+vfcs.get(vfcRows).flags+" | "+vfcs.get(vfcRows).VFC_client_name+" | "+vfcs.get(vfcRows).VFC_client_DRC
                    } else if (vfcs.get(vfcRows).vfcName != null && !vfcs.get(vfcRows).vfcName.isEmpty()) {
                        Row valuerow = vfcSheet.createRow((short) vfcsheetLength);
                        System.out.println("value row" + valuerow);
                        Cell valuecell = valuerow.createCell(0);
                        valuecell.setCellValue(lparName[j]);
                        Cell vfcNamecell = valuerow.createCell(vfcRows);
                        vfcNamecell.setCellValue(vfcs.get(vfcRows).vfcName);
                        Cell physLoccell = valuerow.createCell(vfcRows);
                        physLoccell.setCellValue(vfcs.get(vfcRows).physLoc);
                        Cell clntIdcell = valuerow.createCell(vfcRows);
                        clntIdcell.setCellValue(vfcs.get(vfcRows).clntId);
                        Cell clntNamecell = valuerow.createCell(vfcRows);
                        clntNamecell.setCellValue(vfcs.get(vfcRows).clntName);
                        Cell statuscell = valuerow.createCell(vfcRows);
                        statuscell.setCellValue(vfcs.get(vfcRows).status);
                        Cell fc_namecell = valuerow.createCell(vfcRows);
                        fc_namecell.setCellValue(vfcs.get(vfcRows).fc_name);
                        Cell fc_loc_codecell = valuerow.createCell(vfcRows);
                        fc_loc_codecell.setCellValue(vfcs.get(vfcRows).fc_loc_code);
                        Cell flagscell = valuerow.createCell(vfcRows);
                        flagscell.setCellValue(vfcs.get(vfcRows).flags);
                        Cell VFC_client_namecell = valuerow.createCell(vfcRows);
                        VFC_client_namecell.setCellValue(vfcs.get(vfcRows).VFC_client_name);
                        Cell VFC_client_DRCcell = valuerow.createCell(vfcRows);
                        VFC_client_DRCcell.setCellValue(vfcs.get(vfcRows).VFC_client_DRC);

                        //                    valueCount++;
                        //                Cell value1cell = valuerow.createCell(valueCount);
                        //                value1cell.setCellValue(map.clntName);// i am stuck here to iterate around the values of each instance of the object.
                    }
                }
                FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx");
                wb.write(out);
                out.close();
                break;
            default:
                //            System.out.println("Nothing to do");
                break;

            }
        }
        ;
    }
    ;

    //  getPropValue testRun = new getPropValue("./Systems/dx1010/hw/lparstat-vfcRows.txt");

}

From source file:localization.checkURL.java

public static void check(String desktopFile, String serverFile, String inputFolder) {

    try {//from w w  w. ja v a 2s  .c o  m
        String desktopFolder = desktopFile.substring(desktopFile.lastIndexOf("\\") + 1, desktopFile.length());
        desktop = desktopFolder.substring(0, desktopFolder.indexOf("_"));
        String serverFolder = serverFile.substring(serverFile.lastIndexOf("\\") + 1, serverFile.length());
        server = serverFolder.substring(0, serverFolder.indexOf("_"));

        pubList = new ArrayList<>();
        searchFile(inputFolder);
        String parFolder = inputFolder.substring(0, inputFolder.lastIndexOf("\\"));
        HOName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.lastIndexOf("\\") + 5);
        lang = inputFolder.substring(inputFolder.lastIndexOf("\\") + 1, inputFolder.length());
        outputFileName = parFolder.substring(parFolder.lastIndexOf("\\") + 1, parFolder.length());
        outputFilePath = parFolder + "\\" + outputFileName + "_" + lang + ".xlsx";

        valueArray = new String[pubList.size() + 1][6];
        valueArray[0][0] = "Language";
        valueArray[0][1] = "HO#";
        valueArray[0][2] = "Publication Name";
        valueArray[0][3] = "Type";
        valueArray[0][4] = "Topic Name";
        valueArray[0][5] = "URL";
        for (int i = 0; i < pubList.size(); i++) {
            String fullPath = pubList.get(i);
            valueArray[i + 1][0] = lang.toUpperCase().trim();
            valueArray[i + 1][1] = HOName.trim();
            valueArray[i + 1][4] = fullPath.substring(fullPath.lastIndexOf("\\") + 1, fullPath.length());
            if (fullPath.contains("\\topic\\")) {
                valueArray[i + 1][3] = "topic";
                valueArray[i + 1][2] = fullPath
                        .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\topic\\")).trim();
            } else if (fullPath.contains("\\map\\")) {
                valueArray[i + 1][3] = "map";
                valueArray[i + 1][2] = fullPath
                        .substring(fullPath.indexOf("\\P") + 4, fullPath.indexOf("\\map\\")).trim();
            }
        }

        /* for(int i = 0; i < valueArray.length; i++){
            for(int j = 0; j < valueArray[i].length; j++){
                System.out.print(valueArray[i][j] + ",");
            }
            System.out.println();
        }*/

        File inputDesktopFile = new File(desktopFile);
        File inputServerFile = new File(serverFile);
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputDesktopFile));
        XSSFSheet mysheet = workbook.getSheetAt(0);
        int desktopRowNum = mysheet.getLastRowNum();

        XSSFWorkbook serverWorkbook = new XSSFWorkbook(new FileInputStream(inputServerFile));
        XSSFSheet serverSheet = serverWorkbook.getSheetAt(0);
        int serverRowNum = serverSheet.getLastRowNum();

        for (int k = 1; k < valueArray.length; k++) {
            //System.out.println(valueArray[k][3]);
            if (valueArray[k][3].equals("topic") && (!valueArray[k][4].trim().startsWith("cfg"))) {
                //System.out.println(k + " " +valueArray[k][3]);
                String source = valueArray[k][4].trim();
                for (int i = 1; i < desktopRowNum + 1; i++) {
                    Row desktopRow = mysheet.getRow(i);
                    if (desktopRow != null) {
                        String targetString = desktopRow.getCell(2).getStringCellValue().trim();
                        if (source.contains(targetString)) {
                            String desktopURL = desktopRow.getCell(0).getStringCellValue().trim();
                            desktopURL = desktopURL.replace(".com/en\\", ".com/" + lang.toLowerCase() + "\\");
                            desktopURL = desktopURL.replace("http://" + desktop, "http://" + desktop + "uat");
                            valueArray[k][5] = desktopURL;
                        }
                    }
                }

                for (int j = 0; j < serverRowNum + 1; j++) {
                    Row serverRow = serverSheet.getRow(j);
                    if (serverRow != null) {
                        String targetString1 = serverRow.getCell(2).getStringCellValue().trim();
                        if (source.contains(targetString1)) {
                            String serverURL = serverRow.getCell(0).getStringCellValue().trim();
                            serverURL = serverURL.replace("/en\\", "/" + lang.toLowerCase() + "\\");
                            serverURL = serverURL.replace("http://" + server, "http://" + server + "uat");
                            // System.out.println(serverURL);
                            if (valueArray[k][5] != null) {
                                valueArray[k][5] = valueArray[k][5] + "\n" + serverURL;
                            } else {
                                valueArray[k][5] = serverURL;
                            }

                        }
                    }
                }
            }

            XSSFWorkbook outputworkbook = new XSSFWorkbook();
            XSSFSheet outputsheet = outputworkbook.createSheet("sheet1");
            XSSFCellStyle outputstyle = outputworkbook.createCellStyle();
            outputstyle.setWrapText(true);
            int outputRowNum = 0;
            int outputCellNum = 0;
            for (int i = 0; i < valueArray.length; i++) {
                Row outputRow = outputsheet.createRow(outputRowNum++);
                for (int j = 0; j < valueArray[1].length; j++) {
                    Cell outputCell = outputRow.createCell(outputCellNum++);
                    if (valueArray[i][j] != null) {
                        outputCell.setCellValue(valueArray[i][j]);
                    } else {
                        outputCell.setCellValue("N/A");
                    }
                    if (j == 5) {
                        //outputsheet.autoSizeColumn(4);
                        outputCell.setCellStyle(outputstyle);
                    }
                }
                outputCellNum = 0;
            }
            outputsheet.autoSizeColumn(2);
            outputsheet.autoSizeColumn(4);
            outputsheet.autoSizeColumn(5);
            FileOutputStream out = new FileOutputStream(new File(outputFilePath));
            outputworkbook.write(out);
            out.close();
        }
    } catch (Exception e) {
        try {
            File file = new File(userDir + "\\log.txt");
            if (!file.exists()) {
                file.createNewFile();
            }
            FileWriter fw = new FileWriter(file.getAbsoluteFile());
            BufferedWriter bw = new BufferedWriter(fw);
            bw.write(e.getMessage());
            bw.write(e.getLocalizedMessage());
            bw.close();
            fw.close();
        } catch (Exception e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    }
}

From source file:Logic.RStoXL.java

public void genXLS(ResultSet rs, String Rpt_name, String path) {
    try {//w w  w.j av  a 2s.c o m
        //RS METE DATA
        ResultSetMetaData rsmd = rs.getMetaData();
        int col_count = rsmd.getColumnCount();
        ArrayList<String> col_name = new ArrayList<String>();
        for (int i = 1; i <= col_count; i++) {
            col_name.add(rsmd.getColumnLabel(i));
        }

        //XLS Variable
        XSSFSheet spreadsheet;
        XSSFWorkbook workbook;
        XSSFRow row;
        XSSFCell cell;
        XSSFFont xfont = null;
        XSSFCellStyle xstyle = null;

        //2.Create WorkBook and Sheet
        workbook = new XSSFWorkbook();
        spreadsheet = workbook.createSheet(Rpt_name);

        //set header style
        xfont = workbook.createFont();
        xfont.setFontHeight(11);
        xfont.setFontName("Calibri");
        xfont.setBold(true);

        //Set font into style
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderStyle.setFont(xfont);
        xstyle = workbook.createCellStyle();
        xstyle.setFont(xfont);

        //header
        row = spreadsheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue(Rpt_name);
        cell.setCellStyle(borderStyle);
        spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1));

        //3.Get First Row and Set Headers
        row = spreadsheet.createRow(1);

        for (int i = 0; i < col_count; i++) {
            cell = row.createCell(i);
            cell.setCellValue(col_name.get(i));
            cell.setCellStyle(xstyle);
        }

        //Itrate or Database data and write
        int i = 2;
        while (rs.next()) {
            row = spreadsheet.createRow(i);
            for (int j = 1; j <= col_count; j++) {
                cell = row.createCell(j - 1);
                cell.setCellValue(rs.getString(j));
            }
            i++;
        }

        //Export to Excel
        // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx"));
        FileOutputStream out = new FileOutputStream(new File(path));
        workbook.write(out);

        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "DONE|!");
        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "");
    } catch (SQLException ex) {
        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    } catch (IOException ex) {
        Logger.getLogger(RStoXL.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
    }
}