Reading XLS file using Apache POI into 2D array in Java

Question

My assignment is to read an Excel file which looks like this http://postimg.org/image/jxgc6ng51/ and then sum the units of matching product ID and print them out in similar column/row format. Sorry if this is a sloppy way of doing it, I'm new to java and my knowledge of java API is very limited currently... Here is my code:

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

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;

public class Read {

            public static void readXLSFile() throws IOException{
        InputStream ExcelFile = new FileInputStream("C:/Sales Data.xls");
        HSSFWorkbook wb = new HSSFWorkbook(ExcelFile);
                HSSFSheet sheet=wb.getSheetAt(0);
                int numRows=sheet.getPhysicalNumberOfRows();
                int[][]idSale=new int[numRows][2];

                for(int i=1;i<numRows;i++){
                    HSSFCell proId = sheet.getRow(i).getCell(1);
                    HSSFCell sales = sheet.getRow(i).getCell(2);
                    idSale[i][0]=(int)proId.getNumericCellValue();
                    idSale[i][1]=(int)sales.getNumericCellValue();
                    //the data from the excel sheet is copied into the 2D array at this point
                }

                /*for loop to attempt to compare id number of array to the rest of the array
                problem is there are duplicate ID's and it is re-comparing and adding
                the sales total into the duplicate as well. 
                How to make it so it ignores all duplicate ID's or make it so it 
                only prints out the element the first time the ID pops up and doesn't print the other elements with the same ID?*/
                for(int j=1;j<numRows;j++){
                    for(int jj=j+1;jj<numRows;jj++)
                    if(idSale[j][0]==idSale[jj][0]){
                        idSale[j][1]+=idSale[jj][1];
                    }
                }
            }



            public static void main(String[] args) throws IOException {
        readXLSFile();
            }
}

Rahul Yadav · Accepted Answer · 2015-09-01 09:58:02Z

1

You can try with Map in this you will require only one for loop, as below:-

Map<Integer, Integer> map = new HashMap<Integer, Integer>();
     for(int i=1;i<numRows;i++){
         HSSFCell proId = sheet.getRow(i).getCell(1);
         HSSFCell sales = sheet.getRow(i).getCell(2);
         int prodId =(int)proId.getNumericCellValue();
         int currentSales =(int)sales.getNumericCellValue();
         //the data from the excel sheet is copied into the 2D array at this point
        if (map.containsKey(i)) {
            int prevSales = map.get(i);
            map.put(i, prevSales+currentSales);
        } else {
            map.put(i, currentSales);
        }
    }

answered Sep 1, 2015 at 9:58

Rahul Yadav

1,5138 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jnewbie Over a year ago

so I tweaked this code a bit and I think it's right? now I'm not sure how to printout the entire map. I changed int proId to int proId1 and every" i" starting from if(map.containsKey(i) up map.put(i,currentSales) with proId1.

Collectives™ on Stack Overflow

Reading XLS file using Apache POI into 2D array in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related