Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Java Recursively sum up the Excel column values

1005447
Member Posts: 1
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");
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().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(o) 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.
Edited by: 1002444 on Apr 25, 2013 11:20 PM
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");
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().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(o) 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.
Edited by: 1002444 on Apr 25, 2013 11:20 PM
Tagged:
This discussion has been closed.