java Excel 导入导出

使用poi实现springMVC的Excel导入导出

需要jar包：poi.jar poi-ooxml.jar poi-ooxml-schemas.jar xbean.jar(用于解析excel2007)

controller层导出:

 1 // 导出excel
 2         if (action != null && action.equals("export")) {
 3             List<LoadPriceShipownerQueryItem> exportList = priceShipownerService
 4                     .getPriceShipownerListByCondition(condition, pagingInfo);
 5             HSSFWorkbook workbook = priceShipownerService
 6                     .priceShipownerExportExcel(exportList);
 7             String filename = "priceShipowner.xls";
 8             OutputStream out = response.getOutputStream();
 9             response.setHeader("Content-disposition", "attachment;filename="
10                     + URLEncoder.encode(filename, "UTF-8"));
11             response.setContentType("application/msexcel;charset=UTF-8");
12             workbook.write(out);
13             if (out != null) {
14                 out.close();
15             }
16             workbook.close();
17         }

controller层导入：

 1 // 导入Excel
 2         if (action != null && action.equals("import")) {
 3             final String memorySize = Config.get("memorySize");// 内存缓冲大小
 4             final String sizeMax = Config.get("sizeMax");// 文件允许的最大值
 5             if (ServletFileUpload.isMultipartContent(request)) {
 6                 // 创建磁盘工厂
 7                 DiskFileItemFactory factory = new DiskFileItemFactory();
 8                 // 设置内存缓冲大小
 9                 factory.setSizeThreshold(Integer.parseInt(memorySize));
10                 // 设置临时目录
11                 String tempdir = request.getServletContext().getRealPath(
12                         "/tempDir");
13                 File temDirFile = new File(tempdir);
14                 if (!temDirFile.exists()) {
15                     temDirFile.mkdir();
16                 }
17                 factory.setRepository(temDirFile);
18                 // 创建处理工具
19                 ServletFileUpload upload = new ServletFileUpload(factory);
20                 // 设置最大允许的尺寸
21                 int setFileSize = Integer.parseInt(sizeMax);
22                 int fileSize = request.getContentLength();
23                 upload.setSizeMax(setFileSize);
24                 if (fileSize <= setFileSize) {
25                     // 解析
26                     String contentType = request.getContentType();
27                     if (contentType == null && "".equals(contentType)) {
28                         mv.addObject("msg", "文件上传失败");
29                     }
30                     List<FileItem> fileItems = upload.parseRequest(request);
31                     Iterator<FileItem> iter = fileItems.iterator();
32                     for (; iter.hasNext();) {
33                         FileItem fileItem = (FileItem) iter.next();
34                         String fileName = fileItem.getName();
35                         InputStream is = fileItem.getInputStream();
36                         priceShipownerService.priceShipownerImportExcel(
37                                 fileName, is);
38                     }
39                 }
40             }
41         }

service层导出:

  1 // 第一步，创建一个webbook，对应一个Excel文件
  2         HSSFWorkbook workbook = new HSSFWorkbook();
  3 
  4         // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
  5         HSSFSheet sheet = workbook.createSheet("船东面价管理表");
  6         sheet.setDefaultColumnWidth(10);// 默认列宽
  7         // 设置第1列、第4列、第5列、第10列、第11列 的列宽
  8         sheet.setColumnWidth(0, 10 * 256);
  9         sheet.setColumnWidth(3, 15 * 256);
 10         sheet.setColumnWidth(4, 15 * 256);
 11         sheet.setColumnWidth(11, 20 * 256);
 12         sheet.setColumnWidth(12, 20 * 256);
 13         // 合并第一行标题单元格
 14         sheet.addMergedRegion(new CellRangeAddress((short) 0, (short) 0,
 15                 (short) 0, (short) 12));
 16 
 17         // 第三步，创建单元格样式
 18         HSSFCellStyle style = workbook.createCellStyle();
 19         style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
 20         style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
 21         // 设置边框
 22         style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
 23         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
 24         style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
 25         style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
 26 
 27         // 第四步，设置Title标题格式和内容
 28         HSSFFont font = workbook.createFont();
 29         font.setFontName("黑体");// 设置字体
 30         font.setFontHeightInPoints((short) 18);// 设置字体大小
 31         HSSFCellStyle titleStyle = workbook.createCellStyle();// 创建样式
 32         titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
 33         titleStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 垂直居中
 34         // 设置边框
 35         titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
 36         titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
 37         titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
 38         titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
 39         titleStyle.setFont(font);
 40 
 41         HSSFRow rowTitle = sheet.createRow(0);// 取得sheet第一行
 42         rowTitle.setHeight((short) (40 * 20));// 设置行高
 43         HSSFCell cellTitle = rowTitle.createCell(0);// 取得第一行第一列
 44         cellTitle.setCellValue("船东面价表");// 设置标题
 45         cellTitle.setCellStyle(titleStyle);// 为第一行标题加入样式
 46 
 47         // 第五部，设置Header格式和内容
 48         HSSFFont fontHeader = workbook.createFont();
 49         fontHeader.setFontName("黑体");
 50         fontHeader.setFontHeightInPoints((short) 12);// 设置字体大小
 51         HSSFCellStyle headerStyle = workbook.createCellStyle();
 52         headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个横向居中
 53         headerStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 创建垂直居中
 54         // 设置边框
 55         headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
 56         headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
 57         headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
 58         headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
 59         headerStyle.setFont(fontHeader);// 设置字体
 60 
 61         // 设置表头
 62         HSSFRow rowHeader = sheet.createRow(1);
 63         rowHeader.setHeight((short) (25 * 20));
 64         HSSFCell cellHeader = rowHeader.createCell(0);
 65         cellHeader.setCellValue("序号");
 66         cellHeader.setCellStyle(headerStyle);
 67         cellHeader = rowHeader.createCell(1);
 68         cellHeader.setCellValue("航线");
 69         cellHeader.setCellStyle(headerStyle);
 70         cellHeader = rowHeader.createCell(2);
 71         cellHeader.setCellValue("船公司");
 72         cellHeader.setCellStyle(headerStyle);
 73         cellHeader = rowHeader.createCell(3);
 74         cellHeader.setCellValue("起运港");
 75         cellHeader.setCellStyle(headerStyle);
 76         cellHeader = rowHeader.createCell(4);
 77         cellHeader.setCellValue("目的港");
 78         cellHeader.setCellStyle(headerStyle);
 79         cellHeader = rowHeader.createCell(5);
 80         cellHeader.setCellValue("航程");
 81         cellHeader.setCellStyle(headerStyle);
 82         cellHeader = rowHeader.createCell(6);
 83         cellHeader.setCellValue("船期");
 84         cellHeader.setCellStyle(headerStyle);
 85         cellHeader = rowHeader.createCell(7);
 86         cellHeader.setCellValue("币种");
 87         cellHeader.setCellStyle(headerStyle);
 88         cellHeader = rowHeader.createCell(8);
 89         cellHeader.setCellValue("20GP");
 90         cellHeader.setCellStyle(headerStyle);
 91         cellHeader = rowHeader.createCell(9);
 92         cellHeader.setCellValue("40GP");
 93         cellHeader.setCellStyle(headerStyle);
 94         cellHeader = rowHeader.createCell(10);
 95         cellHeader.setCellValue("40HQ");
 96         cellHeader.setCellStyle(headerStyle);
 97         cellHeader = rowHeader.createCell(11);
 98         cellHeader.setCellValue("有效起始日期");
 99         cellHeader.setCellStyle(headerStyle);
100         cellHeader = rowHeader.createCell(12);
101         cellHeader.setCellValue("有效结束日期");
102         cellHeader.setCellStyle(headerStyle);
103 
104         // 输出日期格式
105         SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");
106 
107         // 创建Content内容
108         for (int i = 0; i < exportList.size(); i++) {
109             LoadPriceShipownerQueryItem priceShipowner = exportList.get(i);
110 
111             // LineCname 转 LineCode
112             BacLineModel bacLineModel = new BacLineModel();
113             bacLineModel.setLineCode(priceShipowner.getLineCode());
114             List<BacLineModel> Linelist = bacLineManger
115                     .findByExample(bacLineModel);
116             String LineCname = Linelist.iterator().next().getLineCname();
117 
118             // 第六步，创建单元格，并设置值
119             HSSFRow rowContent = sheet.createRow(i + 2);
120             rowContent.setHeight((short) (17 * 20));
121 
122             HSSFCell cellContent = rowContent.createCell(0);
123 
124             cellContent.setCellValue(i + 1);
125             cellContent.setCellStyle(style);
126 
127             cellContent = rowContent.createCell(1);
128             cellContent.setCellValue(LineCname);
129             cellContent.setCellStyle(style);
130 
131             cellContent = rowContent.createCell(2);
132             cellContent.setCellValue(priceShipowner.getShipownerCname());
133             cellContent.setCellStyle(style);
134 
135             cellContent = rowContent.createCell(3);
136             cellContent.setCellValue(priceShipowner.getLoadPortCname());
137             cellContent.setCellStyle(style);
138 
139             cellContent = rowContent.createCell(4);
140             cellContent.setCellValue(priceShipowner.getDestPortCname());
141             cellContent.setCellStyle(style);
142 
143             cellContent = rowContent.createCell(5);
144             cellContent.setCellValue(priceShipowner.getVoyage());
145             cellContent.setCellStyle(style);
146 
147             cellContent = rowContent.createCell(6);
148             cellContent.setCellValue(priceShipowner.getSailingDec());
149             cellContent.setCellStyle(style);
150 
151             cellContent = rowContent.createCell(7);
152             cellContent.setCellValue(priceShipowner.getCurrency());
153             cellContent.setCellStyle(style);
154 
155             cellContent = rowContent.createCell(8);
156             cellContent.setCellValue(priceShipowner.getPrice20gp());
157             cellContent.setCellStyle(style);
158 
159             cellContent = rowContent.createCell(9);
160             cellContent.setCellValue(priceShipowner.getPrice40gp());
161             cellContent.setCellStyle(style);
162 
163             cellContent = rowContent.createCell(10);
164             cellContent.setCellValue(priceShipowner.getPrice40hq());
165             cellContent.setCellStyle(style);
166 
167             cellContent = rowContent.createCell(11);
168             cellContent.setCellValue(df.format(priceShipowner
169                     .getValidityStart()));
170             cellContent.setCellStyle(style);
171 
172             cellContent = rowContent.createCell(12);
173             cellContent
174                     .setCellValue(df.format(priceShipowner.getValidityEnd()));
175             cellContent.setCellStyle(style);
176         }
177         return workbook;

service层导入：

  1 // 获取当前用户
  2         UserSessionEntity userEntity = Context.getCurrentUser();
  3         List<PriceShipownerModel> importLst = new ArrayList<PriceShipownerModel>();
  4         Workbook workbook = null;
  5         if (fileName.matches("^.+\\.(?i)(xls)$")) {
  6             // Excel2003
  7             workbook = new HSSFWorkbook(is);
  8         } else if ((fileName.matches("^.+\\.(?i)(xlsx)$"))) {
  9             // Excel2007
 10             workbook = new XSSFWorkbook(is);
 11         } else {
 12             throw new Exception("文件类型错误");
 13         }
 14         /** 得到第一个shell */
 15         Sheet sheet = workbook.getSheetAt(0);
 16         /** 得到Excel的行数 */
 17         int totalRows = sheet.getPhysicalNumberOfRows();
 18         // 设置时间格式
 19         SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
 20         /** 循环Excel的行 */
 21         for (int r = 2; r < totalRows; r++) {
 22             Row row = sheet.getRow(r);
 23             // LineCname 转 LineCode
 24             BacLineModel bacLineModel = new BacLineModel();
 25             bacLineModel.setLineCname(getValue(row.getCell(1)));
 26             List<BacLineModel> Linelist = bacLineManger
 27                     .findByExample(bacLineModel);
 28             String LineCode = Linelist.iterator().next().getLineCode();
 29             // ShipownerCname 转 ShipownerCode
 30             BacShipownerModel bacShipownerModel = new BacShipownerModel();
 31             bacShipownerModel.setShipownerCname(getValue(row.getCell(2)));
 32             List<BacShipownerModel> Shipownerlist = bacShipownerManger
 33                     .findByExample(bacShipownerModel);
 34             String ShipownerCode = Shipownerlist.iterator().next()
 35                     .getShipownerCode();
 36             // PortEname 转 PortCode 起运港
 37             BacPortModel bacLoadPortModel = new BacPortModel();
 38             bacLoadPortModel.setPortEname((getValue(row.getCell(3))));
 39             List<BacPortModel> LoadPortlist = bacPortManager
 40                     .findByExample(bacLoadPortModel);
 41             String LoadPortCode = LoadPortlist.iterator().next().getPortCode();
 42             // PortEname 转 PortCode 目的港
 43             BacPortModel bacDestPortModel = new BacPortModel();
 44             bacDestPortModel.setPortEname((getValue(row.getCell(4))));
 45             List<BacPortModel> bacDestPorList = bacPortManager
 46                     .findByExample(bacDestPortModel);
 47             String DestPortCode = bacDestPorList.iterator().next()
 48                     .getPortCode();
 49 
 50             if (null != row) {
 51                 PriceShipownerModel priceShipowner = new PriceShipownerModel();
 52                 priceShipowner.setLineCode(LineCode); // 航线
 53                 priceShipowner.setShipownerCode(ShipownerCode);// 船公司
 54                 priceShipowner.setLoadPortCode(LoadPortCode);// 起运港
 55                 priceShipowner.setDestPortCode(DestPortCode);// 目的港
 56                 priceShipowner.setVoyage(getValue(row.getCell(5)));// 航程
 57                 priceShipowner.setSailingDec(getValue(row.getCell(6)));// 船期
 58                 priceShipowner.setCurrency(getValue(row.getCell(7)));// 币种
 59                 priceShipowner.setPrice20gp(Double.parseDouble(getValue(row
 60                         .getCell(8))));// 20GP
 61                 priceShipowner.setPrice40gp(Double.parseDouble(getValue(row
 62                         .getCell(9))));// 40GP
 63                 priceShipowner.setPrice40hq(Double.parseDouble(getValue(row
 64                         .getCell(10))));// 40HQ
 65                 priceShipowner.setValidityStart(dateFormat.parse(getValue(row
 66                         .getCell(11))));// 有效日期 //转成Date
 67                 priceShipowner.setValidityEnd(dateFormat.parse(getValue(row
 68                         .getCell(12))));// 结束日期 //转成Date
 69                 importLst.add(priceShipowner);
 70             }
 71         }
 72         if (is != null) {
 73             is.close();
 74         }
 75         workbook.close();
 76 
 77         for (PriceShipownerModel model : importLst) {
 78             // 唯一性校验
 79             PriceShipownerModel priceShipownerModel = new PriceShipownerModel();
 80             priceShipownerModel.setCompanyId(userEntity.getCompanyId());
 81             priceShipownerModel.setShipownerCode(model.getShipownerCode());
 82             priceShipownerModel.setLoadPortCode(model.getLoadPortCode());
 83             priceShipownerModel.setDestPortCode(model.getDestPortCode());
 84             priceShipownerModel.setLineCode(model.getLineCode());
 85             List<PriceShipownerModel> list = priceShipownerManager
 86                     .findByExample(priceShipownerModel);
 87 
 88             if (list != null && list.size() > 0) {
 89                 PriceShipownerModel oldModel = list.get(0);
 90                 oldModel.setPrice20gp(model.getPrice20gp());
 91                 oldModel.setPrice40gp(model.getPrice40gp());
 92                 oldModel.setPrice40hq(model.getPrice40hq());
 93                 oldModel.setValidityStart(model.getValidityStart());
 94                 oldModel.setValidityEnd(model.getValidityEnd());
 95                 oldModel.setCurrency(model.getCurrency());
 96                 oldModel.setSailingDec(model.getSailingDec());
 97                 oldModel.setVoyage(model.getVoyage());
 98                 this.dao.save(oldModel);
 99             } else {
100 
101                 model.setCompanyId(userEntity.getCompanyId());
102                 model.setAuditingStatus(PriceConst.AUDITING_STATUS_NO);
103                 model.setIsPublish(PriceConst.PUBLISH_NO);
104                 this.dao.save(model);
105             }
106         }
107         return 1;

JSP:

<form id="priceShipownerForm" class="form-horizontal" action="${pageContext.request.contextPath}/price/priceShipownerList.shtml" method="post" enctype="application/x-www-form-urlencoded">

<div class="btn-group">
　　<input type="button" id="import" class="btn btn-primary" style="width: 110px" value="Excel导入">
</div>

<div class="btn-group">
　　<input type="button" id="export" class="btn btn-primary" style="width: 110px" value="Excel导出">
</div>

</form>

<script type="text/javascript">
$("#import").click(function(){	
　　$("#uploadfile").click();
});

$('#uploadfile').change(function(){
　　selFile();
});
		      
function selFile(){
　　$("#uploadForm").submit();
		 }

$('#export').click(function(){
　　 $('#priceShipownerForm').attr('action',"${pageContext.request.contextPath}/price/priceShipownerList.shtml?action=export");
　　$('#priceShipownerForm').submit(); 	
　　$('#priceShipownerForm').attr('action',"${pageContext.request.contextPath}/price/priceShipownerList.shtml");
		      });
</script>

发表于 2015-01-27 10:08 陈点点阅读(417) 评论(0) 编辑收藏举报

公告