Java Recursively sum up the Excel column values
I have a requirement to perform some calculations using java taking inputs =
from Excel file.
My Excel file content is as follows:
column(0) column(1)
Row[0] ECOUT - EXPECTED VALUE TotalDownPaymentAmount = 900.00
Row[1] ECIN - INPUT VALUE (ADD) NetTradeAllowanceAmount = -600.00
Row[2] ECIN - INPUT VALUE (ADD) CashDownPayment = 100.00
Row[3] ECIN - INPUT VALUE (ADD) OtherDownPaymentAmount = PATH DOES NOT=
EXIST
Row[4] ECIN - INPUT VALUE (ADD) ManufacturerRebateAmount = 250.00
Row[5] ECIN - INPUT VALUE (ADD) DeferredPaymentAmount = PATH DOES NOT =
EXIST
Row[6] ECIN - INPUT VALUE (ADD)
Row[7] ECIN - INPUT VALUE (SUB) TotalDownPaymentAmount = 900.00
Row[8] ECIN - INPUT VALUE (SUB) NetTradeAllowanceAmount = -600.00
Row[9] ECIN - INPUT VALUE (SUB) CashDownPayment = 100.00
Row[10] ECIN - INPUT VALUE (SUB) OtherDownPaymentAmount = PATH DOES NOT=
EXIST
Row[11] ECIN - INPUT VALUE (SUB) ManufacturerRebateAmount = 250.00
Row[12] ECIN - INPUT VALUE (SUB) DeferredPaymentAmount = PATH DOES NOT =
EXIST
Row[13] ECIN - INPUT VALUE (SUB)
Row[14]
Row[15] ECOUT EXPECTED VALUE 900.00
Row[16] ECOUT ACTUAL VALUE 900.00
Row[17] RESULTS PASS
To perform one calculation there can be any no.of rows but columns are fixe=
d i.e., column(0) & column(1). My calculation logic in java is as follows:
import java.io.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadXlsxXls
{
public static void main(String[] args) throws Exception, FileNotFou=
ndException, IOException
{
try
{
Workbook workbook = WorkbookFactory.create(new FileInputStream("C:/Users=
/Pradeep.HALCYONTEKDC/Desktop/Excel.xlsx"));
Sheet sheet = workbook.getSheet("ROLLUPS - Results");
double summ = 0;
double sub = 0;
double result=0;
for (int i = 0; i < sheet.getLastRowNum(); i++)
{
Row row = sheet.getRow(i);
Cell cell1 = row.getCell(0);
Cell cell2 = row.getCell(1);
if (cell1 != null && cell2 != null)
{
String cellValue1 = cell1.getStringCellValue(=
);
String cellValue2 = cell2.getStringCellValue(=
);
if(cellValue2.contains("="))
{
String stringNumber = cellValue2.split("=
=")[1].trim();
if (cellValue1.contains("ADD"))
{
if (cellValue2.split("=")[1].trim().c=
ontains("PATH DOES NOT EXIST"))
{
//System.out.println("Path Does Not=
Exist");
}
else
{
//System.out.println(cellValue1 + "=
/" + stringNumber);
summ = getSumm(summ, stringNumber=
);
}
}
else if (cellValue1.contains("SUB"))
{
if (cellValue2.split("=")[1].trim().c=
ontains("PATH DOES NOT EXIST"))
{
//System.out.println("Path Does Not=
Exist");
}
else
{
//System.out.println(cellValue1 + "=
/" + stringNumber);
sub = getSubstraction(sub, string=
Number);
}
}
/* else
{
System.out.println("Smt wrong");
}*/
}
}
}
System.out.println("ADD = " + summ);
System.out.println("SUB = " + sub);
result=summ-sub;
System.out.println("RESULT = " +result+"0");
}
catch(NullPointerException e)
{
e.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
}
private static double getSubstraction(double main, String y=
our)
{
if (your.contains("-"))
return main + Double.parseDouble(your.replace("-", ""))=
;
else if (your.contains("+"))
return main - Double.parseDouble(your.replace("+", ""))=
;
else
return main - Double.parseDouble(your);
}
private static double getSumm(double main, String your)
{
if (your.contains("-"))
return main - Double.parseDouble(your.replace("-", ""))=
;
else if (your.contains("+"))
return main + Double.parseDouble(your.replace("+", ""))=
;
else
return main + Double.parseDouble(your);
}
}
Up to here fine. If there exists any more data in the rows after the row ha=
ving cell value RESULTS like below, my program should perform the same logi=
c repeatedly until it finds empty row. i.e., if program find empty row afte=
r RESULTS row stop the loop, else continue the loop to perform the no.of in=
dividual calculations.
column(0) column(1)
Row[0] ECOUT - EXPECTED VALUE TotalDownPaymentAmount = 900.00
Row[1] ECIN - INPUT VALUE (ADD) NetTradeAllowanceAmount = -600.00
Row[2] ECIN - INPUT VALUE (ADD) CashDownPayment = 100.00
Row[3] ECIN - INPUT VALUE (ADD) OtherDownPaymentAmount = PATH DOES NOT=
EXIST
Row[4] ECIN - INPUT VALUE (ADD) ManufacturerRebateAmount = 250.00
Row[5] ECIN - INPUT VALUE (ADD) DeferredPaymentAmount = PATH DOES NOT =
EXIST
Row[6] ECIN - INPUT VALUE (ADD)
Row[7] ECIN - INPUT VALUE (SUB) TotalDownPaymentAmount = 900.00
Row[8] ECIN - INPUT VALUE (SUB) NetTradeAllowanceAmount = -600.00
Row[9] ECIN - INPUT VALUE (SUB) CashDownPayment = 100.00
Row[10] ECIN - INPUT VALUE (SUB) OtherDownPaymentAmount = PATH DOES NOT=
EXIST
Row[11] ECIN - INPUT VALUE (SUB) ManufacturerRebateAmount = 250.00
Row[12] ECIN - INPUT VALUE (SUB) DeferredPaymentAmount = PATH DOES NOT =
EXIST
Row[13] ECIN - INPUT VALUE (SUB)
Row[14]
Row[15] ECOUT EXPECTED VALUE 900.00
Row[16] ECOUT ACTUAL VALUE 900.00
Row[17] RESULTS PASS
Row[18]
Row[19] ECOUT - EXPECTED VALUE Amount = 1100.00
Row[20] ECIN - INPUT VALUE (ADD) TradeAllowance = -300.00
Row[21] ECIN - INPUT VALUE (ADD) Cash = 400.00
Row[22] ECIN - INPUT VALUE (ADD) PaymentAmount = PATH DOES NOT EXIST
Row[23] ECIN - INPUT VALUE (ADD) RebateAmount = 950.00
Row[24] ECIN - INPUT VALUE (ADD) DownPaymentAmount = PATH DOES NOT EXI=
ST
Row[25] ECIN - INPUT VALUE (ADD)
Row[26] ECIN - INPUT VALUE (SUB) Total = 900.00
Row[27] ECIN - INPUT VALUE (SUB) NetAllowanceAmount = -600.00
Row[28] ECIN - INPUT VALUE (SUB) CashPayment = 100.00
Row[29] ECIN - INPUT VALUE (SUB) OtherAmount = PATH DOES NOT EXIST
Row[30] ECIN - INPUT VALUE (SUB) RebateAmount = 250.00
Row[31] ECIN - INPUT VALUE (SUB) DownPaymentAmount = PATH DOES NOT EXI=
ST
Row[32] ECIN - INPUT VALUE (SUB)
Row[33]
Row[34] ECOUT EXPECTED VALUE 440.00
Row[35] ECOUT ACTUAL VALUE 320.00
Row[36] RESULTS FAIL
Row[37]
Row[38] ECOUT - EXPECTED VALUE Bell = 200.00
Row[39] ECIN - INPUT VALUE (ADD) Charges = -700.00
Row[40] ECIN - INPUT VALUE (ADD) Expenses = PATH DOES NOT EXIST
Row[41] ECIN - INPUT VALUE (ADD)
Row[42] ECIN - INPUT VALUE (SUB) Cosmetics = 300.00
Row[43] ECIN - INPUT VALUE (SUB) Allowances = -100.00
Row[44] ECIN - INPUT VALUE (SUB) CashPayment = 500.00
Row[45] ECIN - INPUT VALUE (SUB)
Row[46]
Row[47] ECOUT EXPECTED VALUE 640.00
Row[48] ECOUT ACTUAL VALUE 720.00
Row[49] RESULTS FAIL
I could able to write the logic for one calculation, but I don't have any i=
dea to use the same logic to perform no.of times for no.of calculations if =
there exists any more rows after the row RESULTS.Please help me in this cas=
e.
If my requirement is not clear, please let me know. Thank you.