POI+Struts2+maven excel 导出数据

这个是转载的，直接贴代码    ：

在pom.xml 文件中依赖

                <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.9</version>
</dependency>

ExcelUtil 工具类：

package com.ylink.aps.util;


import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * Excel导出工具类
 * @author qi.deng
 * 2017-06-05
 */
public class ExcelUtil {
private XSSFWorkbook wb = null;
 
    private XSSFSheet sheet = null;
 
    /**
     * @param wb
     * @param sheet
     */
    public ExcelUtil(XSSFWorkbook wb, XSSFSheet sheet)
    {
        this.wb = wb;
        this.sheet = sheet;
    }
 
    /**
     * 合并单元格后给合并后的单元格加边框
     *
     * @param region
     * @param cs
     */
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)
    {
 
        int toprowNum = region.getFirstRow();
        for (int i = toprowNum; i <= region.getLastRow(); i++)
        {
            XSSFRow row = sheet.getRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)
            {
                XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
                // (short) j);
                cell.setCellStyle(cs);
            }
        }
    }
 
    /**
     * 设置表头的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getHeadStyle()
    {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格的背景颜色为淡蓝色
        cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容显示不下时自动换行
        // 设置单元格字体样式
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }
 
    /**
     * 设置表体的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getBodyStyle()
    {
        // 创建单元格样式
        XSSFCellStyle contentStyle = wb.createCellStyle();
        contentStyle.setAlignment(CellStyle.ALIGN_LEFT);
        contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        contentStyle.setBorderTop(CellStyle.BORDER_THIN);
        contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
        contentStyle.setBorderRight(CellStyle.BORDER_THIN);
        contentStyle.setTopBorderColor(IndexedColors.BLACK.index);
        contentStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        contentStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        contentStyle.setRightBorderColor(IndexedColors.BLACK.index);
 
        return contentStyle;
    }
}

Action 代码：

@Action(value = "exportExcel", results = { 
@Result(name = "success", type = "stream", params = { "contentType",  
       "application/octet-stream;charset=ISO8859-1", "inputName",  
       "inputStream", "contentDisposition",  
       "attachment;filename=\"${downloadFileName}\"", "bufferSize", "1024" })})
    public String exportExcel() {
    if(model == null){
model = new ClearSettlementIndirectModel();
}
ClearSettleSearch clearSettleSearch = model.getClearSettleSearch();
if(clearSettleSearch == null){
clearSettleSearch = new ClearSettleSearch();
}
Date startDate = clearSettleSearch.getSettleDate().getStart();
Date endDate = clearSettleSearch.getSettleDate().getEnd();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        try {
            String fileName = "aps-mch-cls" + "-" + sdf.format(startDate) + "-" + sdf.format(endDate) + ".xls";
            ServletOutputStream outputStream = response.getOutputStream();
            String[] titles = { "结算编号", "商户编号", "商户名称","结算金额","银行名称","账户类别","户名","账号" };
            response.setHeader("Content-Disposition", "attachment;filename="+fileName);
            this.exportExcelto(titles, outputStream,clearSettleSearch);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    
public void exportExcelto(String[] titles, OutputStream outputStream, ClearSettleSearch clearSettleSearch) {
ListPage<ClearSettleSearch> page = mechSettlementQueryAppService.getList(null,clearSettleSearch);
List<ClearSettleSearch> list = page.getList();
if (null != list && list.size() > 0) {
// 创建一个workbook 对应一个excel应用文件
XSSFWorkbook workBook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = workBook.createSheet("非直清结算数据");
sheet.setColumnWidth(0, 3000); // 调整第一列宽度
sheet.setColumnWidth(1, 3000); // 调整第二列宽度
sheet.setColumnWidth(2, 3000); // 调整第三列宽度
sheet.setColumnWidth(3, 3000); // 调整第四列宽度
sheet.setColumnWidth(4, 7000); // 调整第二列宽度
sheet.setColumnWidth(5, 2000); // 调整第二列宽度
sheet.setColumnWidth(6, 7000); // 调整第二列宽度
sheet.setColumnWidth(7, 6000); // 调整第二列宽度
ExcelUtil exportUtil = new ExcelUtil(workBook, sheet);
XSSFCellStyle headStyle = exportUtil.getHeadStyle();
XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
// 构建表头
XSSFRow headRow = sheet.createRow(0);
XSSFCell cell = null;
for (int i = 0; i < titles.length; i++) {
cell = headRow.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(titles[i]);
}
// 构建表体数据
if (list != null && list.size() > 0) {
for (int j = 0; j < list.size(); j++) {
XSSFRow bodyRow = sheet.createRow(j + 1);
cell = bodyRow.createCell(0);
cell.setCellStyle(bodyStyle);
cell.setCellValue(list.get(j).getId());
cell = bodyRow.createCell(1);
cell.setCellStyle(bodyStyle);
cell.setCellValue(list.get(j).getMchCode());
cell = bodyRow.createCell(2);
cell.setCellStyle(bodyStyle);
cell.setCellValue(list.get(j).getMchName());


cell = bodyRow.createCell(3);
cell.setCellStyle(bodyStyle);
cell.setCellValue(list.get(j).getAmountSettle());


cell = bodyRow.createCell(4);
cell.setCellStyle(bodyStyle);
try {
cell.setCellValue(BankcardHelper.getBankInfoById(list.get(j).getBackType()).getBankName().toString());
} catch (Exception e) {
e.printStackTrace();
}


cell = bodyRow.createCell(5);
cell.setCellStyle(bodyStyle);
cell.setCellValue(AccountType.valueOf(list.get(j).getAccountType()).toString());


cell = bodyRow.createCell(6);
cell.setCellStyle(bodyStyle);
cell.setCellValue(list.get(j).getCardName());


cell = bodyRow.createCell(7);
cell.setCellStyle(bodyStyle);
cell.setCellValue(list.get(j).getCardNo());


}
}
try {
workBook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

 jsp 代码：

<script>

function aa(){
var start = $($(".date")[0]).val();
var end = $($(".date")[1]).val();
window.location.href='<%=request.getContextPath()%>'+'/cls/clearsettle/exportExcel.do?model.clearSettleSearch.settleDate.start='+start+'&model.clearSettleSearch.settleDate.end='+end;
}
</script>

<button type="button" class="btn btn-primary btn-sm pull-right" οnclick="aa()">
<i class="fa fa-search"></i>
导出Excel
</button>

希望对大家有用。

补充一下， @Result(name = "success", type = "stream", params = { "contentType",  
       "application/octet-stream;charset=ISO8859-1", "inputName",  
       "inputStream", "contentDisposition",  
       "attachment;filename=\"${downloadFileName}\"", "bufferSize", "1024" })})

里面的 application/octet-stream 是在不知道导出的是什么文件的情况下使用的，如果知道要导出的文件是Excel，那么将这里改成application/vnd.ms-excel 就行了。