java Excel 导入导出
浏览数：32 /
时间：2015年06月08日
使用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>
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
