关于java:java读取excel表中的数据

导入依赖

<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;
        }
// 读取 excel
public 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;