根据Excel生成建表语句sql
设计的人跟开发的人总不是同一个，这就导致了设计是设计的思路，开发是开发的思路，表也是一样，开发给加了字段不同步给设计人员，设计加了字段开发可能这个环境加了，另一个没加。为了避免比对和扯皮，以设计为准！
生成思路
设计为标准的excel表，可以用java读取excel内容，再根据内容填入固定vm模板中，最后生成sql语句。
批量的话就是循环读取多张Excel sheet页，然后循环生成到一个文件即可
工程结构如下
Excel读取部分代码
public static List<String[]> readExcel(String filePath) throws Exception {
List<String[]> list = new ArrayList<String[]>();
// 创建输入流
InputStream stream = new FileInputStream(filePath);
// 获取Excel文件对象
Workbook wb = Workbook.getWorkbook(stream);
Sheet[] sheets = wb.getSheets();
for (Sheet sheet : sheets) {
// System.out.println(sheet.getName());
if ("信息发送表".equals(sheet.getName())) {
for (int i = 0; i < sheet.getRows(); i++) {
// 创建一个数组 用来存储每一列的值
String[] str = new String[sheet.getColumns()];
Cell cell = null;
// 列数
for (int j = 0; j < sheet.getColumns(); j++) {
// 获取第i行，第j列的值
cell = sheet.getCell(j, i);
str[j] = cell.getContents();
}
// 把刚获取的列存入list
list.add(str);
}
}
}
return list;
}
public static List<String[]> readExcel(String filePath,String sheetname) throws Exception {
List<String[]> list = new ArrayList<String[]>();
// 创建输入流
InputStream stream = new FileInputStream(filePath);
// 获取Excel文件对象
Workbook wb = Workbook.getWorkbook(stream);
Sheet[] sheets = wb.getSheets();
for (Sheet sheet : sheets) {
// System.out.println(sheet.getName());
if (sheetname.equals(sheet.getName())) {
for (int i = 0; i < sheet.getRows(); i++) {
// 创建一个数组 用来存储每一列的值
String[] str = new String[sheet.getColumns()];
Cell cell = null;
// 列数
for (int j = 0; j < sheet.getColumns(); j++) {
// 获取第i行，第j列的值
cell = sheet.getCell(j, i);
str[j] = cell.getContents();
}
// 把刚获取的列存入list
list.add(str);
}
}
}
return list;
}
public static void main(String[] args) {
try {
String s = System.getProperty("user.dir");
System.out.println(s);
ExcelUtil.readExcel(s + "\\1.xls");
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (JXLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
VM模板部分
create table $tablename
(
#foreach($i in $dataList)
$i
#end
)
tablespace $tablespacename;
comment on table $tablename is '$tablenameCN';
#foreach($i in $commonList)
$i
#end
模板工具类
public class VelocityUtil {
public static boolean createSql(String filePath, String sheetname, String dir) {
boolean result = true;
// 初始化模板引擎
VelocityEngine ve = new VelocityEngine();
ve.setProperty(RuntimeConstants.RESOURCE_LOADER, "classpath");
ve.setProperty("classpath.resource.loader.class", ClasspathResourceLoader.class.getName());
ve.init();
// 获取模板文件
Template t = ve.getTemplate("createsql.vm");
// 设置变量
VelocityContext ctx = new VelocityContext();
// ctx.put("name", "Velocity");
// 获取工程路径
String s = System.getProperty("user.dir");
String tablename = "";
String tablenameCN = "";
String tablespacename = "";
List<String[]> list = new ArrayList<String[]>();
List<String> dataList = new ArrayList<String>();
List<String> commonList = new ArrayList<String>();
try {
// list = ExcelUtil.readExcel(s + "\\1.xls");
list = ExcelUtil.readExcel(filePath, sheetname);
} catch (Exception e) {
e.printStackTrace();
}
for (int i = 0; i < list.size(); i++) {
for (int j = 0; j < list.get(i).length; j++) {
if (!"".equals(list.get(i)[j].trim())) {
// 获取表名
if ("表名".equals(list.get(i)[j])) {
System.out.println("表名:" + list.get(i)[j + 1]);
tablename = list.get(i)[j + 1];
break;
} else
// 获取描述
if ("描述".equals(list.get(i)[j])) {
System.out.println("描述:" + list.get(i)[j + 1]);
tablenameCN = list.get(i)[j + 1];
break;
} else
// 获取表空间
if ("表空间".equals(list.get(i)[j])) {
System.out.println("表空间:" + list.get(i)[j + 1]);
tablespacename = list.get(i)[j + 1];
break;
} else
// 表头行跳过
if ("英文名称".equals(list.get(i)[j])) {
break;
} else
// 表体数据
if (list.get(i).length >= 6) {
System.out.println("---------------------");
System.out.println("英文名称：" + list.get(i)[0]);
System.out.println("中文名称：" + list.get(i)[1]);
System.out.println("类型：" + list.get(i)[2]);
System.out.println("可为空：" + list.get(i)[3]);
System.out.println("主键：" + list.get(i)[4]);
System.out.println("说明：" + list.get(i)[5]);
System.out.println("---------------------");
if (!"N".equals(list.get(i)[3]) && i != list.size() - 1) {
dataList.add("\t" + list.get(i)[0] + "\t" + list.get(i)[2] + ",");
} else if ("N".equals(list.get(i)[3]) && i != list.size() - 1) {
dataList.add("\t" + list.get(i)[0] + "\t" + list.get(i)[2] + "\t" + "not null" + ",");
}
// 判断是否为最后一行
else if (i == list.size() - 1) {
dataList.add("\t" + list.get(i)[0] + "\t" + list.get(i)[2]);
} else {
dataList.add("\t" + list.get(i)[0] + "\t" + list.get(i)[2] + "\t" + "not null");
}
if ("".equals(list.get(i)[5].trim())) {
commonList.add("comment on column " + tablename + "." + list.get(i)[0].trim() + " is '"
+ list.get(i)[1].trim() + "';");
} else {
commonList.add("comment on column " + tablename + "." + list.get(i)[0].trim() + " is '"
+ list.get(i)[1].trim() + ":" + list.get(i)[5].trim() + "';");
}
break;
}
}
}
}
ctx.put("tablename", tablename);// 表英文名
ctx.put("tablenameCN", tablenameCN);// 表中文名
ctx.put("tablespacename", tablespacename);// 表空间
ctx.put("dataList", dataList);// 表数据
ctx.put("commonList", commonList);// 表注释
// 输出
StringWriter sw = new StringWriter();
t.merge(ctx, sw);
System.out.println(sw.toString());
FileTool.wirteFile(sw.toString(), dir+"\\"+sheetname+".sql");
return result;
}
public static void main(String[] args) {
}
}
面板展示
public MainFrame() {
jf = new JFrame();
jf.setVisible(true);
jf.setTitle("转换工具");
jf.setSize(500, 180);
jf.setLocation((width - 400) / 2, (height - 300) / 2);
jf.setResizable(false);// 窗体不可调整
// jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);// 窗体关闭时关闭应用程序
// 创建按钮 选择excl文件的按钮
jPanel = new JPanel();
JLabel label1 = new JLabel("请选择要转换的Excl文件");
JButton input = new JButton("浏览");
JLabel label4 = new JLabel("请输入sheet页名称（默认为第一页）");
JLabel label2 = new JLabel("请选择sql存放的路径");
JButton output = new JButton("浏览");
JButton submit = new JButton("确定");
JButton exit = new JButton("退出");
jPanel.add(label1);
jPanel.add(text_excl);
jPanel.add(input);
jPanel.add(label2);
jPanel.add(text_dir);
jPanel.add(output);
jPanel.add(label4);
jPanel.add(text_sheet);
jPanel.add(submit, BorderLayout.SOUTH);
jPanel.add(exit, BorderLayout.SOUTH);
jf.add(jPanel);
input.addActionListener(new MouseAction("input"));
output.addActionListener(new MouseAction("output"));
submit.addActionListener(new MouseAction("submit"));
exit.addActionListener(new MouseAction("exit"));
}
class MouseAction implements ActionListener {
private String onclick = "";
public MouseAction(String str) {
onclick = str;
}
@Override
public void actionPerformed(ActionEvent e) {
// 文件选择
if ("input".equals(onclick)) {
JFileChooser chooser = new JFileChooser();
chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
chooser.setFileFilter(new FileNameExtensionFilter("Excl files",
"xls"));
int returnval = chooser.showDialog(jf, "请选择文件");
if (returnval == JFileChooser.APPROVE_OPTION) {
String inputFileName = chooser.getSelectedFile().getPath();
text_excl.setText(inputFileName);
}
} else if ("output".equals(onclick)) {
JFileChooser chooser = new JFileChooser();
chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
chooser.setCurrentDirectory(new File("."));
int returnval = chooser.showDialog(jf, "请选择文件");
if (returnval == JFileChooser.APPROVE_OPTION) {
String outputFileName2 = chooser.getSelectedFile()
.getPath();
text_dir.setText(outputFileName2);
}
} else if ("submit".equals(onclick)) {
// Excel文件所在路径
boolean flag1 = true, flag2 = true;
System.out.println(text_excl.getText());
System.out.println(text_sheet.getText());
System.out.println(text_dir.getText());
flag1 = VelocityUtil.createSql(text_excl.getText(), text_sheet.getText(), text_dir.getText());
if (!"".equals(text_excl.getText())
&& !"".equals(text_dir.getText())
&& "".equals(text_sheet.getText())) {
}
if (flag1 && flag2) {
JOptionPane.showMessageDialog(null, "创建成功!", "消息",
JOptionPane.INFORMATION_MESSAGE);
} else {
JOptionPane.showMessageDialog(null, "创建失败!", "消息",
JOptionPane.ERROR_MESSAGE);
}
} else if ("exit".equals(onclick)) {
jf.dispose();
}
}
}
public static void main(String[] args) {
MainFrame mf = new MainFrame();
}
1148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
