Java導(dǎo)出Excel數(shù)據(jù)的方法

在開發(fā)中我們經(jīng)常遇到把數(shù)據(jù)導(dǎo)出到Excel中的需求，這里簡單的描述下個(gè)人實(shí)現(xiàn)方式

Jakarta POI HSSF API組件

HSSF（用于操作Excel的組件）提供給用戶使用的對(duì)象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel對(duì)象，樣式和格式，還有輔助操作。有以下幾種對(duì)象：

基本操作步驟

首先，理解一下一個(gè)Excel的文件的組織形式，一個(gè)Excel文件對(duì)應(yīng)于一個(gè)workbook(HSSFWorkbook)，一個(gè)workbook可以有多個(gè)sheet（HSSFSheet）組成，一個(gè)sheet是由多個(gè)row（HSSFRow）組成，一個(gè)row是由多個(gè)cell（HSSFCell）組成。

基本操作步驟：

下面來看一個(gè)動(dòng)態(tài)生成Excel文件的例子：

//創(chuàng)建HSSFWorkbook對(duì)象
HSSFWorkbook wb = new HSSFWorkbook();
//創(chuàng)建HSSFSheet對(duì)象
HSSFSheet sheet = wb.createSheet("sheet0");
//創(chuàng)建HSSFRow對(duì)象
HSSFRow row = sheet.createRow(0);
//創(chuàng)建HSSFCell對(duì)象
HSSFCell cell=row.createCell(0);
//設(shè)置單元格的值
cell.setCellValue("單元格中的中文");
//輸出Excel文件
FileOutputStream output=new FileOutputStream("d:\\workbook.xls");
wkb.write(output);
output.flush();

HSSF讀取文件同樣還是使用這幾個(gè)對(duì)象，只是把相應(yīng)的createXXX方法變成了getXXX方法即可?？梢娭灰斫饬似渲性?，不管是讀還是寫亦或是特定格式都可以輕松實(shí)現(xiàn)，正所謂知其然更要知其所以然。

導(dǎo)出表格的工具類：

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;
    //各個(gè)列的表頭
    private String[] heardList;
    //各個(gè)列的元素key值
    private String[] heardKey;
    //需要填充的數(shù)據(jù)信息
    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;
    }

    /**
     * 開始導(dǎo)出數(shù)據(jù)信息
     *
     */
    public byte[] exportExport(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //檢查參數(shù)配置信息
        checkConfig();
        //創(chuàng)建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //創(chuàng)建工作表
        HSSFSheet wbSheet = wb.createSheet(this.sheetName);
        //設(shè)置默認(rèn)行寬
        wbSheet.setDefaultColumnWidth(20);

        // 標(biāo)題樣式（加粗，垂直居中）
        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);  //設(shè)置標(biāo)題字體大小
        cellStyle.setFont(fontStyle);

        //在第0行創(chuàng)建rows  (表標(biāo)題)
        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)));
        //設(shè)置表頭樣式，表頭居中
        HSSFCellStyle style = wb.createCellStyle();
        //設(shè)置單元格樣式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //設(shè)置字體
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        style.setFont(font);
        //在第1行創(chuàng)建rows
        HSSFRow row = wbSheet.createRow((int) 1);
        //設(shè)置列頭元素
        HSSFCell cellHead = null;
        for (int i = 0; i < heardList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(heardList[i]);
            cellHead.setCellStyle(style);
        }

        //設(shè)置每格數(shù)據(jù)的樣式 （字體紅色）
        HSSFCellStyle cellParamStyle = wb.createCellStyle();
        HSSFFont ParamFontStyle = wb.createFont();
        cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        ParamFontStyle.setColor(HSSFColor.DARK_RED.index);   //設(shè)置字體顏色 (紅色)
        ParamFontStyle.setFontHeightInPoints((short) this.fontSize);
        cellParamStyle.setFont(ParamFontStyle);
        //設(shè)置每格數(shù)據(jù)的樣式2（字體藍(lán)色）
        HSSFCellStyle cellParamStyle2 = wb.createCellStyle();
        cellParamStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellParamStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont ParamFontStyle2 = wb.createFont();
        ParamFontStyle2.setColor(HSSFColor.BLUE.index);   //設(shè)置字體顏色 (藍(lán)色)
        ParamFontStyle2.setFontHeightInPoints((short) this.fontSize);
        cellParamStyle2.setFont(ParamFontStyle2);
        //開始寫入實(shí)體數(shù)據(jù)信息
        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) {
                    //取出的數(shù)據(jù)是字符串直接賦值
                    value = (String) map.get(heardKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的數(shù)據(jù)是Integer
                    value = String.valueOf(((Integer) (valueObject)).floatValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的數(shù)據(jù)是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                //設(shè)置單個(gè)單元格的字體顏色
                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++;
        }

        //導(dǎo)出數(shù)據(jù)
        try {
            //設(shè)置Http響應(yīng)頭告訴瀏覽器下載這個(gè)附件
            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("導(dǎo)出Excel出現(xiàn)嚴(yán)重異常，異常信息：" + ex.getMessage());
        }

    }

    /**
     * 檢查數(shù)據(jù)配置問題
     *
     * @throws IOException 拋出數(shù)據(jù)異常類
     */
    protected void checkConfig() throws IOException {
        if (heardKey == null || heardList.length == 0) {
            throw new IOException("列名數(shù)組不能為空或者為NULL");
        }

        if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
            throw new IOException("字體、寬度或者高度不能為負(fù)值");
        }

        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[]{"城市","項(xiàng)目名字","合同","實(shí)際"};        //設(shè)置表格表頭字段
            String [] properties = new String[]{"city","projectName","ddNum","sjNum"};  // 查詢對(duì)應(yīng)的字段
            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
 * @創(chuàng)建日期
 * @版本 V1.0
 * @描述 Excel 導(dǎo)出通用工具類
 */
public class ExcelUtil {

    public static byte[] export(String sheetTitle, String[] title, List<Object> list) {

        HSSFWorkbook wb = new HSSFWorkbook();//創(chuàng)建excel表
        HSSFSheet sheet = wb.createSheet(sheetTitle);
        sheet.setDefaultColumnWidth(20);//設(shè)置默認(rèn)行寬

        //表頭樣式（加粗，水平居中，垂直居中）
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //設(shè)置邊框樣式
        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);

        //標(biāo)題樣式（加粗，垂直居中）
        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setFont(fontStyle);

        //設(shè)置邊框樣式
        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);//垂直居中

        //設(shè)置邊框樣式
        cellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下邊框
        cellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左邊框
        cellStyle3.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框
        cellStyle3.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框

        //創(chuàng)建表頭
        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)));
        
        //創(chuàng)建標(biāo)題
        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 {
            //創(chuàng)建表格數(shù)據(jù)
            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;
    }
}

接口類demo：

package com.tjhq.nyb.common.service;

 

import java.util.List;

import java.util.Map;

 

import org.apache.poi.ss.usermodel.Workbook;

 

publicinterfaceExpExcelUtilService{

    /**

     * 業(yè)務(wù)數(shù)據(jù)導(dǎo)出

     * @param mapTitle LinkedHashMap  鏈?zhǔn)降腗ap 表頭與字段對(duì)應(yīng)關(guān)系

     * @param List<?>  表格鏈?zhǔn)降臄?shù)據(jù)集合，?為實(shí)體類 

     * @param excelStyle 樣式表

     * @return生成excel文檔

     * @throws Exception 拋出錯(cuò)誤

     */

    public WorkbookexportExcelWriter2007(Map<String,Object> mapTitle,List<?>list,ExcelStyle excelStyle) throws Exception;

    public WorkbookexportExcelWriter2003(Map<String,Object> mapTitle,List<?>list,ExcelStyle excelStyle) throws Exception;

    public WorkbookexportExcelWriter2003_(Map<String,Object> mapTitle,Map<String,List<?>> mapLstChry,ExcelStyle excelStyle) throws Exception;

    public WorkbookexportExcelWriter2003_Title(Map<String,Object> mapTitle,Map<String,List<?>> mapLstChry,ExcelStyle excelStyle) throws Exception;

}

以上就是Java怎么導(dǎo)出Excel的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注億速云其它相關(guān)文章！