Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 2 of 2

Thread: Calulating Quaterly On Quaterly Growth.

  1. #1
    Junior Member
    Join Date
    Mar 2019
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Calulating Quaterly On Quaterly Growth.

    Hi, I am Rajesh new to java coding. please check the below code and modify it.

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.text.DecimalFormat;

    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.FillPatternType;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class Create_QOQ_Report {
    private XSSFWorkbook inputWorkbooks;
    private XSSFWorkbook outputWorkbook;

    private XSSFSheet inputSheet;
    private XSSFSheet outputSheet;

    public Create_QOQ_Report(String newPath , String DestinatonPath) throws IOException {
    // Step #1 : Locate path and file of input excel.
    File inputFile = new File(newPath);
    //File inputFile = new File("D:\\NewTraining\\RajeshPraharaj\\Source.xlsx ");
    FileInputStream fis = new FileInputStream(inputFile);
    inputWorkbooks = new XSSFWorkbook(fis);
    int inputSheetCount = inputWorkbooks.getNumberOfSheets();
    System.out.println("Input SheetCount: " + inputSheetCount);

    // Step #2 : Locate path and file of output excel.
    File outputFile = new File(DestinatonPath);
    //File outputFile = new File("D:\\NewTraining\\RajeshPraharaj\\target.xlsx ");
    FileOutputStream fos = new FileOutputStream(outputFile);

    // Step #3 : Creating workbook for output excel file.
    outputWorkbook = new XSSFWorkbook();

    // Step #4 : Creating sheets with the same name as appearing in input file.
    for (int i = 0; i < inputSheetCount; i++) {
    inputSheet = inputWorkbooks.getSheetAt(i);
    String inputSheetName = inputWorkbooks.getSheetName(i);
    outputSheet = outputWorkbook.createSheet(inputSheetName);

    // Create and call method to copy the sheet and content in new workbook.
    copySheet();
    }

    // Step #9 : Write all the sheets in the new Workbook(testData_Copy.xlsx) using FileOutStream Object
    outputWorkbook.write(fos);
    // Step #10 : At the end of the Program close the FileOutputStream object.
    fos.close();
    }

    public void copySheet() {
    int rowCount = inputSheet.getLastRowNum();
    int type = 0;
    String cellData = null;
    System.out.println(rowCount + " rows in inputsheet " + inputSheet.getSheetName());

    int rowNum;
    if (rowCount > 0) {
    int rowStart = 0;
    int lastColumn = 0;
    //int rowEnd = inputSheet.getLastRowNum();

    for (rowNum=rowStart; rowNum<rowCount; rowNum++) {
    Row r = inputSheet.getRow(rowNum);
    if (r == null) {
    // This whole row is empty
    // Handle it as needed
    continue;
    }

    Cell c, c1, c2;
    lastColumn = Math.max(r.getLastCellNum(), 0);
    int ls = lastColumn+1;
    for (int cn=0; cn < lastColumn; cn++) {
    //Cell c = r.getCell(cn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    c = r.getCell(cn);

    if(c!=null) {
    type = c.getCellType();
    switch(type) {
    case XSSFCell.CELL_TYPE_BLANK:
    cellData = null;
    break;
    case XSSFCell.CELL_TYPE_BOOLEAN:
    break;
    case XSSFCell.CELL_TYPE_ERROR:
    break;
    case XSSFCell.CELL_TYPE_FORMULA:
    break;
    case XSSFCell.CELL_TYPE_NUMERIC:
    double nvalue = c.getNumericCellValue();
    if(nvalue==new Double(nvalue).longValue()) {
    cellData = String.valueOf(new Double(nvalue).longValue());
    } else {
    cellData = String.valueOf(c.getNumericCellValue());
    }
    if (cn==0) {
    outputSheet.createRow(rowNum).createCell(cn).setCe llValue(nvalue);
    } else {
    outputSheet.getRow(rowNum).createCell(cn).setCellV alue(nvalue);
    }
    break;
    case XSSFCell.CELL_TYPE_STRING:
    cellData = c.getStringCellValue();
    if (cn==0) {
    outputSheet.createRow(rowNum).createCell(cn).setCe llValue(cellData);
    } else {
    outputSheet.getRow(rowNum).createCell(cn).setCellV alue(cellData);
    }
    break;

    }
    }
    }
    // Calculating The Quaterly Over Quaterly Calculation.//

    int lastColumn1 = Math.max(r.getLastCellNum(), 2);
    int ls1 = lastColumn1+1;
    Cell QOQ ;
    QOQ = r.getCell(13);
    outputSheet.getRow(rowNum).createCell(lastColumn1) .setCellValue("Q1-Q2(15)");

    c1 = r.getCell(13);
    c2 = r.getCell(14);
    DecimalFormat df = new DecimalFormat("#%");
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = (c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue();

    outputSheet.getRow(rowNum).createCell(lastColumn). setCellValue(df.format(nvalue));
    }
    QOQ = r.getCell(14);
    outputSheet.getRow(rowNum).createCell(ls1).setCell Value("Q2-Q3(15)");
    c1 = r.getCell(14);
    c2 = r.getCell(15);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = (float) ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls).setCellV alue(df.format(nvalue));
    //System.out.println("Value of the cell"+ Math.round(nvalue));
    }
    QOQ = r.getCell(15);
    outputSheet.getRow(rowNum).createCell(ls1+1).setCe llValue("Q3-Q4(15)");
    c1 = r.getCell(15);
    c2 = r.getCell(16);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+1).setCel lValue(df.format(nvalue));
    }


    QOQ = r.getCell(16);
    outputSheet.getRow(rowNum).createCell(ls1+2).setCe llValue("Q4(15)-Q1(16)");
    c1 = r.getCell(16);
    c2 = r.getCell(17);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+2).setCel lValue(df.format(nvalue));
    }
    QOQ = r.getCell(17);
    outputSheet.getRow(rowNum).createCell(ls1+3).setCe llValue("Q1-Q2(16)");
    c1 = r.getCell(17);
    c2 = r.getCell(18);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+3).setCel lValue(df.format(nvalue));
    }
    QOQ = r.getCell(18);
    outputSheet.getRow(rowNum).createCell(ls1+4).setCe llValue("Q2-Q3(16)");

    c1 = r.getCell(18);
    c2 = r.getCell(19);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+4).setCel lValue(df.format(nvalue));
    }
    QOQ = r.getCell(19);
    outputSheet.getRow(rowNum).createCell(ls1+5).setCe llValue("Q3-Q4(16)");

    c1 = r.getCell(19);
    c2 = r.getCell(20);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+5).setCel lValue(df.format(nvalue));
    }

    QOQ = r.getCell(20);
    outputSheet.getRow(rowNum).createCell(ls1+6).setCe llValue("Q4(16)-Q1(17)");
    c1 = r.getCell(20);
    c2 = r.getCell(21);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+6).setCel lValue(df.format(nvalue));
    }

    QOQ = r.getCell(21);
    outputSheet.getRow(rowNum).createCell(ls1+7).setCe llValue("Q1-Q2(17)");
    c1 = r.getCell(21);
    c2 = r.getCell(22);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+7).setCel lValue(df.format(nvalue));
    }

    QOQ = r.getCell(22);
    outputSheet.getRow(rowNum).createCell(ls1+8).setCe llValue("Q2-Q3(17)");
    c1 = r.getCell(22);
    c2 = r.getCell(23);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+8).setCel lValue(df.format(nvalue));
    }
    QOQ = r.getCell(23);
    outputSheet.getRow(rowNum).createCell(ls1+9).setCe llValue("Q3-Q4(17)");
    c1 = r.getCell(23);
    c2 = r.getCell(24);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+9).setCel lValue(df.format(nvalue));
    }

    QOQ = r.getCell(24);
    outputSheet.getRow(rowNum).createCell(ls1+10).setC ellValue("Q4(17)-Q1(18)");
    c1 = r.getCell(24);
    c2 = r.getCell(25);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+10).setCe llValue(df.format(nvalue));
    }
    QOQ = r.getCell(25);
    outputSheet.getRow(rowNum).createCell(ls1+11).setC ellValue("Q1-Q2(18)");

    c1 = r.getCell(25);
    c2 = r.getCell(26);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+11).setCe llValue(df.format(nvalue));
    }
    QOQ = r.getCell(26);
    outputSheet.getRow(rowNum).createCell(ls1+12).setC ellValue("Q2-Q3(18)");
    c1 = r.getCell(26);
    c2 = r.getCell(27);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+12).setCe llValue(df.format(nvalue));
    }
    QOQ = r.getCell(27);
    outputSheet.getRow(rowNum).createCell(ls1+13).setC ellValue("Q3-Q4(18)");
    c1 = r.getCell(27);
    c2 = r.getCell(28);
    if(rowNum>1 && c1!=null && c2!=null) {
    double nvalue = ((c2.getNumericCellValue() - c1.getNumericCellValue()) / c1.getNumericCellValue());
    outputSheet.getRow(rowNum).createCell(ls+13).setCe llValue(df.format(nvalue));
    }


    }

    for(int i=13; i<=28; i++) {
    outputSheet.autoSizeColumn(i);
    }



    System.out.println();
    System.out.println((rowCount-1) + " rows added to outputsheet " + outputSheet.getSheetName());
    System.out.println();
    }
    }

    public static void main(String[] args) throws IOException {
    new Create_QOQ_Report ("D:\\Selenium QoQ\\ForecasterData.xlsx" , "D:\\Selenium QoQ\\Ovum-ForecasterQOQ.xlsx");
    }
    }

  2. #2
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    22,337
    Thanks
    53
    Thanked 2,394 Times in 2,348 Posts

    Default Re: Calulating Quaterly On Quaterly Growth.

    Do you have any specific java programming questions?

    Please edit your post and wrap your code with code tags:

    [code]
    **YOUR CODE GOES HERE**
    [/code]

    to get highlighting and preserve formatting.
    If you don't understand my answer, don't ignore it, ask a question.

Similar Threads

  1. Order of growth
    By Scorks in forum Algorithms & Recursion
    Replies: 1
    Last Post: November 13th, 2013, 11:27 AM