需求
工作中,经常会有重复性的工作,这种重复性的工作需要考虑一下能不能做成工具,批量作业,省事省力。
现在有个批量测试sql语句的需求
开发流程
1.读取excel内容
2.执行sql测试操作,得到结果
3.结果写入excel
4.检查excel结果
读取excel内容
引入poi的包
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
工具类
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
/**
* Author: zgg
* Date: 2021/11/10
* Description:读写Excel数据
* HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls;
* XSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;
* SXSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;
*/
public class ExcelUtil {
private XSSFSheet sheet;
private XSSFWorkbook workbook = null;
/**
* 构造函数,初始化excel数据
* @param filePath excel路径
* @param sheetName sheet表名
*/
ExcelUtil(String filePath,String sheetName){
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(filePath);
workbook = new XSSFWorkbook(fileInputStream);
//获取sheet
sheet = workbook.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 往excel中写入.
* @param filePath 文件路径
// * @param sheetName 表格索引
*/
public void writeToExcel(String filePath,int rowNum, int cellNum, String context){
try {
FileOutputStream out = null;
XSSFCell cell = sheet.getRow(rowNum).getCell(cellNum);
cell.setCellValue(context);
out = new FileOutputStream(filePath);
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}
/**
* 根据行和列的索引获取单元格的数据
* @param row
* @param column
* @return
*/
public String getExcelDateByIndex(int row,int column){
XSSFRow row1 = sheet.getRow(row);
String cell = row1.getCell(column).toString();
return cell;
}
/**
* 根据某一列值为“******”的这一行,来获取该行第x列的值
* @param caseName
* @param currentColumn 当前单元格列的索引
* @param targetColumn 目标单元格列的索引
* @return
*/
public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
String operateSteps="";
//获取行数
int rows = sheet.getPhysicalNumberOfRows();
for(int i=0;i<rows;i++){
XSSFRow row = sheet.getRow(i);
String cell = row.getCell(currentColumn).toString();
if(cell.equals(caseName)){
operateSteps = row.getCell(targetColumn).toString();
break;
}
}
return operateSteps;
}
}
测试方法,这里主要是测试sql解析。
@Test
public void testCase003(){
SqlParseUtils utils = SqlParseUtils.getInstance();
ExcelUtil sheet = new ExcelUtil("G:\\sql测试.xlsx","case1");
List<String> tempNames = null;
String sql = null;
//行
for(int i = 1;i< 15;i++){
//列
for(int j = 2;j<=4;j++) {
try {
sql = sheet.getExcelDateByIndex(i, j);
if(sql == null || sql.trim() == ""){
continue;
}
sql = sql.replaceAll("WITH[\\s]*TIES ","")
.replaceAll("with[\\s]*rollup","");
tempNames = utils.findTableFromSql(sql);
sheet.writeToExcel("G:\\sql测试.xlsx",i,j+4,tempNames.toString());
} catch (Exception e) {
sheet.writeToExcel("G:\\sql测试.xlsx",i,j+4,"sql解析出错");
e.printStackTrace();
System.out.println("执行失败,sql"+sql);
}
}
}
System.out.println("执行成功");
}