在SSM下使用POI实现Excel表的导入/导出

最新推荐文章于 2024-09-03 20:52:56 发布

nice_fly

最新推荐文章于 2024-09-03 20:52:56 发布

阅读量9.5k

点赞数 15

分类专栏： Excel导入导出文章标签： java poi excel

本文链接：https://blog.csdn.net/nice_fly/article/details/78644630

版权

Excel导入导出专栏收录该内容

1 篇文章 0 订阅

订阅专栏

导出就是将List转化为Excel（listToExcel）

导入就是将Excel转化为List（excelToList）

POI

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

我们知道要创建一张excel你得知道excel由什么组成，比如说sheet也就是一个工作表格，例如一行，一个单元格，单元格格式，单元格内容格式…这些都对应着poi里面的一个类。

一个excel表格：

HSSFWorkbook wb = new HSSFWorkbook();

一个工作表格（sheet）：

HSSFSheet sheet = wb.createSheet("测试表格");

一行（row）：

HSSFRow row1 = sheet.createRow(0);

一个单元格（cell）：

HSSFCell cell2 = row2.createCell((short)0)

单元格格式（cellstyle）：

HSSFCellStyle style4 = wb.createCellStyle()

单元格内容格式（）

HSSFDataFormat format= wb.createDataFormat();

知道上面的基本知识后下面学起来就轻松了

poi批量导入导出的jar包

http://pan.baidu.com/s/1o7CsH78

poi-ooxml-3.13.jar

poi-ooxml-schemas-3.13.jar

poi-3.13.jar

xmlbeans-2.6.0.jar

fastjson-1.2.2-sources.jar

fastjson-1.2.2.jar

创建一个Excel表数据导入和导出的工具类ExcelUtil

http://pan.baidu.com/s/1c2vYsog

package com.city.pms.common.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class ExcellUtil{
	
    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN="yyyy-MM-dd";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;
    
    private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  
    /** 
     * Excel导入
     */  
    public static  List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception{  
        List<List<Object>> list = null;  
        //创建Excel工作薄  
        Workbook work = getWorkbook(in,fileName);  
        if(null == work){  
            throw new Exception("创建Excel工作薄为空！");  
        }  
        Sheet sheet = null;  
        Row row = null;  
        Cell cell = null;  
        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  
            //遍历当前sheet中的所有行  
            //包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部  
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
                //读取一行  
                row = sheet.getRow(j);  
                //去掉空行和表头  
                if(row==null||row.getFirstCellNum()==j){continue;}  
                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                    cell = row.getCell(y);  
                    li.add(getCellValue(cell));  
                }  
                list.add(li);  
            }  
        }  
        return list;  
    }  
    /** 
     * 描述：根据文件后缀，自适应上传文件的版本 
     */  
    public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  
    /** 
     * 描述：对表格中数值进行格式化 
     */  
    public static  Object getCellValue(Cell cell){  
        Object value = null;  
        DecimalFormat df = new DecimalFormat("0");  //格式化字符类型的数字  
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化  
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字  
        switch (cell.getCellType()) {  
            case Cell.CELL_TYPE_STRING:  
                value = cell.getRichStringCellValue().getString();  
                break;  
            case Cell.CELL_TYPE_NUMERIC:  
                if("General".equals(cell.getCellStyle().getDataFormatString())){  
                    value = df.format(cell.getNumericCellValue());  
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){  
                    value = sdf.format(cell.getDateCellValue());  
                }else{  
                    value = df2.format(cell.getNumericCellValue());  
                }  
                break;  
            case Cell.CELL_TYPE_BOOLEAN:  
                value = cell.getBooleanCellValue();  
                break;  
            case Cell.CELL_TYPE_BLANK:  
                value = "";  
                break;  
            default:  
                break;  
        }  
        return value;  
    }  
    
    
    /**
     * 导出Excel 97(.xls)格式 ，少量数据
     * @param title 标题行 
     * @param headMap 属性-列名
     * @param jsonArray 数据集
     * @param datePattern 日期格式，null则用默认日期格式
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
	public static void exportExcel(Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.createInformationProperties();
        workbook.getDocumentSummaryInformation().setCompany("*****公司");
        
         //表头样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        
        // 列头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        
        // 单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        
        // 生成一个(带标题)表格
        HSSFSheet sheet = workbook.createSheet();
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("JACK");
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext();) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = fieldName;

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if(rowIndex == 65535 || rowIndex == 0){
                if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                HSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =0
                for(int i=0;i<headers.length;i++)
                {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 1;//数据内容从 rowIndex=1开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            HSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                HSSFCell newCell = dataRow.createCell(i);

                Object o =  jo.get(properties[i]);
                String cellValue = ""; 
                if(o==null) cellValue = "";
                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
	
    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param title 标题行
     * @param headMap 属性-列头
     * @param jsonArray 数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
    public static void exportExcelX(Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        
         //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext();) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if(rowIndex == 65535 || rowIndex == 0){
                if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                SXSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =0
                for(int i=0;i<headers.length;i++)
                {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 1;//数据内容从 rowIndex=1开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                SXSSFCell newCell = dataRow.createCell(i);

                Object o =  jo.get(properties[i]);
                String cellValue = ""; 
                if(o==null) cellValue = "";
                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else if(o instanceof Float || o instanceof Double) 
                    cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    
    
    //Web 导出excel
    public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcellUtil.exportExcelX(headMap,ja,null,0,os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); 
            response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

导出的实体类

http://pan.baidu.com/s/1c2EVh2w

package com.city.pms.entity.project.design.designDTO;

import java.util.Date;
/**
 * 
 * @Description: 导出的实体类
 * @CreateTime: 2017-11-20 上午9:47:56 
 * @author: zhuhongfei 
 * @version V1.0
 */
public class ProjectDesignDTO {

	private String projectName;
	private String name;
	private String applicantUnit;
	private String reviewUnit;
	private String status;
	private String createName;
	private Date createDate;
	
	
	public String getProjectName() {
		return projectName;
	}
	public void setProjectName(String projectName) {
		this.projectName = projectName;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getApplicantUnit() {
		return applicantUnit;
	}
	public void setApplicantUnit(String applicantUnit) {
		this.applicantUnit = applicantUnit;
	}
	public String getReviewUnit() {
		return reviewUnit;
	}
	public void setReviewUnit(String reviewUnit) {
		this.reviewUnit = reviewUnit;
	}
	public String getStatus() {
		return status;
	}
	public void setStatus(String status) {
		this.status = status;
	}
	
	public String getCreateName() {
		return createName;
	}
	public void setCreateName(String createName) {
		this.createName = createName;
	}
	public Date getCreateDate() {
		return createDate;
	}
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	
}

导入的实体类

http://pan.baidu.com/s/1i5pc6hf

package com.city.pms.entity.project.design.designA;

import java.io.Serializable;
import java.util.Date;

/**
 * 
 * @Description: 导入实体类
 * @CreateTime: 2017-11-16 上午9:23:21 
 * @author: chenzw  
 * @version V1.0
 */

public class DesignA implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1749623906718021284L;
	
	private Double id;
	private Integer eid;//整数
	private Integer safe;//安全值
	private Double prewarning;//预警值
	private Double alarm;//报警值
	private Double voerflow;//溢流值
	private Date createDate;//创建时间
	
	public Double getId() {
		return id;
	}
	public void setId(Double id) {
		this.id = id;
	}
	public Integer getEid() {
		return eid;
	}
	public void setEid(Integer eid) {
		this.eid = eid;
	}
	public Integer getSafe() {
		return safe;
	}
	public void setSafe(Integer safe) {
		this.safe = safe;
	}
	public Double getPrewarning() {
		return prewarning;
	}
	public void setPrewarning(Double prewarning) {
		this.prewarning = prewarning;
	}
	public Double getAlarm() {
		return alarm;
	}
	public void setAlarm(Double alarm) {
		this.alarm = alarm;
	}
	public Double getVoerflow() {
		return voerflow;
	}
	public void setVoerflow(Double voerflow) {
		this.voerflow = voerflow;
	}
	public Date getCreateDate() {
		return createDate;
	}
	public void setCreateDate(Date createDate) {
		this.createDate = createDate;
	}
	
}

整体架构（dao,service,.xml,Controller）

http://pan.baidu.com/s/1hsF2Zha

//Dao层
//获取excel数据并插入数据库
public void insertDatasFromExcel(List<DesignA> designA);
//service层
//从excel导入到designA
public String insertDatasFromExcel(InputStream in,String fileName,Map<String, String> headMap);
//获取显示列表
public List<ProjectDesign> getAllProjectDesign();
//导出接口
public List<ProjectDesignDTO> getAllProjectDesignDTO();

一、ServiceImpl-导入

package com.city.pms.service.project.design.designA;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.city.pms.common.utils.ImportUtil2;
import com.city.pms.entity.project.design.designA.DesignA;
import com.city.pms.repository.project.design.designA.DesignADao;
@Service
public class DesignAServiceImpl implements DesignAService {

	@Autowired  
	private DesignADao designADao;
	
	//
	@Override
	public DesignA save(String id) {
		// TODO Auto-generated method stub
		return designADao.save(id);
	}
	//导入
	@Override
	public String insertDatasFromExcel(InputStream in, String fileName,Map<String, String> headMap) {
		try {
			//首先调用工具类，读取excel文件，封装称实体集合
			List<List<Object>> listob = ImportUtil2.getListByExcel(in, fileName);
			//获取动态数组  动态的增加和减少元素  实现了ICollection和IList接口  灵活的设置数组的大小
			List<DesignA> designA = new ArrayList<DesignA>(); 
			//读取第一列，列头信息，第一行
			List<Object> list = listob.get(0);
			Iterator<String> keys = headMap.keySet().iterator();
			//LinkedHashMap保留插入的顺序（key,value）
			Map<String,Integer> newMap=new LinkedHashMap<String,Integer>();
			while(keys.hasNext()){
				String key = keys.next();
				String name = headMap.get(key);
				boolean flag=true;
				for (int i = 0; i < list.size(); i++) {
					if(StringUtils.contains(name, list.get(i).toString())){
						newMap.put(key,i);
						flag=false;
						break;
					}
				}
				if(flag==true){
					throw new Exception("Excel表格参数错误，缺少"+name);
				}
			}
		    //遍历listob数据，把数据放到List中  从第二行开始
		    for (int i = 1; i < listob.size(); i++) {  
		        List<Object> ob = listob.get(i); 
		        DesignA design = new DesignA();  
		        design.setEid(Integer.parseInt(ob.get(newMap.get("eid")).toString()));
		        design.setSafe(Integer.parseInt(ob.get(newMap.get("safe")).toString()));
		        //object类型转Double类型  
		        design.setPrewarning(Double.parseDouble(ob.get(newMap.get("prewarning")).toString()));
		        design.setAlarm(Double.parseDouble(ob.get(newMap.get("alarm")).toString()));
		        design.setVoerflow(Double.parseDouble(ob.get(newMap.get("voerflow")).toString()));
		        Date parse = new SimpleDateFormat("yyyy-MM-dd").parse(ob.get(newMap.get("createDate")).toString());
		        design.setCreateDate(parse);
		        //Add方法用于添加一个元素到当前列表的末尾
		        designA.add(design);
		    }  
		    //批量插入
		    designADao.insertDatasFromExcel(designA);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	

}

ServiceImpl-导出

//导出
	@Override
	public List<ProjectDesignDTO> getAllProjectDesignDTO() {
		//获取动态数组  动态的增加和减少元素  实现了ICollection和IList接口  灵活的设置数组的大小
		List<ProjectDesignDTO> projectDesignList=new ArrayList<>();
		//获取列表
		List<ProjectDesign> allProjectDesign= projectDesignDao.getAllProjectDesign();
		//遍历projectDesign获取真值
		 for (ProjectDesign projectDesign : allProjectDesign) {
			 //需要导出的实体类
			ProjectDesignDTO projectDesignDTO=new ProjectDesignDTO();
			projectDesignDTO.setProjectName(projectDesign.getProjectinfoId().getName());
			projectDesignDTO.setName(projectDesign.getName());
			projectDesignDTO.setApplicantUnit(projectDesign.getApplicantUnit());
			projectDesignDTO.setReviewUnit(projectDesign.getReviewUnit());
			projectDesignDTO.setStatus(projectDesign.getStatus());
			projectDesignDTO.setCreateName(projectDesign.getCreator().getCnname());
			projectDesignDTO.setCreateDate(projectDesign.getCreateDate());
			//Add方法用于添加一个元素到当前列表的末尾
			projectDesignList.add(projectDesignDTO);
		}
		 return projectDesignList;
	}

一、导入-xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.city.pms.repository.project.design.designA.DesignADao">
	<resultMap type="DesignA" id="DesignAMap">
		<id property="id" column="id"/>
		<id property="eid" column="eid"/>
		<id property="safe" column="safe"/>
		<id property="prewarning" column="prewarning"/>
		<id property="alarm" column="alarm"/>
		<id property="voerflow" column="voerflow"/>
		<id property="createDate" column="createDate"/>
	</resultMap>
	
	<!-- 批量插入 -->
	<insert id="insertDatasFromExcel" parameterType="java.util.List">
<!-- 		<selectKey resultType ="java.lang.Integer" keyProperty= "ID" order= 
			"AFTER" > SELECT LAST_INSERT_ID() </selectKey > -->
		insert into WARNING 
		(ID,EID,SAFE,PREWARNING,ALARM,OVERFLOW,CREATEDATE)
		SELECT WARNING_SEQUENCE.Nextval ID, A.*
		FROM(
		<foreach collection="list" item="item" index="index"
			separator="union all">
			SELECT
			#{item.eid},
	        #{item.safe},
	        #{item.prewarning},
	        #{item.alarm},
	        #{item.voerflow},
	        #{item.createDate}
				FROM
				DUAL
		</foreach>
		) A
	</insert>
</mapper>

一、Controller

package com.city.pms.controller.project.design.designA;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.city.pms.service.project.design.designA.DesignAService;

/**
 * 
 * @Description: TODO 
 * @CreateTime: 2017-11-16 上午10:39:06 
 * @author: zhuhongfei 
 * @version V1.0
 */
@Controller
@RequestMapping("/designa")
public class DesignAController {

	@Autowired
	private DesignAService designAService;
	
	@RequestMapping(value= "toExcel",method = RequestMethod.GET)
	public String toExcel(){
		return "Excel";
	}
	
	//导入
	@RequestMapping(value= "importExcel",method = RequestMethod.GET)
	public String importExcel(HttpServletRequest request, Model model){
		
		try {
		    File f=new File("E:\\嘉兴.xlsx");
			String fileName = "嘉兴.xlsx";
			Map<String, String> headMap=new LinkedHashMap<String, String>();
			headMap.put("eid", "设备编号");
			headMap.put("safe", "安全值");
			headMap.put("prewarning", "预警值");
			headMap.put("alarm", "报警值");
			headMap.put("voerflow", "溢流值");
			headMap.put("createDate", "创建时间");
			//数据导入
			InputStream in2=new FileInputStream(f);
		    designAService.insertDatasFromExcel(in2, fileName, headMap);
		    in2.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return "Excel";
	}
	
	@RequestMapping(value = "/{id}/detail")
	public String detail(@PathVariable String id, Model model) {
		model.addAttribute("d",designAService.save(id));
		return "";
	}
}
	


/**
	 * 数据库数据导入excel(导出)
	 * 
	 * @param response
	 */
	@RequestMapping(value = "exportExcel",method = RequestMethod.GET)
	public void exportExcel(HttpServletResponse response){
			//读取数据库
	        List<ProjectDesignDTO> allProjectDesign = projectDesignService.getAllProjectDesignDTO();
	        //后台返回给前台时，可以把JSON对象转化成JSON字符串
	        com.alibaba.fastjson.JSONArray ja= (com.alibaba.fastjson.JSONArray) com.alibaba.fastjson.JSONArray.toJSON(allProjectDesign);
	        //LinkedHashMap保留插入的顺序（key,value）
	        Map<String,String> headMap = new LinkedHashMap<String,String>();
	        headMap.put("projectName","项目名称");
	        headMap.put("name","方案名称");
	        headMap.put("applicantUnit","申请单位");
	        headMap.put("reviewUnit","评审单位");
	        headMap.put("status","状态");
	        headMap.put("createName","创建人");
	        headMap.put("createDate","创建时间");
	      String title = "工程设计方案 ";
	     //调用工具类导出方法
	     ExcellUtil.downloadExcelFile(title,headMap,ja,response);
	}

一、Jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=basePath%>">
    <title>嘉兴市海绵城市规划建设运营平台</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<link href="css/css2.css" rel="stylesheet" type="text/css" />
	<script type="text/javascript"src="jquery/jquery-3.2.0.min.js"></script>
<style type="text/css">
body{text-align:center}
.ceshi1{margin:0 auto; width:750px;height:100px;}
</style>
  </head>
  
  <body>
    <div class="warpper">
	    <div class="top-warpper">
	        <div class="logo-userInfo">
	            <h1 class="logo"><a href="#" >嘉兴市海绵城市规划建设运营平台</a></h1>
	            </div><br><br>
			<div class="ceshi1">
				<button class="src-derive derive" id="derive">导出</button>
				  
				<!-- <tr>
					<button class="src-derive leadin1" id="leadin" type="file" name="userUploadFile">导入</button> 
                    <td><input type="file" name="userUploadFile"></td>
				</tr> -->
				<form action="/designa/importExcel.do" method="post" enctype="multipart/form-data" name="batchAdd" οnsubmit="return check();">
			        <div class="col-lg-4">
			            <input id="excel_file" type="file" name="filename" accept="xls/xlsx" size="50"/>
			        </div>
			        <input id="excel_button" class="src-derive leadin1" type="submit" value="导入Excel"/>
			       <%--  <font id="importMsg" color="red"><%=importMsg%></font><input type="hidden"/> --%>  
			    </form>
			</div>
			
<script type="text/javascript">
 var root="<%=basePath%>"
</script>
<script type="text/javascript"src="js/excel1.js"></script>
<script type="text/javascript"src="js/excel2.js"></script>

</html>

一、Js

//导出
  		$(document).on("mousedown",".derive",function(){
 			var id=$(this).attr("fileId");
 			window.location.href=root+"/design/exportExcel.do";
 		});
  		//导入
  		$(document).on("mousedown",".leadin1",function(){
  			if(confirm("确定导入？")){
 			var id=$(this).attr("fileId");
 			window.location.href=root+"/designa/importExcel.do";
  			}
 		});