sum up the Excel column values using Java -


i have requirement sum excel column(1) values based on row data found.

my excel file 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 not exist row[3]    ecin - input value (add)       cashdownpayment = 400.00 row[4]    ecin - input value (sub)       otherdownpaymentamount = path 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 need @ column(0), rows: 

1.add column(1) values having rows (add) data. (eg: sum= 300.00 + 400.00 - 600.00  = 700.00 - 600.00 = 100.00) 2.add 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 2 sums.                    (eg: 100.00 - 700.00 = 600.00)

i should save result in variable , record value in other cell.

note: program should not consider value = path not exist, though row having data (sub / add).

to extent have written code. 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 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 not exist"))                            {                              system.out.println(s[1].trim());                            }                         }             }         }     } catch (filenotfoundexception e) {         e.printstacktrace();     } catch (ioexception e) {         e.printstacktrace();     }    } }

output getting follows :

-600.00
300.00
400.00
500.00
-700.00
900.00

the above values in string format, want sum these values. please me!

i have converted above values flaot this: 

float foo = float.parsefloat(s[1].trim());

output got is:

-600.0
300.0
400.0

i want 2 decimal digits , sumup these values. not able sumup values.

is 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 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 not exist"))                            {                                getsubstraction();                              system.out.println(s[1].trim());                            }                         }               }         }     } catch (filenotfoundexception e) {         e.printstacktrace();     } catch (ioexception e) {         e.printstacktrace();     }    } }

help me out in this.

if want use java manipulate excel data best choise me apache poi there're lot's of tutorials on official site, if ned in code can ask , try you.

add 2 methods 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 2 global variables double summ , double sub 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;          (int = 0; < 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 not exist")) {                         system.out.println("path not exist");                     } else {                         system.out.println(cellvalue1 + "/" + stringnumber);                         summ = getsumm(summ, stringnumber);                     }                  } else if (cellvalue1.contains("sub")) {                     if (cellvalue2.split("=")[1].trim().contains("path not exist")) {                         system.out.println("path 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);         }     } }

Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers ...
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());...
Read more