Java 读取EXCEL表格中的数据，将数据转为SQL语句-JZTXT

**[参考文档](https://blog.csdn.net/wl_Honest/article/details/83985751?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_utm_term~default-0-83985751-blog-81103171.pc_relevant_paycolumn_v3&spm=1001.2101.3001.4242.1&utm_relevant_index=3)**
#### 一、需求
存在一个camera表，表中有一个字段叫点位编号，现需要修改该点位编号，用户提供了一个execl表，如下图所示，第2列为原编号，第5列为现编号：
![在这里插入图片描述](https://img-blog.csdnimg.cn/e93c7ddcb3b4408a8991de6f2c1a3c45.png)
#### 二、解决思路
编码读取execl表格数据，将新的编码通过sql语句保存到camera表中，因为camera表有未使用的字段且原来的编码是唯一的，所以我这里就根据原来的编码字段来更新(update)现编码保存的字段；
##### 三、引入相关依赖
```java

<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17-beta1</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17-beta1</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17-beta1</version>
</dependency>

<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
```
#### 四、编码
4.1、创建与excel表对应的实体类
```java
package com.qst.parsexml.domain;

public class HaiXinCamera {

private String name;
private String extcameraid;
private String ip;
private String port;
private String crossing_number;

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getExtcameraid() {
return extcameraid;
}

public void setExtcameraid(String extcameraid) {
this.extcameraid = extcameraid;
}

public String getIp() {
return ip;
}

public void setIp(String ip) {
this.ip = ip;
}

public String getPort() {
return port;
}

public void setPort(String port) {
this.port = port;
}

public String getCrossing_number() {
return crossing_number;
}

public void setCrossing_number(String crossing_number) {
this.crossing_number = crossing_number;
}
}
```
4.2、新建一个ReadExecl.java类，创建main函数
```java
package com.qst.parsexml.test;

import com.qst.parsexml.service.execl.ParseServiceExecl_wangcheng;
import java.io.IOException;
import java.util.Scanner;

public class ReadExecl {

public static void main(String[] args) {
System.out.println("请输入excel文件路径: [execl文件路径] ");
Scanner scanner = new Scanner(System.in);
String[] strs = scanner.nextLine().split("\\s+");
String filepath = strs[0];
try {
//将提供的execl文档中的点位编码保存到camera表中的crossing_number字段中
ParseServiceExecl_wangcheng.parseExecl(filepath);
} catch (IOException e) {
e.printStackTrace();
}
}
}
```
4.3、新建一个ParseServiceExecl_wangcheng.java类，写相关逻辑
```java
package com.qst.parsexml.service.execl;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

import com.qst.parsexml.domain.HaiXinCamera;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ParseServiceExecl_wangcheng {

public static void parseExecl(String filepath) throws IOException{

Workbook wb =null;
Sheet sheet = null;
Row row = null;
List<HaiXinCamera> list = null;
String cellData = null;
//读取Excel文件
wb = readExcel(filepath);
//如果文件不为空
if(wb != null){
//用来存放表中数据
list = new ArrayList<HaiXinCamera>();
//获取第一个sheet
sheet = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
//循环行
for (int i = 1; i<rownum; i++) {
HaiXinCamera camera = new HaiXinCamera();
row = sheet.getRow(i);
if(row !=null){
//循环列
for (int j=0;j<colnum;j++){
cellData = (String) getCellFormatValue(row.getCell(j));
switch (j){
case 0:
camera.setName(cellData);
break;
case 1:
camera.setExtcameraid(cellData);
break;
case 2:
camera.setIp(cellData);
break;
case 3:
camera.setPort(cellData);
break;
case 4:
camera.setCrossing_number(cellData);
break;
default:
break;
}
}
//放入集合
list.add(camera);
}else{
break;
}
}
}
//定义一个文件，用来存数据；
System.out.println("number of camera: "+list.size());
String fileName = filepath.substring(0, filepath.length()-4)+"_update_camera"+".sql";
PrintWriter ps = new PrintWriter(new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileName),"UTF-8")));
if(list.size() > 0){
//遍历解析出来的list
for (HaiXinCamera camera : list ) {

String name = camera.getName() == null ? "null" : "'" + camera.getName() + "'";
String extcameraid = camera.getExtcameraid() == null ? "null" : "'" + camera.getExtcameraid() + "'";
String ip = camera.getIp() == null ? "null" : "'" + camera.getIp() + "'";
String port = camera.getPort() == null ? "null" : "'" + camera.getPort() + "'";
String crossingNumber = camera.getCrossing_number() == null ? "null" : "'" + camera.getCrossing_number() + "'";

String strSQL = String.format("UPDATE camera set crossing_number= %s WHERE extcameraid = %s;",crossingNumber, extcameraid);
ps.println(strSQL);
}
}
ps.close();
}

//读取excel
@SuppressWarnings("resource")
public static Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
//文件后缀名
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
//如果文件后缀名为xls
if(".xls".equals(extString)){
return wb = new HSSFWorkbook(is);
}//如果文件后缀名为xlsx
else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}else{
return wb = null;
}
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}

@SuppressWarnings("deprecation")
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判断cell类型
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:{
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:{
//判断cell是否为日期格式
if(DateUtil.isCellDateFormatted(cell)){
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
}else{
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
}else{
cellValue = "";
}
return cellValue;
}
}
```
4.4、结果：
执行代码，输入excel文件所在的路径，如C:\Users\MECHREVO-PC\Desktop\test.xlsx，回车之后就会在该路径下生成以sql结尾的文件;本文的输出结果如图所示：
![在这里插入图片描述](https://img-blog.csdnimg.cn/978c8699cad547dab4f458c7c1e73a11.png)