Java怎么导出Excel

Java  /  管理员 发布于 8年前   150

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的详细内容，更多请关注其它相关文章！