java poi excel 生成表格的工具封装

效果如下：

代码如下：

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

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.CellType;

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

/**

 * 使用该类时，确定引入的poi相关的jar包

 * Maven如下：

 *         <!-- POI -->

        <dependency>

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

            <artifactId>poi</artifactId>

            <version>3.15</version>

        </dependency>

        <dependency>

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

            <artifactId>poi-ooxml</artifactId>

            <version>3.15</version>

        </dependency>

 * @author Mr.wang

 * @date 2018/06/14

 *

 */

public class ExcelUtils {

    public static void main(String[] args) throws IOException {

        List<String> title = Arrays.asList("姓名", "年龄", "生日", "邮箱");

        List<List<String>> personInfos = new ArrayList<List<String>>();

        List<String> person01 = Arrays.asList("Ben", "26", "1992-02-12", "ben@sina.com");

        List<String> person02 = Arrays.asList("Tom", "27", "1993-08-18", "tom@sina.com");

        List<String> person03 = Arrays.asList("Jack", "28", "1994-03-28", "jack@sina.com");

        List<String> person04 = Arrays.asList("恩索", "29", "1995-01-09", "enso@sina.com");

        personInfos.add(person01);

        personInfos.add(person02);

        personInfos.add(person03);

        personInfos.add(person04);

        File file = new File("D:/demo.xls");

        //createExcelFile(title, personInfos, file, null);

        createExcelFileWithHead(title, personInfos, file, null, "个人信息表");

    }

    /**

     * 由生成流的方法进一步封装成生成excel（.xls）的文件

     * @see #createExcelInStream(List, List, OutputStream, String)

     * @param title

     * @param listData

     * @param fileWithPathAndName

     * @param sheetName

     * @throws IOException

     */

    public static void createExcelFile(List<String> title, List<List<String>> listData, File fileWithPathAndName,

            String sheetName) throws IOException {

        FileOutputStream fos=new FileOutputStream(fileWithPathAndName);

        createExcelInStream(title, listData, fos, sheetName);

        if(fos!=null) {

            fos.close();

        }

    }

    /**

     * 由生成流的方法进一步封装成生成excel（.xls）的文件

     * @see #createExcelInStreamWithHead(List, List, OutputStream, String, String)

     * @param title

     * @param listData

     * @param fileWithPathAndName

     * @param sheetName

     * @param header

     * @throws IOException

     */

    public static void createExcelFileWithHead(List<String> title, List<List<String>> listData, File fileWithPathAndName,

            String sheetName,String header) throws IOException {

        FileOutputStream fos=new FileOutputStream(fileWithPathAndName);

        createExcelInStreamWithHead(title, listData, fos, sheetName, header);

        if(fos!=null) {

            fos.close();

        }

    }

    /**

     * create by Mr.wang 2018/06/14

     *

     * 生成excel流（不带表头）。以xls为后缀的文件，防止有些电脑不支持office07以上的

     *

     * @param title

     *            标题

     * @param listData

     *            数据内容

     * @param outputStream

     *            输出的流

     * @param sheetName

     *            创建的sheet（不是文件）的名称，如果有空，则采用sheet1作用默认的表名称

     * @throws IOException

     *

     *

     * example:

     *

     *  List<String> title = Arrays.asList("姓名", "年龄", "生日", "邮箱");

        List<List<String>> personInfos = new ArrayList<List<String>>();

        List<String> person01 = Arrays.asList("Ben", "26", "1992-02-12", "ben@sina.com");

        List<String> person02 = Arrays.asList("Tom", "27", "1993-08-18", "tom@sina.com");

        List<String> person03 = Arrays.asList("Jack", "28", "1994-03-28", "jack@sina.com");

        List<String> person04 = Arrays.asList("恩索", "29", "1995-01-09", "enso@sina.com");

        personInfos.add(person01);

        personInfos.add(person02);

        personInfos.add(person03);

        personInfos.add(person04);

        File file = new File("D:/demo.xls");

        FileOutputStream fos=new FileOutputStream(file);

        createExcelFile(title, personInfos, fos, null);

        ...

     *

     */

    @SuppressWarnings("deprecation")

    public static void createExcelInStream(List<String> title, List<List<String>> listData, OutputStream outputStream,

            String sheetName) throws IOException {

        // 创建工作簿

        HSSFWorkbook workBook = new HSSFWorkbook();

        // 创建工作表 工作表的名字叫helloWorld

        if (sheetName == null || sheetName.length() == 0) {

            sheetName = "sheet1";

        }

        HSSFSheet sheet = workBook.createSheet(sheetName);

        // 创建单元格，首先设置标题

        HSSFFont fontTitle = workBook.createFont();

        fontTitle.setBold(true);

        HSSFCellStyle titleStyle = workBook.createCellStyle();

        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        titleStyle.setFont(fontTitle);

        HSSFRow titleRow = sheet.createRow(0);

        for (int i = 0; i < title.size(); i++) {

            HSSFCell titleCol = titleRow.createCell(i, CellType.STRING);

            titleCol.setCellValue(title.get(i));

            titleCol.setCellStyle(titleStyle);

        }

        sheet.autoSizeColumn(0);

        // 创建数据行

        HSSFCellStyle dataStyle = workBook.createCellStyle();

        dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < listData.size(); i++) {

            HSSFRow dataRow = sheet.createRow(i + 1);

            List<String> rowData = listData.get(i);

            for (int j = 0; j < rowData.size(); j++) {

                HSSFCell dataCol = dataRow.createCell(j, CellType.STRING);

                dataCol.setCellValue(rowData.get(j));

                dataCol.setCellStyle(dataStyle);

            }

            sheet.autoSizeColumn(i);

        }

        HSSFRow firstRow = sheet.getRow(0);

        int lastCellNum = firstRow.getLastCellNum();

        //为了美观，把所有的cell扩大1/2

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

            int columnWidth = sheet.getColumnWidth(i);

            sheet.setColumnWidth(i, columnWidth+columnWidth*1/2);

        }

        workBook.write(outputStream);

        workBook.close();// 最后记得关闭工作簿

    }

    /**

     * create by Mr.wang 2018/06/14

     *

     * 生成excel流（带表头）。以xls为后缀的文件，防止有些电脑不支持office07以上的

     *

     * @param title

     *            标题

     * @param listData

     *            数据内容

     * @param outputStream

     *            输出的流

     * @param sheetName

     *            创建的sheet（不是文件）的名称，如果有空，则采用sheet1作用默认的表名称

     * @param header

     *            表的表头

     * @throws IOException

     *

     *

     * example:

     *

     *  List<String> title = Arrays.asList("姓名", "年龄", "生日", "邮箱");

        List<List<String>> personInfos = new ArrayList<List<String>>();

        List<String> person01 = Arrays.asList("Ben", "26", "1992-02-12", "ben@sina.com");

        List<String> person02 = Arrays.asList("Tom", "27", "1993-08-18", "tom@sina.com");

        List<String> person03 = Arrays.asList("Jack", "28", "1994-03-28", "jack@sina.com");

        List<String> person04 = Arrays.asList("恩索", "29", "1995-01-09", "enso@sina.com");

        personInfos.add(person01);

        personInfos.add(person02);

        personInfos.add(person03);

        personInfos.add(person04);

        File file = new File("D:/demo.xls");

        FileOutputStream fos=new FileOutputStream(file);

        createExcelFileWithHead(title, personInfos, fos, null, "个人信息表");

        ...

     *

     */

    @SuppressWarnings("deprecation")

    public static void createExcelInStreamWithHead(List<String> title, List<List<String>> listData, OutputStream outputStream,

            String sheetName,String header) throws IOException {

        // 创建工作簿

        HSSFWorkbook workBook = new HSSFWorkbook();

        // 创建工作表 工作表的名字叫helloWorld

        if (sheetName == null || sheetName.length() == 0) {

            sheetName = "sheet1";

        }

        HSSFSheet sheet = workBook.createSheet(sheetName);

        //设置表头

        //参数说明：1：开始行 2：结束行  3：开始列 4：结束列

        //比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));

        sheet.addMergedRegion(new CellRangeAddress(0,0,0,title.size()-1));

        HSSFRow headerRow = sheet.createRow(0);

        HSSFCell headerCell = headerRow.createCell(0);

        // 创建单元格，首先设置标题

        HSSFFont font = workBook.createFont();

        short fontHeightInPoints = font.getFontHeightInPoints();

        font.setFontHeightInPoints((short)(fontHeightInPoints+2));

        font.setBold(true);

        HSSFCellStyle cellHeaderStyle = workBook.createCellStyle();

        cellHeaderStyle.setFont(font);

        cellHeaderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        headerCell.setCellStyle(cellHeaderStyle);//可表头加粗,居中

        headerCell.setCellValue(header);

        //cellStyle.setAlignment(alignmentEnum);//还原，标题不居中

        HSSFFont fontTitle = workBook.createFont();

        fontTitle.setBold(true);

        HSSFCellStyle titleStyle = workBook.createCellStyle();

        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        titleStyle.setFont(fontTitle);

        HSSFRow titleRow = sheet.createRow(1);

        for (int i = 0; i < title.size(); i++) {

            HSSFCell titleCol = titleRow.createCell(i, CellType.STRING);

            titleCol.setCellValue(title.get(i));

            titleCol.setCellStyle(titleStyle);

        }

        sheet.autoSizeColumn(1);

        // 创建数据行

        HSSFCellStyle dataStyle = workBook.createCellStyle();

        dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < listData.size(); i++) {

            HSSFRow dataRow = sheet.createRow(i + 2);

            List<String> rowData = listData.get(i);

            for (int j = 0; j < rowData.size(); j++) {

                HSSFCell dataCol = dataRow.createCell(j, CellType.STRING);

                dataCol.setCellValue(rowData.get(j));

                dataCol.setCellStyle(dataStyle);

            }

            sheet.autoSizeColumn(i);

        }

        HSSFRow firstRow = sheet.getRow(1);

        int lastCellNum = firstRow.getLastCellNum();

        //为了美观，把所有的cell扩大1/2

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

            int columnWidth = sheet.getColumnWidth(i);

            sheet.setColumnWidth(i, columnWidth+columnWidth*1/2);

        }

        workBook.write(outputStream);

        workBook.close();// 最后记得关闭工作簿

    }

}

相关工具方法

/**

     * create by Mr.wang 2018/06/14

     *

     * 将简单的对象(读取不到父类的属性)转化成List<String>

     *

     * @param o 对象

     * @param convertMap 转换器 指定对象属性中某一个属性值如何转化为String。如果不需要，可以设置成null即可

     * @param args 指定要输出的属性,一般用于属性选择和排序 ，如果不需要，则不设置.如果要查找父类的属性，则自身及父类的属性全部定义在此处，不再自动搜索自身的属性

     *                 这里注意如果同样的属性要利用两次，可以使用field&alias的格式来指定转换器

     *                 例如：code,code&1---->code&1可以设定与之对应的转换器

     * @return

     * @throws IllegalArgumentException

     * @throws IllegalAccessException

     * @throws NoSuchFieldException

     * @throws SecurityException

     *

     * example:

     * {

     *         "name":"jack",

     *         "age":26,

     *         "birthday":new Date()

     * }

     * 转成

     * ["jack","26","2018-06-14 16:02:20"]

     */

    public static List<String> convertSimpleObj2StringList(Object o,Map<String,FieldConvert> convertMap,String... args) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException{

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

        Class<? extends Object> objClas = o.getClass();

        if(args.length==0) {

            Field[] declaredFields = objClas.getDeclaredFields();

            for(Field f : declaredFields) {

                f.setAccessible(true);

                String fileName = f.getName();

                Object obj=f.get(o);//对应的属性没值，则设置为“/”

                processFieldValue(convertMap, list, fileName, obj);

            }

        }else {

            for(String ag : args) {

                String fieldKey=null;

                String convertKey=ag;

                if(ag.contains("&")) {

                    String[] split = ag.split("&");

                    fieldKey=split[0];

                }else {

                    fieldKey=ag;

                }

                Object fieldValue = getFieldValue(o, fieldKey);

                processFieldValue(convertMap, list, convertKey, fieldValue);

            }

        }

        return list;

    }

    private static void processFieldValue(Map<String, FieldConvert> convertMap, List<String> list, String ag, Object fieldValue) {

        if(fieldValue==null) {

            list.add("/");

            return;

        }else {

            if(convertMap!=null&&convertMap.containsKey(ag)){

                FieldConvert filedConvert = convertMap.get(ag);

                String s = filedConvert.covertFiledValueToString(fieldValue);

                if(s!=null){

                    list.add(s);

                }else{

                    list.add("/");

                }

            }else{

                list.add(fieldValue.toString());

            }

        }

    }

    /**

     * create by Mr.wang 2018/06/14

     * @see #convertSimpleObj2StringList

     * @param objs

     * @param convertMap

     * @param args

     * @return

     * @throws IllegalArgumentException

     * @throws IllegalAccessException

     * @throws NoSuchFieldException

     * @throws SecurityException

     */

    public static <T> List<List<String>> convertSimpleObjList2StringList(List<T> objs,Map<String,FieldConvert> convertMap,String... args) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException{

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

        for(T obj : objs) {

            list.add(convertSimpleObj2StringList(obj,convertMap,args));

        }

        return list;

    }

    /**

     * create by Mr.wang 2018/06/14

     * 通过反射，向上（父类）一直获取对象的属性，如果一直获取不到则抛 NoSuchFieldException 异常

     * @param obj 要操作的操作

     * @param fieldName 要获取的属性名称

     * @return

     * @throws NoSuchFieldException

     * @throws SecurityException

     * @throws IllegalArgumentException

     * @throws IllegalAccessException

     */

    public static Object getFieldValue(Object obj,String fieldName) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {

        Class<? extends Object> class1 = obj.getClass();

        Field declaredField=null;

        try {

            declaredField = class1.getDeclaredField(fieldName);

        } catch (NoSuchFieldException e) {

            declaredField=null;

        }

        while(declaredField==null) {

            class1=class1.getSuperclass();

            if(class1==null) {

                throw new NoSuchFieldException();

            }

            try {

                declaredField = class1.getDeclaredField(fieldName);

            } catch (NoSuchFieldException e) {

                declaredField=null;

            }

        }

        declaredField.setAccessible(true);

        return declaredField.get(obj);

    }

FieldConvert.java

public interface FieldConvert {

    String covertFiledValueToString(Object filedValue);

}

巴特西

java poi excel 生成表格的工具封装

最新文章

热门文章