实现从数据库导出到excel

实现这个功能可以通过jxl,或这poi 来实现，这里通过poi来实现在struts2导出。

1：ExportUtils.java

2: action.java 

3:需要的jar包：

poi-3.2-FINAL-20081019.jar

poi-contrib-3.2-FINAL-20081019.jar

poi-scratchpad-3.2-FINAL-20081019.jar

1：ExportUtils.java

package com.wsbalance.util;

import java.lang.reflect.Method;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import com.wsbalance.pojo.Performance;

public class ExportUtils {

/**

* 设置sheet表头信息

* @author David

* @param headersInfo

* @param sheet

*/

public static void outputHeaders(String[] headersInfo,HSSFSheet sheet ){

HSSFRow row = sheet.createRow(0);

for (int i = 0; i < headersInfo.length; i++) {

sheet.setColumnWidth(i, 4000);

row.createCell(i).setCellValue(headersInfo[i]);

}

}

public static void outputColumns(String[] headersInfo,

List columnsInfo,HSSFSheet sheet,int rowIndex ){

HSSFRow row ;

//循环插入多少行

for (int i = 0; i < columnsInfo.size(); i++) {

row = sheet.createRow(rowIndex+i);

Object obj = columnsInfo.get(i);

//循环每行多少列

//for (int j = 0; j < headersInfo.length; j++) {

Performance p=(Performance) columnsInfo.get(i);

row.createCell(0).setCellValue(p.getAgname());

row.createCell(1).setCellValue(p.getAgwxnum());

row.createCell(2).setCellValue(p.getMoney());

row.createCell(3).setCellValue(p.getTeam());

row.createCell(4).setCellValue(p.getPersonmoney());

row.createCell(5).setCellValue(p.getMoneyperson());

//}

}

}

}

2:在action中写实现downexcel方法，实现从服务器到本地一定要用response输出流

public void downexcel() throws IOException{

response.setContentType("application/octet-stream");

response.setHeader("Content-Disposition", "attachment;filename=Achievement.xls");

HSSFWorkbook  wb=new HSSFWorkbook();

HSSFSheet sheet=wb.createSheet("sheet0");

String[] title = {"姓名","微信号","团队业绩","团队奖金","个人业绩","个人奖金"};

Listlp=achievementService.getPerformanceAll();

ExportUtils.outputHeaders(title, sheet);

ExportUtils.outputColumns(title, lp, sheet, 1);

ServletOutputStream out=response.getOutputStream();

wb.write(out);

out.flush();

out.close();

}