/** * 该方法,创建表头 * @param headers 列表头 String[] 类型 * @return HSSFWorkbook 工作簿 poi包内类型 * */ public static HSSFWorkbook workbook=new HSSFWorkbook(); public static HSSFSheet tableHeader(String[] headers){ //创建工作簿 //创建表 HSSFSheet sheet=workbook.createSheet("Sheet1"); sheet.setDefaultColumnWidth(20);//设置默认列宽20 //创建表的第一行 HSSFRow row=sheet.createRow(0); row.setHeight((short)400); //创建列的样式 HSSFFont font=workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//创建字体 HSSFCellStyle cellStyle=workbook.createCellStyle(); cellStyle.setFont(font); cellStyle.setBorderBottom((short)4);//设置表框粗细 cellStyle.setBorderLeft((short)4); cellStyle.setBorderRight((short)4); cellStyle.setBorderTop((short)4); cellStyle.setBottomBorderColor(HSSFColor.AQUA.index);//设置边框颜色 cellStyle.setTopBorderColor(HSSFColor.AQUA.index); // cellStyle.setFillPattern(HSSFCellStyle.ALIGN_FILL); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置居中 cellStyle.setVerticalAlignment(HSSFCellStyle.BORDER_MEDIUM); row.setRowStyle(cellStyle); //创建表的列标题 for (int i = 0; i < headers.length; i++) { HSSFCell cell=row.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(cellStyle); } return sheet; } /** * 该方法,创建表头 * @param path 写出excel路径 String 类型 * @param workbook 工作簿 HSSFWorkbook * @return boolean 是否成功 * */ public static boolean writeExcel(String path){ boolean flag=false; File fl=new File(path); try { FileOutputStream fos=new FileOutputStream(fl); workbook.write(fos); fos.flush(); fos.close(); flag=true; } catch (Exception e) { flag=false; e.printStackTrace(); } return flag; } public List queryAll() { List list = new ArrayList(); String sql = "select a.fCode,a.fName,a.fParent,b.fBegBal,b.fDebitBal from g_Account a join g_Bal as b " + "on a.fCode=b.fEntCode where b.fPeriod=1"; Connection con = SqlHelp.getConn(); PreparedStatement pst = null; ResultSet rs = null; try { pst = con.prepareStatement(sql); rs = pst.executeQuery(); while (rs.next()) { Map mp = new HashMap(); mp.put("fCode", rs.getString("fCode")); mp.put("fName", rs.getString("fName")); mp.put("fParent", rs.getString("fParent")); mp.put("fBegBal", rs.getDouble("fBegBal")); mp.put("fDebitBal", rs.getDouble("fDebitBal")); mp.put("sum", (rs.getDouble("fBegBal") + rs .getDouble("fDebitBal"))); list.add(mp); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { SqlHelp.colseConn(con, pst, rs); } return list; } public void tableHeader(List list) { // 创建表头 String[] headers = new String[] { "科目编码", "科目名称", "科目父类", "期初余额", "本期借方发生额", "余额与发生额的和" }; HSSFSheet sheet = ExportExcel.tableHeader(headers); // 循环List数据写入excel for (int i = 0; i < list.size(); i++) { Map mp = (Map) list.get(i); HSSFRow row1 = sheet.createRow(i + 1); Iterator itr = mp.entrySet().iterator(); while (itr.hasNext()) { Map.Entry entry = (Entry) itr.next(); if (entry.getKey().equals("fCode")) { row1.createCell(0) .setCellValue(entry.getValue().toString()); } if (entry.getKey().equals("fName")) { row1.createCell(1) .setCellValue(entry.getValue().toString()); } if (entry.getKey().equals("fParent")) { row1.createCell(2) .setCellValue(entry.getValue().toString()); } if (entry.getKey().equals("fBegBal")) { row1.createCell(3).setCellValue( Double.parseDouble(entry.getValue().toString())); } if (entry.getKey().equals("fDebitBal")) { row1.createCell(4).setCellValue( Double.parseDouble(entry.getValue().toString())); } if (entry.getKey().equals("sum")) { row1.createCell(5).setCellValue( Double.parseDouble(entry.getValue().toString())); } } } // 写入Excel ExportExcel.writeExcel("g:/a.xls"); }