TLv8 数据导入

grid工具栏添加导入按钮：

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

HTML写按钮：

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

JS控制

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文件上传

实例

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内容：

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

完整的代码实例：

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;
	}
}