导出Excel

controller层


/**
* 导出到Excel
*
* @param request
* @param response
*/
@SuppressWarnings("unchecked")
@RequestMapping(params = "method=toExcelNo")
public void toExcelNo(HttpServletRequest request, HttpServletResponse response) {
try {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
} catch (UnsupportedEncodingException e2) {
e2.printStackTrace();
}



String fileName = request.getParameter("fileName");
fileName = (fileName == null) ? "充值卡" : fileName;
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
} catch (UnsupportedEncodingException e2) {
e2.printStackTrace();
}

response.reset();
response.setContentType("application/x-download;charset=gbk");
response.setHeader("Content-disposition", "attachment; filename="
+ fileName + ".xls");

HSSFWorkbook workbook  = excelExportCardNoService.createExcel(request, response);//excel工作薄对象

if (null == workbook) {
return;
}

try {
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}


service层



import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
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;


public class ExcelExportCardNoServiceImpl implements ExcelExportCardNoService {
private List cardList = null;// 充值卡记录

private CardInfo cardInfoDao;

public void setCardInfoDao(CardInfo cardInfoDao) {
this.cardInfoDao = cardInfoDao;
}

/**
* 初始化 */
public void initList(HttpServletRequest request,
HttpServletResponse response) {

StringBuffer hqlstr = new StringBuffer(
"SELECT new CardInfoD(......");
StringBuffer hql = new StringBuffer( "
  FROM  ... WHERE ......");

cardList = cardInfoDao.getPageCard (hqlstr + hql.toString());// 获得要导出的列表
}

/**
* 执行入口
*/
public HSSFWorkbook createExcel(HttpServletRequest request,
HttpServletResponse response) {
HSSFWorkbook workbook = null;
try {
/**
* step1:初始化数据
*/
initList(request, response);
/**
* step2:
* <p>
* 创建新的Excel工作簿
* <p>
*/
workbook = new HSSFWorkbook();
HSSFSheet sheet = null;
String currentSheetName = null;
currentSheetName = "充值卡";
sheet = workbook.createSheet(currentSheetName);
addRow(sheet, currentSheetName, request, response);
} catch (Exception e) {
System.out.println("已运行 excelCreate() : " + e);
}
return workbook;
}

/**
* 增加行
*/
public void addRow(HSSFSheet sheet, String currentSheetName,
HttpServletRequest request, HttpServletResponse response) {
/**
* step3:在索引1的位置创建行（menu菜单）
*/
addTopRow(sheet, currentSheetName);
/**
* step4:
*/

/**
* step5:从索引2(第三行)的位置填充记录
*/
HSSFRow row = null;

int j = 2;
if (null != cardList && cardList.size() > 0) {
CardInfoD card = null;
for (int i = 0; i < cardList.size(); i++) {
card = (CardInfoD) cardList.get(i);
row = sheet.createRow((short) j);
addCell(row, card);
j++;
}
}
}

/**
* 增加列
*/
public void addCell(HSSFRow row, Object cardObj) {
CardInfoD card = (CardInfoD) cardObj;


HSSFCell cell = null;
/**
* 第一列
*/
// 在索引0的位置创建单元格（左上端）
cell = row.createCell((short) 0);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue(card.getCardId());
/**
* 第二列
*/
cell = row.createCell((short) 1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(card.getCardNo()));
/**
* 第三列
*/
cell = row.createCell((short) 2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(card.getCardPwd()));

cell = row.createCell((short) 3);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(new HSSFRichTextString(card.getGoldMoney().toString()));

cell = row.createCell((short) 4);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(card.getCreateDate()+""));


......
}

/**
* excel头菜单
*
* @param sheet
*/
public void addTopRow(HSSFSheet sheet, String currentSheetName) {
HSSFRow row = sheet.createRow((short) 1);
createBasicMenuRow(row);//基本信息，固定列
}

/**
* 创建固定列
*
* @param row
*/
public void createBasicMenuRow(HSSFRow row) {
HSSFCell cell = null;

/**
* 第一列:序号
*/
cell = row.createCell((short) 0);// 创建单元格
cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
cell.setCellValue(new HSSFRichTextString("序号"));// 在单元格中输入一些内容
/**
* 第二列:卡号
*/
cell = row.createCell((short) 1);// 创建单元格
cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 定义单元格为字符串类型
cell.setCellValue(new HSSFRichTextString("卡号"));// 在单元格中输入一些内容
......
}

}