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         }

 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         }

  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 转 LineCode112             BacLineModel bacLineModel = new BacLineModel();113             bacLineModel.setLineCode(priceShipowner.getLineCode());114             List<BacLineModel> Linelist = bacLineManger115                     .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(priceShipowner169                     .getValidityStart()));170             cellContent.setCellStyle(style);171 172             cellContent = rowContent.createCell(12);173             cellContent174                     .setCellValue(df.format(priceShipowner.getValidityEnd()));175             cellContent.setCellStyle(style);176         }177         return workbook;

  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;

<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="http://www.mamicode.com/Excel导入"></div>

<div class="btn-group">　　<input type="button" id="export" class="btn btn-primary" style="width: 110px" value="http://www.mamicode.com/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>