ReadExcel4SQL.java
package rtool;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.io.FileUtils;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel4SQL {
//资源绝对路径
public static StringxlsPath="F:\\文档\\实体类\\黑名单.xls";
//资源的目录
public static StringdirPath="F:\\文档\\实体类\\";
//输出路径
public static StringjavaPath="";
//字段名列
public static int attributesIndex=0;
//类型列
public static int typesIndex=2;
//注释列
public static int[]marksIndex={1,4,3};
//是否为空注释
public static int marksEmptyIndex=3;
public static void main(String[] args)throws IOException, InvalidFormatException {
// TODO Auto-generated method stub
File file =new File(dirPath);
ArrayList files =getListFiles(file);
for(File f:files){
createSQL( f);
}
}
public static void createSQL(File file)throws IOException, InvalidFormatException {
String fileName = file.getName();
String tableName ="demo";
File file1 =null;
FileOutputStream fop =null;
List types =new ArrayList();
List attributes =new ArrayList();
List marks =new ArrayList();
List marksEmpty =new ArrayList();
if (fileName.endsWith("xlsx")){
//?????????????????????????????????
XSSFWorkbook workbooks =new XSSFWorkbook(FileUtils.openInputStream(file));
XSSFSheet xssfSheet = workbooks.getSheetAt(0);
int totalRows = xssfSheet.getPhysicalNumberOfRows();
XSSFRow row = xssfSheet.getRow(0);
tableName = row.getCell(0).getStringCellValue();
System.out.println(tableName+"total:"+totalRows);
for(int i=1;i
XSSFRow row2 = xssfSheet.getRow(i);
attributes.add(row2.getCell(attributesIndex).getStringCellValue());
types.add(row2.getCell(typesIndex).getStringCellValue());
String marksDtr="";
for(int j=marksIndex.length-1;j>=0;j--){
marksDtr+= row2.getCell(marksIndex[j]).getStringCellValue();
}
marks.add(marksDtr);
}
}else if(fileName.endsWith("xls")) {
HSSFWorkbook workbook =new HSSFWorkbook(FileUtils.openInputStream(file));
//读取默认第一个工作表sheet
HSSFSheet sheet = workbook.getSheetAt(0);
int firstRowNum =0;
//获取sheet中最后一行行号
int lastRowNum = sheet.getLastRowNum();
HSSFRow row = sheet.getRow(firstRowNum);
tableName = row.getCell(0).getStringCellValue();
for (int i =1; i <=lastRowNum-1; i++){
HSSFRow row1 = sheet.getRow(i);
attributes.add(row1.getCell(attributesIndex).getStringCellValue());
types.add(row1.getCell(typesIndex).getStringCellValue());
String marksDtr="";
for(int j=marksIndex.length-1,k=0;j>=0;j--,k++){
marksDtr+= row1.getCell(marksIndex[k]).getStringCellValue().equals("")?"":row1.getCell(marksIndex[k]).getStringCellValue()+(j!=0?"--":"");
}
marks.add(marksDtr);
marksEmpty.add(row1.getCell(marksEmptyIndex).getStringCellValue());
}
}else
{
return;
}
StringBuffer sb =new StringBuffer();
try {
// attributes= createUtil.attributesFilter(attributes);
// types= createUtil.typesFilter(types);
marksEmpty=createSQL.marksEmptyFilter(marksEmpty);
types= createSQL.typesFilter(types);
attributes = createSQL.attributesFilter(attributes);
sb.append("CREATE TABLE " + tableName +"(\n\nID int not null auto_increment primary key,");
sb.append(createSQL.appendInit(types, attributes,marksEmpty, marks));
//sb.append(createUtil.getSet(types, attributes, marks));
sb.append(");\n");
}catch (Exception e)
{
System.out.println(e.toString());
}
file1 =new File(file.getParent()+"//createTable.sql");
if (!file1.exists()) {
file1.createNewFile();
fop =new FileOutputStream(file1);
}else {
fop =new FileOutputStream(file1,true);
}
byte[] contentInBytes = sb.toString().getBytes();
fop.write(contentInBytes);
fop.flush();
fop.close();
System.out.println("Done");
}
public static ArrayListgetListFiles(Object obj) {
File directory =null;
if (objinstanceof File) {
directory = (File) obj;
}else {
directory =new File(obj.toString());
}
ArrayList files =new ArrayList();
if (directory.isFile()) {
files.add(directory);
return files;
}else if (directory.isDirectory()) {
File[] fileArr = directory.listFiles();
for (int i =0; i < fileArr.length; i++) {
File fileOne = fileArr[i];
files.addAll(getListFiles(fileOne));
}
}
return files;
}
}
createSQL.java
package rtool;
import java.util.List;
public class createSQL {
public static StringappendInit(List types,List attributes,List marksEmpty,List mark){
StringBuffer sb =new StringBuffer();
if(types.size()==attributes.size()&&types.size()==mark.size()){
for(int i=0;i
sb.append(" /**\n");
sb.append(" *"+mark.get(i)+"\n");
sb.append(" */\n");
sb.append(attributes.get(i)+" "+types.get(i)+" "+marksEmpty.get(i)+" COMMENT '"+mark.get(i) +"',\n\n");
}
}
return (sb.deleteCharAt(sb.lastIndexOf(","))).toString();
}
public static StringgetSet(List types,List attributes,List mark){
StringBuffer sb =new StringBuffer();
if(types.size()==attributes.size()){
for(int i=0;i
String attribute = attributes.get(i);
String c = String.valueOf(attribute.charAt(0));
c = c.toUpperCase();
attribute = c + attribute.substring(1);
sb.append(" /**\n");
sb.append(" * 设置"+mark.get(i)+"\n");
sb.append(" * @param "+attributes.get(i)+" "+mark.get(i)+"\n");
sb.append(" */\n");
sb.append(" public void set"+attribute+"("+types.get(i)+" "+attributes.get(i)+"){\n");
sb.append(" this."+attributes.get(i)+" = "+attributes.get(i)+";\n");
sb.append(" }\n\n");
sb.append(" /**\n");
sb.append(" *获得"+mark.get(i)+"\n");
sb.append(" * @return "+attributes.get(i)+" "+mark.get(i)+"\n");
sb.append(" */\n");
sb.append(" public "+types.get(i)+" get"+attribute+"(){\n");
sb.append(" return "+attributes.get(i)+";\n");
sb.append(" }\n\n");
}
}
return sb.toString();
}
// .toLowerCase();//转成小写
//.toUpperCase();//转成大写
// public static List attributesFilter(List attributes)
// {
// for(int i=0;i< attributes.size();i++)
// {
// String str[]=attributes.get(i).toLowerCase().split("_");
// String Str="";
// Str +=str[0];
// for(int j=1;j< str.length;j++)
// {
// Str+=str[j].substring(0,1).toUpperCase();
// Str+=str[j].substring(1);
// }
// attributes.set(i,Str);
//
// }
// return attributes;
// }
public static ListtypesFilter(List types)
{
for(int i=0;i< types.size();i++)
{
if( types.get(i).toLowerCase().contains("int"))
{
types.set(i,"Integer");
}else if(types.get(i).toLowerCase().contains("double")){
types.set(i,"Double");
}else if(types.get(i).toLowerCase().contains("string")){
types.set(i,"VARCHAR(20)");
}else if(types.get(i).toLowerCase().contains("float")){
types.set(i,"Float");
}else if(types.get(i).toLowerCase().contains("bool")){
types.set(i,"char");
}else if(types.get(i).toLowerCase().contains("date")){
types.set(i,"DATETIME");
}else if(types.get(i).toLowerCase().contains("char")){
types.set(i,"char");
}
}
return types;
}
public static ListattributesFilter(List attributes)
{
for(int i=0;i< attributes.size();i++)
{
if( attributes.get(i).toUpperCase().contains("DESCRIBE"))
{
attributes.set(i,attributes.get(i)+"_");
}
}
return attributes;
}
public static ListmarksEmptyFilter(List m)
{
for(int i=0;i< m.size();i++)
{
if( m.get(i).toLowerCase().contains("y")||m.get(i).toLowerCase().contains("是"))
{
m.set(i,"not null");
}else
{
m.set(i,"");
}
}
return m;
}
public static StringappendCreateTable(List types,List attributes,List mark){
StringBuffer sb =new StringBuffer();
if(types.size()==attributes.size()&&types.size()==mark.size()){
for(int i=0;i
sb.append(" /**\n");
sb.append(" *"+mark.get(i)+"\n");
sb.append(" */\n");
sb.append(" private "+types.get(i)+" "+attributes.get(i)+";\n\n");
}
}
return sb.toString();
}
}
网友评论