Results 1 to 6 of 6
  1. #1
    abhi1402 is offline Member
    Join Date
    Feb 2014
    Posts
    1
    Rep Power
    0

    Default Reding excel file

    guyss this is the code for reading and sending the mail










    package com.abhi;



    import java.io.BufferedReader;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileReader;
    import java.io.IOException;
    import java.io.InputStreamReader;
    import java.util.ArrayList;
    import java.util.Collection;
    import java.util.Collections;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Properties;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;

    public class Main {

    public static FileReader reader;

    public static Properties properties = new Properties();
    private static String password = "";

    public static void main(String[] args)
    {




    String dataSourceType = "";
    try {

    reader = new FileReader("D:\\APNAKHATA\\CS\\Java\\Resource\\pro perties1.properties");
    properties.load(reader);
    dataSourceType = properties.getProperty("DataSource_Type");


    int datasourceType = 0;

    if(dataSourceType.equalsIgnoreCase("XLS")){
    datasourceType = 1;
    }

    if(dataSourceType.equalsIgnoreCase("XML")){
    datasourceType = 2;
    }
    if(dataSourceType.equalsIgnoreCase("ORACLE")){
    datasourceType = 3;
    }
    if(dataSourceType.equalsIgnoreCase("MySQL")){
    datasourceType = 4;
    }

    switch(datasourceType)
    {
    case 1: readFromXLS();
    case 2: readFromXML();
    case 3: readFromOracle();
    case 4: readFromMySql();

    }
    }
    catch (FileNotFoundException e) {
    System.out.println("cannot find the file! exiting program!!");
    System.exit(0);
    } catch (IOException e) {
    System.out.println("cannot read from the file! exiting program!!");
    System.exit(0);
    }


    }

    private static void readFromMySql() {
    // TODO Auto-generated method stub

    }

    private static void readFromOracle() {
    // TODO Auto-generated method stub

    }

    private static void readFromXML() {
    // TODO Auto-generated method stub

    }

    private static void readFromXLS() {
    String xls_file_path = "D:\\APNAKHATA\\CS\\Java\\Resource\\maildetail.xls ";
    //xls_file_path = properties.getProperty("file_path");
    try {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(xls_file_path));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    //System.out.println("file:"+xls_file_path);
    int rows; // No of rows

    rows = sheet.getPhysicalNumberOfRows();
    System.out.println("No. of rows:"+rows);

    HashMap namesMap =new HashMap<>();
    HashMap socnamesmap=new HashMap<>();
    HashMap monthMap =new HashMap<>();
    HashMap currencyMap =new HashMap<>();
    HashMap billamntMap =new HashMap<>();
    HashMap duedateMap =new HashMap<>();
    HashMap emailMap =new HashMap<>();
    HashMap OutputMap =new HashMap<>();

    int cols = 0; // No of columns

    for(int r = 0; r <=rows; r++) {
    row = sheet.getRow(r);
    if(row != null) {
    cols = sheet.getRow(r).getPhysicalNumberOfCells();
    for(int c = 0; c < cols; c++) {
    cell = row.getCell((short) c);
    if(cell != null)
    {
    for (r = 0; r <=rows; r++)
    {

    if(cell.getCellType()== 1 || cell.getCellType()== 2)
    {
    if(c == 1)
    namesMap.put(r, cell.getNumericCellValue());

    if(c == 2)
    socnamesmap.put(r, cell.getStringCellValue());

    if(c == 3)
    monthMap.put(r, cell.getStringCellValue());

    if(c == 4)
    currencyMap.put(r, cell.getStringCellValue());

    if(c == 5)
    billamntMap.put(r, cell.getStringCellValue());

    if(c == 6)
    duedateMap.put(r, cell.getNumericCellValue());

    if(c == 7)
    emailMap.put(r, cell.getNumericCellValue());

    if(c == 8)
    emailMap.put(r, cell.getStringCellValue());
    }
    if (cell.getCellType()==1 && c == 2 && c == 3 && c == 4 && c == 5 && c == 6 && c == 7 && c == 8)
    {
    OutputMap.put(r, cell.getNumericCellValue());
    }
    }
    }
    }
    }

    System.out.println("name:"+namesMap);
    System.out.println("Society Name:"+socnamesmap);
    System.out.println("Month:"+monthMap);
    System.out.println("Currency:"+currencyMap);
    System.out.println("Bill Amount:"+billamntMap);
    System.out.println("Due Date:"+duedateMap);
    System.out.println("Email-id:"+emailMap);
    Collection OutputList = OutputMap.values();
    List list = new ArrayList(OutputList);
    // Collections.sort(list);
    // Collections.reverse(list);
    // System.out.println("ticketList:"+list);


    Integer[] OutputArray = new Integer[list.size()];

    for (int i = 0; i < list.size(); i++) {
    OutputArray[i] = getKey(OutputMap, list.get(i));
    }
    // for (int i = 0; i < sortedArray.length; i++) {
    // System.out.println(sortedArray[i]);
    // }

    //System.out.println("Sorted list:");
    // for (int i = 0; i < sortedArray.length; i++) {
    //System.out.println("Name:"+namesMap.get(sortedArra y[i]));
    //System.out.println("No Of Tickets:"+ticketMap.get(sortedArray[i]));
    //System.out.println("email:"+emailMap.get(sortedArr ay[i]));
    //}

    int totalNoOfMembers = OutputArray.length;
    // if(OutputArray.length>10) totalNoOfValuedCustomers = 10;

    // int i = OutputArray.length;
    String password_text = properties.getProperty("password_text");
    System.out.println(password_text);
    BufferedReader bufferRead = new BufferedReader(new InputStreamReader(System.in));
    password = bufferRead.readLine();

    for (int i = 0; i < totalNoOfMembers; i++)
    {
    //SendMail.sendMailToMembers((String)emailMap.get(Ou tputArray[i]), (String)namesMap.get(OutputArray[i]),(String)socnamesmap.get(OutputArray[i]), (String)monthMap.get(OutputArray[i]) , (String)currencyMap.get(OutputArray[i]) ,(double)billamntMap.get(OutputArray[i]), (double)duedateMap.get(OutputArray[i]) ,password);
    }
    }
    //}
    // }
    }
    catch(Exception ioe) {
    ioe.printStackTrace();
    }
    }

    static Integer getKey(HashMap<Integer, String> map, Object value) {
    Integer key = null;
    for(Map.Entry<Integer, String> entry : map.entrySet()) {
    if((value == null && entry.getValue() == null) || (value != null && value.equals(entry.getValue()))) {
    key = entry.getKey();
    break;
    }
    }
    return key;
    }
    }






    its showing error like..


    BIFFVIEWER REQUIRES A FILENAME***
    java.lang.NullPointerException
    at java.io.FileInputStream.<init>(Unknown Source)
    at java.io.FileInputStream.<init>(Unknown Source)
    at org.apache.poi.hssf.dev.BiffViewer.run(BiffViewer. java:68)
    at org.apache.poi.hssf.dev.BiffViewer.main(BiffViewer .java:649)

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    929
    Rep Power
    2

    Default Re: Reding excel file

    There is a space at the end of "D:\\APNAKHATA\\CS\\Java\\Resource\\maildetail .xls ". I am pretty sure that's not supposed to be there. I am not sure though if Windoze would choke on that.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  3. #3
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,712
    Rep Power
    5

    Default Re: Reding excel file

    Might be the wonky forum formatting, just check out your own post and see that it added one there too :)

    The exception is an NPE, which could only occur if the filename being passed is null. That is simply impossible when the code is in its current state with or without the additional space, so I can only assume that it was not properly recompiled before running it again.
    Last edited by gimbal2; 02-04-2014 at 10:26 AM.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  4. #4
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    929
    Rep Power
    2

    Default Re: Reding excel file

    AHA! Your switch statement doesn't have break in the cases! I WIN!!
    Java Code:
    switch(datasourceType)
    {
    case 1: readFromXLS();
    case 2: readFromXML();
    case 3: readFromOracle();
    case 4: readFromMySql();
    
    }
    Edit: the other methods are empty, so that can't be causing the NPE. I nearly won :)
    Last edited by SurfMan; 02-04-2014 at 11:03 AM.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  5. #5
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    929
    Rep Power
    2

    Default Re: Reding excel file

    Also, you have an if-statement for parsing the datasourceType from the properties file, create an int, then use a switch-statement. As long as you have the if statements in place, why the switch statment? That's kinda double the work...
    Java Code:
    dataSourceType = properties.getProperty("DataSource_Type");
    
    
    int datasourceType = 0;
    
    if(dataSourceType.equalsIgnoreCase("XLS")){
    datasourceType = 1;
    }
    
    if(dataSourceType.equalsIgnoreCase("XML")){
    datasourceType = 2;
    }
    if(dataSourceType.equalsIgnoreCase("ORACLE")){
    datasourceType = 3;
    }
    if(dataSourceType.equalsIgnoreCase("MySQL")){
    datasourceType = 4;
    }
    
    switch(datasourceType)
    {
    case 1: readFromXLS();
    case 2: readFromXML();
    case 3: readFromOracle();
    case 4: readFromMySql();
    
    }
    could be

    Java Code:
    dataSourceType = properties.getProperty("DataSource_Type");
    
    if(dataSourceType.equalsIgnoreCase("XLS")){
       readFromXLS();
    }
    else if(dataSourceType.equalsIgnoreCase("XML")){
       readFromXML();
    }
    else if(dataSourceType.equalsIgnoreCase("ORACLE")){
       readFromOracle();
    }
    else if(dataSourceType.equalsIgnoreCase("MySQL")){
       readFromMySql();
    }
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  6. #6
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    3,712
    Rep Power
    5

    Default Re: Reding excel file

    Clearly, the OP needs to regain understanding of his own code and clean it up. that's not something we can with.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

Similar Threads

  1. Converting excel file to text file
    By saranya_v11 in forum New To Java
    Replies: 3
    Last Post: 07-05-2013, 10:22 AM
  2. Excel File Verification
    By moo_rad in forum Apache POI
    Replies: 1
    Last Post: 10-24-2012, 01:40 PM
  3. Excel File Reader
    By Subhransu in forum Advanced Java
    Replies: 6
    Last Post: 03-02-2011, 09:30 AM
  4. what's the token for excel file?
    By anthrax in forum New To Java
    Replies: 3
    Last Post: 01-27-2010, 07:19 AM
  5. How to Read Excel file??
    By spalax in forum New To Java
    Replies: 3
    Last Post: 08-15-2009, 05:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •