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

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

导入的依赖 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的导入

导入的excel的格式 （）

前端

html

<a href="javascript:void(0)" onclick="ajaxFileUploads()" class="btn">导入</a></td>

js

 function ajaxFileUploads(){
	    $.ajaxFileUpload({
	        //处理文件上传操作的服务器端地址(可以传参数,已亲测可用)
	        url: '${base}/dkInquiry/importExcel',
	        type: 'post',
	        secureuri:false,                      //是否启用安全提交,默认为false
	        fileElementId:'file',          		 //文本上传域的id
	        dataType:'json',                       //服务器返回的格式,可以是json或xml等
	        success:function(data, status){        //服务器响应成功时的处理函数
	        	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){ //服务器响应失败时的处理函数
	             layer.alert('数据上传失败，请联系研发部！！' , {icon: 2});
	        }
	    });
	   
	}

后台

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的导出

前台

 <a href="${base}/dkInquiry/exportExcel?id=${dkInquiry.id}">导出</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);
		// 提示：在调用工具类生成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;
	}
}