Thread: 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");
}
}