工具代码简介
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<String,Integer> DEFAULT_COLUMN_MAP;
private static HashMap<String,String> 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<String, Integer>(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<String, String>(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<String, Integer> columnMap = getColumnMap(sheet);
for(int i=1;i<nums;i++){
final Row row = sheet.getRow(i);
Cell cell = row.getCell(columnMap.get(CNAME));
String cname;
String column = cname = cell.getStringCellValue();
sb.append(column);
sb.append(" ");
cell = row.getCell(columnMap.get(CTYPE));
column = cell.getStringCellValue();
sb.append(column);
cell = row.getCell(columnMap.get(INDEX));
column = cell.getStringCellValue();
if(StringUtils.isNotBlank(column)){
sb.append(" ");
if("主键".equals(column)){
sb.append("PRIMARY KEY");
}else if("自增主键".equals(column)){
sb.append("PRIMARY KEY AUTO_INCREMENT");
}else if("普通索引".equals(column)){
String indexName = "idx_"+cname.toLowerCase();
index.append("create index " + indexName + " on "+ tableName + "(" + cname + ");");
index.append("\n");
}else if("唯一索引".equals(column)){
String indexName = "idx_"+cname.toLowerCase();
index.append("create unique index " + indexName + " on "+ tableName + "(" + cname + ");");
index.append("\n");
}
}
cell = row.getCell(columnMap.get(NOTNULL));
column = cell.getStringCellValue();
if("Y".equalsIgnoreCase(column) || "YES".equalsIgnoreCase(column)){
sb.append(" NOT NULL");
}
cell = row.getCell(columnMap.get(DEFAULT));
final int cellType = cell.getCellType();
double numericCellValue;
if(Cell.CELL_TYPE_NUMERIC == cellType) {
numericCellValue = cell.getNumericCellValue();
sb.append(" DEFAULT ");
sb.append(numericCellValue);
}
else {
column = cell.getStringCellValue();
if (StringUtils.isNotBlank(column)) {
sb.append(" DEFAULT ");
sb.append(column);
}
}
cell = row.getCell(columnMap.get(COMMENT));
column = cell.getStringCellValue();
if(StringUtils.isNotBlank(column)){
sb.append(" COMMENT '");
sb.append(column.trim());
sb.append("'");
}
sb.append(",");
}
sb.setCharAt(sb.length()-1,')');
sb.append(";");
sb.append("\n");
sb.append(index);
final String result = sb.toString().toUpperCase();
System.out.println(result);
return result;
}
private static Map<String,Integer> 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<String, Integer> result = new HashMap<String, Integer>();
for(int i = 0;i<cellNums;i++){
final String stringCellValue = row.getCell(i).getStringCellValue();
result.put(CHINESS_COLUMN_MAP.get(stringCellValue),i);
}
return result;
}
}
工具只处理了6个部分:字段名称,字段描述,字段类型,不为空,缺省值,索引类型
其中字段描述就是注释,不为空标志字段是否可以为空可以只处理了"YES"或"Y",缺省值就是默认值,索引类型是字段索引。自处理了"自增主键,普通索引,唯一索引"
大概的Excel如下图所示:
当然其中的字段位置和字段名称是可以替换的,CHINESS_COLUMN_MAP和getColumnMap就是为了处理这个事情,但是必须作为第一行。当然也可根据自己的需求修改一下代码。
注意:为了兼容2003及以前使用的是WorkbookFactory.create注意看下面的maven依赖
maven依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
<type>jar</type>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>