TLv8 数据导入

grid工具栏添加导入按钮：

</>code
importbtn = subdgrid3.insertSelfBar("导入清单","60px","");

HTML写按钮：

</>code
<button id="importbtn">导入</button>

JS控制

</>code
layui.upload.render({
elem:'#'+importbtn,
url:cpath+'/pur/importContractItems',
accept: 'file',
dataType: 'json',
data:{contractID:ContraData.rowid},
done: function(res, index, upload){
if(res.code != '-1'){ 
subdgrid3.refreshData();
}else{
alert(res.msg);
}
}
});

引用layui：

<link type="text/css" rel="stylesheet" href="../../../resources/layui/css/layui.css">

<script type="text/javascript" src="../../../resources/layui/layui.js"></script>

layui文件上传

实例

</>code
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
 
@Controller
@RequestMapping("/pur")
public class ImportContractItems {
     @ResponseBody
@RequestMapping(value = "/importContractItems")
public Object execute(@RequestParam("file") MultipartFile file, String contractID) throws Exception {
        JSONObject res = new JSONObject();
        return res;
    }   
}

关键参数：@RequestParam("file") MultipartFile file

利用公共类：com.tlv8.core.utils.ExcelUtils解析excel内容：

</>code
Workbook workbook = ExcelUtils.getWorkbook(file);
String[][] datas = ExcelUtils.readSheet(workbook, 0);//0:excel工作簿的编号第一个从0开始

完整的代码实例：

</>code
package com.tlv8.pur.contract;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
 
import org.apache.ibatis.jdbc.SQL;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.JSONObject;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
 
import com.tlv8.base.db.DBUtils;
import com.tlv8.base.utils.IDUtils;
import com.tlv8.core.utils.ExcelUtils;
 
/**
 * 合同清单导入
 * 
 * @author chenqian
 *
 */
@Controller
@RequestMapping("/pur")
public class ImportContractItems {
 
@ResponseBody
@RequestMapping(value = "/importContractItems")
public Object execute(@RequestParam("file") MultipartFile file, String contractID) throws Exception {
JSONObject res = new JSONObject();
SqlSession session = DBUtils.getSession("oa");
Connection conn = null;
PreparedStatement ps = null;
try {
String fileName = file.getOriginalFilename();
if (fileName == null || "".equals(fileName)) {
res.put("code", -1);
res.put("msg", "导入失败：未选择文件或文件类型不正确！");
} else if (fileName.toLowerCase().indexOf("xls") < 0 && !"".equals(fileName)) {
res.put("code", -1);
res.put("msg", "导入失败：文件类型不正确！正确的导入文件为Excel");
} else {
Workbook workbook = ExcelUtils.getWorkbook(file);
String[][] datas = ExcelUtils.readSheet(workbook, 0);
 
SQL sql = new SQL();
sql.INSERT_INTO("pur_contract_items");
sql.INTO_COLUMNS("fID", "fcontractID", "fbrand", "fnumber", "fprice", "ftotalprice", "fremarks");
sql.INTO_COLUMNS("funit", "fservices", "fgoodsname", "fmodel", "fMaincfp", "VERSION", "fnum",
"fmanufacturer", "fplaceofOrigin");
sql.INTO_VALUES("?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?");
conn = session.getConnection();
for (int i = 1; i < datas.length; i++) {
ps = conn.prepareStatement(sql.toString());
ps.setString(1, IDUtils.getGUID());
ps.setString(2, contractID);
ps.setString(3, datas[i][0]);// 品牌
ps.setString(4, datas[i][1]);// 数量
ps.setString(5, datas[i][3]);// 单价
ps.setDouble(6, Double.valueOf(datas[i][1])*Double.valueOf(datas[i][3]));// 总价
ps.setString(7, datas[i][6]);
ps.setString(8, datas[i][2]);//
ps.setString(9, datas[i][5]);// 服务内容
ps.setString(10, datas[i][7]);// 货物名称
ps.setString(11, datas[i][8]);// 型号
ps.setString(12, datas[i][9]);// 主要配置参数
ps.setInt(13, 0);
ps.setInt(14, i);
ps.setString(15, datas[i][10]);// 制造商
ps.setString(16, datas[i][11]);// 产地
ps.executeUpdate();
}
session.commit(true);
res.put("code", 0);
res.put("msg", "导入成功！");
}
} catch (Exception e) {
session.rollback(true);
res.put("code", -1);
res.put("msg", "导入失败！详细：" + e.getMessage());
e.printStackTrace();
} finally {
DBUtils.CloseConn(session, conn, ps, null);
}
return res;
}
}