所需jar包
1.前端调用方法：
2.action入口方法：
public void lackExamineeExportExcel() {
try{
ArrayList fieldData = this.resultExamineeServiceImpl.listResultExamineeByIsProcess();
ArrayList<String> fieldName = this.resultExamineeServiceImpl.getExcelName();
OutputStream os = this.getResponse().getOutputStream(); // 取得输出流
String fileName = System.currentTimeMillis()+"缺勤人员信息表.xls";
fileName = FileUtils.encodeFilename(fileName, getRequest());
this.getResponse().reset(); // 清空输出流
this.getResponse().setContentType("application/vnd.ms-excel"); // 定义输出类型
this.getResponse().setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));// 中文问题
ExcelFileGenerator xlsExportBean = new ExcelFileGenerator(fieldName, fieldData);
xlsExportBean.expordExcel(os); // 调用生成Excel文件bean
System.setOut(new PrintStream(os));
os.flush();
if (os != null){
os.close();
}
}catch(Exception e) {
throw new RuntimeException("导出错误");
}
}
3.业务层方法
public ArrayList<String> getExcelName() {
ArrayList<String> rsList = new ArrayList();
String[] strArray = {"院系名称","姓名","性别","学号","一级学科代码","一级学科名称","专业代码","专业名称"};
if (strArray.length > 0) {
for (int i = 0; i < strArray.length; i++) {
rsList.add(strArray[i]);
}
}
return rsList;
}
public ArrayList listResultExamineeByIsProcess() {
List<ResultExaminee1> resultExaminee1List = this.resultExaminee1DAO.findByProperty("isProcess", false);
ArrayList dataList = new ArrayList();
if (null != resultExaminee1List && resultExaminee1List.size()>0) {
for (ResultExaminee1 resultExaminee1 : resultExaminee1List) {
List tempList=new ArrayList();
// 数据顺序应该跟字段顺序保持一致
tempList.add(resultExaminee1.getCollegeName());
tempList.add(resultExaminee1.getExamineeName());
tempList.add(resultExaminee1.getExamineeSex());
tempList.add(resultExaminee1.getLicence());
tempList.add(resultExaminee1.getSubjectCode());
tempList.add(resultExaminee1.getSubjectName());
tempList.add(resultExaminee1.getSpecialtyCode());
tempList.add(resultExaminee1.getSpecialtyName());
//tempList.add(resultExaminee1.getCreateDate());
dataList.add(tempList);
}
return dataList;
}
return dataList;
}
4.设置下载文件中文件的名称FileUtils
import java.net.URLEncoder;
import java.util.regex.Pattern;
import org.apache.commons.fileupload.util.mime.MimeUtility;
import org.apache.commons.lang.StringUtils;
/**
* 设置下载文件中文件的名称
*
* @param filename
* @param request
* @return
*/
public static String encodeFilename(String fileName, HttpServletRequest request) {
/**
* 获取客户端浏览器和操作系统信息
* 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
* 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
*/
String agent = request.getHeader("USER-AGENT");
try {
if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {
String newFileName = URLEncoder.encode(fileName, "UTF-8");
newFileName = StringUtils.replace(newFileName, "+", "%20");
if (newFileName.length() > 150) {
newFileName = new String(fileName.getBytes("GB2312"), "ISO8859-1");
newFileName = StringUtils.replace(newFileName, " ", "%20");
}
return newFileName;
}
if ((agent != null) && (-1 != agent.indexOf("Mozilla")))
return MimeUtility.decodeText(fileName);
return fileName;
} catch (Exception ex) {
return fileName;
}
}
}
5.核心工具类ExcelFileGenerator
/**
* 系统数据导出Excel 生成器
* @version 1.0
*/
package com.sinotn.utils;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
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.hssf.util.HSSFColor;
/**
* @Description Excel导出
* @Copyright: Copyright (c) 2018 Company:
* @author Libin
* @date 2018年2月1日 上午11:09:38
* @version V1.0
*/
public class ExcelFileGenerator {
private final int SPLIT_COUNT = 1000; //Excel每个工作簿的行数
private ArrayList<String> fieldName = null; //excel标题数据集
private List<List<String>> fieldData = null; //excel数据内容
private HSSFWorkbook workBook = null;
/**
* 构造器
* @param fieldName 结果集的字段名
* @param data
*/
public ExcelFileGenerator(ArrayList<String> fieldName, ArrayList<List<String>> fieldData) {
this.fieldName = fieldName;
this.fieldData = fieldData;
}
/**
* 创建HSSFWorkbook对象
* @return HSSFWorkbook
*/
public HSSFWorkbook createWorkbook() {
workBook = new HSSFWorkbook();//创建一个工作薄对象
int rows = fieldData.size();//总的记录数
int sheetNum = 0; //指定sheet的页数
if (rows % SPLIT_COUNT == 0) {
sheetNum = rows / SPLIT_COUNT;
} else {
sheetNum = rows / SPLIT_COUNT + 1;
}
for (int i = 1; i <= sheetNum; i++) {//循环2个sheet的值
HSSFSheet sheet = workBook.createSheet("Page " + i);//使用workbook对象创建sheet对象
HSSFRow headRow = sheet.createRow((short) 0); //创建行，0表示第一行（本例是excel的标题）
for (int j = 0; j < fieldName.size(); j++) {//循环excel的标题
HSSFCell cell = headRow.createCell( j);//使用行对象创建列对象，0表示第1列
/**************对标题添加样式begin********************/
//设置列的宽度/
sheet.setColumnWidth(j, 6000);
HSSFCellStyle cellStyle = workBook.createCellStyle();//创建列的样式对象
HSSFFont font = workBook.createFont();//创建字体对象
//字体颜色变红
font.setColor(HSSFColor.RED.index);
font.setFontHeightInPoints((short) 16);//设置字体大小
//如果font中存在设置后的字体，并放置到cellStyle对象中，此时该单元格中就具有了样式字体
cellStyle.setFont(font);
/**************对标题添加样式end********************/
//添加样式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if(fieldName.get(j) != null){
//将创建好的样式放置到对应的单元格中
cell.setCellStyle(cellStyle);
cell.setCellValue((String) fieldName.get(j));//为标题中的单元格设置值
}else{
cell.setCellValue("-");
}
}
//分页处理excel的数据，遍历所有的结果
for (int k = 0; k < (rows < SPLIT_COUNT ? rows : SPLIT_COUNT); k++) {
if (((i - 1) * SPLIT_COUNT + k) >= rows)//如果数据超出总的记录数的时候，就退出循环
break;
HSSFRow row = sheet.createRow((short) (k + 1));//创建1行
//分页处理，获取每页的结果集，并将数据内容放入excel单元格
ArrayList<String> rowList = (ArrayList<String>) fieldData.get((i - 1) * SPLIT_COUNT + k);
for (int n = 0; n < rowList.size(); n++) {//遍历某一行的结果
HSSFCell cell = row.createCell( n);//使用行创建列对象
if(rowList.get(n) != null){
cell.setCellValue((String) rowList.get(n).toString());
}else{
cell.setCellValue("");
}
}
}
}
return workBook;
}
public void expordExcel(OutputStream os) throws Exception {
workBook = createWorkbook();
workBook.write(os);//将excel中的数据写到输出流中，用于文件的输出
os.close();
}
}
