Execl生成 insert Sql自用小工具

package com.his.tree.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.*;

public class ExcelRead extends JFrame {
    private JTextField sqltxtField;
    private JTextField execlField;
    private File selectFile;
    String tabSql;

    public Workbook createExcelWorkbook(String filepath) {
        return createWorkbook(filepath);
    }

    public Workbook createWorkbook(String filepath) {
        try {
            if (filepath.endsWith(".xls")) {
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(filepath));
                return new HSSFWorkbook(poifsFileSystem);
            } else if (filepath.endsWith(".xlsx")) {
                return new XSSFWorkbook(filepath);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, filepath + "失败！");
        }
        return null;
    }

    protected Cell getCell(Sheet sheet, int row, int col) {
        Row cellRow = sheet.getRow(row);
        return cellRow == null ? null : cellRow.getCell(col);
    }

    protected String getCellString(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) {
            return getFormvlaValue(cell);
        }
        return getFormvlaValue(cell);
    }

    private String getFormvlaValue(Cell cell) {
        String cellValue = "";
        try {
            cellValue = String.valueOf(cell.getNumericCellValue());
        } catch (Exception e) {
            cellValue = String.valueOf(cell.getRichStringCellValue());
        }
        if (NumberUtils.isNumber(cellValue)) {
            return returnNumric(cell, cellValue);
        } else {
            return StringUtils.trimToEmpty(cell.getStringCellValue());
        }
    }

    protected String returnNumric(Cell cell, String stringvalues) {
        if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
            if (stringvalues.contains(".")) {
                String string = stringvalues.substring(stringvalues.indexOf(".") + 1, stringvalues.length());
                if (NumberUtils.createLong(string) == 0) {
                    string = stringvalues.substring(0, stringvalues.indexOf("."));
                    cell.setCellValue(string);
                    return string;
                }
            }
        }
        return stringvalues;
    }

    protected String getCellContent(Sheet sheet, int row, int col) {
        Cell cell = getCell(sheet, row, col);
        return getCellString(cell);
    }

    private void showView() {
        setDefaultCloseOperation(DISPOSE_ON_CLOSE);
        setResizable(false);
        setTitle("内部insert Sql生成，Swing整理");
        setBounds(getRectangle(413, 175));
        getContentPane().setLayout(null);
        execlField = new JTextField();
        execlField.setBounds(25, 31, 220, 22);
        getContentPane().add(execlField);

        final JButton excelBut = new JButton();
        excelBut.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                JFileChooser jf = new JFileChooser();
                jf.setFileFilter(new FileFilter() {
                    @Override
                    public boolean accept(File f) {
                        if (f.isDirectory()) {
                            return true;
                        }
                        if (f.getName().toLowerCase().endsWith(".xlsx") || f.getName().toLowerCase().endsWith(".xls")) {
                            return true;
                        }
                        return false;
                    }

                    @Override
                    public String getDescription() {
                        return "*.xlsx/*.xlsx";
                    }
                });
                jf.showOpenDialog(null);
                selectFile = jf.getSelectedFile();
                if (selectFile != null) {
                    String selectPath = selectFile.getPath();
                    execlField.setText(selectPath);
                    tabSql = selectFile.getName();
                    tabSql = tabSql.substring(0, tabSql.indexOf("."));
                    sqltxtField.setText(selectPath.substring(0, selectPath.lastIndexOf(".")).concat(".txt"));
                }
            }
        });

        excelBut.setText("选择EXCEL");
        excelBut.setBounds(264, 31, 106, 22);
        getContentPane().add(excelBut);

        sqltxtField = new JTextField();
        sqltxtField.setBounds(25, 59, 220, 22);
        getContentPane().add(sqltxtField);

        final JButton saveBut = new JButton();
        saveBut.setText("存放位置");
        saveBut.setBounds(264, 59, 106, 22);
        getContentPane().add(saveBut);

        final JButton outPutBut = new JButton();
        outPutBut.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                if (selectFile != null) {
                    Workbook workbook = createExcelWorkbook(selectFile.getPath());
                    SplitData(workbook);
                }
            }
        });
        outPutBut.setText("导出");
        outPutBut.setBounds(65, 100, 106, 22);
        getContentPane().add(outPutBut);

        final JButton exitBut = new JButton();
        exitBut.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                System.exit(0);
            }
        });
        exitBut.setText("关闭");
        exitBut.setBounds(218, 100, 106, 22);
        getContentPane().add(exitBut);
        Button excelButton = new Button("选择Excel");
        Button targetButton = new Button("指定存储位置");
        add(excelButton);
        add(targetButton);
        setVisible(true);
    }

    public static Rectangle getRectangle(int w, int y) {
        int ww = Toolkit.getDefaultToolkit().getScreenSize().width;
        int hh = Toolkit.getDefaultToolkit().getScreenSize().height;
        return new Rectangle((ww - w) / 2, (hh - y) / 2, w, y);
    }

    private void SplitData(Workbook workbook) {
        FileWriter file = null;
        BufferedWriter bufferedWriter = null;
        PrintWriter printWriter = null;
        try {
            file = new FileWriter(sqltxtField.getText());
            bufferedWriter = new BufferedWriter(file);
            printWriter = new PrintWriter(bufferedWriter);
            Sheet sheet = workbook.getSheetAt(0);
            StringBuilder inertString = new StringBuilder("insert into [" + tabSql + "] (");
            ParseExcelCell(printWriter, sheet, inertString);
        } catch (IOException e) {
        } finally {
            printWriter.close();
            try {
                bufferedWriter.close();
                file.close();
                selectFile = null;
            } catch (Exception e) {
            }
        }
    }

    private void ParseExcelCell(PrintWriter out, Sheet sheet, StringBuilder inertString) {
        Row row;
        Row firstRow = sheet.getRow(0);
        int col = 0;
        for (int i = firstRow.getFirstCellNum(); i < firstRow.getPhysicalNumberOfCells(); i++) {
            if (!getCellString(getCell(sheet, 0, i)).equals(null)) {
                inertString.append("[" + getCellString(getCell(sheet, 0, i)) + "],");
                ++col;
            }
        }
        inertString.delete(inertString.length() - 1, inertString.length());
        inertString.append(") values (");
        int start = inertString.length();
        for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            inertString.delete(start, inertString.length());
            for (int j = row.getFirstCellNum(); j < col; j++) {
                if (getCell(sheet, i, j).toString() == "") {
                    inertString.append("null,");
                    continue;
                } else {
                    inertString.append("'" + getCellString(getCell(sheet, i, j)) + "',");
                }
            }
            inertString.setLength(inertString.length() - 1);
            inertString.append(");");
            out.println(inertString);
            out.flush();
        }
    }

    public static void main(String[] args) {
        ExcelRead excelRead = new ExcelRead();
        excelRead.showView();
    }
}