导入依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17-beta1</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17-beta1</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17-beta1</version></dependency>
外围代码
import org.apache.poi.hssf.usermodel.HSSFWorkbook;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;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.math.BigDecimal;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.*;public class Excel{public List<Map<String,String>> read(String filePath,String columns[]) { Workbook wb =null; Sheet sheet = null; Row row = null; List<Map<String, String>> list = null; String cellData = null; wb = readExcel(filePath); if(wb != null){ //用来寄存表中数据 list = new ArrayList<Map<String,String>>(); //获取第一个sheet sheet = wb.getSheetAt(0); //获取最大行数 int rownum = sheet.getPhysicalNumberOfRows(); //获取第一行 row = sheet.getRow(0); //获取最大列数 int colnum = row.getPhysicalNumberOfCells(); for (int i = 1; i<rownum; i++) { Map<String,String> map = new LinkedHashMap<String,String>(); row = sheet.getRow(i); if(row !=null){ for (int j=0;j<colnum;j++){ Cell cell = row.getCell(j); cellData = getCellFormatValue(cell); map.put(columns[j], cellData); } }else{ break; } list.add(map); } } return list; }//读取excelpublic static Workbook readExcel(String filePath){ Workbook wb = null; if(filePath==null){ return null; } String extString = filePath.substring(filePath.lastIndexOf(".")); InputStream is = null; try { is = new FileInputStream(filePath); if(".xls".equals(extString)){ return wb = new HSSFWorkbook(is); }else if(".xlsx".equals(extString)){ return wb = new XSSFWorkbook(is); }else{ return wb = null; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return wb; }@SuppressWarnings("deprecation")public static String getCellFormatValue(Cell cell){ String cellValue = ""; if(cell == null){ return cellValue; } //把数字当成String来读，避免出现1读成1.0的状况 if(cell.getCellType()==0) { Date d=cell.getDateCellValue(); //如果是工夫，则转为yyyy-MM-dd的格局。能够依据本人的须要转化 DateFormat df= new SimpleDateFormat("yyyy-MM-dd"); cellValue=df.format(d); }else { if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ cell.setCellType(Cell.CELL_TYPE_STRING); } switch (cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC: //数字 cellValue = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: //字符串 cellValue = String.valueOf(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: //Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: //公式 cellValue = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: //空值 cellValue = ""; break; case Cell.CELL_TYPE_ERROR: //故障 cellValue = "非法字符"; break;default: cellValue = "未知类型"; break; } } return cellValue; } public static void main(String[] args){ Excel ex = new Excel(); String c[]= {"stuId","stuName","userName","carNo", "carNo2","userPhone","carNumber","remark"}; List<Map<String,String>> list=ex.read("文件门路",c); DateFormat df= new SimpleDateFormat("yyyy-MM-dd"); for (Map<String,String> map : list) { //通过健去获取值 String carNo = map.get("carNo"); } }}
去除数据库中某个字段雷同，其余字段不雷同的记录
创立两头表，并获取值
create table tmp as select min(主键) as col1 from 去重表名 GROUP BY 去重字段;
删除反复值，已两头表中记录作为条件
delete from 去重表名 where 主键 not in (select col1 from tmp);
删除两头表
drop table tmp;