package com.liang.common.utils;

import com.liang.entity.*;
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.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.prefs.BackingStoreException;

/**
 * @author
 * @date 2023-03-14-0:29
 * @description 简单excel导入导出工具类
 * @reference
 **/
public class ExcelPoiUtil {
    final static Logger log = LoggerFactory.getLogger(ExcelPoiUtil.class);


    /**
     * @param sheetName  工作表名，文件名，头部信息
     * @param listName   列名
     * @param list       需要写入的数据
     * @param listBottom 底部写入信息：<列位置，数据>
     * @param response   返回
     */
    public static void excelPort(String sheetName, List<String> listName, List<Map<String, String>> list, List<Map<Integer, String>> listBottom, HttpServletResponse response) {
        try {
            if (list.size() == 0) {
                throw new BackingStoreException("数据为空");
            }
            // 声明一个工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建sheet页
            XSSFSheet sheet = wb.createSheet(sheetName);
            sheet.setDefaultColumnWidth(19);

            // 表头
            XSSFRow rowReportTitle = sheet.createRow(0);
            Cell cell1 = rowReportTitle.createCell(0); // 0列
            // 设置值
            cell1.setCellValue(sheetName);

            // 合并表头
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
            rowReportTitle.setHeight((short) 600); // 行高

            // 设置表头字体
            Font headFont = wb.createFont();
            headFont.setFontName("宋体");
            headFont.setFontHeightInPoints((short) 18);// 字体大小

            CellStyle headStyle = wb.createCellStyle();
            headStyle.setFont(headFont);
            headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            // 头部样式添加
            cell1.setCellStyle(headStyle);


            // 标题样式
            CellStyle titleStyle = wb.createCellStyle();
            titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
            titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
            titleStyle.setBorderTop(BorderStyle.THIN);//上边框
            titleStyle.setBorderRight(BorderStyle.THIN);//右边框
            titleStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font titleFont = wb.createFont();
            titleFont.setColor(IndexedColors.WHITE.getIndex());
            titleStyle.setFont(titleFont);

            // 记录标题信息
            TreeMap<String, Integer> headMap = new TreeMap<>();
            // 标题写入
            XSSFRow row = sheet.createRow(1);
            for (int i = 0; i < listName.size(); i++) {
                row.setHeight((short) 450);
                XSSFCell cell = row.createCell(i);
                String headName = listName.get(i);
                cell.setCellValue(headName); // 写入列名
                headMap.put(headName, i);
                cell.setCellStyle(titleStyle);
            }

            // 数据内容样式
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

            // 写入内容数据
            int ind = 2;
            for (Map<String, String> map : list) {
                XSSFRow r = sheet.createRow(ind++);
                for (Map.Entry<String, Integer> m : headMap.entrySet()) {
                    String name = m.getKey(); // 列名
                    String value = map.get(name); // value 不一定存在
                    XSSFCell cell2 = r.createCell(m.getValue());
                    if (value != null) {
                        cell2.setCellValue(value);
                    }
                    cell2.setCellStyle(cellStyle);
                }
            }

            // 底部样式
            CellStyle bottomStyle = wb.createCellStyle();
            bottomStyle.setBorderBottom(BorderStyle.THIN); //下边框
            bottomStyle.setBorderLeft(BorderStyle.THIN);//左边框
            bottomStyle.setBorderTop(BorderStyle.THIN);//上边框
            bottomStyle.setBorderRight(BorderStyle.THIN);//右边框
            bottomStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

            // 写入底部
            if (listBottom != null) {
                int columnNum = listName.size(); // 当前表有多少列
                for (Map<Integer, String> map : listBottom) {
                    XSSFRow bottom = sheet.createRow(ind);
                    bottom.setHeight((short) 400); // 行高
                    // 当前行分几列
                    int size = map.size();
                    if (columnNum % 2 == 0 & size % 2 == 0) {
                        // 都是偶数执行
                        int c = columnNum / size;  // 列大小
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellAddresses0 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
                            sheet.addMergedRegion(cellAddresses0);
                            XSSFCell c0 = bottom.createCell(cellAddresses0.getFirstColumn());
                            c0.setCellValue(map.get(i));
                            c0.setCellStyle(bottomStyle);
                            for (int n = cellAddresses0.getFirstColumn() + 1; n <= cellAddresses0.getLastColumn(); n++) {
                                XSSFCell cn = bottom.createCell(n);
                                cn.setCellStyle(bottomStyle);
                            }
                        }
                    } else if (!(columnNum % 2 == 0) & !(size % 2 == 0)) {
                        // 都是奇数执行
                        int c = columnNum - 1 / size;
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellAddresses1;
                            if (size - i <= 1) {
                                cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c);
                            } else {
                                cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
                            }
                            sheet.addMergedRegion(cellAddresses1);
                            XSSFCell c0 = bottom.createCell(cellAddresses1.getFirstColumn());
                            c0.setCellValue(map.get(i));
                            c0.setCellStyle(bottomStyle);
                            for (int n = cellAddresses1.getFirstColumn() + 1; n <= cellAddresses1.getLastColumn(); n++) {
                                XSSFCell cn = bottom.createCell(n);
                                cn.setCellStyle(bottomStyle);
                            }
                        }
                    } else {
                        // 奇偶不同
                        int c = (columnNum + 1) / size;
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellAddresses2;
                            if (size - i <= 1) {
                                cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 2);
                            } else {
                                cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
                            }
                            sheet.addMergedRegion(cellAddresses2);
                            XSSFCell c0 = bottom.createCell(cellAddresses2.getFirstColumn());
                            c0.setCellValue(map.get(i));
                            c0.setCellStyle(bottomStyle);
                            for (int n = cellAddresses2.getFirstColumn() + 1; n <= cellAddresses2.getLastColumn(); n++) {
                                XSSFCell cn = bottom.createCell(n);
                                cn.setCellStyle(bottomStyle);
                            }
                        }
                    }
                    ind++;
                }
            }

            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            // 设置响应头
            response.setHeader("Content-Disposition", "attchement; filename=" + URLEncoder.encode(sheetName + ".xlsx", "UTF-8"));
            response.setContentType("application/msexcel;charset=utf-8");
            wb.write(output);
            wb.close();
            // 关闭输出流
            output.close();
        } catch (Exception e) {
            log.error("ExcelPoiUtil -- excelPort Exception = {e}", e);
        }
    }

    public static List<PrjMember> memberImport(MultipartFile file) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<PrjMember> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            PrjMember prjMember = new PrjMember();
            if (row.getCell(0).toString().isEmpty()){
            prjMember.setSxbh(i);
            }else {

                prjMember.setSxbh(Double.valueOf(row.getCell(0).toString()).intValue());
                prjMember.setRy(Double.valueOf(row.getCell(1).toString()).intValue());
                prjMember.setRyxm(row.getCell(2).toString().trim());
                prjMember.setXmjsmc(row.getCell(3).toString().trim());
                prjMember.setBxmfg(row.getCell(4).toString().trim());
                prjMember.setTrgzzys(Double.valueOf(row.getCell(5).toString()).intValue());
                list.add(prjMember);
            }
        }
        return list;
    }

    public static List<StaDirector> directorImport(MultipartFile file) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaDirector> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaDirector staDirector = new StaDirector();
            if (row.getCell(0).toString().isEmpty())
                staDirector.setXh(i);
            else
                staDirector.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
            staDirector.setFzr(Double.valueOf(row.getCell(1).toString()).intValue());
            staDirector.setXm(row.getCell(2).toString().trim());
            list.add(staDirector);
        }
        return list;
    }

    /**
     *  差旅费批量导入
     * @param file
     * @return
     * @throws IOException
     */
    public static List<StaTravelexpenses> clfImport(MultipartFile file) throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaTravelexpenses> list = new ArrayList<>();
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
             row = sheet.getRow(i);
             StaTravelexpenses staTravelexpenses =new StaTravelexpenses();

            if (row.getCell(0).toString().isEmpty())
                staTravelexpenses.setXh(String.valueOf(i));
            else {
                staTravelexpenses.setXh(row.getCell(0).toString());
                staTravelexpenses.setCcr(Double.valueOf(row.getCell(1).toString()).intValue());
                staTravelexpenses.setCcdd(row.getCell(2).toString().trim());
                staTravelexpenses.setCcts(Double.valueOf(row.getCell(3).toString()));

                XSSFCell cell = row.getCell(4);
                XSSFCell cell2 = row.getCell(4);
                Date sqlDate = null;
                Date sqlDate2 = null;
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            // 如果单元格是字符串类型，尝试解析为日期
                            String dateString = cell.getStringCellValue();
                            String date2String = cell2.getStringCellValue();
                            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE); // 根据实际格式设置
                            try {
                                // 直接将解析后的时间戳转换为 java.sql.Date
                                long timeInMillis = sdf.parse(dateString).getTime();
                                long time2InMillis = sdf.parse(date2String).getTime();
                                sqlDate = new Date(timeInMillis);
                                sqlDate2 = new Date(time2InMillis);
                            } catch (ParseException e) {
                                e.printStackTrace(); // 处理解析异常
                            }
                            break;

                        case NUMERIC:
                            // 如果单元格是数字类型，检查是否是日期
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                sqlDate = new Date(cell.getDateCellValue().getTime());
                            }
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell2)) {
                                sqlDate2 = new Date(cell2.getDateCellValue().getTime());
                            }
                            break;

                        case FORMULA:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                sqlDate = new Date(cell.getDateCellValue().getTime());
                            }
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell2)) {
                                sqlDate2 = new Date(cell2.getDateCellValue().getTime());
                            }
                            break;

                        default:
                            System.out.println("单元格不是日期类型");
                    }
                }

                if (sqlDate != null) {
                    staTravelexpenses.setQsrq(sqlDate);
                }
                if (sqlDate2 != null) {
                    staTravelexpenses.setJsrq(sqlDate2);
                }
                staTravelexpenses.setZsf(Double.valueOf(row.getCell(6).toString()));
                staTravelexpenses.setCb(Double.valueOf(row.getCell(7).toString()));
                staTravelexpenses.setJtf(Double.valueOf(row.getCell(8).toString()));
                staTravelexpenses.setRpj(Double.valueOf(row.getCell(9).toString()));
                staTravelexpenses.setZj(Double.valueOf(row.getCell(10).toString()));
            }
            list.add(staTravelexpenses);
        }


            return list;
    }

    /**
     * 会议费批量导入
     * @param file
     * @return
     * @throws IOException
     */
    public static List<StaMeetingFee> hyfImport(MultipartFile file) throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaMeetingFee> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaMeetingFee staMeetingFee =new StaMeetingFee();
            if (row.getCell(0).toString().isEmpty())
                staMeetingFee.setXh(i);
            else {
                staMeetingFee.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staMeetingFee.setHydd(row.getCell(1).toString().trim());
            }
            list.add(staMeetingFee);
        }

        return list;
    }

    public static  List<StaSourceFee> zlfImport(MultipartFile file) throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaSourceFee> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaSourceFee staSourceFee =new StaSourceFee();
            if (row.getCell(0).toString().isEmpty())
                staSourceFee.setXh(i);
            else {
                staSourceFee.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staSourceFee.setTsmc(row.getCell(1).toString().trim());
                staSourceFee.setLyr(row.getCell(2).toString().trim());
                staSourceFee.setCbs(row.getCell(3).toString().trim());
                staSourceFee.setSl(Double.valueOf(row.getCell(4).toString()).intValue());
                staSourceFee.setZj(Double.valueOf(row.getCell(5).toString()));
            }
            list.add(staSourceFee);
        }
        return list;
    }

    public static List<StaPrintingFee> ysfImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaPrintingFee> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaPrintingFee staPrintingFee =new StaPrintingFee();
            if (row.getCell(0).toString().isEmpty())
                staPrintingFee.setXh(i);
            else {
                staPrintingFee.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staPrintingFee.setZlmc(row.getCell(1).toString().trim());
                staPrintingFee.setDcys(Double.valueOf(row.getCell(2).toString()).intValue());
                staPrintingFee.setDycs(Double.valueOf(row.getCell(3).toString()).intValue());
                staPrintingFee.setDcfy(Double.valueOf(row.getCell(4).toString()));
                staPrintingFee.setHj(Double.valueOf(row.getCell(5).toString()));
            }
            list.add(staPrintingFee);
        }
        return list;
    }

    public static  List<StaIntellectualProperty> zscqfImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaIntellectualProperty> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaIntellectualProperty staIntellectualProperty =new StaIntellectualProperty();
            if (row.getCell(0).toString().isEmpty())
                staIntellectualProperty.setXh(i);
            else {
                staIntellectualProperty.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staIntellectualProperty.setCqlb(row.getCell(1).toString().trim());
                staIntellectualProperty.setMc(row.getCell(2).toString().trim());
                staIntellectualProperty.setWtjg(row.getCell(3).toString().trim());
            }
            list.add(staIntellectualProperty);
        }
        return list;
    }

    public static List<StaIndirectCosts> jjfImport(MultipartFile file) throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaIndirectCosts> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaIndirectCosts staIndirectCosts = new StaIndirectCosts();
            if (row.getCell(0).toString().isEmpty())
                staIndirectCosts.setXh(String.valueOf(i));
            else {
                staIndirectCosts.setXh(row.getCell(0).toString());
                staIndirectCosts.setFyyt(row.getCell(1).toString().trim());
                staIndirectCosts.setSqje(Double.valueOf(row.getCell(2).toString()));
                staIndirectCosts.setSl(row.getCell(3).toString());
                staIndirectCosts.setBzje(Double.valueOf(row.getCell(4).toString()));
                staIndirectCosts.setBhsje(Double.valueOf(row.getCell(5).toString()));
                staIndirectCosts.setBz(row.getCell(6).toString().trim());
            }
            list.add(staIndirectCosts);
        }
        return list;
    }

    public static  List<StaExpertFee> zjfImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaExpertFee> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaExpertFee staExpertFee = new StaExpertFee();
            if (row.getCell(0).toString().isEmpty())
                staExpertFee.setXh(i);
            else {
                staExpertFee.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staExpertFee.setZjxm(row.getCell(1).toString().trim());
                staExpertFee.setSfzh(row.getCell(2).toString().trim());
                staExpertFee.setZc(row.getCell(3).toString().trim());
            }
            list.add(staExpertFee);
        }
        return list;
    }

    public static List<StaDeviceexpenses> devImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaDeviceexpenses> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaDeviceexpenses staDeviceexpenses =new StaDeviceexpenses();
            if (row.getCell(0).toString().isEmpty())
                staDeviceexpenses.setXh(i);
            else {
                staDeviceexpenses.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staDeviceexpenses.setSbmc(row.getCell(1).toString().trim());
                staDeviceexpenses.setSbxh(row.getCell(2).toString().trim());
                staDeviceexpenses.setSl(Double.valueOf(row.getCell(3).toString()).intValue());
                staDeviceexpenses.setDj(Double.valueOf(row.getCell(4).toString()));
                staDeviceexpenses.setZj(Double.valueOf(row.getCell(5).toString()));
                XSSFCell cell = row.getCell(6);
                Date sqlDate = null;
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            // 如果单元格是字符串类型，尝试解析为日期
                            String dateString = cell.getStringCellValue();
                            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE);
                            try {
                                // 直接将解析后的时间戳转换为 java.sql.Date
                                long timeInMillis = sdf.parse(dateString).getTime();
                                sqlDate = new Date(timeInMillis);
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            break;
                        case NUMERIC:
                            // 如果单元格是数字类型，检查是否是日期
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                sqlDate = new Date(cell.getDateCellValue().getTime());
                            }
                            break;

                        case FORMULA:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                sqlDate = new Date(cell.getDateCellValue().getTime());
                            }
                            break;

                        default:
                            System.out.println("单元格不是日期类型");
                    }
                }
                if (sqlDate!=null){
                    staDeviceexpenses.setGzsj(sqlDate);
                }
                staDeviceexpenses.setSyfjqdj(Double.valueOf(row.getCell(7).toString()));
                staDeviceexpenses.setScgbydq(row.getCell(8).toString().trim());
                staDeviceexpenses.setLsdw(row.getCell(9).toString().trim());
                staDeviceexpenses.setSbzkjzyxnzb(row.getCell(10).toString().trim());
            }
            list.add(staDeviceexpenses);
        }
        return list;
    }

    public static List<StaMaterial> CLfsImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaMaterial> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaMaterial staMaterial = new StaMaterial();
            if (row.getCell(0).toString().isEmpty())
                staMaterial.setXh(String.valueOf(i));
            else {
                staMaterial.setXh(row.getCell(0).toString().trim());
                staMaterial.setClmc(row.getCell(1).toString().trim());
                staMaterial.setWlbm(row.getCell(2).toString().trim());
                staMaterial.setCgdj(Double.valueOf(row.getCell(3).toString()));
                staMaterial.setBz(row.getCell(4).toString().trim());
            }
            list.add(staMaterial);
        }
        return list;
    }

    public static List<StaSoftwareexpenses> swImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaSoftwareexpenses> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaSoftwareexpenses staSoftwareexpenses =new StaSoftwareexpenses();
            if (row.getCell(0).toString().isEmpty())
                staSoftwareexpenses.setXh(i);
            else {
                staSoftwareexpenses.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staSoftwareexpenses.setRjmc(row.getCell(1).toString().trim());
                staSoftwareexpenses.setRjxh(row.getCell(2).toString().trim());
                staSoftwareexpenses.setSl(Double.valueOf(row.getCell(3).toString()).intValue());
                staSoftwareexpenses.setDj(Double.valueOf(row.getCell(4).toString()));
                staSoftwareexpenses.setZj(Double.valueOf(row.getCell(5).toString()));
                XSSFCell cell = row.getCell(6);
                Date sqlDate = null;
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            // 如果单元格是字符串类型，尝试解析为日期
                            String dateString = cell.getStringCellValue();
                            SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE);
                            try {
                                // 直接将解析后的时间戳转换为 java.sql.Date
                                long timeInMillis = sdf.parse(dateString).getTime();
                                sqlDate = new Date(timeInMillis);
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                            break;
                        case NUMERIC:
                            // 如果单元格是数字类型，检查是否是日期
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                sqlDate = new Date(cell.getDateCellValue().getTime());
                            }
                            break;

                        case FORMULA:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                sqlDate = new Date(cell.getDateCellValue().getTime());
                            }
                            break;

                        default:
                            System.out.println("单元格不是日期类型");
                    }
                }
                if (sqlDate!=null){
                    staSoftwareexpenses.setGzsj(sqlDate);
                }
                staSoftwareexpenses.setSyfjqdj(Double.valueOf(row.getCell(7).toString()));
                staSoftwareexpenses.setScgbydq(row.getCell(8).toString().trim());
                staSoftwareexpenses.setLsdw(row.getCell(9).toString().trim());
                staSoftwareexpenses.setRjzyjsxnzb(row.getCell(10).toString().trim());
            }
            list.add(staSoftwareexpenses);
        }
        return list;
    }
    public static List<StaOutexpense> WwImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaOutexpense> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaOutexpense staOutexpense = new StaOutexpense();
            if (row.getCell(0).toString().isEmpty())
                staOutexpense.setXh(i);
            else {
                staOutexpense.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staOutexpense.setXzyjrwmc(row.getCell(1).toString().trim());
                staOutexpense.setXzyjrwnr(row.getCell(2).toString().trim());
                staOutexpense.setXzyjrwcddw(row.getCell(3).toString().trim());
                staOutexpense.setJflz(row.getCell(4).toString().trim());
            }
            list.add(staOutexpense);
        }
        return list;
    }
    public static List<StaOutttesting> WxImport(MultipartFile file)throws IOException{
        XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        List<StaOutttesting> list = new ArrayList<>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //获取每一行数据
            row = sheet.getRow(i);
            StaOutttesting staOutttesting = new StaOutttesting();
            if (row.getCell(0).toString().isEmpty())
                staOutttesting.setXh(i);
            else {
                staOutttesting.setXh(Double.valueOf(row.getCell(0).toString()).intValue());
                staOutttesting.setWxcssyyjgnr(row.getCell(1).toString().trim());
                staOutttesting.setWxcshyyjgdw(row.getCell(2).toString().trim());
                staOutttesting.setWxcshyyjgfy(Double.valueOf(row.getCell(3).toString()));
            }
            list.add(staOutttesting);
        }
        return list;
    }
}
