package org.imooc.dto;

import org.apache.commons.fileupload.FileItem;

public class ImportExcelParamDto {
    private String title;
    private FileItem excel;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public FileItem getExcel() {
        return excel;
    }

    public void setExcel(FileItem excel) {
        this.excel = excel;
    }
}

package org.imooc.dto;

import org.imooc.entity.Student;

import java.util.List;

public class ImportExcelResultDto {
    private String title;
    private List<Student> studentList;
    private String msg;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}

package org.imooc.dto;

import org.apache.commons.fileupload.FileItem;

import java.util.HashMap;
import java.util.Map;

public class ParamDto {
    private Map<String,String> paramMap;
    private Map<String,FileItem> fileMap;

    public ParamDto(){
        paramMap = new HashMap<String,String>();
        fileMap = new HashMap<String, FileItem>();
    }

    public Map<String, String> getParamMap() {
        return paramMap;
    }

    public void setParamMap(Map<String, String> paramMap) {
        this.paramMap = paramMap;
    }

    public Map<String, FileItem> getFileMap() {
        return fileMap;
    }

    public void setFileMap(Map<String, FileItem> fileMap) {
        this.fileMap = fileMap;
    }
}

package org.imooc.entity;

import java.util.Date;

public class Student {
     private String name;
     private Integer age;
     private Date time;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getTime() {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }
}

package org.imooc.service;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.imooc.dto.ImportExcelParamDto;
import org.imooc.dto.ImportExcelResultDto;
import org.imooc.entity.Student;
import org.imooc.util.FileUtil;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelService {
    public ImportExcelResultDto imp(ImportExcelParamDto dto){
        ImportExcelResultDto result = new ImportExcelResultDto();
        result.setTitle(dto.getTitle());
        List<Student> studentList = new ArrayList<>();
        result.setStudentList(studentList);
        String fileName = null;
        fileName = FileUtil.save(dto.getExcel(),FileUtil.SAVE_PATH);
        if(fileName!=null){
            Workbook workbook = null;
            try {
                workbook = WorkbookFactory.create(new File(FileUtil.SAVE_PATH + fileName));
                Sheet sheet = workbook.getSheetAt(0);
                int rowNum = sheet.getLastRowNum();
                for(int i=1; i<rowNum;i++){
                    Row row = sheet.getRow(i);
                    Student student = new Student();
                    studentList.add(student);
                    student.setName(row.getCell(0).getStringCellValue());
                    student.setAge((int) row.getCell(1).getNumericCellValue());
                    student.setTime(row.getCell(2).getDateCellValue());
                    System.out.println("姓名"+row.getCell(0).getStringCellValue());
                    System.out.println("年龄"+row.getCell(1).getNumericCellValue());
                    System.out.println("时间"+row.getCell(2).getDateCellValue());

                }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }finally {
                if(workbook!=null){
                    try {
                        workbook.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return result;
    }
}

package org.imooc.servlet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class ImportExcelInitServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
       request.getRequestDispatcher("/WEB-INF/jsp/importExcel.jsp").forward(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request,response);
    }
}

package org.imooc.servlet;


import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.imooc.dto.ImportExcelParamDto;
import org.imooc.dto.ImportExcelResultDto;
import org.imooc.dto.ParamDto;
import org.imooc.service.ExcelService;
import org.imooc.util.RequestUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet(name = "ImportExcelServlet")
public class ImportExcelServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        if (ServletFileUpload.isMultipartContent(request)) {
            ParamDto dto = RequestUtil.parseParam(request);
            ImportExcelParamDto paramDto = new ImportExcelParamDto();
            paramDto.setTitle(dto.getParamMap().get("title"));
            paramDto.setExcel(dto.getFileMap().get("excel"));

            ExcelService service = new ExcelService();
            ImportExcelResultDto resultDto = service.imp(paramDto);
            request.setAttribute("result", resultDto);
        }
        request.getRequestDispatcher("/WEB-INF/jsp/importExcelResult.jsp").forward(request,response);

    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          this.doPost(request,response);
    }
}

package org.imooc.servlet;

import java.io.IOException;

public class IndexServlet extends javax.servlet.http.HttpServlet {
    protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        request.getRequestDispatcher("/WEB-INF/jsp/index.jsp").forward(request,response);
    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        this.doPost(request,response);
    }
}

package org.imooc.util;

import org.apache.commons.fileupload.FileItem;

import java.io.File;

public class FileUtil {

    public static final String SAVE_PATH = "D:/java/Excel/upload/";
    public static String save(FileItem fileItem,String path){
        String fileName = System.currentTimeMillis()+"_"+ fileItem.getName();
        try {
            fileItem.write(new File(path + fileName));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return fileName;
    }
}

package org.imooc.util;

import com.sun.deploy.net.HttpRequest;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.imooc.dto.ParamDto;

import javax.servlet.http.HttpServletRequest;
import java.io.UnsupportedEncodingException;
import java.util.List;

public class RequestUtil {
    public static ParamDto parseParam(HttpServletRequest request) throws UnsupportedEncodingException {
        ParamDto result = new ParamDto();
        ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());
        upload.setHeaderEncoding("UTF-8");
        try {
            List<FileItem> fileItems = upload.parseRequest(request);
            for (FileItem fileItem : fileItems) {
                if (fileItem.isFormField()) {
                         result.getParamMap().put(fileItem.getFieldName(),fileItem.getString("UTF-8"));
                } else {
                         result.getFileMap().put(fileItem.getFieldName(),fileItem);
                }
            }
        } catch (FileUploadException e) {
            e.printStackTrace();
        }
        return result;
    }
}


<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <meta http-equiv="X-UA-Compatible" content="IE=9; IE=8; IE=7; IE=EDGE" />
      <title>JavaWeb实现导入导出</title>
      <link href="css/all.css" rel="stylesheet" type="text/css" />
      <link href="css/pop.css" rel="stylesheet" type="text/css" />
      <link href="css/index.css" rel="stylesheet" type="text/css" />
      <script src="js/index.js" type="text/javascript"></script>
   </head>
   <body>
      <!-- 蒙版DIV -->
      <div id="mengban" style="display: none"></div>
      <form method="post">
         <div id="header">
            <div class="iheader">
               <div class="logo">
                  <a href="#"><img src="" alt="" height="88px" width="99px" /></a>
               </div>
               <div style="height: 44px;"></div>
               <ul class="nav" id="mainMenuUl">
                  <li class="on"><a><span>导入/导出</span></a></li>
               </ul>
            </div>
         </div>
         <div id="container">
            <table style="vertical-align: top" cellspacing="0" cellpadding="0"
               bgcolor="#e1e9eb" border="0">
               <tbody>
                  <tr>
                     <td class="leftTd" style="vertical-align: top" width="150">
                        <div class="left">
                           <div class="ileft" id="menuDiv">
                              <h3 onclick="clickSecondMenu(this,'${basePath}/importExcelInitServlet')">

                                 <a>导入Excel</a>
                              </h3>
                              <h3 onclick="clickSecondMenu(this,'${basePath}/exportExcelInit')">
                                 <a>导出Excel</a>
                              </h3>
                              <h3 onclick="clickSecondMenu(this,'${basePath}/importWordInit')">
                                 <a>导入Word</a>
                              </h3>
                              <h3 onclick="clickSecondMenu(this,'${basePath}/exportWordInit')">
                                 <a>导出Word</a>
                              </h3>
                           </div>
                        </div>
                     </td>
                     <td width="7">
                        <div class="pointer"></div>
                     </td>
                     <td style="vertical-align: top" height="600px" width="100%">
                        <br/><iframe id="mainPage" src="" frameborder="0" height="580px" width="100%"></iframe><br />
                     </td>
                  </tr>
               </tbody>
            </table>
         </div>
         <div id="footer">
            <div class="copyright">慕课网</div>
            <div class="flr">copyright &copy;</div>
         </div>
      </form>
   </body>
</html>

<%
   String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath();
    request.setAttribute("basePath", basePath);
%>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
      <meta http-equiv="X-UA-Compatible" content="IE=9; IE=8; IE=7; IE=EDGE"/>
      <title></title>
      <link rel="stylesheet" type="text/css" href="css/all.css"/>
      <link rel="stylesheet" type="text/css" href="css/pop.css"/>
      <link rel="stylesheet" type="text/css" href="css/main.css"/>
   </head>
   <body style="background: #e1e9eb;">
      <form id="mainForm" method="post" enctype="multipart/form-data" action="${basePath}/importExcelServlet">
         <div class="right">
            <div class="current">当前位置：<a href="###">导入/导出</a> &gt; 导入Excel</div>
            <div class="rightCont">
               <p class="g_title fix">导入</p>
               <table class="tab1" width="100%">
                  <tbody>
                     <tr>
                     <td align="right" width="10%">标题<font color="red">*</font>：</td>
                     <td width="30%">
                        <input id="title" name="title" class="allInput" style="width:100%;" type="text"/>
                     </td>
                     <td align="right" width="10%">选择文件<font color="red">*</font>：</td>
                     <td width="30%">
                        <input type="file" name="excel" style="width:100%;"/>
                     </td>
                  </tr>
               </tbody></table>
               <div style="text-align: center; margin-top: 30px;">
                  <input class="tabSub" value="导     入" onclick="document.getElementById('mainForm').submit();" type="button" />
               </div>
            </div>
         </div>
      </form>
   </body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt_rt" %>
<html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
      <meta http-equiv="X-UA-Compatible" content="IE=9; IE=8; IE=7; IE=EDGE"/>
      <title></title>
      <link rel="stylesheet" type="text/css" href="css/all.css"/>
      <link rel="stylesheet" type="text/css" href="css/pop.css"/>
      <link rel="stylesheet" type="text/css" href="css/main.css"/>
        <script type="text/javascript">
            function bodyInit() {
                if('${result.msg}') {
                    alert('${result.msg}');
                }
            }
        </script>
   </head>
   <body style="background: #e1e9eb;" onload="bodyInit();">
      <form action="" id="mainForm" method="post">
         <div class="right">
            <div class="current">
               当前位置：<a href="#">导入/导出</a> &gt; 导入结果
            </div>
            <div class="rightCont">
               <p class="g_title fix">导入结果展示1</p>
               <table class="tab1">
                  <tbody>
                     <tr>
                        <td align="right" width="80">标题：</td>
                        <td>${result.title}</td>
                     </tr>
                  </tbody>
               </table>
               <div class="zixun fix">
                  <table class="tab2" width="100%">
                     <tbody>
                        <tr>
                           <th>序号</th>
                           <th>姓名</th>
                           <th>年龄</th>
                           <th>出生日期</th>
                        </tr>
                                <c:forEach items="${result.studentList}" var="item" varStatus="s">
                                    <tr>
                                        <td>${s.count}</td>
                                        <td>${item.name}</td>
                                        <td>${item.age}</td>
                                        <td><fmt:formatDate value="${item.time}" pattern="yyyy-MM-dd"/></td>
                                    </tr>
                                </c:forEach>
                     </tbody>
                  </table>
               </div>
            </div>
         </div>
      </form>
   </body>
</html>