@Test
public void test() {
String filePath = "E:/test/XXX.xlsx"; //指定本地的数据目录
String slqPath = "E:/test/XX.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<Data> excelBean = getExcelBean(sql, sheetAt, lastRowNum);
for(Data bean :excelBean){
sql += String.format("INSERT INTO `data` (`DICT_ID`, `DICT_NAME`) VALUES ('%s', '%s');\n", bean.getDictId(), bean.getDictName());
}
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 List<Data> getExcelBean(String sql, Sheet sheetAt, int lastRowNum) {
List<BaseDataDict> list = new ArrayList<>();
try{
for (int rowNum=0; rowNum <= lastRowNum; rowNum++){
Data data = new Data();
Row row = sheetAt.getRow(rowNum);
String testName1 = "";
String testName2 = "";
if (row != null){
Cell cell = row.getCell(0);
if (cell != null) {
testName1 = cell.getStringCellValue();
data.setDictId(testName1);
}
cell = row.getCell(1);
if (cell != null) {
testName2 = cell.getStringCellValue();
data.setDictName(testName2);
}
}
list.add(data);
}
}catch (Exception e){
System.out.print(e);
}
return list;
}