利用poi实现数据库的数据导入导出excel文件
在这里首先我要将自己遇到的各种问题,以及需求记录下来,做一个备忘,便于以后查看:
需求:主要实现两个功能,将oracle数据库里的数据导出为excel,同时需要将excel表格的数据导入到数据库
环境:springmvc + spring + mybatis + jdk1.7 + poi3.8 + easyui + oracle
在开始的时候,我就各种找jar包搭建环境,搭建环境时候所有的jar包都没有,只能去各种找,去下载,话不多说,直接上jar包,
然后就是各种配置文件,将配置文件现在直接贴出来:
《 applicationContext-dao.xml 》:
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 xmlns:context="http://www.springframework.org/schema/context"xmlns:p="http://www.springframework.org/schema/p"
4 xmlns:aop="http://www.springframework.org/schema/aop"xmlns:tx="http://www.springframework.org/schema/tx"
5 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd7 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd9 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
《 applicationContext-service.xml 》:
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 xmlns:context="http://www.springframework.org/schema/context"xmlns:p="http://www.springframework.org/schema/p"
4 xmlns:aop="http://www.springframework.org/schema/aop"xmlns:tx="http://www.springframework.org/schema/tx"
5 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd7 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd8 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd9 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">
10
11
12
13
14
15 class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 pointcut="execution(* com.sword.dataprocess.service*.*.*(..))" />
35
36
数据库的连接信息:
《 db.properties 》:
1 jdbc.dbType=oracle2 jdbc.driver=oracle.jdbc.driver.OracleDriver3 jdbc.url=jdbc:oracle:thin:@远程的连接ip:orcl4 jdbc.username=xxx5 jdbc.password=xxx
日志文件:
《log4j.properties 》: 这里不再贴出
springmvc的xml:
《springmvc.xml》:
1
2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:mvc="http://www.springframework.org/schema/mvc"
3 xmlns:context="http://www.springframework.org/schema/context"
4 xmlns:aop="http://www.springframework.org/schema/aop"xmlns:tx="http://www.springframework.org/schema/tx"
5 xmlns:task="http://www.springframework.org/schema/task"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans7 http://www.springframework.org/schema/beans/spring-beans-4.2.xsd8 http://www.springframework.org/schema/mvc9 http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd10 http://www.springframework.org/schema/context11 http://www.springframework.org/schema/context/spring-context-4.2.xsd12 http://www.springframework.org/schema/aop13 http://www.springframework.org/schema/aop/spring-aop-4.2.xsd14 http://www.springframework.org/schema/tx15 http://www.springframework.org/schema/tx/spring-tx-4.2.xsd16 http://www.springframework.org/schema/task17 http://www.springframework.org/schema/task/spring-task-4.2.xsd">
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 class="org.springframework.web.servlet.view.InternalResourceViewResolver">
33
34
35
36
37
38
39 class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
40
41
42
43 5242880
44
45
46
47
最后最重要的就是web.xml :
《web.xml 》:
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 DataProcess
4
5 index.html
6 index.htm
7 index.jsp
8 default.html
9 default.htm
10 default.jsp
11
12
13
14 org.springframework.web.context.ContextLoaderListener
15
16
17 contextConfigLocation
18 classpath:applicationContext-*.xml
19
20
21
22
23 springmvc
24 org.springframework.web.servlet.DispatcherServlet
25
26
27 contextConfigLocation
28 classpath:springmvc.xml
29
30
31
32 springmvc
33 /
34
35
前端主要使用的是easyui里面的datagried :大致页面如下:
然后就是把jsp页面的代码贴出来:
《index.jsp》:
1
2 pageEncoding="UTF-8"%>
3
4
5
6
7
MRP导入导出8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 functiondoExport() {24 location.href="${pageContext.request.contextPath}/export";25 }26
27 functiondoImport() {28 $("#button-import").upload({29 name:'myFile',30 action:'${pageContext.request.contextPath}/import"',31 onComplete:function(data){32 alert(data);33 if(data == "success"){34 $.messager.alert('友情提示','恭喜你,导入成功');35 }36 if(data == "error"){37 $.messager.alert('友情提示','导入失败,请按正确的模板数据导入!');38 }39 $('#grid').datagrid('load');40 }41 });42
43 }44
45
46 //工具栏
47 var toolbar =[{48 id : 'button-import',49 text : '导入',50 iconCls : 'icon-redo',51 handler : doImport52 }, {53 id : 'button-export',54 text : '导出',55 iconCls : 'icon-undo',56 handler : doExport57 } ];58 //定义列
59 var columns =[ [ {60 field : 'p_id',61 title : '料件编号',62 width : 120,63 align : 'center',64 }, {65 field : 'p_name',66 title : '品名',67 width : 120,68 align : 'center',69 }, {70 field : 'p_guige',71 title : '规格',72 width : 120,73 align : 'center',74 }, {75 field : 'p_xdata',76 title : '行动日期',77 width : 120,78 align : 'center'
79 }, {80 field : 'p_jdate',81 title : '交货日期',82 width : 100,83 align : 'center'
84 }, {85 field : 'p_descCount',86 title : '排产数量',87 width : 100,88 align : 'center'
89 } ] ];90
91 $(function() {92 /*daoru fenqu*/
93 //先将body隐藏,再显示,不会出现页面刷新效果
94 $("body").css({95 visibility : "visible"
96 });97
98 //管理数据表格
99 $('#grid').datagrid({100 iconCls : 'icon-forward',101 fit : true,102 border : true,103 rownumbers : true,104 striped : true,105 pageList : [ 30, 50, 100],106 pagination : true,107 toolbar : toolbar,108 url : "${pageContext.request.contextPath}/show",109 idField : 'p_id',110 columns : columns,111 });112
113 var pager = $('#grid').datagrid('getPager'); //get the pager of datagrid
114 pager.pagination({115 showPageList:false,116 });117
118 });119
120
121
122
123
124
125
126
127
接下来就是代码的实现:
《controller层》:
1 importjava.io.File;2 importjava.io.FileInputStream;3 importjava.util.List;4 importjava.util.Map;5
6 importjavax.servlet.ServletOutputStream;7 importjavax.servlet.http.HttpServletRequest;8 importjavax.servlet.http.HttpServletResponse;9
10 importorg.apache.commons.fileupload.disk.DiskFileItem;11 importorg.springframework.beans.factory.annotation.Autowired;12 importorg.springframework.stereotype.Controller;13 importorg.springframework.ui.Model;14 importorg.springframework.web.bind.annotation.RequestMapping;15 importorg.springframework.web.bind.annotation.ResponseBody;16 importorg.springframework.web.context.request.RequestContextHolder;17 importorg.springframework.web.context.request.ServletRequestAttributes;18 importorg.springframework.web.multipart.MultipartFile;19 importorg.springframework.web.multipart.MultipartHttpServletRequest;20 importorg.springframework.web.multipart.commons.CommonsMultipartFile;21
22 importcom.sword.dataprocess.pojo.DataProcess;23 importcom.sword.dataprocess.service.DataService;24 importcom.sword.dataprocess.utils.FileUtils;25
26 @Controller27 public classDataController {28
29 @Autowired30 privateDataService dataService;31
32 @RequestMapping(value={"/index","/index.html","/index.htm"})33 publicString index(){34 return "index";35 }36
37 @RequestMapping("/show")38 @ResponseBody39 public Listshow(Model model){40 List list =dataService.findAll();41 model.addAttribute("list", list);42 returnlist;43 }44
45 //文件导出
46 @RequestMapping("/export")47 public void exportXls(HttpServletRequest request,HttpServletResponse response) throwsException{48 //一个流49 //两个头50 //下载文件的mime类型
51 response.setContentType("application/vnd.ms-excel"); //常见的文件 可以省略52
53 //文件的打开方式 inline在线打开 attachment
54 String agent = request.getHeader("User-Agent");55 String filename = FileUtils.encodeDownloadFilename("data.xlsx", agent);56 response.setHeader("content-disposition", "attachment;fileName="+filename);57 ServletOutputStream outputStream =response.getOutputStream();58
59 //获取模板 在当前项目
60 ServletRequestAttributes attributes =(ServletRequestAttributes) RequestContextHolder.currentRequestAttributes();61 String templatePath = request.getServletContext().getRealPath(File.separator)+"temp"+File.separator+"data.xlsx";62 System.out.println(templatePath);63 FileInputStream fileInputStream = newFileInputStream(templatePath);64
65 dataService.exportAls(fileInputStream, outputStream);66 }67
68
69 //文件导入70 //接收页面传来的文件
71 @RequestMapping("/import")72 @ResponseBody73 publicString importXlsx(HttpServletRequest request){74 System.out.println(111);75 MultipartHttpServletRequest multipartRequest =(MultipartHttpServletRequest) request;76 MultipartFile myFile = multipartRequest.getFile("myFile"); //通过参数名获取指定文件 文件本身 变量名和文件上传时的名称保持一致
77 String myFileFileName = myFile.getOriginalFilename();//文件的名字
78 String myFileContentType = myFile.getContentType(); //文件的mime类型
79
80 CommonsMultipartFile cf=(CommonsMultipartFile)myFile;81 DiskFileItem fi =(DiskFileItem)cf.getFileItem();82
83 File f =fi.getStoreLocation();84 String msg = null;85
86 Boolean flag =dataService.importXls(f,myFileContentType);87 if(flag){88 msg = "success";89 }else{90 msg = "error";91 }92 returnmsg;93 }94
95
96 }
《service层》:
1 importjava.io.File;2 importjava.io.FileInputStream;3 importjava.io.IOException;4 importjava.text.SimpleDateFormat;5 importjava.util.Date;6 importjava.util.List;7
8 importjavax.servlet.ServletOutputStream;9
10 importorg.apache.commons.lang3.StringUtils;11 importorg.apache.poi.hssf.usermodel.HSSFRow;12 importorg.apache.poi.hssf.usermodel.HSSFSheet;13 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;14 importorg.apache.poi.xssf.usermodel.XSSFCell;15 importorg.apache.poi.xssf.usermodel.XSSFRow;16 importorg.apache.poi.xssf.usermodel.XSSFSheet;17 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;18 importorg.springframework.beans.factory.annotation.Autowired;19 importorg.springframework.stereotype.Service;20
21 importcom.sword.dataprocess.mapper.DataMapper;22 importcom.sword.dataprocess.pojo.DataProcess;23 importcom.sword.dataprocess.service.DataService;24
25 @Service26 public class DataServiceImpl implementsDataService{27 @Autowired28 privateDataMapper dataMapper;29
30 @Override31 public intdataCount() {32 returndataMapper.dataCount();33 }34
35 @Override36 public voidexportAls(FileInputStream fileInputStream, ServletOutputStream outputStream) {37 //Workbook工作簿
38 XSSFWorkbook book = null;39 try{40 book = newXSSFWorkbook(fileInputStream);41 } catch(IOException e) {42 e.printStackTrace();43 }44
45 //工作表 sheet
46 XSSFSheet sheet = book.getSheetAt(0);47 //获取第二个sheet中的第一行第一列的样式 及边框48 //XSSFCellStyle cellStyle = book.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
49 List list =dataMapper.findAll();50 System.out.println(list.size());51 int rowIndex = 1; //让表格从第二行开始导入
52 XSSFCell cell = null;53 for(DataProcess dataProcess : list) {54 //新建一行
55 XSSFRow row =sheet.createRow(rowIndex);56 cell = row.createCell(0); //第一个单元格57 //设定已经准备好单元格的样式58 //cell.setCellStyle(cellStyle);
59 String id =dataProcess.getP_id();60 if(id != null){61 cell.setCellValue(id);62 }63
64 cell = row.createCell(1); //第一个单元格
65 String name =dataProcess.getP_name();66 if(name != null){67 cell.setCellValue(name);68 }69
70 cell = row.createCell(2); //第二个单元格
71 String guige =dataProcess.getP_guige();72 if(guige != null){73 cell.setCellValue(guige);74 }75
76 cell = row.createCell(3); //第三个单元格
77 String xdata =dataProcess.getP_xdata();78 if(xdata != null){79 cell.setCellValue(xdata);80 }81
82 cell = row.createCell(4); //第四个单元格
83 String jdate =dataProcess.getP_jdate();84 if(jdate != null){85 cell.setCellValue(jdate);86 }87
88 /*cell = row.createCell(5); // 第五个单元格89 Integer sourceCount = dataProcess.getP_sourceCount();90 if(sourceCount != null){91 cell.setCellValue(sourceCount);92 }*/
93 cell = row.createCell(6); //第六个单元格
94 Integer descCount =dataProcess.getP_descCount();95 if (descCount != null) {96 cell.setCellValue(descCount);97 }98
99 rowIndex++;100 }101 //把工作簿放在输出流中
102 try{103 book.write(outputStream);104 } catch(IOException e) {105 e.printStackTrace();106 }107 }108
109 //导入数据
110 @Override111 publicBoolean importXls(File myFile, String myFileContentType) {112
113 if ("application/vnd.ms-excel".equals(myFileContentType)) {114 System.out.println(123);115 try{116 //获取workbook工作簿
117 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(newFileInputStream(myFile));118 //获取sheet 工作表
119 HSSFSheet sheet = hssfWorkbook.getSheetAt(0);120 //获取工作表的最后一行索引
121 int lastRowNum =sheet.getLastRowNum();122 for (int i = 1; i <= lastRowNum; i++) {123 DataProcess dataProcess = newDataProcess();124 HSSFRow row =sheet.getRow(i);125 //料件编号 特征码(8个0)行动日期 交货日期 排产数量 版本号(一次导入只用设置一个相同的值就行) 已执行步骤为0126
127 //料件编号
128 String p_id = row.getCell(0).getStringCellValue();129 dataProcess.setP_id(p_id);130 //行动日期
131 String p_xdata = row.getCell(3).getStringCellValue();132 dataProcess.setP_xdata(p_xdata);;133 //交货日期
134 String p_jdate = row.getCell(4).getStringCellValue();135 dataProcess.setP_jdate(p_jdate);136 /*// 需求数量137 Integer p_sourceCount = (int) row.getCell(5).getNumericCellValue();138 dataProcess.setP_sourceCount(p_sourceCount);*/
139 //排产数量
140 Integer p_descCount = (int) row.getCell(5).getNumericCellValue();141 dataProcess.setP_descCount(p_descCount);142 //版本号(一次导入只用设置一个相同的值就行)
143 SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-dd");144 String datetime = tempDate.format(newDate());145 String p_version = "MRPVERNO"+datetime;146 dataProcess.setP_version(p_version);147 //向tc_aau_file表插入数据
148 dataMapper.insertdata(dataProcess);149 //向tc_aat_file表插入数据
150 if(i==lastRowNum){151 dataMapper.insertToAAT(p_version);152 }153 }154 } catch(Exception e) {155 e.printStackTrace();156 return false;157 }158
159 } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(myFileContentType)) {160 try{161 //获取workbook工作簿
162 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(newFileInputStream(myFile));163 //获取sheet 工作表
164 XSSFSheet sheet = xssfWorkbook.getSheetAt(0);165 //获取工作表的最后一行索引
166 int lastRowNum =sheet.getLastRowNum();167 for (int i = 1; i <= lastRowNum; i++) {168 DataProcess dataProcess = newDataProcess();169 XSSFRow row =sheet.getRow(i);170 //料件编号 特征码(8个0)行动日期 交货日期 排产数量 版本号(一次导入只用设置一个相同的值就行) 已执行步骤为0171
172 //料件编号
173 String p_id = row.getCell(0).getStringCellValue();174 dataProcess.setP_id(p_id);175 //行动日期
176 String p_xdata = row.getCell(3).getStringCellValue();177 dataProcess.setP_xdata(p_xdata);178 //交货日期
179 String p_jdate = row.getCell(4).getStringCellValue();180 dataProcess.setP_jdate(p_jdate);181
182 /*// 需求数量183 Integer p_sourceCount = (int) row.getCell(5).getNumericCellValue();184 dataProcess.setP_sourceCount(p_sourceCount);*/
185 //排产数量
186 Integer p_descCount = (int) row.getCell(5).getNumericCellValue();187 dataProcess.setP_descCount(p_descCount);188 //版本号(一次导入只用设置一个相同的值就行)
189 SimpleDateFormat tempDate = new SimpleDateFormat("yyyy-MM-dd");190 String datetime = tempDate.format(newDate());191 String p_version = "MRPVERNO"+datetime;192 dataProcess.setP_version(p_version);193
194 //向tc_aau_file表插入数据
195 dataMapper.insertdata(dataProcess);196 //向tc_aat_file表插入数据
197 if(i==lastRowNum){198 dataMapper.insertToAAT(p_version);199 }200 }201 }catch(Exception e) {202 e.printStackTrace();203 return false;204 }205 } //elseif 结束
206 return true;207 }208
209
210 //查询所有数据
211 @Override212 public ListfindAll() {213 List result =dataMapper.findAll();214 returnresult;215 }216 }
《mapper 层》:
1 importjava.util.List;2
3 importorg.apache.ibatis.annotations.Param;4
5 importcom.sword.dataprocess.pojo.DataProcess;6
7 public interfaceDataMapper {8 public intdataCount();9
10 public ListfindAll();11
12 public void insertdata(@Param("dataProcess")DataProcess dataProcess);13
14 public void insertToAAT(@Param("p_version")String p_version);15 }
《对应的xml:》:
1 <?xml version="1.0" encoding="UTF-8"?>
2
3
4
5
6 select count(1) from tc_aau_file7
8
9
10
11
12
13
14
15
16
17
18
19
20 select t.TC_AAU01 ,21 i.IMA02 ,22 i.ima021 ,23 t.TC_AAU06 ,24 t.TC_AAU07 ,25 t.TC_AAU0926 from SWORD.IMA_FILE i, SWORD.TC_AAU_FILE t where i.ima01 = t.tc_aau0127
28
29
30 INSERT INTO SWORD.TC_AAU_FILE ("TC_AAU01", "TC_AAU03", "TC_AAU06", "TC_AAU07", "TC_AAU09", "TC_AAU13", "TC_AAU14") VALUES (#{dataProcess.p_id}, '00000000', TO_DATE(#{dataProcess.p_xdata}, 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE(#{dataProcess.p_jdate}, 'SYYYY-MM-DD HH24:MI:SS'),#{dataProcess.p_descCount}, #{dataProcess.p_version}, '0')31
32
33
34 INSERT INTO SWORD.TC_AAT_FILE ("TC_AAT01") VALUES (#{p_version})35
36
37
用到了一个工具类(fileutils):
1 importjava.io.IOException;2 importjava.net.URLEncoder;3
4 importsun.misc.BASE64Encoder;5
6 public classFileUtils {7 /**
8 * 下载文件时,针对不同浏览器,进行附件名的编码9 *10 *@paramfilename11 * 下载文件名12 *@paramagent13 * 客户端浏览器14 *@return编码后的下载附件名15 *@throwsIOException16 */
17 public staticString encodeDownloadFilename(String filename, String agent)18 throwsIOException {19 if (agent.contains("Firefox")) { //火狐浏览器
20 filename = "=?UTF-8?B?"
21 + new BASE64Encoder().encode(filename.getBytes("utf-8"))22 + "?=";23 filename = filename.replaceAll("\r\n", "");24 } else { //IE及其他浏览器
25 filename = URLEncoder.encode(filename, "utf-8");26 filename = filename.replace("+"," ");27 }28 returnfilename;29 }30 }
遇到的问题:
1.在springmvc.xml配置了前端静态资源不拦截之后,在显示前端界面时候,总是报一个错:不能够找到各种静态文件,无论我怎么设置,就是获取不到,而且在项目启动之后,直接访问对应的页面,是可以正常显示的,然后一通过视图解析器就获取不到静态资源,不能够正常显示,最终各种查资料,都显示的是没有配置忽略前端静态资源文件,解决不了问题,最后我处理了很久,终于发现自己犯了一个大错,就是将静态资源的导入时候,写的是相对路径,找不到对应的文件,最终解决办法就是写的动态获取的全路径。
2.遇到的第二个问题:我在导入的时候封装了一个对象,在mapper那里传入了一个对象,但是在对应的xml里面我取不到对象里面的属性值,最终查阅资料,需要在传的对象前面添加一个@param(”xxx“)注解,问题得以解决。
后续会继续补充,未经允许不得转载,欢迎大家多多指正!