java做 excel文件的导入导出（SSM+layer）

╰+攻爆jí腚メ 2024-04-18 11:47 64阅读 0赞

做的项目使用时 Java ssm + 前端layer+ freemark。  
因为是从项目中扣的代码。整理了下逻辑。 有问题的下方留言哈

导入的依赖 poi

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

# excel的导入 #

导入的excel的格式 （）

![在这里插入图片描述][20190906115429944.png]

## 前端 ##

html

## 后台 ##

Controller

@RequestMapping("/dkInquiry")
    public class DkInquiryController  {
    @RequestMapping(value = "/importExcel")
    	@ResponseBody
    	public 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")
    	@Override
    	public Map<String, String> saveImportExcel(MultipartFile file) {
    		Map<String, String> map = new HashMap<String, String>();
    		//判断上传的文件 格式
    		if (file.getOriginalFilename().endsWith(".xlsx")) {
    			try {
    				// 创建一个XSSFWorkbook对象工作簿
    				XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
    				// 获取到sheet对象
    				XSSFSheet sheet = wb.getSheetAt(0);
    					//限制行数
    				int s = sheet.getPhysicalNumberOfRows();
    				if(s>201){
    					String msg = "上传失败，您上传的文件数据多于200行";
    					map.put("msg", msg);
    					return map;
    				}
    				//行对象
    				XSSFRow row;
    				//单元格对象
    				XSSFCell cell;
    				//第一行是标题，第二行就是数据 
    				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);
    					//判断第二行的 每个单元格 是否是 字符串或者是数
    					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();
    				//取第二行的数据 公司信息  
    				DkInquiry dkinq = util.getDkInquiry(row);
    				List<DkInquiryDetails> details = new ArrayList<DkInquiryDetails>();
    //				boolean p = false;
    //getPhysicalNumberOfRows()获取的是物理行数，也就是不包括那些空行（隔行）的情况。
    				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;
    							}
    						}
    					}
    					//读取数据 封装成对象
    					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 block
    				e.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 {
    //将表格数据封装成对象
    	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;
    	}
    	
    	/**
    	 * 
    	 * @描述：询价单详情相关
    	 * @创建时间：2018年6月22日
    	 * @param row
    	 * @return
    	 */
    	public static InquirySheetDetails getInquirySheetDetails(int rowId, XSSFRow row){
    		String goodsName = StringUtils.toString(row.getCell(1)); // 产品名称
    		String goodsSpecifications = StringUtils.toString(row.getCell(2)); // 产品型号
    		XSSFCell cell = row.getCell(3); // 产品数量
    		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)); // 产品单位
    		String gcName = StringUtils.toString(row.getCell(5)); // 产品分类
    		String goodsRemark = StringUtils.toString(row.getCell(6)); //备注
    		rowId = rowId + 1;
    		if (StringUtils.isBlank(goodsName)) {
    			return null;
    		}
    		if (StringUtils.isBlank(goodsSpecifications)) {
    			throw new BussinessException("上传失败，您的文件在第"+rowId+"行，产品型号为空");
    		}
    		if (StringUtils.isBlank(amount)) {
    			throw new BussinessException("上传失败，您的文件在第"+rowId+"行，产品数量为空");
    		}
    		if (StringUtils.isBlank(goodsUnit)) {
    			throw new BussinessException("上传失败，您的文件在第"+rowId+"行，产品单位为空");
    		}
    		if (StringUtils.isBlank(gcName)) {
    			throw new BussinessException("上传失败，您的文件在第"+rowId+"行，产品分类为空");
    		}
    		if (gcName.indexOf("x") > -1 || amount.indexOf("x") > -1 || goodsUnit.indexOf("x") > -1) {
    			throw new BussinessException("上传失败，请上传真实询价单数据！");
    		}
    		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的导出 #

## 前台 ##

后台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);
    		// 提示：在调用工具类生成Word文档之前应当检查所有字段是否完整
    		// 否则Freemarker的模板殷勤在处理时可能会因为找不到值而报错 这里暂时忽略这个步骤了
    		File file = null;
    		InputStream inputStream = null;
    		ServletOutputStream out = null;
    		try {
    			req.setCharacterEncoding("UTF-8");
    			//创建一个excel
    			file = ExcelUtils.createExcel(map, "resume");// 适用于office
    			inputStream = 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]; // 缓冲区
    			int bytesToRead = -1;
    			// 通过循环将读入的Excel文件的内容输出到浏览器中
    			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 block
    				e.printStackTrace();
    			}
    
    		}
    		return file;
    	}
    }

[20190906115429944.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/23e1f3e85875475b9170efd2c6ffd071.png