poi 导出excl

package com.thinkgem.jeesite.modules.publicoption.util;

import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;

import com.thinkgem.jeesite.modules.publicoption.entity.PublicOpinion;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;


public class ExcelUtil {

    private final static String regxpForHtml = "<([^>]*)>"; // 过滤所有以<开头以>结尾的标签

    private final static String regxpForImgTag = "<\\s*img\\s+([^>]*)\\s*>"; // 找出IMG标签

    private final static String regxpForImaTagSrcAttrib = "src=\"([^\"]+)\""; // 找出IMG标签的SRC属性

    /**
     * 导出用户的所有列表到excel
     *
     * [@param](http://my.oschina.net/u/2303379) outputStream 输出流
     */
    public  void exportPublicOpinionExcel(List<PublicOpinion> list, ServletOutputStream outputStream) {
        try {
            //1、创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1、创建合并单元格对象
         //   CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 6);//起始行号，结束行号，起始列号，结束列号

            //1.2、头标题样式
            //HSSFCellStyle style1 = createCellStyle(workbook, (short)16);

            //1.3、列标题样式
            HSSFCellStyle style2 = createCellStyle(workbook, (short)13);

            //2、创建工作表
            HSSFSheet sheet = workbook.createSheet("回复情况");
            //2.1、加载合并单元格对象
            //sheet.addMergedRegion(cellRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(30);

            //3、创建行
            //3.1、创建头标题行；并且设置头标题
           // HSSFRow row1 = sheet.createRow(0);
            //HSSFCell cell1 = row1.createCell(0);

            //加载单元格样式
           // cell1.setCellStyle(style1);

          //  cell1.setCellValue("回复情况");

            //3.2、创建列标题行；并且设置列标题
            HSSFRow row2 = sheet.createRow(0);
            String[] titles = {"编号","领导是否批示", "标题", "日期", "来源" ,"部门","相关链接","回复"};
            for(int i = 0; i < titles.length; i++){
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(style2);
                cell2.setCellValue(titles[i]);
            }

            //4、操作单元格；将用户列表写入excel
            if(list != null){
                for(int j = 0; j < list.size(); j++){
                    HSSFRow row = sheet.createRow(j+1);
                    HSSFCell cell11 = row.createCell(0);
                    cell11.setCellValue(j+1);

                    HSSFCell cell12 = row.createCell(1);
                    cell12.setCellValue("");

                    HSSFCell cell13 = row.createCell(2);
                    cell13.setCellValue(list.get(j).getTitle());

                    HSSFCell cell14 = row.createCell(3);
                    cell14.setCellValue(list.get(j).getReleaseTime());

                    HSSFCell cell15 = row.createCell(4);
                    cell15.setCellValue(list.get(j).getCollectOrigin());

                    HSSFCell cell16 = row.createCell(5);
                    cell16.setCellValue(list.get(j).getInvolveTownName());

                    HSSFCell cell7 = row.createCell(6);
                    cell7.setCellValue(list.get(j).getOriginUrl());

                    HSSFCell cell8 = row.createCell(7);

                    cell8.setCellValue(Html2Text(list.get(j).getReplyContent()));

                }
            }
            //5、输出
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建单元格样式
     * [@param](http://my.oschina.net/u/2303379) workbook 工作簿
     * [@param](http://my.oschina.net/u/2303379) fontSize 字体大小
     * [@return](http://my.oschina.net/u/556800) 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体
        font.setFontHeightInPoints(fontSize);
        //加载字体
        style.setFont(font);
        return style;
    }

    public static   String Html2Text(String inputString) {
        String htmlStr = inputString; // 含html标签的字符串
        String textStr = "";
        java.util.regex.Pattern p_script;
        java.util.regex.Matcher m_script;
        java.util.regex.Pattern p_style;
        java.util.regex.Matcher m_style;
        java.util.regex.Pattern p_html;
        java.util.regex.Matcher m_html;

        try {
            String regEx_script = "<[\\s]*?script[^>]*?>[\\s\\S]*?<[\\s]*?\\/[\\s]*?script[\\s]*?>"; // 定义script的正则表达式{或<script[^>]*?>[\\s\\S]*?<\\/script>
            // }
            String regEx_style = "<[\\s]*?style[^>]*?>[\\s\\S]*?<[\\s]*?\\/[\\s]*?style[\\s]*?>"; // 定义style的正则表达式{或<style[^>]*?>[\\s\\S]*?<\\/style>
            // }
            String regEx_html = "<[^>]+>"; // 定义HTML标签的正则表达式

            inputString = inputString.replaceAll("\\r\\n\\t","");

            p_script = Pattern.compile(regEx_script, Pattern.CASE_INSENSITIVE);
            m_script = p_script.matcher(htmlStr);
            htmlStr = m_script.replaceAll(""); // 过滤script标签

            p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE);
            m_style = p_style.matcher(htmlStr);
            htmlStr = m_style.replaceAll(""); // 过滤style标签

            p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE);
            m_html = p_html.matcher(htmlStr);
            htmlStr = m_html.replaceAll(""); // 过滤html标签

            textStr = htmlStr;

        } catch (Exception e) {
            System.err.println("Html2Text: " + e.getMessage());
        }

        return textStr;// 返回文本字符串
    }

}

转载于:https://my.oschina.net/u/2310996/blog/737668