关于Excel导出实例（适合新手，比较详细）

需要源代码的可以加我微信好友gqljxg1514

1，首先配置依赖pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

  <modelVersion>4.0.</modelVersion>

  <groupId>com.nn-kc</groupId>

  <artifactId>nnkc-reportexcel</artifactId>

  <packaging>war</packaging>

  <version>1.0-SNAPSHOT</version>

  <name>nnkc-reportexcel Maven Webapp</name>

  <url>http://maven.apache.org</url>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>4.8.</version>

    </dependency>

    <dependency>

      <groupId>org.mortbay.jetty</groupId>

      <artifactId>servlet-api-2.5</artifactId>

      <version>6.1.</version>

      <scope>provided</scope>

    </dependency>

    <dependency>

      <groupId>org.apache.poi</groupId>

      <artifactId>poi</artifactId>

      <version>3.9</version>

    </dependency>

    <dependency>

      <groupId>org.apache.poi</groupId>

      <artifactId>poi-ooxml</artifactId>

      <version>3.9</version>

    </dependency>

    <dependency>

      <groupId>commons-lang</groupId>

      <artifactId>commons-lang</artifactId>

      <version>2.3</version>

    </dependency>

  </dependencies>

  <build>

    <finalName>nnkc-reportexcel</finalName>

  </build>

</project>

2,创建实体类Product.java

package com.nnkc.pojo;

import com.nnkc.util.ExcelAnnotation;

import java.util.Date;

public class Product {

    @ExcelAnnotation(id=,name={"产品名称","商品名称"},width = )

    private String name;

    @ExcelAnnotation(id=,name={"产品价格","商品价格"},width = )

    private double price;

    @ExcelAnnotation(id=,name={"生产日期"},width = )

    private Date date;

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public double getPrice() {

        return price;

    }

    public void setPrice(double price) {

        this.price = price;

    }

    public Date getDate() {

        return date;

    }

    public void setDate(Date date) {

        this.date = date;

    }

}

3，编写工具类/nnkc-reportexcel/src/main/java/com/nnkc/util/ExcelAnnotation.java

package com.nnkc.util;

import java.lang.annotation.*;

/**

 * <p>

 *  功能：excel模板设置

 * </p>

 */

@Documented

@Retention(RetentionPolicy.RUNTIME)

@Target(ElementType.FIELD)

public @interface ExcelAnnotation {

    //Excel列ID(Excel列排序序号)

    int id();

    //Excel列名

    String[] name();

    //Excel列宽

    int width() default ;

}

以及/nnkc-reportexcel/src/main/java/com/nnkc/util/ExcelUtil.java

package com.nnkc.util;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.hssf.util.Region;

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

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.lang.reflect.Field;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.text.SimpleDateFormat;

import java.util.*;

public class ExcelUtil<T> {

    public static final int EXPORT_07_LEAST_SIZE = ;

    public static final int EXPORT_07S_LEAST_SIZE = ;

    /**

     * 功能 :获取表单导出数据

     * @param list        数据列表

     * @param title     首行标题

     * @param className 实体对象class

     * @param exportType         模板标号

     * @return

     * @throws Exception

     */

    public HSSFWorkbook exportExcel(List<T> list, String title, Class className,Integer exportType) throws Exception {

        // 获取属性

        Field[] fields = className.getDeclaredFields();

        List<Field> fieldList = new ArrayList<Field>();

        for (Field fie : fields) {

            if (fie.isAnnotationPresent(ExcelAnnotation.class)) {

                fieldList.add(fie);

            }

        }

        // 按照id进行排序

        Collections.sort(fieldList, new Comparator<Field>() {

            public int compare(Field f1, Field f2) {

                return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();

            }

        });

        int columnsize = fieldList.size(), rowindex = ;

        // 创建一个HSSFWorbook对象（excel的文档对象）

        HSSFWorkbook hWorkbook = new HSSFWorkbook();

        // 创建一个HSSFSheet对象（excll的表单）

        HSSFSheet hSheet = hWorkbook.createSheet();

        // 创建行（excel的行）

        HSSFRow hRow = hSheet.createRow(rowindex++);

        //设置行高度

        hRow.setHeight((short));

        // 创建单元格（从0开始）

        HSSFCell hCell = hRow.createCell((short) );

        //样式对象

        HSSFCellStyle cellStyle = getCellStyle(hWorkbook, (short) , (short) );

        // 将上面获得的样式对象给对应单元格

        hCell.setCellStyle(cellStyle);

        //设置标题行

        hCell.setCellValue(title);

        if (getHuoResult(fieldList.isEmpty(),list == null,list.isEmpty())) {

            return hWorkbook;

        }

        //创建第二行，代表列名

        hRow = hSheet.createRow(rowindex++);

        cellStyle = getCellStyle(hWorkbook, (short) , (short) );

        generateTitle(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);

        //组装excel的数据

        cellStyle = getCellStyle(hWorkbook, (short) , (short) );// 设置单元格格式

        generateData(list, fieldList, columnsize, rowindex, hSheet, cellStyle);

        /**

         * 第1个参数：从哪一行开始

         * 第2个参数：到哪一行结束

         * 第3个参数：从哪一列开始

         * 第4个参数：到哪一列结束

         */

        hSheet.addMergedRegion(new CellRangeAddress(,,,columnsize-));

        // 固定表头（前一个参数代表列，后一个参数单表行）

        hSheet.createFreezePane(, );

        return hWorkbook;

    }

    /**

     * 功能：組裝列明

     * @param exportType 模板编号

     * @param fieldList 列名

     * @param columnsize 列数

     * @param hSheet sheet页

     * @param hRow  行

     * @param cellStyle 样式

     */

    private void generateTitle(Integer exportType, List<Field> fieldList, int columnsize, HSSFSheet hSheet, HSSFRow hRow,

            HSSFCellStyle cellStyle) {

        HSSFCell hCell;

        for (int i = ; i < columnsize; i++) {

            Field field = fieldList.get(i);

            if (field.isAnnotationPresent(ExcelAnnotation.class)) {

                // 获取该字段的注解对象

                ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);

                hCell = hRow.createCell((short) i);

                String colName = field.getAnnotation(ExcelAnnotation.class).name().length>exportType

                                ?field.getAnnotation(ExcelAnnotation.class).name()[exportType]

                                :field.getAnnotation(ExcelAnnotation.class).name()[];

                hCell.setCellValue(colName);

                hCell.setCellStyle(cellStyle);

                hSheet.setColumnWidth((short) i, (short) anno.width());

            }

        }

    }

    /**

     * 组装excel的数据

     * @param list 具体数据

     * @param fieldList 列名

     * @param columnsize 列数

     * @param rowindex 行数计数

     * @param hSheet sheet页

     * @param cellStyle 样式

     * @return

     * @throws NoSuchMethodException

     * @throws IllegalAccessException

     * @throws InvocationTargetException

     */

    private int generateData(List<T> list, List<Field> fieldList, int columnsize, int rowindex, HSSFSheet hSheet,

            HSSFCellStyle cellStyle) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {

        HSSFRow hRow;

        HSSFCell hCell;

        for (Object model : list) {

            hRow = hSheet.createRow(rowindex++);

            //获取该类

            Class clazz = model.getClass();

            for (int i = ; i < columnsize; i++) {

                Field field =fieldList.get(i);

                //获取方法名

                String methodName = "get" + field.getName().substring(, ).toUpperCase()+field.getName().substring();

                Method method = clazz.getMethod(methodName);

                try {

                    // 获取该字段的注解对象

                    Object result = method.invoke(model);

                    hCell = hRow.createCell((short) i);

                    if (result != null) {

                        if (result.getClass().isAssignableFrom(Date.class)) {

                            SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");

                            result = format.format(result);

                        }

                        hCell.setCellValue(new HSSFRichTextString(result.toString()));

                    } else {

                        hCell.setCellValue(new HSSFRichTextString("-"));

                    }

                    hCell.setCellStyle(cellStyle);

                } catch (IllegalArgumentException e) {

                    System.out.println(e.getMessage());

                }

            }

        }

        return rowindex;

    }

    /**

     * 生成07格式的excel对象 使用流方式防止内存溢出

     * @param list

     * @param title

     * @param className

     * @param exportType

     * @return

     * @throws Exception

     */

    public SXSSFWorkbook exportExcel07S(List<T> list, String title, Class className,Integer exportType) throws Exception {

        // 获取属性

        Field[] fields = className.getDeclaredFields();

        List<Field> fieldList = new ArrayList<Field>();

        for (Field fie : fields) {

            if (fie.isAnnotationPresent(ExcelAnnotation.class)){

                fieldList.add(fie);

            }

        }

        // 按照id进行排序

        Collections.sort(fieldList, new Comparator<Field>() {

            public int compare(Field f1, Field f2) {

                return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();

            }

        });

        int columnsize = fieldList.size(), rowindex = ;

        // 创建一个HSSFWorbook对象s

        SXSSFWorkbook hWorkbook = new SXSSFWorkbook();

        // 创建一个HSSFSheet对象（sheet页）

        Sheet hSheet =  hWorkbook.createSheet();

        // 创建第一行(此行作为头)

        Row hRow = hSheet.createRow(rowindex++);

        hRow.setHeight((short));

        // 创建单元格（第一（0）个）

        Cell hCell = hRow.createCell((short) );

        // 设置样式

        CellStyle cellStyle = getCellStyle07S(hWorkbook, (short) , (short) );

        // 将上面获得的样式对象给对应单元格

        hCell.setCellStyle(cellStyle);

        //设置标题行

        hCell.setCellValue(title);

        if (getHuoResult(fieldList.isEmpty(),list == null,list.isEmpty())) {

            return hWorkbook;

        }

        // 创建第二列，列名

        hRow = hSheet.createRow(rowindex++);

        cellStyle = getCellStyle07S(hWorkbook, (short) , (short) );

        createTitle07S(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);

        //生成数据

        cellStyle = getCellStyle07S(hWorkbook, (short) , (short) );// 设置单元格格式

        dealCreateRow07S(list, fieldList, columnsize, rowindex, hSheet, cellStyle);

        /**

         * 第1个参数：从哪一行开始

         * 第2个参数：到哪一行结束

         * 第3个参数：从哪一列开始

         * 第4个参数：到哪一列结束

         */

        hSheet.addMergedRegion(new CellRangeAddress(,,,columnsize-));

        // 固定表头（前一个参数代表列，后一个参数单表行）

        hSheet.createFreezePane(, );

        return hWorkbook;

    }

    private int dealCreateRow07S(List<T> list, List<Field> fieldList, int columnsize, int rowindex, Sheet hSheet,

            CellStyle cellStyle) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {

        Row hRow;

        Cell hCell;

        for (Object model : list) {

            hRow = hSheet.createRow(rowindex++);

            // 获取该类 并获取自身方法

            Class clazz = model.getClass();

            for (int i = ; i < columnsize; i++) {

                Field field = fieldList.get(i);

                String methodName = "get" + field.getName().substring(, ).toUpperCase()

                + field.getName().substring();

                Method method = clazz.getMethod(methodName);

                try {

                    // 获取该字段的注解对象

                    Object result = method.invoke(model);

                    hCell = hRow.createCell((short) i);

                    if (result != null) {

                        if (result.getClass().isAssignableFrom(Date.class)) {

                            SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日");

                            result = format.format(result);

                        }

                        hCell.setCellValue(new XSSFRichTextString(result.toString()));

                    } else {

                        hCell.setCellValue(new XSSFRichTextString("-"));

                    }

                    hCell.setCellStyle(cellStyle);

                } catch (IllegalArgumentException e) {

                    System.out.println(e.getMessage());

                }

            }

        }

        return rowindex;

    }

    /**

     * 生成列名

     * @param exportType 模板编号

     * @param fieldList 列名

     * @param columnsize 列数

     * @param hSheet

     * @param hRow

     * @param cellStyle

     */

    private void createTitle07S(Integer exportType, List<Field> fieldList, int columnsize, Sheet hSheet, Row hRow,

            CellStyle cellStyle) {

        Cell hCell;

        for (int i = ; i < columnsize; i++) {

            Field field = (Field) fieldList.get(i);

            if (field.isAnnotationPresent(ExcelAnnotation.class)) {

                // 获取该字段的注解对象

                ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);

                hCell = hRow.createCell((short) i);

                String colName = field.getAnnotation(ExcelAnnotation.class).name().length>exportType

                ?field.getAnnotation(ExcelAnnotation.class).name()[exportType]

                                                                   :field.getAnnotation(ExcelAnnotation.class).name()[];

                hCell.setCellValue(colName);

                hCell.setCellStyle(cellStyle);

                hSheet.setColumnWidth((short) i, (short) anno.width());

            }

        }

    }

    /**

     * 功能 :设置excel表格默认样式

     * @param hWorkbook     需导出Excel数据

     * @param fontHeight     字体粗度

     * @param boldWeight    表格线的粗度

     * @return

     */

    public HSSFCellStyle getCellStyle(HSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {

        HSSFCellStyle cellStyle;

        HSSFFont font;

        cellStyle = hWorkbook.createCellStyle();

        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

        font = hWorkbook.createFont();

        font.setFontHeight(fontHeight);

        font.setBoldweight(boldWeight);

        font.setFontName("宋体");

        cellStyle.setFont(font);

        cellStyle.setWrapText(true);

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return cellStyle;

    }

    /**

     * 功能 :设置excel 07表格默认样式

     * @param hWorkbook     需导出Excel数据

     * @param fontHeight     字体粗度

     * @param boldWeight    表格线的粗度

     * @return

     */

    public CellStyle getCellStyle07S(SXSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {

        CellStyle cellStyle;

        Font font;

        cellStyle = hWorkbook.createCellStyle();

        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);

        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);

        font = hWorkbook.createFont();

        font.setFontHeight(fontHeight);

        font.setBoldweight(boldWeight);

        font.setFontName("宋体");

        cellStyle.setFont(font);

        cellStyle.setWrapText(true);

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return cellStyle;

    }

    /*

     * 获取或运算结果

     */

    private static boolean getHuoResult(Boolean... bs){

        for(boolean b:bs){

            if(b){

                return b;

            }

        }

        return false;

     }

}

4，配置web.xml

<?xml version="1.0" encoding="UTF-8"?>

<web-app id="starter" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"

         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

  <servlet>

    <servlet-name>ReportExcel</servlet-name>

    <servlet-class>com.nnkc.ReportExcelTest</servlet-class>

  </servlet>

  <servlet-mapping>

    <servlet-name>ReportExcel</servlet-name>

    <url-pattern>/reportExcel</url-pattern>

  </servlet-mapping>

</web-app>

5，创建ReportExcel.java用来导出Excel

package com.nnkc;

import com.nnkc.util.ExcelUtil;

import org.apache.commons.lang.StringUtils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.io.OutputStream;

import java.io.UnsupportedEncodingException;

import java.net.URLEncoder;

import java.util.List;

/**

 *功能描述：导出报表

 */

public class ReportExcel {

    /**

     * 功能: Excel导出公共方法

     * 记录条数大于50000时 导出.xlsx文件(excel07+)  小于等于50000时导出 .xls文件(excel97-03)

     * @param list            需要导出的列表数据

     * @param title            导出文件的标题

     * @param className        导出对象的类名

     * @param exportType    针对同一个pojo可能有多个不同的导出模板时,可以通过此属性来决定导出哪一套模板，默认第一套

     * @param response         用来获取输出流

     * @param request       针对火狐浏览器导出时文件名乱码的问题,也可以不传入此值

     * @throws IOException

     */

    public void excelExport(List list, String title, Class className, Integer exportType, HttpServletResponse response, HttpServletRequest request) throws IOException {

        OutputStream out = response.getOutputStream();

        try {

            ExcelUtil excel = new ExcelUtil();

            if(list!=null && list.size()>ExcelUtil.EXPORT_07_LEAST_SIZE){

                dealBigNumber(list, title, className, exportType, response, request, out, excel);

            }else{

                HSSFWorkbook hss = new HSSFWorkbook();

                if(exportType==null){

                    hss = excel.exportExcel(list,title,className,);

                }else{

                    hss = excel.exportExcel(list, title, className, exportType);

                }

                String disposition = "attachment;filename=";

                if(request!=null&&request.getHeader("USER-AGENT")!=null&& StringUtils.contains(request.getHeader("USER-AGENT"), "Firefox")){

                    disposition += new String((title+".xls").getBytes(),"ISO8859-1");

                }else{

                    disposition += URLEncoder.encode(title+".xls", "UTF-8");

                }

                response.setContentType("application/vnd.ms-excel;charset=UTF-8");

                response.setHeader("Content-disposition", disposition);

                hss.write(out);

            }

        } catch (Exception e) {

            System.out.println(e.getMessage());

        } finally {

            out.close();

        }

    }

    private void dealBigNumber(List list, String title, Class className, Integer exportType,

                               HttpServletResponse response, HttpServletRequest request, OutputStream out, ExcelUtil excel)

            throws Exception{

        SXSSFWorkbook hss;

        if(exportType==null){

            hss = excel.exportExcel07S(list,title,className,);

        }else{

            hss = excel.exportExcel07S(list, title, className, exportType);

        }

        String disposition = "attachment;filename=";

        if(request!=null && request.getHeader("USER-AGENT") != null && StringUtils.contains(request.getHeader("USER-AGENT"), "Firefox")){

            disposition += new String((title+".xlsx").getBytes(),"ISO8859-1");

        }else{

            disposition += URLEncoder.encode(title+".xlsx", "UTF-8");

        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");

        response.setHeader("Content-disposition", disposition);

        hss.write(out);

    }

}

6最后创建/nnkc-reportexcel/src/main/java/com/nnkc/ReportExcelTest.java用来测试结果

package com.nnkc;

import com.nnkc.pojo.Product;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

public class ReportExcelTest extends HttpServlet {

    @Override

    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        System.out.println("调用成功");

    }

    @Override

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        List<Product> list = new ArrayList<Product>();

        for (int i =  ; i< ; i++) {

            //组装测试数据

            Product product = new Product();

            product.setName("爱奇艺会员"+i);

            product.setPrice(9.99);

            product.setDate(new Date());

            list.add(product);

        }

        ReportExcel reportExcel = new ReportExcel();

        reportExcel.excelExport(list,"测试",Product.class,,resp,req);

    }

}

运行结果：

巴特西

关于Excel导出实例（适合新手，比较详细）

最新文章

热门文章