使用 Apache POI 和 Spring 框架将 Excel 文件上传到数据库 [英] 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.
我的要求是从电子表格中读取每一行，并使用单元格中的值生成一个 sql 语句，并在读取电子表格完成后进行批量上传.
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.
我应该如何从 excel 生成 sqls?
How should i generate the sqls from excel?
- 有带有 args (?) 和格式的 sql 语句吗?
或
- 通过连接单元格内容来准备 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>
提交时触发的控制器
@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 中进行 spring 上传的 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>
文件bean
public class FileBean {
private CommonsMultipartFile fileData;
public CommonsMultipartFile getFileData()
{
return fileData;
}
public void setFileData(CommonsMultipartFile fileData)
{
this.fileData = fileData;
}
}
这篇关于使用 Apache POI 和 Spring 框架将 Excel 文件上传到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
