<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>cn.hutool</groupId>
	<artifactId>hutool-all</artifactId>
	<version>5.8.8</version>
</dependency>

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.創(chuàng)建一個(gè)工作簿。03
        Workbook workbook = new HSSFWorkbook();
        // 2.創(chuàng)建一個(gè)工作表
        Sheet sheet = workbook.createSheet("統(tǒng)計(jì)表");
        // 3.創(chuàng)建行。第一行
        Row row = sheet.createRow(0);
        // 4.創(chuàng)建列。
        // (1,1) 第一行第一列的單元格
        Cell cell = row.createCell(0);
        cell.setCellValue("我們都一樣");
        // (1,2) 第一行第二列的單元格
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);

        // 第二行。(1,0)
        Row row1 = sheet.createRow(1);
        //（2,1）第二行第一列的單元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(DateUtil.now());

        // 判斷文件是否存在，不存在就創(chuàng)建
        if (FileUtil.isEmpty(new File(path))) {
            FileUtil.mkdir(path);
        }
        // 5.生成一張表。03版本的工作簿是以.xls結(jié)尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        // 輸出
        workbook.write(fileOutputStream);
        // 6.關(guān)閉流
        fileOutputStream.close();
        System.out.println("03表生成成功！");
    }
}

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.創(chuàng)建一個(gè)工作簿。03
        Workbook workbook = new XSSFWorkbook(); // 07和03版本只有對象不同，其他操作一樣
        // 2.創(chuàng)建一個(gè)工作表
        Sheet sheet = workbook.createSheet("統(tǒng)計(jì)表");
        // 3.創(chuàng)建行。第一行
        Row row = sheet.createRow(0);
        // 4.創(chuàng)建列。
        // (1,1) 第一行第一列的單元格
        Cell cell = row.createCell(0);
        cell.setCellValue("我們都一樣");
        // (1,2) 第一行第二列的單元格
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);

        // 第65537行。(65537,0)
        Row row1 = sheet.createRow(65536);
        //（2,1）第二行第一列的單元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(DateUtil.now());

        // 判斷文件是否存在，不存在就創(chuàng)建
        if (FileUtil.isEmpty(new File(path))) {
            FileUtil.mkdir(path);
        }
        // 5.生成一張表。03版本的工作簿是以.xls結(jié)尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        // 輸出
        workbook.write(fileOutputStream);
        // 6.關(guān)閉流
        fileOutputStream.close();
        System.out.println("03表生成成功！");
    }
}

//當(dāng)為-1的時(shí)候表示 將會(huì)把所有的行刷新到臨時(shí)文件
Workbook workbook = new SXSSFWorkbook(-1);
//當(dāng)為100的時(shí)候表示 將會(huì)把超過100行的數(shù)據(jù)刷新到臨時(shí)文件
Workbook workbook = new SXSSFWorkbook(100);
//表示手動(dòng)刷新所有數(shù)據(jù)到臨時(shí)文件的方式 ，可指定參數(shù) 行數(shù)
((SXSSFSheet) sheet).flushRows()；

public class ExcelWriterTest03BigData {
    public static void main(String[] args) throws IOException {
        // 開始時(shí)間
        long start = System.currentTimeMillis();
        String path = "D:\\poi\\";
        // 1.創(chuàng)建一個(gè)工作簿。03
        Workbook workbook = new SXSSFWorkbook(5000);
        // 2.創(chuàng)建一個(gè)工作表
        Sheet sheet = workbook.createSheet("統(tǒng)計(jì)表");
        // 3.創(chuàng)建行。
        for (int rowNum = 0; rowNum < 65537; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum + "," + cellNum);
            }
        }
        // 5.生成一張表。03版本的工作簿是以.xlsx結(jié)尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "07BigDataUpGrade.xlsx");
        // 輸出
        workbook.write(fileOutputStream);
        // 6.關(guān)閉流
        fileOutputStream.close();
        // 7.清除臨時(shí)文件
        ((SXSSFWorkbook) workbook).dispose();
        System.out.println("07大數(shù)據(jù)量表優(yōu)化后生成成功！");
        // 結(jié)束時(shí)間
        long end = System.currentTimeMillis();
        System.out.println("用時(shí)：" + ((end - start) / 1000) + "秒");
    }
}

@Component
public class ExcelConfig {
 
	private final static Logger logger = LoggerFactory.getLogger(ExcelConfig.class);
 
	@Value("${application.tmp.path}")
	private String applicationTmpPath;
 
	/**
	 * 設(shè)置使用SXSSFWorkbook對象導(dǎo)出excel報(bào)表時(shí)，TempFile使用的臨時(shí)目錄，代替{java.io.tmpdir}
	 */
	@PostConstruct
	public void setExcelSXSSFWorkbookTmpPath() {
		String excelSXSSFWorkbookTmpPath = applicationTmpPath + "/poifiles";
		File dir = new File(excelSXSSFWorkbookTmpPath);
		if (!dir.exists()) {
			dir.mkdirs();
		}
		TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
		logger.info("setExcelSXSSFWorkbookTmpPath={}", excelSXSSFWorkbookTmpPath);
	}
 
}

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03BigData {
    public static void main(String[] args) throws IOException {
        // 開始時(shí)間
        long start = System.currentTimeMillis();
        String path = "D:\\poi\\";
        // 1.創(chuàng)建一個(gè)工作簿。03
        Workbook workbook = new HSSFWorkbook();
        // 2.創(chuàng)建一個(gè)工作表
        Sheet sheet = workbook.createSheet("統(tǒng)計(jì)表");
        // 3.創(chuàng)建行。
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum+","+cellNum);
            }
        }
        // 5.生成一張表。03版本的工作簿是以.xls結(jié)尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03BigData.xls");
        // 輸出
        workbook.write(fileOutputStream);
        // 6.關(guān)閉流
        fileOutputStream.close();
        System.out.println("03大數(shù)據(jù)量表生成成功！");
        // 結(jié)束時(shí)間
        long end = System.currentTimeMillis();
        System.out.println("用時(shí)："+((end-start)/1000)+"秒");
    }
}

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.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ClassSXSSFWorkBookUtil {

    public static void main(String[] args) throws IOException {
        long curr_time = System.currentTimeMillis();
        // 內(nèi)存中緩存記錄行數(shù)
        int rowAccess = 100;
        SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess);
        // 生成3個(gè)SHEET
        int sheetNum = 3;

        for (int i = 0; i < sheetNum; i++) {
            Sheet sh = wb.createSheet();
            // 每一個(gè)SHEET有 200000 ROW
            for (int rowNum = 0; rowNum < 200000; rowNum++) {
                Row row = sh.createRow(rowNum);
                //每行有10個(gè)CELL
                for (int cellnum = 0; cellnum < 10; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
                // 每當(dāng)行數(shù)達(dá)到設(shè)置的值就刷新數(shù)據(jù)到硬盤,以清理內(nèi)存，這塊本質(zhì)上其實(shí)不加這個(gè)poi在達(dá)到閥值也會(huì)向臨時(shí)文件寫數(shù)據(jù)，
                // 假如導(dǎo)出60w數(shù)據(jù)3個(gè)sheet，加上手動(dòng)刷新是34s，然后不加是40s，所以在一定程度上來講手動(dòng)刷新要快一點(diǎn)
                if (rowNum % rowAccess == 0) {
                    ((SXSSFSheet) sh).flushRows();
                }
            }
        }
        FileOutputStream os = new FileOutputStream("D:\\poi\\biggrid.xlsx");
        wb.write(os);
        os.close();
        System.out.println("耗時(shí)(秒):" + (System.currentTimeMillis() - curr_time) / 1000);
    }
}

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

import java.io.FileOutputStream;
import java.io.IOException;

public class Test {
    public static void main(String[] args) throws IOException {
        //創(chuàng)建HSSFWorkbook對象
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立sheet對象
        HSSFSheet sheet = wb.createSheet("成績表");

        // 設(shè)置列寬
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 25 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.setColumnWidth(4, 25 * 256);

        // 記住一點(diǎn)設(shè)置單元格樣式相關(guān)的都是CellStyle來控制的,設(shè)置完之后只需set給單元格即可：cell.setCellStyle(cellStyle);
        // 合并單元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 設(shè)置字體
        Font font = wb.createFont();
        font.setFontName("宋體");
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 設(shè)置背景色
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 設(shè)置邊框（一般標(biāo)題不設(shè)置邊框，是標(biāo)題下的所有表格設(shè)置邊框）
        cellStyle.setBorderBottom(BorderStyle.THIN); //下邊框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左邊框
        cellStyle.setBorderTop(BorderStyle.THIN);//上邊框
        cellStyle.setBorderRight(BorderStyle.THIN);//右邊框

        //在sheet里創(chuàng)建第一行，參數(shù)為行索引
        HSSFRow row1 = sheet.createRow(0);
        // 合并單元格：參數(shù)1：行號 參數(shù)2：起始列號 參數(shù)3：行號 參數(shù)4：終止列號
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

        // 創(chuàng)建單元格
        HSSFCell cell = row1.createCell(0);
        cell.setCellStyle(cellStyle);
        //設(shè)置單元格內(nèi)容
        cell.setCellValue("學(xué)生成績表");


        //在sheet里創(chuàng)建第二行
        HSSFRow row2 = sheet.createRow(1);
        //創(chuàng)建單元格并設(shè)置單元格內(nèi)容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班級");
        row2.createCell(2).setCellValue("語文成績");
        row2.createCell(3).setCellValue("數(shù)學(xué)成績");
        row2.createCell(4).setCellValue("英語成績");

        //在sheet里創(chuàng)建第三行
        HSSFRow row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("小明");
        row3.createCell(1).setCellValue("1班");
        row3.createCell(2).setCellValue(80);
        row3.createCell(3).setCellValue(75);
        row3.createCell(4).setCellValue(88);

        //在sheet里創(chuàng)建第四行
        HSSFRow row4 = sheet.createRow(3);
        row4.createCell(0).setCellValue("小紅");
        row4.createCell(1).setCellValue("1班");
        row4.createCell(2).setCellValue(82);
        row4.createCell(3).setCellValue(70);
        row4.createCell(4).setCellValue(90);

        FileOutputStream fileOutputStream = new FileOutputStream("D:\\poi\\04.xlsx");
        wb.write(fileOutputStream);
        fileOutputStream.close();
    }
}

XSSFWorkbook(java.io.File file)
XSSFWorkbook(java.io.InputStream is)
XSSFWorkbook(OPCPackage pkg)
XSSFWorkbook(java.lang.String path)

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.IOException;

public class ExcelReadTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        FileInputStream fileInputStream = new FileInputStream(path + "03.xlsx");
        // 1.創(chuàng)建一個(gè)工作簿。使得excel能操作的，這邊他也能操作。
//        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Workbook workbook = new XSSFWorkbook(fileInputStream);

        // 2.得到表。
        Sheet sheet = workbook.getSheetAt(0);
        // 3.得到行。
        Row row = sheet.getRow(0);
        // 4.得到列。
        Cell cell = row.getCell(0);
        // 讀取值。一定要注意類型，否則會(huì)讀取失敗
        System.out.println(cell.getStringCellValue());// 字符串類型
        Cell cell1 = row.getCell(1);
        System.out.println(cell1.getNumericCellValue());// 數(shù)字類型
        // 5.關(guān)閉流。
        fileInputStream.close();
    }
}

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelReadTestType {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.獲取文件流
        FileInputStream fileInputStream = new FileInputStream(path + "會(huì)員消費(fèi)商品明細(xì)表.xls");
        // 2.創(chuàng)建一個(gè)工作簿。使用excel能操作的這邊他也可以操作。
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Workbook workbook = new XSSFWorkbook(fileInputStream);
        // 3.獲取第一張表。
        Sheet sheet = workbook.getSheetAt(0);
        // 4.獲取標(biāo)題內(nèi)容。
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            // 獲取一行有多少列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            // 循環(huán)遍歷，獲取每一個(gè)標(biāo)題名稱
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    System.out.print(cell.getStringCellValue() + "|");
                }
            }
            System.out.println();
        }
        // 5.獲取表中的記錄
        // 獲取有多少行記錄
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            // 獲取每一行記錄
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                // 讀取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    // 獲得單元格
                    Cell cell = rowData.getCell(cellNum);
                    // 匹配列的數(shù)據(jù)類型
                    String cellValueByCell = getCellValueByCell(cell);
                    System.out.println(cellValueByCell);
                }
            }
            System.out.println("----");
        }
        fileInputStream.close();
    }


    //獲取單元格各類型值，返回字符串類型
    public static String getCellValueByCell(Cell cell) {
        //判斷是否為null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();
        switch (cellType) {
            // 數(shù)字
            case NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 處理自定義日期格式：m月d日(通過判斷單元格的格式id解決，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {
                        // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        // 日期
                        cellValue = sdf.format(cell.getDateCellValue());
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    // 數(shù)值 這種用BigDecimal包裝再獲取plainString，可以防止獲取到科學(xué)計(jì)數(shù)值
                    cellValue = bd.toPlainString();
                }
                break;
            // 字符串
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            // Boolean
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                break;
            // 公式
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            // 空值
            case BLANK:
                cellValue = "";
                break;
            // 故障
            case ERROR:
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }
}

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

import java.io.FileInputStream;
import java.io.IOException;

public class GS {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        FileInputStream fileInputStream = new FileInputStream(path + "計(jì)算公式.xls");
        // 1.創(chuàng)建一個(gè)工作簿。使得excel能操作的，這邊他也能操作。
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 2.得到表。
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        // 拿到計(jì)算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        // 輸出單元格內(nèi)容
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA:
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                // 計(jì)算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }
}