javaweb17

bean.Dao

package bean;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class Dao {

public void insert(student s) throws ClassNotFoundException, SQLException {
Connection conn=null;
    PreparedStatement ps=null;
    conn=jdbc.getConnection();
    String sql="insert into stu values(?,?,?)";
    ps=conn.prepareStatement(sql);
    ps.setString(1, s.getName());
    ps.setString(2, s.getSex());
    ps.setString(3, s.getTel());
    ps.executeUpdate();
}

public List<student> select(String id) throws ClassNotFoundException, SQLException{
Connection conn=null;
    PreparedStatement ps=null;
    conn=jdbc.getConnection();
    String sql="select * from "+id;
    ps=conn.prepareStatement(sql);
    List<student>list=new ArrayList<>();
    ResultSet rs=ps.executeQuery();
    while(rs.next()) {
    student p=new student();
    p.setName(rs.getString("name"));
    p.setSex(rs.getString("sex"));
    p.setTel(rs.getString("tel"));
         list.add(p);}
    return list;
}

}

bean.jdbc

package bean;
import java.sql.*;


public class jdbc {
static Connection conn=null;
public static   Connection getConnection() throws ClassNotFoundException, SQLException {
String user="root";
String drivername="com.mysql.jdbc.Driver";
String usepwd="test";
String dbname="sys";
String url1="jdbc:mysql://localhost:3306/"+dbname;
String url2="?user="+user+"&password="+usepwd;
String url3="&useUnicode=true&characterEncoding=UTF-8";
String url=url1+url2+url3;
Class.forName(drivername);
try{conn=DriverManager.getConnection(url);
return conn;}catch(Exception e) {
e.printStackTrace();
return null;
}
}
public static void free(ResultSet rs,Statement st,Connection conn)
{  
    try
    { 
        if(rs!=null)              
            rs.close();                       
    }catch (SQLException e) 
    { 
        e.printStackTrace();  
    }finally{
             try
             {
                 if(st!=null)
                     st.close();                 
             }catch (SQLException e) { e.printStackTrace();  
             } finally{
                 if(conn!=null)                       
                     try
                 { 
                         conn.close();  
                 }
                 catch (SQLException e) {  
                     e.printStackTrace();  
                  }  
                 }   
               }
    }

}

bean.student

package bean;


public class student {
private String name;
private String sex;
private String tel;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}

}

servlet.servlet

package servlet;


import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;


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 bean.Dao;
import bean.jdbc;
import bean.student;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;


/**
 * Servlet implementation class servlet
 */
@WebServlet("/servlet")
public class servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public servlet() {
        super();
        // TODO Auto-generated constructor stub
    }


/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}


/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
try {
String  url=request.getParameter("in");
Connection conn=jdbc.getConnection();
int i;  
        Sheet sheet;  
        Workbook book;  
        Cell cell1,cell2,cell3,cell4,cell5,cell6,cell7;  
            PrintWriter out=response.getWriter();
            out.print("Excel导入成功");
            try {   
            book= Workbook.getWorkbook(new File(url));   
            sheet=book.getSheet(0);                  
            i=1;  
            student s=new student();
            Dao d=new Dao();
            while(true)  
            {  
                cell1=sheet.getCell(0,i);  
                cell2=sheet.getCell(1,i);  
                cell3=sheet.getCell(2,i);  
                if("".equals(cell1.getContents())==true)    
                    break;  
                String a=cell1.getContents();
                String a1=cell2.getContents();
                String a2=cell3.getContents();
                
                s.setName(a);
                s.setSex(a1);
                s.setTel(a2);
                d.insert(s);
                
                System.out.println(a);  
                System.out.println(a1);  
                System.out.println(a2);  
                i++;  
            } 
            book.close();   
        }  
        catch(Exception e)  { }  
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}


}

}

servlet.servlet1

package servlet;


import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;


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 org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


import bean.Dao;
import bean.student;




/**
 * Servlet implementation class servlet1
 */
@WebServlet("/servlet1")
public class servlet1 extends HttpServlet {
private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public servlet1() {
        super();
        // TODO Auto-generated constructor stub
    }


/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}


/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
Dao d=new Dao();
try {
List<student>list=d.select("stu");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户表");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("姓名");
        cell = row.createCell(1);
        cell.setCellValue("性别");
        cell = row.createCell(2);
        cell.setCellValue("电话");


        for (int i = 0; i < list.size(); i++) {
            HSSFRow row1 = sheet.createRow(i + 1);
            student user = list.get(i);
            row1.createCell(0).setCellValue(user.getName());
            row1.createCell(1).setCellValue(user.getSex());
            row1.createCell(2).setCellValue(user.getTel());
        }


        try {
        PrintWriter out=response.getWriter();
            FileOutputStream fos = new FileOutputStream("D:\\book1.xls");
            workbook.write(fos);
            out.println("Excel导出成功");
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}

}

}

web17.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<br><br><br><br><br><br><br><br>
<div align="center">
<a href="web17-1.jsp"><h2>导入</h2></a>
<br>
<a href="web17-2.jsp"><h2>导出</h2></a>
</div>
</body>

</html>

web17-1.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="servlet" method="post">
<br><br><br><br><br><br><br><br>
<div align="center">
请选择要导入的excel表:<input type="file" name="in">
<input type="submit" value="导入">
</div>
</form>
</body>

</html>

web17-2.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="servlet1" method="post">
<br><br><br><br><br><br><br><br>
<div align="center">
请选择要导入的excel表:<input type="file" name="out">
<input type="submit" value="导出">
</div>
</form>
</body>
</html>