SSH将Excel中的数据读入保存到数据库中
最近做一个练习,就是将excel中的数据读入并保存在数据库中,现在对具体的实现做一个简单的记录。
1、jsp页面通过ajax实现异步文件上传
需要引入ajax对文件上传的支持
<script type="text/javascript" language="javascript" src="js/ajaxfileupload.js">
</script>
<script type="text/javascript" src="js/jquery-1.7.1.js">
</script>
相关文件可到网上下载
相关的代码如下:
<!--html代码-->
<font size=2>文件上传:</font>
<input id="fileToUpload" type="file" name="file">
<input id="loading" type="button" name="批量上传" size="10" value="上传文件">
<!--js代码-->
$(document).ready(function() {
$("#loading").click(function() {
$.ajaxFileUpload({
url : "uploadExcelFile",
secureuri : false,
fileElementId : "fileToUpload",
dataType : "json",
success : function(json) {
alert("cg");
$("#uploadFile").hide();
},
error : function() {
alert("上传失败,请重试");
}
});
});
});
2、后台通过一个Action接收文件,将文件保存在服务器的一个文件夹中
<!--action配置-->
<action name="uploadExcelFile" class="uploadExcelAction"
method="uploadExcel">
<interceptor-ref name="json" />
<interceptor-ref name="fileUpload" />
<interceptor-ref name="defaultStack" />
<result name="success" type="json"></result>
</action>
<!--spring配置-->
<bean id="saveExcelInfoDao" class="com.petstore.dao.impl.SaveExcelInfoDaoImpl">
<property name="hibernateTemplate" ref="hibernateTemplate" />
</bean>
<bean id="readExcelInfoBiz" class="com.petstore.biz.impl.ReadExcelInfoBizImpl">
<property name="saveExcelInfoDao" ref="saveExcelInfoDao"/>
</bean>
<bean id="uploadExcelAction" class="com.petstore.action.UploadExcelAction">
<property name="readExcelInfoBiz" ref="readExcelInfoBiz"/>
</bean>
//UploadExcelAction.java
private ReadExcelInfoBiz readExcelInfoBiz;
// 文件名
private String fileName;
private File file;
private String fileFileName;
private String fileContentType;
public UploadExcelAction() {
}
public String uploadExcel() {
// HttpServletRequest request = ServletActionContext.getRequest();
String root = ServletActionContext.getRequest().getSession()
.getServletContext().getRealPath("temp");
FileInputStream fis;
FileOutputStream fos;
String savefileName = fileFileName;
// 得到文件保存的位置(根据root来得到文件保存的路径在tomcat下的该工程里)
File destFile = new File(root, savefileName);
// is中保存了文件的内容
try {
fis = new FileInputStream(getFile());
fos = new FileOutputStream(destFile);
// 把文件写入到上面设置的路径里
byte[] buffer = new byte[512];
int length = 0;
while ((length = fis.read(buffer)) != -1) {
fos.write(buffer, 0, length);
}
fis.close();
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
String path = root + "\\" + fileFileName;
readExcelInfoBiz = new ReadExcelInfoBizImpl();
readExcelInfoBiz.readExcelInfo(path);
return "success";
}
//set/get方法
3、service层读取该文件,解析excel文件获取数据,将数据组装成对象集合
//ReadExcelInfoBizImpl.java
private SaveExcelInfoDao saveExcelInfoDao;
public ReadExcelInfoBizImpl() {
}
public void readExcelInfo(String path) {
List<Exceldata> listLogs = new ArrayList<Exceldata>();
// 获取Excel文档的路径
System.out.println(path);
try {
// 创建对Excel工作簿文件的引用
HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(
new File(path)));
// 在Excel文档中,第一张工作表的缺省索引是0
// 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);?
HSSFSheet sheet = wookbook.getSheetAt(0);
// 获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
// 遍历行
for (int i = 0; i < rows; i++) {
// 读取左上端单元格?
HSSFRow row = sheet.getRow(i);
String[] value = new String[6];
// 行不为空
if (row != null) {
// 获取到Excel文件中的所有的列?
int cells = row.getPhysicalNumberOfCells();
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
HSSFCell cell = row.getCell((short) j);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value[j] = cell.getNumericCellValue() + "";
break;
case HSSFCell.CELL_TYPE_STRING:
value[j] = cell.getRichStringCellValue()
.getString();
break;// getStringCellValue()
case HSSFCell.CELL_TYPE_BLANK:
value[j] = " ";
break;
default:
value[j] = " ";
break;
}
}
String id = value[0];
String username = value[1];
String ip = value[2];
String url = value[3];
String isSuccess = value[4];
String date = value[5];
Exceldata exceldata = new Exceldata(id, username, ip, url,
isSuccess, date);
listLogs.add(exceldata);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("listLogs = " + listLogs.size());
saveExcelInfoDao = new SaveExcelInfoDaoImpl();
saveExcelInfoDao.saveExcelInfo(listLogs);
}
4、dao层将service层传入的对相机和保存到数据库中
//SaveExcelInfoDaoImpl.java
public void saveExcelInfo(List<Exceldata> listLogs) {
Session session = hibernateTemplate.getSessionFactory().openSession();
for (Exceldata exceldata : listLogs) {
session.save(exceldata);
}
}
//数据库
private String id;
username varchar
ip varchar
url varchar
is_success varchar
date varchar