工具代码简介

import org.apache.commons.lang3.StringUtils;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.*;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.io.IOException;import java.io.InputStream;import java.util.HashMap;import java.util.Map;public class ExcelToSql {    private static final Logger logger = LoggerFactory.getLogger(ExcelToSql.class);    private static HashMap
    
      DEFAULT_COLUMN_MAP;    private static HashMap
     
       CHINESS_COLUMN_MAP;    public static final String CREATE_TABLE_PROMPT = "CREATE TABLE ";    /**     * 字段名称     */    public static final String CNAME = "CNAME";    /**     * 字段描述 注释     */    public static final String COMMENT = "COMMENT";    /**     * 字段类型     */    public static final String CTYPE = "CTYPE";    /**     * 非空     */    public static final String NOTNULL = "NOTNULL";    /**     * 默认值     */    public static final String DEFAULT = "DEFAULT";    /**     * 索引值     */    public static final String INDEX = "INDEX";    static {        DEFAULT_COLUMN_MAP = new HashMap
      
       (6);        DEFAULT_COLUMN_MAP.put(CNAME,0);        DEFAULT_COLUMN_MAP.put(COMMENT,1);        DEFAULT_COLUMN_MAP.put(CTYPE,2);        DEFAULT_COLUMN_MAP.put(NOTNULL,3);        DEFAULT_COLUMN_MAP.put(DEFAULT,4);        DEFAULT_COLUMN_MAP.put(INDEX,5);        CHINESS_COLUMN_MAP = new HashMap
       
        (6);        CHINESS_COLUMN_MAP.put("字段名称",CNAME);        CHINESS_COLUMN_MAP.put("字段描述",COMMENT);        CHINESS_COLUMN_MAP.put("字段类型",CTYPE);        CHINESS_COLUMN_MAP.put("不为空",NOTNULL);        CHINESS_COLUMN_MAP.put("缺省值",DEFAULT);        CHINESS_COLUMN_MAP.put("索引类型",INDEX);    }    public static void main(String [] args){        final InputStream is = ExcelToSql.class.getResourceAsStream("/db.xlsx");        Workbook wb = null;        try {            wb = WorkbookFactory.create(is);        } catch (IOException e) {            e.printStackTrace();        } catch (InvalidFormatException e) {            e.printStackTrace();        }//        System.out.println(wb.getNumberOfSheets());//表单sheet数量        final Sheet sheet = wb.getSheetAt(0);        excelToSql(sheet);    }    private static String excelToSql(Sheet sheet){        final String sheetName = sheet.getSheetName();        return excelToSql(sheet,sheetName);    }    private static String excelToSql(Sheet sheet,String tableName){        final int nums = sheet.getLastRowNum();        final String sheetName = sheet.getSheetName();        if(nums < 1){            logger.info(sheetName + "为空");            throw new RuntimeException("表单为空");        }        final StringBuffer sb = new StringBuffer(CREATE_TABLE_PROMPT);        final StringBuffer index = new StringBuffer();        sb.append(tableName);        sb.append("(");        final Map
        
          columnMap = getColumnMap(sheet);        for(int i=1;i
         
           getColumnMap(Sheet sheet){ final int nums = sheet.getLastRowNum(); final String sheetName = sheet.getSheetName(); if(nums <= 1){ logger.info(sheetName + "为空"); return DEFAULT_COLUMN_MAP; } final Row row = sheet.getRow(0); final int cellNums = row.getPhysicalNumberOfCells(); assert cellNums == 6; final HashMap
          
            result = new HashMap
           
            (); for(int i = 0;i
           
          
         
        
       
      
     
    

工具只处理了6个部分:字段名称,字段描述,字段类型,不为空,缺省值,索引类型

其中字段描述就是注释，不为空标志字段是否可以为空可以只处理了"YES"或"Y",缺省值就是默认值，索引类型是字段索引。自处理了"自增主键，普通索引，唯一索引"

大概的Excel如下图所示：

当然其中的字段位置和字段名称是可以替换的，CHINESS_COLUMN_MAP和getColumnMap就是为了处理这个事情，但是必须作为第一行。当然也可根据自己的需求修改一下代码。

注意：为了兼容2003及以前使用的是WorkbookFactory.create注意看下面的maven依赖

maven依赖

            
     
      org.apache.commons
             
     
      commons-lang3
             
     
      3.1
         
        
            
     
      mysql
             
     
      mysql-connector-java
             
     
      5.1.35
             
     
      jar
             
     
      compile
         
        
            
     
      org.apache.poi
             
     
      poi
             
     
      3.9
         
        
            
     
      org.apache.poi
             
     
      poi-ooxml-schemas
             
     
      3.9
         
        
            
     
      org.apache.poi
             
     
      poi-ooxml
             
     
      3.9