简单的Excel导出公共方法类

1.Excel的导出自己写的小的公共类方法，以下代码是公共类

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
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.HSSFRichTextString;
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;




//将数据以Excel的形式输出到指定的IO设备上
public class ExportExcelUtil {


/*
* @param title  Excel表格标题名
* @param headers  表格属性列名二维数组{英文列名，中文列头}
* @param dataset  List<Map>数据集合
* @param out  与输出设备关联的流对象，可以将Excel文档导出到本地或网络中
*
*/



public static <R> void exportExcel(String title,String[][] headers,List<R> dataSet,String fileName ,OutputStream fos){

//判断参数是否为空
if(title == null || title.equals("")){
System.out.println("Excel表格的标题名为空");
}
if(headers == null || headers.equals("")){
System.out.println("没有定义表头字段集合");
}
if(dataSet == null || dataSet.equals("")){
System.out.println("没有定义导出数据集合");
}
if(fileName == null || fileName.equals("")){
System.out.println("没有定义输出Excel的文件名");
}
//创建一个Excel
HSSFWorkbook workbook = new HSSFWorkbook();
try{


//创建一张工作表
HSSFSheet sheet = workbook.createSheet(title);
//创建工作表的第一行(表头)
HSSFRow row = sheet.createRow(0);
sheet.setDefaultColumnWidth(20);
//将dataSet中的数据放入迭代器中
/*Iterator it = dataSet.iterator();*/

//工作簿字体、样式设置
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);   //设置字体颜色为黑色
HSSFCellStyle cStyle = workbook.createCellStyle();
cStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);   //设置表标题样式居中

//将表头数据写入工作表的第一行中，逐个单元格写入
for(int i=0;i < headers.length;i++){
HSSFCell cell = row.createCell(i);   //创建单元格
HSSFRichTextString text = new HSSFRichTextString(headers[i][1]);
cell.setCellValue(text);
}

//将数据写入表中
for(int i=0;i<dataSet.size();i++){   //行
try{
row = sheet.createRow(i+1);
int index=0;  //行内单元格个数初始化
for(int j=0;j<headers.length;j++){    //行中的各单元格
String uppFistName = headers[j][0];

String lowFistName = uppFistName.substring(0, 1).toLowerCase() + uppFistName.substring(1);
Field field = ExportExcelUtil .getDeclaredField(dataSet.get(i),lowFistName);
String type = field.getGenericType().toString();
String textValue = "";

if (type.equals("class java.lang.String")){
Method m = dataSet.get(i).getClass().getMethod("get" + uppFistName);
textValue = (String) m.invoke(dataSet.get(i));
}else if (type.equals("class java.lang.Boolean")){
Method m = dataSet.get(i).getClass().getMethod("get" + uppFistName);
Boolean textValueStr = (Boolean) m.invoke(dataSet.get(i));
if(textValueStr != null){
textValue =String.valueOf(textValueStr);
}
}else if (type.equals("class java.lang.Float")){
Method m = dataSet.get(i).getClass().getMethod("get" + uppFistName);
Float textValueStr = (Float) m.invoke(dataSet.get(i));
if(textValueStr != null){
textValue =String.valueOf(textValueStr);
}
}else if (type.equals("class java.util.Date")){
Method m = dataSet.get(i).getClass().getMethod("get" + uppFistName);
Date textValueDate =(Date) m.invoke(dataSet.get(i));
if(textValueDate != null){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
textValue = sdf.format(textValueDate);
}

}

//if (textValue != null && !textValue.trim().equals("")) {
HSSFCell cell = row.createCell(index++);
HSSFRichTextString text = new HSSFRichTextString(textValue);
cell.setCellValue(text);
//}

}
}catch(Exception e){
e.printStackTrace();
}
}

//写入流
workbook.write(fos);


}catch(Exception e){
e.printStackTrace();
}finally {
try {
if(workbook != null){
workbook.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}



}

/** 
     * 循环向上转型, 获取对象的 DeclaredField 
     * @param object : 子类对象 
     * @param fieldName : 父类中的属性名 
     * @return 父类中的属性对象 
     */  
      
    public static Field getDeclaredField(Object object, String fieldName){  
        Field field = null ;  
          
        Class<?> clazz = object.getClass() ;  
          
        for(; clazz != Object.class ; clazz = clazz.getSuperclass()) {  
            try {  
                field = clazz.getDeclaredField(fieldName) ;  
                return field ;  
            } catch (Exception e) {  
                //这里甚么都不要做！并且这里的异常必须这样写，不能抛出去。  
                //如果这里的异常打印或者往外抛，则就不会执行clazz = clazz.getSuperclass(),最后就不会进入到父类中了  
                  
            }   
        }  
      
        return null;  
    }     
}
2.实现代码如下:
@GetMapping("/exportExcel")
public ResponseEntity<byte[]> exportExcel(@ModelAttribute Entity entity) {
List<Entity> data = entityService
.queryList(entity);
String title = "excel标题";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String headers[][] = {{"Id","ID"},{"Name","姓名"},{"Birthday","生日"},{"Gender","性别"}};
String fileName = "用户表"  + sdf.format(new Date()) + ".xls";
ByteArrayOutputStream os = new ByteArrayOutputStream();

try {
//FileOutputStream os = new FileOutputStream("E://"+fileName+".xls");
ExportExcelUtil.exportExcel(title, headers, data, fileName,os);
byte[] bytes = os.toByteArray();
HttpHeaders httpHeaders = new HttpHeaders();
httpHeaders.setContentType(MediaType.parseMediaType("application/x-msdownload"));
httpHeaders.setContentDispositionFormData("attachment",
new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
return new ResponseEntity<byte[]>(bytes, httpHeaders, HttpStatus.CREATED);
}catch(Exception e){
e.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}