java操作excel中的sheet和word中的表格，导入mysql

import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellType; 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.InputStream; public class ReadExcelhuiyishizhegnligeshi { public static void main(String[] args) throws Exception { excel(); } public static void excel() throws Exception { File dir = new File("F:\\myWorkFiles\\XXXXXXX\\2022年10月8日，导入数据\\数据\\临时\\会议室资源 - 整理数据格式\\最终"); File[] files = dir.listFiles(); for (File file : files) { if (!file.exists()) { throw new Exception("文件不存在!"); } if(file.getPath().contains("$")){ continue; } InputStream in = new FileInputStream(file); if(file.getPath().endsWith(".xlsx")){ // 读取整个Excel XSSFWorkbook sheets = new XSSFWorkbook(in); // 获取第一个表单Sheet XSSFSheet sheetAt = sheets.getSheetAt(0); for (int i = 0; i < 503; i++) { String id = "huiyishi"+i; String bangongqu = sheetAt.getRow(i).getCell(0).getStringCellValue().replaceAll("\\s*", ""); sheetAt.getRow(i).getCell(1).setCellType(CellType.STRING); String roomName = sheetAt.getRow(i).getCell(1).getStringCellValue().replaceAll("\\s*", ""); String location = sheetAt.getRow(i).getCell(2).getStringCellValue().replaceAll("\\s*", ""); String smallType = sheetAt.getRow(i).getCell(3).getStringCellValue().replaceAll("\\s*", ""); Object remark; if(sheetAt.getRow(i).getCell(4)!= null){ if(sheetAt.getRow(i).getCell(4).getCellType().equals(CellType.NUMERIC)){ sheetAt.getRow(i).getCell(4).setCellType(CellType.NUMERIC); remark = sheetAt.getRow(i).getCell(4).getNumericCellValue(); }else { sheetAt.getRow(i).getCell(4).setCellType(CellType.STRING); remark = sheetAt.getRow(i).getCell(4).getStringCellValue().replaceAll("\\s*", ""); } }else{ remark = ""; } String companyId= sheetAt.getRow(i).getCell(5).getStringCellValue().replaceAll("\\s*", ""); String baozhangdanwei = sheetAt.getRow(i).getCell(6).getStringCellValue().replaceAll("\\s*", ""); // System.out.println("huiyishi"+i+"，"+bangongqu+"，"+roomName+"，"+location+"，"+smallType+"，"+remark+"，"+companyId+ // "，"+baozhangdanwei); // System.out.println("INSERT INTO tab_resource (id,resource_name,big_type,small_type,office_area," + // "resource_location,company_id,create_time)VALUES('"+id+"','"+roomName+"'," + // "'1503605963402776502','"+smallType+"'," + // "'"+bangongqu+"','"+location+"','"+companyId+"',now());"); String fuzerenName1 = sheetAt.getRow(i).getCell(7).getStringCellValue().replaceAll("\\s*", ""); sheetAt.getRow(i).getCell(8).setCellType(CellType.STRING); String fuzerenphone1 = sheetAt.getRow(i).getCell(8).getStringCellValue().replaceAll("\\s*", ""); if(!"".equals(fuzerenName1)){ // System.out.println(fuzerenName1+"，"+fuzerenphone1); System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES ( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone1+"' );"); } String fuzerenName2 = "",fuzerenphone2 = ""; if(sheetAt.getRow(i).getCell(9)!= null){ fuzerenName2 = sheetAt.getRow(i).getCell(9).getStringCellValue().replaceAll("\\s*", ""); sheetAt.getRow(i).getCell(10).setCellType(CellType.STRING); fuzerenphone2 = sheetAt.getRow(i).getCell(10).getStringCellValue().replaceAll("\\s*", ""); } if(!"".equals(fuzerenName2)){ // System.out.println(fuzerenName2+"，"+fuzerenphone2); System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES ( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone2+"' );"); } String fuzerenName3 = "",fuzerenphone3 = ""; if(sheetAt.getRow(i).getCell(11)!= null){ fuzerenName3 = sheetAt.getRow(i).getCell(11).getStringCellValue().replaceAll("\\s*", ""); sheetAt.getRow(i).getCell(12).setCellType(CellType.STRING); fuzerenphone3 = sheetAt.getRow(i).getCell(12).getStringCellValue().replaceAll("\\s*", ""); } if(!"".equals(fuzerenName3)){ // System.out.println(fuzerenName3+"，"+fuzerenphone3); System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES ( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone3+"' );"); } String fuzerenName4 = "",fuzerenphone4=""; if(sheetAt.getRow(i).getCell(13) != null){ fuzerenName4 = sheetAt.getRow(i).getCell(13).getStringCellValue().replaceAll("\\s*", ""); sheetAt.getRow(i).getCell(14).setCellType(CellType.STRING); fuzerenphone4 = sheetAt.getRow(i).getCell(14).getStringCellValue().replaceAll("\\s*", ""); } if(!"".equals(fuzerenName4)){ // System.out.println(fuzerenName4+"，"+fuzerenphone4); System.out.println("INSERT INTO tab_resource_people ( id, resource_id, duty_type, user_id ) VALUES ( uuid(), '"+id+"', 'zrdw100', '"+fuzerenphone4+"' );"); } // System.out.println("====================================="); } } } } }