apache poi 生成excel

最新推荐文章于 2025-08-20 17:55:57 发布

转载最新推荐文章于 2025-08-20 17:55:57 发布 · 65 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/cocoat/p/5289224.html

文章标签：

#java

本文介绍了一个用于Excel数据导出的工具类实现，包括创建包含标题的Sheet、填充数据等核心功能，并提供了示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ExcelBuilder.Java

package com.coracle.yk.xmanager.util.poi;

import com.coracle.yk.xframework.util.BlankUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 * 创建Sheet, 填充数据
 * author: huangbaidong
 * created : 2016/3/18.
 */
public class ExcelBuilder {

    /**
     * 创建只包含标题列的sheet表
     * @param wb
     * @param sheetName
     * @param titleNames
     * @return
     */
    public static HSSFSheet createExcelSheetWithTitle(HSSFWorkbook wb, String sheetName, List<String> titleNames) {
        if(BlankUtil.isNotEmpty(titleNames)) {
            return createExcelSheetWithTitle(wb, sheetName, titleNames.toArray(new String[titleNames.size()]));
        }
        return createExcelSheetWithTitle(wb, sheetName, new String[]{});
    }

    /**
     * 创建只包含标题列的sheet表
     * @param wb
     * @param sheetName
     * @param titleNames
     * @return
     */
    public static HSSFSheet createExcelSheetWithTitle(HSSFWorkbook wb, String sheetName, String[] titleNames) {
        if(BlankUtil.isEmpty(sheetName)) {//如果没传sheetName,给定默认值
            sheetName = "Sheet";
        }
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(20);//设置默认列宽
        sheet.setDefaultRowHeight((short) 300);//设置默认行高
        if(BlankUtil.isNotEmpty(titleNames)) {//设置Sheet首行列标题
            Row row = sheet.createRow(0);
            Cell cell = null;
            for (int i = 0; i < titleNames.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titleNames[i]);
            }
        }
        return sheet;
    }

    /**
     * 创建包含标题列的sheet, 并填充数据
     * @param wb
     * @param sheetName
     * @param titleNames
     * @param datas
     * @return
     */
    public static HSSFSheet createExcelSheetWithTitleAndData(
            HSSFWorkbook wb, String sheetName, List<String> titleNames, List<Map<String, Object>> datas) {
        if(BlankUtil.isNotEmpty(titleNames) && BlankUtil.isNotEmpty(datas)) {
            return createExcelSheetWithTitleAndData(wb, sheetName, titleNames.toArray(new String[titleNames.size()]), datas);
        }
        return createExcelSheetWithTitle(wb, sheetName, new String[]{});
    }


    /**
     * 创建包含标题列的sheet, 并填充数据
     * @param wb
     * @param sheetName
     * @param titleNames
     * @param datas
     * @return
     */
    public static HSSFSheet createExcelSheetWithTitleAndData(
            HSSFWorkbook wb, String sheetName, String[] titleNames, List<Map<String, Object>> datas) {
        HSSFSheet sheet = createExcelSheetWithTitle(wb, sheetName, titleNames);//创建包含标题列的空sheet
        fillDataToSheet(sheet, datas);//给sheet填充数据
        return sheet;
    }

    /**
     * 向sheet中填充数据行
     * @param sheet
     * @param datas
     */
    public static HSSFSheet fillDataToSheet(HSSFSheet sheet, List<Map<String, Object>> datas) {
        if(sheet !=null && BlankUtil.isNotEmpty(datas)) {
            Row row = null;
            Cell cell = null;
            Map<String, Object> rowDataMap = null;
            Object columnValue = null;
            //第一行是标题行, 所以从第二行开始插入数据
            for (int rowIndex = 0; rowIndex < datas.size(); rowIndex++) {
                row = sheet.createRow(rowIndex+1);//从第二行开始
                rowDataMap = datas.get(rowIndex);
                int columnIndex = 0;
                for (String key : rowDataMap.keySet()) {
                    cell = row.createCell(columnIndex);
                    columnValue = rowDataMap.get(key);
                    if(columnValue instanceof Integer) {
                        cell.setCellValue((Integer)columnValue);
                    } else if(columnValue instanceof Long){
                        cell.setCellValue((Long)columnValue);
                    } else {
                        cell.setCellValue(String.valueOf(columnValue));
                    }
                    columnIndex++;
                }
            }
        }
        return sheet;
    }

}

ExcelTest.java

public static void main(String args[]) throws IOException {
        String path = "d:\\statistic.xls";
        //POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/test.xls"));
        HSSFWorkbook wb = new HSSFWorkbook();
        List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
        Map<String,Object> map1 = new LinkedHashMap<String,Object>();
        map1.put("name","zhangsan");
        map1.put("age",10);
        map1.put("sex","男");
        map1.put("birthday", Xiruo.stringToDate("2015-3-5 12:22:33"));
        Map<String,Object> map2 = new LinkedHashMap<String,Object>();
        map2.put("name","lisi");
        map2.put("age",13);
        map2.put("sex","女");
        map2.put("birthday",Xiruo.stringToDate("2015-3-6 12:22:33"));
        datas.add(map1);
        datas.add(map2);
        HSSFSheet sheet1 = ExcelBuilder.createExcelSheetWithTitle(wb, null, new String[]{"姓名", "年龄", "性别", "生日"});
        ExcelBuilder.fillDataToSheet(sheet1,datas);
        //false表示不追加, 每次都重写
        FileOutputStream fileOut = new FileOutputStream(path, false);
        wb.write(fileOut);
    }