package com.yc.cloud.excel.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

@Slf4j
public class ExcelFmtConvert {

    /**
     * Excel格式從xls轉(zhuǎn)換成xlsx格式
     *
     * @param xlsInputStream   xls格式的輸入流
     * @param xlsxOutputStream xlsx格式的輸出流
     */
    public static void convertXlsToXlsxByStream(InputStream xlsInputStream, OutputStream xlsxOutputStream) {
        try {
            HSSFWorkbook oldWorkbook = new HSSFWorkbook(xlsInputStream);
            XSSFWorkbook newWorkbook = new XSSFWorkbook();

            for (int i = 0; i < oldWorkbook.getNumberOfSheets(); i++) {
                HSSFSheet oldSheet = oldWorkbook.getSheetAt(i);
                XSSFSheet newSheet = newWorkbook.createSheet(oldSheet.getSheetName());

                // 遍歷行，創(chuàng)建行
                for (int j = 0; j <= oldSheet.getLastRowNum(); j++) {
                    HSSFRow oldRow = oldSheet.getRow(j);
                    XSSFRow newRow = newSheet.createRow(j);
                    if (oldRow != null) {
                        // 復(fù)制行高
                        newRow.setHeight(oldRow.getHeight());

                        //遍歷列，復(fù)制單元格
                        for (int k = 0; k < oldRow.getLastCellNum(); k++) {
                            HSSFCell oldCell = oldRow.getCell(k);
                            XSSFCell newCell = newRow.createCell(k);
                            if (oldCell != null) {
                                try {
                                    setCellValue(newCell, oldCell);
                                    copyCellStyle(newWorkbook, newCell, oldWorkbook, oldCell);
                                } catch (Exception ex) {
                                    log.warn("單元格拷貝異常:", ex);
                                }
                            }
                        }
                    }
                }

                // 復(fù)制單元格合并信息
                List<CellRangeAddress> mergedRegions = oldSheet.getMergedRegions();
                for (CellRangeAddress mergedRegion : mergedRegions) {
                    CellRangeAddress targetMergedRegion = new CellRangeAddress(
                            mergedRegion.getFirstRow(),
                            mergedRegion.getLastRow(),
                            mergedRegion.getFirstColumn(),
                            mergedRegion.getLastColumn()
                    );
                    newSheet.addMergedRegion(targetMergedRegion);
                }

                // 復(fù)制列寬
                int columnCount = 0;
                if (newSheet.getRow(0) != null) {
                    // 假設(shè)第一行包含所有列，根據(jù)第一行的列數(shù)獲取列數(shù)
                    columnCount = newSheet.getRow(0).getLastCellNum();
                }
                for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                    newSheet.setColumnWidth(columnIndex, oldSheet.getColumnWidth(columnIndex));
                }
            }

            newWorkbook.write(xlsxOutputStream);
            oldWorkbook.close();
            newWorkbook.close();
        } catch (Exception e) {
            log.error("excel格式轉(zhuǎn)換(xls->xlsx)異常:", e);
        }
    }

    private static void setCellValue(XSSFCell newCell, HSSFCell oldCell) {
        if (oldCell == null) {
            return;
        }
        switch (oldCell.getCellType()) {
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(oldCell)) {
                    newCell.setCellValue(oldCell.getDateCellValue());
                } else {
                    newCell.setCellValue(oldCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case FORMULA:
                newCell.setCellValue(oldCell.getCellFormula());
                break;
            default:
        }
    }

    private static void copyCellStyle(XSSFWorkbook xssfWorkbook, XSSFCell newCell, HSSFWorkbook hssfWorkbook, HSSFCell oldCell) {
        HSSFCellStyle oldCellStyle = oldCell.getCellStyle();

        // 創(chuàng)建一個XSSFCellStyle（新Excel格式）
        XSSFCellStyle newCellStyle = xssfWorkbook.createCellStyle();

        // 復(fù)制對齊方式
        newCellStyle.setAlignment(oldCellStyle.getAlignment());
        newCellStyle.setVerticalAlignment(oldCellStyle.getVerticalAlignment());

        // 復(fù)制字體屬性
        XSSFFont newFont = xssfWorkbook.createFont();
        HSSFFont oldFont = oldCellStyle.getFont(hssfWorkbook);
        newFont.setFontName(oldFont.getFontName());
        newFont.setFontHeightInPoints(oldFont.getFontHeightInPoints());
        newFont.setColor(oldFont.getColor());
        newCellStyle.setFont(newFont);

        // 復(fù)制填充顏色
        newCellStyle.setFillPattern(oldCellStyle.getFillPattern());
        newCellStyle.setFillForegroundColor(oldCellStyle.getFillForegroundColor());
        newCellStyle.setFillBackgroundColor(oldCellStyle.getFillBackgroundColor());

        // 復(fù)制數(shù)據(jù)格式
        newCellStyle.setDataFormat(oldCellStyle.getDataFormat());

        newCell.setCellStyle(newCellStyle);
    }
}