基于SSM+Easy UI项目将Excel指定列导入数据库并显示在Datagrid

需求：将某一个Excel表格中指定列导入数据库，并且在导入之前做格式检查

1.格式检查工具类ExcelPoiUtil

  1 package com.glaf.apps.kpityrpartprice.util;
  2 
  3 import java.text.SimpleDateFormat;
  4 import java.util.Date;
  5 
  6 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  7 import org.apache.poi.ss.usermodel.Cell;
  8 import org.apache.poi.ss.usermodel.Row;
  9 
 10 /**
 11  * POI工具类
 12  * 
 13  * @author key 2014-12-28
 14  */
 15 public class ExcelPoiUtil {
 16 
 17     /**
 18      * 检查是否日期单元格
 19      * 
 20      * @param cell
 21      * @return
 22      */
 23     public static boolean checkDate(Cell cell) {
 24         if (HSSFDateUtil.isCellDateFormatted(cell)) {
 25             return true;
 26         }
 27         return false;
 28     }
 29 
 30     /**
 31      * 检查是否日期单元格
 32      * 
 33      * @param cell
 34      * @return
 35      */
 36     public static String checkDate(Row row, int[] cellIndexs) {
 37         String ret = "OK";
 38         Cell cell = null;
 39         if (null != cellIndexs) {
 40             for (int i = 0; i < cellIndexs.length; i++) {
 41                 cell = row.getCell(cellIndexs[i]);
 42                 if (!checkDate(cell)) {
 43                     ret = "导入失败！\n行数：" + row.getRowNum() + "，列数："
 44                             + cell.getColumnIndex() + "，单元格值："
 45                             + ExcelPoiUtil.getCellValue(cell) + "，日期格式有误";
 46                     break;
 47                 }
 48             }
 49         }
 50         return ret;
 51     }
 52 
 53     /**
 54      * 检查是否数字单元格
 55      * 
 56      * @param cell
 57      * @return
 58      */
 59     public static boolean checkNumber(Cell cell) {
 60         if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
 61             return true;
 62         }
 63         return false;
 64     }
 65 
 66     /**
 67      * 检查是否日期单元格
 68      * 
 69      * @param cell
 70      * @return
 71      */
 72     public static String checkNumber(Row row, int[] cellIndexs) {
 73         String ret = "OK";
 74         Cell cell = null;
 75         if (null != cellIndexs) {
 76             for (int i = 0; i < cellIndexs.length; i++) {
 77                 cell = row.getCell(cellIndexs[i]);
 78                 if (!checkNumber(cell)) {
 79                     ret = "导入失败！\n行数：" + row.getRowNum() + "，列数："
 80                             + cell.getColumnIndex() + "，单元格值："
 81                             + ExcelPoiUtil.getCellValue(cell) + "，数字格式有误";
 82                     break;
 83                 }
 84             }
 85         }
 86         return ret;
 87     }
 88 
 89     /**
 90      * 取单元格值，返回字符串
 91      * 
 92      * @param cell
 93      * @return
 94      */
 95     public static String getCellValue(Cell cell) {
 96         if (cell == null) {
 97             return null;
 98         }
 99         String tempStr = "";
100         try {
101             if (cell.getCellType() == Cell.CELL_TYPE_STRING) {// 字符串
102                 tempStr = cell.getRichStringCellValue().getString();
103             } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {// boolean类型
104                 tempStr = cell.getBooleanCellValue() + ",";
105             } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {// 公式
106                 tempStr = cell.getCellFormula() + ",";
107             } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// 日期或数字
108                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
109                     Date date = cell.getDateCellValue();
110                     if (date != null)
111                         tempStr = new SimpleDateFormat("yyyy-MM-dd")
112                                 .format(date);
113                 } else {
114                     tempStr = cell.getNumericCellValue() + "";
115                 }
116             } else {
117                 tempStr = cell.getRichStringCellValue().getString();
118             }
119         } catch (Exception E) {
120             E.printStackTrace();
121         }
122         return tempStr;
123     }
124 
125     /**
126      * 返回日期值
127      * 
128      * @param cell
129      * @return
130      */
131     public static Date getCellValueDate(Cell cell) {
132         Date date = null;
133         if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期
134             date = cell.getDateCellValue();
135         }
136         return date;
137     }
138 
139     /**
140      * 返回数字值
141      * 
142      * @param cell
143      * @return
144      */
145     public static double getCellValueDouble(Cell cell) {
146         double d = 0;
147         if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// 日期
148             d = cell.getNumericCellValue();
149         }
150         return d;
151     }
152 
153     /**
154      * 检测字符串长度
155      * 
156      * @param cell
157      * @return
158      */
159     public static int checkStringLength(String str) {
160         int len = 0;
161         for (int i = 0; i < str.length(); i++) {
162             char c = str.charAt(i);
163             // 单字节加1
164             if ((c >= 0x0001 && c <= 0x007e) || (0xff60 <= c && c <= 0xff9f)) {
165                 len++;
166             } else {
167                 len += 2;
168             }
169         }
170         return len;
171     }
172 }

2.Excel导入jsp页面

 1  function importExcel(){
 2     jQuery('#importDiv').dialog({
 3              title:'试作件价格导入',
 4             modal:true,
 5               width:600,
 6               height:150
 7          });    
 8           jQuery('#importDiv').dialog('open');
 9}
10 ........
11   <div id="tb" style="padding: 5px; height: auto">
12        <a href="#" class="easyui-linkbutton"
13                    data-options="plain:true, iconCls:'icon-imp'"
14                    onclick="javascript:importExcel();">导入</a> 
15   </div>
16 <!-- 导入上传Excel界面 -->
17 <jsp:include page="/WEB-INF/views/apps/kpiTyrpartprice/init_importupload.jsp" flush="true" />

3.上传Excel界面 init_importupload.jsp

  1 <%@ page contentType="text/html;charset=UTF-8" %>
  2 <script type="text/javascript">
  3 function importExcelUpload(){    
  4     var filepath = jQuery("#myFile").val();
  5     if(filepath=="") {
  6         alert("请选择上传的文件名!");
  7         return ;
  8     }
  9     var extname = filepath.substring(filepath.lastIndexOf(".")+1,filepath.length);
 10     extname = extname.toLowerCase();//处理了大小写
 11     if(extname!= "xls" && extname!= "xlsx" ){
 12         alert("只能上传excel格式(xls、xlsx)的文件！");
 13         return;
 14     }
 15     jQuery.messager.confirm('提示', '确认上传吗？', function(r){
 16         if (r){
 17             var link = "<%=request.getContextPath()%>/apps/kpiTyrpartprice.do?method=importExcel";
 18             ajaxFileUploadExcel(link);
 19         }
 20     });
 21 }
 22 
 23 jQuery(function(){
 24     jQuery('#importDiv').dialog({
 25             title:'导入',
 26             modal:true,
 27             width:400,
 28             height:150
 29         });
 30     jQuery('#importDiv').dialog('close');
 31 
 32  jQuery('#uploadModalDialog').dialog({
 33             title:false,
 34             modal:true,
 35             width:200,
 36             height:60
 37         });            
 38     jQuery('#uploadModalDialog').dialog('close');
 39     jQuery('#validateMsgModalDialog').dialog('close');
 40     
 41     });
 42      
 43     function ajaxFileUploadExcel(url){
 44         var params = jQuery("#importForm").formSerialize();
 45         jQuery('#uploadModalDialog').dialog('open');
 46         jQuery.ajaxFileUpload({
 47             url:url,
 48             data : params,
 49             type:"POST",
 50             dataType:'text',
 51             fileElementId:['myFile'],
 52             success:function(data){ 
 53                 jQuery('#uploadModalDialog').dialog('close');
 54                 data = decodeURI(data);
 55                 data = data.replace('<pre style="word-wrap: break-word; white-space: pre-wrap;">','');
 56                 data = data.replace("</pre>","");
 57                 if(data=='-1'){
 58                      jQuery.messager.alert("提示","上传失败!","info");
 59                 }else if(data=='1'){
 60                      jQuery.messager.alert("提示","文件格式错误,文件格式必需为(.xls)!","info");
 61                 }else if(data=='2'){
 62                      jQuery.messager.alert("提示","文件大小不能超过10M!","info");
 63                 }else{
 64                     jQuery('#importDiv').dialog('close');
 65                      jQuery.messager.alert("提示",data.length==0?"上传成功!":data,"info");
 66                     reloadGrid();
 67                      //$("#mySpan").text(data);
 68                      //$('#validateMsgModalDialog').dialog('open');
 69                 }
 70             },
 71             error:function(){
 72                 jQuery('#uploadModalDialog').dialog('close');
 73                  jQuery.messager.alert("错误","系统处理出错!","error");
 74             }
 75         });
 76     }
 77 
 78     function closeDiv(){
 79         jQuery('#validateMsgModalDialog').dialog('close');
 80     }
 81 </script>
 82     <div id="importDiv">
 83         <form id="importForm" name="importForm" enctype="multipart/form-data" method="post"> 
 84         <br/>
 85             <table style="width: 100%">
 86                 <tr>
 87                     <td  align="center">选择文件:<input type="file" id="myFile" name="myFile" style="width:320px;"/></td>
 88                 </tr>
 89                 <tr>
 90                     <td align="center">
 91                         <a href="#" class="easyui-linkbutton" icon="icon-submit" onclick="javascript:importExcelUpload();" id="daoru">导入</a>
 92                         <a href="#" class="easyui-linkbutton" icon="icon-back" onclick="javascript:jQuery('#importDiv').dialog('close');">取消</a>
 93                     </td>
 94                 </tr>
 95             </table>
 96         </form>
 97     </div>
 98     <div id="uploadModalDialog" style="text-align:center;vertical-align:center;">
 99            <img alt="" src="../images/loading.gif" width="20" height="20" style="margin-top:10px">正在导入，请耐心等候...
100      </div>

4.controller层

  1 @RequestMapping(params = "method=importExcel")
  2     public void importExcel(HttpServletRequest request,
  3             HttpServletResponse response, ModelMap modelMap) {
  4         // User user = RequestUtils.getUser(request);
  5         // BaseDataManager bdm = BaseDataManager.getInstance();
  6         String msg = "";
  7 
  8         try {
  9             Workbook wb = null;
 10             InputStream is = FileOperateUtil.getUploadInputStream(request,
 11                     "myFile");
 12 
 13             wb = WorkbookFactory.create(is);
 14             Sheet sheet = wb.getSheetAt(0);
 15 
 16             int rowNum = sheet.getLastRowNum();// 取出总行数
 17             Row row = null;
 18             row = sheet.getRow(0);
 19             // int colNum = row.getLastCellNum();
 20             for (int i = 1; i <= rowNum; i++) {// 遍历
 21                 row = sheet.getRow(i);
 22                 KpiTyrpartprice kpiTyrpartprice = new KpiTyrpartprice();
 23                 // 1.订单号
 24                 String orderNo = ExcelPoiUtil.getCellValue(row.getCell(38))//订单号在excel中列号（下标从0开始）
 25                         .trim();
 26                 kpiTyrpartprice.setOrderNo(orderNo);
 27                 // 2.订货日期检查
 28                 try {
 29                     Date orderDate = ExcelPoiUtil.getCellValueDate(row
 30                             .getCell(39));
 31                     kpiTyrpartprice.setOrderDate(orderDate);
 32                 } catch (Exception e2) {
 33                     e2.printStackTrace();
 34                     msg += "第" + (i + 1) + "行订货日期格式不正确\n";
 35                     continue;
 36                 }
 37                 // 3.品番号检查，部品信息表若有，则将部品信息表中的番号中文、番号英文加入试作件表
　　　　　　          /*插入试作件价格番号字段的时候，首先根据番号（partCode）去部品信息表（Partlist）中检查是否存在该字段，如果有，则顺带将Partlist中的PartId、
                           partNameC（番号（中文））、partNameE（番号（英文））以及Excel中的番号插入试作件价格信息表,供应商信息检查与此相仿*/

 38                 String partCode = ExcelPoiUtil.getCellValue(row.getCell(120))
 39                         .trim();
 40                 KpiPartlistQuery kpiPartlistQuery = new KpiPartlistQuery();
 41                 kpiPartlistQuery.setPartCode(partCode);
 42                 List<KpiPartlist> list_part = kpiPartlistService
 43                         .list(kpiPartlistQuery);
 44                 if (list_part.size() > 0) {
 45                     kpiTyrpartprice.setPartId(list_part.get(0).getPartId());
 46                     kpiTyrpartprice.setPartCode(partCode);
 47                     kpiTyrpartprice.setPartNameC(list_part.get(0)
 48                             .getPartNameC());
 49                     kpiTyrpartprice.setPartNameE(list_part.get(0)
 50                             .getPartNameE());
 51                 } else {
 52                     msg += "第" + (i + 1) + "行番号在部品信息表中不存在\n";
 53                     continue;
 54                 }
 55 
 56                 // 4.供应商检查
 57                 String supplierNo = ExcelPoiUtil.getCellValue(row.getCell(495))
 58                         .trim();
 59                 KpiSupplierinfoQuery kpiSupplierinfoQuery = new KpiSupplierinfoQuery();
 60                 kpiSupplierinfoQuery.setSupplierNo(supplierNo);
 61                 List<KpiSupplierinfo> list_sup = kpiSupplierinfoService
 62                         .list(kpiSupplierinfoQuery);
 63                 if (list_sup.size() > 0) {
 64                     kpiTyrpartprice.setSupplierId(list_sup.get(0)
 65                             .getSupplierId());
 66                     kpiTyrpartprice.setSupplierNo(supplierNo);
 67                     kpiTyrpartprice.setSupplierName(list_sup.get(0)
 68                             .getSupplierName());
 69                 } else {
 70                     msg += "第" + (i + 1) + "行供应商编号在供应商信息表中不存在\n";
 71                     continue;
 72                 }
 73                 // 5.采购担当检查
 74                 String buyBear = ExcelPoiUtil.getCellValue(row.getCell(17))
 75                         .trim();
 76                 kpiTyrpartprice.setBuyBear(buyBear);
 77                 // 6.采购单价检查
 78                 //Double price = ExcelPoiUtil.getCellValueDouble(row.getCell(8));
 79                 //kpiTyrpartprice.setPrice(price);
 80                 try {
 81                     kpiTyrpartprice.setPrice(ExcelPoiUtil
 82                             .getCellValueDouble(row.getCell(433)));
 83                 } catch (Exception e1) {
 84                     msg += "第" + (i + 1) + "行采购价格不能为空或是非数字\n";
 85                     kpiTyrpartprice.setPrice(0.0);
 86                     continue;
 87                 }
 88                 kpiTyrpartpriceService.save(kpiTyrpartprice);
 89             }
 90             if(msg==""){
 91                 msg = "导入成功！";
 92              }
 93         } catch (UnsupportedEncodingException e) {
 94             e.printStackTrace();
 95         } catch (IOException e) {
 96             e.printStackTrace();
 97         } catch (Exception e) {
 98             e.printStackTrace();
 99         } finally {
100             try {
101                 response.getWriter().print(URLEncoder.encode(msg, "UTF-8"));// encode返回值msg
102             } catch (UnsupportedEncodingException e) {
103                 // e.printStackTrace();
104             } catch (IOException e) {
105                 // e.printStackTrace();
106             }
107         }
108     }

posted on 2018-08-27 12:28 一直深夜吃食的喵阅读(349) 评论(0) 收藏举报