关于POI导出到Excel表格中（servlet中）

本人用的时mysql数据库，其中需要的包如下图，需要你自己百度去下载

项目下的包和类的截图：

studentSerlet.java

package com.neusoft.ctr;import java.io.IOException;import java.io.PrintWriter;import java.sql.ResultSet;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.neusoft.Dao.impl.OperaterStudent;import com.neusoft.entity.Student;import com.neusoft.poi.ToExcel;public class studentServlet extends HttpServlet {/** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. *  * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {this.doPost(request, response);}/** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. *  * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.setContentType("text/html");String amethod = request.getParameter("Allmethod");System.out.println(amethod);if(""!=amethod && "allStudent".endsWith(amethod)){this.doAllStudent(request, response);}else if(""!=amethod && "toexcel".endsWith(amethod)){this.doExcel(request, response);}else{System.out.println("没有和前台获取到数据");}}/** *  * @param request * @param response * @throws ServletException * @throws IOException */public void doAllStudent(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {List<Student> list = new OperaterStudent().getAllStudents();        request.setAttribute("allstudent", list);        System.out.println(list.size());        for(int i = 1;i<list.size();i++){        Student s = (Student) list.get(i);        System.out.println(s.getName());        }                        request.getRequestDispatcher("/allstudent.jsp").forward(request, response);    }public void doExcel(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {ResultSet rs  = new OperaterStudent().getTheStudent();ToExcel toExcel = new ToExcel();toExcel.resultSetToExcel(rs, "D://student.xls", "学生信息");request.getRequestDispatcher("/index.jsp").forward(request, response);   }}

GetAllStudent.java

 

package com.neusoft.Dao;import java.sql.ResultSet;import java.util.List;import com.neusoft.entity.Student;public interface GetAllStudent {public List<Student> getAllStudents();public ResultSet getTheStudent();}

OperaterStudent.java

package com.neusoft.Dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.neusoft.Dao.GetAllStudent;import com.neusoft.entity.Student;import com.neusoft.poi.DBTools;public class OperaterStudent implements GetAllStudent {public List<Student> getAllStudents() {Connection conn = DBTools.getConnection();String sql  = "select *from student";Student student = null;List<Student> studentList = new ArrayList<Student>();try {PreparedStatement pstst = conn.prepareStatement(sql);ResultSet rs = pstst.executeQuery();while(rs.next()){student = new Student();student.setId(rs.getInt(1));student.setName(rs.getString(2));student.setSex(rs.getString(3));student.setTel(rs.getString(4));student.setAddress(rs.getString(5));studentList.add(student);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return studentList;}public ResultSet getTheStudent() {// TODO Auto-generated method stubConnection conn = DBTools.getConnection();String sql  = "select *from student";ResultSet rs = null;try {PreparedStatement pstst = conn.prepareStatement(sql);rs = pstst.executeQuery();}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return rs;}}

实体类Student

package com.neusoft.entity;public class Student {private int id;private String name;private String sex;private String tel;private String address;public Student(){}public Student(int id, String name, String sex, String tel, String address) {this.id = id;this.name = name;this.sex = sex;this.tel = tel;this.address = address;}public int getId() {return id;}public void setId(int id) {this.id = id;}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;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}}

核心的代码：ToExcel.java

package com.neusoft.poi;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;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;public class ToExcel {public void resultSetToExcel(ResultSet rs,String xlsName,String sheetName){System.out.println("----->>>>>.进入 toExcel方法中<<<<<<<-------");//产生一个工作簿HSSFWorkbook  hssfWorkbook  = new HSSFWorkbook();//产生一个工作表HSSFSheet hssfSheet = hssfWorkbook.createSheet();//设置工作表的名称hssfWorkbook.setSheetName(0, sheetName);//创建第一行HSSFRow hssfRow= hssfSheet.createRow(0);//申明工作单元HSSFCell hssfCell ;    //结果集的字段名称ResultSetMetaData md;try {//添加标题//some problem in this promblemmd = rs.getMetaData();int nColumn = md.getColumnCount();System.out.println("列数："+nColumn); //测试for(int i = 1;i<=nColumn;i++){hssfCell = hssfRow.createCell(nColumn);System.out.println("标题：："+md.getColumnLabel(i));  //测试hssfCell.setCellValue(md.getColumnLabel(i));}//添加标题结束System.out.println("填充标题结束了");//填充值。。。。int iRow = 1;while(rs.next()){hssfRow = hssfSheet.createRow(iRow);for(int j=1;j<=nColumn;j++){hssfCell = hssfRow.createCell(j-1);System.out.println("对应的值："+rs.getObject(j).toString());hssfCell.setCellValue(rs.getObject(j).toString()); //测试}iRow++;}//填充结束//输出到硬盘中FileOutputStream fos = new FileOutputStream(xlsName);hssfWorkbook.write(fos);fos.flush();fos.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

其余的链接mysql数据库的代码自己写，也可以去百度。。。