做的項目使用時 Java ssm + 前端layer+ freemark。
因為是從項目中扣的代碼。整理了下邏輯。 有問題的下方留言哈

導(dǎo)入的依賴 poi

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version> </dependency>

excel的導(dǎo)入

導(dǎo)入的excel的格式 （）

前端

html

<a href="javascript:void(0)" onclick="ajaxFileUploads()" class="btn">導(dǎo)入</a></td>

js

function ajaxFileUploads(){$.ajaxFileUpload({//處理文件上傳操作的服務(wù)器端地址(可以傳參數(shù),已親測可用)url: '${base}/dkInquiry/importExcel',type: 'post',secureuri:false, //是否啟用安全提交,默認為falsefileElementId:'file', //文本上傳域的iddataType:'json', //服務(wù)器返回的格式,可以是json或xml等success:function(data, status){ //服務(wù)器響應(yīng)成功時的處理函數(shù)var msg = data.msg;var yes = data.yes;if(msg!=""&&msg!=null){layer.alert(msg, {icon: 2});}else{layer.alert(yes, {icon: 1}, function(index){window.location.href='${base}/dkInquiry/list';layer.close(index);}); }},error:function(data, status, e){ //服務(wù)器響應(yīng)失敗時的處理函數(shù)layer.alert('數(shù)據(jù)上傳失敗，請聯(lián)系研發(fā)部！！' , {icon: 2});}});}

后臺

Controller

@RequestMapping("/dkInquiry") public class DkInquiryController { @RequestMapping(value = "/importExcel")@ResponseBodypublic String importExcel(@RequestParam MultipartFile file,HttpServletRequest request, HttpServletResponse response)throws IOException {Map<String, String> map = dkInquiryService.saveImportExcel(file);JSONObject jsonObject = JSONObject.fromObject(map);String result = jsonObject.toString();return result;} }

Server

@SuppressWarnings("resource")@Overridepublic Map<String, String> saveImportExcel(MultipartFile file) {Map<String, String> map = new HashMap<String, String>();//判斷上傳的文件 格式if (file.getOriginalFilename().endsWith(".xlsx")) {try {// 創(chuàng)建一個XSSFWorkbook對象工作簿XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());// 獲取到sheet對象XSSFSheet sheet = wb.getSheetAt(0);//限制行數(shù)int s = sheet.getPhysicalNumberOfRows();if(s>201){String msg = "上傳失敗，您上傳的文件數(shù)據(jù)多于200行";map.put("msg", msg);return map;}//行對象XSSFRow row;//單元格對象XSSFCell cell;//第一行是標題，第二行就是數(shù)據(jù) row = sheet.getRow(1);//判斷文件是否為空 if (row == null) {String msg = "上傳失敗，您的文件第2行為空";map.put("msg", msg);return map;}// 概念: 一行里面 多個單元格//取到第二行以后，判斷值是否正確for (int i = 0; i < 5; i++) {cell = row.getCell(i);//判斷第二行的 每個單元格 是否是 字符串或者是數(shù)if (cell == null|| (cell.getCellType() != XSSFCell.CELL_TYPE_STRING && cell.getCellType() != XSSFCell.CELL_TYPE_NUMERIC)) {String msg = "上傳失敗，您的文件在第" + 2 + "行，第" + (i + 1)+ "列有問題，請查看";map.put("msg", msg);return map;}}ImportExcelUtil util = new ImportExcelUtil();//取第二行的數(shù)據(jù) 公司信息 DkInquiry dkinq = util.getDkInquiry(row);List<DkInquiryDetails> details = new ArrayList<DkInquiryDetails>(); // boolean p = false; //getPhysicalNumberOfRows()獲取的是物理行數(shù)，也就是不包括那些空行（隔行）的情況。System.out.println(sheet.getPhysicalNumberOfRows());for (int j = 3; j < sheet.getPhysicalNumberOfRows(); j++) {row = sheet.getRow(j);if (row != null) {/** String msg = "上傳失敗，您的文件第"+(j+1)+"行為空"; map.put("msg",* msg); return map;*/for (int k = 0; k < 6; k++) {cell = row.getCell(k);System.out.println(cell);}System.out.println("-----");}for (int k = 0; k < 6; k++) {cell = row.getCell(k);if (k == 3) {if (cell == null|| cell.getCellType() != XSSFCell.CELL_TYPE_NUMERIC) {String msg = "上傳失敗，您的文件在第" + (j + 1)+ "行，第4列有問題，請查看";map.put("msg", msg);return map;}} else if(k==0 || k==1 || k==4 ){if (cell == null|| cell.getCellType() != XSSFCell.CELL_TYPE_STRING) {String msg = "上傳失敗，您的文件在第" + (j + 1) + "行，第"+ (k + 1) + "列有問題，請查看";System.out.println(cell);System.out.println(row);map.put("msg", msg);return map;}}}//讀取數(shù)據(jù) 封裝成對象DkInquiryDetails datail = util.getDkInquiryDetail(row);details.add(datail);}dkinq.setDetails(details);dkinq.setCompany("DK");dkinq.setCreateTime(System.currentTimeMillis());dkInquiryDao.saveDkInquiry(dkinq);//入庫for (DkInquiryDetails detail : dkinq.getDetails()) {detail.setPrId(dkinq.getId());if (StringUtils.isBlank(detail.getName())) {continue;} else {dkInquiryDetailsDao.saveDkInquiryDetails(detail);}}} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}map.put("yes", "上傳成功");} else {map.put("msg", "上傳失敗，請選擇格式為.xlsx的文件進行上傳");}return map;}

ImportExcelUtil工具類 里面一些類是 公司項目的 自定義類，不方便哈

package com.leimingtech.core.common;import java.math.BigDecimal; import java.util.HashMap; import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDataFormatter; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow;public class ImportExcelUtil { //將表格數(shù)據(jù)封裝成對象public DkInquiry getDkInquiry(XSSFRow row){String inqTitle = row.getCell(0).toString();String companyName = row.getCell(1).toString();String realname = row.getCell(2).toString();XSSFCell cell = row.getCell(3);String phone ="";switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:phone=cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_FORMULA:phone=cell.getCellFormula();break;case HSSFCell.CELL_TYPE_NUMERIC:HSSFDataFormatter dataFormatter = new HSSFDataFormatter();phone=dataFormatter.formatCellValue(cell); break;case HSSFCell.CELL_TYPE_ERROR:phone="";break;}String remark = row.getCell(4).toString();DkInquiry dkinq = new DkInquiry();dkinq.setInqTitle(inqTitle);dkinq.setCompanyName(companyName);dkinq.setRealname(realname);dkinq.setPhone(phone);dkinq.setRemark(remark);return dkinq;}public DkInquiryDetails getDkInquiryDetail(XSSFRow row){String name = row.getCell(0).toString();String gcName = row.getCell(1).toString();String specifications = row.getCell(2).toString();XSSFCell cell = row.getCell(3);String a = "";switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:a=cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_FORMULA:a=cell.getCellFormula();break;case HSSFCell.CELL_TYPE_NUMERIC:HSSFDataFormatter dataFormatter = new HSSFDataFormatter();a=dataFormatter.formatCellValue(cell); break;case HSSFCell.CELL_TYPE_ERROR:a="";break;}BigDecimal amount = new BigDecimal(a);amount.setScale(2,BigDecimal.ROUND_HALF_UP );String unit = row.getCell(4).toString();String remark = row.getCell(5).toString();String gcId = getGcId(gcName);DkInquiryDetails datail = new DkInquiryDetails();datail.setName(name);datail.setGcName(gcName);datail.setSpecifications(specifications);datail.setAmount(amount);datail.setUnit(unit);datail.setRemark(remark);datail.setGcId(gcId);return datail;}/*** * @描述：詢價單詳情相關(guān)* @創(chuàng)建時間：2018年6月22日* @param row* @return*/public static InquirySheetDetails getInquirySheetDetails(int rowId, XSSFRow row){String goodsName = StringUtils.toString(row.getCell(1)); // 產(chǎn)品名稱String goodsSpecifications = StringUtils.toString(row.getCell(2)); // 產(chǎn)品型號XSSFCell cell = row.getCell(3); // 產(chǎn)品數(shù)量String amount = "";switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_STRING:amount=cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_FORMULA:amount=cell.getCellFormula();break;case HSSFCell.CELL_TYPE_NUMERIC:HSSFDataFormatter dataFormatter = new HSSFDataFormatter();amount=dataFormatter.formatCellValue(cell); break;case HSSFCell.CELL_TYPE_ERROR:amount="";break;}String goodsUnit = StringUtils.toString(row.getCell(4)); // 產(chǎn)品單位String gcName = StringUtils.toString(row.getCell(5)); // 產(chǎn)品分類String goodsRemark = StringUtils.toString(row.getCell(6)); //備注rowId = rowId + 1;if (StringUtils.isBlank(goodsName)) {return null;}if (StringUtils.isBlank(goodsSpecifications)) {throw new BussinessException("上傳失敗，您的文件在第"+rowId+"行，產(chǎn)品型號為空");}if (StringUtils.isBlank(amount)) {throw new BussinessException("上傳失敗，您的文件在第"+rowId+"行，產(chǎn)品數(shù)量為空");}if (StringUtils.isBlank(goodsUnit)) {throw new BussinessException("上傳失敗，您的文件在第"+rowId+"行，產(chǎn)品單位為空");}if (StringUtils.isBlank(gcName)) {throw new BussinessException("上傳失敗，您的文件在第"+rowId+"行，產(chǎn)品分類為空");}if (gcName.indexOf("x") > -1 || amount.indexOf("x") > -1 || goodsUnit.indexOf("x") > -1) {throw new BussinessException("上傳失敗，請上傳真實詢價單數(shù)據(jù)！");}BigDecimal goodsAmount = new BigDecimal(amount);goodsAmount.setScale(2,BigDecimal.ROUND_HALF_UP );InquirySheetDetails inquirySheetDetails = new InquirySheetDetails();inquirySheetDetails.setGoodsName(goodsName);inquirySheetDetails.setGoodsSpecifications(goodsSpecifications);inquirySheetDetails.setGoodsAmount(goodsAmount);inquirySheetDetails.setGoodsUnit(goodsUnit);inquirySheetDetails.setGcName(gcName);String gcId = getGcId(gcName);if(!StringUtils.isBlank(gcId) && !"null".equals(gcId)){inquirySheetDetails.setGcId(gcId);}inquirySheetDetails.setGoodsRemark(goodsRemark);return inquirySheetDetails;}public static String getGcId(String gcName){Map<String,String> map = new HashMap<String,String>();map.put("1","49f6ce570e5445a585dfb6546fa3b0c2");.。。。。此出省略，公司的東西return map.get(gcName);}}

excel的導(dǎo)出

前臺

<a href="${base}/dkInquiry/exportExcel?id=${dkInquiry.id}">導(dǎo)出</a>

后臺Controller

@RequestMapping("/exportExcel")public void exportExcel(HttpServletRequest req,HttpServletResponse resp,@RequestParam(required = false, value = "id", defaultValue = "0") String id)throws ServletException, IOException {Map<String, Object> map = new HashMap<String, Object>();DkInquiry dkInquiry = dkInquiryService.findDkInquiryBaseInfoById(id);Pager pager = new Pager();pager.setPageSize(-1);DkQuotes condition = new DkQuotes();condition.setIsExport(1);condition.setPrId(dkInquiry.getId());pager.setCondition(condition);List<DkQuotes> dkQuotesList = dkQuotesService.findDkQuotesAndGoodsDetailsPagerList(pager);SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");String time = sdf.format(new Date());int num = dkQuotesList.size();map.put("dkInquiry", dkInquiry);map.put("dkQuotesList", dkQuotesList);map.put("time", time);map.put("num", num);System.out.println(dkQuotesList);// 提示：在調(diào)用工具類生成Word文檔之前應(yīng)當檢查所有字段是否完整// 否則Freemarker的模板殷勤在處理時可能會因為找不到值而報錯 這里暫時忽略這個步驟了File file = null;InputStream inputStream = null;ServletOutputStream out = null;try {req.setCharacterEncoding("UTF-8");//創(chuàng)建一個excelfile = ExcelUtils.createExcel(map, "resume");// 適用于officeinputStream = new FileInputStream(file);resp.setCharacterEncoding("utf-8");resp.setContentType("application/msexcel");resp.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode("物資報價單" + ".xls", "UTF-8"));out = resp.getOutputStream();byte[] buffer = new byte[512]; // 緩沖區(qū)int bytesToRead = -1;// 通過循環(huán)將讀入的Excel文件的內(nèi)容輸出到瀏覽器中while ((bytesToRead = inputStream.read(buffer)) != -1) {out.write(buffer, 0, bytesToRead);}out.flush();} catch (Exception e) {e.printStackTrace();} finally {if (inputStream != null)inputStream.close();if (out != null)out.close();if (file != null)file.delete(); // 刪除臨時文件}}

工具類 ExcelUtils

import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStreamWriter; import java.io.Writer; import java.util.HashMap; import java.util.Map;import freemarker.template.Configuration; import freemarker.template.Template;public class ExcelUtils {private static Configuration configuration = null;private static Map<String, Template> allTemplates = null;public ExcelUtils() {throw new AssertionError();}public static File createExcel(Map<?, ?> dataMap, String type) {try {configuration = new Configuration();configuration.setDefaultEncoding("UTF-8");configuration.setClassForTemplateLoading(ExcelUtils.class,"/template/contract");allTemplates = new HashMap<String, Template>();allTemplates.put("resume", configuration.getTemplate("export.ftl"));} catch (IOException ex) {ex.printStackTrace();throw new RuntimeException(ex);}String name = "temp" + (int) (Math.random() * 100000) + ".xls";File file = new File(name);Template template = allTemplates.get(type);Writer w = null;try {w = new OutputStreamWriter(new FileOutputStream(file), "utf-8");template.process(dataMap, w);w.close();} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {try {w.flush();w.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}return file;} }

總結(jié)

以上是生活随笔為你收集整理的java做 excel文件的 导入导出 （SSM+layer）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。