java读取excel表格拼接sql_java实现读取excel相应行列数据并拼接sql语句输出

用到poi和io包

直接上源码

package com.excelproblem.huige.excelimport;

import org.apache.poi.ss.usermodel.*;

import java.io.*;

public class ExcelImport {

public static void main(String[] args) {

new ExcelImport().read();

}

public void read() {

File file = new File("F:\\excel\\浙江.xlsx");

InputStream inputStream = null;

Workbook workbook = null;

try {

inputStream = new FileInputStream(file);

workbook = WorkbookFactory.create(inputStream);

inputStream.close();

//工作表对象

Sheet sheet = workbook.getSheetAt(0);

//总行数

int rowLength = sheet.getLastRowNum()+1;

//根据第一行，获取总列数

Row row = sheet.getRow(0);

//总列数

int colLength = row.getLastCellNum();

//得到指定的单元格

Cell SCHOOLID = row.getCell(0);

Cell NAME = row.getCell(0);

Cell SCHOOL_CLASSES = row.getCell(0);

Cell LEGAL_TYPE = row.getCell(0);

Cell CUSTOMER_NO = row.getCell(0);

Cell SIGN_INSID = row.getCell(0);

System.out.println("行数：" + rowLength + ",列数：" + colLength);

for (int i = 2; i < rowLength; i++) {

row = sheet.getRow(i);

// 获取第二列的内容

SCHOOLID = row.getCell(0);

NAME = row.getCell(1);

SIGN_INSID = row.getCell(2);

SCHOOL_CLASSES = row.getCell(3);

LEGAL_TYPE = row.getCell(4);

CUSTOMER_NO = row.getCell(5);

System.out.println("update zhxy_schoollist set SCHOOL_CLASSES='"+SCHOOL_CLASSES+"',LEGAL_TYPE='"+LEGAL_TYPE+"',CUSTOMER_NO='"+CUSTOMER_NO+"',SIGN_INSID='"+SIGN_INSID+"',NAME=trim('"+NAME+"') where trim(SCHOOLID)='"+SCHOOLID+"';");

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

输出可以设置idea的log日志

excel处理：需要将excel表格设置为文本，这样输出格式就不会乱，我这里还有个批量更新内容的功能用的excel实现，没写java代码，简单实用型，没必要为了写代码而写代码，项目实践省时间，excel小工具方方格子，如图下：