页面点击下载查询数据库数据写入到SXSSFWorkbook（工作簿）中的`Sheet sheet = xswb.createSheet();`（工作表）
前端发送请求到servlet，把需要的参数传递到后台【我这里把response对象也封装到了(EiInfo inInfo)对象中】（此步骤省略...）
接收参数，通过jdbc的方法(jdbc连接数据库步骤省略...)查询数据库数据，`getMetaData()`直接操作数据库便捷，但效率可能较低
通过response对象获得输出流，将SXSSFWorkbook写入到浏览器
//jdbc方式保存到excel
public EiInfo downData(EiInfo inInfo) {
String sql = convertToSQL(date_PRC1, date_PRC2, type);
String sheetName = convertToSheetName(type);
HttpServletResponse response =(HttpServletResponse) inInfo.get("response");
SXSSFWorkbook xswb;
try {
// 创建xlsx
xswb = createXlsx(sql, sheetName);
// 生成文件
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(sheetName, "utf-8"));
OutputStream outputStream;
outputStream = response.getOutputStream();
xswb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
inInfo.set("status", false);
inInfo.set("msg", "导出失败！");
return inInfo;
}
return inInfo;
}
/*
* 传入sql语句，和需要的命名
* 返回工作簿对象
*/
private SXSSFWorkbook createXlsx(String sql, String sheetName) throws Exception {
SXSSFWorkbook xswb = null;
Connection con = null;
Statement st = null;
ResultSet rs = null;try {
con = JDBCUtils.getConnection();
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
xswb = new SXSSFWorkbook();// 创建工作表Sheet sheet = xswb.createSheet();
xswb.setSheetName(0, sheetName);// 查询数据rs = st.executeQuery(sql);
System.out.println(rs);
ResultSetMetaData metaData = rs.getMetaData();int columnCount = metaData.getColumnCount();// 创建表头Row head = sheet.createRow(0);for (int i = 0; i < columnCount; i++) {
Cell cell = head.createCell(i);
cell.setCellValue(metaData.getColumnName(i + 1));
}// 获取总行数 rs.last();int rowCount = rs.getRow();
rs.beforeFirst();// 创建标题for (int i = 1; i <= rowCount; i++) {
rs.next();
Row row = sheet.createRow(i);for (int j = 0; j < columnCount; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(rs.getString(j + 1));
}
}
} catch (Exception e) {throw new Exception(e);
} finally {
JDBCUtils.colseResource(con, st, rs);
}return xswb;
}
【Excel学习】Excel速成班视频 https://306t.com/file/686368-477370654
更多资源下载地址：https://z701.com/f/686368-485346643-6740b7
（访问密码：4939）
