import Data from Excel using Java

import Data from Excel using Java

Import Data from Excel using Java

Before we commence to Import Data from Excel Sheet into MySQL Database using Apache POI, it has to be stated that jExcel only reads data from Excel 95, 97, 2000 workbooks, if you want to read Excel files created after Excel 2000, then you need to use apache POI (Note: if you attempt to access an Excel file created after 2000, you will get the exception “Unable to recognize OLE Stream”)

The jExcel API is open source and can be downloaded from here. It allows Software Developers read excel spreadsheets dynamically
To read data from an Excel Spreadsheet stored on your computer, you will need to create a workbook i.e.
Workbook workbook = Workbook.getWorkbook(new File(“myfile.xls”));
After storing the Excel file in a workbook, you can then access the sheets, the first sheet will be 0 i.e.
Sheet sheet = workbook.getSheet(0);
Once you have assigned an object of sheet, you can now proceed to get the individual cell values in the sheet as a String value i.e.

Cell a1 = sheet.getCell(0,0);
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);


String stringa1 = a1.getContents();
String stringb2 = b2.getContents();
String stringc2 = c2.getContents();

After getting your desired contents, it is advisable to close the workbook object free up some resources in the memory i.e.

workbook.close();

However not all cells may have String values and you may need to get the original value (i.e. Numeric, Date etc.). To achieve this, the cell must be cast to the correct type. The code snippet below shows how this can be achieved


String stringa1 = null;
double numberb2 = 0;
Date datec2 = null;

Cell a1 = sheet.getCell(0,0);
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);

if (a1.getType() == CellType.LABEL)
{
LabelCell lc = (LabelCell) a1;
stringa1 = lc.getString();
}

if (b2.getType() == CellType.NUMBER)
{
NumberCell nc = (NumberCell) b2;
numberb2 = nc.getValue();
}

if (c2.getType() == CellType.DATE)
{
DateCell dc = (DateCell) c2;
datec2 = dc.getDate();
}

// Do stuff with dates and doubles
Thank you for Reading this post, if you have any questions, kindly post a comment below