如何将数据写入excel,并将excel包装为一个ByteArray字节数组返回呢?

实现思路:
    1.定义一个ByteArrayOutputStream
	2.将workbook写入到ByteArrayOutputStream out对象中
	3.new ByteArrayInputStream(out.toByteArray())

引入相应的依赖

 <dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.1</version>
</dependency>

代码

package com.excel;

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

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelUtil {


    /**
     * 将数据写入excel并包装成ByteArray字节数组返回，可用于网络下载
     * @param title
     * @param data
     * @param protectIndex
     * @return
     */
    public static ByteArrayInputStream writeExcelWithTitle(list<String> title, 
	    List<List<String>> data,List<Integer> protectIndex) {
        if (title == null || data == null || title.size() != data.get(0).size()) {
            return null;
        }
        ByteArrayOutputStream out = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            out = new ByteArrayOutputStream();

            Sheet sheet = workbook.createSheet();
            sheet.protectSheet("edu");

            //单元格锁定样式
            CellStyle lockStyle = workbook.createCellStyle();
            lockStyle.setLocked(true);
            lockStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            lockStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            //单元格不锁定样式
            CellStyle unlockStyle = workbook.createCellStyle();
            unlockStyle.setLocked(false);
            //设置表头
            Row row = sheet.createRow(0);
            for (int i = 0; i < title.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(title.get(i));
            }

            Map<Integer, Integer> index = null;
            if(protectIndex != null){
                 index = protectIndex.parallelStream().collect(Collectors.toMap(e -> e, e -> e));
            }
            //添加内容
            for (int j = 0; j < data.size(); j++) {
                row = sheet.createRow(j + 1);
                for (int i = 0; i < data.get(j).size(); i++) {
                    Cell cell = row.createCell(i);
                    if(index != null){//设置单元格的锁定
                        if(index.containsKey(i)){
                            cell.setCellStyle(lockStyle);
                        }else{
                            cell.setCellStyle(unlockStyle);
                        }
                    }
                    cell.setCellValue(data.get(j).get(i));
                }
            }


            workbook.write(out);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return new ByteArrayInputStream(out.toByteArray());
    }


    /**
     * 将数据写入excel，并通过文件输出
     * @param title 表头
     * @param data 数据
     * @param protectIndex 加锁的列
     * @param fileName 写入的文件
     */
    public static void writeExcelWithTitle(List<String> title, List<List<String>> data,
	        List<Integer> protectIndex, String fileName) {
        if (title == null || data == null || title.size() != data.get(0).size()) {
            return;
        }

        FileOutputStream out = null;
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook();
            out = new FileOutputStream(fileName);

            Sheet sheet = workbook.createSheet();
            sheet.protectSheet("edu");

            CellStyle lockStyle = workbook.createCellStyle();
            lockStyle.setLocked(true);
            lockStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            lockStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

            CellStyle unlockStyle = workbook.createCellStyle();
            unlockStyle.setLocked(false);


            Map<Integer, Integer> index = null;
            if(protectIndex != null){
                index = protectIndex.parallelStream().collect(Collectors.toMap(e -> e, e -> e));
            }
            //设置表头
            Row row = sheet.createRow(0);
            for (int i = 0; i < title.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(lockStyle);
                cell.setCellValue(title.get(i));
            }

            //添加内容
            for (int j = 0; j < data.size(); j++) {
                row = sheet.createRow(j + 1);
                for (int i = 0; i < data.get(j).size(); i++) {
                    Cell cell = row.createCell(i);
                    if(index.containsKey(i)){
                        cell.setCellStyle(lockStyle);
                    }else{
                        cell.setCellStyle(unlockStyle);
                    }
                    cell.setCellValue(data.get(j).get(i));
                }
            }

            workbook.write(out);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

测试代码

    public static void main(String[] args) {
        List<String> title = new ArrayList<String>();

        title.add("姓名");
        title.add("性别");
        title.add("年龄");


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

        List<String> element1 = new ArrayList<String>();
        element1.add("猫猫");
        element1.add("女");
        element1.add("18");

        List<String> element2 = new ArrayList<String>();
        element2.add("狗狗");
        element2.add("男");
        element2.add("21");

        data.add(element1);
        data.add(element2);

        String fileName = "D:\\tmp\\test.xls";

        writeExcelWithTitle(title, data, Arrays.asList(0,1),fileName);
    }
}