利用poi把excel转成sql建表语句并写入文件

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.6</version>
        </dependency>

一个小工具，自我学习可以把现有的excel表格里面的sheet表转成sql建表语句，因为每个表的字段非常多，上百个字段，所以通过这种自动生成建表sql的方式，会比较方便，目前这个仅仅可以满足关系型数据库，后续可能还会增加一个转hive sql的工具类。以后会完善excel和sql互转。直接上代码。

package utils;
import java.util.*;
import java.io.File;
import java.io.IOException;
import java.io.FileInputStream;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Created by happy on 2019/06/05
 * In order to transform excel to sql/hsql,
 * otherwise, as same with sql/hsql to excel.
 */
public class ExcelSQLTransform {

    /**
     *
     * @param filePath
     * @param storePath
     * @return
     * @throws Exception
     */
    public static List<Map> readExcel(String filePath,String storePath) throws Exception{
        File file=new File(filePath);
        if (!file.exists()) {
            return null;
        }

        /**
         * Get the suffix of the file
         */
        String suffix= filePath.substring(filePath.lastIndexOf("."));

        /**
         * if suffix cannot equals .xls && .xlsx ,return null
         */
        if(!".xls".equals(suffix) && !".xlsx".equals(suffix)){
            return null;
        }

        //返回值列
        List<Map> reaultList=new ArrayList<Map>();
        if(".xls".equals(suffix)){
            reaultList=readExcel2007(filePath,storePath);
        }else if(".xlsx".equals(suffix)){
            reaultList=readExcel2007(filePath,storePath);
        }
        return reaultList;
    }

    /**
     *
     * @param filePath
     * @param storePath
     * @return
     * @throws IOException
     */
    public static List<Map> readExcel2007(String filePath,String storePath) throws IOException{
        List<Map> valueList=new ArrayList<Map>();
        FileInputStream fis =null;
        try {
            fis =new FileInputStream(filePath);
            XSSFWorkbook xwb = new XSSFWorkbook(fis);       // 构造 XSSFWorkbook 对象，strPath 传入文件路径

            int sheetNum = xwb.getNumberOfSheets();
            /**
             *  foreach to read sheet.
             */
            XSSFSheet sheet;
            XSSFRow row;
            String sql;

            for (int s=0;s<=sheetNum-1;s++){
                sheet = xwb.getSheetAt(s);
                String table_name           = sheet.getSheetName();
                int totalRow                = sheet.getLastRowNum();
                sql = "CREATE TABLE IF NOT EXISTS "+table_name+"(";
                for (int i=1;i<=totalRow;i++){
                     row      = sheet.getRow(i);
                     if (i==totalRow){
                         String a = row.getCell(1)+" "+row.getCell(2)+");";
                         sql = sql+a;
                     }else {
                         String a  = row.getCell(1)+" "+row.getCell(2)+",";
                         sql = sql+a;
                     }
                }
                new SaveDataToFile().appendFile(storePath,sql);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            fis.close();
        }
        return valueList;
    }

    /**
     * main
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        String readurl  = "D:\\worker\\personalWorkSpace\\syphonage\\cup.xls";  //读取excel文件的路径
        String storeurl = "storeurl";                                           //保存sql命令的路径
        readExcel(readurl,storeurl);
    }
}

下面这个是单独的一个类，实现数据追加到文件，该文件有则追加，该文件没有创建并追加。

package utils;

import java.io.*;

/**
 * Created by happy on 2019/06/09
 * write data to destination.
 *
 * attention: print is informal,must be log.
 */
public class SaveDataToFile {
    public void appendFile(String fileName, String content) {
        try {
            //打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件,如果为 true，则将字节写入文件末尾处，而不是写入文件开始处
            FileWriter writer = new FileWriter(fileName, true);
            writer.write(content+"\n");
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}