• java实现Excel数据导出

    java实现Excel数据导出：

         目前，比较常用的实现Java导入、导出Excel的技术有两种Jakarta POI和Java Excel

    Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成，其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF，在各种组件中目前只有用于操作Excel的HSSF相对成熟。官方主页http://poi.apache.org/index.html，API文档http://poi.apache.org/apidocs/index.html

    Jakarta POI HSSF API组件

    HSSF（用于操作Excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。有以下几种对象： 

    2.3 基本操作步骤

    首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。

    基本操作步骤：

     下面来看一个动态生成Excel文件的例子：

     1 //创建HSSFWorkbook对象
 2 HSSFWorkbook wb = new HSSFWorkbook();
 3 //创建HSSFSheet对象
 4 HSSFSheet sheet = wb.createSheet("sheet0");
 5 //创建HSSFRow对象
 6 HSSFRow row = sheet.createRow(0);
 7 //创建HSSFCell对象
 8 HSSFCell cell=row.createCell(0);
 9 //设置单元格的值
10 cell.setCellValue("单元格中的中文");
11 //输出Excel文件
12 FileOutputStream output=new FileOutputStream("d:\workbook.xls");
13 wkb.write(output);
14 output.flush();

          HSSF读取文件同样还是使用这几个对象，只是把相应的createXXX方法变成了getXXX方法即可。可见只要理解了其中原理，不管是读还是写亦或是特定格式都可以轻松实现，正所谓知其然更要知其所以然。

     2：导出Excel应用实例：

     <!--导出文件-->
            <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

     3：导出表格的工具类：

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

     由于上面的代码不够灵活，下面加以改进：

    ExcelExportUtil：
    package com.zhl.push.Utils;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

public class ExcelExportUtil {

    //excel表对象
    private Workbook workbook;
    //工作表对象
    private Sheet sheet;
    //标题
    private String title;
    //sheet各个列的表头
    private String[] headList;
    //各个列的元素key值
    private String[] headKey;
    //sheet需要填充的数据信息
    private List<Map> data;
    //工作表名称
//    private String sheetName = "sheet1";
    //工作表列宽
    private Integer columnWidth=20;
    //工作表行高
    private Integer rowHeight=10;
    //字体大小
    private int fontSize = 14;

    public int getFontSize() {
        return fontSize;
    }

    public void setFontSize(int fontSize) {
        this.fontSize = fontSize;
    }

    public Integer getColumnWidth() {
        return columnWidth;
    }

    public void setColumnWidth(Integer columnWidth) {
        this.columnWidth = columnWidth;
    }

    public Integer getRowHeight() {
        return rowHeight;
    }

    public void setRowHeight(Integer rowHeight) {
        this.rowHeight = rowHeight;
    }

    public Workbook getWorkbook() {
        return workbook;
    }

    public void setWorkbook(Workbook workbook) {
        this.workbook = workbook;
    }

    public Sheet getSheet() {
        return sheet;
    }

    public void setSheet(Sheet sheet) {
        this.sheet = sheet;
    }

    public String[] getHeadKey() {
        return headKey;
    }

    public void setHeadKey(String[] headKey) {
        this.headKey = headKey;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String[] getHeadList() {
        return headList;
    }

    public void setHeadList(String[] headList) {
        this.headList = headList;
    }

    public List<Map> getData() {
        return data;
    }

    public void setData(List<Map> data) {
        this.data = data;
    }





    /**
     * @return *
     * @Author
     * @Description //TODO 插入数据到表格（body）
     * @Date 2019/7/26 17:28
     * @Param startRow: 开始插入数据的行
     */
    public void writeMainData(Integer startRow) throws IOException {
        //设置每格数据的样式 （字体红色）
        CellStyle fontRed = ExcelStyleUtilFor2003.getFontStyle(this.workbook, this.fontSize, HSSFColor.RED.index);
        //设置每格数据的样式2（字体蓝色）
        CellStyle fontBlue = ExcelStyleUtilFor2003.getFontStyle(this.workbook, this.fontSize, HSSFColor.BLUE.index);
        //设置body样式
        CellStyle bodyStyle = ExcelStyleUtilFor2003.getBodyStyle(this.workbook, this.fontSize);

        //开始写入实体数据信息
        if (data.size() > 0 && null != data.get(0)) {
            for (int i = 0; i < data.size(); i++) {
                Row row = this.sheet.createRow(startRow);
                Map map = data.get(i);
                map.put("index",i);
                map.put("des","无");
                for (int j = 0; j < headKey.length; j++) {
                    Cell cell = row.createCell(j);
                    Object value = map.get(headKey[j]);
                        if (null == value) {
                            String valueN = "";
                            cell.setCellValue(valueN);
                        } else if (value instanceof Integer) {
                            Integer valueInt = Integer.valueOf(value.toString());
                            cell.setCellValue(valueInt);
                        } else if (value instanceof String) {
                            String valueStr = String.valueOf(value);
                            cell.setCellValue(valueStr);
                        }
                    }
                }
                startRow++;
            }
        }


    }


    /**
     * @return * @param null
     * @Author
     * @Description //TODO 添加表格标题
     * @Date 2019/7/26 17:58
     * @Param
     */
    public void writeTitle() throws IOException {
        checkConfig();
        //设置默认行宽
        this.sheet.setDefaultColumnWidth(20);
        //在第0行创建rows  (表标题)
        Row title = this.sheet.createRow(0);
        title.setHeightInPoints(this.rowHeight);//行高
        Cell cell = title.createCell(0);
        cell.setCellValue(this.title);
        CellStyle cellStyle = ExcelStyleUtilFor2003.getTitleStyle(this.workbook,true,16);
        cell.setCellStyle(cellStyle);
        ExcelStyleUtilFor2003.mergeCell(sheet,0,0,0, (this.headList.length - 1));
    }


    /**
     * @Author
     * @Description //TODO 添加表头
     * @Date 2019/7/26 15:41
     * @Param headRowNum: 添加表头所在行数
     * @return  *
     */

    public void writeTableHead(String[] head, CellStyle cellStyle,Integer headRowNum) {
        Row row = this.sheet.createRow(headRowNum);
        if (head.length > 0) {
            for (int i = 0; i < head.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(head[i]);
                cell.setCellStyle(cellStyle);
            }
        }
    }


    /**
     * 检查数据配置问题
     *
     * @throws IOException 抛出数据异常类
     */
    protected void checkConfig() throws IOException {
        if (headKey == null || headList.length == 0) {
            throw new IOException("列名数组不能为空或者为NULL");
        }
        if (fontSize < 0) {
            throw new IOException("字体不能为负值");
        }
    }

    /**
     * @Author
     * @Description //TODO 写入拼接好的数据，不需要通过表头key值来对应
     * @Date 2019/7/27 11:01
     * @Param
     * @return  * @param null
     */
    public void writeMainData(List<List<String>> datas,Integer startRow){
        if(datas.size()>0){
            for(List<String> data : datas){
                Row row = this.sheet.createRow(startRow);
                for(int i =0; i<data.size(); i++){
                    Cell cell = row.createCell(i);
                    cell.setCellValue(data.get(i));
                    CellStyle cellStyle = ExcelStyleUtilFor2003.setCellBorder(this.workbook);
                    cell.setCellStyle(cellStyle);
                }
                startRow++;
            }
        }
    }



    public static void setResponseInfo(HttpServletResponse response, Workbook wb) throws IOException {
        //导出数据
        try {
            //设置Http响应头告诉浏览器下载这个附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage());
        }
    }

}

    我们把样式单独提取成一个工具类：

    package com.zhl.push.Utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @Author
 * @ClassName ExcelStyleUtilFor2003
 * @Description TODO
 * @Date 2019/7/26 14:16
 * @Version 1.0
 */
public class ExcelStyleUtilFor2003 {


    /**
     * @return * @param null
     * @Author
     * @Description //TODO 样式居中
     * @Date 2019/7/26 14:51
     * @Param
     */
    public static void center(CellStyle cellStyle) {
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
    }

    /**
     * @return * @param null
     * @Author
     * @Description //TODO 单元格合并
     * @Date 2019/7/26 14:54
     * @Param wbSheet :工作表对象
     * firstRow ：合并的开始行
     * lastRow：合并的结束行
     * firstCol: 合并的开始列
     * lastColL: 合并的结束列
     */
    public static void mergeCell(Sheet wbSheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        wbSheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }


    /**
     * @return *
     * @Author
     * @Description //TODO 标题样式 :加粗，垂直居中
     * @Date 2019/7/26 14:21
     * @Param
     */
    public static CellStyle getTitleStyle(Workbook wb, Boolean isBold, int FontISize) {
        // 标题样式（加粗，垂直居中）
        CellStyle cellStyle = wb.createCellStyle();
        center(cellStyle);
        Font font = wb.createFont();
        font.setBold(isBold);   //加粗
        font.setFontHeightInPoints((short) FontISize);  //设置标题字体大小
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * @return *
     * @Author
     * @Description //TODO 表头样式
     * @Date 2019/7/26 14:30
     * @Param
     */
    public static CellStyle getHeadStyle(Workbook wb, int fontSize) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.AQUA.index);//设置表头的背景颜色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index); //设置表头的背景颜色
        center(cellStyle);
        //设置字体
        Font font = setFont(wb, fontSize);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * @return * @param null
     * @Author
     * @Description //TODO body通用样式: 居中，设置字体大小
     * @Date 2019/7/26 15:11
     * @Param
     */
    public static CellStyle getBodyStyle(Workbook wb, int fontSize) {
        CellStyle cellStyle = wb.createCellStyle();
        //设置单元格样式
        center(cellStyle);
        Font font = setFont(wb, fontSize);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * @return * @param null
     * @Author
     * @Description //TODO 设置单元格字体居中、并设置字体颜色
     * @Date 2019/7/26 15:26
     * @Param
     */
    public static CellStyle getFontStyle(Workbook wb, int fontSize, short color) {
        CellStyle cellStyle = wb.createCellStyle();
        Font font = setFont(wb, fontSize, color);
        center(cellStyle);
        cellStyle.setFont(font);
        return cellStyle;
    }


    /**
     * @return * @param null
     * @Author
     * @Description //TODO 设置单元格字体
     * @Date 2019/7/26 15:16
     * @Param
     */
    public static Font setFont(Workbook wb, int fontSize, short color) {
        //设置字体
        Font font = wb.createFont();
        font.setColor(color);
        font.setFontHeightInPoints((short) fontSize);
        return font;
    }

    public static Font setFont(Workbook wb, int fontSize) {
        //设置字体
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) fontSize);
        return font;
    }

    /**
     * @Author
     * @Description //TODO 设置cell边框
     * @Date 2019/7/27 16:16
     * @Param
     * @return  * @param null
     */
    public static CellStyle setCellBorder(Workbook workbook){
        CellStyle cellStyle = workbook.createCellStyle();
        //设置了边框属性
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        //设置边框颜色黑色
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index); 
            return cellStyle; 
    } 
    }

     =======================================================

    如果需要使用模板来导出excel可以使用jxls:

    官网：http://jxls.sourceforge.net/

    可以参考博客：  https://www.cnblogs.com/foxlee1024/p/7616987.html

    https://blog.csdn.net/sinat_15769727/article/details/78898894
  • 相关阅读:
    【转载】eclipse常用插件在线安装地址或下载地址
    【转载】Eclipse快捷键 10个最有用的快捷键
    Visual Studio（C#）快捷键与Eclipse（JAVA）快捷键对比
    【转载】Eclipse 的快捷键以及文档注释、多行注释的快捷键
    Spring 事务配置的几种方式
    Linux下 执行程序
    转：C# 通过委托更新UI(异步加载）
    C#AutoResetEvent和ManualResetEvent的区别
    转 RMI、RPC、SOAP通信技术介绍及比对
    转泛型
  • 原文地址：https://www.cnblogs.com/dw3306/p/9522880.html
Copyright © 2020-2023  润新知