aspose file tools*
The moose likes Other Open Source Projects and the fly likes Excel Calculation Code written in java must be performed required no. of times depending on conditio Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Excel Calculation Code written in java must be performed required no. of times depending on conditio" Watch "Excel Calculation Code written in java must be performed required no. of times depending on conditio" New topic
Author

Excel Calculation Code written in java must be performed required no. of times depending on conditio

Java Pradeep Kumar Kumar
Greenhorn

Joined: Feb 22, 2013
Posts: 7
I have a requirement to perform some calculations using java taking inputs from Excel file.

My Excel file content is as follows:


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) DeferredDownPaymentAmount = 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) DeferredDownPaymentAmount = 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 fixed 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, FileNotFoundException, IOException
{
try
{
Workbook workbook = WorkbookFactory.create(new FileInputStream("C:/Users/Pradeep.HALCYONTEKDC/Desktop/Excel.xlsx"));
Sheet sheet = workbook.getSheet("ROLLUPS - Results");
int lastRowNum = sheet.getLastRowNum();
double summ = 0;
double sub = 0;
double result=0;

for (int i = 0; i < lastRowNum; 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().contains("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().contains("PATH DOES NOT EXIST"))
{
//System.out.println("Path Does Not Exist");
}
else
{
//System.out.println(cellValue1 + "/" + stringNumber);
sub = getSubstraction(sub, stringNumber);
}
}
/* 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 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);
}
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 having cell value RESULTS like below, my program should perform the same logic repeatedly until it finds empty row. i.e., if program find empty row after RESULTS row stop the loop, else continue the loop to perform the no.of individual 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) DeferredDownPaymentAmount = 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) DeferredDownPaymentAmount = 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 EXIST
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 EXIST
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 idea 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 case.

If my requirement is not clear, please let me know. Thank you.



[Thumbnail for excel.jpg]

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Excel Calculation Code written in java must be performed required no. of times depending on conditio