Java 导入Excel (直接插入)
//配置
<!--pom.xml 需要导入poi包-->
<!--excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
还需要添加对应的service和service的实现类
Mapping.xml 添加
<insert id="insert">
insert into ${tableName} (${columns} ) values ( ${values})
</insert>
Mapping.java 添加
int insert(@Param("tableName") String tableName,@Param("values") String values,@Param("columns") String columns);
html内容
<!DOCTYPE html>
<html>
<head>
<script src="vendor/jquery/jquery-2.1.4.min.js"></script>
<script src="vendor/ajaxfileupload.js"></script>
<meta charset="UTF-8">
<title></title>
<base href="http://127.0.0.1/"><!-- 请求路径,服务器地址 -->
</head>
<body>
<input type="hidden" value="import/excel" id="action"><!-- value值为表名 controller地址 -->
<input type="file" id="fileExcel" name="file" accept="*.xls">
<button type="button" onclick="excelImport()">上传</button>
</body>
<script>
function excelImport() {
if ($("#fileExcel").val() == "" || $("#fileExcel") == undefined) {
alert("文件为空");
return;
}
var action = $("#action").val();//当前页面请求的controller
var container = $("#fileForm");
var data = {};
var randomNumberName = new Date().getTime() + Math.ceil(Math.random() * 1000) + \'.xls\';
data["name"] = randomNumberName;
data["category"] = action;
var url = $("base").attr("href") + "/file/plupload/excel/common?category=" + action;// 上传excel的请求路径
$.ajaxFileUpload({
url : url,
type : \'get\',
data : data,
dataType : \'JSON\',
fileElementId : \'fileExcel\',
cache : false,
processData : false,
contentType : false,
success : function(data, status) //服务器成功响应处理函数
{
fileImport(randomNumberName, action);
},
error : function(data, status, e) //服务器响应失败处理函数
{
plus.nativeUI.closeWaiting();
$("#file").bind("change", function() {
});
}
});
}
function fileImport(name, table) {
var data = {};
data["name"] = name;
data["table"] = table;
$.ajax({
data: data,
type: "post",
dataType: \'json\',
url: $("base").attr("href") + "import/excel",
success: function(data) {
if(data.responseText=="success"){
alert("导入成功");
}else{
alert("导入失败")
}
},
error: function(data) {
if(data.responseText=="success"){
alert("导入成功");
}else{
alert("导入失败")
}
}
});
}
</script>
</html>
Excel格式
第一行 (横) 为中文名如:用户名等等
第二行 (横) 为数据库表的列名如:name等等
第三行及之后 (横) 为需要导入的数据
特殊处理列 (纵) 如日期,需要设置列的单元格格式
Java controller
@ResponseBody
@RequestMapping(value = "/excel")
public String commonFileExcel(@RequestParam String name, @RequestParam String table, HttpServletRequest request) {
String path = RunTimeConfig.getRealPath(PhotoType.getPath(PhotoType.EXCEL)) + "/" + name;
try {
Workbook wb = null;
try {
wb = new HSSFWorkbook(new FileInputStream(new File(path)));// 2003excel
} catch (Exception e) {
wb = new XSSFWorkbook(new FileInputStream(new File(path)));// 2007excel
}
Sheet sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
int cellNum;
Row row;
Cell cell;
String columns = "";
for (int i = 1; i <= rowNum; i++) {
String value = "";
row = sheet.getRow(i);
cellNum = row.getLastCellNum();// 列
for (int j = 0; j < cellNum; j++) {// 对一行的每个列进行解析
cell = row.getCell(j);
if (i == 1) {
if (columns.equals("")) {// 全部都以字符串形式取出
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
columns = table + "." + cell.getStringCellValue();// 保险起见以表名.列名
}
} else {
if (cell != null) {// 全部都以字符串形式取出
cell.setCellType(Cell.CELL_TYPE_STRING);
columns = columns + "," + table + "." + cell.getStringCellValue();// 保险起见以表名.列名
}
}
} else {
if (value.equals("")) {// 全部都以字符串形式取出
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
value = "\'" + cell.getStringCellValue() + "\'";
}
} else {
if (cell != null) {// 全部都以字符串形式取出
cell.setCellType(Cell.CELL_TYPE_STRING);
value = value + "," + "\'" + cell.getStringCellValue() + "\'";
}
}
}
}
if (i != 1) {
baseValuesServiceI.insert(table, value, columns);
System.out.println("insert into " + table + "(" + columns + ") values (" + value + ")");
}
}
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
log.error(e);
return "fail";
} catch (IOException e) {
e.printStackTrace();
log.error(e);
return "fail";
}
return "success";
}
以上是 Java 导入Excel (直接插入) 的全部内容， 来源链接： utcz.com/z/391699.html
