java方式实现保存Excel格式数据
在浏览器中下载查询到的数据库数据，全部保存到SXSSFWorkbook（工作簿）中的`Sheet sheet = xswb.createSheet();`（工作表）
方式一：通过JDBCUtils的方式导出
（优点：
1、可以不用一个个去设置excel表格的头行字段
2、可以适用于查询不通的表，不用处理各个表字段不同的问题
）
-
前端发送请求到servlet，把需要的查询条件（参数）传递到后台（此步骤省略...）
-
接收参数，通过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; }
方式二：通过MyBatis的xml放式导出数据（我觉得适用于条件只查询一张表数据）
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
if (null == req || null == resp){
return;
}
......
省略 获得JSONArray格式的数据
//二、 将数据转成excel req.setCharacterEncoding("UTF-8"); resp.setCharacterEncoding("UTF-8"); resp.setContentType("application/x-download"); String fileName = convertToSheetName(type); fileName = URLEncoder.encode(fileName, "UTF-8"); resp.addHeader("Content-Disposition", "attachment;filename=" + fileName); // 第一步：定义一个新的工作簿 XSSFWorkbook wb = new XSSFWorkbook(); // 第二步：创建一个Sheet页 XSSFSheet sheet = wb.createSheet("startTimeendTime"); sheet.setDefaultRowHeight((short) (2 * 256));//设置行高 sheet.setColumnWidth(0, 6000);//设置列宽 sheet.setColumnWidth(3,5500); sheet.setColumnWidth(4,5500); sheet.setColumnWidth(8,5500); sheet.setColumnWidth(9,5500); sheet.setColumnWidth(10,5500); sheet.setColumnWidth(11,5500); XSSFFont font = wb.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 16); XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell(0); cell.setCellValue("记录ID"); cell = row.createCell(1); cell.setCellValue("账套"); cell = row.createCell(2); cell.setCellValue("日期"); cell = row.createCell(3); cell.setCellValue("产品属性名称"); cell = row.createCell(4); cell.setCellValue("库存类型名称"); cell = row.createCell(5); cell.setCellValue("大类名称"); cell = row.createCell(6); cell.setCellValue("中类名称"); cell = row.createCell(7); cell.setCellValue("物料名称"); // cell = row.createCell(8); // cell.setCellValue("厂别名称"); // cell = row.createCell(9); // cell.setCellValue("库存分类名称"); cell = row.createCell(8); cell.setCellValue("部门名称"); // cell = row.createCell(11); // cell.setCellValue("库龄名称"); // cell = row.createCell(12); // cell.setCellValue("内外销名称"); // cell = row.createCell(13); // cell.setCellValue("库区名称"); // cell = row.createCell(14); // cell.setCellValue("期货现货名称"); // cell = row.createCell(15); // cell.setCellValue("付款方式名称"); // cell = row.createCell(16); // cell.setCellValue("产品流向名称"); // cell = row.createCell(17); // cell.setCellValue("库存标志名称"); cell = row.createCell(9); cell.setCellValue("库存重量"); cell = row.createCell(10); cell.setCellValue("库存金额"); cell = row.createCell(11); cell.setCellValue("市场金额"); XSSFRow rows; XSSFCell cells; for(int i = 0; i < listArray.size(); i++) { // 第三步：在这个sheet页里创建一行 rows = sheet.createRow(i+1); // 第四步：在该行创建一个单元格 cells = rows.createCell(0); // 第五步：在该单元格里设置值 cells.setCellValue(listArray.getJSONObject(i).get("REC_ID").toString()); cells = rows.createCell(1); cells.setCellValue(listArray.getJSONObject(i).get("ACCOUNT").toString()); cells = rows.createCell(2); cells.setCellValue(listArray.getJSONObject(i).get("DATE_PRC").toString()); cells = rows.createCell(3); cells.setCellValue(listArray.getJSONObject(i).get("PROD_ATTR_NAME").toString()); cells = rows.createCell(4); cells.setCellValue(listArray.getJSONObject(i).get("INV_TYPE_NAME").toString()); cells = rows.createCell(5); cells.setCellValue(listArray.getJSONObject(i).get("BIGCLASS_NAME").toString()); cells = rows.createCell(6); cells.setCellValue(listArray.getJSONObject(i).get("MIDCLASS_NAME").toString()); cells = rows.createCell(7); cells.setCellValue(listArray.getJSONObject(i).get("MAT_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("FACTORY_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("INV_CAT_NAME").toString()); cells = rows.createCell(8); cells.setCellValue(listArray.getJSONObject(i).get("DEPT_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("INV_AGE_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("DOMESTIC_EXP_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("STORE_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("FUTURES_SPOT_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("PAY_METHOD_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("PROD_FLOW_NAME").toString()); // cells.setCellValue(listArray.getJSONObject(i).get("INV_FLAG_NAME").toString()); cells = rows.createCell(9); cells.setCellValue(listArray.getJSONObject(i).get("INV_WT").toString()); cells = rows.createCell(10); cells.setCellValue(listArray.getJSONObject(i).get("INV_AMT").toString()); cells = rows.createCell(11); cells.setCellValue(listArray.getJSONObject(i).get("MARKET_AMT").toString()); } try { OutputStream out = resp.getOutputStream(); wb.write(out); out.flush(); out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
