VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 编程开发 > Java教程 >

  • Java 操作 Excel(2)--POI 用户模式读写Excel

Apache POI 是基于 Office Open XML 标准（OOXML）和 Microsoft  的 OLE 2复合文档格式（OLE2）处理各种文件格式的开源框架。本文主要介绍使用 POI 的用户模式来读写 Excel，POI 的用户模式使用简单但比较消耗内存，适合小数据量。本文中所使用到的软件版本：jdk1.8.0_181、POI 5.0.0。

1、引入依赖

复制代码
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>
复制代码

2、编写工具类

2.1、Excel 2003 工具类

复制代码
package com.abc.demo.general.excel.user;

import com.abc.demo.general.util.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.util.List;

/**
 * Excel 2003 工具类
 */
public class Excel2003Util {
    private Excel2003Util() {

    }

    /**
     * 创建单元格样式
     * @param workbook
     * @param bgColor
     * @param fontColor
     * @return
     */
    public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short bgColor, short fontColor) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(bgColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFFont font = workbook.createFont();
        font.setColor(fontColor);
        cellStyle.setFont(font);
        return cellStyle;
    }

    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb, short bgColor) {
        return createCellStyle(wb, bgColor, Font.COLOR_NORMAL);
    }

    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
        return createCellStyle(wb, IndexedColors.WHITE.index, Font.COLOR_NORMAL);
    }

    /**
     * 设置单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param colNum 列数
     * @param cs 单元格样式（从外部传进来，减少对象个数及“不同样式的单元格太多”的问题）
     * @param value 设的值
     */
    public static void setCellValue(HSSFSheet sheet, int rowNum, int colNum, HSSFCellStyle cs, Object value) {
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }

        HSSFCell cell = row.getCell(colNum);
        if (cell == null) {
            cell = row.createCell(colNum);
        }
        cell.setCellType(CellType.STRING);

        if (cs != null) {
            cell.setCellStyle(cs);
        }

        if (value == null) {
            value = "";
        }
        cell.setCellValue(new HSSFRichTextString(value.toString()));
    }

    /**
     * 设置一行单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param startColNum 起始列数
     * @param cs 单元格样式
     * @param values 值的数组
     */
    public static void setLineValue(HSSFSheet sheet, int rowNum, int startColNum, HSSFCellStyle cs, Object[] values) {
        for (int i = 0; i < values.length; i++) {
            setCellValue(sheet, rowNum, startColNum++, cs, values[i]);
        }
    }

    /**
     * 设置一行单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param startColNum 起始列数
     * @param cs 单元格样式
     * @param values 值的集合
     */
    public static void setLineValue(HSSFSheet sheet, int rowNum, int startColNum, HSSFCellStyle cs, List<Object> values) {
        for (int i = 0; i < values.size(); i++) {
            setCellValue(sheet, rowNum, startColNum++, cs, values.get(i));
        }
    }

    /**
     * 获取单元格的值
     * @param row 行对象
     * @param cellNum 列索引
     * @return
     */
    public static String getCellValue(HSSFRow row, int cellNum) {
        HSSFCell cell = row.getCell(cellNum);
        if (cell == null) {
            return "";
        }

        String cellValue = "";
        if (cell.getCellType() == CellType.STRING) {
            cellValue = cell.getRichStringCellValue().toString().trim();
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            cellValue = cell.getBooleanCellValue() + "";
        } else if (cell.getCellType() == CellType.FORMULA) {
            cellValue = cell.getCellFormula() + "";
        } else if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType._NONE) {
            cellValue = "";
        } else if (cell.getCellType() == CellType.ERROR) {
            cellValue = cell.getErrorCellValue() + "";
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                cellValue = DateUtil.getDateString(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue()), "yyyy-MM-dd hh:mm:ss");
            } else {
                double val = cell.getNumericCellValue();
                int val2 = (int)val;
                if (val - val2 == 0) {
                    cellValue = String.valueOf(val2);
                } else {
                    cellValue = String.valueOf(val);
                }
            }
        } else {
            throw new RuntimeException("无效的单元格类型:" + cell.getCellType());
        }
        return cellValue.trim();
    }

    /**
     * 获取单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param cellNum 列数
     * @return
     */
    public static String getCellValue(HSSFSheet sheet, int rowNum, int cellNum) {
        HSSFRow row = sheet.getRow(rowNum);
        return getCellValue(row, cellNum);
    }

    /**
     * 判断一行的值是否为空
     * @param row 行对象
     * @param start 从第几列开始判断
     * @param num 判断多少列
     * @return
     */
    public static boolean lineIsNull(HSSFRow row, int start, int num) {
        for (int i = start; i < start + num; i++) {
            if (StringUtils.isNotBlank(getCellValue(row, i))) {
                return false;
            }
        }
        return true;
    }

}
复制代码

2.2、Excel 2007 工具类

复制代码
package com.abc.demo.general.excel.user;

import com.abc.demo.general.util.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.util.Date;
import java.util.List;

/**
 * Excel 2007 工具类
 */
public class Excel2007Util {
    private Excel2007Util() {}

    /**
     * 创建单元格样式
     * @param workbook
     * @param bgColor
     * @return
     */
    public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short bgColor) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(bgColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    /**
     * 创建单元格样式
     * @param wb
     * @return
     */
    public static XSSFCellStyle createCellStyle(XSSFWorkbook wb) {
        return createCellStyle(wb, IndexedColors.WHITE.index);
    }

    /**
     * 设置单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param colNum 列数
     * @param cs 单元格样式（从外部传进来，减少对象个数及“不同样式的单元格太多”的问题）
     * @param value 设的值
     */
    public static void setCellValue(XSSFSheet sheet, int rowNum, int colNum, XSSFCellStyle cs, Object value) {
        XSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }

        XSSFCell cell = row.getCell(colNum);
        if (cell == null) {
            cell = row.createCell(colNum);
        }
        cell.setCellType(CellType.STRING);
        
        if (cs != null) {
            cell.setCellStyle(cs);
        }
        
        if (value == null) {
            value = "";
        }
        cell.setCellValue(new XSSFRichTextString(value.toString()));
    }
    
    
    /**
     * 设置一行单元格的样式
     * @param sheet
     * @param rowNum excel行
     * @param startColNum 起始列数
     * @param endColNum 终止列数
     * @param cs 样式
     */
    public static void setLineCellStyle(XSSFSheet sheet, int rowNum, int startColNum, int endColNum, XSSFCellStyle cs) {
        XSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        for (int i = startColNum; i <= endColNum; i++) {
            XSSFCell cell = row.getCell(i);
            if (cell == null) {
                cell = row.createCell(i);
            }
            cell.setCellStyle(cs);
        }
    }
    
    
    /**
     * 设置一行单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param startColNum 起始列数
     * @param cs 单元格样式
     * @param values 值(集合)
     */
    public static void setLineValue(XSSFSheet sheet, int rowNum, int startColNum, XSSFCellStyle cs, List<Object> values) {
        for (int i = 0; i < values.size(); i++) {
            setCellValue(sheet, rowNum, startColNum++, cs, values.get(i));
        }
    }
    
    /**
     * 设置一行单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param startColNum 起始列数
     * @param cs 单元格样式
     * @param values 值(数组)
     */
    public static void setLineValue(XSSFSheet sheet, int rowNum, int startColNum, XSSFCellStyle cs, Object[] values) {
        for (int i = 0; i < values.length; i++) {
            setCellValue(sheet, rowNum, startColNum++, cs, values[i]);
        }
    }
    
    
    /**
     * 获得单元格的值
     * @param row 行对象
     * @param cellNum 列索引
     * @return
     */
    public static String getCellValue(XSSFRow row, int cellNum) {
        XSSFCell cell = row.getCell(cellNum);
        if (cell == null) {
            return "";
        }
        String cellValue = "";
        if (cell.getCellType() == CellType.STRING) {
            cellValue = cell.getRichStringCellValue().toString().trim();
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            cellValue = cell.getBooleanCellValue() + "";
        } else if (cell.getCellType() == CellType.FORMULA) {
            cellValue = cell.getCellFormula() + "";
        } else if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType._NONE) {
            cellValue = "";
        } else if (cell.getCellType() == CellType.ERROR) {
            cellValue = cell.getErrorCellString();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
                cellValue = DateUtil.getDateString(date, "yyyy-MM-dd hh:mm:ss");
            } else {
                double val = cell.getNumericCellValue();
                long val2 = (long)val;
                if (val - val2 == 0) {
                    cellValue = String.valueOf(val2);
                } else {
                    cellValue = String.valueOf(val);
                }
            }
        } else {
            throw new RuntimeException("无效的单元格类型:" + cell.getCellType());
        }
        return cellValue.trim();
    }
    
    
    /**
     * 判断一行的值是否为空
     * @param row 行对象
     * @param start 从第几列开始判断
     * @param num 判断多少列
     * @return
     */
    public static boolean lineIsNull(XSSFRow row, int start, int num) {
        for (int i = start; i < start + num; i++) {
            if (StringUtils.isNotBlank(getCellValue(row, i))) {
                return false;
            }
        }
        return true;
    }


    /****************************以下为POI流式相关API****************************/

    /**
     * 创建单元格样式
     * @param wb
     * @param bgColor
     * @return
     */
    public static CellStyle createCellStyle(SXSSFWorkbook wb, short bgColor) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(bgColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    /**
     * 设置单元格的值(excel流)
     * 
     * @param sheet 工作表
     * @param rowNum 行数
     * @param colNum 列数
     * @param cs 单元格样式（从外部传进来，减少对象个数及“不同样式的单元格太多”的问题）
     * @param value 设的值
     */
    public static void setCellValue(SXSSFSheet sheet, int rowNum, int colNum, CellStyle cs, Object value) {
        SXSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }

        Cell cell = row.getCell(colNum);
        if (cell == null) {
            cell = row.createCell(colNum);
        }
        
        if (cs != null) {
            cell.setCellStyle(cs);
        }
        
        if (value == null) {
            value = "";
        }
        cell.setCellValue(value.toString());
    }
    
    
    /**
     * 设置一行单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param startColNum 起始列数
     * @param cs 单元格样式
     * @param values 值的数组
     */
    public static void setLineValue(SXSSFSheet sheet, int rowNum, int startColNum, CellStyle cs, Object[] values) {
        for (int i = 0; i < values.length; i++) {
            setCellValue(sheet, rowNum, startColNum++, cs, values[i]);
        }
    }
    
    /**
     * 设置一行单元格的值
     * @param sheet 工作表
     * @param rowNum 行数
     * @param startColNum 起始列数
     * @param cs 单元格样式
     * @param values 值(集合)
     */
    public static void setLineValue(SXSSFSheet sheet, int rowNum, int startColNum, CellStyle cs, List<Object> values) {
        for (int i = 0; i < values.size(); i++) {
            setCellValue(sheet, rowNum, startColNum++, cs, values.get(i));
        }
    }
}
复制代码

3、编写样例

3.1、Excel 2003 样例

复制代码
/**
 * Excel 2003 写
 * @throws IOException
 */
@Test
public void excel2003Write() throws IOException {
    OutputStream outputStream = null;
    try {
        outputStream = new FileOutputStream("d:/a.xls");
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();

        HSSFCellStyle cellStyle = Excel2003Util.createCellStyle(wb, IndexedColors.WHITE.index);
        int row = 0;
        Random random = new Random();
        Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重"));
        for (int i = 0; i < 100; i++) {
            Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50));
        }
        wb.write(outputStream);
    } finally {
        FileUtil.close(outputStream);
    }
}

/**
 * Excel 2003 读
 * @throws IOException
 */
@Test
public void excel2003Read() throws IOException {
    InputStream inputStream = null;
    try {
        inputStream = new FileInputStream("d:/a.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);

        List<String> rowData = new ArrayList<>();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            rowData.clear();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                rowData.add(Excel2003Util.getCellValue(row, j));
            }
            logger.info("第{}行数据:{}", i, rowData);
        }
    } finally {
        FileUtil.close(inputStream);
    }
}
复制代码

3.2、Excel 2007 样例

复制代码
/**
 * Excel 2007 写
 * @throws IOException
 */
@Test
public void excel2007Write() throws IOException {
    OutputStream outputStream = null;
    try {
        outputStream = new FileOutputStream("d:/a.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();

        XSSFCellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index);
        int row = 0;
        Random random = new Random();
        Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重"));
        for (int i = 0; i < 100; i++) {
            Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50));
        }
        workbook.write(outputStream);
    } finally {
        FileUtil.close(outputStream);
    }
}

/**
 * Excel 2007 读
 * @throws IOException
 */
@Test
public void excel2007Read() throws IOException {
    InputStream inputStream = null;
    try {
        inputStream = new FileInputStream("d:/a.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);

        List<String> rowData = new ArrayList<>();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            rowData.clear();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                rowData.add(Excel2007Util.getCellValue(row, j));
            }
            logger.info("第{}行数据:{}", i, rowData);
        }
    } finally {
        FileUtil.close(inputStream);
    }
}
复制代码

3.3、Excel 2007 样例（流模式）

使用流模式来写 Excel，只有部分记录放在内存，其他的写入到临时文件，可以避免对内存的大量使用。

复制代码
/**
 * Excel 2007 写(流方式)
 * @throws IOException
 */
@Test
public void excel2007WriteStream() throws IOException {
    OutputStream outputStream = null;
    SXSSFWorkbook workbook = null;
    try {
        outputStream = new FileOutputStream("d:/a2.xlsx");
        workbook = new SXSSFWorkbook(1000);
        SXSSFSheet sheet = workbook.createSheet();
        CellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index);
        int row = 0;
        Random random = new Random();
        Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重"));
        for (int i = 0; i < 10000; i++) {
            Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50));
        }
        workbook.write(outputStream);
    } finally {
        FileUtil.close(outputStream);
        if (workbook != null) {
            workbook.dispose();
        }
    }
}
复制代码

3.4、完整代码

复制代码
package com.abc.demo.general.excel.user;

import com.abc.demo.general.util.FileUtil;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;

/**
 * POI用户模式读写Excel
 */
public class PoiUserCase {
    private static Logger logger = LoggerFactory.getLogger(PoiUserCase.class);

    /**
     * Excel 2003 写
     * @throws IOException
     */
    @Test
    public void excel2003Write() throws IOException {
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream("d:/a.xls");
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();

            HSSFCellStyle cellStyle = Excel2003Util.createCellStyle(wb, IndexedColors.WHITE.index);
            int row = 0;
            Random random = new Random();
            Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重"));
            for (int i = 0; i < 100; i++) {
                Excel2003Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50));
            }
            wb.write(outputStream);
        } finally {
            FileUtil.close(outputStream);
        }
    }

    /**
     * Excel 2003 读
     * @throws IOException
     */
    @Test
    public void excel2003Read() throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream("d:/a.xls");
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            HSSFSheet sheet = workbook.getSheetAt(0);

            List<String> rowData = new ArrayList<>();
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                HSSFRow row = sheet.getRow(i);
                rowData.clear();
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    rowData.add(Excel2003Util.getCellValue(row, j));
                }
                logger.info("第{}行数据:{}", i, rowData);
            }
        } finally {
            FileUtil.close(inputStream);
        }
    }

    /**
     * Excel 2007 写
     * @throws IOException
     */
    @Test
    public void excel2007Write() throws IOException {
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream("d:/a.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet();

            XSSFCellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index);
            int row = 0;
            Random random = new Random();
            Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重"));
            for (int i = 0; i < 100; i++) {
                Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50));
            }
            workbook.write(outputStream);
        } finally {
            FileUtil.close(outputStream);
        }
    }

    /**
     * Excel 2007 读
     * @throws IOException
     */
    @Test
    public void excel2007Read() throws IOException {
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream("d:/a.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);

            List<String> rowData = new ArrayList<>();
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                XSSFRow row = sheet.getRow(i);
                rowData.clear();
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    rowData.add(Excel2007Util.getCellValue(row, j));
                }
                logger.info("第{}行数据:{}", i, rowData);
            }
        } finally {
            FileUtil.close(inputStream);
        }
    }

    /**
     * Excel 2007 写(流方式)
     * @throws IOException
     */
    @Test
    public void excel2007WriteStream() throws IOException {
        OutputStream outputStream = null;
        SXSSFWorkbook workbook = null;
        try {
            outputStream = new FileOutputStream("d:/a2.xlsx");
            workbook = new SXSSFWorkbook(1000);
            SXSSFSheet sheet = workbook.createSheet();
            CellStyle cellStyle = Excel2007Util.createCellStyle(workbook, IndexedColors.WHITE.index);
            int row = 0;
            Random random = new Random();
            Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList("姓名", "年龄", "身高", "体重"));
            for (int i = 0; i < 10000; i++) {
                Excel2007Util.setLineValue(sheet, row++, 0, cellStyle, Arrays.asList(RandomStringUtils.randomAlphanumeric(6), random.nextInt(10) + 10, random.nextInt(20) + 160, random.nextInt(20) + 50));
            }
            workbook.write(outputStream);
        } finally {
            FileUtil.close(outputStream);
            if (workbook != null) {
                workbook.dispose();
            }
        }
    }

}
复制代码
复制代码
package com.abc.demo.general.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;


/**
 * 文件操作工具
 */
public class FileUtil {
    private FileUtil() {}
    
    public static void close(InputStream in) {
        try {
            if (in != null) {
                in.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void close(OutputStream out) {
        try {
            if (out != null) {
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
复制代码

 

 
来源：https://www.cnblogs.com/wuyongyin/p/14699488.html


相关教程