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;    }}