java实现Excel数据导出：（推荐：java视频教程）
目前，比较常用的实现Java导入、导出Excel的技术有两种Jakarta POI和Java Excel
Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，在各种组件中目前只有用于操作Excel的HSSF相对成熟。
Jakarta POI HSSF API组件
HSSF（用于操作Excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。有以下几种对象：
基本操作步骤
首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。
基本操作步骤：
下面来看一个动态生成Excel文件的例子：
//创建HSSFWorkbook对象HSSFWorkbook wb = new HSSFWorkbook();//创建HSSFSheet对象HSSFSheet sheet = wb.createSheet("sheet0");//创建HSSFRow对象HSSFRow row = sheet.createRow(0);//创建HSSFCell对象HSSFCell cell=row.createCell(0);//设置单元格的值cell.setCellValue("单元格中的中文");//输出Excel文件FileOutputStream output=new FileOutputStream("d:\\workbook.xls");wkb.write(output);output.flush();HSSF读取文件同样还是使用这几个对象，只是把相应的createXXX方法变成了getXXX方法即可。可见只要理解了其中原理，不管是读还是写亦或是特定格式都可以轻松实现，正所谓知其然更要知其所以然。
导出表格的工具类：
excelUtil:
package com.zhl.push.Utils;import com.google.common.base.Strings;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.util.CellRangeAddress;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.OutputStream;import java.math.BigDecimal;import java.util.List;import java.util.Map;public class ExcelExportUtil { //表头 private String title; //各个列的表头 private String[] heardList; //各个列的元素key值 private String[] heardKey; //需要填充的数据信息 private List<Map> data; //字体大小 private int fontSize = 14; //行高 private int rowHeight = 30; //列宽 private int columWidth = 200; //工作表 private String sheetName = "sheet1"; public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String[] getHeardList() { return heardList; } public void setHeardList(String[] heardList) { this.heardList = heardList; } public String[] getHeardKey() { return heardKey; } public void setHeardKey(String[] heardKey) { this.heardKey = heardKey; } public List<Map> getData() { return data; } public void setData(List<Map> data) { this.data = data; } public int getFontSize() { return fontSize; } public void setFontSize(int fontSize) { this.fontSize = fontSize; } public int getRowHeight() { return rowHeight; } public void setRowHeight(int rowHeight) { this.rowHeight = rowHeight; } public int getColumWidth() { return columWidth; } public void setColumWidth(int columWidth) { this.columWidth = columWidth; } public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } /** * 开始导出数据信息 * */ public byte[] exportExport(HttpServletRequest request, HttpServletResponse response) throws IOException { //检查参数配置信息 checkConfig(); //创建工作簿 HSSFWorkbook wb = new HSSFWorkbook(); //创建工作表 HSSFSheet wbSheet = wb.createSheet(this.sheetName); //设置默认行宽 wbSheet.setDefaultColumnWidth(20); // 标题样式（加粗，垂直居中） HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 HSSFFont fontStyle = wb.createFont(); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontStyle.setBold(true); //加粗 fontStyle.setFontHeightInPoints((short)16); //设置标题字体大小 cellStyle.setFont(fontStyle); //在第0行创建rows (表标题) HSSFRow title = wbSheet.createRow((int) 0); title.setHeightInPoints(30);//行高 HSSFCell cellValue = title.createCell(0); cellValue.setCellValue(this.title); cellValue.setCellStyle(cellStyle); wbSheet.addMergedRegion(new CellRangeAddress(0,0,0,(this.heardList.length-1))); //设置表头样式，表头居中 HSSFCellStyle style = wb.createCellStyle(); //设置单元格样式 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置字体 HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) this.fontSize); style.setFont(font); //在第1行创建rows HSSFRow row = wbSheet.createRow((int) 1); //设置列头元素 HSSFCell cellHead = null; for (int i = 0; i < heardList.length; i++) { cellHead = row.createCell(i); cellHead.setCellValue(heardList[i]); cellHead.setCellStyle(style); } //设置每格数据的样式 （字体红色） HSSFCellStyle cellParamStyle = wb.createCellStyle(); HSSFFont ParamFontStyle = wb.createFont(); cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); ParamFontStyle.setColor(HSSFColor.DARK_RED.index); //设置字体颜色 (红色) ParamFontStyle.setFontHeightInPoints((short) this.fontSize); cellParamStyle.setFont(ParamFontStyle); //设置每格数据的样式2（字体蓝色） HSSFCellStyle cellParamStyle2 = wb.createCellStyle(); cellParamStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellParamStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont ParamFontStyle2 = wb.createFont(); ParamFontStyle2.setColor(HSSFColor.BLUE.index); //设置字体颜色 (蓝色) ParamFontStyle2.setFontHeightInPoints((short) this.fontSize); cellParamStyle2.setFont(ParamFontStyle2); //开始写入实体数据信息 int a = 2; for (int i = 0; i < data.size(); i++) { HSSFRow roww = wbSheet.createRow((int) a); Map map = data.get(i); HSSFCell cell = null; for (int j = 0; j < heardKey.length; j++) { cell = roww.createCell(j); cell.setCellStyle(style); Object valueObject = map.get(heardKey[j]); String value = null; if (valueObject == null) { valueObject = ""; } if (valueObject instanceof String) { //取出的数据是字符串直接赋值 value = (String) map.get(heardKey[j]); } else if (valueObject instanceof Integer) { //取出的数据是Integer value = String.valueOf(((Integer) (valueObject)).floatValue()); } else if (valueObject instanceof BigDecimal) { //取出的数据是BigDecimal value = String.valueOf(((BigDecimal) (valueObject)).floatValue()); } else { value = valueObject.toString(); } //设置单个单元格的字体颜色 if(heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")){ if((Long)map.get("ddNum")!=null){ if((Long)map.get("sjNum")==null){ cell.setCellStyle(cellParamStyle); } else if((Long) map.get("ddNum") != (Long) map.get("sjNum")){ if ((Long) map.get("ddNum") > (Long) map.get("sjNum")) { cell.setCellStyle(cellParamStyle); } if ((Long) map.get("ddNum") < (Long) map.get("sjNum")) { cell.setCellStyle(cellParamStyle2); } }else { cell.setCellStyle(style); } } } cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value); } a++; } //导出数据 try { //设置Http响应头告诉浏览器下载这个附件 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); OutputStream outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.close(); return wb.getBytes(); } catch (Exception ex) { ex.printStackTrace(); throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage()); } } /** * 检查数据配置问题 * * @throws IOException 抛出数据异常类 */ protected void checkConfig() throws IOException { if (heardKey == null || heardList.length == 0) { throw new IOException("列名数组不能为空或者为NULL"); } if (fontSize < 0 || rowHeight < 0 || columWidth < 0) { throw new IOException("字体、宽度或者高度不能为负值"); } if (Strings.isNullOrEmpty(sheetName)) { throw new IOException("工作表表名不能为NULL"); } }}service :
@Override public void queryProjectInfoBySchemeId(HttpServletResponse response, HttpServletRequest request, String schemeId, String pushDate) throws IOException { List<Map> maps = pushMonitorDao.queryProjectInfoBySchemeId(schemeId, pushDate); if(maps!=null && maps.size()>0){ String companyName = pushMonitorDao.queryCompanyNameBySchemeId(schemeId); String sheetTitle = companyName; String [] title = new String[]{"城市","项目名字","合同","实际"}; //设置表格表头字段 String [] properties = new String[]{"city","projectName","ddNum","sjNum"}; // 查询对应的字段 ExcelExportUtil excelExport2 = new ExcelExportUtil(); excelExport2.setData(maps); excelExport2.setHeardKey(properties); excelExport2.setFontSize(14); excelExport2.setSheetName(sheetTitle); excelExport2.setTitle(sheetTitle); excelExport2.setHeardList(title); excelExport2.exportExport(request, response); } }Excel格式
import java.io.ByteArrayOutputStream;import java.io.IOException;import java.lang.reflect.Field;import java.util.List;import java.util.logging.Level;import java.util.logging.Logger;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.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.util.CellRangeAddress;/** * @作者 yan * @创建日期 * @版本 V1.0 * @描述 Excel 导出通用工具类 */public class ExcelUtil { public static byte[] export(String sheetTitle, String[] title, List<Object> list) { HSSFWorkbook wb = new HSSFWorkbook();//创建excel表 HSSFSheet sheet = wb.createSheet(sheetTitle); sheet.setDefaultColumnWidth(20);//设置默认行宽 //表头样式（加粗，水平居中，垂直居中） HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //设置边框样式 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 HSSFFont fontStyle = wb.createFont(); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(fontStyle); //标题样式（加粗，垂直居中） HSSFCellStyle cellStyle2 = wb.createCellStyle(); cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 cellStyle2.setFont(fontStyle); //设置边框样式 cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //字段样式（垂直居中） HSSFCellStyle cellStyle3 = wb.createCellStyle(); cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 //设置边框样式 cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //创建表头 HSSFRow row = sheet.createRow(0); row.setHeightInPoints(20);//行高 HSSFCell cell = row.createCell(0); cell.setCellValue(sheetTitle); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(0,0,0,(title.length-1))); //创建标题 HSSFRow rowTitle = sheet.createRow(1); rowTitle.setHeightInPoints(20); HSSFCell hc; for (int i = 0; i < title.length; i++) { hc = rowTitle.createCell(i); hc.setCellValue(title[i]); hc.setCellStyle(cellStyle2); } byte result[] = null; ByteArrayOutputStream out = null; try { //创建表格数据 Field[] fields; int i = 2; for (Object obj : list) { fields = obj.getClass().getDeclaredFields(); HSSFRow rowBody = sheet.createRow(i); rowBody.setHeightInPoints(20); int j = 0; for (Field f : fields) { f.setAccessible(true); Object va = f.get(obj); if (null == va) { va = ""; } hc = rowBody.createCell(j); hc.setCellValue(va.toString()); hc.setCellStyle(cellStyle3); j++; } i++; } out = new ByteArrayOutputStream(); wb.write(out); result = out.toByteArray(); } catch (Exception ex) { Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); } finally{ try { if(null != out){ out.close(); } } catch (IOException ex) { Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); } finally{ try { wb.close(); } catch (IOException ex) { Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); } } } return result; }}更多java知识请关注java基础教程栏目。
