Javatpoint Logo

91-9990449935

 0120-4256464

JavaTpoint

Reading excel file and inserting data into database

By: mahboob.ignou@gmail.com On: Sat Jun 18 15:09:57 IST 2016     0 0 0  0
Sir I have to write a java program which reads the data from the excel sheet and insert the data into database .How we map the excel sheet coloumn into database table coloumn .Please help me0

 
For inserting Excel file data into database, First we have to read the Excel file.Then insert data into database.
For reading Excel file,we will use Apache POI. Download the POI library from following link:
http://poi.apache.org/download.html
Extract zip files from this and use following library in your project:
poi-ooxml-schemas-3.8-20120326
poi-3.8-20120326
dom4j-1.6.1
xmlbeans-2.3.0

For MySql connection use mysql-connector-java-5.1.7-bin jar file. Include all these jars into lib folder.
Now create a java bean for connecting to MySQL database.Its not necessary ,you can also create connection in your jsp page.
Hierarchy of DB_Connection.java(Java-Bean).

Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java.

Writing an excel file code
Writing a file using POI is very simple and involve following steps:

Create a workbook
Create a sheet in workbook
Create a row in sheet
Add cells in sheet
Repeat step 3 and 4 to write more data.

public class WriteExcelDemo
{
public static void main(String[] args)
{
//Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();

//Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Employee Data");

//This data needs to be written (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();
data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
data.put("2", new Object[] {1, "Amit", "Shukla"});
data.put("3", new Object[] {2, "Lokesh", "Gupta"});
data.put("4", new Object[] {3, "John", "Adwards"});
data.put("5", new Object[] {4, "Brian", "Schultz"});
//Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset)
{
Row row = sheet.createRow(rownum++);
Object [] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr)
{
Cell cell = row.createCell(cellnum++);
if(obj instanceof String)
cell.setCellValue((String)obj);
else if(obj instanceof Integer)
cell.setCellValue((Integer)obj);
}
}
try
{
//Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
workbook.write(out);
out.close();
System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
......................

Reading an excel file code

Reading an excel file is also very simple if we divide this in steps.

Create workbook instance from excel sheet
Get to the desired sheet
Increment row number
iterate over all cells in a row
repeat step 3 and 4 until all data is read.

//import statements
public class ReadExcelDemo
{
public static void main(String[] args)
{
try
{
FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));

//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();

while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
//Check the cell type and format accordingly
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "t");
break;
}
}
System.out.println("");
}
file.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

hope you will understand this example.
0
By: mohammadsaleembasha@gmail.com On: Mon Jun 20 06:58:38 IST 2016 0 0 0 0
Are You Satisfied :0Yes0No
 
Sir I have to map excel row with database table row?How we mapp it is not described in this soltion.Please describe how to map the row of excel row with database table row?0
By: mahboob.ignou@gmail.com On: Mon Jun 20 09:49:38 IST 2016 0 0 0 0
Are You Satisfied :0Yes0No


PLEASE REPLY

Please login first to post reply. Login please!