序:需求是往数据库批量造测试数据、数量较多。用代码直接生成sql文件会省很多事。
注、引包:poi.jar系列引入即可。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.7</version>
</dependency>
ExcelBean为字段类。自己新建并添加相应字段即可。
package demo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
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 excelToSql {
public static void executeInfo() throws IOException{
String filePath = "D:/FavoriteVideo/testExcel.xlsx"; //指定本地的数据目录
String slqPath = "D:/FavoriteVideo/testSql.sql"; //指定生成文件目录
try {
InputStream in = new FileInputStream(filePath);
Workbook wb = new XSSFWorkbook(in);
in.close();
String sql = "";
Sheet sheetAt = wb.getSheetAt(0);
int lastRowNum = sheetAt.getLastRowNum();
Map map = new HashMap();
sql = "";
List<ExcelBean> excelBean = getExcelBean(sql, sheetAt, lastRowNum);
Map<String,String> map1 =new HashMap<>();
for(ExcelBean bean :excelBean){
map1.put(bean.getTestName(),bean.getUser_());
}
Iterator<Map.Entry<String, String>> it = map1.entrySet().iterator();
while (it.hasNext()) {
Map.Entry<String, String> entry = it.next();
System.out.println("key= " + entry.getKey() + " and value= " + entry.getValue());
sql += String.format("INSERT IGNORE INTO `test_table_info` (`testName`, `updateTime`, `createTime`, `user_`) VALUES ('%s', now(), now(), '%s');\n", entry.getKey(), entry.getValue());
}
System.out.println(map1);
System.out.println(map1.size());
File file = new File(slqPath);
if(!file.exists()){
file.createNewFile();
}
byte[] bytes = sql.getBytes();
OutputStream os = new FileOutputStream(slqPath);
os.write(bytes);
os.flush();
os.close();
System.out.println("sheets");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private static List<ExcelBean> getExcelBean(String sql, Sheet sheetAt, int lastRowNum) {
List<ExcelBean> list = new ArrayList<>();
for (int rowNum=0; rowNum <= lastRowNum; rowNum++){
ExcelBean bean = new ExcelBean(); //创建一个单独Bean、
System.out.println("rowNum = "+rowNum);
Row row = sheetAt.getRow(rowNum);
String testName = "";
String user_ = "";
if (row != null){
Cell cell = row.getCell(0);
if (cell != null) {
testName = cell.getStringCellValue();
bean.setTestName(testName);
}
cell = row.getCell(1);
if (cell != null) {
user_ = cell.getStringCellValue();
bean.setUser_(user_);
}
/**
* 更多列在这里补充
*/
}
list.add(bean);
}
return list;
}
public static void main(String args[]) throws IOException{
long startTime = System.currentTimeMillis();
executeInfo();
long endTime = System.currentTimeMillis();
System.out.println("耗时为m:" + (endTime - startTime)/1000);
}
}
excel测试数据:
生成.sql文件:
生成的.sql直接拿入数据库执行即可。(oracle库记得更改日期字段哦..)
end_