ExcelUtil

 1 public class ExcelUtil {
 2     
 3     public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
 4         int rowIndex=0;
 5         Sheet sheet=wb.createSheet();
 6         Row row=sheet.createRow(rowIndex++);
 7         for(int i=0;i<headers.length;i++){
 8             row.createCell(i).setCellValue(headers[i]);
 9         }
10         while(rs.next()){
11             row=sheet.createRow(rowIndex++);
12             for(int i=0;i<headers.length;i++){
13                 row.createCell(i).setCellValue(rs.getObject(i+1).toString());
14             }
15         }
16     }
17     
18     public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
19         InputStream inp=ExcelUtil.class.getResourceAsStream("/template/"+templateFileName);
20         POIFSFileSystem fs=new POIFSFileSystem(inp);
21         Workbook wb=new HSSFWorkbook(fs);
22         Sheet sheet=wb.getSheetAt(0);
23         // ��ȡ����
24         int cellNums=sheet.getRow(0).getLastCellNum();
25         int rowIndex=1;
26         while(rs.next()){
27             Row row=sheet.createRow(rowIndex++);
28             for(int i=0;i<cellNums;i++){
29                 row.createCell(i).setCellValue(rs.getObject(i+1).toString());
30             }
31         }
32         return wb;
33     }
34     
35     public static String formatCell(HSSFCell hssfCell){
36         if(hssfCell==null){
37             return "";
38         }else{
39             if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
40                 return String.valueOf(hssfCell.getBooleanCellValue());
41             }else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
42                 return String.valueOf(hssfCell.getNumericCellValue());
43             }else{
44                 return String.valueOf(hssfCell.getStringCellValue());
45             }
46         }
47     }
48     
49     
50     public static String getCell(HSSFCell cell) {
51         DecimalFormat df = new DecimalFormat("#");
52         if (cell == null)
53             return "";
54         switch (cell.getCellType()) {
55         case HSSFCell.CELL_TYPE_NUMERIC:
56             if(HSSFDateUtil.isCellDateFormatted(cell)){
57                 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
58                 return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
59             }
60             return df.format(cell.getNumericCellValue());
61         case HSSFCell.CELL_TYPE_STRING:
62             System.out.println(cell.getStringCellValue());
63             return cell.getStringCellValue();
64         case HSSFCell.CELL_TYPE_FORMULA:
65             return cell.getCellFormula();
66         case HSSFCell.CELL_TYPE_BLANK:
67             return "";
68         case HSSFCell.CELL_TYPE_BOOLEAN:
69             return cell.getBooleanCellValue() + "";
70         case HSSFCell.CELL_TYPE_ERROR:
71             return cell.getErrorCellValue() + "";
72         }
73         return "";
74     }
75     /*public static String formateDate(HSSFWorkbook wb,HSSFCell hssfCell){
76         //HSSFWorkbook wb = new HSSFWorkbook();  
77         CreationHelper createHelper=wb.getCreationHelper();
78         HSSFCellStyle cellStyle = wb.createCellStyle();  
79         cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
80         //HSSFSheet sheet = wb.createSheet("format sheet");  
81         //HSSFDataFormat format = wb.createDataFormat();  
82         //HSSFRow row = sheet.createRow(0);  
83         //HSSFCell cell = row.createCell(0);  
84         //cell = row.getCell(2);
85         hssfCell.setCellValue(formatCell(hssfCell));
86         hssfCell.setCellStyle(cellStyle);  
87         return hssfCell.getStringCellValue();
88     }
89 */    
90 }