java excel 导出

public class ExcelUtils {
/**
* 对象反射
*/
public static Object getFieldValueByName(String fieldName, Object o) {
try {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter, new Class[] {});
Object value = method.invoke(o, new Object[] {});
return value;
} catch (Exception e) {
return null;
}
}


/**
* 生成Excel
*/
public static void write(OutputStream outputStream, List<?> lists, List<Grid> grids) throws IOException {
// 初始一个workbook
HSSFWorkbook workbook = new HSSFWorkbook();


// 每个sheet表存60000条数据
int sheetNum = lists.size() / 60000;


// 循环创建多个sheet
for (int sheetIndex = 0; sheetIndex < sheetNum + 1; sheetIndex++) {
HSSFSheet sheet = workbook.createSheet("sheet" + sheetIndex);


// 创建表头
HSSFRow rowTitle = sheet.createRow(0);
int rowNum = 0;
for (Grid grid : grids) {
HSSFCell cell = rowTitle.createCell(rowNum);
cell.setCellValue(grid.getText());
rowNum++;
}


//数据量
int dataNum = 0;
if(sheetIndex == sheetNum){
dataNum = lists.size()-sheetIndex*60000;
}else{
dataNum = 60000;
}

// 创建多行
for (int rowIndex = 0; rowIndex < dataNum; rowIndex++) {
HSSFRow row = sheet.createRow(rowIndex + 1);


Object list = lists.get(rowIndex + sheetIndex*60000);
// 创建多列
int cellnum = 0;
for (Grid grid : grids) {
HSSFCell cell = row.createCell(cellnum);
Object value = getFieldValueByName(grid.getId(), list);
if (value == null) {
cell.setCellValue("");
} else {
cell.setCellValue(value.toString());
}


cellnum++;
}
}
}


workbook.write(outputStream);
}


/**
* 根据模板生成Excel
*/
public static void templateWrite(OutputStream outputStream, HttpServletResponse response, HttpServletRequest request, List<Dealer> lists, String templatePath) throws IOException {
//获取所读取excel模板的对象
File fi = new File(templatePath); 
if(!fi.exists()){ 
return;  
}   
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));  
// 初始一个workbook
HSSFWorkbook workbook = new HSSFWorkbook(fs);   
// 获取sheet
HSSFSheet sheet = workbook.getSheetAt(0);
//获取标题
HSSFRow rowTitle = sheet.getRow(0);
HSSFCellStyle style = workbook.createCellStyle();
rowTitle.setRowStyle(style);
 
// 创建多行
int rowIndex = 1;
for (Dealer list : lists) {
HSSFRow row = sheet.createRow(rowIndex);


// 创建多列
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(list.getCode());
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue(list.getName());
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue(list.getShortName());
HSSFCell cell3 = row.createCell(3);
cell3.setCellValue(list.getAddDate().toString());
HSSFCell cell4 = row.createCell(4);
cell4.setCellValue(list.getNote());

rowIndex++;
}


   workbook.write(outputStream);  
}


/**
* 下载
*/
private static void download(String path, HttpServletResponse response) {
try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}


/**
* 生成Excel
*/
public static void ExcelExport(List<?> lists, List<Grid> grids, HttpServletResponse response, HttpServletRequest request) {
OutputStream outputStream = null;
try {
String rootPath = request.getSession().getServletContext().getRealPath("/");
String path = rootPath + "Download";
File filePath =new File(path);    
//如果文件夹不存在则创建    
if  (!filePath .exists()  && !filePath .isDirectory())      
{       
   filePath .mkdir();    
} 

String fileName = filePath + "\\" + (new Date()).getTime() + ".xls";
File file = new File(fileName);
outputStream = new FileOutputStream(file);
//生成Excel
write(outputStream, lists, grids);


//弹出下载框
download(fileName, response);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

/**
* 根据模板生成Excel
*/
public static void TemplateExcelExport(List<Dealer> lists, HttpServletResponse response, HttpServletRequest request) {
OutputStream outputStream = null;
try {
//模板路径
String templatePath = request.getSession().getServletContext().getRealPath("/")+ "ExcelTemplate\\Excel模板.xls"; 

//生成的Excel路径
String rootPath = request.getSession().getServletContext().getRealPath("/");
String path = rootPath + "Download";
File filePath =new File(path);    
//如果文件夹不存在则创建    
if  (!filePath .exists()  && !filePath .isDirectory())      
{       
   filePath .mkdir();    
} 
String fileName = filePath + "\\" + (new Date()).getTime() + ".xls";
File file = new File(fileName);
outputStream = new FileOutputStream(file);

templateWrite(outputStream, response, request, lists, templatePath);

//弹出下载框
download(fileName, response);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}