java编写数据处理excel的可视化jar包程序

编写数据处理excel的可视化jar包程序

采用excel的处理模版：

https://github.com/Crab2died/Excel4J

涉及到的几个功能点的需求，excle数据的读取（可以获取某一列数据的值），同时读取多个表格，可以对数据进行处理。

一、模版功能的Excel的读取功能：

public class DefaultConvertible implements WriteConvertible, ReadConvertible { @Override public Object execRead(String object) { throw new UnsupportedOperationException(); } @Override public Object execWrite(Object object) { throw new UnsupportedOperationException(); }}

测试用例：

public class Excel2Module { @Test public void excel2Object() throws Exception { String path = "students_01.xlsx"; System.out.println("读取全部："); List<Student1> students = ExcelUtils.getInstance().readExcel2Objects(path, Student1.class); for (Student1 stu : students) { System.out.println(stu); } System.out.println("读取指定行数："); students = ExcelUtils.getInstance().readExcel2Objects(path, Student1.class, 0, 3, 1); for (Student1 stu : students) { System.out.println(stu); } } @Test public void excel2Object2() { String path = "students_02.xlsx"; try { // 1) // 不基于注解,将Excel内容读至List<List<String>>对象内 List<List<String>> lists = ExcelUtils.getInstance().readExcel2List(path, 1, 2, 0); System.out.println("读取Excel至String数组："); for (List<String> list : lists) { System.out.println(list.get(1)); } // 2) // 基于注解,将Excel内容读至List<Student2>对象内 // 验证读取转换函数Student2ExpelConverter // 注解 `@ExcelField(title = "是否开除", order = 5, readConverter = Student2ExpelConverter.class)` List<Student2> students = ExcelUtils.getInstance().readExcel2Objects(path, Student2.class, 0, 0); System.out.println("读取Excel至对象数组(支持类型转换)："); for (Student2 st : students) { System.out.println(st); } } catch (Exception e) { e.printStackTrace(); } } // 基于注解,将Excel内容读至List<Student2>对象内 // 验证读取转换函数Student2ExpelConverter，注解 `@ExcelField(title = "是否开除", order = 5, readConverter = Student2ExpelConverter.class)` @Test public void testReadConverter() throws Exception { String path = "students_02.xlsx"; List<Student2> students = ExcelUtils.getInstance().readExcel2Objects(path, Student2.class, 0, 0); System.out.println("读取Excel至对象数组(支持类型转换)："); for (Student2 st : students) { System.out.println(st); } } // 测试读取带有公式的单元格，并返回公式的值 @Test public void testReadExcel_XLS() throws Exception { String path = "StudentScore1.xlsx"; System.out.println(Paths.get(path).toUri().getPath()); List<StudentScore> projectExcelModels = ExcelUtils.getInstance().readExcel2Objects(path, StudentScore.class); for(StudentScore pr:projectExcelModels){ System.out.println(pr.getTotal()); } } // 测试读取CSV文件 @Test public void testReadCSV() throws Excel4JException { List<Student2> list = ExcelUtils.getInstance().readCSV2Objects("J.csv", Student2.class); for (Student2 student2 : list) { System.out.println(student2); } }}

第一个测试用例：

测试指定的行数的数据处理，这个是已知道很明确的读取的行数，可以使用，但是对于大量的数据不适合这个方法。

第二个、第三个测试用例类似：

对于数组的方式读取，方便后期的数据处理，可以选取的数组里面不同位置对应的地方。

第四个测试用例：

将数据转换成csv这个看情况需求。

二、模版功能的Excel的写入功能：

主要是基于模版或者是不基于模版处理

public class Module2Excel { @Test public void testObject2Excel() throws Exception { String tempPath = "normal_template.xlsx"; String path = "StudentScore.xlsx"; String path1 = "StudentScore2.xlsx"; List<Student1> list = new ArrayList<>(); List<StudentScore> projectExcelModels = ExcelUtils.getInstance().readExcel2Objects(path, StudentScore.class); List<StudentScore1> projectExcelModels1 = ExcelUtils.getInstance().readExcel2Objects(path1, StudentScore1.class); list.add(new Student1("1010001", "盖伦", "六年级三班")); list.add(new Student1("1010002", "古尔丹", "一年级三班")); list.add(new Student1("1010003", "蒙多(被开除了)", "六年级一班")); list.add(new Student1("1010004", "萝卜特", "三年级二班")); list.add(new Student1("1010005", "奥拉基", "三年级二班")); list.add(new Student1("1010006", "得嘞", "四年级二班")); list.add(new Student1("1010007", "瓜娃子", "五年级一班")); list.add(new Student1("1010008", "战三", "二年级一班")); list.add(new Student1("1010009", "李四", "一年级一班")); Map<String, String> data = new HashMap<>(); data.put("title", "风险值计算"); data.put("info", "整合汇总情况"); // 基于模板导出Excel FileOutputStream os = new FileOutputStream(new File("/Users/fuwei/Documents/idea_program/Excel4J/src/test/resources/A.xlsx")); ExcelUtils.getInstance().exportObjects2Excel(tempPath, list, data, Student1.class, false, os); os.close(); // 不基于模板导出Excel ExcelUtils.getInstance().exportObjects2Excel(list, Student1.class, true, null, true, "B.xlsx"); ExcelUtils.getInstance().exportObjects2CSV(list, Student1.class, "JJ.csv"); } // 基于模板、注解的多sheet导出 @Test public void testObject2BatchSheet() throws Exception { List<NormalSheetWrapper> sheets = new ArrayList<>(); for (int i = 0; i < 2; i++) { List<Student1> list = new ArrayList<>(); list.add(new Student1("1010001", "盖伦", "六年级三班")); list.add(new Student1("1010002", "古尔丹", "一年级三班")); list.add(new Student1("1010003", "蒙多(被开除了)", "六年级一班")); list.add(new Student1("1010004", "萝卜特", "三年级二班")); list.add(new Student1("1010005", "奥拉基", "三年级二班")); list.add(new Student1("1010006", "得嘞", "四年级二班")); list.add(new Student1("1010007", "瓜娃子", "五年级一班")); list.add(new Student1("1010008", "战三", "二年级一班")); list.add(new Student1("1010009", "李四", "一年级一班")); Map<String, String> data = new HashMap<>(); data.put("title", "战争学院花名册"); data.put("info", "学校统一花名册"); sheets.add(new NormalSheetWrapper(i, list, data, Student1.class, false)); } String tempPath = "D:\\JProject\\Excel4J\\src\\test\\resources\\normal_batch_sheet_template.xlsx"; FileOutputStream os = new FileOutputStream(new File("JK.xlsx")); // 基于模板导出Excel ExcelUtils.getInstance().normalSheet2Excel(sheets, tempPath, "AA.xlsx"); ExcelUtils.getInstance().normalSheet2Excel(sheets, tempPath, os); os.close(); } @Test public void testMap2Excel() throws Exception { Map<String, List<?>> classes = new HashMap<>(); Map<String, String> data = new HashMap<>(); data.put("title", "战争学院花名册"); data.put("info", "学校统一花名册"); classes.put("class_one", Arrays.asList( new Student1("1010009", "李四", "一年级一班"), new Student1("1010002", "古尔丹", "一年级三班") )); classes.put("class_two", Collections.singletonList( new Student1("1010008", "战三", "二年级一班") )); classes.put("class_three", Arrays.asList( new Student1("1010004", "萝卜特", "三年级二班"), new Student1("1010005", "奥拉基", "三年级二班") )); classes.put("class_four", Collections.singletonList( new Student1("1010006", "得嘞", "四年级二班") )); classes.put("class_six", Arrays.asList( new Student1("1010001", "盖伦", "六年级三班"), new Student1("1010003", "蒙多", "六年级一班") )); ExcelUtils.getInstance().exportMap2Excel("/map_template.xlsx", 0, classes, data, Student1.class, false, "C.xlsx"); } // Map数据的多sheet导出 @Test public void testMap2BatchSheet() throws Exception { List<MapSheetWrapper> sheets = new ArrayList<>(); for (int i = 0; i < 2; i++) { Map<String, List<?>> classes = new HashMap<>(); Map<String, String> data = new HashMap<>(); data.put("title", "战争学院花名册"); data.put("info", "学校统一花名册"); classes.put("class_one", Arrays.asList( new Student1("1010009", "李四", "一年级一班"), new Student1("1010002", "古尔丹", "一年级三班") )); classes.put("class_two", Collections.singletonList( new Student1("1010008", "战三", "二年级一班") )); classes.put("class_three", Arrays.asList( new Student1("1010004", "萝卜特", "三年级二班"), new Student1("1010005", "奥拉基", "三年级二班") )); classes.put("class_four", Collections.singletonList( new Student1("1010006", "得嘞", "四年级二班") )); classes.put("class_six", Arrays.asList( new Student1("1010001", "盖伦", "六年级三班"), new Student1("1010003", "蒙多", "六年级一班") )); sheets.add(new MapSheetWrapper(i, classes, data, Student1.class, false)); } ExcelUtils.getInstance().mapSheet2Excel(sheets, "/map_batch_sheet_template.xlsx", "CC.xlsx"); } @Test public void testList2Excel() throws Exception { List<List<String>> list2 = new ArrayList<>(); List<String> header = new ArrayList<>(); for (int i = 0; i < 10; i++) { List<String> _list = new ArrayList<>(); for (int j = 0; j < 10; j++) { _list.add(i + " -- " + j); } list2.add(_list); header.add(i + "---栏"); } ExcelUtils.getInstance().exportObjects2Excel(list2, header, "D.xlsx"); } @Test public void uuid() throws IOException { List<String> list = new ArrayList<>(); for (int i = 0; i < 10000; i++) { list.add(UUID.randomUUID().toString()); } ExcelUtils.getInstance().exportObjects2Excel(list, new ArrayList<String>() {{ add("uuid"); }}, "J.xlsx"); } // 验证日期转换函数 Student2DateConverter // 注解 `@ExcelField(title = "入学日期", order = 3, writeConverter = Student2DateConverter.class)` @Test public void testWriteConverter() throws Exception { List<Student2> list = new ArrayList<>(); for (int i = 0; i < 1000; i++) { list.add(new Student2(10000L + i, "学生" + i, new Date(), 201, false)); } ExcelUtils.getInstance().exportObjects2Excel(list, Student2.class, true, "sheet0", true, "E.xlsx"); } // 多sheet无模板、基于注解的导出 @Test public void testBatchNoTemplate2Excel() throws Exception { List<NoTemplateSheetWrapper> sheets = new ArrayList<>(); for (int s = 0; s < 3; s++) { List<Student2> list = new ArrayList<>(); for (int i = 0; i < 1000; i++) { list.add(new Student2(10000L + i, "学生" + i, new Date(), 201, false)); } sheets.add(new NoTemplateSheetWrapper(list, Student2.class, true, "sheet_" + s)); } ExcelUtils.getInstance().noTemplateSheet2Excel(sheets, "EE.xlsx"); } // 多sheet无模板、无注解导出 @Test public void testBatchSimple2Excel() throws Exception { // 生成sheet数据 List<SimpleSheetWrapper> list = new ArrayList<>(); for (int i = 0; i <= 2; i++) { //表格内容数据 List<String[]> data = new ArrayList<>(); for (int j = 0; j < 1000; j++) { // 行数据(此处是数组) 也可以是List数据 String[] rows = new String[5]; for (int r = 0; r < 5; r++) { rows[r] = "sheet_" + i + "row_" + j + "column_" + r; } data.add(rows); } // 表头数据 List<String> header = new ArrayList<>(); for (int h = 0; h < 5; h++) { header.add("column_" + h); } list.add(new SimpleSheetWrapper(data, header, "sheet_" + i)); } ExcelUtils.getInstance().simpleSheet2Excel(list, "K.xlsx"); } // 导出csv @Test public void testExport2CSV() throws Excel4JException { List<Student2> list = new ArrayList<>(); list.add(new Student2(1000001L, "张三", new Date(), 1, true)); list.add(new Student2(1010002L, "古尔丹", new Date(), 2, false)); list.add(new Student2(1010003L, "蒙多(被开除了)", new Date(), 6, true)); list.add(new Student2(1010004L, "萝卜特", new Date(), 3, false)); list.add(new Student2(1010005L, "奥拉基", new Date(), 4, false)); list.add(new Student2(1010006L, "得嘞", new Date(), 4, false)); list.add(new Student2(1010007L, "瓜娃子", new Date(), 5, true)); list.add(new Student2(1010008L, "战三", new Date(), 4, false)); list.add(new Student2(1010009L, "李四", new Date(), 2, false)); ExcelUtils.getInstance().exportObjects2CSV(list, Student2.class, "J.csv"); } // 超大数据量导出csv // 9999999数据本地测试小于1min @Test public void testExport2CSV2() throws Excel4JException { List<Student2> list = new ArrayList<>(); for (int i = 0; i < 9999999; i++) { list.add(new Student2(1000001L + i, "路人 -" + i, new Date(), i % 6, true)); } ExcelUtils.getInstance().exportObjects2CSV(list, Student2.class, "L.csv");}

对于模版数据写入：

模版的数据处理导出，基于公式计算的Excel处理导出（这里不做过多的说明）

csv数据

基本导出处理数据，基本类似的情况，注意在选取的时候，数据需要怎么处理来选择不同的处理方法。

三、根据自己的需求编写业务处理逻辑：

读取excel里面的数据：

public class read { public String testObject2Excel(String path12,String path113) throws Exception { String tempPath = "normal_template.xlsx"; String path = path12; String path1 = path113; List<Student1> list = new ArrayList<>(); List<StudentScore> projectExcelModels = ExcelUtils.getInstance().readExcel2Objects(path, StudentScore.class); List<StudentScore1> projectExcelModels1 = ExcelUtils.getInstance().readExcel2Objects(path1, StudentScore1.class); for (StudentScore pr : projectExcelModels) { for (StudentScore1 pr1 : projectExcelModels1) { Double sum; sum = (pr.getChinese() + pr1.getChinese()) / 3; { System.out.println(pr.getNum() + "------ok--------" + pr1.getNum()); System.out.println(list.add(new Student1(pr.getChinese().toString(), pr1.getChinese().toString(), sum.toString()))); } else { System.out.println(pr.getNum() + "-------error-------" + pr1.getNum()); System.out.println("不是同一漏洞匹配数据类型的值"); } } } Map<String, String> data = new HashMap<>(); data.put("title", "风险值计算"); data.put("info", "整合汇总情况"); // 基于模板导出Excel FileOutputStream os = new FileOutputStream(new File("A.xlsx")); ExcelUtils.getInstance().exportObjects2Excel(tempPath, list, data, Student1.class, false, os); os.close(); // 不基于模板导出Excel ExcelUtils.getInstance().exportObjects2Excel(list, Student1.class, true, null, true, "B.xlsx"); ExcelUtils.getInstance().exportObjects2CSV(list, Student1.class, "JJ.csv"); } }

改写方法需要不同的传参数，重新定制不同的方法：

点击运行事件的监听操作：

//button2 数据处理class MyActionListener2 implements ActionListener{ public void actionPerformed(ActionEvent arg0) { read re = new read(); jtf.getText(); jtf1.getText(); try { try { List<Student1> list = (List<Student1>) re.testObject2Excel(jtf.getText(),jtf1.getText()); }catch (Exception e){ e.printStackTrace(); } }catch (Exception e){ e.printStackTrace(); } }}

基本业务核心的代码的数据处理还有类的相关操作省略

接下来就是java swing的GUI页面的绘制：

private JLabel label=new JLabel("所选文件1路径：");private JTextField jtf=new JTextField(20);private JButton button=new JButton("浏览");private JLabel label1=new JLabel("所选文件2路径：");private JTextField jtf1=new JTextField(20);private JButton button1=new JButton("浏览");private JButton button2=new JButton("计算脆弱性值");private JTextArea jta=new JTextArea("",15,30);public JFileChooserDemo(){ JFrame jf=new JFrame("数据转换器"); jf.setSize(450,400); JScrollPane jsp=new JScrollPane(jta);//添加滚动条 jf.setResizable(false); //限制画布大小 JPanel panel=new JPanel(); panel.add(label); panel.add(jtf); panel.add(button); panel.add(label1); panel.add(jtf1); panel.add(button1); panel.add(button2); panel.add(jsp); jf.add(panel); //jf.setBounds(100,100,300,300); //jf.pack(); //自动调整大小 jf.setVisible(true); button.addActionListener(new MyActionListener());//监听按钮事件 button1.addActionListener(new MyActionListener1()); //监听按钮事件 button2.addActionListener(new MyActionListener2());//监听按钮事件数据处理 jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);}

页面绘制的核心相关代码

public void actionPerformed(ActionEvent arg0) { read re = new read(); jtf.getText(); jtf1.getText(); try { try { List<Student1> list = (List<Student1>) re.testObject2Excel(); }catch (Exception e){ e.printStackTrace(); } }catch (Exception e){ e.printStackTrace(); }

数据的处理核心代码

数据处理导出结果；

最后将数据打包成为jar包；

https://blog.csdn.net/qq_43189115/article/details/99856659

解决IDEA打Jar包找不到或无法加载主类问题

正确流程：

首先：点击主菜单 File 中 Project Structure 选项

然后，按照图片所示操作

关键点来了

主类要选择

路径选到src即可，不要选到java

正确操作

接下来可以打Jar包了

最后一步

检验一下，发现META-INF文件夹已经在Jar包中了