场景:
从文件里面读取一列的数据,作为插入sql的参数,批量生成sql脚本。
package com.yecc.fileRead;
import cn.hutool.core.io.file.FileReader;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.UUID;
/**
* Created by yecc on 2020/11/20 15:56
*/
public class ScriptToImport {
public static void main(String[] args) {
File file = new File("C:\\Users\\Administrator\\Desktop\\派出所.xls");
try {
System.out.println("正在读取书名...");
readColumn(file, 0);//读取第一列
System.out.println("读取完毕");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取excel表格中特定的列
*
* @param file
* 文件
* @param index
* 第index列(0开始)
* @throws Exception
*/
public static void readColumn(File file, int index) throws Exception {
InputStream inputStream = new FileInputStream(file.getAbsoluteFile());
Workbook workbook = Workbook.getWorkbook(inputStream);
Sheet sheet = workbook.getSheet(0);
int rows = sheet.getRows();
int columns = sheet.getColumns();
for (int i = 1; i < rows; i++) {
Cell cell = sheet.getCell(index, i);
String contents = cell.getContents();
System.out.println(String.format("INSERT INTO \"public\".\"t_md_stand_code_val\" (\"id\", \"name\", \"version\", \"type\", \"memo\", \"code\", \"owner_id\", \"map_key\", \"extended_type\", \"operate_time\", \"operate_user_id\", \"mb_id\", \"originally_val\", \"filed_id\") VALUES ('%s', '%s', NULL, 'StandCodeVal', '%s', '%s', NULL, NULL, NULL, '2020-11-23 08:44:54', NULL, '8080a79005eb5853a8a0e5d18f83a330', NULL, NULL);",UUID.randomUUID().toString().replaceAll("-",""),contents,contents,contents));
}
}
}