java 匯出excel

標籤：util   object   圖片   void   with   tin   odi   控制器   控制   

首先進入控制器

@RequestMapping(value="account/exportWithPopularizeDetail")public void exportWithPopularizeDetail(HttpServletRequest request, HttpServletResponse response,String fnickName,String fpuserNickName){FileInputStream in = null;OutputStream out = null;File file = null;try {response.setCharacterEncoding("UTF-8");response.setContentType("application/x-excel;charset=utf-8");String dateTime = DateUtil.DateToStr(new Date(), "yyyyMMddHHmmss");String fileName = "推廣明細統計記錄" + dateTime + ".xls";response.setHeader("Content-Disposition","attachment;fileName="+URLEncoder.encode(fileName,"UTF-8"));String proPath = request.getSession().getServletContext().getRealPath("/");String path = proPath+File.separator+"tempFile";file = new File(path);if(!file.isDirectory()){file.mkdir();}file = new File(path+File.separator+String.valueOf(System.currentTimeMillis())+".xls");if(!file.exists()){file.createNewFile();}userBo.exportPopularizeDetailExcel(fnickName,fpuserNickName,new FileOutputStream(file));out = response.getOutputStream();in = new FileInputStream(file);byte[] b = new byte[1024];int i=0;while((i=in.read(b))>0){out.write(b, 0, i);}out.flush();} catch (IOException e) {e.printStackTrace();}finally{try{if(in!=null){in.close();}if(out!=null){out.close();}if(file!=null){file.delete();}} catch (IOException e) {e.printStackTrace();}}}

　服務層　條件查詢出所需要列印的list

public void exportPopularizeDetailExcel(String fnickName,String fpuserNickName,OutputStream out){String title = "推廣明細統計";    //excel 頭String[] headers = {"暱稱","推廣","推廣日期"};String pattern = "yyyy-MM-dd HH:mm:ss";UserInfoDTO userInfoDTO = new UserInfoDTO();userInfoDTO.setFnickName(fnickName);userInfoDTO.setFrealName(fpuserNickName);List<PopularizedtlDTO> list = this.getpopularizeDetail(userInfoDTO);ExcelExportUtils<PopularizedtlDTO> util = new ExcelExportUtils<PopularizedtlDTO>();util.exportExcel(title, headers, list, out, pattern);}            

　　工具類匯出excel到臨時檔案的 檔案輸出資料流裡

public class ExcelExportUtils<T> {        @SuppressWarnings({ "resource"})    public void exportExcel(String title, String[] headers,            Collection<T> dataset, OutputStream out, String pattern) {        // 聲明一個工作薄        HSSFWorkbook workbook = new HSSFWorkbook();        // 產生一個表格        HSSFSheet sheet = workbook.createSheet(title);        // 設定表格預設列寬度為15個位元組        sheet.setDefaultColumnWidth(15);        // 產生一個樣式        HSSFCellStyle style = workbook.createCellStyle();        // 設定這些樣式        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);        style.setBorderRight(HSSFCellStyle.BORDER_THIN);        style.setBorderTop(HSSFCellStyle.BORDER_THIN);        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        // 產生一個字型        HSSFFont font = workbook.createFont();        font.setColor(HSSFColor.VIOLET.index);        font.setFontHeightInPoints((short) 12);        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        // 把字型應用到當前的樣式        style.setFont(font);        // 產生並設定另一個樣式        HSSFCellStyle style2 = workbook.createCellStyle();        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        // 產生另一個字型        HSSFFont font2 = workbook.createFont();        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);        // 把字型應用到當前的樣式        style2.setFont(font2);        // 聲明一個畫圖的頂級管理器        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();        // 定義注釋的大小和位置,詳見文檔        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,                0, 0, 0, (short) 4, 2, (short) 6, 5));        // 設定注釋內容        comment.setString(new HSSFRichTextString("可以在POI中添加註釋！"));        // 設定注釋作者，當滑鼠移動到儲存格上是可以在狀態列中看到該內容.        comment.setAuthor("leno");        // 產生表格標題列        HSSFRow row = sheet.createRow(0);        for (int i = 0; i < headers.length; i++) {            HSSFCell cell = row.createCell(i);            cell.setCellStyle(style);            HSSFRichTextString text = new HSSFRichTextString(headers[i]);            cell.setCellValue(text);        }        // 遍曆集合資料，產生資料行        Iterator<T> it = dataset.iterator();        int index = 0;        while (it.hasNext()) {            index++;            row = sheet.createRow(index);            T t = (T) it.next();            // 利用反射，根據javabean屬性的先後順序，動態調用getXxx()方法得到屬性值            Field[] fields = t.getClass().getDeclaredFields();            for (int i = 0; i < fields.length; i++) {                HSSFCell cell = row.createCell(i);                cell.setCellStyle(style2);                Field field = fields[i];                field.setAccessible(true);                try {                    if(field.getName().equals("stop")){                        break;                    }                    Object value = field.get(t);                                        // 判斷值的類型後進行強制類型轉換                    String textValue = null;                     if (value instanceof Integer) {                     int intValue = (Integer) value;                     cell.setCellValue(intValue);                     } else if (value instanceof Float) {                     float fValue = (Float) value;                     cell.setCellValue(new HSSFRichTextString(                             String.valueOf(fValue)));                     } else if (value instanceof Double) {                     double dValue = (Double) value;                     cell.setCellValue(new HSSFRichTextString(                             String.valueOf(dValue)));                     } else if (value instanceof Long) {                     long longValue = (Long) value;                     cell.setCellValue(longValue);                     }                    if (value instanceof Boolean) {                        boolean bValue = (Boolean) value;                        textValue = "是";                        if (!bValue) {                            textValue = "否";                        }                        cell.setCellValue(textValue);                    } else if (value instanceof Date) {                        Date date = (Date) value;                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);                        textValue = sdf.format(date);                        cell.setCellValue(textValue);                    }else{                        // 其它資料類型都當作字串簡單處理                        textValue = value==null?"":value.toString();                        cell.setCellValue(textValue);                    }                    // 如果不是圖片資料，就利用Regex判斷textValue是否全部由數字組成//                    if (textValue != null) {//                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");//                        Matcher matcher = p.matcher(textValue);//                        if (matcher.matches()) {//                            // 是數字當作double處理//                            cell.setCellValue(Double.parseDouble(textValue));//                        } else {//                            HSSFRichTextString richString = new HSSFRichTextString(//                                    textValue);//                            HSSFFont font3 = workbook.createFont();//                            font3.setColor(HSSFColor.BLUE.index);//                            richString.applyFont(font3);//                            cell.setCellValue(richString);//                        }//                    }                } catch (SecurityException e) {                    e.printStackTrace();                } catch (IllegalArgumentException e) {                    e.printStackTrace();                } catch (IllegalAccessException e) {                    e.printStackTrace();                } finally {                    // 清理資源                }            }        }        try {            workbook.write(out);        } catch (IOException e) {            e.printStackTrace();        }finally{            try {                out.flush();                out.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }}

 

java 匯出excel