读取xlsx文件生成SQL

读取xlsx文件到list

1.pom.xm

<dependencies>
    <!-- 引入poi，解析workbook视图 -->

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

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

    <!-- 处理excel和上面功能是一样的-->
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.10</version>
    </dependency>
</dependencies>

2.读取xlsx存放到list

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

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class ExcelPoiUtils {



    public static void main(String[] args) {
        read();
    }


    public  static void read(){

        try {
            InputStream inputStream = new FileInputStream("C:\\Users\\MACHENIKE\\Desktop\\vv.xlsx");

            XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);
             //获取第一页的对象
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
             //获取xlsx第三列的内容
            XSSFRow titleRow = sheetAt.getRow(2);

              //从第4行还是开始  到当前页的最后一行进行遍历      getLastRowNum(获取最后一行不为空的行数)
            for(int i=3;i<sheetAt.getLastRowNum();i++){
                Map<String,List<TableEntity>> mapTabelEntity=new HashMap();
                    //从第0个开始，获取获取每一列的值           getPhysicalNumberOfCells（获取不为空的列个数）
                for(int x=0;x<sheetAt.getRow(i).getPhysicalNumberOfCells();x++){
                    //获取第三列的每个值
                    String tabcol=titleRow.getCell(x).toString();
                      //对tabcol进行切割
                    String table=tabcol.split(":")[0];
                     //获取sys_special_indicators:name:indicatorsIgfbp3     获取这个属性  indicatorsIgfbp3
                    String column=tabcol.split(":")[tabcol.split(":").length -1];
                         //创建对象   进行赋值
                    TableEntity tableEntity=new TableEntity();

                    tableEntity.setColumn(column);
                      //获取i行   x列的值
                    tableEntity.setValue(sheetAt.getRow(i).getCell(x).toString());
                      //判断Map集合里有没有table这个key
                    if(mapTabelEntity.get(table)==null){
                        List tableEntityList=new ArrayList();
                        tableEntityList.add(tableEntity);
                        mapTabelEntity.put(table, tableEntityList);
                    }else{
                        List<TableEntity> tableEntities = mapTabelEntity.get(table);
                        tableEntities.add(tableEntity);
                    }
                }
                //获取Map里所有的key
                Set<String> mapKeySet = mapTabelEntity.keySet();
                //迭代器
                Iterator<String> iterator = mapKeySet.iterator();
                    //进行判断     遍历
                while(iterator.hasNext()){
                    String key=iterator.next();
                       //通过key获取list值
                    List<TableEntity> tableEntityList=mapTabelEntity.get(key);
                    String insertsql="insert into "+key;
                    String column ="(";
                    String value ="(";
                    for(int x=0;x<tableEntityList.size();x++){
                          //sql语句进行拼接
                        column+=tableEntityList.get(x).getColumn()+",";

                        value+=tableEntityList.get(x).getValue()+",";
                    }
                    //insert  into  key (column,sdf) values (value,value)
                    insertsql+=column+")"+" values "+value+")";

                    System.out.println(insertsql);

                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}