Java处理Excel

1 引言

Excel是我们平时工作中比较常用的用于存储二维表数据的。Java中有两种常用的方法操作Excel：jxl和poi。
其中，在小数据量时jxl快于poi，在大数据量时poi要快于jxl。但差距都不明显。
本文使用poi进行处理Excel

2 POI的maven依赖

<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>com.github.pjfanning</groupId>
	<artifactId>excel-streaming-reader</artifactId>
	<version>4.0.5</version>
</dependency>
<!--        <dependency>-->
<!--            <groupId>com.monitorjbl</groupId>-->
<!--            <artifactId>xlsx-streamer</artifactId>-->
<!--            <version>2.2.0</version>-->
<!--        </dependency>-->

3 创建Excel

// 创建Excel到指定文件
public static void createExcel(String fileName) throws IOException {
   /**
    * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls
    * 07版本和07版本以后的做法excel文件的后缀名为.xlsx,并且
    * 使用
    *  XSSFWorkbook workbook = new XSSFWorkbook();
    *  来操作excel
    */
   //创建新工作簿
   Workbook workbook = new HSSFWorkbook();
   //新建工作表
   Sheet sheet = workbook.createSheet("pwx");
   //创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
   Row row = sheet.createRow(0);
   //创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
   Cell cell = row.createCell(2);
   //设置单元格的值,即C1的值(第一行,第三列)
   cell.setCellValue("pwx");
   //输出到磁盘中
   FileOutputStream fos = new FileOutputStream(new File(fileName));
   workbook.write(fos);
   fos.close();
}

4 解析Excel

// 解析指定文件中的Excel表格
public static void parseExcel(String fileName) throws IOException {
    //创建输入流
    FileInputStream fis = new FileInputStream(new File(fileName));
    //通过构造函数传参
    Workbook workbook = new HSSFWorkbook(fis);
    //获取工作表
    Sheet sheet = workbook.getSheetAt(0);
    //获取行,行号作为参数传递给getRow方法,第一行从0开始计算
    Row row = sheet.getRow(0);
    //获取单元格,row已经确定了行号,列号作为参数传递给getCell,第一列从0开始计算
    Cell cell = row.getCell(2);
    //设置单元格的值,即C1的值(第一行,第三列)
    String cellValue = cell.getStringCellValue();
    System.out.println("第一行第三列的值是: " + cellValue);
    fis.close();
}

注意：由于HSSFWorkbook只能操作excel2003一下版本,XSSFWorkbook只能操作excel2007以上版本，所以利用Workbook接口创建对应的对象操作excel来处理兼容性

二、大文件处理

1、创建

public static void createExcel(String fileName) throws IOException, IOException {
    //创建新工作簿
    Workbook workbook = new SXSSFWorkbook(1000);
    //新建工作表
    Sheet sheet = workbook.createSheet("pwx00");

    Row row;
    Cell cell;
    for (int i = 0; i < 1048575; i++) {
        row = sheet.createRow(i);
        for (int j = 0; j < 30; j++) {
            cell = row.createCell(j);
            cell.setCellValue(i + "_" + j);
        }
    }

    FileOutputStream fos = new FileOutputStream(fileName);
    workbook.write(fos);
    fos.close();
}

2.2 读取

public static void readExcel(String fileName) throws FileNotFoundException {
   InputStream is = new FileInputStream(fileName);

   Workbook workbook = StreamingReader.builder()
           .rowCacheSize(1000)    // 缓存到内存中的行数(默认是10)
           .bufferSize(1024 * 8)     // 读取资源时，缓存到内存的字节大小(默认是1024)
           .open(is);


   for (Sheet sheet : workbook) {
       System.out.println(sheet.getSheetName());
       for (Row r : sheet) {
           for (Cell c : r) {
               System.out.println(c.getStringCellValue());
           }
       }
   }
}

2.3 复制

import com.github.pjfanning.xlsx.StreamingReader;
import com.github.pjfanning.xlsx.impl.XlsxHyperlink;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.InputStream;

public class CopyToSXSSFUtil {
    public static SXSSFWorkbook copyToSXSSF(final InputStream inputStream) throws Exception {
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();

        try (Workbook wbInput = StreamingReader.builder().setReadHyperlinks(true).open(inputStream)) {
            //note that StreamingReader.builder().setReadHyperlinks(true) and that cellCopyPolicy.setCopyHyperlink(false)
            //the hyperlinks appear at end of sheet, so we need to iterate them separately at the end
            final CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
            cellCopyPolicy.setCopyHyperlink(true);
            cellCopyPolicy.setCopyCellStyle(true);

            final CellCopyContext cellCopyContext = new CellCopyContext();

            for (Sheet sheetInput : wbInput) {
                SXSSFSheet sheetOutput = sxssfWorkbook.createSheet(sheetInput.getSheetName());
                for (Row rowInput : sheetInput) {
                    SXSSFRow rowOutput = sheetOutput.createRow(rowInput.getRowNum());
                    for (Cell cellInput : rowInput) {
                        SXSSFCell cellOutput = rowOutput.createCell(cellInput.getColumnIndex());
                        CellUtil.copyCell(cellInput, cellOutput, cellCopyPolicy, cellCopyContext);
                    }
                }
                //this loop only works with POI 5.2.3
                for (Hyperlink hyperlink : sheetInput.getHyperlinkList()) {
                    sheetOutput.addHyperlink(((XlsxHyperlink) hyperlink).createXSSFHyperlink());
                }
                //POI versions before 5.2.3: you need XSSFSheet instance
        /*
        XSSFSheet xssfSheet = sxssfWorkbook.getXSSFWorkbook().getSheet(sheetInput.getSheetName());
        for (Hyperlink hyperlink : sheetInput.getHyperlinkList()) {
          xssfSheet.addHyperlink(((XlsxHyperlink)hyperlink).createXSSFHyperlink());
        }
        */
            }
        } catch (Exception e) {
            sxssfWorkbook.dispose();
            throw e;
        }
        return sxssfWorkbook;
    }
}