Java操作Excel | POI基本使用

前记：

• 农科院实习时，项目需要把大Excel格式化成Mysql可接受的格式而学习使用
• 数据量巨大，涉及到JVM内存问题。故需要分两步导出

依赖：

    <dependencies>
        <!--xlsx-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
    </dependencies>

示例代码：

package hao.testmain;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;

public class TestMain{
//    public static void main(String[] args) throws Exception {
//        //1.创建workbook
//        Workbook workbook=new XSSFWorkbook();
//        //2.根据workbook创建sheet
//        Sheet sheet = workbook.createSheet("会员列表");
//        //3.根据sheet创建row
//        Row row1 = sheet.createRow(0);
//        //4.根据row创建cell
//        Cell cell1 = row1.createCell(0);
//        //5.向cell里面设置值
//        cell1.setCellValue("按键");
//        //6.通过输出流写到文件里去
//        FileOutputStream fos=new FileOutputStream("C:\\Users\\aj\\Desktop\\test.xlsx");
//        workbook.write(fos);
//        fos.close();
//    }

//    public static void main(String[] args) {
//            FileInputStream fis=new FileInputStream("D:\\123\\01.xls");
//            Workbook workbook=new HSSFWorkbook(fis);
//            Sheet sheet = workbook.getSheetAt(0);
//            Row row = sheet.getRow(0);
//            Cell cell = row.getCell(0);
//            System.out.println(cell.getStringCellValue());
//        }
//    }

    public static void main(String[] args) throws Exception {
        //新建
        // 1.创建workbook
        Workbook newWorkbook=new XSSFWorkbook(); // XSSF 代表 xlsx 格式
        //2.根据workbook创建sheet
        Sheet newSheet = newWorkbook.createSheet("sheet1");
        int newRowNum = 0;//控制新表的总行数
        Row header = newSheet.createRow(newRowNum++);
        //设置头
        header.createCell(0).setCellValue("index");
        header.createCell(1).setCellValue("level");
        header.createCell(2).setCellValue("creature");
        header.createCell(3).setCellValue("value");
        
        //读取
        //@TODO 改文件名信息
        FileInputStream fis=new FileInputStream("C:\\Users\\aj\\Desktop\\农科院实习\\猪肠道\\数据库\\画热图用(1)\\Level_7.xlsx");
        Workbook oldWorkbook=new XSSFWorkbook(fis);
        Sheet oldSheet = oldWorkbook.getSheetAt(0);
        short cellNum = oldSheet.getRow(0).getLastCellNum();
        int commonCellNum = 8;//@TODO 记得减去共同属性
        
        //for (int i = 1; i < 125; i ++) {
        for (int i = 125; i < cellNum - commonCellNum; i++) {
            Iterator<Row> rowIterator = oldSheet.rowIterator();
            Row oldHeaderRow = rowIterator.next();
            while (rowIterator.hasNext()) {
                Row newRow = newSheet.createRow(newRowNum++);
                Row oldRow = rowIterator.next();
                Cell oldCell = oldRow.getCell(i);
                int cellType = oldCell.getCellType();

                newRow.createCell(0).setCellValue(oldRow.getCell(0).getStringCellValue());
                newRow.createCell(1).setCellValue("7");//@TODO 记得设置层次信息
                newRow.createCell(2).setCellValue(oldHeaderRow.getCell(i).getStringCellValue());

                if (cellType == 0) { //表格类型判断
                    newRow.createCell(3).setCellValue(oldRow.getCell(i).getNumericCellValue());
                } else if (cellType == 1) {
                    newRow.createCell(3).setCellValue(oldRow.getCell(i).getStringCellValue());
                }
            }
            System.out.println("处理完第"  + i + "列");
        }
        
        //写入
        //6.通过输出流写到文件里去
        //@TODO 改新路径
        FileOutputStream fos=new FileOutputStream("C:\\Users\\aj\\Desktop\\农科院实习\\猪肠道\\数据库\\拆分\\Level_7_2.xlsx");
        newWorkbook.write(fos);
        fos.close();
    }
}

参考文章：https://www.cnblogs.com/fqh2020/p/14675105.html