POI操作excel示例工具類 – JAVA編程語言程序開發技術文章

由於近期項目要用到excel來轉存頁面中的表單數據,對poi操作excel進行瞭一番瞭解,寫瞭以下,但總覺的不是很好
特此:發佈在此供大傢評論,廣義集思,還望多多指教
   1. workBook處理類

[java] 
/**
 * Excel WorkBook工具類
 * @author dsy
 * @version 1.0
 */ 
public class ExcelWorkBook { 
     
    public HSSFWorkbook workbook = null; 
    public static HSSFWorkbook workbookTemp = null; 
    //設置當前workbookName 
    private String workbookName = null; 
    private HSSFSheet sheet = null; 
    private FileOutputStream fileOut; 
     
    public ExcelWorkBook() { 
        if(workbook != null) { 
            workbook = null; 
        } 
        workbook = workbookTemp; 
    } 
     
    public ExcelWorkBook(String workbookName) { 
        workbook = workbookTemp; 
        setWorkbookName(workbookName); 
    } 
    public String getWorkbookName() { 
        return workbookName; 
    } 
 
    public void setWorkbookName(String workbookName) { 
        workbookName = workbookName; 
    } 
 
    public HSSFSheet getSheet() { 
        sheet = workbook.createSheet(getWorkbookName()); 
        return sheet; 
    } 
     
    /**
     * 用於stylUtils的所需要的workbook必須項所做的處理
     * @return
     */ 
    public static HSSFWorkbook getWorkbook() { 
        return workbookTemp; 
    } 
 
    public static void setWorkbook(HSSFWorkbook workbook) { 
        workbookTemp = workbook; 
    } 
     
    /**
     * 輸入當前WorkBook為下載臨時文件記錄
     * @param excelName
     */ 
    public void writerFileStream(String excelName) { 
        try { 
            fileOut = new FileOutputStream(excelName); 
            workbook.write(fileOut); 
        } catch (FileNotFoundException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        } catch (IOException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        } finally { 
            try { 
                fileOut.flush(); 
                fileOut.close(); 
                if(workbook != null) { 
                    workbook = null; 
                } 
            } catch (IOException e) { 
                // TODO Auto-generated catch block 
                e.printStackTrace(); 
            } 
        } 
    } 
     

   2. Excel Row工具類

[java]
/**
 * Excel Row工具類
 * @author dsy
 * @version 1.0
 */ 
public class ExcelSheetRow { 
     
     
    public ExcelSheetRow() { 
        // TODO Auto-generated constructor stub 
    } 
 
    public static HSSFSheet sheet = null; 
    /**
     * 設置當前Sheet名字
     */ 
    private static String sheetName = null; 
    private static HSSFRow row = null; 
     
     
    /**
     * 創建當前標題行
     * @param sheet
     * @return
     */ 
    public static HSSFRow createCurrSheetTitle(ExcelWorkBook work) { 
       HSSFSheet sheet = work.getSheet(); 
       row = sheet.createRow(0); 
       return row; 
    } 
     
    /**
     * 創建當前excel記錄內容
     * @param sheet
     * @param i
     * @return
     */ 
    public static HSSFRow createCurrSheetRecord(ExcelWorkBook work,int i) { 
        HSSFSheet sheet = work.getSheet(); 
        row = sheet.createRow(i+1); 
        return row; 
    }  
 
    public static String getSheetName() { 
        return sheetName; 
    } 
 
    public static void setSheetName(String sheetName) { 
        ExcelSheetRow.sheetName = sheetName; 
    } 
 

3.  Excel Cell工具類

[java] 
/**
 * Excel Cell工具類
 * @author dsy
 * @version 1.0
 */ 
public class ExcelSheetCell { 
     
    private static HSSFRow row = null; 
    private static HSSFCell cell = null; 
     
     
     
    /**
     * 用於產生當前excel標題
     * @param sheet [當前工作表單]
     * @param firstRowValue [標題數組]
     * @param style [當前單元格風格]
     */ 
    public static void createCurrRowTitle(ExcelSheetRow sheetRow,ExcelWorkBook work ,String[] firstRowValue,HSSFCellStyle style) { 
        row = sheetRow.createCurrSheetTitle(work); 
        for (int i = 0; i < firstRowValue.length; i++) { 
            cell = row.createCell((short) i); 
            cell.setCellStyle(style); 
            cell.setEncoding(HSSFCell.ENCODING_UTF_16); 
            cell.setCellValue(firstRowValue[i]); 
        } 
    } 
     
    /**
     * 用於生成excel當前記錄內容,標題除外
     * @param sheet [當前工作表單]
     * @param beanList [當前數據列表,i=Object[]]
     * @param style [當前單元格風格]
     */ 
    public static void createCurrRowRecord(ExcelSheetRow sheetRow,ExcelWorkBook work,List beanList,HSSFCellStyle style) { 
        Object[] obj = null; 
        for (int i = 0; i < beanList.size(); i++) { 
            row = sheetRow.createCurrSheetRecord(work,i); 
            obj = (Object[]) beanList.get(i); 
            if (obj != null) { 
                createExcelCell(row, obj,style); 
            } 
        } 
    } 
     
    /**
     * 需要以數組的方式提供當前每條記錄
     * 通過數組自動判斷有多少列,生成當前行
     */ 
    private static void createExcelCell(HSSFRow row, Object[] obj,HSSFCellStyle style) { 
        try { 
            for (int i = 0; i < obj.length; i++) { 
                try { 
                    if (obj[i].toString() != null) { 
 
                        cell = row.createCell((short) i); 
                        cell.setCellStyle(style); 
                        cell.setEncoding(HSSFCell.ENCODING_UTF_16); 
                        cell.setCellValue(obj[i].toString()); 
                    } 
                } catch (NullPointerException e) { 
                    continue; 
                } 
 
            } 
        } catch (Exception ex) { 
            System.out.print(ex); 
        } 
    } 

  4. Excel Style風格工具類

[java] 
/**
 * Excel Style風格工具類
 * @author dsy
 * @version 1.0
 */ 
public class ExcelCellStyleUtils{ 
 
    //標題樣式 
    public static HSSFCellStyle titleStyle; 
    //時間樣式 
    public static HSSFCellStyle dataStyle; 
    //單元格樣式 
    public static HSSFCellStyle nameStyle; 
    //超鏈接樣式 
    public static HSSFCellStyle linkStyle; 
    public static HSSFFont font; 
     
    public ExcelCellStyleUtils(ExcelWorkBook work) { 
        titleStyle = linkStyle(work.getWorkbook()); 
        dataStyle = dataStyle(work.getWorkbook()); 
        nameStyle = nameStyle(work.getWorkbook()); 
        linkStyle = linkStyle(work.getWorkbook()); 
    } 
    /**
     * 超鏈接樣式
     * @return HSSFCellStyle
     */ 
    private static HSSFCellStyle linkStyle(HSSFWorkbook work) { 
        HSSFCellStyle linkStyle = work.createCellStyle(); 
          linkStyle.setBorderBottom((short)1); 
          linkStyle.setBorderLeft((short)1); 
          linkStyle.setBorderRight((short)1); 
          linkStyle.setBorderTop((short)1); 
          linkStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); 
          linkStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
          HSSFFont font = work.createFont(); 
          font.setFontName(HSSFFont.FONT_ARIAL); 
          font.setUnderline((byte)1); 
          font.setColor(HSSFColor.BLUE.index); 
          linkStyle.setFont(font); 
          return linkStyle; 
    } 
     
    /**s
     * 單元格樣式
     * @return HSSFCellStyle
     */ 
    private static HSSFCellStyle nameStyle(HSSFWorkbook work) { 
        HSSFCellStyle nameStyle = work.createCellStyle(); 
          nameStyle.setBorderBottom((short)1); 
          nameStyle.setBorderLeft((short)1); 
          nameStyle.setBorderRight((short)1); 
          nameStyle.setBorderTop((short)1); 
          nameStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); 
          nameStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
          return nameStyle; 
    } 
     
    /**
     * 時間樣式
     * @return HSSFCellStyle
     */ 
    private static HSSFCellStyle dataStyle(HSSFWorkbook work) { 
        HSSFCellStyle dataStyle = work.createCellStyle(); 
          dataStyle.setBorderBottom((short)1); 
          dataStyle.setBorderLeft((short)1); 
          dataStyle.setBorderRight((short)1); 
          dataStyle.setBorderTop((short)1); 
          dataStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); 
          dataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
          dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
          return dataStyle; 
    } 
     
    /**
     * 標題樣式
     * @return HSSFCellStyle
     */ 
    private static HSSFCellStyle titleStyle(HSSFWorkbook work) { 
        HSSFCellStyle titleStyle = work.createCellStyle(); 
        font = work.createFont(); 
        font.setItalic(true); 
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
        font.setColor(HSSFColor.BLUE.index); 
          titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); 
          titleStyle.setBorderLeft((short)1); 
          titleStyle.setBorderRight((short)1); 
          titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); 
          titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); 
          titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
          return titleStyle; 
    } 

  5. 創建Excel工廠類

[html] 
/** 
 * 創建Excel工具類 
 * @author Administrator 
 * 
 */ 
public class ExcelUtilFactory { 
     
    private static ExcelUtilFactory instance = null; 
    private static HttpServletRequest excelRequest = null; 
    private static HttpServletResponse excelResponse = null; 
     
    public static ExcelUtilFactory getInstance(HttpServletRequest request, 
            HttpServletResponse response) { 
        if(instance == null) { 
            instance = new ExcelUtilFactory(); 
        } 
        excelRequest = request; 
        excelResponse = response; 
        return instance; 
    } 
     
    public static void outputExcel(String excelName, List list, String[] firstRowValue) { 
        ExcelWorkBook work = new ExcelWorkBook(); 
        work.setWorkbookName(excelName); 
        ExcelSheetRow sheetRow = new ExcelSheetRow(); 
        ExcelSheetCell sheetCell = new ExcelSheetCell(); 
        ExcelCellStyleUtils util = new ExcelCellStyleUtils(work); 
        sheetCell.createCurrRowTitle(sheetRow, work, firstRowValue, util.titleStyle); 
        sheetCell.createCurrRowRecord(sheetRow, work, list, util.nameStyle); 
        String realPath = getExcelRealPath(excelName); 
//      String realPath = "e:/temp/testRealPath_2.xls"; 
        work.writerFileStream(realPath); 
        downloadFile(realPath); 
    } 
     
    private static String getExcelRealPath(String excelName) { 
        String realPath = excelRequest.getRealPath("/UploadFile"); 
        File excelFile = new File(realPath); 
        if(!excelFile.exists()) { 
            excelFile.mkdirs(); 
        } 
        excelName = realPath+ "\\" + excelName+".xls"; 
        return  excelName; 
    }  
     
    private static void downloadFile(String strfileName) { 
        try { 
            // 獲得ServletContext對象 
            if(excelFileNotFund(strfileName)) { 
                throw new IllegalArgumentException("File=["+strfileName+"] not fund file path"); 
            } 
            // 取得文件的絕對路徑 
            File excelFile = getExcelDownloadPath(strfileName); 
            putResponseStream(strfileName, excelFile); 
        } catch (IOException e) { 
            e.printStackTrace(); 
        }  
    } 
     
    private static File getExcelDownloadPath(String excelName) { 
//      String realPath = excelRequest.getRealPath("/UploadFile"); 
//      excelName = realPath+ "\\" + excelName; 
//      excelName = replaceRNAll(excelName); 
        File excelFile = new File(excelName); 
        return  excelFile; 
    } 
     
    //用傳入參數的判斷 
    private static boolean excelFileNotFund(String strfileName) { 
        return strfileName ==  null|| strfileName.equals(""); 
    } 
     
    /** 
     *  
     * @param strfileName : 文件名稱 
     * @param excelName  : 文件的相對路徑或絕對路徑 
     * @throws UnsupportedEncodingException 
     * @throws FileNotFoundException 
     * @throws IOException 
     */ 
    private static void putResponseStream(String strfileName, File excelName) 
            throws UnsupportedEncodingException, FileNotFoundException, 
            IOException { 
        strfileName = URLEncoder.encode(strfileName, "UTF-8"); 
        excelResponse.setHeader("Content-disposition","attachment; filename=" + strfileName); 
        excelResponse.setContentLength((int) excelName.length()); 
        excelResponse.setContentType("application/x-download"); 
        byte[] buffer = new byte[1024]; 
        int i = 0; 
        FileInputStream fis = new FileInputStream(excelName); 
        while ((i = fis.read(buffer)) > 0) { 
            JspWriter out = null; 
            excelResponse.getOutputStream().write(buffer, 0, i); 
        } 
    } 
     
    public static void main(String[] args) { 
        long beginTime = System.currentTimeMillis(); 
        System.out.println("開始時間:"+beginTime/1000); 
        List beanList = new ArrayList(); 
        String[] excelTitle = new String[10]; 
        excelTitle[0] = "編號"; 
        excelTitle[1] = "基金名稱"; 
        excelTitle[2] = "單位凈值(NAV)"; 
        excelTitle[3] = "日增長率(%)"; 
        excelTitle[4] = "累積凈值"; 
        excelTitle[5] = "編號"; 
        excelTitle[6] = "基金名稱"; 
        excelTitle[7] = "單位凈值(NAV)"; 
        excelTitle[8] = "日增長率(%)"; 
        excelTitle[9] = "累積凈值"; 
        String[] beanArr = new String[10]; 
        for (int i = 0; i < 55000; i++) { 
            beanArr[0] = String.valueOf(i+1); 
            beanArr[1] = "基金A"+i; 
            beanArr[2] = "1.0427"; 
            beanArr[3] = "-2.7514%"; 
            beanArr[4] = "1.1558"; 
            beanArr[5] = String.valueOf(i+1); 
            beanArr[6] = "基金A"+i; 
            beanArr[7] = "1.0427"; 
            beanArr[8] = "-2.7514%"; 
            beanArr[9] = "1.1558"; 
            beanList.add(beanArr); 
        } 
        outputExcel("今天測試_factory", beanList, excelTitle); 
        long endTime = System.currentTimeMillis(); 
        System.out.println("測試55000,總計"+(endTime-beginTime)/1000+"秒,用時"); 
    } 

發佈留言