数据库中的表:
和javaBean中对应
javaBean:
- public class mainpart {
- private int id;
- private String name;//名称
- private String spid;
- private String specification;//规格
- private String suppid;
- private String suppname;//供应商名称
- private String authenticatedMarks;//认证标志
- private String standard;//标准
- private String parameter;//参数
- private Integer modelId;
-
-
- public Integer getModelId() {
- return modelId;
- }
- public void setModelId(Integer modelId) {
- this.modelId = modelId;
- }
- public String getAuthenticatedMarks() {
- return authenticatedMarks;
- }
- public void setAuthenticatedMarks(String authenticatedMarks) {
- this.authenticatedMarks = authenticatedMarks;
- }
- public String getStandard() {
- return standard;
- }
- public void setStandard(String standard) {
- this.standard = standard;
- }
- public String getParameter() {
- return parameter;
- }
- public void setParameter(String parameter) {
- this.parameter = parameter;
- }
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getSpid() {
- return spid;
- }
- public void setSpid(String spid) {
- this.spid = spid;
- }
- public String getSpecification() {
- return specification;
- }
- public void setSpecification(String specification) {
- this.specification = specification;
- }
- public String getSuppid() {
- return suppid;
- }
- public void setSuppid(String suppid) {
- this.suppid = suppid;
- }
- public String getSuppname() {
- return suppname;
- }
- public void setSuppname(String suppname) {
- this.suppname = suppname;
- }
-
- }
上传调用:思想是读取excel中的每一行数据,从第1行起(第0列为表头),每列的数据转换为string类型的,在通过sql语句
依次插入数据库(插入,更新的两个函数就不再贴出了)
- public class MainPartimportBean {
- private static Logger log = Logger.getLogger(SampleBean.class);
- public void insertDB(InputStream fp,String modelId) {
- try {
- HSSFWorkbook workbook = new HSSFWorkbook(fp);// 创建工作薄
- HSSFSheet sheet = workbook.getSheetAt(0);// 得到工作表
- HSSFRow row = null;// 对应excel的行
- HSSFCell cell = null;// 对应excel的列
- String Var="";
- row = sheet.getRow((short)0);
-
-
- int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数
- int modelId2 = Integer.valueOf(modelId);
- String name = "";//名称
- String specification = "";//规格
- String suppname = "";//供应商名称
- String parameter = "";//参数
- String standard = "";//标准
- String authenticatedMarks = "";//认证标志
-
-
- for (short i = 1; i <=totalRow; i++) {
- mainBean mb = new mainBean();
- mainpart mp1 = new mainpart();
- row = sheet.getRow(i);
- cell = row.getCell((short)0);
- if(cell!=null)
- name = cell.getRichStringCellValue().toString();
- mp1.setName(name);
-
- cell = row.getCell((short)1);
- if(cell!=null)
- specification =cell.getRichStringCellValue().toString();
- mp1.setSpecification(specification);
-
- cell = row.getCell((short)2);
- if(cell!=null)
- suppname = cell.getRichStringCellValue().toString();
- mp1.setSuppname(suppname);
-
- cell = row.getCell((short)3);
- if(cell!=null)
- parameter = cell.getRichStringCellValue().toString();
- mp1.setParameter(parameter);
-
- cell = row.getCell((short)4);
- if(cell!=null)
- standard = cell.getRichStringCellValue().toString();
- mp1.setStandard(standard);
-
- cell = row.getCell((short)5);
- if(cell!=null)
- authenticatedMarks = cell.getRichStringCellValue().toString();
- mp1.setAuthenticatedMarks(authenticatedMarks);
- mp1.setModelId(modelId2);
- if(mb.isEmptymainpart(authenticatedMarks))
- mb.updataMainBymainpart(mp1);//防止数据重复
- else
- mb.newMainUpdata(mp1);//插入数据
- }
-
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
-
- }
-
-
-
-
- }
jsp页面:
a.jsp
- <script type="text/javascript">
- function imp(modelId){
- var form = document.getElementById("form1");
- form.setAttribute('method','post');
- if(form.encoding){
- form.setAttribute('encoding','multipart/form-data');
- }else{
- form.setAttribute('enctype','multipart/form-data');
- }
- form.setAttribute('action','<%=path%>/b.jsp?modelId='+modelId);
- form.submit();
- }
-
- </script>
- <body topmargin=0 leftmargin=0 rightmargin=0 bottommargin=0 >
- <form id="form1" name="form1" >
- <br/>
-
-
- <table width="750" border="0" align="center">
-
- <tr height="30">
- <td width="5%">
- <img src="<%=path%>/images/orgadd.gif"/>
- </td>
- <td width="10%">
- <input type="file" id="f1" name="f1" style="position:absolute;filter:alpha(opacity=0);width:30px;" onchange="imp(<%=modelId%>)"/>
- <input type="button" name="checkSAPmain" id="checkSAPmain" value="导入数据" Class="listLink" >
-
- </td>
- <td align="left">
- <font Class="contentLabel">导入数据</font>
-
- </td>
- </tr>
-
-
- </body>
b.jsp:(先上传了文件,在对文件进行处理,避免服务器和客户端不是同一机器时出错)
- <html>
- <head>
-
- <title>My JSP 'dataSave.jsp' starting page</title>
-
- <meta http-equiv="pragma" content="no-cache">
- <meta http-equiv="cache-control" content="no-cache">
- <meta http-equiv="expires" content="0">
- <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
- <meta http-equiv="description" content="This is my page">
- <!--
- <link rel="stylesheet" type="text/css" href="styles.css">
- -->
-
- </head>
- <Script type="text/javascript">
- function onload(){
- var existSub=document.getElementById("existSub").value;
- str=window.encodeURI(existSub);
- str=window.encodeURI(str);
- var path="<%=basePath%>standard/stdDefault.jsp?existSub="+str;
- self.location.href=path;
- self.parent.frames["sdtree"].location.href="<%=basePath%>standard/standTree.jsp";
-
-
- }
- </Script>
-
-
- <%
- String modelId = request.getParameter("modelId");
- MainPartimportBean mp =new MainPartimportBean();
- String existSub="";
- boolean isMultipart = ServletFileUpload.isMultipartContent(request);
- if (isMultipart) {
- DiskFileItemFactory factory = new DiskFileItemFactory();
- factory.setSizeThreshold(1024 * 10);
-
- // 设置存放临时文件的目录,web根目录下的ImagesUploadTemp目录
- // factory.setRepository(new File("f:\\test"));// 临时文件
-
- // 用上面的工厂实例化上传组件,
- ServletFileUpload upload = new ServletFileUpload(factory);
-
- // 设置最大上传大小 10M
- upload.setSizeMax(1024*1024*10);
- String name = "";
- try {
- List items = upload.parseRequest(request); // 得到所有FileItem
- // 上传文件的个数
- Iterator iter = items.iterator();
- // 循环处理所有文件
- while (iter.hasNext()) {
- FileItem item = (FileItem) iter.next();
- // 判断是否是表单元素(<input type="text" />单选,多选等)
- if (!item.isFormField()) {
- // 得到文件的名称
- name = item.getName();
- // 文件长度
- long size = item.getSize();// 过滤大小
- if (name == null || "".equals(name.trim())) {
- // 未选择上传文件
- continue;
- }
- // 以下为文件名处理,将上传的文件保存在项目所在目录下。
- // 获取文件名字符串的长度
- //int end = name.length();
- // 返回在此字符串中最右边出现的指定子字符串的索引。
- int begin = name.lastIndexOf("\\");
- //int start = name.lastIndexOf(".");
- // 输出上传文件类型,此处可以进行类型的过滤
- System.out.println(application.getRealPath("/js")+System.getProperty("file.separator")+name.substring(begin + 1));
- // File file = new File("f:\\"+name.substring(begin + 1, end));
- File file=new File(application.getRealPath("/js")+System.getProperty("file.separator")+name.substring(begin + 1));
- if (file.exists()) {
- file.delete();
- }
- item.write(file);
-
-
- ByteArrayOutputStream byteOS = new ByteArrayOutputStream();
- FileInputStream fis = new FileInputStream(file);
- byte[] by = new byte[512];
- int t = fis.read(by,0,by.length);
- while(t>0){
- byteOS.write(by, 0, 512); //这里别写成t,写够512
- t = fis.read(by,0,by.length);
- }
- byteOS.close();
- InputStream byteIS = new ByteArrayInputStream(byteOS.toByteArray());
- POIImportExcel pe=new POIImportExcel();
- mp.insertDB(byteIS,modelId);//只有这里是调用了函数,其他地方都为固定格式
- byteIS.close();
-
- file.delete();
-
- existSub="<script>alert('导入成功')</script>";
- }
- }
-
- } catch (FileUploadException e) {
- existSub="<script>alert('文件不能大于10M')</script>";
- e.printStackTrace();
- } catch (Exception e) {
- // 处理文件写入时的异常
- e.printStackTrace();
- }
- }
- request.getRequestDispatcher("a.jsp").forward(request, response);
-
- %>
-
- <body onload="javascript:onload();">
- <input type="hidden" id="existSub" value="<%=existSub%>">
- </body>
- </html>
|