@SuppressWarnings("rawtypes")
public static void main(String[] args) throws Exception {
List list = getPathExcel("D:\\Users\\111\\Desktop\\tsss.xlsx");
System.out.println(list);
outPathSql("D:\\Users\\111\\Desktop\\tsss.sql", list);
}
@SuppressWarnings({ "rawtypes" })
public static void outPathSql(String path, List list) throws IOException {
OutputStreamWriter out = new OutputStreamWriter(new FileOutputStream(new File(path)), "GBK");
StringBuffer sb = new StringBuffer();
// OutputStream out1 = new FileOutputStream(new File(path),true);
// out1.write(sb.toString().getBytes());
for (int a = 1; a < list.size(); a++) {
sb.append("select * from ssp_bas w where w.aaa = ");
List rowList = (List) list.get(a);
sb.append(rowList.get(0) + " and w.bbb= " + rowList.get(1) + " ;").append("\n");
}
out.write(sb.toString());
out.flush();
out.close();
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public static List getPathExcel(String Path) throws IOException {
// 读传入地址的文件
InputStream io = new FileInputStream(Path);
XSSFWorkbook xssF = new XSSFWorkbook(io);
// 遍历每一页的每一行
List sheetList = new ArrayList();
for (XSSFSheet xssFsheet : xssF) {
if (xssFsheet == null) {
continue;
}
System.out.println(xssFsheet.getLastRowNum());
int minColl = xssFrow.getFirstCellNum();
int MaxColl = xssFrow.getLastCellNum();
System.out.println(minColl + "***MaxColl&&&" + MaxColl);
List rowList = new ArrayList();
for (int colx = minColl; colx <= MaxColl; colx++) {
XSSFCell xssCell = xssFrow.getCell(colx);
if (xssCell == null) {
continue;
}
// 对于当前单元格的值处理。
String val = getResultCell(xssCell);
rowList.add(val);
}
System.out.println(JSONObject.toJSONString(rowList));
sheetList.add(rowList);
}
System.out.println(sheetList);
}
return sheetList;
}
public static String getResultCell(XSSFCell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
return null;
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_NUMERIC:
// 如果是時間格式的數字
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat da = new SimpleDateFormat("yyyy-MM-dd");
return da.format(cell.getDateCellValue());
} else {
// 保留两位小数点
DecimalFormat df = new DecimalFormat("#.##");
return df.format(cell.getNumericCellValue());
}
case Cell.CELL_TYPE_STRING:
// 空格所对应的ascii码为32 ,源码中显示如果为小于等于 "32"的则被认为是空格给去掉。
return cell.getStringCellValue().trim();
default:
break;
}
return "";
}
public static void main(String[] args) throws Exception {
List list = getPathExcel("D:\\Users\\111\\Desktop\\tsss.xlsx");
System.out.println(list);
outPathSql("D:\\Users\\111\\Desktop\\tsss.sql", list);
}
@SuppressWarnings({ "rawtypes" })
public static void outPathSql(String path, List list) throws IOException {
OutputStreamWriter out = new OutputStreamWriter(new FileOutputStream(new File(path)), "GBK");
StringBuffer sb = new StringBuffer();
// OutputStream out1 = new FileOutputStream(new File(path),true);
// out1.write(sb.toString().getBytes());
for (int a = 1; a < list.size(); a++) {
sb.append("select * from ssp_bas w where w.aaa = ");
List rowList = (List) list.get(a);
sb.append(rowList.get(0) + " and w.bbb= " + rowList.get(1) + " ;").append("\n");
}
out.write(sb.toString());
out.flush();
out.close();
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public static List getPathExcel(String Path) throws IOException {
// 读传入地址的文件
InputStream io = new FileInputStream(Path);
XSSFWorkbook xssF = new XSSFWorkbook(io);
// 遍历每一页的每一行
List sheetList = new ArrayList();
for (XSSFSheet xssFsheet : xssF) {
if (xssFsheet == null) {
continue;
}
System.out.println(xssFsheet.getLastRowNum());
for (int rowNum = 0; rowNum < xssFsheet.getLastRowNum(); rowNum++) {
// 获取每行的信息 并将每行的每个单元格信息存入List中
XSSFRow xssFrow = xssFsheet.getRow(rowNum);int minColl = xssFrow.getFirstCellNum();
int MaxColl = xssFrow.getLastCellNum();
System.out.println(minColl + "***MaxColl&&&" + MaxColl);
List rowList = new ArrayList();
for (int colx = minColl; colx <= MaxColl; colx++) {
XSSFCell xssCell = xssFrow.getCell(colx);
if (xssCell == null) {
continue;
}
// 对于当前单元格的值处理。
String val = getResultCell(xssCell);
rowList.add(val);
}
System.out.println(JSONObject.toJSONString(rowList));
sheetList.add(rowList);
}
System.out.println(sheetList);
}
return sheetList;
}
public static String getResultCell(XSSFCell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
return null;
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_NUMERIC:
// 如果是時間格式的數字
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat da = new SimpleDateFormat("yyyy-MM-dd");
return da.format(cell.getDateCellValue());
} else {
// 保留两位小数点
DecimalFormat df = new DecimalFormat("#.##");
return df.format(cell.getNumericCellValue());
}
case Cell.CELL_TYPE_STRING:
// 空格所对应的ascii码为32 ,源码中显示如果为小于等于 "32"的则被认为是空格给去掉。
return cell.getStringCellValue().trim();
default:
break;
}
return "";
}