Java 处理excel，打包成exe

package com.nsk666;  
  
   
public class Main {  
    public static void main(String[] args) {  
         new MainPanel();   
    }   
}

package com.nsk666;  
  
import net.miginfocom.swing.MigLayout;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.jdesktop.swingx.JXDatePicker;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
  
import javax.swing.*;  
import javax.swing.filechooser.FileNameExtensionFilter;  
import java.awt.*;  
import java.awt.event.*;  
import java.io.*;  
import java.util.Date;  
import java.util.HashSet;  
  
public class MainPanel extends JFrame implements KeyListener,ActionListener {  
    private static final Logger logger = LoggerFactory.getLogger(MainPanel.class);  
    private static final int WIDTH = 800;  
    private static final int HEIGHT = 600;  
    private final Container contentPane = this.getContentPane();  
    private DataEntity dataEntity;  
    private File file;  
    /**  
     * 标题头  
     */  
    private JTextField title;  
    /**  
     * 委托编号  
     */  
    private JTextField orderNumber;  
    /**  
     * 检验依据  
     */  
    private JTextField gist;  
    /**  
     * 仪器型号（编号）  
     */  
    private JTextField instrumentNo;  
    /**  
     * 实验湿度  
     */  
    private JTextField humidness;  
    /**  
     * 实验温度  
     */  
    private JTextField temperature;  
    /**  
     * 仪器检定有效期至  
     */  
    private JXDatePicker validDate;  
    /**  
     * 实验日期  
     */  
    private JXDatePicker examDateFrom;  
    /**  
     * 实验结束日期  
     */  
    private JXDatePicker examDateTo;  
    /**  
     * 试样编号  
     */  
    private JTextField examNo;  
  
    private JButton upload;  
    private JLabel uploadSuccess;  
    private JButton download;  
    private  HSSFWorkbook formatWorkbook;  
  
    public MainPanel() {  
        super("ExcelTool");  
        logger.error("123");  
        logger.info("123");  
        // 增加美化效果  
        try {  
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {  
                if ("Nimbus".equals(info.getName())) {  
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());  
                    break;  
                }  
            }  
        } catch (Exception e) {  
            logger.error("", e);  
        }  
        // 初始化窗体  
        initJFrame();  
        // 初始化组件  
        initPanel();  
        // 初始化数据  
        initBiz();  
        //显示窗体  
        this.setVisible(true);    //设置窗口可见  
    }  
  
    private void initJFrame() {  
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);  
        // 获取当前屏幕宽高  
        Dimension screensize = Toolkit.getDefaultToolkit().getScreenSize();  
        int screenWidth = (int) screensize.getWidth();//获得屏幕得宽  
        int screenHeight = (int) screensize.getHeight();//获得屏幕得高  
        // 设置窗口大小  
        this.setBounds((screenWidth - WIDTH) / 2, (screenHeight - HEIGHT) / 2, WIDTH, HEIGHT);  
  
    }  
  
    private void initBiz() {  
        try {  
            dataEntity = ExcelUtils.loadLocalData();  
            formatWorkbook = ExcelUtils.loadFormat();  
        }catch (Exception e){  
            JOptionPane.showMessageDialog(this, "读取格式文件失败！", "错误",  
                    JOptionPane.ERROR_MESSAGE);  
            logger.error("读取格式文件失败",e);  
        }  
        if (dataEntity == null) {  
            dataEntity = new DataEntity();  
        }else{  
            title.setText(dataEntity.getTitle());  
            orderNumber.setText(dataEntity.getOrderNumber());  
            gist.setText(dataEntity.getGist());  
            instrumentNo.setText(dataEntity.getInstrumentNo());  
            humidness.setText(dataEntity.getHumidness());  
            temperature.setText(dataEntity.getTemperature());  
            validDate.setDate(dataEntity.getValidDate());  
            examDateFrom.setDate(dataEntity.getExamDateFrom());  
            examDateTo.setDate(dataEntity.getExamDateTo());  
            examNo.setText(dataEntity.getExamNo());  
        }  
    }  
  
    private void initPanel() {  
        JPanel panel = new BasePanel();  
        panel.setLayout(new FlowLayout());  
        panel.setPreferredSize(new Dimension(WIDTH, HEIGHT));  
        panel.setBackground(Color.WHITE);  
        contentPane.add(panel, BorderLayout.CENTER);  
        {  
            JPanel row1 = new BasePanel();  
            row1.setLayout(new MigLayout("", "[]10[]", "grow"));  
            row1.add(new JLabel("标题头："), "cell 0 0");  
            title = new JTextField();  
            title.setPreferredSize(new Dimension(200, 30));  
            title.addKeyListener(this);  
            row1.add(title, "cell 1 0");  
            panel.add(row1);  
  
            JPanel row2 = new BasePanel();  
            row2.setLayout(new MigLayout("", "110[40]10[]30[40]10[]110", "grow"));  
            row2.add(new JLabel("委托编号："), "cell 0 0");  
            orderNumber = new JTextField();  
            orderNumber.setPreferredSize(new Dimension(200, 30));  
            orderNumber.addKeyListener(this);  
            row2.add(orderNumber, "cell 1 0");  
  
            row2.add(new JLabel("检验依据："), "cell 2 0");  
            gist = new JTextField();  
            gist.setPreferredSize(new Dimension(200, 30));  
            gist.addKeyListener(this);  
            row2.add(gist, "cell 3 0");  
            panel.add(row2);  
  
            JPanel row3 = new BasePanel();  
            row3.setLayout(new MigLayout("", "110[40]10[450]110", "grow"));  
            row3.add(new JLabel("仪器型号（编号）："), "cell 0 0");  
            instrumentNo = new JTextField();  
            instrumentNo.setPreferredSize(new Dimension(450, 30));  
            instrumentNo.addKeyListener(this);  
            row3.add(instrumentNo, "cell 1 0");  
            panel.add(row3);  
  
            JPanel row4 = new BasePanel();  
            row4.setLayout(new MigLayout("", "100[40]10[][10]30[40]10[][10]100", "grow"));  
  
            row4.add(new JLabel("实验湿度："), "cell 0 0");  
            humidness = new JTextField();  
            humidness.setPreferredSize(new Dimension(180, 30));  
            humidness.addKeyListener(this);  
            row4.add(humidness, "cell 1 0");  
            row4.add(new JLabel("%"), "cell 2 0");  
  
            row4.add(new JLabel("实验温度："), "cell 3 0");  
            temperature = new JTextField();  
            temperature.setPreferredSize(new Dimension(180, 30));  
            temperature.addKeyListener(this);  
            row4.add(temperature, "cell 4 0");  
            row4.add(new JLabel("℃"), "cell 5 0");  
            panel.add(row4);  
  
            JPanel row5 = new BasePanel();  
            row5.setLayout(new MigLayout("", "[40]10[40]30[40]10[40][40][10][40]", "grow"));  
            row5.add(new JLabel("仪器检定有效期至："), "cell 0 0");  
            validDate = new JXDatePicker();  
            validDate.setDate(new Date());  
            validDate.setPreferredSize(new Dimension(200, 30));  
            validDate.addActionListener(this::dealDate);  
            row5.add(validDate, "cell 1 0");  
            row5.add(new JLabel("实验日期从："), "cell 2 0");  
            examDateFrom = new JXDatePicker();  
            examDateFrom.setDate(new Date());  
            examDateFrom.addActionListener(this::dealDate);  
            examDateFrom.setPreferredSize(new Dimension(200, 30));  
            row5.add(examDateFrom, "cell 3 0");  
            row5.add(new JLabel("到："), "cell 4 0");  
            panel.add(row5);  
            examDateTo = new JXDatePicker();  
            examDateTo.setDate(new Date());  
            examDateTo.addActionListener(this::dealDate);  
            examDateTo.setPreferredSize(new Dimension(200, 30));  
            row5.add(examDateTo, "cell 5 0");  
  
            JPanel row6 = new BasePanel();  
            row6.setLayout(new MigLayout("", "110[40]10[450]110", "grow"));  
            row6.add(new JLabel("试样编号："), "cell 0 0");  
            examNo = new JTextField();  
            examNo.setPreferredSize(new Dimension(450, 30));  
            examNo.addKeyListener(this);  
            row6.add(examNo, "cell 1 0");  
            panel.add(row6);  
  
            JPanel row7 = new BasePanel();  
            row7.setLayout(new MigLayout("", "[40]10[]5[]30[40]10[]", "grow"));  
            row7.add(new JLabel("上传文件："), "cell 0 0");  
            upload = new JButton("上传");  
            upload.addActionListener(this);  
            upload.setPreferredSize(new Dimension(50, 30));  
            row7.add(upload, "cell 1 0");  
            uploadSuccess = new JLabel("");  
            uploadSuccess.setForeground(Color.RED);  
            row7.add(uploadSuccess,"cell 2 0");  
  
            row7.add(new JLabel("解析下载："), "cell 3 0");  
            download = new JButton("下载");  
            download.setPreferredSize(new Dimension(50, 30));  
            download.addActionListener(this);  
            row7.add(download, "cell 3 0");  
            panel.add(row7);  
  
        }  
    }  
  
    private void dealUpload() {  
        // 创建文件选择器  
        JFileChooser fileChooser = new JFileChooser();  
        // 只能选择文件  
        fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);  
        // 选择当前文件夹开始  
        fileChooser.setCurrentDirectory(new java.io.File("."));  
        fileChooser.setDialogTitle("请选择文件");  
        // 过滤文件类型 只能选择xls文件  
        FileNameExtensionFilter filter = new FileNameExtensionFilter("xls", "xls");  
        fileChooser.setFileFilter(filter);  
        // 只能选择一个文件  
        fileChooser.setMultiSelectionEnabled(false);  
        JButton jButton = new JButton();  
        int result = fileChooser.showOpenDialog(jButton);  
        if (result == JFileChooser.APPROVE_OPTION) {  
            // 得到选择的文件  
            File file = fileChooser.getSelectedFile();  
            if (file == null) {  
                return;  
            }  
            this.file = file;  
            this.uploadSuccess.setText("上传成功："+file.getName());  
            JOptionPane.showMessageDialog(this, "上传成功！", "提示",  
                    JOptionPane.INFORMATION_MESSAGE);  
        }  
        logger.info("file:{}", fileChooser.getSelectedFile());  
    }  
    private void dealDownLoad(){  
        // 设置日期。防止日期为空  
        if (this.dataEntity.getValidDate() == null){  
            this.dataEntity.setValidDate(validDate.getDate());  
        }  
        if (this.dataEntity.getExamDateFrom() == null){  
            this.dataEntity.setExamDateFrom(examDateFrom.getDate());  
        }  
        if (this.dataEntity.getExamDateTo() == null){  
            this.dataEntity.setExamDateTo(examDateTo.getDate());  
        }  
        if (this.file !=null){  
            try {  
                ExcelUtils.parseData(this.file,this.dataEntity,this.formatWorkbook);  
                JOptionPane.showMessageDialog(this,"导出成功","提示",  
                        JOptionPane.WARNING_MESSAGE);  
            }catch (Exception e){  
                logger.error("解析错误",e);JOptionPane.showMessageDialog(this,e.getMessage(),"提示",  
                        JOptionPane.ERROR_MESSAGE);  
            }  
        }else{  
            JOptionPane.showMessageDialog(this,"请先上传文件","提示",  
                    JOptionPane.WARNING_MESSAGE);  
        }  
    }  
  
    /**  
     * 时间选择框，选择触发后调用  
     */  
    private void dealDate(ActionEvent event) {  
        JXDatePicker picker = (JXDatePicker) event.getSource();  
        if (picker == validDate) {  
            this.dataEntity.setValidDate(picker.getDate());  
        } else if (picker == examDateFrom) {  
            this.dataEntity.setExamDateFrom(picker.getDate());  
        } else if (picker == examDateTo) {  
            this.dataEntity.setExamDateTo(picker.getDate());  
        } else {  
            logger.error("未知事件");  
        }  
        ExcelUtils.saveLocalData(dataEntity);  
    }  
  
  
    @Override  
    public void actionPerformed(ActionEvent e) {  
        Object source = e.getSource();  
        if (source == upload){  
            dealUpload();  
        }else if (source == download){  
            dealDownLoad();  
        }else{  
            logger.error("未知事件");  
        }  
    }  
  
    /**  
     * 文本框键入事件   
*/  
    @Override  
    public void keyTyped(KeyEvent e) {  
    }  
  
    /**  
     * 文本框按下事件  
     */  
    @Override  
    public void keyPressed(KeyEvent e) {  
    }  
  
    /**  
     * 文本框松开按键事件 ，监控最后一个文本框，防止  
     */  
    @Override  
    public void keyReleased(KeyEvent e) {  
        JTextField source = (JTextField) e.getSource();  
        if (source == title) {  
            this.dataEntity.setTitle(source.getText());  
        } else if (source == orderNumber) {  
            this.dataEntity.setOrderNumber(source.getText());  
        } else if (source == gist) {  
            this.dataEntity.setGist(source.getText());  
        } else if (source == instrumentNo) {  
            this.dataEntity.setInstrumentNo(source.getText());  
        } else if (source == humidness) {  
            this.dataEntity.setHumidness(source.getText());  
        } else if (source == temperature) {  
            this.dataEntity.setTemperature(source.getText());  
        }else if (source == examNo){  
            this.dataEntity.setExamNo(source.getText());  
        }else {  
            logger.error("未知事件");  
        }  
        ExcelUtils.saveLocalData(dataEntity);  
    }  
  
    private static class BasePanel extends JPanel {  
        public BasePanel() {  
            this.setBackground(Color.WHITE);  
        }  
    }  
  
  
}

package com.nsk666;  
  
import com.alibaba.fastjson2.JSONObject;  
import org.apache.poi.hssf.usermodel.*;  
import org.apache.poi.ss.usermodel.*;  
import org.apache.poi.ss.util.CellAddress;  
import org.apache.poi.ss.util.CellRangeAddress;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
  
import java.io.*;  
import java.math.BigDecimal;  
import java.math.RoundingMode;  
import java.nio.charset.StandardCharsets;  
import java.text.SimpleDateFormat;  
import java.util.*;  
  
  
public class ExcelUtils {  
  
    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);  
  
    /**  
     * 解析xls文件  
     */  
    public static void loadXls(FileInputStream fileInputStream, HSSFWorkbook hssfWorkbook) {  
        try {  
            //创建工作簿  
            hssfWorkbook = new HSSFWorkbook(fileInputStream);  
            //获取工作簿下sheet的个数  
            int sheetNum = hssfWorkbook.getNumberOfSheets();  
            System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");  
            //遍历工作簿中的所有数据  
            for (int i = 0; i < 3; i++) {  
                //读取第i个工作表  
                System.out.println("读取第" + (i + 1) + "个sheet");  
                HSSFSheet sheet = hssfWorkbook.getSheetAt(i);  
                //获取最后一行的num，即总行数。此处从0开始  
                int maxRow = sheet.getLastRowNum();  
                for (int row = 0; row <= maxRow; row++) {  
                    //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***  
                    int maxRol = sheet.getRow(row).getLastCellNum();  
                    System.out.println("--------第" + row + "行的数据如下--------");  
                    for (int rol = 0; rol < maxRol; rol++) {  
                        if (rol == 1 && row == 4) {  
                            sheet.getRow(row).getCell(rol).setCellValue("测试");  
                        }  
                        System.out.print(sheet.getRow(row).getCell(rol) + " ");  
                    }  
                    System.out.println();  
                }  
                sheet.shiftRows(19, sheet.getLastRowNum(), 1);  
                HSSFRow row = sheet.getRow(18);  
                HSSFCell cell1 = row.getCell(0);  
                short height = row.getHeight();  
                HSSFCellStyle cellStyle = cell1.getCellStyle();  
                HSSFCell cell = sheet.createRow(19).createCell(0);  
                cell.setCellStyle(cellStyle);  
                cell.setCellValue("测试");  
            }  
            hssfWorkbook.createSheet();  
            hssfWorkbook.setSheetName(sheetNum, "测试");//更改sheet1的名字为“测试”设置为utf-16  
            FileOutputStream out = null;  
            try {  
                out = new FileOutputStream("sample2.xls");  
                hssfWorkbook.write(out);  
            } catch (IOException e) {  
                System.out.println(e.toString());  
            } finally {  
                try {  
                    out.close();  
                } catch (IOException e) {  
                    System.out.println(e.toString());  
                }  
            }  
  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
  
    public static void parseData(File file, DataEntity dataEntity, HSSFWorkbook formatWorkbook) throws Exception {  
        if (file != null && dataEntity != null) {  
            FileInputStream fileInputStream = new FileInputStream(file);  
            //创建工作簿  
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);  
            //获取工作簿下sheet的个数  
            int sheetNum = hssfWorkbook.getNumberOfSheets();  
            log.info("文件:{},该excel文件中总共有：{}个sheet", file.getName(), sheetNum);  
            HSSFSheet first = hssfWorkbook.getSheetAt(0);  
            parseFirstSheet(first, dataEntity);  
            while (hssfWorkbook.getNumberOfSheets()>0){  
                hssfWorkbook.removeSheetAt(0);  
            }  
            // 复制第二个sheet,保证格式  
            HSSFSheet sheet = hssfWorkbook.createSheet("结果");  
            copySheets(sheet, formatWorkbook.getSheetAt(0));  
            parseResultSheet(sheet, dataEntity);  
            // 合并垂直单元格  
            sheet.addMergedRegion(new CellRangeAddress(10, 11, 0, 0));  
            sheet.addMergedRegion(new CellRangeAddress(12, 14, 0, 0));  
            sheet.addMergedRegion(new CellRangeAddress(10, 14, 1, 1));  
            sheet.addMergedRegion(new CellRangeAddress(10, 14, 2, 2));  
            sheet.addMergedRegion(new CellRangeAddress(10, 14, 3, 3));  
            sheet.addMergedRegion(new CellRangeAddress(10, 14, 4, 4));  
            sheet.addMergedRegion(new CellRangeAddress(10, 14, 5, 5));  
            sheet.addMergedRegion(new CellRangeAddress(11, 13, 6, 6));  
            sheet.addMergedRegion(new CellRangeAddress(11, 13, 7, 7));  
            sheet.addMergedRegion(new CellRangeAddress(11, 13, 8, 8));  
            sheet.addMergedRegion(new CellRangeAddress(11, 13, 9, 9));  
            sheet.addMergedRegion(new CellRangeAddress(10, 13, 10, 10));  
            exportExcel(hssfWorkbook);  
        }  
  
    }  
  
    /**  
     * 处理结果  
     */  
    private static void parseResultSheet(HSSFSheet sheet, DataEntity dataEntity) {  
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
        int lastRowNum = sheet.getLastRowNum();  
        // 第三行Title  
        sheet.getRow(2).getCell(0).setCellValue(dataEntity.getTitle());  
  
        // 设置委托编号  
        HSSFRow row3 = sheet.getRow(4);  
        setStringVale(row3.getCell(1), dataEntity.getOrderNumber());  
        // 设置仪器编号  
        String instrumentNo = dataEntity.getInstrumentNo();  
        if (instrumentNo != null && !instrumentNo.isEmpty()) {  
            HSSFFont font = sheet.getRow(5).getCell(2).getCellStyle().getFont(sheet.getWorkbook());  
            String substring = instrumentNo.substring(0, instrumentNo.length() / 2);  
            setStringVale(sheet.getRow(5).getCell(2), substring);  
            sheet.getRow(6).getCell(0).getCellStyle().setFont(font);  
            setStringVale(sheet.getRow(6).getCell(0), instrumentNo.replace(substring, ""));  
        }  
        // 设置实验温度  
        setDoubleVale(sheet.getRow(7).getCell(1), dataEntity.getTemperature());  
        // 设置湿度  
        setDoubleVale(sheet.getRow(7).getCell(6), dataEntity.getHumidness());  
        // 设置实验有效期  
        setStringVale(sheet.getRow(8).getCell(2), dataEntity.getValidDate() != null ? format.format(dataEntity.getValidDate()) : "");  
        String examDateStr = dataEntity.getExamDateFrom() == null || dataEntity.getExamDateTo() == null ? "" :  
                format.format(dataEntity.getExamDateFrom()) + " ~ " + format.format(dataEntity.getExamDateTo());  
        setStringVale(sheet.getRow(8).getCell(6), examDateStr);  
        // 设置实验编号  
        setStringVale(sheet.getRow(10).getCell(0), dataEntity.getExamNo());  
        // 设置数据，从第16行 index为 15开始 ，倒数第三行应当是空白表的最后一行  
        List<DataContentEntity> contentList = dataEntity.getContentList();  
        int i = lastRowNum - 3;   // 共21行最后一盒行num为20，最后一个空白num为 18        int index = 15;  
        HSSFRow row15 = sheet.getRow(15);  
        short contentHeight = row15.getHeight();  
        for (DataContentEntity data : contentList) {  
            int rowIndex = 0;  
            // 大于18开始扩容  
            if (index > 18) {  
                //获取当前行  
                HSSFRow rowSource = sheet.getRow(index - 2);  
                // 从前一行往下移动1行  
                sheet.shiftRows(index, sheet.getLastRowNum(), 1, true, false);  
                //获取当前行样式  
                HSSFCellStyle rowStyle = rowSource.getRowStyle();  
                //新增行  
                HSSFRow rowInsert = sheet.createRow(index);  
                rowInsert.setHeight(contentHeight);  
                if (rowStyle != null) {  
                    rowInsert.setRowStyle(rowStyle);  
                    rowInsert.setHeight(rowSource.getHeight());  
                }  
                for (int col = 0; col < 11; col++) {  
                    HSSFCell cellSource = rowSource.getCell(col);  
                    HSSFCell cellInsert = rowInsert.createCell(col);  
                    HSSFCellStyle cellStyle = cellSource.getCellStyle();  
                    //设置单元格样式　　　　                    if (cellStyle != null) {  
                        cellInsert.setCellStyle(cellStyle);  
                    }  
                }  
                // 从前一行往下移动1行  
                sheet.shiftRows(index + 1, sheet.getLastRowNum(), 1, true, false);  
                //新增行  
                rowInsert = sheet.createRow(index + 1);  
                rowInsert.setHeight(contentHeight);  
                for (int col = 0; col < 11; col++) {  
                    HSSFCell cellSource = rowSource.getCell(col);  
                    HSSFCell cellInsert = rowInsert.createCell(col);  
                    HSSFCellStyle cellStyle = cellSource.getCellStyle();  
                    //设置单元格样式　　　　                    if (cellStyle != null) {  
                        cellInsert.setCellStyle(cellStyle);  
                    }  
                }  
                rowIndex = index;  
            } else {  
                rowIndex = index;  
            }  
            HSSFRow row = sheet.getRow(rowIndex);  
            setDoubleVale(row.getCell(0), data.getNo());  
            setStringVale(row.getCell(1), data.getExamSiteNo());  
            setDoubleVale(row.getCell(2), data.getWetWeight());  
            setDoubleVale(row.getCell(3), data.getBeforeWeight());  
            setDoubleVale(row.getCell(4), data.getAfterWeight());  
            setDoubleVale(row.getCell(5), data.getWetDensity());  
            // 盒号过掉  
            setDoubleVale(row.getCell(6), data.getBoxWetWeight1());  
            setDoubleVale(row.getCell(7), data.getBoxDryWeight1());  
            setDoubleVale(row.getCell(8), data.getBoxWeight1());  
            setDoubleVale(row.getCell(9), data.getWaterRate());  
            setDoubleVale(row.getCell(10), data.getDryDensity());  
            HSSFRow row2 = sheet.getRow(rowIndex + 1);  
            setDoubleVale(row2.getCell(6), data.getBoxWetWeight2());  
            setDoubleVale(row2.getCell(7), data.getBoxDryWeight2());  
            setDoubleVale(row2.getCell(8), data.getBoxWeight2());  
            // 合并单元格  
            for (int i1 = 0; i1 < 6; i1++) {  
                // 合并单元格，合并行 index和index+1, 合并列 i1                CellRangeAddress cellAddresses = new CellRangeAddress(index, index + 1, i1, i1);  
                sheet.addMergedRegion(cellAddresses);  
            }  
            // 合并单元格  
            for (int i1 = 9; i1 < 11; i1++) {  
                // 合并单元格，合并行 index和index+1, 合并列 i1                CellRangeAddress cellAddresses = new CellRangeAddress(index, index + 1, i1, i1);  
                sheet.addMergedRegion(cellAddresses);  
            }  
            index += 2;  
        }  
    }  
  
    private static void setStringVale(HSSFCell cell, String value) {  
        cell.getCellStyle().setAlignment(HorizontalAlignment.CENTER);  
        cell.setCellValue("");  
        cell.setCellType(CellType.STRING);  
        cell.setCellValue(value);  
    }  
  
    private static void setDoubleVale(HSSFCell cell, Object value) {  
        cell.getCellStyle().setAlignment(HorizontalAlignment.CENTER);  
        cell.setCellValue(0);  
        cell.setCellType(CellType.NUMERIC);  
        BigDecimal bigDecimal = new BigDecimal(value == null ? 0 : Double.valueOf(value.toString()));  
        // 保留2位小数  
        bigDecimal = bigDecimal.setScale(2, BigDecimal.ROUND_HALF_UP);  
        cell.setCellValue(bigDecimal.doubleValue());  
    }  
  
    /**  
     * 处理第一个sheet数据。提取数据  
     */  
    private static void parseFirstSheet(HSSFSheet sheet, DataEntity dataEntity) throws Exception {  
        if (sheet != null && dataEntity != null) {  
            List<DataContentEntity> contentEntityList = new LinkedList<>();  
            dataEntity.setContentList(contentEntityList);  
            // 获取当前sheet的最后一行行号  
            int lastRowNum = sheet.getLastRowNum();  
            // 根据格式，除了第一行，下面都是数据。并且A-M列为数据列共13列  
            for (int rowIndex = 1; rowIndex <= lastRowNum; rowIndex++) {  
                DataContentEntity entity = new DataContentEntity();  
                HSSFRow row = sheet.getRow(rowIndex);  
                // 第一个应当是顺序号，但是实际是一个表达式，不解析，使用功能LinkList保证顺序  
                HSSFCell cell0 = row.getCell(0);  
                entity.setNo(rowIndex);  
                // 桩号及位置  
                HSSFCell cell1 = row.getCell(1);  
                entity.setExamSiteNo(cell1 == null ? "" : cell1.getStringCellValue());  
                // 试坑中湿土样质量  
                HSSFCell cell2 = row.getCell(2);  
                testDoubleVale(cell2);  
                entity.setWetWeight(cell2 == null ? 0.0 : cell2.getNumericCellValue());  
                // 筒＋量砂质量  
                HSSFCell cell3 = row.getCell(3);  
                // 筒＋剩余砂质量  
                HSSFCell cell4 = row.getCell(4);  
                // 筒+量砂总质量  灌砂前量砂+筒质量  
                HSSFCell cell5 = row.getCell(5);  
                testDoubleVale(cell5);  
                entity.setBeforeWeight(cell5 == null ? 0.0 : cell5.getNumericCellValue());  
                // 灌满试坑后剩砂＋试筒质量 灌砂后量砂+筒质量  
                HSSFCell cell6 = row.getCell(6);  
                testDoubleVale(cell6);  
                entity.setAfterWeight(cell6 == null ? 0.0 : cell6.getNumericCellValue());  
                // 计算湿密度  
                calculateWetDensity(entity);  
                // 盒质量1  
                HSSFCell cell7 = row.getCell(7);  
                testDoubleVale(cell7);  
                entity.setBoxWeight1(cell7 == null ? 0.0 : cell7.getNumericCellValue());  
                // 盒质量2  
                HSSFCell cell8 = row.getCell(8);  
                testDoubleVale(cell8);  
                entity.setBoxWeight2(cell8 == null ? 0.0 : cell8.getNumericCellValue());  
                // 盒子湿土质量1  
                HSSFCell cell9 = row.getCell(9);  
                testDoubleVale(cell9);  
                entity.setBoxWetWeight1(cell9 == null ? 0.0 : cell9.getNumericCellValue());  
                // 盒子湿土质量2  
                HSSFCell cell10 = row.getCell(10);  
                testDoubleVale(cell10);  
                entity.setBoxWetWeight2(cell10 == null ? 0.0 : cell10.getNumericCellValue());  
                // 盒子干土质量1  
                HSSFCell cell11 = row.getCell(11);  
                testDoubleVale(cell11);  
                entity.setBoxDryWeight1(cell11 == null ? 0.0 : cell11.getNumericCellValue());  
                // 盒子干土质量2  
                HSSFCell cell12 = row.getCell(12);  
                testDoubleVale(cell12);  
                entity.setBoxDryWeight2(cell12 == null ? 0.0 : cell12.getNumericCellValue());  
                // 计算含水率  
                calculateWaterRate(entity);  
                // 计算干密度  
                calculateDryDensity(entity);  
                contentEntityList.add(entity);  
            }  
        }  
    }  
  
    private static void calculateWetDensity(DataContentEntity entity) {  
        if (entity != null) {  
            double v = entity.getWetWeight() * 1.0 /( (entity.getBeforeWeight() - entity.getAfterWeight() - 1306)/1.53);  
            entity.setWetDensity(v);  
        }  
    }  
  
    private static void calculateDryDensity(DataContentEntity entity) {  
        if (entity != null) {  
            entity.setDryDensity(entity.getWetDensity() / (1 + entity.getWaterRate()));  
        }  
    }  
  
    private static void calculateWaterRate(DataContentEntity entity) {  
        if (entity != null) {  
            double v1 = (entity.getBoxWetWeight1() - entity.getBoxDryWeight1()) / (entity.getBoxDryWeight1() - entity.getBoxWeight1());  
            double v2 = (entity.getBoxWetWeight2() - entity.getBoxDryWeight2()) / (entity.getBoxDryWeight2() - entity.getBoxWeight2());  
            entity.setWaterRate(((v1 + v2) / 2)*100);  
        }  
    }  
  
    /**  
     * 测试值类型  
     */  
    private static void testDoubleVale(HSSFCell cell) throws Exception {  
        if (cell != null && CellType.NUMERIC != cell.getCellType()) {  
            log.error("第" + (cell.getRow().getRowNum() + 1) + "行，第" + (cell.getColumnIndex() + 1) + "列不是数字");  
            throw new Exception("第" + (cell.getRow().getRowNum() + 1) + "行，第" + (cell.getColumnIndex() + 1) + "列不是数字");  
        }  
    }  
  
    public static void exportExcel(HSSFWorkbook hssfWorkbook) {  
        FileOutputStream fileOutputStream = null;  
        try {  
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");  
            fileOutputStream = new FileOutputStream("sampleExport_" + format.format(new Date()) + ".xls");  
            hssfWorkbook.write(fileOutputStream);  
        } catch (IOException e) {  
            log.error("", e);  
        } finally {  
            try {  
                if (fileOutputStream != null) {  
                    fileOutputStream.close();  
                }  
            } catch (IOException e) {  
                log.error("", e);  
            }  
        }  
    }  
  
    public static DataEntity loadLocalData() {  
        // 项目根路径  
        File file = new File("data.json");  
        if (file.exists()) {  
            // 读取文件  
            return readerMethod(file);  
        }  
        return null;  
    }  
  
    public static void saveLocalData(DataEntity dataEntity) {  
        saveFile(dataEntity);  
    }  
  
    private static synchronized void saveFile(DataEntity dataEntity) {  
        new Thread(() -> {  
            // 项目根路径  
            try {  
                if (dataEntity != null) {  
                    // 保存文件  
                    File file = new File("data.json");  
                    if (!file.exists()) {  
                        file.createNewFile();  
                    }  
                    FileWriter fileWriter = new FileWriter(file);  
                    fileWriter.write(JSONObject.toJSONString(dataEntity));  
                    fileWriter.flush();  
                    fileWriter.close();  
                }  
            } catch (Exception e) {  
                log.error("保存本地文件错误", e);  
            }  
        }).start();  
    }  
  
    private static DataEntity readerMethod(File file) {  
        try {  
            FileReader fileReader = new FileReader(file);  
            Reader reader = new InputStreamReader(new FileInputStream(file), StandardCharsets.UTF_8);  
            int ch = 0;  
            StringBuilder sb = new StringBuilder();  
            while ((ch = reader.read()) != -1) {  
                sb.append((char) ch);  
            }  
            fileReader.close();  
            reader.close();  
            String jsonStr = sb.toString();  
            return JSONObject.parseObject(jsonStr, DataEntity.class);  
        } catch (Exception e) {  
            log.error("读取本地文件错误", e);  
        }  
        return null;  
  
    }  
  
    /**  
     * 解析xlsx文件  
     */  
    public static void loadXlsx() {  
        try {  
            //创建工作簿对象  
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream("src/main/resources/副本软件.xls"));  
            //获取工作簿下sheet的个数  
            int sheetNum = xssfWorkbook.getNumberOfSheets();  
            System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");  
            //遍历工作簿中的所有数据  
            for (int i = 0; i < sheetNum; i++) {  
                //读取第i个工作表  
                System.out.println("读取第" + (i + 1) + "个sheet");  
                XSSFSheet sheet = xssfWorkbook.getSheetAt(i);  
                //获取最后一行的num，即总行数。此处从0开始  
                int maxRow = sheet.getLastRowNum();  
                for (int row = 0; row <= maxRow; row++) {  
                    //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***  
                    int maxRol = sheet.getRow(row).getLastCellNum();  
                    System.out.println("--------第" + row + "行的数据如下--------");  
                    for (int rol = 0; rol < maxRol; rol++) {  
                        System.out.print(sheet.getRow(row).getCell(rol) + "  ");  
                    }  
                    System.out.println();  
                }  
            }  
  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
  
  
    public static HSSFWorkbook loadFormat() throws Exception {  
        try {  
            HSSFWorkbook hssfWorkbook = null;  
            String path = System.getProperty("user.dir")+"\\format.xls";  
            log.info("读取模板：{}", path);  
            //打印出文件内容  
            File file = new File(path);  
            //创建工作簿  
            hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));  
            return hssfWorkbook;  
        } catch (FileNotFoundException e) {  
            throw new Exception("未找到模板文件");  
        }  
    }  
  
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet) {  
        copySheets(newSheet, sheet, true);  
    }  
  
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet,  
                                  boolean copyStyle) {  
        int maxColumnNum = 0;  
        Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>()  
                : null;  
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {  
            HSSFRow srcRow = sheet.getRow(i);  
            HSSFRow destRow = newSheet.createRow(i);  
            if (srcRow != null) {  
                copyRow(sheet, newSheet, srcRow, destRow,  
                        styleMap);  
                if (srcRow.getLastCellNum() > maxColumnNum) {  
                    maxColumnNum = srcRow.getLastCellNum();  
                }  
            }  
        }  
        for (int i = 0; i <= maxColumnNum; i++) {    //设置列宽  
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));  
        }  
    }  
  
    /**  
     * 复制并合并单元格  
     */  
    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet,  
                               HSSFRow srcRow, HSSFRow destRow,  
                               Map<Integer, HSSFCellStyle> styleMap) {  
        Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();  
        destRow.setHeight(srcRow.getHeight());  
        int deltaRows = destRow.getRowNum() - srcRow.getRowNum(); //如果copy到另一个sheet的起始行数不同  
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {  
            HSSFCell oldCell = srcRow.getCell(j); // old cell  
            HSSFCell newCell = destRow.getCell(j); // new cell  
            if (oldCell != null) {  
                if (newCell == null) {  
                    newCell = destRow.createCell(j);  
                }  
                copyCell(oldCell, newCell, styleMap);  
                CellRangeAddress mergedRegion = getMergedRegion(srcSheet,  
                        srcRow.getRowNum(), (short) oldCell.getColumnIndex());  
                if (mergedRegion != null) {  
                    CellRangeAddress newMergedRegion = new CellRangeAddress(  
                            mergedRegion.getFirstRow() + deltaRows,  
                            mergedRegion.getLastRow() + deltaRows, mergedRegion  
                            .getFirstColumn(), mergedRegion  
                            .getLastColumn());  
                    CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(  
                            newMergedRegion);  
                    if (isNewMergedRegion(wrapper, mergedRegions)) {  
                        mergedRegions.add(wrapper);  
                        destSheet.addMergedRegion(wrapper.range);  
                    }  
                }  
            }  
        }  
    }  
  
    /**  
     * 把原来的Sheet中cell（列）的样式和数据类型复制到新的sheet的cell（列）中  
     */  
    public static void copyCell(HSSFCell oldCell, HSSFCell newCell,  
                                Map<Integer, HSSFCellStyle> styleMap) {  
        if (styleMap != null) {  
            if (oldCell.getSheet().getWorkbook() == newCell.getSheet()  
                    .getWorkbook()) {  
                newCell.setCellStyle(oldCell.getCellStyle());  
            } else {  
                int stHashCode = oldCell.getCellStyle().hashCode();  
                HSSFCellStyle newCellStyle = styleMap.get(stHashCode);  
                if (newCellStyle == null) {  
                    newCellStyle = newCell.getSheet().getWorkbook()  
                            .createCellStyle();  
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());  
                    styleMap.put(stHashCode, newCellStyle);  
                }  
                newCell.setCellStyle(newCellStyle);  
            }  
        }  
        switch (oldCell.getCellType()) {  
            case STRING:  
                newCell.setCellValue(oldCell.getStringCellValue());  
                break;  
            case NUMERIC:  
                newCell.setCellValue(oldCell.getNumericCellValue());  
                break;  
            case BLANK:  
                newCell.setCellType(CellType.BLANK);  
                break;  
            case BOOLEAN:  
                newCell.setCellValue(oldCell.getBooleanCellValue());  
                break;  
            case ERROR:  
                newCell.setCellErrorValue(oldCell.getErrorCellValue());  
                break;  
            case FORMULA:  
                String value = "";  
                try {  
                    value = String.valueOf(oldCell.getStringCellValue());  
                    newCell.setCellValue(value);  
                } catch (IllegalStateException e) {  
                    String valueOf = String.valueOf(oldCell.getNumericCellValue());  
                    BigDecimal bd = new BigDecimal(Double.valueOf(valueOf));  
                    bd = bd.setScale(2, RoundingMode.HALF_UP);  
                    value = bd + "";  
                    newCell.setCellValue(value);  
                }  
                newCell.setCellFormula(oldCell.getCellFormula());  
                break;  
            default:  
                break;  
        }  
  
    }  
  
    // 获取merge对象  
    public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum,  
                                                   short cellNum) {  
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {  
            CellRangeAddress merged = sheet.getMergedRegion(i);  
            if (merged.isInRange(rowNum, cellNum)) {  
                return merged;  
            }  
        }  
        return null;  
    }  
  
    private static boolean isNewMergedRegion(  
            CellRangeAddressWrapper newMergedRegion,  
            Set<CellRangeAddressWrapper> mergedRegions) {  
        boolean bool = mergedRegions.contains(newMergedRegion);  
        return !bool;  
    }  
}

package com.nsk666;  
  
import org.apache.poi.ss.util.CellRangeAddress;  
  
public class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper> {  
  
    public CellRangeAddress range;  
  
    public CellRangeAddressWrapper(CellRangeAddress theRange) {  
        this.range = theRange;  
    }  
  
    public int compareTo(CellRangeAddressWrapper craw) {  
        if (range.getFirstColumn() < craw.range.getFirstColumn()  
                && range.getFirstRow() < craw.range.getFirstRow()) {  
            return -1;  
        } else if (range.getFirstColumn() == craw.range.getFirstColumn()  
                && range.getFirstRow() == craw.range.getFirstRow()) {  
            return 0;  
        } else {  
            return 1;  
        }  
    }  
  
}

package com.nsk666;  
  
public class DataContentEntity {  
    /**  
     * 编号  
     */  
    private Integer no;  
    /**  
     * 试坑号  
     */  
    private String examSiteNo;  
    /**  
     * 试坑中湿土样质量  
     */  
    private Double wetWeight;  
    /**  
     * 灌砂前量砂+筒质量  
     */  
    private Double beforeWeight;  
    /**  
     * 灌砂后量砂+筒质量  
     */  
    private Double afterWeight;  
    /**  
     * 湿密度  
     */  
    private Double wetDensity;  
    /**  
     * 盒+湿土质量1  
     */    private Double boxWetWeight1;  
    /**  
     * 盒+干土质量1  
     */    private Double boxDryWeight1;  
    /**  
     * 盒+湿土质量2  
     */    private Double boxWetWeight2;  
    /**  
     * 盒+干土质量2  
     */    private Double boxDryWeight2;  
    /**  
     * 盒质量1  
     */    private Double boxWeight1;  
    /**  
     * 盒质量2  
     */    private Double boxWeight2;  
    /*  
     * 含水率  
     */    private Double waterRate;  
    /**  
     * 干密度  
     */  
    private Double dryDensity;  
  
    public Integer getNo() {  
        return no;  
    }  
  
    public void setNo(Integer no) {  
        this.no = no;  
    }  
  
    public String getExamSiteNo() {  
        return examSiteNo;  
    }  
  
    public void setExamSiteNo(String examSiteNo) {  
        this.examSiteNo = examSiteNo;  
    }  
  
    public Double getWetWeight() {  
        return wetWeight;  
    }  
  
    public void setWetWeight(Double wetWeight) {  
        this.wetWeight = wetWeight;  
    }  
  
    public Double getBeforeWeight() {  
        return beforeWeight;  
    }  
  
    public void setBeforeWeight(Double beforeWeight) {  
        this.beforeWeight = beforeWeight;  
    }  
  
    public Double getAfterWeight() {  
        return afterWeight;  
    }  
  
    public void setAfterWeight(Double afterWeight) {  
        this.afterWeight = afterWeight;  
    }  
  
    public Double getWetDensity() {  
        return wetDensity;  
    }  
  
    public void setWetDensity(Double wetDensity) {  
        this.wetDensity = wetDensity;  
    }  
  
    public Double getBoxWetWeight1() {  
        return boxWetWeight1;  
    }  
  
    public void setBoxWetWeight1(Double boxWetWeight1) {  
        this.boxWetWeight1 = boxWetWeight1;  
    }  
  
    public Double getBoxDryWeight1() {  
        return boxDryWeight1;  
    }  
  
    public void setBoxDryWeight1(Double boxDryWeight1) {  
        this.boxDryWeight1 = boxDryWeight1;  
    }  
  
    public Double getBoxWetWeight2() {  
        return boxWetWeight2;  
    }  
  
    public void setBoxWetWeight2(Double boxWetWeight2) {  
        this.boxWetWeight2 = boxWetWeight2;  
    }  
  
    public Double getBoxDryWeight2() {  
        return boxDryWeight2;  
    }  
  
    public void setBoxDryWeight2(Double boxDryWeight2) {  
        this.boxDryWeight2 = boxDryWeight2;  
    }  
  
    public Double getBoxWeight1() {  
        return boxWeight1;  
    }  
  
    public void setBoxWeight1(Double boxWeight1) {  
        this.boxWeight1 = boxWeight1;  
    }  
  
    public Double getBoxWeight2() {  
        return boxWeight2;  
    }  
  
    public void setBoxWeight2(Double boxWeight2) {  
        this.boxWeight2 = boxWeight2;  
    }  
  
    public Double getWaterRate() {  
        return waterRate;  
    }  
  
    public void setWaterRate(Double waterRate) {  
        this.waterRate = waterRate;  
    }  
  
    public Double getDryDensity() {  
        return dryDensity;  
    }  
  
    public void setDryDensity(Double dryDensity) {  
        this.dryDensity = dryDensity;  
    }  
}

package com.nsk666;  
  
import java.util.Date;  
import java.util.List;  
  
public class DataEntity {  
    /**  
     * 标题  
     */  
    private String title;  
    /**  
     * 委托编号  
     */  
    private String orderNumber;  
  
    /**  
     * 检验依据  
     */  
    private String gist;  
  
    /**  
     * 仪器型号（编号）  
     */  
    private String instrumentNo;  
  
    /**  
     * 实验湿度  
     */  
    private String humidness;  
  
    /**  
     * 实验温度  
     */  
    private String temperature;  
    /**  
     * 仪器检定有效期至  
     */  
    private Date validDate;  
    /**  
     * 实验开始日期  
     */  
    private Date examDateFrom;  
    /**  
     * 实验结束日期  
     */  
    private Date examDateTo;  
  
    /**  
     * 试样编号  
     */  
    private String examNo;  
  
    /**  
     * 数据内容  
     */  
    List<DataContentEntity> contentList;  
  
    public String getTitle() {  
        return title;  
    }  
  
    public void setTitle(String title) {  
        this.title = title;  
    }  
  
    public String getOrderNumber() {  
        return orderNumber;  
    }  
  
    public void setOrderNumber(String orderNumber) {  
        this.orderNumber = orderNumber;  
    }  
  
    public String getGist() {  
        return gist;  
    }  
  
    public void setGist(String gist) {  
        this.gist = gist;  
    }  
  
    public String getInstrumentNo() {  
        return instrumentNo;  
    }  
  
    public void setInstrumentNo(String instrumentNo) {  
        this.instrumentNo = instrumentNo;  
    }  
  
    public String getHumidness() {  
        return humidness;  
    }  
  
    public void setHumidness(String humidness) {  
        this.humidness = humidness;  
    }  
  
    public String getTemperature() {  
        return temperature;  
    }  
  
    public void setTemperature(String temperature) {  
        this.temperature = temperature;  
    }  
  
    public Date getValidDate() {  
        return validDate;  
    }  
  
    public void setValidDate(Date validDate) {  
        this.validDate = validDate;  
    }  
  
    public Date getExamDateFrom() {  
        return examDateFrom;  
    }  
  
    public void setExamDateFrom(Date examDateFrom) {  
        this.examDateFrom = examDateFrom;  
    }  
  
    public Date getExamDateTo() {  
        return examDateTo;  
    }  
  
    public void setExamDateTo(Date examDateTo) {  
        this.examDateTo = examDateTo;  
    }  
  
    public String getExamNo() {  
        return examNo;  
    }  
  
    public void setExamNo(String examNo) {  
        this.examNo = examNo;  
    }  
  
    public List<DataContentEntity> getContentList() {  
        return contentList;  
    }  
  
    public void setContentList(List<DataContentEntity> contentList) {  
        this.contentList = contentList;  
    }  
}