poi java生成excel_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 voList = new ArrayList();

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();

}

}

页面只有一个生成excel按钮，如下所示

点击按钮生成excel，内容如下所示