【个人经验】导出表格数据为excel的过程

====================================点击导出按钮之后直接调用的方法===数据是通过session在查询后保存的 在这里取出来======================

====================================session的弊端是一段时间以后session会自动清空 也可以选择导出的时候重新查询一次数据===================

/**

* 导出excel
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(params = "action=exportPortNetFlow")
public void exportPortNetFlow(HttpServletRequest request, HttpServletResponse response,String[] svgs) throws Exception{
response.setContentType("application/msexcel;charset=UTF-8");

Map<String,PortNetFlowView> portFlowList = (Map<String,PortNetFlowView>) request.getSession().getAttribute("portFlowList");
List<PortNetFlowView> list = new ArrayList<PortNetFlowView>();
for(Map.Entry<String, PortNetFlowView> entry : portFlowList.entrySet()) {
PortNetFlowView pfv = entry.getValue();
list.add(pfv);
/*list.add(pfv.getRoutName());
list.add(pfv.getPortName());
list.add(pfv.getMaxInputFlux());
list.add(pfv.getMaxOutputFlux());
list.add(pfv.getMinInputFlux());
list.add(pfv.getMaxOutputFlux());
list.add(pfv.getAvgInputFlux());
list.add(pfv.getAvgOutputFlux());
list.add(pfv.getIntPortUR());
list.add(pfv.getOutPortUR());
list.add(pfv.getMaxInputUR());
list.add(pfv.getMaxOutputUR());*/
}

String title = "端口性能报表";
      
String[] hearders = new String[] {"设备","设备IP", "端口备注","端口", "带宽(MB)","入流量峰值(Kbps)", "出流量峰值(Kbps)","入流量谷值(Kbps)","出流量谷值(Kbps)","入流量均值(Kbps)","出流量均值(Kbps)","入均值利用率(%)","出均值利用率(%)","入峰值利用率(%)","出峰值利用率(%)"};//表头数组     
String[] fields = new String[] {"routName","ipaddress","note","portName","bandWidth","maxInputFlux", "maxOutputFlux", "minInputFlux", "minOutputFlux", 
"avgInputFlux","avgOutputFlux","intPortUR","outPortUR","maxInputUR","maxOutputUR"};
TableData tabledata = ExcelUtils.createTableData(list, ExcelUtils.createTableHeader( hearders), fields);
GridReportBase report = new GridReportBase(request, response);
report.exportToExcel(title, "",svgs, tabledata);
}

}

======================被相同颜色标注的就是所需连接的类文件   TableData.java=================

/*
 * 文件名：TableData.java
 * 版权：
 * 描述：
 * 修改人：
 * 修改时间：
 * 修改内容：
 */
package cn.com.zyserv.portal.common.report;


import java.util.LinkedList;
import java.util.List;


public class TableData {
/**
* 字符串型
*/
public static final int STYLE_TYPE_STRING = 0;


/**
* 浮点型，保留2位小数
*/
public static final int STYLE_TYPE_FLOAT_2 = 1;


/**
* 浮点型，保留3位小数
*/
public static final int STYLE_TYPE_FLOAT_3 = 2;


/**
* 整形
*/
public static final int STYLE_TYPE_INTEGER = 3;


/**
* 红色背景
*/
public static final int STYLE_TYPE_RED_BG = 10;


/**
* 黄色背景
*/
public static final int STYLE_TYPE_YELLOW_BG = 11;


/**
* 绿色背景
*/
public static final int STYLE_TYPE_GREEN_BG = 12;


private String sheetTitle;

private TableHeaderMetaData header;


private LinkedList<TableDataRow> rows;


private int totalRows;

public TableData(){}


public TableData(TableHeaderMetaData header) {
this.header = header;
rows = new LinkedList<TableDataRow>();
}


public TableHeaderMetaData getTableHeader() {
return header;
}


public void addRow(TableDataRow row) {
rows.add(row);
}


public List<TableDataRow> getRows() {
return rows;
}


public TableDataRow getRowAt(int index) {
return rows.get(index);
}


public int getRowCount() {
return rows.size();
}


public int getTotalRows() {
return totalRows;
}


public void setTotalRows(int totalRows) {
this.totalRows = totalRows;
}


public void setHeader(TableHeaderMetaData header) {
this.header = header;
}


public void setRows(LinkedList<TableDataRow> rows) {
this.rows = rows;
}


public String getSheetTitle() {
return sheetTitle;
}


public void setSheetTitle(String sheetTitle) {
this.sheetTitle = sheetTitle;
}


}

==================================ExcelUtils.java=================================================

/*
 * 文件名：ExcelUtils.java
 * 版权：
 * 描述：
 * 修改人：
 * 修改时间：
 * 修改内容：
 */
package cn.com.zyserv.portal.common.report;


import java.beans.PropertyDescriptor;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.commons.lang.StringUtils;
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.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;


public class ExcelUtils {

/**
* JavaBean转Map
* @param obj
* @return
*/
public static Map<String, Object> beanToMap(Object obj) {  
        Map<String, Object> params = new HashMap<String, Object>(0);  
        try {  
            PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();  
            PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);  
            for (int i = 0; i < descriptors.length; i++) {  
                String name = descriptors[i].getName();  
                if (!StringUtils.equals(name, "class")) {  
                    params.put(name, propertyUtilsBean.getNestedProperty(obj, name));  
                }  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return params;  
    }

/**
* 创建普通表头
* @param list 表头名称列表
* @return
*/
public static TableHeaderMetaData createTableHeader(List<String> list){
TableHeaderMetaData headMeta = new TableHeaderMetaData();
       for(String title : list){
        TableColumn tc = new TableColumn();
       tc.setDisplay(title);
       headMeta.addColumn(tc);
       }
       return headMeta;
}

/**
* 创建普通表头
* @param titls 表头名称数组
* @return
*/
public static TableHeaderMetaData createTableHeader(String[] titls){
TableHeaderMetaData headMeta = new TableHeaderMetaData();
for(String title : titls){
TableColumn tc = new TableColumn();
tc.setDisplay(title);
headMeta.addColumn(tc);
}
return headMeta;
}

/**
* 创建合并表头
* @param parents 父表头数组
* @param children 子表头数组
* @return
*/
public static TableHeaderMetaData createTableHeader(List<String> parents,List<String[]> children){
TableHeaderMetaData headMeta = new TableHeaderMetaData();
TableColumn parentColumn = null;
TableColumn sonColumn = null;
for (int i = 0; i < parents.size(); i++) {
parentColumn = new TableColumn();
parentColumn.setDisplay(parents.get(i));
if (children != null && children.get(i) != null) {
for (int j = 0; j < children.get(i).length; j++) {
sonColumn = new TableColumn();
sonColumn.setDisplay(children.get(i)[j]);
parentColumn.addChild(sonColumn);
}
}
headMeta.addColumn(parentColumn);
}
return headMeta;
}
public static TableHeaderMetaData createTableHeader(String[] parents,String[][] children){
TableHeaderMetaData headMeta = new TableHeaderMetaData();
TableColumn parentColumn = null;
TableColumn sonColumn = null;
for (int i = 0; i < parents.length; i++) {
parentColumn = new TableColumn();
parentColumn.setDisplay(parents[i]);
if (children != null && children[i] != null) {
for (int j = 0; j < children[i].length; j++) {
sonColumn = new TableColumn();
sonColumn.setDisplay(children[i][j]);
parentColumn.addChild(sonColumn);
}
}
headMeta.addColumn(parentColumn);
}
return headMeta;
}

    /**
     * 拼装数据
     * 
     * @param list 数据集
     * @param headMeta 表头
     * @param fields 对象或Map属性数组（注意：顺序要与表头标题顺序对应，如数据集为List<Object[]>，则该参数可以为null）
     * @return TableData
     */
@SuppressWarnings("unchecked")
public static TableData createTableData(List list,TableHeaderMetaData headMeta,String[] fields){


        TableData td = new TableData(headMeta);
        TableDataRow row = null;
        if(list != null && list.size()>0){
        if(list.get(0).getClass().isArray()){//数组类型
                for (Object obj : list){
                row = new TableDataRow(td);
            for(Object o : (Object[])obj){
    row.addCell(o);
    }
                    td.addRow(row);
                }
        }else{//JavaBean或Map类型
                for (Object obj : list){
                row = new TableDataRow(td);
                Map<String, Object> map = (obj instanceof Map)?(Map<String, Object>)obj:beanToMap(obj);
                    for(String key : fields){
                        row.addCell(map.get(key));
                    }
                    td.addRow(row);
                }
        }
        }
        return td;
    }
    
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest,
HSSFWorkbook srcwb, HSSFSheet src) {
if (src == null || dest == null)
return;


dest.setAlternativeExpression(src.getAlternateExpression());
dest.setAlternativeFormula(src.getAlternateFormula());
dest.setAutobreaks(src.getAutobreaks());
dest.setDialog(src.getDialog());
if (src.getColumnBreaks() != null) {
for (int col : src.getColumnBreaks()) {
dest.setColumnBreak((short) col);
}
}
dest.setDefaultColumnWidth(src.getDefaultColumnWidth());
dest.setDefaultRowHeight(src.getDefaultRowHeight());
dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints());
dest.setDisplayGuts(src.getDisplayGuts());
dest.setFitToPage(src.getFitToPage());
dest.setHorizontallyCenter(src.getHorizontallyCenter());
dest.setDisplayFormulas(src.isDisplayFormulas());
dest.setDisplayGridlines(src.isDisplayGridlines());
dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings());
dest.setGridsPrinted(src.isGridsPrinted());
dest.setPrintGridlines(src.isPrintGridlines());


for (int i = 0; i < src.getNumMergedRegions(); i++) {
Region r = src.getMergedRegionAt(i);
dest.addMergedRegion(r);
}


if (src.getRowBreaks() != null) {
for (int row : src.getRowBreaks()) {
dest.setRowBreak(row);
}
}
dest.setRowSumsBelow(src.getRowSumsBelow());
dest.setRowSumsRight(src.getRowSumsRight());


short maxcol = 0;
for (int i = 0; i <= src.getLastRowNum(); i++) {
HSSFRow row = src.getRow(i);
if (row != null) {
if (maxcol < row.getLastCellNum())
maxcol = row.getLastCellNum();
}
}
for (short col = 0; col <= maxcol; col++) {
if (src.getColumnWidth(col) != src.getDefaultColumnWidth())
dest.setColumnWidth(col, src.getColumnWidth(col));
dest.setColumnHidden(col, src.isColumnHidden(col));
}
}


public static String dumpCellStyle(HSSFCellStyle style) {
StringBuffer sb = new StringBuffer();
sb.append(style.getHidden()).append(",");
sb.append(style.getLocked()).append(",");
sb.append(style.getWrapText()).append(",");
sb.append(style.getAlignment()).append(",");
sb.append(style.getBorderBottom()).append(",");
sb.append(style.getBorderLeft()).append(",");
sb.append(style.getBorderRight()).append(",");
sb.append(style.getBorderTop()).append(",");
sb.append(style.getBottomBorderColor()).append(",");
sb.append(style.getDataFormat()).append(",");
sb.append(style.getFillBackgroundColor()).append(",");
sb.append(style.getFillForegroundColor()).append(",");
sb.append(style.getFillPattern()