sturts2批量导入excel中的信息入库大致代码

View Code

  1 package com.test.bcp.check.view;
  2 
  3 import java.io.File;
  4 import java.util.ArrayList;
  5 import java.util.List;
  6 
  7 import com.kms.framework.core.view.support.Result;
  8 import com.kms.framework.core.view.support.entity.BaseEntityAction;
  9 import com.test.bcp.check.domain.CheckSet;
 10 import com.test.bcp.check.service.ICheckSetService;
 11 import com.test.bcp.config.service.impl.ConfigEhcacheService;
 12 import com.test.bcp.config.util.ConfigUtils;
 13 import com.test.bcp.user.util.UploadUtils;
 14 import com.test.bcp.user.util.UserConfigUtils;
 15 import com.test.bcp.user.vo.ResultObject;
 16 
 17 /**
 18  * @author: lifeng@foxmail.com
 19  * @date: 2012-10-15
 20  * @time: 11:06:48
 21  * @desc:
 22  */
 23 public class CheckSetAction extends
 24         BaseEntityAction<CheckSet, ICheckSetService> {
 25 
 26     private ICheckSetService checkSetService;
 27     
 28     
 29     private String savePath = UserConfigUtils.UPLOAD_USER_URL;// 文件保存路径,通过ioc注入
 30 
 31     private File upload;// 上传的文件
 32     
 33     private ConfigEhcacheService configEhcacheService; 
 34      
 35     private String uploadFileName;
 36     
 37     public void setCheckSetService(ICheckSetService checkSetService) {
 38         this.checkSetService = checkSetService;
 39     }
 40     protected ICheckSetService getEntityManager() {
 41         // TODO Auto-generated method stub
 42         return checkSetService;
 43     }
 44     public ConfigEhcacheService getConfigEhcacheService() {
 45         return configEhcacheService;
 46     }
 47 
 48     public void setConfigEhcacheService(ConfigEhcacheService configEhcacheService) {
 49         this.configEhcacheService = configEhcacheService;
 50     }
 51 
 52     public File getUpload() {
 53         return upload;
 54     }
 55 
 56     public void setUpload(File upload) {
 57         this.upload = upload;
 58     }
 59     
 60 
 61     public String getUploadFileName() {
 62         return uploadFileName;
 63     }
 64 
 65     public void setUploadFileName(String uploadFileName) {
 66         this.uploadFileName = uploadFileName;
 67     }
 68 
 69     public String getCheckWeightConfig() {
 70         List list = new ArrayList();
 71 
 72         List<CheckSet> checkWeightconfig = checkSetService
 73                 .getCheckWeightConfig();
 74         if (checkWeightconfig.size() > 0) {
 75             entity = checkWeightconfig.get(0);
 76         }
 77         entitys.add(entity);
 78         return SUCCESS;
 79     }
 80     
 81     /**
 82      * 通过excel批量上传业务经理考核资料
 83      * @return
 84      */
 85     public String batchUploadCheckSet(){
 86         if (upload != null) {
 87             if(upload.length()>3*Math.pow(2,20)){
 88                 result = new Result(false,getText("user.upload.upload.maxlimit"));
 89                 return INPUT;
 90             }
 91             ResultObject oResult =null;
 92             try{
 93                 oResult = checkSetService.createDatas(upload);
 94             }catch(Exception e){
 95                 e.printStackTrace();
 96                 result = new Result(false, getText("user.upload.file.error"));
 97             }
 98             result = oResult.getResult();
 99             if(result.isSuccess()){
100                 doUploadExcel();    
101             }else{
102                 int code = Integer.valueOf(oResult.getResult().getMessage());
103                 String errorMsg = oResult.getErrorName();
104                 switch (code) {
105                 case 1:
106                     result.setMessage(getText("user.upload.connect.error"));
107                     break;
108                 case 2:
109                     result.setMessage(getText("user.upload.format.error"));
110                     break;
111                 case 3:
112                     result.setMessage(getText("user.upload.format.2003"));
113                     break;
114                 case 4:
115                     result.setMessage(getText("user.upload.format.nulltr"));
116                     break;
117                 case 5:
118                     result.setMessage(getText("user.upload.data.max"));
119                     break;
120                 case 6:
121                     result.setMessage(getText("user.upload.data.mapping")+errorMsg);
122                     break;
123                 case 7:
124                     result.setMessage(getText("user.upload.data.vali")+errorMsg);
125                     break;
126                 case 8:
127                     result.setMessage(getText("user.upload.data.dup")+errorMsg);
128                     break;
129                 case 9:
130                     result.setMessage(getText("user.upload.insert.organise")+errorMsg);
131                     break;
132                 case 12:
133                     result.setMessage(getText("user.upload.insert.temple"));
134                     break;
135                 case 13:
136                     result.setMessage(getText("user.upload.dump.phone")+errorMsg);
137                     break;
138                 case 14:
139                     result.setMessage(getText("user.upload.dump.loginname")+errorMsg);
140                     break;
141                 default:
142                     result.setMessage("导入数据失败,请检查数据的格式是否正确");
143                     break;
144                 }
145             }
146         }
147         else{
148             result = new Result(false, getText("user.upload.file.error"));
149         }
150         return INPUT;
151     }
152     
153     /**
154      * 上传EXCEL文件 
155      * @return 文件地址
156      */
157     private void doUploadExcel(){
158         if (upload != null) {
159             savePath = getUploadFile()+"/"+ savePath;
160             UploadUtils.mkDirectory(savePath);
161             UploadUtils.upload(getUploadFileName(),savePath, getUpload());
162         }
163     }
164     
165     private String getUploadFile(){
166         return (String)configEhcacheService.get(ConfigUtils.CACHE_KEY_UPLOAD_FILE_PATH);
167     }
168 
169 }

--解析excel中数据到入库的核心代码

View Code

  1 package com.test.bcp.check.service.impl;
  2 import java.io.BufferedInputStream;
  3 import java.io.File;
  4 import java.io.FileInputStream;
  5 import java.io.IOException;
  6 import java.io.InputStream;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 11 import org.apache.poi.ss.usermodel.Cell;
 12 import org.apache.poi.ss.usermodel.Row;
 13 import org.apache.poi.ss.usermodel.Sheet;
 14 import org.apache.poi.ss.usermodel.Workbook;
 15 
 16 import com.kms.framework.core.dao.IEntityDao;
 17 import com.kms.framework.core.logger.LoggerFactory;
 18 import com.kms.framework.core.logger.type.DebugLogger;
 19 import com.kms.framework.core.logger.type.UserLogger;
 20 import com.kms.framework.core.service.BaseEntityService;
 21 import com.kms.framework.core.view.support.Result;
 22 import com.test.bcp.check.dao.CheckSetDao;
 23 import com.test.bcp.check.domain.CheckSet;
 24 import com.test.bcp.check.service.ICheckSetService;
 25 import com.test.bcp.user.dao.OrganiseDao;
 26 import com.test.bcp.user.util.PoiExcelUtils;
 27 import com.test.bcp.user.vo.ResultObject;
 28 /**
 29  * @author: lifeng@foxmail.com
 30  * @date: 2012-10-15
 31  * @time: 11:06:48
 32  * @desc:
 33  */
 34 public class CheckSetService  extends BaseEntityService<CheckSet> implements ICheckSetService {
 35      
 36     private CheckSetDao  checkSetDao;
 37     
 38     private OrganiseDao organiseDao;
 39     
 40     private DebugLogger debugLogger = LoggerFactory.getDebugLogger(CheckSetService.class);
 41     
 42     private UserLogger userLogger = LoggerFactory.getUserLogger(CheckSetService.class);
 43     
 44     //上传文件成功
 45     public final static String UPLOAD_SUCCESS ="0";
 46     //上传文件服务器连接错误
 47     public final static String UPLOAD_ERROR_CONNECT = "1";
 48     //上传文件的格式错误
 49     public final static String UPLOAD_ERROR_FORMATE = "2";
 50     //上传文件excel格式必须为office 2003
 51     public final static String UPLOAD_ERROR_2003 = "3";
 52     //上传文件excel文件不能有空行
 53     public final static String UPLOAD_ERROR_NULLTR = "4";
 54     //上传数据超过限制
 55     public final static String UPLOAD_ERROR_MAX = "5";
 56     //将数据装载到用户实体上错误
 57     public final static String MAPPING_ERROR_DATA = "6";
 58     //验证数据错误
 59     public final static String VERFY_ERROR_DATA = "7";
 60     //重复的数据 一些数据具有唯一性
 61     public final static String ONLY_ERROR_DATA = "8";
 62     //批量插入时错误，检查用户的组织机构ID是否存在
 63     public final static String ORGANSIE_ERROR_DATA = "9";
 64     //重复的数据 手机号具有唯一性
 65     public final static String ONLY_PHONEID_DATA ="10";
 66     //重复的数据 用户工号具有唯一性
 67     public final static String ONLY_USERSEQ_DATA = "11";
 68     //重复的数据 用户工号具有唯一性
 69     public final static String ORGANSIE_ERROR_TEMPLE = "12";
 70     //上传的文件中有电话重复
 71     public final static String UPLOAD_ERROR_PHONE = "13";
 72     //上传的文件中有用户名重复
 73     public final static String UPLOAD_ERROR_LOGIN = "14";
 74     
 75 
 76     
 77     public OrganiseDao getOrganiseDao() {
 78         return organiseDao;
 79     }
 80 
 81     public void setOrganiseDao(OrganiseDao organiseDao) {
 82         this.organiseDao = organiseDao;
 83     }
 84 
 85     public void setCheckSetDao(CheckSetDao checkSetDao) {
 86         this.checkSetDao = checkSetDao;
 87     }
 88 
 89     protected IEntityDao<CheckSet> getDao() {
 90         // TODO Auto-generated method stub
 91         return checkSetDao;
 92     }
 93  
 94     public List<CheckSet> getCheckWeightConfig() {
 95         // TODO Auto-generated method stub
 96         return checkSetDao.getAll();
 97     }
 98     
 99     public ResultObject createDatas(File file){
100         ResultObject _ro = new ResultObject();
101         _ro = uploadUsers(file);
102         if(!_ro.getResult().isSuccess()){
103             return _ro;
104         }
105         _ro = mappingData(_ro);
106         if(!_ro.getResult().isSuccess()){
107             return _ro;
108         }
109         
110         return createData(_ro);
111     }
112     
113     /*----------------------1. 读取数据 ---------------------*/
114     private ResultObject uploadUsers(File file){
115         FileInputStream fis = null;
116         try{
117             fis = new FileInputStream(file);
118             return readExcel(new BufferedInputStream(fis),1000);
119         }catch(Exception ex){
120             return new ResultObject(false ,UPLOAD_ERROR_CONNECT , null ,null);
121         }
122     }
123     
124     private ResultObject readExcel(InputStream file,int maxNum){
125         ArrayList<String[]> hlist = new ArrayList<String[]>();
126         try { 
127         Workbook workBook = null;
128         try {
129             workBook = new HSSFWorkbook(file);
130         } catch (IOException e) {
131             e.printStackTrace();
132             return new ResultObject(false , UPLOAD_ERROR_2003 ,null,null);  
133         }
134         Sheet sheet = workBook.getSheetAt(0);
135         int lastrow = sheet.getLastRowNum()+1;
136         int rows = sheet.getPhysicalNumberOfRows(); 
137         int cells = sheet.getRow(0).getLastCellNum();// 获得列数 
138         if(cells!=8){
139             return new ResultObject(false , ORGANSIE_ERROR_TEMPLE ,null,null); 
140         }
141             if(lastrow !=rows){
142                 return new ResultObject(false , UPLOAD_ERROR_NULLTR ,null,null); 
143             }
144             if(maxNum <rows ){
145                 return new ResultObject(false , UPLOAD_ERROR_MAX ,null,null);
146             }
147             if (rows > 1) { 
148                 sheet.getMargin(Sheet.TopMargin);
149                 // 行循环 从第二行开始，EXCEL文档第一行所放的是标题不是数据
150                 for (int r = 1; r < rows; r++) {
151                 Row row = sheet.getRow(r);
152                     if (row != null) { 
153                     String[] llist = new String[cells];
154                         for (short c = 0; c < cells; c++) { // 列循环 
155                         Cell cell = row.getCell(c); 
156                             if (cell != null) {
157                                 //将循环的值装载进string数组
158                                 llist[c] = PoiExcelUtils.getValue(cell);
159                             } 
160                         }
161                         hlist.add(llist);    
162                     }
163                 } 
164             } 
165         } catch (Exception ex) { 
166             return new ResultObject(false , UPLOAD_ERROR_FORMATE , null,null);
167         }
168         return new ResultObject(true , UPLOAD_SUCCESS , hlist ,null);
169     }
170     
171     /*----------------------2. 装载数据 ---------------------*/
172     private ResultObject mappingData(ResultObject result){
173         ArrayList<String[]> datas = (ArrayList<String[]>)result.getObjects();
174         ArrayList<CheckSet> list = new ArrayList<CheckSet>();
175         for (String[] data : datas) {
176             try {
177                 CheckSet checkset = new CheckSet();
178                 // 0:考核月份,1:拓展经理,2:区域,3:推荐总量,4:成功总量,
179                 // 5:网点总量,6:有效网点总量,7:地市编码
180                 checkset.setCheckMonth(trimStr(data[0]));
181                 checkset.setManagerName(trimStr(data[1]));
182                 checkset.setOrganiseName(trimStr(data[2]));
183                 checkset.setRecommendTotal(trimStr(data[3]));
184                 checkset.setSuccessTotal(trimStr(data[4]));
185                 checkset.setNetPointTotal(trimStr(data[5]));
186                 checkset.setEffentNetPointTotal(trimStr(data[6]));
187                 checkset.setOrganiseCode(trimStr(data[7]));
188                 list.add(checkset);
189             } catch (Exception e) {
190                 return new ResultObject(false,MAPPING_ERROR_DATA ,null,data[0]);
191             }
192         }
193         result.setObjects(list);
194         return result;
195     }
196     
197     /**
198      * 去除excel后面的.
199      * @param ob
200      * @return
201      */
202     private String trimStr(Object ob) {
203         if (ob == null) {
204             return "";
205         }
206         String ss = ob.toString();
207         if (ss.indexOf(".") == -1)
208             return ss;
209         while (ss.endsWith("0")) {
210             ss = ss.substring(0, ss.length() - 1);
211         }
212         if (ss.endsWith("."))
213             ss = ss.substring(0, ss.length() - 1);
214         return ss;
215     }
216     
217     /*-----------------------4. 批量插入数据 ----------------------------*/
218     /**
219      * 批量新增业务经理考核指标
220      * @param 新增业务经理考核
221      * 如果导入的记录中，以考核月份和地区编码为查询条件,如果数据库中存在,则跳过这个记录不进行
222      * 插入数据的操作
223      */
224     private ResultObject createData(ResultObject _ro) {
225         ArrayList<CheckSet> checkSets = _ro.getObjects();
226         String curretUser = null;
227         boolean flag = false;
228         //判断excle中地市code是否为空
229         boolean isNullFlag = false;
230         //判断传进来的地市code是否在我们的系统中存在
231         boolean isNotExsit = false;
232         try{
233             for (CheckSet entity : checkSets) {
234                 //第一步校验如果code为空
235                 isNullFlag = (null ==entity.getOrganiseCode() || "".equals(entity.getOrganiseCode()));
236                 //判断传入进来的code是否存在
237                 isNotExsit = organiseDao.isNotExist(entity.getOrganiseCode());
238                 //验证月份和区域两个条件并列是否数据库中有,有的话就不进行插入操作
239                 flag = checkSetDao.isExist(entity.getCheckMonth(), entity.getOrganiseCode());
240                 if (isNullFlag || isNotExsit){
241                     //如果OrganiseCode为空或者这个OrganiseCode在数据库不存在则不向数据库插入此记录
242                     debugLogger.info("select db exist  OrganiseCode is null or not exist OrganiseCode: "
243                              + entity.getOrganiseCode() + "record" );
244                 }else{
245                     //如果OrganiseCode在数据库已经有了，但是OrganiseCode和CheckMonth联合查询数据已经存在也不向数据插入数据
246                     if (flag){
247                         //如果存在则不向数据库插入此记录
248                         debugLogger.info("select db exist checkMonth is: "+ entity.getCheckMonth() +
249                                 " and organiseCode is " + entity.getOrganiseCode() + "record" );
250                     }else{
251                         create(entity);
252                     }
253                 }
254             }
255         }catch(Exception e){
256             e.printStackTrace();
257             _ro.setResult(new Result(false,ORGANSIE_ERROR_DATA));
258             _ro.setErrorName(curretUser);
259             debugLogger.info("batchinsert insert checkSet failture the result is "+ e.toString());
260         }
261         debugLogger.info("batchinsert checkset success");
262         userLogger.info("batchinsert checkset success");
263         return _ro;
264     }
265 
266 }

--保存结果的类

View Code

 1 /**
 2  * com.test.bcp.user.vo.OrganiseMsgVo *
 3  * @licences:
 4  */
 5 package com.test.bcp.user.vo;
 6 
 7 import java.util.ArrayList;
 8 
 9 import com.kms.framework.core.view.support.Result;
10 
11 /**
12  * @author: ryan
13  * @date: 2011-11-30
14  * @time: 下午03:31:04 
15  * @desc: 对result装饰
16  */
17 public class ResultObject{
18     
19     private Result result;
20     
21     private ArrayList objects;
22     
23     private String errorName;
24     
25     public ResultObject(){
26         setResult(new Result(true,"0"));
27         setObjects(new ArrayList<Object>());
28         setErrorName(null);
29     }
30     
31     public ResultObject( boolean bn , String msg , ArrayList list ,String errorName){
32         setResult(new Result(bn,msg));
33         setObjects(list);
34         setErrorName(errorName);
35     }
36     
37     public Result getResult() {
38         return result;
39     }
40 
41     public void setResult(Result result) {
42         this.result = result;
43     }
44 
45     public ArrayList getObjects() {
46         return objects;
47     }
48 
49     public void setObjects(ArrayList objects) {
50         this.objects = objects;
51     }
52 
53     public String getErrorName() {
54         return errorName;
55     }
56 
57     public void setErrorName(String errorName) {
58         this.errorName = errorName;
59     }
60 }

--读取的工具类

View Code

  1 /**
  2  * com.newyulong.bcp.user.util.PoiExcelUtils
  3  * @licences:
  4  */
  5 package com.newyulong.bcp.user.util;
  6 
  7 import java.io.BufferedInputStream;
  8 import java.io.IOException;
  9 import java.io.InputStream;
 10 import java.net.URL;
 11 import java.text.DecimalFormat;
 12 import java.util.ArrayList;
 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 14 import org.apache.poi.ss.usermodel.Cell;
 15 import org.apache.poi.ss.usermodel.DateUtil;
 16 import org.apache.poi.ss.usermodel.Row;
 17 import org.apache.poi.ss.usermodel.Sheet;
 18 import org.apache.poi.ss.usermodel.Workbook;
 19 //import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 20 
 21 /**
 22  * @author: ryan
 23  * @date: 2011-10-11
 24  * @time: 下午02:17:01 
 25  * @desc: POI excel操作
 26  */
 27 public class PoiExcelUtils {
 28     
 29     /**
 30      * 读excel内容
 31      * @param filePath
 32      * @return
 33      */
 34     public static ArrayList<String[]> readExcel(String filePath) {
 35         ArrayList<String[]> hlist = new ArrayList<String[]>();
 36         try { 
 37             Workbook workBook = null;
 38             try {
 39                 URL url = new URL(filePath);
 40                 workBook = new HSSFWorkbook(new BufferedInputStream(url.openStream())); //支持2003及以前 
 41             } catch (Exception ex) { 
 42     //            workBook = new XSSFWorkbook(filePath); //maven POI 3.8-beta4中没有XSSFWorkbook类  支持2007 
 43             } 
 44         // 获得Excel中工作表个数 
 45             //循环每个工作表 
 46             // 创建工作表 
 47             Sheet sheet = workBook.getSheetAt(0); 
 48             // 获得行数 
 49             int lastrow = sheet.getLastRowNum()+1;
 50             int rows = sheet.getPhysicalNumberOfRows(); 
 51                 if(lastrow !=rows){
 52                     return null;
 53                 }
 54                 if (rows > 1) { 
 55                     sheet.getMargin(Sheet.TopMargin);
 56                     // 行循环 从第二行开始，EXCEL文档第一行所放的是标题不是数据 比如 用户名 账号
 57                     int cells = sheet.getRow(0).getLastCellNum();// 获得列数 
 58                     for (int r = 1; r < rows; r++) {
 59                     Row row = sheet.getRow(r);
 60                         if (row != null) { 
 61                         String[] llist = new String[cells];
 62                             for (short c = 0; c < cells; c++) { // 列循环 
 63                             Cell cell = row.getCell(c); 
 64                                 if (cell != null) {
 65                                     //将循环的值装载进string数组
 66                                     llist[c] = getValue(cell);
 67                                 } 
 68                             }
 69                             hlist.add(llist);    
 70                         }
 71                     } 
 72                 } 
 73             } catch (Exception ex) { 
 74                 ex.printStackTrace(); 
 75             }
 76             return hlist;
 77         } 
 78 
 79         public static String getValue(Cell cell) { 
 80         String value = ""; 
 81             switch (cell.getCellType()) {
 82             /* 此行表示单元格的内容为数值型  */ 
 83             case Cell.CELL_TYPE_NUMERIC: 
 84                 if (DateUtil.isCellDateFormatted(cell)) { 
 85                 // 如果是date类型则 ，获取该cell的date值 
 86                 value = DateUtil.getJavaDate(cell.getNumericCellValue()).toString(); 
 87                 } else {
 88                 // 纯数字 
 89                 DecimalFormat df = new DecimalFormat("0");  
 90                 Double db =cell.getNumericCellValue();
 91                 value = df.format(db); 
 92                 } 
 93             break; 
 94             /* 此行表示单元格的内容为string类型 */ 
 95             case Cell.CELL_TYPE_STRING:
 96                 value = cell.getRichStringCellValue().toString(); 
 97             break; 
 98             case Cell.CELL_TYPE_FORMULA:
 99                 // 读公式计算值 
100                 value = String.valueOf(cell.getNumericCellValue()); 
101                 // 如果获取的数据值为非法值,则转换为获取字符串
102                 if (value.equals("NaN")) { 
103                     value = cell.getRichStringCellValue().toString(); 
104                 } 
105             break;
106             /* 布尔类型 */ 
107             case Cell.CELL_TYPE_BOOLEAN:
108                 value = " "+ cell.getBooleanCellValue(); 
109             break; 
110             /* 此行表示该单元格值为空 */ 
111             case Cell.CELL_TYPE_BLANK: 
112                 value = " "; 
113             break;
114             /* 此行表示该单元格值故障  */
115             case Cell.CELL_TYPE_ERROR:
116                 value = " "; 
117             break; 
118             default: 
119                 value = cell.getRichStringCellValue().toString(); 
120             } 
121         return value; 
122         } 
123 
124 
125         
126         /**
127           * 写excel内容
128           * @param names 所写入的标题
129           * @return values 所写入的内容
130          */
131         public static String writeExcel(String[] names ,ArrayList<?> values){
132             HSSFWorkbook wb = new HSSFWorkbook();   
133             Sheet sheet = wb.createSheet();   
134       
135             // 创建一个新的行，添加几个单元格。   
136             // 行号从0开始计算   
137             Row row     = sheet.createRow((short)0);   
138             // 创建一个单元格，设置单元格的值   
139             Cell cell   = row.createCell((short)0);   
140             cell.setCellValue(1);   
141       
142             row.createCell((short)1).setCellValue(1.2);   
143             row.createCell((short)2).setCellValue(11);   
144             row.createCell((short)3).setCellValue(true);   
145             // 写入输出结果   
146 //            OutputStream out = response.getOutputStream();   
147 //            wb.write(out);   
148 //            out.close();   
149 
150             for(int i = 0 ; i< names.length ; i++){
151                 
152             }
153             return "";
154         }
155         
156     }

posted @ 2013-03-15 12:14 lifeng_study 阅读(266) 评论(0) 编辑收藏举报

刷新页面返回顶部

lifeng_study

sturts2批量导入excel中的信息入库大致代码

公告