Java怎么导出Excel

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基础教程栏目。

以上就是Java怎么导出Excel的详细内容，更多内容请关注技术你好其它相关文章！