Java的读取Excel文件后，如何将一个数据库表行 [英] Java- how to insert row in a db table after reading excel file
问题描述
我能够从下面的code读取Excel文件数据，但是的我不能让后面怎么走，才能在POJO类来存储读取Excel文件后，Excel数据的逻辑。 的
简而言之：我对如何把这些读取Excel数据发送到我的模型类的混乱将存储在我的数据库表
I am able to read the excel file data from the below code, but i am unable to get the logic behind how to get excel data in order to store in POJO class after reading the excel file. In short: I have confusion on how to send these read excel data to my model class to be stored in my Database table?
以下code正确打印Excel的排在我的Eclipse控制台：
The following code prints correctly the excel rows in my Eclipse Console:
..............
..............
public String execute()
{
try
{
String filePath=servletRequest.getSession().getServletContext().getRealPath("/");
File fileToCreate= new File(filePath,this.excelDataFileName);
FileUtils.copyFile(this.excelData, fileToCreate);
UploadExcel obj=new UploadExcel();
obj.readExcel(excelData.getAbsolutePath());
}
catch(Exception e){
e.printStackTrace();
addActionError(e.getMessage());
return INPUT;
}
return SUCCESS;
}
/*
*Method to read the each sheet ,row & column of the excel sheet of the uploaded xls file
*/
public void readExcel(String filePath)
{
try
{
FileInputStream file=new FileInputStream(new File(filePath));
//Getting the instance for XLS file
HSSFWorkbook workbook=new HSSFWorkbook(file);
//Get First sheet from the workbook
HSSFSheet sheet=workbook.getSheetAt(0);
ArrayList myList = new ArrayList();
//Iterate start from the first sheet of the uploaded excel file
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext())
{
Row row=rowIterator.next();
if(row.getRowNum()==0)
{
continue;//skip to read the first row of file
}
//For each row, iterate through each coulumns
Iterator<Cell> cellIterator=row.cellIterator();
while(cellIterator.hasNext())
{
Cell cell=cellIterator.next();
if(cell.getColumnIndex()==0)
{
continue;
}
switch(cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t\t");
// myList.add(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue()+ "\t\t");
// myList.add(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue()+ "\t\t");
// myList.add(cell.getStringCellValue());
break;
}
}
System.out.println("");
}
file.close();
FileOutputStream out=
new FileOutputStream(new File(filePath));
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
在控制台输出：
TEXTit 6695 PROSPECT RD Nova Scotia B3z 3t1
row2sdfsda 61695 P sfsdfdsf 23B3z 3t1
我的想法是，
我有一个获得该行之一，我将在此行的数据添加到我的POJO类对象，并将其发送给道，最后使用 saveOrupdate（tableobj）休眠方法将数据保存到我的数据库表。
但我没能想到，我怎么可以将这些数据添加到我的POJO类？
What i thought is,
I have to get the row one by one and i will add this row data to my POJO class object and send it to dao , and finally use saveOrupdate(tableobj) of hibernate method to save the data into my db table.
But i could not able to think, how can i add these data to my Pojo class?
希望有人能帮助我在这里。
Hope someone can help me here.
推荐答案
您可以注入/传引用您的DAO到UploadExcel类。然后，当在READEXCEL（）方法，你可以创建新的实体和值填充它。
You can inject/pass reference to your DAO into UploadExcel class. Then when in readExcel() method you can create new ENTITY and fill it with values.
Entity entity = new Entity();
while(cellIterator.hasNext())
{
Cell cell=cellIterator.next();
if(cell.getColumnIndex()==0)
{
continue;
}
switch(cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
entity.setBooleanValue(cell.getBooleanValue);
break;
...
}
}
最后，你在你的DAO插入/ createOrUpdate你的实体。
Finally you insert/createOrUpdate your entity by your DAO.
dao.insertOrUpdate(entity);
*编辑*
code解释注释
* EDIT * Code to explain comment
实体只不过是你从Excel读取该模型的数据自定义实体的例子。
例如，如果您的Excel包含有关客户的数据，那么你可以创建
客户实体。
Entity is just an example of custom entity which models data you read from excel. If for example your excel contains data about Customer then you can create customer entity.
public class Customer {
private String name;
private String surname;
private Integer age;
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
// + getters and setters for other fields
}
然后你这个实体充满了Excel中的数据发送到你的DAO（其中管理客户表），并插入给定实体到数据库。
Then you send this entity filled with excel data to your DAO (which manages customer table), and insert given entity into DB.
* EDIT2 *
删除dao.saveOrUpdateCompany（公司）;从以下code：
Remove "dao.saveOrUpdateCompany(company);" from following code:
public String execute()
{
try
{
String filePath=servletRequest.getSession().getServletContext().getRealPath("/");
File fileToCreate= new File(filePath,this.excelDataFileName);
FileUtils.copyFile(this.excelData, fileToCreate);
UploadExcel obj=new UploadExcel();
obj.readExcel(excelData.getAbsolutePath());
}
catch(Exception e){
e.printStackTrace();
addActionError(e.getMessage());
return INPUT;
}
return SUCCESS;
}
您想每行一个新的实体，现在你只在上课开始创建一个实体
You want one new entity PER ROW, now you just create one entity in the beginning of class
阅读评论以下code
Read comments following code
public void readExcel(String filePath)
{
try
{
List sheetData = new ArrayList();
FileInputStream file=new FileInputStream(new File(filePath));
//Getting the instance for XLS file
HSSFWorkbook workbook=new HSSFWorkbook(file);
//Get First sheet from the workbook
HSSFSheet sheet=workbook.getSheetAt(0);
//Iterate start from the first sheet of the uploaded excel file
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
// CHANGE
Company company = new Company();
company.setName(getCellValue(row.getCell((short)1)));
// HERE YOU CAN SAVE COMPANY
dao.saveOrUpdateCompany(company);
// OR ADD COMPANY TO LIST
// List<Company> companies = new ArrayList<Company>();
// Declare just one list for entire class not per row
// In this case you call custom made DAO method which batch save
// all company entities in list but you call this custom method
// at the end of excel processing (your original dao code position).
// Try it without list first
list.add(company);
}
System.out.println("Seet data size-"+sheetData.size());
file.close();
FileOutputStream out=
new FileOutputStream(new File(filePath));
workbook.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
* EDIT3 *
我认为这应该工作，终于
I think this should finally work
try{
session=HibernateUtil.getSessionFactory().openSession();
transaction=session.beginTransaction();
for(int i=0;i<companies.size();i++)
{
// THIS IS BAD
//Company com=new Company();
//You need this
Company com = companies.get(i);
session.saveOrUpdate(com);
}
transaction.commit();
status++;
}
这篇关于Java的读取Excel文件后，如何将一个数据库表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
