Fetch Data From Excel

Question

I have to Fetch the data from Excel file which is Continuosly Changing. I have made a code in java it read the data from execl but not changing it. When i click the save button in excel and then run my code it will get the changing data.

private static void fuctionCall() throws BiffException, IOException {
     // TODO Auto-generated method stub
     Workbook workbook = Workbook.getWorkbook(new java.io.File("C:/ODIN/DIET/Arbitrage.xls"));
     Sheet sheet = workbook.getSheet(0);
     /*Cell a1 = sheet.getCell(0,0); String s1=a1.getContents(); System.out.println("My name is "+s1); */ 
     for(int i=0;i<sheet.getColumns();i++) { 
     for(int j=1;j<sheet.getRows();j++) { 
     Cell cell=sheet.getCell(i, j); 
     System.out.println(" "+cell.getContents());
} 

Answer 1

The problem is because the excel data is not saved. I was also dealing with the same problem and got up with a different solution which worked for me. I just created a macro in excel to save the excel workbook whenever it's cell values got changed. Now I got the excel file with up-to-date saved data which can be read through java code and can be used for other purposes.

I'll post my macro code and java code which I used to retrieve the data from excel,

Macro Code

Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A6:A12,B6:B12,L6:L12,O6:O12,P6:P12,Y6:Y12")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    Do Until Not IsFileLocked(ThisWorkbook.Name)
        If IsFileLocked(ThisWorkbook.Name) Then
            Application.Wait Now + TimeSerial(0, 0, 0.5)
        End If
    Loop

    ActiveWorkbook.Save

End If

Set KeyCells = Nothing

End Sub

Function IsFileLocked(filePath As String) As Boolean
On Error Resume Next
Open filePath For Binary Access Read Write Lock Read Write As #1
Close #1
If Err.Number <> 0 Then
    IsFileLocked = True
    ' Application.StatusBar = "Waiting for file to close"
    Err.Clear
Else
    IsFileLocked = False
    ' Application.StatusBar = ""
End If
End Function

Java Code

    fileName="C:\\ODIN\\Diet\\Arbitrage.xls";
 public boolean readExcel(String fileName)
 {        
    ArrayList newList=null;
    FileInputStream fis=null;    
    POIFSFileSystem poifs=null;
    HSSFWorkbook hssfwb=null;
    HSSFSheet hssfs=null;
    Iterator rowIterate=null;

    try
    {            
        newList=new ArrayList();
        fis=new FileInputStream(fileName);
        poifs=new POIFSFileSystem(fis);
        hssfwb=new HSSFWorkbook(poifs);
        hssfs=hssfwb.getSheetAt(0);
        rowIterate=hssfs.rowIterator();

        while(rowIterate.hasNext())
        {
            HSSFRow row=(HSSFRow) rowIterate.next();
            Iterator cellIterate=row.cellIterator(); 
            ArrayList<HSSFCell> cellList=new ArrayList<>();

            while(cellIterate.hasNext())
            {
                HSSFCell cell=(HSSFCell) cellIterate.next();
                cellList.add(cell);                    
            }
            newList.add(cellList);
        }

        for(int i=0;i<newList.size();i++)
        {
            ArrayList<HSSFCell> cellList=(ArrayList<HSSFCell>) newList.get(i);

            for(int j=0;j<cellList.size();j++)
            {
                HSSFCell cell=cellList.get(j);
                System.out.println("Cell Values: "+cell);
            }

            System.out.println("----------------------");

        }            

    }
    catch(Exception e)
    {
        log.error(e.getMessage());
    }

    return true;
}

The next step you need to know is how to create a macro and save it so that at next restart of excel application your macro will be working fine without any user intervention.

Your excel file will be already present inside the above mentioned location. Now I'll explain each step to proceed further in MS Excel 2007.

1. Open the excel sheet
2. Goto Office Button-> Excel Options-> check "Show developer tab" option
3. Open Developer tab and Click Visual Basic Icon.
4. Your code window will be opened now, in that create a new Module from project explorer.
5. Paste the above code there and inside all sheets.
6. Now you have to change the macro security settings to low.
7. Goto Developer tab and click macro security.
8. Select "Enable all Macros" radio button and check "Trust access to VBA project object model" option.
9. Now save and close the excel, that's it.

But this will lead to performance issue because the saving process will be performed at each cell change.