Java 利用poi產生excel表格

標籤：內容   image   ##   response   png   for   osi   ace   try   

所需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();}}

　　

 頁面只有一個產生excel按鈕，如下所示

點擊按鈕產生excel，內容如下所示

 

Java 利用poi產生excel表格