在servlet里用poi导出数据成excel

Question

这个问题 我研究了 很久 可是 还是 不行  

Answer 1

public String save2Excel() throws Exception{
String ref="";
//创建一个新的WORKbook对象,生成的文件名为.xls
Workbook wb = new HSSFWorkbook();
        //新建一个MAP对象，对象中封装了若干个样式，比如表头什么样式，字段什么样式，某些值是什么样式,
        //每个样式都是有一个值，即key与value的关系，key是String类型，value是CellStyle类型
        Map<String, CellStyle> styles = createStyles(wb);
        //新建一个sheet,命名sheet的名字
        Sheet sheet = wb.createSheet(tabletitle);
        PrintSetup printSetup = sheet.getPrintSetup();
        //设置纸张是纵向还是横向，当表格很宽，设置为横向true,否则为false；
        printSetup.setLandscape(pageIsBigWidth);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        //创建一行,第0行，对应EXCEL中是第1行
        Row titleRow = sheet.createRow(0);
        //设置行高,55个像点高
        titleRow.setHeightInPoints(55);
        //创建行中的一个单行格，即一个CELL
        Cell titleCell = titleRow.createCell(0);
        //设置CELL中的值
        titleCell.setCellValue(tabletitle);
        //设置CELL中的样式，取MAP对象中的title样式，
        titleCell.setCellStyle(styles.get("title"));
        //拼行。从A1到F1合并，成第一行"$A$1:$F$1"
        ref = "$".concat(chars[0]).concat("$1:$").concat(chars[heads.length-1]).concat("$1");
        sheet.addMergedRegion(CellRangeAddress.valueOf(ref));
        
        //创建1行，对应EXCEL中是第2行
        Row Row2 = sheet.createRow(1);
        //设置行高,30个像点高
        titleRow.setHeightInPoints(30);
        //创建行中的一个单行格，即一个CELL
        Cell titleCell2 = Row2.createCell(0);
        titleCell2.setCellStyle(styles.get("datetime"));
        //设置CELL中的值
        titleCell2.setCellValue(datetime);
        //拼行。从A1到F1合并，成第一行"$A$2:$F$2"
        ref = "$".concat(chars[0]).concat("$2:$").concat(chars[heads.length-1]).concat("$2");
        sheet.addMergedRegion(CellRangeAddress.valueOf(ref));
        
        //创建第三行，即表格的头
        Row headerRow = sheet.createRow(2);
        //设置行高
        headerRow.setHeightInPoints(35);
        //设置cell对象，
        Cell headerCell;
        //循环为cell对象设置值及样式,title数组中存放着表头的几个字段，
        for (int i = 0; i < heads.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(heads[i]);
            headerCell.setCellStyle(styles.get("header"));
        }
        
      //报表头与表头占用了两行，接下来创建行从第三行开始
        int rownum = 3;
        for(int i=0;i<lines;i++) {
         //创建一行，注意行数变化是 rownum++ 
            Row row = sheet.createRow(rownum++);
            Cell cell = null;
            for (int j = 0; j < heads.length; j++) {
             cell = row.createCell(j);
             cell.setCellStyle(styles.get("cell"));
             if(datas[i][j] instanceof String) {
                    row.getCell(j).setCellValue((String)datas[i][j]);
                } else {
                    row.getCell(j).setCellValue((Integer)datas[i][j]);
                }
             if(i == lines-1 && j>colindex ){
             //设置单元格公式，即SUM(C3:I3)
             ref= "SUM(".concat(chars[j]).concat("4:").concat(chars[j]).concat(String.valueOf(3+lines-1)).concat(")");
                    cell.setCellFormula(ref);
             }
           }
        }
        for (int i = 0; i < heads.length-1; i++) {
            sheet.setColumnWidth(i, columnWidth*256);  //6 characters wide
        }
        String filename=sdf.format(new Date()).concat(".xls");
        FileOutputStream out = new FileOutputStream(new File(diz.concat(filename)));
        wb.write(out);
        out.close();
        return filename;
}

@SuppressWarnings("deprecation")
public String saveExcel( String[] str) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(tabletitle);
        //创建第一行，即表格的头

  // 另一个样式    
        HSSFCellStyle headstyle = wb.createCellStyle();    
        headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    
        headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    
        headstyle.setLocked(true);    
        headstyle.setWrapText(true);// 自动换行  
        Cell headerCell;
        Cell headerCell2;
        int rownum = 0;
        
    
        if(str!=null&&str.length>0){
            Row   row1   =   sheet.createRow((short)   rownum); 
            Cell   cell1;
            short line = 0;
            for(int i=0;i<str.length;i++){
             String[] taste = str[i].split(",");
             cell1 = row1.createCell(line); 
             cell1.setCellStyle(headstyle);    
                cell1.setCellValue( taste[0]); 
                sheet.addMergedRegion(new Region(0,(short)line,0,(short)(line+Short.valueOf(taste[1])-1))); 
                line=(short)(line+Short.valueOf(taste[1]));
            }
            rownum++;
        }
      
        Row headerRow = sheet.createRow(rownum++);
        headerCell = headerRow.createCell((short)heads.length);
        Row headerRow2 = sheet.createRow(rownum++);
        headerCell2 = headerRow2.createCell((short)heads.length);
        for (int i = 0; i < heads.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell2 = headerRow2.createCell(i);
             headerCell.setCellValue(heads[i].split(",")[0]);
                headerCell.setCellStyle(headstyle);
                headerCell2.setCellValue(heads[i].split(",")[1]);
                headerCell2.setCellStyle(headstyle);
        }
        
        Cell cell = null;
        for(int i=0;i<lines;i++) {
         //创建一行，注意行数变化是 rownum++ 
            Row row = sheet.createRow(rownum++);
            for (int j = 0; j < heads.length; j++) {
             cell = row.createCell(j);
             cell.setCellStyle(headstyle);    
             if(datas[i][j] instanceof String) {
                    row.getCell(j).setCellValue((String)datas[i][j]);
                } else if(datas[i][j] instanceof Short) {
                    row.getCell(j).setCellValue((Short)datas[i][j]);
                }
                else if(datas[i][j] instanceof Integer) {
                    row.getCell(j).setCellValue((Integer)datas[i][j]);
                }
                else if(datas[i][j] instanceof Long) {
                    row.getCell(j).setCellValue((Long)datas[i][j]);
                }
                else if(datas[i][j] instanceof Double) {
                    row.getCell(j).setCellValue((Double)datas[i][j]);
                }
                else if(datas[i][j] instanceof Float) {
                    row.getCell(j).setCellValue((Float)datas[i][j]);
                }
                else if(datas[i][j] instanceof Boolean) {
                    row.getCell(j).setCellValue((Boolean)datas[i][j]);
                }
                else{
                  row.getCell(j).setCellValue((String)datas[i][j]);
                }
           }
        }
        //单个CELL的默然宽度
        for (int i = 0; i < heads.length-1; i++) {
            sheet.setColumnWidth(i, columnWidth*256);  //6 characters wide
        }
        String filename=sdf.format(new Date()).concat(".xls");
        FileOutputStream out = new FileOutputStream(new File(diz.concat(filename)));
        wb.write(out);
        out.close();
        return filename;
}