导出 Excel 表格

Workbook 工作簿
Sheet 每页表格的页名
Row 行
Cell 单元格

HSSFWorkbook 生成以 .xls 结尾的文件，比较老了  对应的 Sheet 实现类为HSSFSheet 
XSSFWorkbook .xlsx 可以存万条以内的数据
SXSSFWorkbook .xlsx 104万多条

第一种

通过原生 jdbc 查询数据库，
org.apache.poi包 即第三方组件处理 Excel 表格，
通过 IO 流，下载到本地

import java.sql.*;
import org.apache.poi.ss.usermodel.*;//Workbook Sheet Row Cell
import org.apache.poi.xssf.usermodel.*;//XSSFWorkbook
import java.io.*;

//把数据库下的所有表和数据导出到Excel表中
public class Exec1{
	public static void main(String args[])throws Exception{

		//~~~~~~~~~内存中构造一个空的Workbook对象
		Workbook wb = new XSSFWorkbook();

		//通过 show tables 语句获取该数据库下的所有表名
		Connection con = getConnection();
		String selectTable="show tables";
		PreparedStatement pst = con.prepareStatement(selectTable);
		//结果集只有一列，其列名为 Tables_in_数据库名字，其下的值为各个表名
		ResultSet tables = pst.executeQuery();
		while(tables.next()){
			//拿取结果集的第一列数据，即各个表名
			String tableName = tables.getString(1);

			//~~~~~~~~~创建sheet，表名赋值给页名
			Sheet sheet = wb.createSheet(tableName);

			//拿取表中的所有数据
			String sql="select * from "+tableName;
			pst = con.prepareStatement(sql);
			ResultSet data = pst.executeQuery();
			//拿取该表的元数据
			ResultSetMetaData rsmd = data.getMetaData();
			//列数
			int count = rsmd.getColumnCount();

			//~~~~~~~~~创建表头行
			Row header = sheet.createRow(0);
			for(int i=1;i<=count;i++){
				//~~~~~~~~~创建表头行的单元格
				Cell cell = header.createCell(i-1);
				//~~~~~~~~~添加内容，即表的字段名
				cell.setCellValue(rsmd.getColumnName(i));
			}
			//~~~~~~~~~因为表头行已经被用来添加字段名，这里从第二行开始添加
			int k=1;
			while(data.next()){
				//~~~~~~~~~创建一行
				Row data_row = sheet.createRow(k++);
				for(int i=1;i<=count;i++){
					//获取列名字
					String cName = rsmd.getColumnName(i);
					//获取列内容
					String cValue = data.getString(cName);
					//~~~~~~~~~在该行创建单元格
					Cell cell = data_row.createCell(i-1);
					//~~~~~~~~~单元格添加内容
					cell.setCellValue(cValue);
				}
			}
			data.close();
		}
		pst.close();
		con.close();

		OutputStream os = new FileOutputStream(new File("execl.xlsx"));
		//~~~~~~~~~输出文件到指定目录
		wb.write(os);
		wb.close();
	}

	private static Connection getConnection()throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb","root","");
		return con;
	}
}

第二种

导入 org.apache.poi 坐标依赖

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.14</version>
    </dependency>

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.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet(value = "/stu")
public class StudentServlet extends HttpServlet {

	@Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.export2Excel(req,resp);
	}
	
    protected void export2Excel(HttpServletRequest req, HttpServletResponse resp) 
    							throws ServletException, IOException{
    	//拿取数据
    	List<Student> data = service.queryData();
    							
        //把查询到的数据 写出到excel
        //1.Workbook
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("学生信息列表");
        //2.Sheet
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("名字");
        row.createCell(2).setCellValue("年龄");
        row.createCell(3).setCellValue("生日");
        row.createCell(4).setCellValue("邮箱");

        //3.Row
        int k = 1;
        for (Student d : data){
            Row dataRow = sheet.createRow(k++);
            dataRow.createCell(0).setCellValue(k-1);
            dataRow.createCell(1).setCellValue(d.getName());
            dataRow.createCell(2).setCellValue(d.getAge());
            dataRow.createCell(3).setCellValue(d.getBirth());
            dataRow.createCell(4).setCellValue(d.getEmail());
        }
        //设置响应表头，用户可从浏览器下载该文件到本地
        resp.setHeader("Content-Disposition","attachment;filename="+
                URLEncoder.encode("学生列表","utf-8")+".xlsx");
        OutputStream os = resp.getOutputStream();
        wb.write(os);
        wb.close();
	}
}

上面是通过mybatis拿取的数据，如果要通过mybatis拿取表的字段名和数据类型，如下

1、只拿字段名

	List<String> querySheet();

    <select id="querySheet" resultType="string">
        select DISTINCT COLUMN_NAME from information_schema.COLUMNS where table_name = 'student2'
    </select>

2、字段名和数据类型

    List<Map<String,String>> querySheet();

如果对键名 没要求，可以不用 resultMap 接收，用 resultType=“map” 即可

    <resultMap id="sheet_Map" type="HashMap">
        <result property="COLUMN_NAME" column="COLUMN_NAME"/>
        <result property="DATA_TYPE" column="DATA_TYPE"/>
    </resultMap>
    <select id="querySheet" resultMap="sheet_Map">
        select DISTINCT COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS where table_name = 'student2'
    </select>