javaSwing excel处理的一个小工具

		平时处理excel,经常会遇到要处理多个excel合并处理的问题，例如要比对多个excel里面的同一个用户是否出现。
		今天就做了一个使用JAVAswing 开发的小工具
		需求： 能够直接在windows桌面，双击打开的exe 工具。
		使用软件： java开发工具，exe4j 打包工具

先看成品：1.可以选择多个excel文件，
2. 读取第一行标题
3. 点击选择的列
4. 最后点击处理文件，等待即可得到 处理后的excel.

代码结构：

App.java

package cat;


import cat.jframe.MainJframe;

public class App {

	public static void main(String[] args) {
		new MainJframe();
	}
}

BusinessException.java

package cat.excel;

/**
 * 业务异常
 *
 * @author luoyang
 * @date 2019/11/23 18:12:02
 */
public class BusinessException extends RuntimeException {

    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String code;

    public BusinessException() {
        super();
    }

    public BusinessException(String message) {
        super(message);
    }

    public BusinessException(String code, String message) {
        super(message);
        this.code = code;
    }

    public String getCode() {
        return code;
    }
}

ExcelUtil2.java

package cat.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.excel.EasyExcelFactory;

public class ExcelUtil2 {

	public static List<Object> readSheetRow(File file, int sheet, int rownum) {
		InputStream fileStream = null;
		try {
			fileStream = new FileInputStream(file);
			return EasyExcelFactory.read(fileStream).sheet(sheet).headRowNumber(rownum).doReadSync();
		} catch (FileNotFoundException e) {
			
		} finally {
			try {
				if (fileStream != null) {
					fileStream.close();
				}
			} catch (IOException e) {
			}
		}
		return null;
	}

	public static void writeExcel(String[] heads,String path,List<List<String>> dataList) {
		List<List<String>> h =  new ArrayList<List<String>>();
		for (String head : heads) {
			List<String> head0 = new ArrayList<String>();
	        head0.add(head);
	        h.add(head0);
		}
		EasyExcelFactory.write(new File(path)).head(h).sheet().doWrite(dataList);
    }
}

HandExcelEo.java

package cat.excel;

public class HandExcelEo {

	public HandExcelEo(String text) {
		this.nums = 1;
		this.text = text;
	}
	
	private int nums;
	
	private String text;

	public int getNums() {
		return nums;
	}

	public void setNums(int nums) {
		this.nums = nums;
	}

	public String getText() {
		return text;
	}

	public void setText(String text) {
		this.text = text;
	}
	
	public void add(String text) {
		this.nums++;
		this.text += ","+text;
	}
	

}

ExcelHandActionListener.java

package cat.jframe;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.swing.JOptionPane;

import cat.excel.BusinessException;
import cat.excel.ExcelUtil2;
import cat.excel.HandExcelEo;

public class ExcelHandActionListener  implements ActionListener{

	private  int pro = 0;
	
	private Map<String, ExcelItemJpanel> excelPanelMap;
	
	private MainJframe jframe;
	
	public ExcelHandActionListener(MainJframe jframe,Map<String, ExcelItemJpanel> excelPanelMap) {
		this.excelPanelMap = excelPanelMap;
		this.jframe = jframe;
	}
	
	public void actionPerformed(ActionEvent e) {
		try {
			handExcel();
		} catch (BusinessException e1) {
			JOptionPane.showMessageDialog(jframe, e1.getMessage());
		}
		
	}
	
	public  void handExcel() {
		//校验
		checkExcel(excelPanelMap);
		jframe.updateHanding(5);
		int excelIndex = 1;
		Map<String,HandExcelEo>  handData = new HashMap<String, HandExcelEo>(); 
		String path = "";
		for (String key : excelPanelMap.keySet()) {
			ExcelItemJpanel rootPanel=excelPanelMap.get(key);
			int  choose = rootPanel.getChoose();
			if(path.length() == 0) {
				path = rootPanel.getFilePath();
				path = path.split("\\.")[0]+"_"+"处理结果.xls";
			}
			//取出来 所有的数据
			Set<String> set = new HashSet<String>(); 
			int j= 0;
			for (Object o: rootPanel.getDataList()) {
				j++;
				if(j == 1) {
					continue;
				}
				//
				String chooseRowVlaue = ((HashMap<Integer, String>)o).get(choose);
				if(chooseRowVlaue == null || chooseRowVlaue.trim().length() == 0) {
					continue;
				}
				boolean putFlag =set.add(chooseRowVlaue);
				if(!putFlag) {
					System.out.println("有重复");
					int chooseShowNum = choose+1;
					throw new BusinessException("第"+excelIndex+"个 excel,第"+chooseShowNum+"列，"+chooseRowVlaue+":重复.");
				}
			}
			//放入总数据里面
			for (String k : set) {
				HandExcelEo eo = handData.get(k);
				if(null == eo) {
					handData.put(k, new HandExcelEo(excelIndex+""));
				}else {
					eo.add(excelIndex+"");
				}
			}
			excelIndex++;
			jframe.updateHanding(pro + (70/excelIndex));
		}
		
		jframe.updateHanding(80);
		
		List<List<String>> dataList = new ArrayList<List<String>>();
		for (String k : handData.keySet()) {
			List<String> data = new ArrayList<String>();
			data.add(k);
			data.add(handData.get(k).getNums()+"");
			data.add(handData.get(k).getText());
			dataList.add(data);
//			System.out.println("计算后的结果 ，目标值："+k+",次数："+handData.get(k).getNums()+",备注说明："+handData.get(k).getText());
		}
		jframe.updateHanding(90);
		String[] heads = new String[]{"选择的指","出现次数","出现excel位置"};
		ExcelUtil2.writeExcel(heads, path, dataList);
		jframe.updateHanding(100);
		jframe.updateResult(path);
	}
	
	
	public  void checkExcel(Map<String, ExcelItemJpanel> excelPanelMap) {
		if(null == excelPanelMap || excelPanelMap.isEmpty()) {
			throw new BusinessException("您还没有添加excel,请先添加excel");
		}
		
		for (String key : excelPanelMap.keySet()) {
			ExcelItemJpanel rootPanel=excelPanelMap.get(key);
			if(rootPanel.getChoose() == null) {
				throw new BusinessException("第"+key+"个execl,还未选择列。");
			}
		}
		JOptionPane.showConfirmDialog(jframe, "确定合并处理excel?", "友情提示", JOptionPane.YES_NO_OPTION); 
	}

}

ExcelItemJpanel.java

package cat.jframe;

import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.ScrollPane;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JPanel;
import javax.swing.border.TitledBorder;

import cat.excel.ExcelUtil2;

public class ExcelItemJpanel extends JPanel {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private int index;

	private String filePath;
	
	private File file;

	private int hight;
	
	private List<JButton>  buttonList = new ArrayList<JButton>();
	
	private List<Object>  dataList = new ArrayList<Object>();
	
	
	private Integer choose = null;
	
	public Integer getChoose() {
		return this.choose;
	}
	
	public List<Object> getDataList() {
		return this.dataList;
	}
	
	public String getFilePath() {
		return this.filePath;
	}
	
	public ExcelItemJpanel(int index, File file, String filePath,int width,int hight) {
		this.index = index;
		this.filePath = filePath;
		this.hight = hight;
		this.file = file;
		initJpanel();
		
		initButton();
	}

	public void initJpanel() {
		TitledBorder t = BorderFactory.createTitledBorder(index + "");
		t.setTitleJustification(TitledBorder.CENTER);
		this.setBorder(t);
		this.setLayout(new FlowLayout());
	}

	public void initButton() {
		dataList = ExcelUtil2.readSheetRow(file, 0, 0);
		HashMap<Integer, String> exceltitle = (HashMap<Integer, String>) dataList.get(0);
		
		// 2.添加excel
		ScrollPane sp = new ScrollPane();
		sp.setSize(1180, hight);
		JPanel excelbuttonPanel = new JPanel();
		excelbuttonPanel.setLayout(new FlowLayout(FlowLayout.LEFT));
		for (Integer key : exceltitle.keySet()) {
			JButton excelButton = createExcelButton(key, exceltitle.get(key));
			buttonList.add(excelButton);
			excelbuttonPanel.add(excelButton);
		}
		sp.add(excelbuttonPanel);
		this.add(sp);
	}
	
	public JButton createExcelButton(final int i,String value) {
		final ExcelButton b = new ExcelButton(i,value);
		b.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				System.out.println(getForeground());
				for (JButton jButton : buttonList) {
					jButton.setBackground(null);
				}
				b.setBackground(new Color(0,191,255));
				//设置选择的列
				updateTitleBorder(b);
			}
		});
		return b;
	}
	
	public void updateTitleBorder(ExcelButton b) {
		TitledBorder t = BorderFactory.createTitledBorder(index + "     "+b.getText());
		t.setTitleColor(new Color(30,144,255));
		t.setTitleJustification(TitledBorder.CENTER);
		this.setBorder(t);
		this.choose = b.getIndex();
	}
}

class ExcelButton extends JButton{
	
	private int index;
	
	public ExcelButton(Integer index,String text) {
		this.index = index;
		init(text, null);
	}

	public int getIndex() {
		return index;
	}

	
	
}

MainJframe.java

package cat.jframe;

import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.Rectangle;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.util.HashMap;
import java.util.Map;

import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JProgressBar;
import javax.swing.JTextArea;
import javax.swing.SwingUtilities;
import javax.swing.border.TitledBorder;

public class MainJframe extends JFrame{

	private static final long serialVersionUID = 1L;
	
	private JPanel headPanel = new JPanel();
	
	private JPanel bodyPanel = new JPanel();
	
	private JPanel handExcelPanle = new JPanel();
	
	private JProgressBar progressBar = new JProgressBar(0,100);
	
	private JTextArea result = new JTextArea();
	
	public JProgressBar getJProgressBar() {
		return this.progressBar;
	}
	
	private int i = 0;
	
	private Map<String, ExcelItemJpanel> excelPanelMap = new HashMap<String, ExcelItemJpanel>();
	/**
	 * 构造方法
	 */
	public MainJframe(){
		this.setTitle("Excel处理");
		this.setSize(1220,600);
		this.setLayout(null);
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		
		//头部
		this.add(headPanelInit());

		this.add(headHandInit());
		
		//body
		this.add(bodyPanelInit());
		
		
		this.setVisible(true);
		
	}
	
	//内容
	private JPanel bodyPanelInit() {
		bodyPanel.setBounds(0, 110, 1200, 450);
		TitledBorder t = BorderFactory.createTitledBorder("excel列表，点击按钮，选择要处理的列");
		t.setTitleJustification(TitledBorder.LEFT);
		bodyPanel.setBorder(t);
		bodyPanel.setLayout(new FlowLayout());
		return bodyPanel;
	}
	
	/**
	 * 创建excel
	 * @param i
	 * @param file
	 * @param path
	 * @return
	 */
	private void createExcelScrollPanel(int i,File file,String path) {
		ExcelItemJpanel itemJpanle = new ExcelItemJpanel(i, file, path, 1180, 60);
		this.bodyPanel.add(itemJpanle);
		//重新绘制页面
		SwingUtilities.updateComponentTreeUI(this);
		excelPanelMap.put(""+i, itemJpanle);
	}
	
	/**
	 * 头部
	 */
	private  JPanel headPanelInit() {
		headPanel.setBounds(0, 0, 400, 100);
		TitledBorder t = BorderFactory.createTitledBorder("Excel选择");
		t.setTitleJustification(TitledBorder.LEFT);
		headPanel.setBorder(t);
		headPanel.setLayout(null);
		
        JLabel passwordLabel = new JLabel("选择EXCEL文件导入，可多次选择");
        passwordLabel.setBounds(20,20,350,25);
        headPanel.add(passwordLabel);
        
        JButton b = new JButton("点击选择文件");
		b.setBounds(20, 60, 180, 25);
		b.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				final JFrame fil = new JFrame("选择文件导入");
				fil.setLayout(null);
				fil.setSize(650,450);
				fil.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
				final JFileChooser filec = new JFileChooser();
		        filec.setBounds(0,0,630,390);
		        filec.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent e) {
						if(e.getActionCommand().equalsIgnoreCase("ApproveSelection")) {
							File file =  filec.getSelectedFile();
							i++;
							createExcelScrollPanel(i, file, file.getAbsolutePath());
						}
						//关闭
						fil.dispose();
					}
				});
		        fil.add(filec);
		        fil.setVisible(true);
			}
		});
		headPanel.add(b);
		
		JButton clearButton = new JButton("清空数据");
		clearButton.setBounds(220, 60, 100, 25);
		clearButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				clearBody();
			}
		});
		headPanel.add(clearButton);
		return headPanel;
	}
	
	
	/**
	 * 头部
	 */
	private  JPanel headHandInit() {
		handExcelPanle.setBounds(410, 0, 780, 100);
		TitledBorder t = BorderFactory.createTitledBorder("excel处理");
		t.setTitleJustification(TitledBorder.LEFT);
		handExcelPanle.setBorder(t);
		handExcelPanle.setLayout(null);
		
        JLabel handLabel = new JLabel("点击处理excel");
        handLabel.setBounds(20,20,380,25);
        handExcelPanle.add(handLabel);
        
        JButton b = new JButton("点击处理文件");
		b.setBounds(20, 60, 180, 25);
		//添加excelc处理函数
		b.addActionListener(new ExcelHandActionListener(this,excelPanelMap));
		handExcelPanle.add(b);
		
		//添加处理中
		JLabel handing = new JLabel();
		handing.setText("处理进度：");
		handing.setBounds(270,20,70,25);
		handExcelPanle.add(handing);
		
		progressBar.setBounds(380,20,250,25);
        progressBar.setValue(0);
        progressBar.setStringPainted(true);
		handExcelPanle.add(progressBar);
		
		
		//添加处理中
		JLabel resultLable = new JLabel();
		resultLable.setText("结果文件地址：");
		resultLable.setBounds(270,50,100,25);
		handExcelPanle.add(resultLable);
		
		result.setBounds(380,50,250,40);
		result.setAutoscrolls(true);
		handExcelPanle.add(result);
				
		return handExcelPanle;
	}
	
	
	public  void updateHanding(int p) {
		//判断可以改变进度条
		Dimension d = progressBar.getSize();
		Rectangle rect = new Rectangle(0, 0, d.width, d.height);
		progressBar.setValue(p);
		progressBar.paintImmediately(rect);
	}
	
	
	public  void updateResult(String path) {
		result.setText(path);
		result.updateUI();
	}
	
	
	private  void clearBody() {
		i= 0;
		excelPanelMap.clear();
		updateHanding(0);
		updateResult("");
		bodyPanel.removeAll();
		bodyPanel.updateUI();
		
//		SwingUtilities.updateComponentTreeUI(this);
	}
	
}

Pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.cat</groupId>
	<artifactId>excel-swing</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-shade-plugin</artifactId>
				<version>1.2.1</version>
				<executions>
					<execution>
						<phase>package</phase>
						<goals>
							<goal>shade</goal>
						</goals>
						<configuration>
							<transformers>
								<transformer
									implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
									<mainClass>cat.App</mainClass>
								</transformer>
							</transformers>
						</configuration>
					</execution>
				</executions>
			</plugin>

		</plugins>
	</build>

</project>