jquery+springMvc+Freemaker实现Excel文件的解析

准备工作：

解析excel，我们需要借助别人写好的工具，那么需要导入相应的jar包。所以需要添加以下的maven依赖。

		    <dependency>
			   <groupId>org.apache.poi</groupId>
			   <artifactId>poi</artifactId>
			   <version>3.9</version>
		   </dependency>
		   <dependency>
			   <groupId>org.apache.poi</groupId>
			   <artifactId>poi-ooxml</artifactId>
			   <version>3.9</version>
		 </dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.4</version>
		</dependency>

Freemaker页面编写：

添加一个“批量导入版本”按钮

 <a href="javascript:Edition.batch('channel','/bro/admin/channel/list.do')">
     <button type="button" class="btn btn-sm">批量导入版本</button>
 </a>

添加“Excel”文件：

<!-- 新建编辑 -->
<form action="/bro/admin/edition/upload.do" method="post" enctype="multipart/form-data" class="form-horizontal">
	<div class="modal-header">
		<button type="button" class="close" data-dismiss="modal">X</button>
		<h3><#if file?? >编辑<#else>新建</#if></h3>
	</div>
	<div class="modal-body">
		<table>
			<input type="hidden" name="id" value="${(file.id)!'0'}" />
			<div class="control-group">
				<label class="control-label" for="file">excel文件</label>
				<div class="controls">
					<input id="file" type="file" name="uploadfile" class="form-control" value="" multiple required/> (可上传多个文件)
				</div>
			</div>
			<input type="hidden" name="status" value="1" />
		</table>
	</div>
	<div class="modal-footer">
		<a href="#" class="btn" data-dismiss="modal">取消</a> <input type="submit" value="确定" />
	</div>
</form>

Jquery页面编写：

Edition.js:点击“批量版本导入”按钮时触发的事件：

/** 批量导入*/
	batch : function(bound,  url) {
		this.showTypeWindow("/bro/admin/edition/batch.do?bound="+bound + "&url=" + url);
	},
	

	showTypeWindow : function(url) {
		htmlobj = $.ajax({
			url : url,
			async : false
		});
		$("#typeWindow").empty();
		$("#typeWindow").html(htmlobj.responseText);
		$("#typeWindow").modal('show');
	}

new_edit.ftl:添加“Excel"类型文件：

<!-- 新建编辑 -->
<form action="/bro/admin/edition/upload.do" method="post" enctype="multipart/form-data" class="form-horizontal">
	<div class="modal-header">
		<button type="button" class="close" data-dismiss="modal">X</button>
		<h3><#if file?? >编辑<#else>新建</#if></h3>
	</div>
	<div class="modal-body">
		<table>
			<input type="hidden" name="id" value="${(file.id)!'0'}" />
			<div class="control-group">
				<label class="control-label" for="file">excel文件</label>
				<div class="controls">
					<input id="file" type="file" name="uploadfile" class="form-control" value="" multiple required/> (可上传多个文件)
				</div>
			</div>
			<input type="hidden" name="status" value="1" />
		</table>
	</div>
	<div class="modal-footer">
		<a href="#" class="btn" data-dismiss="modal">取消</a> <input type="submit" value="确定" />
	</div>
</form>

Spring代码编写：

/**
     * 批量导入版本
     * 
     * @param id
     * @return
     */
    @RequestMapping("batch.do")
    public ModelAndView batch(String bound, String url) {
        ModelAndView mv = new ModelAndView("/bro/admin/edition/new_edit");
        mv.addObject("bound", bound);
        mv.addObject("url", url);
        return mv;
    }

//相关代码还木有完全写完，但是写到目前为止，是可以从excel中解析出数据了。
@RequestMapping(value = "upload.do", headers = ("content-type=multipart/*"))
    public String save( @RequestParam(value = "uploadfile", required = false) MultipartFile[] files) throws Exception {
        
        for (MultipartFile file : files) {

            Assert.notNull(file, "图片不能为空");
            StringBuffer resultMsgSB = new StringBuffer();
            System.out.println("通过 jquery.form.js 提供的ajax方式上传文件！");
     
            InputStream in = null;
            List<List<Object>> listob = null;
            if (file.isEmpty()) {
                throw new Exception("文件不存在！");
            }
     
            in = file.getInputStream();
            listob = new ImportExcelUtil().getBankListByExcel(in, file.getOriginalFilename());
            logger.info(JsonUtil.object2Json(listob));
            
            //List<Map<String, Object>> listUserMap = new ArrayList<>();

            
            
        }

        return "";
    }

import java.io.IOException;  
import java.io.InputStream;  
import java.text.DecimalFormat;  
import java.text.SimpleDateFormat;  
import java.util.ArrayList;  
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  
//解析excel的工具类

public class ImportExcelUtil {

      private final static String excel2003L =".xls";    //2003- 版本的excel  
        private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
          
        /** 
         * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
         * @param in,fileName 
         * @return 
         * @throws IOException  
         */  
        public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
            List<List<Object>> list = null;  
              
            //创建Excel工作薄  
            Workbook work = this.getWorkbook(in,fileName);  
            if(null == work){  
                throw new Exception("创建Excel工作薄为空！");  
            }  
            Sheet sheet = null;  
            Row row = null;  
            Cell cell = null;  
              
            list = new ArrayList<List<Object>>();  
            //遍历Excel中所有的sheet  
            for (int i = 0; i < work.getNumberOfSheets(); i++) {  
                sheet = work.getSheetAt(i);  
                if(sheet==null){continue;}  
                  
                //遍历当前sheet中的所有行  
                for (int j = sheet.getFirstRowNum()+1; j < sheet.getLastRowNum()+1; j++) {  
                    row = sheet.getRow(j);  
                    if(row==null){continue;}  
                  //  if(row==null||row.getFirstCellNum()==j){continue;}  
                      
                    //遍历所有的列  
                    List<Object> li = new ArrayList<Object>();  
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                        cell = row.getCell(y);  
                        li.add(this.getCellValue(cell));  
                    }  
                    list.add(li);  
                }  
            }  
            //work.close();  
            return list;  
        }  
          
        /** 
         * 描述：根据文件后缀，自适应上传文件的版本  
         * @param inStr,fileName 
         * @return 
         * @throws Exception 
         */  
        public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
            Workbook wb = null;  
            String fileType = fileName.substring(fileName.lastIndexOf("."));  
            if(excel2003L.equals(fileType)){  
                wb = new HSSFWorkbook(inStr);  //2003-  
            }else if(excel2007U.equals(fileType)){  
                wb = new XSSFWorkbook(inStr);  //2007+  
            }else{  
                throw new Exception("解析的文件格式有误！");  
            }  
            return wb;  
        }  
      
        /** 
         * 描述：对表格中数值进行格式化 
         * @param cell 
         * @return 
         */  
        public  Object getCellValue(Cell cell){  
            Object value = null;  
            DecimalFormat df = new DecimalFormat("0");  //格式化number String字符  
            SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化  
            DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字  
              
            switch (cell.getCellType()) {  
            case Cell.CELL_TYPE_STRING:  
                value = cell.getRichStringCellValue().getString();  
                break;  
            case Cell.CELL_TYPE_NUMERIC:  
                if("General".equals(cell.getCellStyle().getDataFormatString())){  
                    value = df.format(cell.getNumericCellValue());  
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){  
                    value = sdf.format(cell.getDateCellValue());  
                }else{  
                    value = df2.format(cell.getNumericCellValue());  
                }  
                break;  
            case Cell.CELL_TYPE_BOOLEAN:  
                value = cell.getBooleanCellValue();  
                break;  
            case Cell.CELL_TYPE_BLANK:  
                value = "";  
                break;  
            default:  
                break;  
            }  
            return value;  
        }  
          
}

参考文章：

Upload an excel