使用 POI导出到Excel
private String productEcel(List<BiUserAction> list) throws Exception {// 创建工作表和标题HSSFWorkbook workbook = null;try {workbook = new HSSFWorkbook();} catch (Exception e) {e.printStackTrace();}int rowIndex=0;HSSFSheet sheet = workbook.createSheet("贝壳奖励统计"); // 创建工作区/*HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象HSSFFont title_font = workbook.createFont(); // 创建标题的字体title_font.setFontHeightInPoints((short) 8);title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);title_font.setColor((short) (HSSFFont.COLOR_NORMAL));HSSFCellStyle title_style = workbook.createCellStyle();// 创建样式title_style.setFont(title_font);title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//合并 第一个的 0 到 3 的单元格Region region=new Region(0,(short)0,0,(short)3);sheet.addMergedRegion(region);HSSFCell cell_title = row_title.createCell((short) 0);// 创建单元格引用对象cell_title.setCellStyle(title_style);cell_title.setCellValue("贝壳奖励统计");*/// 创建数据表头String titles[] = { "序号", "奖励项目", "贝壳总数", "经验总数"};//定义字体HSSFFont celltbnamefont = workbook.createFont();celltbnamefont.setFontHeightInPoints((short) 12); //字体大小celltbnamefont.setColor((short) (HSSFFont.COLOR_NORMAL)); //颜色celltbnamefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体//定义列的样式 HSSFCellStyle items_style = workbook.createCellStyle();items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER); //设置对其方式items_style.setFont(celltbnamefont);items_style.setWrapText(true); //设置自动换行HSSFRow row = sheet.createRow((short) rowIndex);//加入 标题for (int i = 0; i < titles.length; i++) {HSSFCell cell = row.createCell(i,Cell.CELL_TYPE_STRING); //设置 列类型if (i == 1 || i == 3 || i == 2 || i == 5) {sheet.setColumnWidth(i, 5335);}else{//sheet.setColumnWidth((short) i, (short) 3335);sheet.setColumnWidth(i, 3335);}cell.setCellValue(titles[i]);cell.setCellStyle(items_style);}//定义 date 的数据样式HSSFCellStyle datestyle = workbook.createCellStyle();HSSFDataFormat df = workbook.createDataFormat();datestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);datestyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss"));//定义 int 的数据样式HSSFCellStyle intdatestyle = workbook.createCellStyle();intdatestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);//定义 float 的数据样式HSSFCellStyle floatdatestyle = workbook.createCellStyle();floatdatestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);df = workbook.createDataFormat();floatdatestyle.setDataFormat(df.getFormat("#.##"));//定义 long 的数据样式HSSFCellStyle longdatestyle = workbook.createCellStyle();longdatestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);HSSFCell cell;Iterator<BiUserAction> it =list.iterator();while (it.hasNext()) {HSSFRow dataRow = sheet.createRow((short) (++rowIndex));BiUserAction userAction = it.next();cell = dataRow.createCell(0,Cell.CELL_TYPE_NUMERIC);cell.setCellStyle(intdatestyle);cell.setCellValue(rowIndex);cell = dataRow.createCell(1,Cell.CELL_TYPE_STRING);String ruleName = userAction.getRuleName();cell.setCellValue(ruleName);cell = dataRow.createCell(2,Cell.CELL_TYPE_NUMERIC);cell.setCellStyle(longdatestyle);cell.setCellValue(userAction.getCredits());cell = dataRow.createCell(3,Cell.CELL_TYPE_NUMERIC);cell.setCellStyle(longdatestyle);cell.setCellValue(userAction.getExperience());}HSSFRow dataRow = sheet.createRow((short) (++rowIndex));cell = dataRow.createCell(0,Cell.CELL_TYPE_STRING);cell.setCellStyle(intdatestyle);cell.setCellValue("合计:");cell = dataRow.createCell(1,Cell.CELL_TYPE_STRING);cell.setCellValue("");//添加 公式cell = dataRow.createCell(2,Cell.CELL_TYPE_NUMERIC);cell.setCellStyle(longdatestyle);cell.setCellFormula("SUM(" + getColLetter(2) + sheet.getRow(2).getCell(2).getRowIndex() + ":" + getColLetter(2) +sheet.getRow(sheet.getLastRowNum()).getCell(2).getRowIndex() + ")");//添加 公式cell = dataRow.createCell(3,Cell.CELL_TYPE_NUMERIC);cell.setCellStyle(longdatestyle);cell.setCellFormula("SUM(" + getColLetter(3) + sheet.getRow(2).getCell(3).getRowIndex() + ":" + getColLetter(3) +sheet.getRow(sheet.getLastRowNum()).getCell(3).getRowIndex() + ")");String workDir = this.getServlet().getServletContext().getRealPath("/");String workddd = workDir.replaceAll("\\\", "/");Calendar calendar = Calendar.getInstance();String month = calendar.get(Calendar.YEAR) + "/"+ (calendar.get(Calendar.MONTH) + 1);String filePath = "newsxls/adxls/" + month + "/";File f = new File(workDir + filePath);if (!f.isDirectory()) {f.mkdirs();}String fileNameCode = java.util.UUID.randomUUID().toString();String completeFilePath = workddd + filePath + fileNameCode + ".xls";FileOutputStream fileOut = new FileOutputStream(completeFilePath);this.getRequest().getSession(true).setAttribute("completeFilePath",completeFilePath);workbook.write(fileOut);fileOut.flush();fileOut.close();return completeFilePath;} /** * 将列的索引换算成ABCD字母，这个方法要在插入公式时用到。 * @param colIndex 列索引。 * @return ABCD字母。 */ private String getColLetter(int colIndex) { String ch = ""; if (colIndex < 26) ch = "" + (char)((colIndex) + 65); else ch = "" + (char)((colIndex) / 26 + 65 - 1) + (char)((colIndex) % 26 + 65); return ch; }