首页 > 代码库 > SpringMVC导入Excule并解析Excule中的数据以及下载Excule模板

SpringMVC导入Excule并解析Excule中的数据以及下载Excule模板

把Excule导入,并把Excule中的数据解析出来,包装成对象的属性,保存在数据库中;

Excule中的数据:

技术分享

1.web.xml的配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns="http://java.sun.com/xml/ns/javaee"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    id="WebApp_ID" version="3.0">
    <servlet>
        <servlet-name>Dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath:ApplicationContext.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>Dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
    
    <!-- 文件上传 -->
    <!--
     1.在web.xml中添加listener 
     2.在web.xml中添加spring框架启动的加载的配置文件路径:
     -->
    <listener>  
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>  
    </listener>
    <context-param>  
        <param-name>contextConfigLocation</param-name>  
        <param-value>classpath:ApplicationContext.xml</param-value>  
    </context-param>
</web-app>

2.ApplicationContext.xml中的配置

<!-- 支持上传文件 -->  
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> </bean>  

3.文件上传的JSP以及JS判断上传的文件是否为指定格式

①:form表单

<form enctype="multipart/form-data" action="${paths}/upload.do" method="post" onchange="selectFile(this)">
                    <p style="font-size:16px;">请选择正确的excel文件上传</p>
                    <input id="txt" class="input" type="text" disabled="disabled" value="文件域" name="txt">
                    <input type="button" onclick="file.click()" value="上传文件" onm ousemove="file.style.pixelLeft=event.x-60;file.style.pixelTop=this.offsetTop;">
                    <input id="file1" class="files" type="file" hidefocus="" size="1" style="height:26px;" name="file" onchange="txt.value=http://www.mamicode.com/this.value">
                    <p style="color:red;">支持的excel格式为:xls、xlsx、xlsb、xlsm、xlst!</p>
                       <input  class="btn btn_ok" type="submit" value="确认"> 
                </form>

②:JS代码:

function selectFile(fnUpload){
        var filename=document.getElementById("txt").value;
        var mime = filename.toLowerCase().substr(filename.lastIndexOf(".")); 
        if(!(mime==".xls"||mime==".xlsx"||mime==".xlsb"||mime==".xlsm"||mime==".xlst")){
            alert("请选择正确的格式上传"); 
        } 
    }

4.ExculeUtil类

package com.bgiseq.Experiment_Center.controller;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExculeUtils {
    /**
     * 下载Excule模板
     * 
     * @param output
     * @return
     * @throws IOException
     * @throws WriteException
     */
    public static WritableWorkbook createTemplate(OutputStream output) throws IOException, WriteException {
        WritableWorkbook writableWorkbook = Workbook.createWorkbook(output);
        WritableSheet wsheet = writableWorkbook.createSheet("测试title", 0);

        CellFormat cf = writableWorkbook.getSheet(0).getCell(1, 0).getCellFormat();
        WritableCellFormat wc = new WritableCellFormat();
        // 设置居中
        wc.setAlignment(Alignment.CENTRE);
        // 设置边框线
        // wc.setBorder(Border.ALL, BorderLineStyle.THIN);
        wc.setBackground(jxl.format.Colour.GREEN);// 设置背景颜色
        // Label(x,y,z)其中x代表单元格的第x+1列,第y+1行,
        Label nc1 = new Label(0, 0, "药品编号", wc);
        Label nc0 = new Label(1, 0, "药品名称", wc);
        // 单元格的内容是z
        Label nc2 = new Label(2, 0, "药品厂家", wc);
        Label nc3 = new Label(3, 0, "备注", wc);
        Label nc4 = new Label(4, 0, "创建日期", wc);
        Label nc5 = new Label(5, 0, "修改日期", wc);
        Label nc6 = new Label(6, 0, "删除标志", wc);
        wsheet.addCell(nc0);
        wsheet.addCell(nc1);
        wsheet.addCell(nc2);
        wsheet.addCell(nc3);
        wsheet.addCell(nc4);
        wsheet.addCell(nc5);
        wsheet.addCell(nc6);
        return writableWorkbook;
    }

    /**
     * 解析Excule,并将解析出来的数据保存在list中
   * JXL解析(要导入JXL包) * *
@param file 传入一个Excule文件 * @return */ public static List<String[]> readEcxule(File file) { Sheet sheet; Workbook book; Cell cell1 = null; try { // file为要读取的excel文件名 book = Workbook.getWorkbook(file); // 获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....) sheet = book.getSheet(0); int columns = sheet.getColumns();//获取一共有多少列 int rows = sheet.getRows();//获取一共有多少行 List<String[]> list = new ArrayList<>(); // 获取左上角的单元格 // cell1 = sheet.getCell(0, 0); // System.out.println("标题:"+cell1.getContents()); for (int i = 0; i < rows; i++) { String[] vals = new String[columns]; for (int j = 0; j < columns; j++) { // 获取每一行的单元格 cell1 = sheet.getCell(j, i);// (列,行) String contents = cell1.getContents(); //将每一行的数据添加的String[]中 vals[j] = contents; } list.add(vals); } book.close(); return list; } catch (Exception e) { e.printStackTrace(); } return null;

5.T_KitInfo对象类

public class T_KitInfo {

    private String ID;//试剂盒编号
    private String KitNO;//
    private String KitName;//试剂盒名称
    private String Manufactor;//制造厂
    private String Note;//票据
    private Date CreateTime;//当前时间
    private Date UpdateTime;//修改时间
    private Character flag;//标记 
        //getter和setter方法
}    

6.Controller类

      @RequestMapping(value = "http://www.mamicode.com/upload.do")  
      public String upload(@RequestParam(value = "http://www.mamicode.com/file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model) {  
          String path = request.getSession().getServletContext().getRealPath("/WebContent/upload");  
          String fileName = file.getOriginalFilename();       
          try { 
          File targetFile=null;
          if(fileName!=null||fileName!=""){
              //用\\.将文件名进行解析
              String[] split = fileName.split("\\.");
              String lastname = split[1];
              //判断文件的后缀名是不是指定的格式
              if("xls".equals(lastname)||"xlsx".equals(lastname)||"xlsb".equals(lastname)||"xlsm".equals(lastname)||"xlst".equals(lastname)){
                  targetFile = new File(path, fileName);  
                  if(!targetFile.exists()){  
                      targetFile.mkdirs();  
                  }
                  //保存文件
                  file.transferTo(targetFile);
                  //调用ExculeUtils类中的解析Excule的方法
                  List<String[]> list = ExculeUtils.readEcxule(targetFile);
                  //遍历得到的list
                  for (int i = 1; i < list.size(); i++) {
                    //因为第一行数据为表头信息,所以不用获取到,i从1开始
                      String[] strings = list.get(i);
                      //将获取到的信息设置为T_KitInfo的属性
                      T_KitInfo t=new T_KitInfo();
                    for (int j = 0; j < strings.length; j++) {                        
                        String KitNO = strings[0];//药品编号
                        String KitName = strings[1];//药品名称
                        String Manufactor = strings[2];//药品厂家    
                        String Note = strings[3];//备注
                        String createDate1 = strings[4];//创建日期
                        //T_KitInfo中的时间对象为Date对象,利用Date工具类对String进行解析,得到date对象
                        Date createDate = DateUtil.getDateFromString(createDate1);
                        String updateDate1 = strings[5];//修改日期
                        Date updateDate = DateUtil.getDateFromString(updateDate1);
                        t.setKitNO(KitNO);
                        t.setKitName(KitName);
                        t.setManufactor(Manufactor);
                        t.setNote(Note);
                        t.setCreateTime(createDate);
                        t.setCreateTime(updateDate);
                    }
                    //调用service层的保存对象的方法
                    service.save(t);
                }

-------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------

文件下载:

1.jsp信息:

 

 <a class="import" href="${paths}/sjdcExcel">模板下载</a>

 

2.controller类:

 

 @RequestMapping("sjdcExcel")  
        public void download(HttpServletRequest request, HttpServletResponse response) throws IOException, BiffException, WriteException {
            String filename="试剂盒信息";
              response.setHeader("Content-Disposition", "attachment; filename=SJHMessage.xls");
            WritableWorkbook writableWorkbook = ExculeUtils.createTemplate(response.getOutputStream());
            writableWorkbook.write();
            writableWorkbook.close();
        }

 

SpringMVC导入Excule并解析Excule中的数据以及下载Excule模板