将Excel表格多个Sheet表的数据转换为SQL存储语句

将Excel表格多个Sheet表的数据转换为SQL存储语句
如：将

转换为：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Excel2Sql {
    private static final String FILE_PATH = "E:\\data\\test.xls";
    private static String WRITE_FILE_PATH = "E:\\data\\sql\\";
    private static POIFSFileSystem fs;
    //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls 
    //XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
    private static HSSFWorkbook wb;
    private static HSSFSheet sheet;
    private static HSSFRow row;

    public static void main(String[] args) throws FileNotFoundException {
        InputStream is = new FileInputStream(FILE_PATH);
        readExcel(is);
    }

    public static void readExcel(InputStream is) {
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
             System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }
        System.out.println("--- start ---");
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            sheet = wb.getSheetAt(i);
            String sheetName = sheet.getSheetName();

            if (sheetName.equals("品牌")) {
                String table = "mt_brand";
                sheet = wb.getSheetAt(i);
                connectSql(table);
            } else if (sheetName.equals("系列")) {
                String table = "mt_series";
                sheet = wb.getSheetAt(i);
                connectSql(table);
            } else if (sheetName.equals("模型")) {
                String table = "mt_model";
                sheet = wb.getSheetAt(i);
                connectSql(table);
            } else if (sheetName.equals("省市")) {
                String table = "mt_city";
                sheet = wb.getSheetAt(i);
                connectSql(table);
            } else {
                System.out.println("不需要对  \"" + sheetName + "\" 表进行处理!");
            }
        }
        System.out.println("--- process end ---");
    }

    private static void connectSql(String table) {
        //处理表头数据
        String[] titleResult = readExcelTitle(sheet);
        String titleSb = connectTitle(titleResult, table);

        //生成sql
        String createTableSql = connectCreatTable(titleResult, table);
        createTableSql = createTableSql.replace(",\r\n)", "\r\n)");

      //处理表内容数据
        String contentResult = readExcelContent(sheet);
        String[] contents = contentResult.split("---");
        List<Map<Integer, String>> contentList = new ArrayList<>();
        int count = contents.length;
        for (int j = 0; j < count; j++) {
            Map<Integer, String> map = new LinkedHashMap<>();
            String values = "\r\nVALUES \r\n";
            StringBuilder insertSb = new StringBuilder();
            insertSb.append(titleSb);
            insertSb.append(values);
            insertSb.append(contents[j]);
            String insert = insertSb.toString();
            insert = insert.substring(0, insert.lastIndexOf(",")) + ";";
            map.put(j, insert);
            contentList.add(map);
        }
        writeStringToFile(table,createTableSql, contentList, count);
    }

    private static void writeStringToFile(String table, String createTableSql, List<Map<Integer, String>> contentList,
            int count) {
        try {
            String filePath = WRITE_FILE_PATH + table + ".sql";
            File file = new File(filePath);
            PrintStream ps = new PrintStream(new FileOutputStream(file));
            ps.println(createTableSql);// 往文件里写入字符串
            String otherSql = null;
            for (int i = 0; i < contentList.size(); i++) {
                Map<Integer, String> map = contentList.get(i);
                otherSql = map.get(i);
                ps.append(otherSql + "\r\n\r\n");// 在已有的基础上添加字符串
            }
            System.out.println("Has been written to " + filePath);
            ps.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static String connectCreatTable(String[] title, String table) {
        String start = "CREATE TABLE " + table + "(\r\n";
        String end = ") ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n";
        StringBuilder sb = new StringBuilder();
        sb.append(start);
        for (String filed : title) {
            if (filed.contains("id") || filed.contains("_year")) {
                sb.append(filed);
                sb.append(" int(11) NOT NULL,\r\n");
            } else if (filed.contains("name")) {
                sb.append(filed);
                sb.append(" varchar(100) NOT NULL,\r\n");
            } else {
                sb.append(filed);
                sb.append(" varchar(50) NOT NULL,\r\n");
            }
        }
        sb.append(end);
        return sb.toString();
    }

    private static String readExcelContent(HSSFSheet sheetData) {
           Map<Integer, String> content = new HashMap<Integer, String>();
            String data = "";
            int rowNum = sheetData.getLastRowNum();// 得到总行数
            row = sheetData.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            for (int i = 1; i <= rowNum; i++) {// 正文内容应该从第二行开始,第一行为表头的标题
                row = sheetData.getRow(i);
                data +="(";
                int j = 0;
                while (j < colNum) {
                    if (j != colNum - 1) {
                        String value = ExcelReaderUtil.getCellFormatValue(row.getCell(j)).trim();
                        String str = null;
                        if (value.endsWith(".0")) {
                            str = value.replace(".0", "");
                        } else {
                            str = value;
                        }
                        if (isInteger(str)) {
                            data += str;
                            data +=",";
                        } else {
                            data += "'" + str + "'";
                            data +=",";
                        }
                    } else {
                        data += "'" + ExcelReaderUtil.getCellFormatValue(row.getCell(j)).trim() + "'";
                        data +=")";
                    }
                    j++;
                }
                data +=",";
                content.put(i, data);//str = ('1.0','奥迪','A'),('2.0','阿斯顿·马丁','A'),
                data = "";
            }

            StringBuilder sb = new StringBuilder();
            int mapCount = content.size();
            for (int i = 1; i < mapCount + 1; i++) {
                String value = content.get(i);
                if (i % 500 != 0) {
                    sb.append(value + "\r\n");
                } else {
                    sb.append(value + "\r\n");
                    sb.append("---");
                }
            }

            return sb.toString();
    }

    private static String connectTitle(String[] title, String table) {
        String insertSql = "INSERT INTO " + table + "(\r\n" ;
        StringBuilder sb = new StringBuilder();

        int titleCount = title.length;
        int i = 1;
        sb.append(insertSql);
        for (String s : title) {
            if (i != titleCount) {
                sb.append(s);
                sb.append(",\r\n");
            } else {
                sb.append(s);
                sb.append(")");
            }
            i++;
        }
        return sb.toString();
    }

    private static String[] readExcelTitle(HSSFSheet sheetData) {
        row = sheetData.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        String[] title = new String[colNum];
        for (int k = 0; k < colNum; k++) {
         title[k] = ExcelReaderUtil.getCellFormatValue(row.getCell(k));
        }
        return title;
    }

    /***
     * 判断是否是 int
     * @param input
     */
    public static boolean isInteger(String input){
        Matcher mer = Pattern.compile("^[+-]?[0-9]+$").matcher(input);
        return mer.find();
    }

}

ExcelReaderUtil：

import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;

public class ExcelReaderUtil {
    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    static String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                }
                else {
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            case HSSFCell.CELL_TYPE_STRING:
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

}