将数据库查询结果生成excel

package com.dong.util;


import java.io.FileOutputStream;
import java.io.IOException;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;

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.Row;



public class TestExcel{
    

	//显示的导出表的标题
    private String title;
    //导出表的列名
    private String[] cloName ;
    
    private List<Object[]>  dataList = new ArrayList<Object[]>();
    
    HttpServletResponse  response;
    
    //构造方法，传入要导出的数据
    public TestExcel(String title,String[] cloName,List<Object[]>  dataList){
        this.dataList = dataList;
        this.cloName = cloName;
        this.title = title;
    }
            
    /*
     * 导出数据
     * */
    public void export() throws Exception{
        try{
        	HSSFWorkbook workbook = new HSSFWorkbook();  // 创建工作簿对象
        	HSSFSheet sheet = workbook.createSheet(title); // 创建工作表sheet
            
            
            // 定义所需列数
           int columnNum = cloName.length;
           Row rowRowName = sheet.createRow(0); // 创建行
            // 将列头设置到sheet的单元格中
            for(int n=0;n<columnNum;n++){
                Cell  cellRowName = rowRowName.createCell(n);  //创建列头对应个数的单元格
                HSSFRichTextString text = new HSSFRichTextString(cloName[n]);
                cellRowName.setCellValue(text);  //设置列头单元格的值              
            }
            
            //将查询出的数据设置到sheet对应的单元格中
            for(int i=0;i<dataList.size();i++){
                Object[] obj = dataList.get(i);//遍历每个对象
                Row row = sheet.createRow(i+1);//创建所需的行数
                for(int j=0; j<obj.length; j++){
                    HSSFCell  cell = null;   //设置单元格的数据类型
                    if(j == 0){
                        cell = (HSSFCell) row.createCell(j);
                        cell.setCellValue(i+1);    
                    }else{
                        cell = (HSSFCell) row.createCell(j);
                        if(!"".equals(obj[j]) && obj[j] != null){
                            cell.setCellValue(obj[j].toString()); //设置单元格的值
                        }
                    }
                                        
                }
            }
            if(workbook !=null){
                try
                {
                    FileOutputStream out = new FileOutputStream("D:/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xlsx");
                    workbook.write(out);
                    out.close();
                    System.out.println("ok");
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }
        
    }
}

查询数据

package com.dong.dao;

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

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hnrj.util.JDBCUtil;

public class UserDao {
	  public List<Object[]> getUser() throws SQLException {
	        //获取连接对象
	        //执行SQL语句的对象st调用executeQuery()方法，执行查询语句，将查询到的结果返回到一个结果集中
			Connection conn = null;
			ResultSet rs =  null;
			PreparedStatement st = null;
			List<Object[]>  dataList = new ArrayList<Object[]>();
			Object[] objs = null;
			try {
				conn = JDBCUtil.getConn();
				//连接对象conn调用createStatement()方法，创建一个执行SQL语句的对象st
				  st = conn.prepareStatement("select * from user");
//				  st.setString(1, name);
//				  st.setString(2, id);
				  rs = st.executeQuery();
				  String[] cloName = new String[]{"id","name","password","gender","phone","email","role","img","nicename","regist_time"};
				  
				   while(rs.next()){
					   objs = new Object[cloName.length];
					   objs[0]=rs.getInt("id");
					   objs[1]=rs.getString("username");
					   objs[2]=rs.getString("password");
					   objs[3]=rs.getInt("gender");
					   objs[4]=rs.getString("phone");
					   objs[5]=rs.getString("email");
					   objs[6]=rs.getInt("role");
					   objs[7]=rs.getString("img");
					   objs[8]=rs.getString("nickname");
					   objs[9]=rs.getString("regist_time");
					   dataList.add(objs);
		            }
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				JDBCUtil.close(rs, st, conn);
			}
			return dataList; 
	    }
}

jdbc

package com.dong.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hnrj.credit.entity.User;

public class JDBCUtil {
	
	 private static final String DRIVER="com.mysql.cj.jdbc.Driver";
     private static final String URl="jdbc:mysql://localhost:3306/esport";
     private static final String  DBUSER="root";
     private static final String  DBPWD="123456";

     static{
         try {
             //注册驱动
             Class.forName(DRIVER);
         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         }
     }
			
		
	
	 //获取mysql数据库连接
	public static Connection getConn(){
		Connection conn = null;
			try {
				conn = DriverManager.getConnection(URl, DBUSER, DBPWD);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    	return conn;
	}
	      
	 
	public static void close(ResultSet resultSet, Statement statement, Connection connection) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			resultSet = null;
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			statement = null;
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
	}
}

servlet

package com.dong.servlet;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.ResultSet;
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.commons.io.IOUtils;

import com.alibaba.fastjson.JSONObject;
import com.hnrj.credit.dao.UserDao;
import com.hnrj.credit.service.lhjc.impl.TestExcel;
import com.hnrj.util.JDBCUtil;


@WebServlet("/FindUser")
public class UserServlet extends HttpServlet{
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp){
		try {
			InputStream is= req.getInputStream();
			String bodyInfo = IOUtils.toString(is, "UTF-8");
			JSONObject jsonobject = JSONObject.parseObject(bodyInfo);
			String  name=jsonobject.getString("nickname");
			String id =jsonobject.getString("id");
			UserDao userdao = new  UserDao();
			List<Object[]> dataList = userdao.getUser();
			  String[] cloName = new String[]{"id","name","password","gender","phone","email","role","img","nicename","regist_time"};
			  TestExcel ex = new TestExcel("test", cloName, dataList);
		      try {
				ex.export();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
	}
}