java將數據庫裏的數據導出到excel

package com.hui10.app.controller.merchant;


import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;


import javax.servlet.http.HttpServletResponse;


import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import com.hui10.app.common.web.BaseController;


import java.io.BufferedOutputStream;
import java.lang.reflect.Field;


@Controller
public class TestController extends BaseController {
/**
* 測試生成excel表格
*/
@RequestMapping(value = "/testhh/download", produces = { "application/json;charset=UTF-8" }, method = RequestMethod.GET)
@ResponseBody
public ModelAndView downloadFile(HttpServletResponse response) {
List<User> userlList = new ArrayList<User>();
User user=new User(1,"張三","男",39);
userlList.add(user);
user=new User(2,"如花","女",21);
userlList.add(user);
ExportExcel<User> ee= new ExportExcel<User>();
String[] headers = { "序號", "姓名", "性別", "年齡" };
String fileName = "用戶信息表";
ee.exportExcel(headers,userlList,fileName,response);


return null;
}
}


class ExportExcel<T> {
public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {
// 聲明一個工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一個表格
XSSFSheet sheet = workbook.createSheet(fileName);
// 設置表格默認列寬度爲15個字節
sheet.setDefaultColumnWidth((short) 20);
// 產生表格標題行
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
try {
// 遍歷集合數據，產生數據行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根據javabean屬性的先後順序，動態調用getXxx()方法得到屬性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判斷值的類型後進行強制類型轉換
String textValue = null;
// 其它數據類型都當作字符串簡單處理
if(value != null && value != ""){
textValue = value.toString();
}
if (textValue != null) {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
getExportedFile(workbook, fileName,response);
} catch (Exception e) {
e.printStackTrace();
} 
}
   
//產生輸出
public void getExportedFile(XSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {
BufferedOutputStream fos = null;
try {
String fileName = name + ".xlsx";
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));
fos = new BufferedOutputStream(response.getOutputStream());
workbook.write(fos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
fos.close();
}
}
}
}
class User{
private int id;
private String name;
private String sex;
private int age;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public User(int id, String name, String sex, int age) {
super();
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}

}

實際測試結果如下：

打開下載好的excel表格如下：

參考地址：https://blog.csdn.net/u011900448/article/details/53097382

https://www.cnblogs.com/xdcr/p/6564963.html

點擊打開鏈接