零件库房管理项目(Poi+JDBC)

编写环境

编译软件：IDEA

数据库可视化软件：DataGrip

操作系统：Linux

JDK版本：8

用到的jar包(下载地址)：Poi的jar包，JDBC的jar包

一个软件工程的作业，要求写一个项目，花了半个月时间由我一个人完成！

作业要求：

①利用Poi技术能够生成两个Excel表然后汇总成一个表，汇总表必须按照编号进行排序，能够对表进行增删改查操作

②将三个表中的数据通过JDBC连接本地数据库导入到对应的表中，并且能对三个表进行增删改查等操作，同时能够在本地生成操作后的xxx表.xlsx

我写的是一个零件库房管理项目，想要生成零件汇总表.xlsx，首先要生成零件表.xlsx和零件进库表.xlsx，零件表和零件进库表有两种生成方式，具体实现和功能如下图

系统功能实现流程图

主界面

生成零件汇总表

想要生成零件汇总表.xlsx，首先要生成零件表.xlsx和零件进库表.xlsx，零件表和零件进库表有两种生成方式

①手动输入零件信息进行生成

②从外界已经准备好的零件或零件进库表进行导入

Poi操作Excel表中的零件信息

JDBC操作Excel表中的零件信息

数据库准备工作

首先先建立一个compoents数据库，完事之后

建立一个零件表.xlsx对应的part表

建立一个零件进库表.xlsx对应的partentry表

建立一个零件汇总表.xlsx对应的partsum表

零件库房项目功能实现结果截图

数据库中的零件汇总表partsum

生成的三个Excel表

项目源代码(结尾有下载地址)

实体类(包括三个表的零件信息，表的文件路径)

package rjgc.Entity;
/**
 * 零件表的零件信息类
 */
public class ComponentsOne {
    /**
     * 零件表中的零件编号
     *
     */
    public  String[] number;
    /**
     * 零件表中的零件名称
     */
    public  String[] name;
    /**
     * 零件表中的零件规格
     */
    public  String[] size;
    /**
     * 零件表中的零件信息行数
     */
    public int numOne;

    public String[] getNumber() {
        return number;
    }

    public void setNumber(String[] number) {
        this.number = number;
    }

    public String[] getName() {
        return name;
    }

    public void setName(String[] name) {
        this.name = name;
    }

    public String[] getSize() {
        return size;
    }

    public void setSize(String[] size) {
        this.size = size;
    }

    public int getNumOne() {
        return numOne;
    }

    public void setNumOne(int numOne) {
        this.numOne = numOne;
    }
}

package rjgc.Entity;

/**
 * 零件进库表的零件信息类
 */
public class ComponentsTwo {
    /**
     * 零件进库表中的零件编号
     */
    public  String[] number;
    /**
     * 零件进库表中的零件数量
     */
    public  int[] amount;
    /**
     * 零件进库表中的零件信息行数
     */
    public  int numTwo;

    public String[] getNumber() {
        return number;
    }

    public void setNumber(String[] number) {
        this.number = number;
    }

    public int[] getAmount() {
        return amount;
    }

    public void setAmount(int[] amount) {
        this.amount = amount;
    }

    public int getNumTwo() {
        return numTwo;
    }

    public void setNumTwo(int numTwo) {
        this.numTwo = numTwo;
    }

}

package rjgc.Entity;
/**
 * 零件汇总表的零件信息类
 */
public class ComponentsSum {
    /**
     * 零件汇总表中的零件编号
     */
    public  String[] number;
    /**
     * 零件进库表中的零件名称
     */
    public  String[] name;
    /**
     * 零件进库表中的零件规格
     */
    public  String[] size;
    /**
     * 零件进库表中的零件数量
     */
    public  int[] amount;

    public String[] getNumber() {
        return number;
    }

    public void setNumber(String[] number) {
        this.number = number;
    }

    public String[] getName() {
        return name;
    }

    public void setName(String[] name) {
        this.name = name;
    }

    public String[] getSize() {
        return size;
    }

    public void setSize(String[] size) {
        this.size = size;
    }

    public int[] getAmount() {
        return amount;
    }

    public void setAmount(int[] amount) {
        this.amount = amount;
    }
}

package rjgc.Entity;

public class Pathname {
    /**
     * 零件表文件路径
     */
    private static String pathnameOne;
    /**
     * 零件进库表文件路径
     */
    private static String pathnameTwo;
    /**
     * 零件汇总表文件路径
     */
    private static String pathnameSum;

    public  String getPathnameSum() {
        return pathnameSum;
    }

    public  void setPathnameSum(String pathnameSum) {
        Pathname.pathnameSum = pathnameSum;
    }

    public String getPathnameOne() {
        return pathnameOne;
    }

    public  void setPathnameOne(String pathnameOne) {
        Pathname.pathnameOne = pathnameOne;
    }

    public  String getPathnameTwo() {
        return pathnameTwo;
    }

    public  void setPathnameTwo(String pathnameTwo) {
        Pathname.pathnameTwo = pathnameTwo;
    }
}

三个表的生成类

零件表的生成类

package Generate;
/*
    生成默认的零件表.xlsx
    包含零件编号,名称,规格
 */
import Entity.ComponentsOne;
import Entity.Pathname;
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 java.io.*;
import java.util.Scanner;

/**
 * 零件表的生成类
 */
public class XlsxOne {

    public static void creat(ComponentsOne one, Pathname pn){
        Scanner sc=new Scanner(System.in);
        int mark=0;
        while (mark!=1){
            System.out.println("输入你想生成零件表的方式代号:");
            System.out.println("1.手动输入零件表信息 2.从外界导入零件表信息");
            int choose=sc.nextInt();
            if(choose==1){
                creatXlsx1(one,pn);
                mark=1;
            }else if(choose==2){
                creatXlsx2(one,pn);
                mark=1;
            }else{
                System.out.println("输入生成代号错误,请重新输入!");
            }
        }
    }
    //生成手动输入零件信息的零件表.xlsx
    public static void creatXlsx1(ComponentsOne one,Pathname pn) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("零件表");
        FileOutputStream fos = null;
        FileInputStream fis = null;
        Workbook sheets = null;
        try {
            Scanner sn = new Scanner(System.in);
            System.out.println("输入零件表.xlsx文件生成的位置:(需要带上文件名称)");
            String pathname = sn.nextLine();
            pn.setPathnameOne(pathname);
            fos = new FileOutputStream(pn.getPathnameOne());
            System.out.println("输入零件表的行数:");
            one.setNumOne(sn.nextInt());
            XSSFRow row = null;
            XSSFCell cell = null;
            Scanner sc = new Scanner(System.in);
            one.number = new String[one.getNumOne() + 1];
            one.name = new String[one.getNumOne() + 1];
            one.size = new String[one.getNumOne() + 1];
            //用String类型数组存放输入的零件编号,名字,规格
            for (int i = 0; i < one.getNumOne() + 1; i++) {
                if (i == 0) {
                    one.number[0] = "零件编号";
                    one.name[0] = "零件名称";
                    one.size[0] = "零件规格";
                } else {
                    System.out.println("请输入" + i + "行零件编号:");
                    one.number[i] = sc.nextLine();
                    System.out.println("请输入" + i + "行零件名称:");
                    one.name[i] = sc.nextLine();
                    System.out.println("请输入" + i + "行零件规格:");
                    one.size[i] = sc.nextLine();
                }
            }
            one.setNumber(one.number);
            one.setName(one.name);
            one.setSize(one.size);
            for (int i = 0; i < one.getNumOne() + 2; i++) {
                if (i == 0) {
                    row = sheet.createRow(i);
                    cell = row.createCell(i);
                    //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
                    row.createCell(0).setCellValue("零件表信息");
                    //设置水平居中
                    CellStyle style = workbook.createCellStyle();
                    style.setAlignment(HorizontalAlignment.CENTER);
                    cell.setCellStyle(style);
                    sheet.addMergedRegion(region);
                } else {
                    row = sheet.createRow(i);
                    cell = row.createCell(i);
                    row.createCell(0).setCellValue(one.getNumber()[i - 1]);
                    row.createCell(1).setCellValue(one.getName()[i - 1]);
                    row.createCell(2).setCellValue(one.getSize()[i - 1]);
                }
            }
            System.out.println("生成成功,已存入" + pathname + "目录中!");
            workbook.write(fos);
            workbook.close();
            fos.close();
            //打印上面输入的零件表信息
            fis = new FileInputStream(pn.getPathnameOne());
            sheets = new XSSFWorkbook(fis);
            Sheet sheet1 = sheets.getSheetAt(0);
            System.out.println("------零件表------");
            for (int i = 1; i < sheet1.getLastRowNum() + 1; i++) {
                //获取行数
                Row row1 = sheet1.getRow(i);
                //获取单元值并且取值
                System.out.println(row1.getCell(0).getStringCellValue()
                        + "   " + row1.getCell(1).getStringCellValue()
                        + "   " + row1.getCell(2).getStringCellValue());
            }
            System.out.println("-----------------");

        } catch (FileNotFoundException e) {
            System.out.println("文件路径错误");
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (sheets != null) {
                try {
                    sheets.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //从外面导入已经准备好的零件表.xlsx
     public  static void  creatXlsx2(ComponentsOne one,Pathname pn)  {
          FileInputStream fis = null;
          Workbook sheets = null;
              try {
                  System.out.println("输入你要导入的零件表文件路径:");
                  Scanner sn = new Scanner(System.in);
                  String pathname=sn.nextLine();
                  pn.setPathnameOne(pathname);
                  fis = new FileInputStream(pn.getPathnameOne());
                  sheets = new XSSFWorkbook(fis);
                  Sheet sheet1 = sheets.getSheetAt(0);
                  System.out.println("导入零件表.xlsx成功!");
                  System.out.println("------零件表-------");
                  one.number = new String[sheet1.getLastRowNum()]; //4
                  one.name = new String[sheet1.getLastRowNum()];
                  one.size = new String[sheet1.getLastRowNum()];
                  for (int i = 1; i < sheet1.getLastRowNum() + 1; i++) { //1 2 3 4
                      //获取行数
                      Row row1 = sheet1.getRow(i);
                      //获取单元值并且取值
                      System.out.println(row1.getCell(0).getStringCellValue()
                              + "   " + row1.getCell(1).getStringCellValue()
                              + "   " + row1.getCell(2).getStringCellValue());
                      one.number[i-1]=row1.getCell(0).getStringCellValue();
                      one.name[i-1]=row1.getCell(1).getStringCellValue();
                      one.size[i-1]=row1.getCell(2).getStringCellValue();
                      one.setNumber(one.number);
                      one.setName(one.name);
                      one.setSize(one.size);
                  }
                  System.out.println("-----------------");
              } catch (FileNotFoundException e) {
                  System.out.println("文件导入的路径错误");
                  e.printStackTrace();
              } catch (IOException e) {
                  e.printStackTrace();
              } finally {
                  if (sheets != null) {
                      try {
                          sheets.close();
                      } catch (IOException e) {
                          e.printStackTrace();
                      }
                  }
                  if (fis != null) {
                      try {
                          fis.close();
                      } catch (IOException e) {
                          e.printStackTrace();
                      }
                  }
              }
    }

    public  static void printXlsxOne(Pathname pn) throws IOException {
        FileInputStream fis = new FileInputStream(pn.getPathnameOne());
        Workbook sheets=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets.getSheetAt(0);
        for (int i = 1; i < sheet1.getLastRowNum() + 1; i++) {
                //获取行数
                Row row1 = sheet1.getRow(i);
                //获取单元值并且取值
                System.out.println(row1.getCell(0).getStringCellValue()
                        + "   " + row1.getCell(1).getStringCellValue()
                        + "   " + row1.getCell(2).getStringCellValue());
            }
            System.out.println("-----------------");
        fis.close();
        sheets.close();
        }
}

零件进库表的生成类

package Generate;

import Entity.ComponentsTwo;
import Entity.Pathname;
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 java.io.*;
import java.text.NumberFormat;
import java.util.Scanner;

/**
 * 零件进库表的生成类
 */
public class XlsxTwo {

    //生成手动输入零件信息的零件进库表.xlsx
    public  static void creatXlsx1(ComponentsTwo cp, Pathname pn)  {
        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件进库表");
        FileOutputStream fos= null;
        FileInputStream fis=null;
        Workbook sheets=null;
        try {
            Scanner sn = new Scanner(System.in);
            System.out.println("输入零件进库表.xlsx文件生成的位置:");
            String pathname = sn.nextLine();
            pn.setPathnameTwo(pathname);
            fos = new FileOutputStream(pn.getPathnameTwo());
            System.out.println("输入零件进库表的行数:");
            cp.setNumTwo(sn.nextInt());
            XSSFRow row = null;
            XSSFCell cell = null;
            Scanner sc = new Scanner(System.in);
            cp.number=new String[cp.getNumTwo()+1];
            cp.amount=new int[cp.getNumTwo()+1];
            //用String类型数组存放输入的零件编号,名字,规格
            for (int i = 0; i < cp.getNumTwo()+1; i++) {
                if (i == 0) {
                    cp.number[0]= "零件编号";
                    cp.amount[0]=0;
                } else {
                    System.out.println("请输入" + i  +"行零件编号:");
                    cp.number[i]= sc.next();
                    System.out.println("请输入" + i  + "行零件数量:");
                    cp.amount[i] = sc.nextInt();
                }
            }
            cp.setNumber(cp.number);
            cp.setAmount(cp.amount);
            for (int i = 0; i < cp.getNumTwo()+2; i++) {
                if (i == 0) {
                    row = sheet.createRow(i);
                    cell = row.createCell(i);
                    //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
                    row.createCell(0).setCellValue("零件进库表信息");
                    //设置水平居中
                    CellStyle style = workbook.createCellStyle();
                    style.setAlignment(HorizontalAlignment.CENTER);
                    cell.setCellStyle(style);
                    sheet.addMergedRegion(region);
                } else {
                    row = sheet.createRow(i);
                    cell = row.createCell(i);
                    row.createCell(0).setCellValue(cp.getNumber()[i-1]);
                    if(i==1){
                        row.createCell(1).setCellValue("零件数量");
                    }else {
                        row.createCell(1).setCellValue(cp.getAmount()[i-1]);
                        //由于Excel设置数字默认类型为浮点型,需要将小数点.0去掉
                        double a=row.getCell(1).getNumericCellValue();
                        NumberFormat nf=NumberFormat.getInstance();
                        String s=nf.format(a);
                        int c=Integer.parseInt(s);
                        row.createCell(1).setCellValue(c);
                    }
                }
            }
            System.out.println("生成成功,已存入" + pathname + "目录中!");
            System.out.println("------零件进库表------");
            workbook.write(fos);
            workbook.close();
            fos.close();

            //打印上面输入的零件表信息
            fis = new FileInputStream(pathname);
            sheets = new XSSFWorkbook(fis);
            Sheet sheet1 = sheets.getSheetAt(0);
            for (int i = 1; i < sheet1.getLastRowNum() + 1; i++) {
                //获取行数
                Row row1 = sheet1.getRow(i);
                //获取单元值并且取值
                if(i==1){
                    System.out.println(row1.getCell(0).getStringCellValue()
                            + "   " + row1.getCell(1).getStringCellValue());
                }else {
                    double a=row1.getCell(1).getNumericCellValue();
                    System.out.println(row1.getCell(0).getStringCellValue()
                            + "   " + (int)a);
                }
            }
            System.out.println("-----------------");
        } catch (FileNotFoundException e) {
            System.out.println("输入的文件路径有误");
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(sheets!=null){
                try {
                    sheets.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(fis!=null){
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //从外面导入已经准备好的零件进库表.xlsx
    public static void creatXlsx2(ComponentsTwo two,Pathname pn)  {
        FileInputStream fis= null;
        Workbook sheets=null;
        try {
            System.out.println("输入你要导入的零件进库表文件路径:");
            Scanner sn=new Scanner(System.in);
            String pathname=sn.nextLine();
            pn.setPathnameTwo(pathname);
            fis = new FileInputStream(pn.getPathnameTwo());
            sheets=new XSSFWorkbook(fis);
            Sheet sheet1=sheets.getSheetAt(0);
            System.out.println("导入零件进库表.xlsx成功!");
            System.out.println("------零件进库表-------");
            two.number=new String[sheet1.getLastRowNum()];
            two.amount=new int[sheet1.getLastRowNum()];
            for (int i = 1; i < sheet1.getLastRowNum()+1;i++){
                //获取行数
                Row row1=sheet1.getRow(i);
                //获取单元值并且取值
                two.number[i-1]=row1.getCell(0).getStringCellValue();
                if(i==1){
                    System.out.println(row1.getCell(0).getStringCellValue()
                            +"   "+row1.getCell(1).getStringCellValue());
                    two.amount[i-1]=0;
                }else{
                    double a=row1.getCell(1).getNumericCellValue();
                    System.out.println(row1.getCell(0).getStringCellValue()
                            +"   "+(int)a);
                    two.amount[i-1]=(int)a;
                }
                two.setNumber(two.number);
                two.setAmount(two.amount);
            }
            System.out.println("---------------------");
        } catch (FileNotFoundException e) {
            System.out.println("输入的导入文件路径有误");
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(sheets!=null){
                try {
                    sheets.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(fis!=null){
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    //打印输出零件进库表的零件信息
    public  static void printXlsxTwo(Pathname pn) throws IOException {
        FileInputStream fis = new FileInputStream(pn.getPathnameTwo());
        Workbook sheets=new XSSFWorkbook(fis);
        Sheet sheet2 = sheets.getSheetAt(0);
        for (int i = 1; i < sheet2.getLastRowNum() + 1; i++) {
            //获取行数
            Row row2 = sheet2.getRow(i);
            //获取单元值并且取值
            if(i==1){
                System.out.println(row2.getCell(0).getStringCellValue()
                        + "   " + row2.getCell(1).getStringCellValue());
            }else{
                double a=row2.getCell(1).getNumericCellValue();
                System.out.println(row2.getCell(0).getStringCellValue()
                        + "   " + (int)a);
            }
        }
        System.out.println("-----------------");
        fis.close();
        sheets.close();
    }

}

零件汇总表的生成类

package Generate;

import Entity.ComponentsSum;
import Entity.Pathname;
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 java.io.*;
import java.util.Arrays;
import java.util.Scanner;

/*
    根据零件表和零件进库表的编号判断是否相同,
    抽取相同的零件信息并且排列编号为汇总表
 */

class E implements Comparable<E>{ //实现comparable接口，复写comparato方法
    String number;
    String name;
    String size;
    @Override
    public int compareTo(E e) {
        if (this.number.compareTo(e.number) <0 )
            return 1;
        else
            return -1;
    }
}
class H implements Comparable<H>{ //实现comparable接口，复写comparato方法
    String number;
    Double amount;
    @Override
    public int compareTo(H d) {
        if (this.number.compareTo(d.number) <0 )
            return 1;
        else
            return -1;
    }
}

/**
 * 零件汇总表的生成类
 */
public class XlsxSum {
    public  static void creatSumXlsx1(ComponentsSum three, Pathname pn) throws IOException {
        //获取两个表的信息
        FileInputStream fis1 = new FileInputStream(pn.getPathnameOne());
        FileInputStream fis2 = new FileInputStream(pn.getPathnameTwo());
        Workbook sheetsOne=new XSSFWorkbook(fis1);
        Sheet sheet1=sheetsOne.getSheetAt(0);
        Workbook sheetsTwo=new XSSFWorkbook(fis2);
        Sheet sheet2=sheetsTwo.getSheetAt(0);
        //产生汇总表
        XSSFWorkbook workbook=new XSSFWorkbook();
        System.out.println("已自动生成汇总表!");
        XSSFSheet sheet=workbook.createSheet("零件汇总表");
        System.out.println("输入零件汇总表.xlsx生成的位置:");
        Scanner sc=new Scanner(System.in);
        String pathnameThree=sc.nextLine();
        pn.setPathnameSum(pathnameThree);
        FileOutputStream fos=new FileOutputStream(pn.getPathnameSum());
        XSSFRow row=null;
        XSSFCell cell=null;
        //用String类型数组存放输入的零件编号,名字,规格
        int num1=sheet1.getLastRowNum()-1;
        int num2=sheet2.getLastRowNum()-1;
        int a=Math.min(num1,num2);
        //定义一个arr数组用来存储零件表.xlsx所有零件信息
        String[] numberOne=new String[num1]; //编号
        String[] name=new String[num1];
        String[] size=new String[num1];
        //定义一个brr数组用来存储零件进库表.xlsx所有零件信息
        String[] numberTwo=new String[num2]; //编号
        double[] amount=new double[num2];
        //定义零件汇总表对象的成员变量
        three.number=new String[a+1];
        three.name=new String[a+1];
        three.size=new String[a+1];
        three.amount=new int[a+1];
        //先将零件表.xlsx按编号排序好零件信息,并存储在对应的数组里
        for (int i = 2; i < num1+2; i++) {
            Row row1=sheet1.getRow(i);
            numberOne[i-2]=row1.getCell(0).getStringCellValue();
            name[i-2]=row1.getCell(1).getStringCellValue();
            size[i-2]=row1.getCell(2).getStringCellValue();
        }
        //先将零件进库表.xlsx按编号排序好零件信息,并存储在对应的数组里
        for (int i = 2; i < num2+2; i++) {
            Row row2=sheet2.getRow(i);
            numberTwo[i-2]=row2.getCell(0).getStringCellValue();
            amount[i-2]=row2.getCell(1).getNumericCellValue();
        }

        E e [] = new E[num1];
        for (int i = 0; i < num1; i++) {
            e[i] = new E();
            e[i].number = numberOne[i];
            e[i].name = name[i];
            e[i].size = size[i];
        }
        Arrays.sort(e);//从大到小排序
        int e1=0;
        for (int i = num1-1; i >=0; i--) {
            numberOne[e1]=e[i].number;
            name[e1]=e[i].name;
            size[e1]=e[i].size;
            e1++;
        }

        H h [] = new H[num2];
        for (int i = 0; i < num2; i++) {
            h[i] = new H();
            h[i].number= numberTwo[i];
            h[i].amount = amount[i];
        }
        Arrays.sort(h);
        int h1=0;
        for (int i = num2-1; i >=0; i--) {
            numberTwo[h1]=h[i].number;
            amount[h1]=h[i].amount;
            h1++;
        }

        //设置零件汇总表.xlsx标题和表头
        for (int i = 0; i < 2; i++) {
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if(i==0){
                //设置标题
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
                row.createCell(0).setCellValue("零件汇总表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                //设置表头:编号,名称,规格,进库数量
                row.createCell(0).setCellValue("零件编号");
                row.createCell(1).setCellValue("零件名称");
                row.createCell(2).setCellValue("零件规格");
                row.createCell(3).setCellValue("零件数量");
                three.number[0]="零件编号";
                three.name[0]="零件名称";
                three.size[0]="零件规格";
                three.amount[0]=0;
                three.setNumber(three.number);
                three.setName(three.name);
                three.setSize(three.size);
                three.setAmount(three.amount);
            }
        }
        //采用归并思想,判断两个(排列好编号)表的编号是否相同,若相同则放入汇总表里
        int i=0,j=0,d1=0,d2=0;
        int k=2;
        while(i<numberOne.length && j<numberTwo.length){
            if(numberOne[i].equals(numberTwo[j])){
                ++d2;
                row=sheet.createRow(k);
                row.createCell(0).setCellValue(numberOne[i]);
                row.createCell(1).setCellValue(name[i]);
                row.createCell(2).setCellValue(size[i]);
                row.createCell(3).setCellValue(amount[j]);
                three.number[d2]=numberOne[d1];
                three.name[d2]=name[d1];
                three.size[d2]=size[d1];
                three.amount[d2]=(int)amount[d1];
                three.setNumber(three.number);
                three.setName(three.name);
                three.setSize(three.size);
                three.setAmount(three.amount);
                ++d1;
                k++;
                i++;
                j++;
            }else if((numberOne[i].compareTo(numberTwo[j]))==-1){
                i++;
            }else{
                j++;
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        PrintSumXlsx(pn);
    }

    //从外面导入汇总表
    public static void creatSumXlsx2(ComponentsSum three,Pathname pn) throws IOException {
        System.out.println("请输入要导入的零件汇总表路径:");
        Scanner sc=new Scanner(System.in);
        String pathname = sc.next();
        pn.setPathnameSum(pathname);
        FileInputStream fis = new FileInputStream(pn.getPathnameSum());
        Workbook sheets=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets.getSheetAt(0);
        three.number=new String[sheet1.getLastRowNum()];
        three.name=new String[sheet1.getLastRowNum()];
        three.size=new String[sheet1.getLastRowNum()];
        three.amount=new int[sheet1.getLastRowNum()];
        System.out.println("------零件汇总表------");
        for (int i = 1; i < sheet1.getLastRowNum() + 1; i++) {
            //获取行数
            Row row1 = sheet1.getRow(i);
            //获取单元值并且取值
            three.number[i-1]=row1.getCell(0).getStringCellValue();
            three.name[i-1]=row1.getCell(1).getStringCellValue();
            three.size[i-1]=row1.getCell(2).getStringCellValue();
            if(i==1){
                three.amount[i-1]=0;
                System.out.println(row1.getCell(0).getStringCellValue()
                        + "   " + row1.getCell(1).getStringCellValue()
                        + "   " + row1.getCell(2).getStringCellValue()
                        + "   " + row1.getCell(3).getStringCellValue());
            }else{
                double c=row1.getCell(3).getNumericCellValue();
                System.out.println(row1.getCell(0).getStringCellValue()
                        + "   " + row1.getCell(1).getStringCellValue()
                        + "   " + row1.getCell(2).getStringCellValue()
                        + "   " + (int)c);
                three.amount[i-1]=(int)c;
            }
        }
        System.out.println("-----------------");
        fis.close();
        sheets.close();
    }



    //打印输出汇总表
    public  static void PrintSumXlsx(Pathname pn) throws IOException {
        FileInputStream fis=new FileInputStream(pn.getPathnameSum());
        Workbook sheets=new XSSFWorkbook(fis);
        Sheet sheet=sheets.getSheetAt(0);
        System.out.println("--------零件汇总表(排序好编号后)----------");
        for (int i = 1; i < sheet.getLastRowNum()+1;i++){
            //获取行数
            Row row=sheet.getRow(i);
            //获取单元值并且取值
            if(i==1){
                System.out.println(row.getCell(0).getStringCellValue()
                        +"   "+row.getCell(1).getStringCellValue()
                        +"   "+row.getCell(2).getStringCellValue()
                        +"   "+row.getCell(3).getStringCellValue());
            }else{
                double c= row.getCell(3).getNumericCellValue();
                System.out.println(row.getCell(0).getStringCellValue()
                        +"   "+row.getCell(1).getStringCellValue()
                        +"   "+row.getCell(2).getStringCellValue()
                        +"   "+(int)c);
            }
        }
        System.out.println("--------------------------");
        sheets.close();
        fis.close();
    }
}

Poi操作三个Excel表中的零件信息

增加功能类

package Function;

import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import Generate.XlsxOne;
import Generate.XlsxSum;
import Generate.XlsxTwo;
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 java.io.*;
import java.util.Arrays;
import java.util.Scanner;

class G implements Comparable<G>{ //实现comparable接口,复写comparato方法
    String number;
    String name;
    String size;
    int amount;
    @Override
    public int compareTo(G g) {
        if (this.number.compareTo(g.number) <0 )
            return 1;
        else
            return -1;
    }
}
/**
 *  添加功能类:
 *  添加一行零件(零件表,零件进库表,零件汇总表)信息
 */
public class XlsxAdd {

    //零件表末尾添加一行信息的方法
    public  static void addXlsxOne(ComponentsOne one, Pathname pn) throws IOException {
        System.out.println("------零件表(添加前)------");
        XlsxOne.printXlsxOne(pn);
        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件表");
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameOne()));
        Workbook sheets=null;
        XSSFRow row=null;
        XSSFCell cell=null;
        Scanner sc=new Scanner(System.in);
        //增加零件表每个零件信息数组的长度+1
        one.number = Arrays.copyOf(one.number,one.number.length+1);
        one.name = Arrays.copyOf(one.name, one.name.length + 1);
        one.size = Arrays.copyOf(one.size,one.size.length+1);
        System.out.println("依次输入需要添加的零件编号,零件名称,零件规格");
        String addNumber=sc.next();
        String addName=sc.next();
        String addSize=sc.next();
        for (int i = 0; i < one.number.length+1; i++) { //4+1=5
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if (i == 0) {
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
                row.createCell(0).setCellValue("零件表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
            }  else {
                //在数组尾部添加新增的零件信息
                if(i==one.number.length){
                    row.createCell(0).setCellValue(addNumber);
                    row.createCell(1).setCellValue(addName);
                    row.createCell(2).setCellValue(addSize);
                    one.number[i-1]=addNumber;
                    one.name[i-1]=addName;
                    one.size[i-1]=addSize;
                    one.setNumber(one.number);
                    one.setName(one.name);
                    one.setSize(one.size);
                }else {
                    row.createCell(0).setCellValue(one.getNumber()[i-1]);
                    row.createCell(1).setCellValue(one.getName()[i-1]);
                    row.createCell(2).setCellValue(one.getSize()[i-1]);
                }
            }
        }
        System.out.println("添加成功,已存入" + pn.getPathnameOne() + "目录中!");
        workbook.write(fos);
        workbook.close();
        fos.close();
        //打印上面输入的零件表信息
        System.out.println("------零件表(添加后)------");
        XlsxOne.printXlsxOne(pn);
    }


   //零件进库表末尾添加一行信息的方法
    public  static void addXlsxTwo(ComponentsTwo two, Pathname pn) throws IOException {
        System.out.println("------零件进库表(添加前)------");
        XlsxTwo.printXlsxTwo(pn);
        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件进库表");
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameTwo()));
        Workbook sheets=null;
        XSSFRow row=null;
        XSSFCell cell=null;
        Scanner sc=new Scanner(System.in);
        //增加零件表每个零件信息数组的长度+1
        two.number = Arrays.copyOf(two.number,two.number.length+1);
        two.amount = Arrays.copyOf(two.amount,two.amount.length+1);
        System.out.println(two.number.length);
        System.out.println("依次输入需要添加的零件编号,零件数量");
        String addNumber=sc.next();
        int addAmount=sc.nextInt();
        for (int i = 0; i < two.number.length+1; i++) { //4+1=5
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if (i == 0) {
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
                row.createCell(0).setCellValue("零件进库表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
            }  else {
                //在数组尾部添加新增的零件信息
                if(i==two.number.length){
                    row.createCell(0).setCellValue(addNumber);
                    row.createCell(1).setCellValue(addAmount);
                    two.number[i-1]=addNumber;
                    two.amount[i-1]=addAmount;
                    two.setNumber(two.number);
                    two.setAmount(two.amount);
                }else if(i==1){
                    row.createCell(0).setCellValue(two.getNumber()[i-1]);
                    row.createCell(1).setCellValue("零件数量");
                }else{
                    row.createCell(0).setCellValue(two.getNumber()[i-1]);
                    row.createCell(1).setCellValue(two.getAmount()[i-1]);
                }
            }
        }
        System.out.println("添加成功,已存入" + pn.getPathnameTwo() + "目录中!");
        workbook.write(fos);
        workbook.close();
        fos.close();

        //打印上面输入的零件表信息
        System.out.println("------零件进库表(添加后)------");
        XlsxTwo.printXlsxTwo(pn);
    }

    //零件汇总表末尾添加一行信息的方法,并且排序好编号
    public static void addXlsxThree(ComponentsSum three, Pathname pn) throws IOException {
        System.out.println("------零件汇总表(添加前)------");
        XlsxSum.PrintSumXlsx(pn);
        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件汇总表");
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameSum()));
        Workbook sheets=null;
        XSSFRow row=null;
        XSSFCell cell=null;
        Scanner sc=new Scanner(System.in);
        //增加零件表每个零件信息数组的长度+1
        three.number = Arrays.copyOf(three.number,three.number.length+1);
        three.name = Arrays.copyOf(three.name,three.name.length+1);
        three.size = Arrays.copyOf(three.size,three.size.length+1);
        three.amount = Arrays.copyOf(three.amount,three.amount.length+1);
        System.out.println("依次输入需要添加的零件编号,零件名称,零件规格,零件数量");
        String addNumber=sc.next();
        String addName=sc.next();
        String addSize=sc.next();
        int addAmount=sc.nextInt();

        //将汇总表的零件信息全部存储在数组g里面
        G g [] = new G[three.number.length-1];
        for (int i = 0; i < g.length; i++) {   //g.length==6
            g[i] = new G();
            g[i].number= three.getNumber()[i+1];
            g[i].name = three.getName()[i+1];
            g[i].size= three.getSize()[i+1];
            g[i].amount = three.getAmount()[i+1];
            if(i==(g.length-1)){
                g[i].number = addNumber;
                g[i].name = addName;
                g[i].size = addSize;
                g[i].amount = addAmount;
            }
        }
        //从大到小对编号进行排序
        Arrays.sort(g);
        //想得到从小到大的编号,所以倒置循环一下,将零件信息又存储在three对象的成员变量数组中
        int e=1;
        for (int i = g.length; i >= 1; i--) {
            three.number[e]=g[i-1].number;
            three.name[e]=g[i-1].name;
            three.size[e]=g[i-1].size;
            three.amount[e]=g[i-1].amount;
            ++e;
        }
        for (int i = 0; i < three.number.length+1; i++) {
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if (i == 0) {
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
                row.createCell(0).setCellValue("零件汇总表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
                three.number[0]="零件编号";
                three.name[0]="零件名称";
                three.size[0]="零件规格";
                three.amount[0]=0;
            }  else {
                three.setNumber(three.number);
                three.setName(three.name);
                three.setSize(three.size);
                three.setAmount(three.amount);
                //在数组尾部添加新增的零件信息
                row.createCell(0).setCellValue(three.getNumber()[i-1]);
                row.createCell(1).setCellValue(three.getName()[i-1]);
                row.createCell(2).setCellValue(three.getSize()[i-1]);
                if(i==1){
                    row.createCell(3).setCellValue("零件数量");
                }else{
                    row.createCell(3).setCellValue(three.getAmount()[i-1]);
                }
            }
        }
        System.out.println("添加成功,已存入" + pn.getPathnameSum() + "目录中!");
        workbook.write(fos);
        workbook.close();
        fos.close();

        //打印上面输入的零件表信息
        System.out.println("------零件进库表(添加后)------");
        XlsxSum.PrintSumXlsx(pn);
    }
}

删除功能

package Function;

import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import Generate.XlsxOne;
import Generate.XlsxSum;
import Generate.XlsxTwo;
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 java.io.*;
import java.util.Arrays;
import java.util.Scanner;

/**
 *  删除功能:根据零件编号删除对应的零件(零件表,零件进库表,零件汇总表)信息
 */
public class XlsxDelete {

    //根据零件编号删除该零件表的零件信息的方法
    public  static void deleteXlsxOne(ComponentsOne one, Pathname pn) throws IOException {
        System.out.println("------零件表(删除前)------");
        XlsxOne.printXlsxOne(pn);
            Scanner sc=new Scanner(System.in);
            System.out.println("输入你想删除的零件信息对应的编号:");
            String deleteNumber=sc.next();
            int b=0;
        for (int i = 0; i < one.number.length; i++) {
            if(deleteNumber.equals(one.getNumber()[i])){
                b=i;
            }
        }
        for (int i = b; i < one.number.length-1; i++) { //1 2
                one.number[i]=one.getNumber()[i+1];
                one.name[i]=one.getName()[i+1];
                one.size[i]=one.getSize()[i+1];
                one.setNumber(one.number);
                one.setName(one.name);
                one.setSize(one.size);
        }
        //将零件信息的数组长度缩小一个长度
        one.number=Arrays.copyOf(one.number,one.number.length-1);
        one.name=Arrays.copyOf(one.name,one.name.length-1);
        one.size=Arrays.copyOf(one.size,one.size.length-1);

        //创建一个删除指定零件编号的零件表
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameOne()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件表");
        XSSFRow rowDelete=null;
        XSSFCell cellDelete=null;
        for (int i = 0; i < one.number.length+1; i++) { // 0 1 2 3
            rowDelete=sheet.createRow(i);
            cellDelete=rowDelete.createCell(i);
            if(i==0){
                rowDelete = sheet.createRow(0);
                cellDelete = rowDelete.createCell(0);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
                rowDelete.createCell(0).setCellValue("零件表信息");
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cellDelete.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                rowDelete.createCell(0).setCellValue(one.getNumber()[i-1]);
                rowDelete.createCell(1).setCellValue(one.getName()[i-1]);
                rowDelete.createCell(2).setCellValue(one.getSize()[i-1]);
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        //输出删除后的零件表信息
        System.out.println("------零件表(删除后)------");
        XlsxOne.printXlsxOne(pn);
    }

    //根据零件编号删除该零件进库表的零件信息的方法
    public static void deleteXlsxTwo(ComponentsTwo two, Pathname pn) throws IOException {
        System.out.println("------零件进库表(删除前)------");
        XlsxTwo.printXlsxTwo(pn);
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想删除的零件信息对应的零件编号:");
        String deleteNumber=sc.next();
        int b=0;
        for (int i = 0; i < two.number.length; i++) {
            if(deleteNumber.equals(two.getNumber()[i])){
                b=i;
            }
        }
        for (int i = b; i < two.number.length-1; i++) { //1 2
            two.number[i]=two.getNumber()[i+1];
            two.amount[i]=two.getAmount()[i+1];
            two.setNumber(two.number);
            two.setAmount(two.amount);
        }
        //将零件信息的数组长度缩小一个长度
        two.number=Arrays.copyOf(two.number,two.number.length-1);
        two.amount=Arrays.copyOf(two.amount,two.amount.length-1);

        //创建一个删除指定零件编号的零件进库表
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameTwo()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件进库表");
        XSSFRow rowDelete=null;
        XSSFCell cellDelete=null;
        for (int i = 0; i < two.number.length+1; i++) {
            rowDelete=sheet.createRow(i);
            cellDelete=rowDelete.createCell(i);
            if(i==0){
                rowDelete = sheet.createRow(0);
                cellDelete = rowDelete.createCell(0);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
                rowDelete.createCell(0).setCellValue("零件进库表信息");
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cellDelete.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                rowDelete.createCell(0).setCellValue(two.getNumber()[i-1]);
                if(i==1){
                rowDelete.createCell(1).setCellValue("零件数量");
                }else {
                    rowDelete.createCell(1).setCellValue(two.getAmount()[i-1]);
                }
        }
    }
        workbook.write(fos);
        workbook.close();
        fos.close();
        //输出删除后的零件进库表信息
        System.out.println("------零件进库表(删除后)------");
        XlsxTwo.printXlsxTwo(pn);
    }

    //根据零件编号删除该零件汇总表的零件信息的方法
    public static void deleteXlsxSum(ComponentsSum three, Pathname pn) throws IOException {
        System.out.println("------零件汇总表(删除前)------");
        XlsxSum.PrintSumXlsx(pn);
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想删除的零件信息对应的编号:");
        String deleteNumber=sc.next();
        int b=0;
        for (int i = 0; i < three.number.length; i++) {
            if(deleteNumber.equals(three.getNumber()[i])){
                b=i;
            }
        }
        for (int i = b; i < three.number.length-1; i++) { //1 2
            three.number[i]=three.getNumber()[i+1];
            three.name[i]=three.getName()[i+1];
            three.size[i]=three.getSize()[i+1];
            three.amount[i]=three.getAmount()[i+1];
            three.setNumber(three.number);
            three.setName(three.name);
            three.setSize(three.size);
            three.setAmount(three.amount);
        }
        //将零件信息的数组长度缩小一个长度
        three.number=Arrays.copyOf(three.number,three.number.length-1);
        three.name=Arrays.copyOf(three.name,three.name.length-1);
        three.size=Arrays.copyOf(three.size,three.size.length-1);
        three.amount=Arrays.copyOf(three.amount,three.amount.length-1);
        //创建一个删除指定零件编号的零件表
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameSum()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件汇总表");
        XSSFRow rowDelete=null;
        XSSFCell cellDelete=null;
        for (int i = 0; i < three.number.length+1; i++) {
            rowDelete=sheet.createRow(i);
            cellDelete=rowDelete.createCell(i);
            if(i==0){
                rowDelete = sheet.createRow(0);
                cellDelete = rowDelete.createCell(0);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
                rowDelete.createCell(0).setCellValue("零件汇总表信息");
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cellDelete.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                rowDelete.createCell(0).setCellValue(three.getNumber()[i-1]);
                rowDelete.createCell(1).setCellValue(three.getName()[i-1]);
                rowDelete.createCell(2).setCellValue(three.getSize()[i-1]);
                if(i==1){
                    rowDelete.createCell(3).setCellValue("零件数量");
                }else {
                    rowDelete.createCell(3).setCellValue(three.getAmount()[i-1]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        //输出删除后的零件表信息
        System.out.println("------零件汇总表(删除后)------");
        XlsxSum.PrintSumXlsx(pn);
    }
}

修改功能

package Function;

import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import Generate.XlsxOne;
import Generate.XlsxSum;
import Generate.XlsxTwo;
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 java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Scanner;
/**
 *  修改功能类:
 *  零件表根据零件编号修改对应的零件名称和零件规格
 *  零件进库表根据零件编号修改对应的零件数量
 *  零件汇总表根据零件编号修改对应的零件名称,零件规格,零件数量
 */

public class XlsxChange {



    //修改零件表的某个信息方法
    public  static void changeXlsxOne(ComponentsOne one, Pathname pn) throws IOException {
        System.out.println("------零件表(修改前)------");
        XlsxOne.printXlsxOne(pn);
        FileInputStream fis=new FileInputStream(pn.getPathnameOne());
        XSSFWorkbook workbook = new XSSFWorkbook();
        //输入编号打印零件名称和零件规格
        Workbook sheets1=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets1.getSheetAt(0);
        Scanner sc=new Scanner(System.in);
        XSSFRow row = null;
        XSSFCell cell = null;
        FileOutputStream fos=null;
        XSSFSheet sheet = workbook.createSheet("零件表");
        fos = new FileOutputStream(pn.getPathnameOne());
        //设置标题
        row = sheet.createRow(0);
        cell = row.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
        row.createCell(0).setCellValue("零件表信息");
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        sheet.addMergedRegion(region);
        //设置表头和表体
        System.out.println("输入想要修改的零件对应的编号:");
        String idXlsx=sc.next();
        System.out.println("输入修改后的零件名字:");
        String nameChange=sc.next();
        System.out.println("输入修改后的零件规格:");
        String sizeChange=sc.next();
        for (int i = 0; i < one.getNumber().length; i++) {
            row = sheet.createRow(i + 1);
            cell = row.createCell(i + 1);
            if (i == 0) {
                row.createCell(0).setCellValue(one.getNumber()[0]);
                row.createCell(1).setCellValue(one.getName()[0]);
                row.createCell(2).setCellValue(one.getSize()[0]);
            } else {
                row.createCell(0).setCellValue(one.getNumber()[i]);
                if (idXlsx.equals(one.getNumber()[i])) {
                    one.name[i] = nameChange;
                    one.size[i] = sizeChange;
                    one.setName(one.name);
                    one.setSize(one.size);
                    row.createCell(1).setCellValue(nameChange);
                    row.createCell(2).setCellValue(sizeChange);
                } else {
                    row.createCell(1).setCellValue(one.getName()[i]);
                    row.createCell(2).setCellValue(one.getSize()[i]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        System.out.println("修改成功,已存入" +pn.getPathnameOne()+ "目录中!");
        //打印输出修改后的零件表信息
        System.out.println("------零件表(修改后)------");
        XlsxOne.printXlsxOne(pn);
        fis.close();
        sheets1.close();
    }



    //修改零件进库表中某个零件信息的方法
    public static void changeXlsxTwo(ComponentsTwo two, Pathname pn) throws IOException {
        System.out.println("------零件进库表(添加前)------");
        XlsxTwo.printXlsxTwo(pn);
        FileInputStream fis=new FileInputStream(pn.getPathnameTwo());
        XSSFWorkbook workbook = new XSSFWorkbook();
        //输入编号打印零件名称和零件规格
        Workbook sheets2=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets2.getSheetAt(0);
        Scanner sc=new Scanner(System.in);
        XSSFRow row = null;
        XSSFCell cell = null;
        FileOutputStream fos=null;
        XSSFSheet sheet = workbook.createSheet("零件进库表");
        fos = new FileOutputStream(pn.getPathnameTwo());
        //设置标题
        row = sheet.createRow(0);
        cell = row.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
        row.createCell(0).setCellValue("零件进库表信息");
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        sheet.addMergedRegion(region);
        //设置表头和表体
        System.out.println("输入想要修改的零件对应的编号:");
        String idXlsx=sc.next();
        System.out.println("输入修改后的零件数量:");
        int amountChange=sc.nextInt();
            for (int i = 0; i < two.getNumber().length; i++) {
                row = sheet.createRow(i+1);
                cell = row.createCell(i+1);
                if (i == 0) {
                    row.createCell(0).setCellValue(two.getNumber()[0]);
                    row.createCell(1).setCellValue("零件数量");
                } else {
                    row.createCell(0).setCellValue(two.getNumber()[i]);
                    if (idXlsx.equals(two.getNumber()[i])) {
                        two.amount[i]=amountChange;
                        two.setAmount(two.amount);
                        row.createCell(1).setCellValue(amountChange);
                    }else{
                        row.createCell(1).setCellValue(two.getAmount()[i]);
                    }
                }
            }
        workbook.write(fos);
        workbook.close();
        fos.close();
        System.out.println("修改成功,已存入" +pn.getPathnameTwo()+ "目录中!");
        //打印输出零件进库表信息
        System.out.println("------零件进库表(添加前)------");
        XlsxTwo.printXlsxTwo(pn);
        fis.close();
        sheets2.close();
    }

    //修改零件汇总表零件信息的方法
    public static void changeXlsxSum(ComponentsSum three, Pathname pn) throws IOException {
        System.out.println("------零件汇总表(添加前)------");
        XlsxSum.PrintSumXlsx(pn);
        FileInputStream fis=new FileInputStream(pn.getPathnameSum());
        XSSFWorkbook workbook = new XSSFWorkbook();
        Workbook sheets2=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets2.getSheetAt(0);
        Scanner sc=new Scanner(System.in);
        XSSFRow row = null;
        XSSFCell cell = null;
        FileOutputStream fos=null;
        XSSFSheet sheet = workbook.createSheet("零件汇总表");
        fos = new FileOutputStream(pn.getPathnameSum());
        //设置标题
        row = sheet.createRow(0);
        cell = row.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
        row.createCell(0).setCellValue("零件汇总表信息");
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        sheet.addMergedRegion(region);
        //设置表头和表体
        System.out.println("输入想要修改的零件对应的编号:");
        String idXlsx=sc.next();
        //输入需要修改的零件名称,规格,数量
        System.out.println("输入修改后的零件名称");
        String nameChange=sc.next();
        System.out.println("输入修改后的零件规格");
        String sizeChange=sc.next();
        System.out.println("输入修改后的零件数量");
        int amountChange= sc.nextInt();
            for (int i = 0; i < three.getNumber().length; i++) {
                row = sheet.createRow(i+1);
                cell = row.createCell(i+1);
                if (i == 0) {
                    row.createCell(0).setCellValue(three.getNumber()[0]);
                    row.createCell(1).setCellValue(three.getName()[0]);
                    row.createCell(2).setCellValue(three.getSize()[0]);
                    row.createCell(3).setCellValue("零件数量");
                } else {
                    row.createCell(0).setCellValue(three.getNumber()[i]);
                    if (idXlsx.equals(three.getNumber()[i])) {
                        three.name[i]=nameChange;
                        three.size[i]=sizeChange;
                        three.amount[i]=amountChange;
                        three.setName(three.name);
                        three.setSize(three.size);
                        three.setAmount(three.amount);
                        row.createCell(1).setCellValue(nameChange);
                        row.createCell(2).setCellValue(sizeChange);
                        row.createCell(3).setCellValue(amountChange);
                    }else{
                        row.createCell(1).setCellValue(three.getName()[i]);
                        row.createCell(2).setCellValue(three.getSize()[i]);
                        row.createCell(3).setCellValue(three.getAmount()[i]);
                    }
                }
            }
        workbook.write(fos);
        workbook.close();
        fos.close();
        System.out.println("修改成功,已存入" +pn.getPathnameSum()+ "目录中!");
        //打印修改后的零件汇总表信息
        System.out.println("------零件汇总表(添加后且自动排序)------");
        XlsxSum.PrintSumXlsx(pn);
        fis.close();
        sheets2.close();
    }
}

查询功能

package Function;


import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import Generate.XlsxOne;
import Generate.XlsxSum;
import Generate.XlsxTwo;

import java.io.*;
import java.util.Scanner;

/**
 * 查询功能:(零件表,零件进库表,零件汇总表)根据零件编号查询该对应的零件信息
 */
public class XlsxSelect {

    //根据零件编号查询零件表对应的零件信息的方法
    public static void selectXlsxOne(ComponentsOne one, Pathname pn) throws IOException {
        System.out.println("------零件表------");
        XlsxOne.printXlsxOne(pn);
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查询零件表的零件编号:");
        String idSelect = sc.next();
        for (int i = 0; i < one.number.length; i++) {
            if (idSelect.equals(one.getNumber()[i])) {
                System.out.println("零件编号:" + one.getNumber()[i]);
                System.out.println("零件名称:" + one.getName()[i]);
                System.out.println("零件规格:" + one.getSize()[i]);
            }
        }
    }

    //根据零件编号查询零件进库表对应的零件信息的方法
    public static void selectXlsxTwo(ComponentsTwo two, Pathname pn) throws IOException {
        System.out.println("------零件进库表------");
        XlsxTwo.printXlsxTwo(pn);
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查询零件进库表的零件编号:");
        String idSelect = sc.next();
        for (int i = 0; i < two.number.length; i++) {
            if (idSelect.equals(two.getNumber()[i])) {
                System.out.println("零件编号:" + two.getNumber()[i]);
                System.out.println("零件数量:" + two.getAmount()[i]);
            }
        }
    }

    //根据零件编号查询零件汇总表对应的零件信息的方法
    public static void selectXlsxSum(ComponentsSum three, Pathname pn) throws IOException {
        System.out.println("------零件汇总表------");
        XlsxSum.PrintSumXlsx(pn);
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查询零件汇总表的零件编号:");
        String idSelect = sc.next();
        for (int i = 0; i < three.number.length; i++) {
            if (idSelect.equals(three.getNumber()[i])) {
                System.out.println("零件编号:" + three.getNumber()[i]);
                System.out.println("零件名称:" + three.getName()[i]);
                System.out.println("零件规格:" + three.getSize()[i]);
                System.out.println("零件数量:" + three.getAmount()[i]);
            }
        }
    }
}

JDBC连接数据库操作三个Excel表中的零件信息

src目录建立一个mysql.properties配置文件

url=jdbc:mysql://localhost:3306/compoents
user=root
password=123456
driver=com.mysql.cj.jdbc.Driver

配置文件加载类Config

package jdbc;

import java.io.FileInputStream;
import java.util.Properties;

/**
 * mysql.properties配置文件加载类
 */
public class Config {
    private static Properties p =null;
    static{
        try{
            p=new Properties();
            //加载配置文件
            p.load(new FileInputStream("src/mysql.properties"));
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //获取键对应的值
    public static String getValue(String key){
        return p.get(key).toString();
    }
}

工具类

package jdbc;

import java.sql.*;

/**
 * 数据库工具类
 */
public class DBUtil {
    Connection conn=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    /**
     * 得到数据库连接
     */
    public Connection getConnection(){
        //通过Config获取mysql数据库配置信息
        String driver=Config.getValue("driver");
        String url=Config.getValue("url");
        String user=Config.getValue("user");
        String password=Config.getValue("password");
        try{
            //指定驱动程序
            Class.forName(driver);
            //建立数据库连接
            conn= DriverManager.getConnection(url,user,password);
            return conn;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }
    /**
     * 释放资源
     */
    public void closeAll(){
        //如果rs不空，关闭rs
        if(rs!=null){
            try{
                rs.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        //如果pstmt不空，关闭rs
        if(pstmt!=null){
            try{
                pstmt.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        //如果conn不空，关闭rs
        if(conn!=null){
            try{
                conn.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
    /**
     * 执行sql语句，可以进行查询
     */
    public ResultSet executeQuery(String preparedSql,Object[] param){
        //处理SQL,执行Sql
        try{
            //得到PreparedStatement对象
            pstmt=conn.prepareStatement(preparedSql);
            if(param!=null){
                for(int i=0;i<param.length;i++){
                    //为预编译sql设置参数
                    pstmt.setObject(i+1,param[i]);
                }
            }
            rs=pstmt.executeQuery();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return rs;
    }
    /**
     * 执行sql语句，可以进行增、删、改的操作，不能进行查询
     */
    public int executeUpdate(String preparedSql,Object[] param){
        int num=0;
        //处理Sql，执行SQL
        try {
            //得到PreparedStatement对象
            pstmt=conn.prepareStatement(preparedSql);
            if(param!=null){
                for(int i=0;i<param.length;i++){
                    //为预编译sql设置参数
                    pstmt.setObject(i+1,param[i]);
                }
            }
            num=pstmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return num;
        }
}

操作零件表在数据库中对应的零件信息类

package jdbc;

import Entity.ComponentsOne;
import Entity.Pathname;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
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 java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Scanner;

/**
 * 操作零件表在数据库中对应的数据类
 */

public class PartDB {


    public static void insertAllPart(ComponentsOne one) throws SQLException {
        String insertSql="insert into part(number,name,size)values(?,?,?)";
        DBUtil db=new DBUtil();
        db.getConnection();
        int a=0;
        for (int i = 1; i < one.number.length; i++) {
            Object[] param =new Object[]{one.getNumber()[i],one.getName()[i],one.getSize()[i]};
            a=db.executeUpdate(insertSql,param);
        }
        if(a!=0){
            System.out.println("零件表.xlsx信息导入数据库part表成功!");
        }
        db.closeAll();
        printPart();
    }

    public static void insertPart(ComponentsOne one, Pathname pn) throws IOException, SQLException {
        String insertSql = "insert into part(number,name,size)values(?,?,?)";
        DBUtil db = new DBUtil();
        db.getConnection();
        int a = 0;
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要插入的零件编号:");
        String number = sc.next();
        System.out.println("请输入要插入的零件名称:");
        String name = sc.next();
        System.out.println("请输入要插入的零件规格:");
        String size = sc.next();
        Object[] param = new Object[]{number, name, size};
        int b = db.executeUpdate(insertSql, param);
        if (b != 0) {
            System.out.println("插入成功数据到part表成功," + "已自动更新到" + pn.getPathnameOne() + "中!");
        }
        db.closeAll();
        printPart();

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("零件表");
        FileOutputStream fos = new FileOutputStream(new File(pn.getPathnameOne()));
        Workbook sheets = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        //增加零件表每个零件信息数组的长度+1
        one.number = Arrays.copyOf(one.number, one.number.length + 1);
        one.name = Arrays.copyOf(one.name, one.name.length + 1);
        one.size = Arrays.copyOf(one.size, one.size.length + 1);
        for (int i = 0; i < one.number.length + 1; i++) { //4+1=5
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if (i == 0) {
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
                row.createCell(0).setCellValue("零件表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
            } else {
                //在数组尾部添加新增的零件信息
                if (i == one.number.length) {
                    row.createCell(0).setCellValue(number);
                    row.createCell(1).setCellValue(name);
                    row.createCell(2).setCellValue(size);
                    one.number[i -1] = number;
                    one.name[i - 1] = name;
                    one.size[i - 1] = size;
                    one.setNumber(one.number);
                    one.setName(one.name);
                    one.setSize(one.size);
                } else {
                    row.createCell(0).setCellValue(one.getNumber()[i - 1]);
                    row.createCell(1).setCellValue(one.getName()[i - 1]);
                    row.createCell(2).setCellValue(one.getSize()[i - 1]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
    }


    public static void selectPart(ComponentsOne one) throws SQLException {
        String selectSql="select number,name,size from part where number=?";
        DBUtil db=new DBUtil();
        db.getConnection();
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想查询的零件编号:");
        String number=sc.next();
        Object[] param=new Object[]{number};
        ResultSet rs=db.executeQuery(selectSql,param);
        while (rs.next()){
            System.out.println(rs.getString(1)+"       "+rs.getString(2)+"       "+rs.getString(3));
        }
        db.closeAll();
    }
    public static void updatePart(ComponentsOne one,Pathname pn) throws SQLException, IOException {
        String updateSql="update part set name=?,size=? where number=?";
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想修改的零件编号:");
        String number=sc.next();
        System.out.println("输入修改后的零件名称:");
        String name=sc.next();
        System.out.println("输入修改后的零件规格:");
        String size=sc.next();
        //将更新的零件信息也更新到零件想信息实体类
        for (int i = 0; i < one.number.length; i++) {
            if(number.equals(one.getNumber()[i])){
                one.number[i]=number;
                one.name[i]=name;
                one.size[i]=size;
                one.setNumber(one.number);
                one.setName(one.name);
                one.setSize(one.size);
            }
        }
        DBUtil db=new DBUtil();
        db.getConnection();
        Object[] param=new Object[]{name,size,number};
        int b=db.executeUpdate(updateSql,param);
        if (b!=0){
            System.out.println("从part表修改该条零件信息成功!"+"已自动更新到"+pn.getPathnameOne()+"中!");
        }
        db.closeAll();
        printPart();


        FileInputStream fis=new FileInputStream(pn.getPathnameOne());
        XSSFWorkbook workbook = new XSSFWorkbook();
        //输入编号打印零件名称和零件规格
        Workbook sheets1=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets1.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        FileOutputStream fos=null;
        XSSFSheet sheet = workbook.createSheet("零件表");
        fos = new FileOutputStream(pn.getPathnameOne());
        //设置标题
        row = sheet.createRow(0);
        cell = row.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
        row.createCell(0).setCellValue("零件表信息");
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        sheet.addMergedRegion(region);
        for (int i = 0; i < one.getNumber().length; i++) {
            row = sheet.createRow(i);
            cell = row.createCell(i);
            row.createCell(0).setCellValue(one.getNumber()[i]);
            row.createCell(1).setCellValue(one.getName()[i]);
            row.createCell(2).setCellValue(one.getSize()[i]);
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        //打印输出修改后的零件表信息
        fis.close();
        sheets1.close();

    }
    public static void deletePrat(ComponentsOne one,Pathname pn) throws SQLException, IOException {
        String deleteSql="delete from part where number=?";;
        Scanner sc=new Scanner(System.in);
        System.out.println("输入删除的零件对应的零件编号:");
        String number = sc.next();
        DBUtil db=new DBUtil();
        db.getConnection();
        Object[] param=new Object[]{number};
        int c=db.executeUpdate(deleteSql,param);
        if (c!=0){
            System.out.println("从part表删除该条零件信息成功!"+"已自动更新到"+pn.getPathnameOne()+"中!");
        }
        db.closeAll();
        printPart();

        int b=0;
        for (int i = 0; i < one.number.length; i++) {
            if(number.equals(one.getNumber()[i])){
                b=i;
            }
        }
        for (int i = b; i < one.number.length-1; i++) { //1 2
            one.number[i]=one.getNumber()[i+1];
            one.name[i]=one.getName()[i+1];
            one.size[i]=one.getSize()[i+1];
            one.setNumber(one.number);
            one.setName(one.name);
            one.setSize(one.size);
        }
        //将零件信息的数组长度缩小一个长度
        one.number=Arrays.copyOf(one.number,one.number.length-1);
        one.name=Arrays.copyOf(one.name,one.name.length-1);
        one.size=Arrays.copyOf(one.size,one.size.length-1);

        //创建一个删除指定零件编号的零件表
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameOne()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件表");
        XSSFRow rowDelete=null;
        XSSFCell cellDelete=null;
        for (int i = 0; i < one.number.length+1; i++) { // 0 1 2 3
            rowDelete=sheet.createRow(i);
            cellDelete=rowDelete.createCell(i);
            if(i==0){
                rowDelete = sheet.createRow(0);
                cellDelete = rowDelete.createCell(0);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
                rowDelete.createCell(0).setCellValue("零件表信息");
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cellDelete.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                rowDelete.createCell(0).setCellValue(one.getNumber()[i-1]);
                rowDelete.createCell(1).setCellValue(one.getName()[i-1]);
                rowDelete.createCell(2).setCellValue(one.getSize()[i-1]);
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
    }
    public static void printPart() throws SQLException {
        DBUtil db=new DBUtil();
        db.getConnection();
        String sql="select * from part";
        ResultSet rs=db.executeQuery(sql,null);
        System.out.println("零件编号  零件名称  零件规格");
        while (rs.next()){
            System.out.println(rs.getString(1)+"       "+rs.getString(2)+"       "+rs.getString(3));
        }
        db.closeAll();
    }
}

操作零件进库表在数据库中对应的零件信息类

package jdbc;

import Entity.ComponentsTwo;
import Entity.Pathname;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
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 java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Scanner;



/**
 * 操作零件进库表在数据库中对应的数据类
 */
public class PartEntryDB {


    /**
     *  将零件进库表的零件信息导入数据库compoents的partentry表中
     */
    public static void insertAllPartEntry(ComponentsTwo two) throws SQLException {
        String insertSql="insert into partentry(number,amount)values(?,?)";
        DBUtil db=new DBUtil();
        db.getConnection();
        int a=0;
        for (int i = 1; i < two.number.length; i++) {
            Object[] param =new Object[]{two.getNumber()[i],two.getAmount()[i]};
            a=db.executeUpdate(insertSql,param);
        }
        if(a!=0){
            System.out.println("零件进库表.xlsx信息导入数据库partentry表成功!");
        }
        db.closeAll();
        printPartEntry();
    }


    public static void insertPartEntry(ComponentsTwo two, Pathname pn) throws SQLException, IOException {
        String insertSql = "insert into partentry(number,amount)values(?,?)";
        DBUtil db = new DBUtil();
        db.getConnection();
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要插入的零件编号:");
        String number = sc.next();
        System.out.println("请输入要插入的零件名称:");
        int amount = sc.nextInt();

        Object[] param = new Object[]{number,amount};
        int b = db.executeUpdate(insertSql, param);
        if (b != 0) {
            System.out.println("插入成功数据到partentry表成功," + "已自动更新到" + pn.getPathnameTwo() + "中!");
        }
        db.closeAll();
        printPartEntry();

        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件进库表");
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameTwo()));
        Workbook sheets=null;
        XSSFRow row=null;
        XSSFCell cell=null;
        //增加零件表每个零件信息数组的长度+1
        two.number = Arrays.copyOf(two.number,two.number.length+1);
        two.amount = Arrays.copyOf(two.amount,two.amount.length+1);

        for (int i = 0; i < two.number.length+1; i++) { //4+1=5
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if (i == 0) {
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
                row.createCell(0).setCellValue("零件进库表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
            }  else {
                //在数组尾部添加新增的零件信息
                if(i==two.number.length){
                    row.createCell(0).setCellValue(number);
                    row.createCell(1).setCellValue(amount);
                    two.number[i-1]=number;
                    two.amount[i-1]=amount;
                    two.setNumber(two.number);
                    two.setAmount(two.amount);
                }else if(i==1){
                    row.createCell(0).setCellValue(two.getNumber()[i-1]);
                    row.createCell(1).setCellValue("零件数量");
                }else{
                    row.createCell(0).setCellValue(two.getNumber()[i-1]);
                    row.createCell(1).setCellValue(two.getAmount()[i-1]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();

    }
    public static void selectPartEntry(ComponentsTwo two) throws SQLException {
        String selectSql="select number,amount from partentry where number=?";
        DBUtil db=new DBUtil();
        db.getConnection();
        System.out.println("输入想要查询的零件编号:");
        Scanner sc=new Scanner(System.in);
        String number =sc.next();
        Object[] param=new Object[]{number};
        ResultSet rs=db.executeQuery(selectSql,param);
        System.out.println("零件编号"+"   "+"零件数量");
        while (rs.next()){
            System.out.println(rs.getString(1)+"       "+rs.getInt(2));
        }
        db.closeAll();
    }

    public static void updatePartEntry(ComponentsTwo two,Pathname pn) throws SQLException, IOException {
        String updateSql="update partentry set amount=? where number=?";
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想修改的零件编号:");
        String number=sc.next();
        System.out.println("输入修改后的零件数量:");
        int amount=sc.nextInt();
        //将更新的零件信息也更新到零件想信息实体类
        for (int i = 0; i < two.number.length; i++) {
            if(number.equals(two.getNumber()[i])){
                two.number[i]=number;
                two.amount[i]=amount;
                two.setNumber(two.number);
                two.setAmount(two.amount);
            }
        }
        DBUtil db=new DBUtil();
        db.getConnection();
        Object[] param=new Object[]{amount,number};
        int c=db.executeUpdate(updateSql,param);
        if (c!=0){
            System.out.println("修改该条零件信息成功!"+"已自动更新到"+pn.getPathnameTwo()+"中");
        }
        db.closeAll();
        printPartEntry();


        FileInputStream fis=new FileInputStream(pn.getPathnameTwo());
        XSSFWorkbook workbook = new XSSFWorkbook();
        //输入编号打印零件名称和零件规格
        Workbook sheets2=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets2.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        FileOutputStream fos=null;
        XSSFSheet sheet = workbook.createSheet("零件进库表");
        fos = new FileOutputStream(pn.getPathnameTwo());
        //设置标题
        row = sheet.createRow(0);
        cell = row.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
        row.createCell(0).setCellValue("零件进库表信息");
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        sheet.addMergedRegion(region);
        for (int i = 0; i < two.getNumber().length; i++) {
            row = sheet.createRow(i+1);
            cell = row.createCell(i+1);
            if (i == 0) {
                row.createCell(0).setCellValue(two.getNumber()[0]);
                row.createCell(1).setCellValue("零件数量");
            } else {
                row.createCell(0).setCellValue(two.getNumber()[i]);
                if (number.equals(two.getNumber()[i])) {
                    two.amount[i]=amount;
                    two.setAmount(two.amount);
                    row.createCell(1).setCellValue(amount);
                }else{
                    row.createCell(1).setCellValue(two.getAmount()[i]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        fis.close();
        sheets2.close();

    }

    public static void deletePratEntry(ComponentsTwo two,Pathname pn) throws SQLException, IOException {
        String deleteSql="delete from partentry where number=?";;
        Scanner sc=new Scanner(System.in);
        System.out.println("输入删除的零件对应的零件编号:");
        String number = sc.next();
        DBUtil db=new DBUtil();
        db.getConnection();
        Object[] param=new Object[]{number};
        int c=db.executeUpdate(deleteSql,param);
        if (c!=0){
            System.out.println("删除该条零件信息成功!"+"已自动更新到"+pn.getPathnameTwo()+"中!");
        }
        db.closeAll();
        printPartEntry();


        int b=0;
        for (int i = 0; i < two.number.length; i++) {
            if(number.equals(two.getNumber()[i])){
                b=i;
            }
        }
        for (int i = b; i < two.number.length-1; i++) { //1 2
            two.number[i]=two.getNumber()[i+1];
            two.amount[i]=two.getAmount()[i+1];
            two.setNumber(two.number);
            two.setAmount(two.amount);
        }
        //将零件信息的数组长度缩小一个长度
        two.number=Arrays.copyOf(two.number,two.number.length-1);
        two.amount=Arrays.copyOf(two.amount,two.amount.length-1);

        //创建一个删除指定零件编号的零件进库表
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameTwo()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件进库表");
        XSSFRow rowDelete=null;
        XSSFCell cellDelete=null;
        for (int i = 0; i < two.number.length+1; i++) {
            rowDelete=sheet.createRow(i);
            cellDelete=rowDelete.createCell(i);
            if(i==0){
                rowDelete = sheet.createRow(0);
                cellDelete = rowDelete.createCell(0);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1);
                rowDelete.createCell(0).setCellValue("零件进库表信息");
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cellDelete.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                rowDelete.createCell(0).setCellValue(two.getNumber()[i-1]);
                if(i==1){
                    rowDelete.createCell(1).setCellValue("零件数量");
                }else {
                    rowDelete.createCell(1).setCellValue(two.getAmount()[i-1]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
    }

    public static void printPartEntry() throws SQLException {
        DBUtil db=new DBUtil();
        db.getConnection();
        String sql="select * from partentry";
        ResultSet rs=db.executeQuery(sql,null);
        System.out.println("零件编号  零件数量");
        while (rs.next()){
            System.out.println(rs.getString(1)+"       "+rs.getInt(2));
        }
        db.closeAll();
    }
}

操作零件汇总表在数据库中对应的零件信息类

package jdbc;

import Entity.ComponentsSum;
import Entity.Pathname;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
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 java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Scanner;

class G implements Comparable<G>{ //实现comparable接口,复写comparato方法
    String number;
    String name;
    String size;
    int amount;
    @Override
    public int compareTo(G g) {
        if (this.number.compareTo(g.number) <0 )
            return 1;
        else
            return -1;
    }
}
/**
 * 操作零件汇总表在数据库中对应的数据类
 */
public class PartSumDB {


    public static void insertAllPartSum(ComponentsSum three) throws SQLException {
        String insertSql="insert into partsum(number,name,size,amount)values(?,?,?,?)";
        DBUtil db=new DBUtil();
        db.getConnection();
        int a=0;
        for (int i = 1; i < three.number.length; i++) {
            Object[] param =new Object[]{three.getNumber()[i],three.getName()[i],three.getSize()[i],three.getAmount()[i]};
            a=db.executeUpdate(insertSql,param);
        }
        if(a!=0){
            System.out.println("零件汇总表.xlsx信息导入数据库partsum表成功!");
        }
        db.closeAll();
        printPartSum();
    }

    public static void insertPartSum(ComponentsSum three, Pathname pn) throws IOException, SQLException {
        String insertSql = "insert into partsum(number,name,size,amount)values(?,?,?,?)";
        DBUtil db = new DBUtil();
        db.getConnection();
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要插入的零件编号:");
        String number = sc.next();
        System.out.println("请输入要插入的零件名称:");
        String name = sc.next();
        System.out.println("请输入要插入的零件规格:");
        String size = sc.next();
        System.out.println("请输入要插入的零件数量:");
        int amount =sc.nextInt();


        XSSFWorkbook workbook=new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件汇总表");
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameSum()));
        Workbook sheets=null;
        XSSFRow row=null;
        XSSFCell cell=null;
        //增加零件表每个零件信息数组的长度+1
        three.number = Arrays.copyOf(three.number,three.number.length+1);
        three.name = Arrays.copyOf(three.name,three.name.length+1);
        three.size = Arrays.copyOf(three.size,three.size.length+1);
        three.amount = Arrays.copyOf(three.amount,three.amount.length+1);
        //将汇总表的零件信息全部存储在数组g里面
        G g [] = new G[three.number.length-1];
        for (int i = 0; i < g.length; i++) {   //g.length==6
            g[i] = new G();
            g[i].number= three.getNumber()[i+1];
            g[i].name = three.getName()[i+1];
            g[i].size= three.getSize()[i+1];
            g[i].amount = three.getAmount()[i+1];
            if(i==(g.length-1)){
                g[i].number = number;
                g[i].name = name;
                g[i].size = size;
                g[i].amount = amount;
            }
        }
        //从大到小对编号进行排序
        Arrays.sort(g);
        //想得到从小到大的编号,所以倒置循环一下,将零件信息又存储在three对象的成员变量数组中
        int e=1;
        for (int i = g.length; i >= 1; i--) {
            three.number[e]=g[i-1].number;
            three.name[e]=g[i-1].name;
            three.size[e]=g[i-1].size;
            three.amount[e]=g[i-1].amount;
            ++e;
        }
        for (int i = 0; i < three.number.length+1; i++) {
            row = sheet.createRow(i);
            cell = row.createCell(i);
            if (i == 0) {
                //设置单元格范围地址,参数分别表示为(起始行号,终止行号,起始列号,终止列号)
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
                row.createCell(0).setCellValue("零件汇总表信息");
                //设置水平居中
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(style);
                sheet.addMergedRegion(region);
                three.number[0]="零件编号";
                three.name[0]="零件名称";
                three.size[0]="零件规格";
                three.amount[0]=0;
            }  else {
                three.setNumber(three.number);
                three.setName(three.name);
                three.setSize(three.size);
                three.setAmount(three.amount);
                //在数组尾部添加新增的零件信息
                row.createCell(0).setCellValue(three.getNumber()[i-1]);
                row.createCell(1).setCellValue(three.getName()[i-1]);
                row.createCell(2).setCellValue(three.getSize()[i-1]);
                if(i==1){
                    row.createCell(3).setCellValue("零件数量");
                }else{
                    row.createCell(3).setCellValue(three.getAmount()[i-1]);
                }
            }
        }
        System.out.println("添加成功,已存入" + pn.getPathnameSum() + "目录中!");
        workbook.write(fos);
        workbook.close();
        fos.close();

        String sql="delete from partsum";
        db.executeUpdate(sql,null);
        int a=0;
        for (int i = 1; i < three.number.length; i++) {
            Object[] param =new Object[]{three.getNumber()[i],three.getName()[i],three.getSize()[i],three.getAmount()[i]};
            a=db.executeUpdate(insertSql,param);
        }
        if (a!=0){
            System.out.println("插入该条数据成功!按编号自动排序+"+",已自动更新到"+pn.getPathnameSum()+"中");
        }
        db.closeAll();
        printPartSum();

    }


    public static void selectPartSum() throws SQLException {
        String selectSql="select number,name,size,amount from partsum where number=?";
        DBUtil db=new DBUtil();
        db.getConnection();
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想查询的零件编号:");
        String number=sc.next();
        Object[] param=new Object[]{number};
        ResultSet rs=db.executeQuery(selectSql,param);
        while (rs.next()){
            System.out.println(rs.getString(1)+"       "+rs.getString(2)+"       "+rs.getString(3)+"       "+rs.getInt(4));
        }
        db.closeAll();

    }
    public static void updatePartSum(ComponentsSum three,Pathname pn) throws SQLException, IOException {
        String updateSql="update partsum set name=?,size=?,amount=? where number=?";
        Scanner sc=new Scanner(System.in);
        System.out.println("输入你想修改零件对应的编号:");
        String number=sc.next();
        System.out.println("输入修改后的零件名称:");
        String name=sc.next();
        System.out.println("输入修改后的零件规格:");
        String size=sc.next();
        System.out.println("输入修改后的零件数量:");
        int amount = sc.nextInt();

        DBUtil db=new DBUtil();
        db.getConnection();
        Object[] param=new Object[]{name,size,amount,number};
        int b=db.executeUpdate(updateSql,param);
        if (b!=0){
            System.out.println("从partsum表修改该条零件信息成功!"+"已自动更新到"+pn.getPathnameSum()+"中!");
        }
        db.closeAll();
        printPartSum();


        FileInputStream fis=new FileInputStream(pn.getPathnameSum());
        XSSFWorkbook workbook = new XSSFWorkbook();
        Workbook sheets2=new XSSFWorkbook(fis);
        Sheet sheet1 = sheets2.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        FileOutputStream fos=null;
        XSSFSheet sheet = workbook.createSheet("零件汇总表");
        fos = new FileOutputStream(pn.getPathnameSum());
        //设置标题
        row = sheet.createRow(0);
        cell = row.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
        row.createCell(0).setCellValue("零件汇总表信息");
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
        sheet.addMergedRegion(region);
        //设置表头和表体
        for (int i = 0; i < three.getNumber().length; i++) {
            row = sheet.createRow(i+1);
            cell = row.createCell(i+1);
            if (i == 0) {
                row.createCell(0).setCellValue(three.getNumber()[0]);
                row.createCell(1).setCellValue(three.getName()[0]);
                row.createCell(2).setCellValue(three.getSize()[0]);
                row.createCell(3).setCellValue("零件数量");
            } else {
                row.createCell(0).setCellValue(three.getNumber()[i]);
                if (number.equals(three.getNumber()[i])) {
                    three.name[i]=name;
                    three.size[i]=size;
                    three.amount[i]=amount;
                    three.setName(three.name);
                    three.setSize(three.size);
                    three.setAmount(three.amount);
                    row.createCell(1).setCellValue(name);
                    row.createCell(2).setCellValue(size);
                    row.createCell(3).setCellValue(amount);
                }else{
                    row.createCell(1).setCellValue(three.getName()[i]);
                    row.createCell(2).setCellValue(three.getSize()[i]);
                    row.createCell(3).setCellValue(three.getAmount()[i]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();
        fis.close();
        sheets2.close();

    }
    public static void deletePratSum(ComponentsSum three,Pathname pn) throws SQLException, IOException {
        String deleteSql="delete from partsum where number=?";
        Scanner sc=new Scanner(System.in);
        System.out.println("输入删除的零件对应的零件编号:");
        String number = sc.next();
        DBUtil db=new DBUtil();
        db.getConnection();
        Object[] param=new Object[]{number};
        int c=db.executeUpdate(deleteSql,param);
        if (c!=0){
            System.out.println("从partsum表删除该条零件信息成功!"+"已自动更新到"+pn.getPathnameSum()+"中!");
        }
        db.closeAll();
        printPartSum();

        int b=0;
        for (int i = 0; i < three.number.length; i++) {
            if(number.equals(three.getNumber()[i])){
                b=i;
            }
        }
        for (int i = b; i < three.number.length-1; i++) { //1 2
            three.number[i]=three.getNumber()[i+1];
            three.name[i]=three.getName()[i+1];
            three.size[i]=three.getSize()[i+1];
            three.amount[i]=three.getAmount()[i+1];
            three.setNumber(three.number);
            three.setName(three.name);
            three.setSize(three.size);
            three.setAmount(three.amount);
        }
        //将零件信息的数组长度缩小一个长度
        three.number=Arrays.copyOf(three.number,three.number.length-1);
        three.name=Arrays.copyOf(three.name,three.name.length-1);
        three.size=Arrays.copyOf(three.size,three.size.length-1);
        three.amount=Arrays.copyOf(three.amount,three.amount.length-1);
        //创建一个删除指定零件编号的零件表
        FileOutputStream fos=new FileOutputStream(new File(pn.getPathnameSum()));
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet("零件汇总表");
        XSSFRow rowDelete=null;
        XSSFCell cellDelete=null;
        for (int i = 0; i < three.number.length+1; i++) {
            rowDelete=sheet.createRow(i);
            cellDelete=rowDelete.createCell(i);
            if(i==0){
                rowDelete = sheet.createRow(0);
                cellDelete = rowDelete.createCell(0);
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
                rowDelete.createCell(0).setCellValue("零件汇总表信息");
                CellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);
                cellDelete.setCellStyle(style);
                sheet.addMergedRegion(region);
            }else{
                rowDelete.createCell(0).setCellValue(three.getNumber()[i-1]);
                rowDelete.createCell(1).setCellValue(three.getName()[i-1]);
                rowDelete.createCell(2).setCellValue(three.getSize()[i-1]);
                if(i==1){
                    rowDelete.createCell(3).setCellValue("零件数量");
                }else {
                    rowDelete.createCell(3).setCellValue(three.getAmount()[i-1]);
                }
            }
        }
        workbook.write(fos);
        workbook.close();
        fos.close();

    }
    public static void printPartSum() throws SQLException {
        DBUtil db=new DBUtil();
        db.getConnection();
        String sql="select * from partsum";
        ResultSet rs=db.executeQuery(sql,null);
        System.out.println("零件编号  零件名称  零件规格  零件数量");
        while (rs.next()){
            System.out.println(rs.getString(1)+"       "+rs.getString(2)+"       "+rs.getString(3)+"       "+rs.getInt(4));
        }
        db.closeAll();
    }
}

Poi和JDBC以及生成汇总表功能选择类

零件汇总表的生成选择类

package Choose;


import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import Generate.XlsxOne;
import Generate.XlsxSum;
import Generate.XlsxTwo;

import java.io.IOException;
import java.util.Scanner;

/**
 * 零件表,零件进库表,零件汇总表的生成方式选择类
 */
public class ChooseGenerate {
    static {
        System.out.println("------------------------");
        System.out.println("|  欢迎进入零件库房系统!   |");
        System.out.println("------------------------");
    }
    public static void generateSum(ComponentsOne one, ComponentsTwo two, ComponentsSum three , Pathname pn) throws IOException {
        Scanner sc=new Scanner(System.in);
        int mark=0;
        while (mark!=1){
            System.out.println("输入你想生成零件汇总表的方式代号:");
            System.out.println("1.通过创建零件表和零件进库表生成  2.通过已经准备好的零件汇总表导入  3.生成完毕,退出生成功能");
            int choose=sc.nextInt();
            if(choose==1){
                creatComponents(one,pn);
                creatComponentsEntry(two,pn);
                XlsxSum.creatSumXlsx1(three,pn);
                System.out.println("请输入下一步操作代号:");
            }else if(choose==2){
                XlsxSum.creatSumXlsx2(three,pn);
                System.out.println("为了后续的功能请导入零件表和零件进库表");
                XlsxOne.creatXlsx2(one,pn);
                XlsxTwo.creatXlsx2(two,pn);
                System.out.println("请输入下一步操作代号:");
            }else if(choose==3){
                System.out.println("已退出生成零件汇总表功能!");
                mark=1;
            }else {
                System.out.println("输入生成方式代号错误,请重新输入:");
            }
        }
    }


    public static void creatComponents(ComponentsOne one, Pathname pn){
        Scanner sc=new Scanner(System.in);
        int mark=0;
        while (mark!=1){
            System.out.println("输入你想生成零件表的方式代号:");
            System.out.println("1.手动输入零件表信息  2.从外界导入零件表信息 ");
            int choose=sc.nextInt();
            if(choose==1){
                XlsxOne.creatXlsx1(one,pn);
                mark=1;
            }else if(choose==2){
                XlsxOne.creatXlsx2(one,pn);
                mark=1;
            }else{
                System.out.println("输入生成代号错误,请重新输入!");
            }
        }
    }
    public static void creatComponentsEntry(ComponentsTwo two,Pathname pn){
        Scanner sc=new Scanner(System.in);
        int mark=0;
        while (mark!=1){
            System.out.println("输入你想生成零件进库表的方式代号:");
            System.out.println("1.手动输入零件进库表信息  2.从外界导入零件进库表信息");
            int choose=sc.nextInt();
            if (choose==1){
                XlsxTwo.creatXlsx1(two, pn);
                mark=1;
            }else if(choose==2){
                XlsxTwo.creatXlsx2(two, pn);
                mark=1;
            }else {
                System.out.println("输入生成方式代号错误,请重新输入:");
            }
        }
    }
}

Poi增删改查功能选择类

package Choose;


import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import Function.XlsxAdd;
import Function.XlsxChange;
import Function.XlsxDelete;
import Function.XlsxSelect;

import java.io.IOException;
import java.util.Scanner;

/**
 * Poi技术增删改查功能选择类
 */
public class ChooseFunciton {

    static {
        System.out.println("------------------------");
        System.out.println("| 欢迎进入Poi增删改查功能! |");
        System.out.println("------------------------");
    }
    public static void Merit(ComponentsOne one, ComponentsTwo two, ComponentsSum three, Pathname pn) throws IOException {
        System.out.println("已进入Poi操作零件信息功能!");
        Scanner sc=new Scanner(System.in);
        System.out.println("请选择你想进行的功能代号:");
        int mark=0;
        while (mark!=1){
            System.out.println("1.零件表 2.零件进库表 3.零件汇总表 4.退出Poi操作零件信息功能");
            int choose=sc.nextInt();
            if(choose==1){
                XlsxOneMerite(one, pn);
            }else if(choose==2){
                XlsxTwoMerite(two, pn);
            }else if(choose==3){
                XlsxSumMerite(three, pn);
            }else if(choose==4){
                mark=1;
            }else {
                System.out.println("输入代号错误,请重新输入:");
            }
        }
    }

    public static void XlsxOneMerite (ComponentsOne one,Pathname pn) throws IOException {
        int mark=0;
        while (mark!=1){
        System.out.println("1.插入单条信息 2.删除信息 3.修改信息 4.查询信息 5.退出操作零件表");
        Scanner sc=new Scanner(System.in);
        int choose=sc.nextInt();
            if(choose==1){
                XlsxAdd.addXlsxOne(one,pn);
            }else if (choose==2){
                XlsxDelete.deleteXlsxOne(one,pn);
            }else if(choose==3){
                XlsxChange.changeXlsxOne(one, pn);
            }else if (choose==4){
                XlsxSelect.selectXlsxOne(one, pn);
            } else if (choose == 5) {
                mark=1;
            }else {
                System.out.println("输入错误,请重新输入:");
            }
        }
    }
    public static void XlsxTwoMerite(ComponentsTwo two,Pathname pn) throws IOException {
        int mark=0;
        while (mark!=1){
        System.out.println("1.插入单条信息 2.删除信息 3.修改信息 4.查询信息 5.退出操作零件进库表");
        Scanner sc=new Scanner(System.in);
        int choose=sc.nextInt();      
            if(choose==1){
                XlsxAdd.addXlsxTwo(two, pn);
            }else if (choose==2){
                XlsxDelete.deleteXlsxTwo(two, pn);
            }else if(choose==3){
                XlsxChange.changeXlsxTwo(two, pn);
            }else if (choose==4){
                XlsxSelect.selectXlsxTwo(two, pn);
            }else if (choose==5){
                mark=1;
            }else {
                System.out.println("输入错误,请重新输入:");
            }
        }
    }
    public static void XlsxSumMerite(ComponentsSum three,Pathname pn) throws IOException {
        int mark=0;
        while (mark!=1){
        System.out.println("1.插入单条信息 2.删除信息 3.修改信息 4.查询信息 5.退出操作零件汇总表");
        Scanner sc=new Scanner(System.in);
        int choose=sc.nextInt();
            if(choose==1){
                XlsxAdd.addXlsxThree(three, pn);
            }else if (choose==2){
                XlsxDelete.deleteXlsxSum(three, pn);
            }else if(choose==3){
                XlsxChange.changeXlsxSum(three, pn);
            }else if (choose==4){
                XlsxSelect.selectXlsxSum(three, pn);
            }else if (choose==5){
                mark=1;
            }else {
                System.out.println("输入错误,请重新输入:");
            }
        }
    }
}

JDBC操作三个表的零件信息功能选择类

package Choose;

import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;
import jdbc.PartDB;
import jdbc.PartEntryDB;
import jdbc.PartSumDB;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * JDBC操作三个表的零件信息功能选择类
 * 事先请建立一个compoents数据库
 * 在compoents数据库中建立三个表:
 * 1.part表
 *     字段为: number(varchar) name(varchar) size(varchar)
 * 2.partentry表
 *     字段为: number(varchar) amount(int)
 * 3.partsum表
 *     字段为: number(varchar) name(varchar) size(varchar) amount(int)
 */
public class ChooseDataBase {
static {
    System.out.println("--------------------------");
    System.out.println("| 欢迎进入数据库增删改查功能! |");
    System.out.println("--------------------------");
}

    public static void dataBase(ComponentsOne one, ComponentsTwo two, ComponentsSum three, Pathname pn) throws IOException, SQLException {
        System.out.println("已进入数据库操作表信息功能!");
        System.out.println("输入你想操作的表:");
        int mark=0;
        while (mark!=1){
            System.out.println("1.零件表  2.零件进库表 3.零件汇总表 4.退出数据库操作功能");
            Scanner sc=new Scanner(System.in);
            int choose=sc.nextInt();
            if (choose==1){
                functionPartDB(one,pn);
            }else if (choose==2){
                functionPartEntryDB(two, pn);
            }else if (choose==3){
                functionPartSumDB(three, pn);
            }else if (choose==4){
                mark=1;
            }else {
                System.out.println("输入操作代号错误,请重新输入:");
            }
        }
    }

    //JDBC操作零件表part的方法
    public static void functionPartDB(ComponentsOne one, Pathname pn) throws SQLException, IOException {
        System.out.println("已连接到compoents数据库,请输入操作part表的代号:");
        int mark=0;
        while (mark!=1){
            System.out.println("1.将零件表.xlsx信息导入到part表  2.插入单条信息 3.查询信息 4.修改信息 5.删除信息 6.退出");
            Scanner sc=new Scanner(System.in);
            int choose=sc.nextInt();
            if (choose==1){
                PartDB.insertAllPart(one);
            }else if (choose==2){
                PartDB.insertPart(one,pn);
            }else if (choose==3){
                PartDB.selectPart(one);
            }else if (choose==4){
                PartDB.updatePart(one,pn);
            }else if (choose==5){
                PartDB.deletePrat(one, pn);
            }else if (choose==6){
                mark=1;
            }else {
                System.out.println("输入代号错误,请重新输入!");
            }
        }
    }

    //JDBC操作零件进库表partentry的方法
    public static void functionPartEntryDB(ComponentsTwo two, Pathname pn) throws SQLException, IOException {
        System.out.println("已连接到compoents数据库,请输入操作partentry表的代号:");
        int mark=0;
        while (mark!=1){
            System.out.println("1.将零件进库表.xlsx信息导入到partentry表  2.插入单条信息 3.查询信息 4.修改信息 5.删除信息 6.退出");
            Scanner sc=new Scanner(System.in);
            int choose=sc.nextInt();
            if (choose==1){
                PartEntryDB.insertAllPartEntry(two);
            }else if (choose==2){
                PartEntryDB.insertPartEntry(two,pn);
            }else if (choose==3){
                PartEntryDB.selectPartEntry(two);
            }else if (choose==4){
                PartEntryDB.updatePartEntry(two, pn);
            }else if (choose==5){
                PartEntryDB.deletePratEntry(two, pn);
            } else if (choose==6){
                mark=1;
            }else {
                System.out.println("输入代号错误,请重新输入!");
            }
        }
    }

    //JDBC操作零件汇总表partsum的方法
    public static void functionPartSumDB(ComponentsSum three, Pathname pn) throws SQLException, IOException {
        System.out.println("已连接到compoents数据库,请输入操作partsum表的代号:");
        int mark=0;
        while (mark!=1){
            System.out.println("1.将零件汇总表.xlsx信息导入到partsum表  2.插入单条信息 3.查询信息 4.修改信息 5.删除信息 6.退出");
            Scanner sc=new Scanner(System.in);
            int choose=sc.nextInt();
            if (choose==1){
                PartSumDB.insertAllPartSum(three);
            }else if (choose==2){
                PartSumDB.insertPartSum(three, pn);
            }else if (choose==3){
                PartSumDB.selectPartSum();
            }else if (choose==4){
                PartSumDB.updatePartSum(three, pn);
            }else if (choose==5){
                PartSumDB.deletePratSum(three, pn);
            }else if (choose==6){
                mark=1;
            }else {
                System.out.println("输入代号错误,请重新输入!");
            }
        }
    }
}

测试类(主类)

package Main;

import Choose.ChooseDataBase;
import Choose.ChooseFunciton;
import Choose.ChooseGenerate;
import Entity.ComponentsOne;
import Entity.ComponentsSum;
import Entity.ComponentsTwo;
import Entity.Pathname;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 零件库房系统主类
 */
public class ComponentsDemo {
    public static void main(String[] args) throws IOException, SQLException {
        //创建零件表信息类对象
        ComponentsOne cp1=new ComponentsOne();
        //创建零件进库表信息类对象
        ComponentsTwo cp2=new ComponentsTwo();
        //创建零件表汇总表类对象
        ComponentsSum cp3=new ComponentsSum();
        //创建三个表文件路径对象
        Pathname pn=new Pathname();
        //生成汇总表
        ChooseGenerate.generateSum(cp1,cp2,cp3,pn);
        /*
            1.利用poi技术增删改查功能
            2.利用JDBC将三个表零件信息导入数据库对应的表中,操作数据库同时将零件信息写入到对应的文件中
         */
        choose(cp1,cp2,cp3,pn);
    }

    public static void choose(ComponentsOne one,ComponentsTwo two,ComponentsSum three,Pathname pn) throws IOException, SQLException {
        int mark=0;
        while (mark!=1){
            System.out.println("1.Poi技术操作三个表的零件信息  2.数据库JDBC驱动操作三个表的零件信息  3.退出零件库房系统");
            Scanner sc=new Scanner(System.in);
            int choose=sc.nextInt();
            if (choose==1){
                //利用poi技术增删改查功能
                ChooseFunciton.Merit(one,two,three,pn);
            }else if(choose==2){
                //利用JDBC将三个表零件信息导入数据库对应的表中,操作数据库同时将零件信息写入到对应的文件中
                ChooseDataBase.dataBase(one, two, three, pn);
            }else if(choose==3){
                System.out.println("已退出零件库房系统");
                mark=1;
            }else {
                System.out.println("输入代号错误,请重新输入:");
            }
        }
    }
}

微信扫下方二维码下载项目源代码：

有问题欢迎与我交流
本人QQ:1015447235