基于jse的金蝶K3ERP-BOM物料编码位置号比对工具（输出excel）



 Long ago...之前接到硬件部的需求：要求做一个BOM比对工具，从K3的bom多级展开中拿到BOM的物料清单并把物料的的位置号取出并计数。

网上找了下K3的数据结构表，分析了下，用swing写了个小程序，实现了需求。下面将代码分享给各位看客。

技术关键词：swing,JFrame,POI;

package controller;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.FileOutputStream;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;


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;

class InputUIx extends JFrame {

 JTextField jTextField; // 定义文本框组件
 JLabel jLabel1;
 JPanel BOM_Name, jp2, jp3;
 JButton enter, cancel; // 创建按钮

 public InputUIx() {

  // File file = chooser.getSelectedFile();

  jTextField = new JTextField(12);
  jLabel1 = new JLabel("BOM编码：");
  enter = new JButton("确认");
  cancel = new JButton("退出");
  BOM_Name = new JPanel();
  jp3 = new JPanel();

  // 设置布局
  this.setLayout(new GridLayout(3, 1));

  BOM_Name.add(jLabel1);
  BOM_Name.add(jTextField);// 第一块面板添加jLabel1和文本框

  jp3.add(enter);
  jp3.add(cancel); // 第三块面板添加确认和取消

  String root = System.getProperty("user.dir");
  System.out.println(root);
  
  String strSql = "select A.FBOMNumber,B.FNote,B.FPositionNo,C.FNumber,B.FAuxQty "
    + "from ICBOM as A "
    + "left join ICBOMCHILD as B on A.FInterID = B.FInterID "
    + "left join t_Item as C on B.FItemID = C.FItemID "
    + "where A.FBOMNumber ='" + "BOMNumber" + "'";
  

  enter.addActionListener(new ActionListener() {
   public void actionPerformed(ActionEvent e) {
    String BOMNumber = jTextField.getText();
    if (jTextField.getText().equals("")) {
     JOptionPane.showMessageDialog(null, "错误：0001：BOM编号不能为空", "提示信息",
       JOptionPane.ERROR_MESSAGE);
    } else {

     int autogrow = 1;

     try {
//      String BOMNumber = jTextField.getText();
      
      System.out.println("数据库已连接! "); // 测试数据库连接


      System.out.println(strSql);

      // 新建EXCEL，以poi方式输出xlsx格式的excel
      XSSFWorkbook book = new XSSFWorkbook();
      XSSFSheet sheet = book.createSheet("BOM位置号统计表");

      XSSFRow row = sheet.createRow(0);

      XSSFCell cell = row.createCell(0);
      cell.setCellValue("BOM编号");
      cell = row.createCell(1);
      cell.setCellValue("物料编码");
      cell = row.createCell(2);
      cell.setCellValue("位置号合计");
      cell = row.createCell(3);
      cell.setCellValue("计数");
      cell = row.createCell(4);
      cell.setCellValue("原计数");
      cell = row.createCell(5);
      cell.setCellValue("对比");

      System.out.println("5.循环次数：" + autogrow);
      System.out.println("---------------------");
      //生成含BOM编号的excel文件
      String path = root + "\\" + BOMNumber + ".xlsx";

      FileOutputStream os = new FileOutputStream(path);
      System.out.println(path);
      book.write(os);

      os.close();
      // 结果集为空报错
      JOptionPane.showMessageDialog(null, "文档已生成！");
     }

     catch (Exception f) {

      f.printStackTrace();
      JOptionPane.showMessageDialog(null, "错误：0002：程序异常", "提示信息",
        JOptionPane.ERROR_MESSAGE);
     }

    }

   }
  });

  // cancel 关闭程序
  cancel.addActionListener(new ActionListener() {
   public void actionPerformed(ActionEvent e) {
    dispose();
   }
  });

  this.add(BOM_Name);
  // this.add(jp2);
  this.add(jp3); // 将三块面板添加到登陆框上面
  // 设置显示
  this.setSize(400, 150);
  // this.pack();
  this.setDefaultCloseOperation(EXIT_ON_CLOSE);
  this.setVisible(true);
  this.setTitle("BOM位置号统计工具");
  this.setLocationRelativeTo(getOwner());

 }

}