Java POI读取excel，自动生成代码

本文是介绍通过读取Excel格式的数据字典，生成sql脚本的工具类，目前涉及到的读取方法，特别是行和列的属性还是硬编码，是针对我们现有的数据字典的格式的，如果数据字典的格式发生了变动，相关代码也要做相应改动。

所用的Excel版本为2007，后缀是.xlsx，生成的sql脚本是针对Mysql数据库的

1、读取Excel的工具类

package allan.dbUtil;

import java.util.HashMap;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelUtil{

	@SuppressWarnings("static-access")
	public static String getValue(XSSFCell xssfCell) {
		if(xssfCell!=null){
			if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
				return String.valueOf(xssfCell.getBooleanCellValue());
			} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
				return String.valueOf(xssfCell.getNumericCellValue());
			} else {
				return String.valueOf(xssfCell.getStringCellValue());
			}
		}
		return "";
	}
	public static Table getTabelModel(XSSFWorkbook xssfWorkbook,int numSheet,Map<String,String> sheetMap){
		return new Table(getTableName(xssfWorkbook, numSheet), getColumns(xssfWorkbook, numSheet,sheetMap));
	}
	public static String getTableName(XSSFWorkbook xssfWorkbook,int numSheet){
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
		//获取指定单元格的值 begin
		XSSFRow xssfRow = xssfSheet.getRow(1);
		XSSFCell xssfCell1 = xssfRow.getCell(0);
		String text=getValue(xssfCell1);
		//获取指定单元格的值 end
		return text;
	}
	public static Column[] getColumns(XSSFWorkbook xssfWorkbook,int numSheet,Map<String,String> sheetMap){
		//for (int numSheet = 2; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			xssfSheet.getSheetName();
			int lastRowNum=xssfSheet.getLastRowNum();
			Column[]  columns=new Column[lastRowNum-3];
			XSSFHyperlink link=null;
			// 循环行Row,从第四行开始
			for (int rowNum = 4; rowNum <= lastRowNum; rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow == null) {
					continue;
				}
				//获得字段描述
				String columnDesc =getValue(xssfRow.getCell(0));
				//获得字段名
				String columnName = getValue(xssfRow.getCell(1));
				
				//获得字段类型
				String columnType = getValue(xssfRow.getCell(2));
				//获得字段长度
				String columnLength =getValue(xssfRow.getCell(3));
				//获得小数长度
				String decimalLength=getValue(xssfRow.getCell(4));
				//是否必填
				String isNotNull =getValue(xssfRow.getCell(5));
				//是否主键 暂时无用
				String isPrimary =getValue(xssfRow.getCell(6));
				//是否外键
				String isForeign =getValue(xssfRow.getCell(7));
				if(isForeign!=null&&!isForeign.equals("")){
					link=xssfRow.getCell(7).getHyperlink();
					String[] s=link.getLocation().split("!");
					int foreignNumSheet=Integer.parseInt(sheetMap.get(s[0]));
					XSSFSheet foreignSheet = xssfWorkbook.getSheetAt(foreignNumSheet);
					String foreignTableName=getTableName(xssfWorkbook,foreignNumSheet);
					int index=Integer.parseInt(s[1].substring(1));
					String foreignColumnName=getValue(foreignSheet.getRow(index-1).getCell(1));
					String foreignColumnType=getValue(foreignSheet.getRow(index-1).getCell(2));
					String foreignColumnLength=getValue(foreignSheet.getRow(index-1).getCell(3));
					String foreignColumnDecimalLength=getValue(foreignSheet.getRow(index-1).getCell(4));
					isForeign=foreignTableName+","+foreignColumnName+","
								+foreignColumnType+","+foreignColumnLength
								+","+foreignColumnDecimalLength;
				}
				
				columns[rowNum-4]=new Column(columnName, columnType, columnLength, 
						decimalLength,isNotNull, columnDesc, isPrimary, isForeign);
			}
		return columns;
	}
	//将sheet的名字和序号放入map中保存,用于解析外键，因其是以链接形式存在的
	public static Map<String,String> getSheetMap(XSSFWorkbook xssfWorkbook){
		Map<String,String> sheetMap=new HashMap<String,String>();
		XSSFSheet xssfSheet;
		for (int numSheet = 2; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			sheetMap.put(xssfSheet.getSheetName(), String.valueOf(numSheet));
		}
		return sheetMap;
	}
}

 

2、用于生成sql脚本的工具类

package allan.dbUtil;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.UUID;

public class WriteUtil {
	public static void generateJavaHead(String fileName){
		File f = new File("D:\\codeGeneration\\"+fileName+".java");
		StringBuffer sb=new StringBuffer("package com.amaxgs.epp.model\n");
		sb.append("public class "+fileName+"{\n");
		try {
			BufferedWriter output = new BufferedWriter(new FileWriter(f));
			output.write(sb.toString());
			output.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void generateSqlFile(Table tableModel){
		String fileName="D:\\codeGeneration\\createTable.sql";
		String tableName=tableModel.getName();
		StringBuffer sb=new StringBuffer("DROP TABLE IF EXISTS "+tableName+";\n");
		sb.append("CREATE TABLE "+tableName+" (\n");
		
		writeFile(fileName,sb.toString(),true);
		//添加字段信息
		Column[] columns=tableModel.getColumns();
		for(Column column:columns){
			String content="";
			if(column!=null){
				//如果不是外键
				if(column.getIsForeign().equals("")){
					content=column.getName()+" "+column.getType();
					if(!column.getLength().equals("")){
						content+="("+replaceZeroSuffix(column.getLength());
						if(!column.getDecimalLength().equals("")){
							content+=","+replaceZeroSuffix(column.getDecimalLength());
						}
						content+=")"+replaceNull(column.getIsNotNull())+",\n";
					}else{
						content+=",\n";
					}
				}else{
					//如果字段是外键，那么它的类型，长度等信息从外键所在表里取得
					String[] s=column.getIsForeign().split(",");
					String type=s[2];
					String length="";
					String decimalLength="";
					if(s.length==5){
						decimalLength=s[4];
						length=s[3];
					}else if(s.length==4){
						length=s[3];
					}
					if(!length.equals("")){
						content=column.getName()+" "+type
						+"("+replaceZeroSuffix(length);
						
						if(!decimalLength.equals("")){
							content+=","+replaceZeroSuffix(decimalLength);
						}
						content+=")"+replaceNull(column.getIsNotNull())+",\n";
					}else{
						content+=",\n";
					}
					
				}
				writeFile(fileName,content,true);
			}
		}
		//添加主键信息
		writeFile(fileName, "PRIMARY KEY (id)", true);
		//添加外键信息
		for(Column column:columns){
			String s=column.getIsForeign();
			if(!s.equals("")){
				String fk=("FK"+UUID.randomUUID().toString()).replace("-", "");
				String columnName=column.getName();
				String[] fs=s.split(",");
				writeFile(fileName,",\nKEY "+fk+ " ("+columnName+"),\n" +
						"CONSTRAINT "+fk+" FOREIGN KEY ("
						+columnName+") REFERENCES "
						+fs[0]+" ("+fs[1]+")",true);
			}
		}
		//添加编码信息
		writeFile(fileName,"\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 \n\n\n",true);
	}
	//用于初版的数据字典，后来字段类型直接用数据库中的名称表示了，就用不到这个方法了
	public static String replaceColumnType(String originType){
		String type="";
		if(originType.equals("字符型")||originType.equals("布尔型")){
			type="varchar";
		}else if(originType.equals("数值型")){
			type="int";
		}
		return type;
	}
	//去掉数值类型后面的".0"，excel中的数字比如36,读出来是36.0
	public static String replaceZeroSuffix(String originString){
		return originString.replace(".0", "");
	}
	//转换是否为空
	public static String replaceNull(String originString){
		String s="";
		if(originString.equals("是")){
			s=" NOT NULL ";
		}else{
			s=" DEFAULT NULL ";
		}
		return s;
	}
     //新建或追加文件：使用FileWriter
    public static void writeFile(String fileName, String content,boolean append) {
        try {
            //打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件
        	BufferedWriter output = new BufferedWriter(new FileWriter(fileName,append));
			output.write(content);
			output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 

3、用于封装数据表信息的模型类

package allan.dbUtil;

public class Table {
	private String name;
	private Column[] columns;
	
	public Table(String name, Column[] columns) {
		super();
		this.name = name;
		this.columns = columns;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Column[] getColumns() {
		return columns;
	}
	public void setColumns(Column[] columns) {
		this.columns = columns;
	}
}

 

4、用于封装字段信息的模型类

package allan.dbUtil;
/**
 * 
 * @author Allan
 * 用于封装字段数据的类
 */
public class Column {
	public Column(String name, String type, String length,
			String decimalLength, String isNotNull, String desc,
			String isPrimary, String isForeign) {
		super();
		this.name = name;
		this.type = type;
		this.length = length;
		this.decimalLength = decimalLength;
		this.isNotNull = isNotNull;
		this.desc = desc;
		this.isPrimary = isPrimary;
		this.isForeign = isForeign;
	}
	//字段名称
	private String name;
	//字段类型
	private String type;
	//字段长度
	private String length;
	//小数长度
	private String decimalLength;
	//是否为空，1是，0否
	private String isNotNull;
	//字段描述
	private String desc;
	//是否主键，1是，0否
	private String isPrimary;
	//是否外键，1是，0否
	private String isForeign;
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public String getLength() {
		return length;
	}
	public void setLength(String length) {
		this.length = length;
	}
	public String getDecimalLength() {
		return decimalLength;
	}
	public void setDecimalLength(String decimalLength) {
		this.decimalLength = decimalLength;
	}
	public String getIsNotNull() {
		return isNotNull;
	}
	public void setIsNotNull(String isNotNull) {
		this.isNotNull = isNotNull;
	}
	public String getDesc() {
		return desc;
	}
	public void setDesc(String desc) {
		this.desc = desc;
	}
	public String getIsPrimary() {
		return isPrimary;
	}
	public void setIsPrimary(String isPrimary) {
		this.isPrimary = isPrimary;
	}
	public String getIsForeign() {
		return isForeign;
	}
	public void setIsForeign(String isForeign) {
		this.isForeign = isForeign;
	}
	
}

 5、包含main方法的主类

package allan.dbUtil;

import java.io.IOException;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CodeGeneration {
	public static void main(String[] args) throws IOException{
		String fileName = "D:\\codeGeneration\\newEpp.xlsx";
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
		Map<String,String> sheetMap=ReadExcelUtil.getSheetMap(xssfWorkbook);
		for (int numSheet = 2; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			String name=ReadExcelUtil.getTableName(xssfWorkbook, numSheet);
			//取得下划线分割的字符串数组
			String[] nameArr=name.split("_");
			//将首字母替换为大写,并保存为新的文件名
			String newName="";
			for(int i=0;i<nameArr.length;i++){
				newName+=nameArr[i].replace(nameArr[i].charAt(0), Character.toUpperCase(nameArr[i].charAt(0)));
			}
			Table tableModel=ReadExcelUtil.getTabelModel(xssfWorkbook, numSheet,sheetMap);
			WriteUtil.generateSqlFile(tableModel);
			//WriteUtil.generateJavaHead(newName);
		}
	}
}