上传Excel文件导入数据库使用Apache POI和Spring框架 [英] Upload Excel file into database using Apache POI and Spring framework
问题描述
无法在互联网上找到一个很好的职位，以帮助我。
Couldn't find a good post on the internet to help me with this.
我的要求是，从A S preadsheet阅读每一行，并生成一个与细胞值的sql语句与阅读在S preadsheet做的时候做了批量上传。
My requirement is to read each row from a spreadsheet, and generate a sql statement with values from the cells and do a batch upload when reading the spreadsheet is done.
我使用Apache POI，Spring框架和JDBC。
I am using Apache POI, Spring framework and JDBC.
我应该如何生成sql语句从Excel？
How should i generate the sqls from excel?
- 有ARGS一个SQL语句（？）和格式的单元格内容？
或
- $ P $通过连接单元格的内容ppare一个SQL？
什么是做到这一点？最好的办法
What's the best way to do this??
推荐答案
我会做同样的事情前几个星期，结束了对你的问题的Excel的一部分，以下解决方案。该解决方案同时支持新的和97-2007表格式。我使用的春天和POI。我不认为这是更多钞票来回答你的问题的其余部分没有更多的信息。
I was going to do the same thing some weeks ago, ended up with the following solution for the excel part of your question. This solution support both the new and the 97-2007 sheet format. I am using spring and POI. I dont think it is posible to answer the rest of your question without more information.
在JSP网站，用户上传的文件：
the jsp site where the user upload the file:
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>import</title>
</head>
<body>
<form:form modelAttribute="fileBean" method="post" enctype="multipart/form-data">
<form:label for="fileData" path="fileData">Select file</form:label><br/><br/>
<form:input path="fileData" type="file"/>
<input type="submit" />
</form:form>
</body>
</html>
这将触发的CONTROLER提交
the controler that will be triggered on submit
@Controller
@RequestMapping("/upload")
public class ExcelImporterController {
@RequestMapping(method = RequestMethod.POST)
public String upload(FileBean uploadItem, BindingResult result) {
importService.import(uploadItem);
return "import/importDone";
}
}
接口..
public interface importService {
public void import(FileBean fileBean);
}
接口的实现与导入方法。
implementation of the interface with the import method..
@Override
public void import(FileBean fileBean) {
ByteArrayInputStream bis = new ByteArrayInputStream(filedBean.getFileData().getBytes());
Workbook workbook;
try {
if (fileBean.getFileData().getOriginalFilename().endsWith("xls")) {
workbook = new HSSFWorkbook(bis);
} else if (fileBean.getFileData().getOriginalFilename().endsWith("xlsx")) {
workbook = new XSSFWorkbook(bis);
} else {
throw new IllegalArgumentException("Received file does not have a standard excel extension.");
}
for (Row row : sheet) {
if (row.getRowNum() == 0) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
//go from cell to cell and do create sql based on the content
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
这将在FileBean用于春季上传的bean的配置。
the configuration of the bean that will be used for the spring upload in the FileBean..
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="10000000"/>
</bean>
文件豆
public class FileBean {
private CommonsMultipartFile fileData;
public CommonsMultipartFile getFileData()
{
return fileData;
}
public void setFileData(CommonsMultipartFile fileData)
{
this.fileData = fileData;
}
}
这篇关于上传Excel文件导入数据库使用Apache POI和Spring框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
