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

最新推荐文章于 2022-01-06 14:46:57 发布

MakeGreatEffort

最新推荐文章于 2022-01-06 14:46:57 发布

阅读量1.1k

点赞数 3

分类专栏： JAVA

本文链接：https://blog.csdn.net/qq_36838191/article/details/80100515

版权

JAVA 专栏收录该内容

280 篇文章 4 订阅

订阅专栏

导出就是将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

 
   [java]  
   view plaincopy
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

 
   [java]  
   view plaincopy
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

 
   [java]  
   view plaincopy
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

 
   [java]  
   view plaincopy
//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-导入

 
   [java]  
   view plaincopy
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-导出

 
   [java]  
   view plaincopy
//导出  
    @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

 
   [html]  
   view plaincopy
<?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

 
   [java]  
   view plaincopy
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

 
   [javascript]  
   view plaincopy
<%@ 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

 
   [javascript]  
   view plaincopy
//导出  
        $(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";  
            }  
        });  