Java 利用poi生成excel表格

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

所需jar包，如下所示

写一个excel工具类 ExcelUtils .java

import java.lang.reflect.Field;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class ExcelUtils {
/**
* 导出excel
* @param headerName （excel列名称）
* @param headerKey （导出对象属性名）
* @param sheetName （excel 页签名称）
* @param dataList （导出的数据）
* @return
*/
public static HSSFWorkbook createExcel(String[] headerName, String[] headerKey, String sheetName, List dataList) {
try {
if (headerKey.length <= 0) {
return null;
}
if (dataList.size() <= 0) {
return null;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet;
if ((sheetName == null) || (sheetName.equals("")))
sheet = wb.createSheet("Sheet1");
else {
sheet = wb.createSheet(sheetName);
}
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment((short)2);
HSSFCell cell = null;
if (headerName.length > 0) {
for (int i = 0; i < headerName.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerName[i]);
cell.setCellStyle(style);}
}
int n = 0;
HSSFCellStyle contextstyle = wb.createCellStyle();
contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00_);(#,##0.00)"));HSSFCellStyle contextstyle1 = wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
contextstyle1.setDataFormat(format.getFormat("@"));HSSFCell cell0 = null;
HSSFCell cell1 = null;for (Iterator localIterator = dataList.iterator(); localIterator.hasNext();) {
Object obj = localIterator.next();
Field[] fields = obj.getClass().getDeclaredFields();
row = sheet.createRow(n + 1);
for (int j = 0; j < headerKey.length; j++) {
if (headerName.length <= 0) {
cell0 = row.createCell(j);
cell0.setCellValue(headerKey[j]);
cell0.setCellStyle(style);}
for (int i = 0; i < fields.length; i++) {
if (fields[i].getName().equals(headerKey[j])) {
fields[i].setAccessible(true);
if (fields[i].get(obj) == null) {
row.createCell(j).setCellValue("");
break;
}
if ((fields[i].get(obj) instanceof Number)) {
cell1 = row.createCell(j);
cell1.setCellType(0);
cell1.setCellStyle(contextstyle);
cell1.setCellValue(Double.parseDouble(fields[i].get(obj).toString()));
break;
}
if ("".equals(fields[i].get(obj))) {
cell1 = row.createCell(j);
cell1.setCellStyle(contextstyle1);
row.createCell(j).setCellValue("");
cell1.setCellType(1);
break;
}
row.createCell(j).setCellValue(fields[i].get(obj).toString());
break;
}}
}
n++;
}
for (int i = 0; i < headerKey.length; i++) {
sheet.setColumnWidth(i, headerKey[i].getBytes().length*2*256);
}
HSSFWorkbook localHSSFWorkbook1 = wb;
return localHSSFWorkbook1;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
}
}
}

添加一个vo，studentVo.java

public class StudentVo {
private int id;private String sex;private String name;private String grade;public String getId() {
return id;
}public void setId(String id) {
this.id = id;
}public String getSex() {
return sex;
}public void setSex(String sex) {
this.sex = sex;
}public String getName() {
return name;
}public void setName(String name) {
this.name = name;
}public String getGrade() {
return grade;
}public void setGrade(String grade) {
this.grade = grade;
}
}

在controller类中添加导出excel接口，如下所示

/**
* 国网数据导出
* @param request
* @param response
*/
@RequestMapping("/exportGWDataManageList")
public void exportGWDataManageList(HttpServletRequest request, HttpServletResponse response){
try{
List<StudentVo> voList = new ArrayList<StudentVo>();
StudentVo vo = new StudentVo();
vo.setId("1");
vo.setSex("男");
vo.setName("张三");
vo.setGrade("二年级");
voList.add(vo);vo = new StudentVo();
vo.setId("2");
vo.setSex("女");
vo.setName("李四");
vo.setGrade("一年级");
voList.add(vo);vo = new StudentVo();
vo.setId("3");
vo.setSex("男");
vo.setName("王五");
vo.setGrade("三年级");
voList.add(vo);String[] headerName = { "序号","性别", "姓名", "年级"};
String[] headerKey = { "id","sex", "name", "grade"};
HSSFWorkbook wb = ExcelUtils.createExcel(headerName, headerKey, "年数据管理", voList);
if (wb == null) {
return;
}
response.setContentType("application/vnd.ms-excel");
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
Date date = new Date();
String str = sdf.format(date);
String fileName = "学生信息管理" + str;
response.setHeader("Content-disposition",
"attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
OutputStream ouputStream = response.getOutputStream();
ouputStream.flush();
wb.write(ouputStream);
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}