sum up the Excel column values using Java

Question

I have a requirement to sum up the Excel Column(1) values based on the Row data found.

My excel file is as follows:

                 column(0)                         column(1)
Row[0]    ECIN - INPUT VALUE (ADD)       NetTradeAllowanceAmount = -600.00
Row[1]    ECIN - INPUT VALUE (ADD)       CashDownPayment = 300.00
Row[2]    ECIN - INPUT VALUE (ADD)       OtherDownPaymentAmount = PATH DOES NOT EXIST
Row[3]    ECIN - INPUT VALUE (ADD)       CashDownPayment = 400.00
Row[4]    ECIN - INPUT VALUE (SUB)       OtherDownPaymentAmount = PATH DOES NOT EXIST
Row[5]    ECIN - INPUT VALUE (SUB)       ManufacturerRebateAmount = 500.00
Row[6]    ECIN - INPUT VALUE (SUB)       DeferredDownPaymentAmount = -700.00
Row[7]    ECIN - INPUT VALUE (SUB)       DeferredDownPaymentAmount = 900.00

First I need to look at Column(0), all the rows:

1.add the column(1) values having rows (ADD) data. (eg: SUM= 300.00 + 400.00 - 600.00  = 700.00 - 600.00 = 100.00)
2.add the column(1) values having rows (SUB) data. (eg: SUM=500.00 - 700.00 + 900.00 = 1400.00 - 700.00 = 700.00)
3.then subtract above two SUMs.                    (eg: 100.00 - 700.00 = 600.00)

I should save this result in some variable and record this value in some other cell.

Note: Program should not consider value = PATH DOES NOT EXIST, even though row is having the data (SUB / ADD).

To some extent I have written the code. it is as follows:

import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
public class Hai
{
public static void main(String[] args)
{
    try 
    {
        FileInputStream file = new FileInputStream(new File("C:/Users/Excel.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(5);
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()) 
        {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext())
            {
                    Cell cell = cellIterator.next();
                    String Tag=cell.getStringCellValue().toString();
                    cell = row.getCell(0+1);
                    if(cell !=null)
                    if(Tag.contains("ADD"))
                        {
                           String Tag1=cell.getStringCellValue().toString();
                           String[] s= Tag1.split("=");
                           //System.out.println(s[1]);
                           if(!s[1].contains("PATH DOES NOT EXIST"))
                           {
                             System.out.println(s[1].trim());
                           }
                        }
                    else if(Tag.contains("SUB"))
                        {
                           String Tag1=cell.getStringCellValue().toString();
                           String[] s= Tag1.split("=");
                           if(!s[1].contains("PATH DOES NOT EXIST"))
                           {
                             System.out.println(s[1].trim());
                           }
                        }
            }
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }   
}
}

Output I am getting is as follows :

-600.00
300.00
400.00
500.00
-700.00
900.00

The above values are in string format, I want to sum up these values. Please help me!

I have converted above values to Flaot like this:

Float foo = Float.parseFloat(s[1].trim());

output I got is:

-600.0
300.0
400.0

I want to get two decimal digits and sumup these values. I could not able to sumup the values.

Is it like this

import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
 public class Hai
{
public static double getSubstraction(double summ, 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);
    }

}
 public static double getSumm(double sub, 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);
        }
    }
public static void main(String[] args)
{
    try 
    {
        double summ, sub;
        FileInputStream file = new FileInputStream(new File("C:/Users/Pradeep.HALCYONTEKDC/Desktop/19-04-2013.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(5);
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()) 
        {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext())
            {
                    Cell cell = cellIterator.next();
                    String Tag=cell.getStringCellValue().toString();
                    cell = row.getCell(0+1);
                    if(cell !=null)
                    if(Tag.contains("ADD"))
                        {
                           String Tag1=cell.getStringCellValue().toString();
                           String[] s= Tag1.split("=");
                           //System.out.println(s[1]);
                           if(!s[1].contains("PATH DOES NOT EXIST"))
                           {
                               getSumm() ;
                             Float foo = Float.parseFloat(s[1].trim());
                             System.out.println("1----  "+foo);
                             for(int i=0; i<5;i++)
                             {
                                 foo+=foo;
                                 //System.out.println(foo);
                             }
                           }
                        }
                    else if(Tag.contains("SUB"))
                        {
                           String Tag1=cell.getStringCellValue().toString();
                           String[] s= Tag1.split("=");
                           if(!s[1].contains("PATH DOES NOT EXIST"))
                           {
                               getSubstraction();
                             System.out.println(s[1].trim());
                           }
                        }


            }
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }   
}
}

Help me out in this.

You can convert them to int http://stackoverflow.com/questions/5585779/how-to-convert-string-to-int-in-java — Ishikawa Yoshi, Apr 19 '13 at 10:52

Ishikawa Yoshi · Accepted Answer · 2013-04-19T12:33:15.183

if you want to use java to manipulate excel data the best choise for me was apache POI There're a lot's of tutorials on official site, if you ned some help in code you can ask and we try to help you.

Add this two methods to your code

private static double getSubstraction(double summ, String your) {
    if (your.contains("-")) {
        return summ + Double.parseDouble(your.replace("-", ""));
    } else if (your.contains("+")) {
        return summ - Double.parseDouble(your.replace("+", ""));
    } else {
        return summ  - Double.parseDouble(your);
    }

}

private static double getSumm(double sub, String your) {
    if (your.contains("-")) {
        return sub - Double.parseDouble(your.replace("-", ""));
    } else if (your.contains("+")) {
        return sub  + Double.parseDouble(your.replace("+", ""));
    } else {
        return sub + Double.parseDouble(your);
    }
}

define two global variables double summ and double sub for example

public class MainCreator {

    public static void main(String[] args) throws IOException {
        FileInputStream file = new FileInputStream(new File("workbook.xls"));
        Workbook wb = new HSSFWorkbook(file);
        Sheet sh = wb.getSheetAt(0);
        int lastRownum = sh.getLastRowNum();
        double summ = 0;
        double sub = 0;

        for (int i = 0; i < lastRownum + 1; i++) {
            Row row = sh.getRow(i);

            Cell cell1 = row.getCell(1);
            Cell cell2 = row.getCell(2);

            if (cell1 != null && cell2 != null) {
                String cellValue1 = cell1.getStringCellValue();
                String cellValue2 = cell2.getStringCellValue();

                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("Summ = " + summ);
        System.out.println("Sub = " + sub);
    }

    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);
        }
    }
}

add this two methods to your class, first method substract second one is summ, don't forghet to add two variables in the begining if your main method, and call this method in your code in ADD use getSumm() in SUB use getSubstract() — Ishikawa Yoshi, Apr 19 '13 at 11:42
so you have java.lang.ArrayIndexOutOfBoundsException: what you have on line 28 ? — Ishikawa Yoshi, Apr 19 '13 at 12:43
it looks like that you haven't got enough carma, do you have skype? — Ishikawa Yoshi, Apr 19 '13 at 12:48
java.lang.ArrayIndexOutOfBoundsException means that you try to get absent element check your excel again maybe you didn't split properly any of the cells. — Ishikawa Yoshi, Apr 19 '13 at 13:05
i can't understand logic properly, as i understan you have fields with ADD in first column and number in second you collect all numbers and summirize, the same thing with substraction. Anyway try to look in code, and understand what works wrong, then try to do it yourself, after that if you'll have any question ask it. Try to add more details as it possible — Ishikawa Yoshi, Apr 19 '13 at 14:10
Hi buddy, Please provide me the solution to the requirement I posted in the url http://stackoverflow.com/questions/16194765/excel-calculation-code-written-in-java-must-be-performed-required-no-of-times-d. Thank you. — Java Developer, Apr 24 '13 at 14:42

sum up the Excel column values using Java

1 Answers1