Java实现前端上传Excel批量导入数据库
excel导出的相关代码请移步:
请戳这里!!!!
————————————————————————————
明确需求,前端JSP页面上传Excel文件,后台解析数据后批量插入数据库:
- 前端页面导入excel文件;
- 尽量保持excel文件与数据库字段顺序一致;
- 后台拿到Excel文件之后解析数据;
- 将解析出来的数据插入到数据库中;
maven导一下包
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
前端页面
前端相关代码:
<form id="table_img_file" enctype="multipart/form-data">
<label>数据表(csv)</label>
<input type="file" name="table" class="form-control">
</form>
<button type="button" class="btn btn-primary" onclick="upload();">添加</button>
function upload(){
var data = new FormData($("#table_img_file")[0]);
var url = "add";
$.ajax({
url : url,
data : data,
type : "post",
dataType : "json",
processData : false,
contentType : false,
success : function(){
location.reload();
},
error : function(){
alert("系统异常,请稍后重试");
}
})
}
后端controller的代码:
@RequestMapping("/add")
@ResponseBody
@DataSource("dataSourceDzzw")
public void add(MultipartFile table) throws Exception {
Workbook workbook = WorkbookFactory.create(table.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
PlatformAccess pfa = new PlatformAccess();
int rowNumber = sheet.getPhysicalNumberOfRows();
for(int i=0;i<rowNumber;i++){
int cellNumber = sheet.getRow(i).getPhysicalNumberOfCells();
Row row = sheet.getRow(i);
StringBuffer data = new StringBuffer();
for(int j = 0;j<cellNumber;j++) {
Object cellValue = "";
if (row.getCell(j) != null) {
//必须使用switch来获取单元格数据,否则会报错,除非确保execl所有单元格的数据类型一致
switch (row.getCell(j).getCellType()) {
case Cell.CELL_TYPE_BOOLEAN://获取布尔型数据
cellValue = row.getCell(j).getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK://获取空值
cellValue = row.getCell(j).getBooleanCellValue();
break;
case Cell.CELL_TYPE_ERROR://获取错误单元格
cellValue = row.getCell(j).getErrorCellValue();
break;
case Cell.CELL_TYPE_NUMERIC://获取数字类型的数据
cellValue = row.getCell(j).getNumericCellValue();
break;
case Cell.CELL_TYPE_STRING://获取字符串
cellValue = row.getCell(j).getStringCellValue();
break;
}
}else {
cellValue= "";
}
//将excel数据拼接成字符串
data.append(cellValue.toString() + " ");
}
//拆分字符串分别赋值
String words[] = data.toString().split(" ");
pfa.setSysId(words[0]);
pfa.setPlatformId(words[1]);
pfa.setPlatformSecretKey(words[2]);
pfa.setPlatformDesc(words[3]);
//加入数据库
service.addPlatformInfo(pfa);
}
}
大佬们有什么建议可以交流交流!