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 }
