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;
}
