java 代码处理excle到mongodb

package com.pistonint.nlp.module.test;




import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

public class test {

    public static String tagFile = "C:\\Users\\admin\\Desktop\\data_final_20210301_3.xlsx";
    public static InputStream in;
    public static XSSFWorkbook srcExcel;

    static {
        try {
            in = new FileInputStream(tagFile);
            srcExcel = new XSSFWorkbook(in);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static XSSFSheet sheet = srcExcel.getSheet("Sheet1");
    public static List<String> titleList = Arrays.asList(
            "id",
            "location",
            "school",
            "class",
            "sex",
            "netWords",
            "copyScore",
            "theme",
            "solveProblem",
            "job",
            "wish",
            "emotion1",
            "emotion2",
            "emotion3",
            "invent",
            "inventScore"
    );

    public static void main(String[] args) {
        HashMap dbPara = new HashMap();
        dbPara.put("etlip", "192.168.1.98");
        dbPara.put("etlport", Integer.valueOf("27017"));
        dbPara.put("etluserName", "yckPricing_rw");
        dbPara.put("etlauthDBName", "yckPricing");
        dbPara.put("etlpwd", "Xvd2YKOpYK9MeZQL");
        dbPara.put("etlusedb", "yckPricing");


        MongoDBUtil util = new MongoDBUtil();

        MongoClient etlMongoClient = util.getMongoClient(
                dbPara.get("etlip").toString(),
                Integer.parseInt(dbPara.get("etlport").toString()),
                dbPara.get("etluserName").toString(),
                dbPara.get("etlauthDBName").toString(),
                dbPara.get("etlpwd").toString()
        );
        MongoDatabase newcarDB = etlMongoClient.getDatabase(dbPara.get("etlusedb").toString());
        MongoCollection coll = newcarDB.getCollection("nlp"); // 标准表 //mix_standard

        coll.drop();
        ArrayList<Document> doclist = new ArrayList<>();
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Document document = new Document();
            XSSFRow row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();

            for (int col = row.getFirstCellNum(); col < lastCellNum; col++) {
                XSSFCell cell = row.getCell(col);//col对应的列值
                XSSFCell cellHead = sheet.getRow(0).getCell(col);

                String cellV = null;
                if (cell != null) {
                    cellV = cell.toString();
                }

                if ("id".equals(cellHead.toString())) {
                    document.append("_id",cellV);

                }


                if ("theme".equals(cellHead.toString())) {
                    List<String> topicsList = null;
                    String s = cellV;
                    if (s != null) {
                        String[] split = s.split(",");
                        for (int i = 0; i < split.length; i++) {
                            split[i] = split[i].trim();
                        }
                        topicsList = Arrays.asList(split);
                    }
                    document.append("theme", topicsList);
                } else if ("solveProblem".equals(cellHead.toString())) {
                    List<String> topicsList = null;
                    String s = cellV;
                    if (s != null) {
                        String[] split = s.split(",");
                        for (int i = 0; i < split.length; i++) {
                            split[i] = split[i].trim();
                        }
                        topicsList = Arrays.asList(split);
                    }
                    document.append("solveProblem", topicsList);
                } else if ("job".equals(cellHead.toString())) {
                    List<String> topicsList = null;
                    String s = cellV;
                    if (s != null) {
                        String[] split = s.split(",");
                        for (int i = 0; i < split.length; i++) {
                            split[i] = "".equals(split[i].trim()) ? null : split[i].trim();
                        }
                        topicsList = Arrays.asList(split);
                    }
                    document.append("job", topicsList);
                } else if ("wish".equals(cellHead.toString())) {
                    List<String> topicsList = null;
                    String s = cellV;
                    if (s != null) {
                        s = s.replaceAll("^\\(", "")
                                .replaceAll("\\)$", "");
                        String[] split = s.split(",");
                        for (int i = 0; i < split.length; i++) {
                            split[i] = "".equals(split[i].trim()) ? null : split[i].trim();
                        }
                        topicsList = Arrays.asList(split);
                    }
                    document.append("wish", topicsList);
                } else if ("invent".equals(cellHead.toString())) {
                    List<String> topicsList = null;
                    String s = cellV;
                    if (s != null) {
                        String[] split = s.split(",");
                        for (int i = 0; i < split.length; i++) {
                            split[i] = split[i].trim();
                        }
                        topicsList = Arrays.asList(split);
                    }
                    document.append("invent", topicsList);
                } else if ("inventScore".equals(cellHead.toString())) {
                    List<String> topicsList = null;
                    String s = cellV;
                    if (s != null) {
                        String[] split = s.split(",");
                        for (int i = 0; i < split.length; i++) {
                             split[i] = split[i].trim();
                        }
                        topicsList = Arrays.asList(split);
                    }
                    document.append("inventScore", topicsList);
                } else {
                    document.append(cellHead.toString(), cellV);
                }


            }
            document.remove("id");
            System.out.println(document);
            doclist.add(document);

        }

        coll.insertMany(doclist);

        if (etlMongoClient!=null) {
            etlMongoClient.close();
        }
    }
}

package com.pistonint.nlp.module.test;

import com.mongodb.*;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.UpdateOptions;
import org.bson.Document;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MongoDBUtil {

	private final static MongoClientOptions MONGO_CLIENT_OPTIONS = new MongoClientOptions.Builder()
			.cursorFinalizerEnabled(true)
			.writeConcern(WriteConcern.ACKNOWLEDGED)
			// Default is WriteConcern.NORMAL
			.readPreference(ReadPreference.secondaryPreferred())
			.connectionsPerHost(100)
			// Default is 100
			.minConnectionsPerHost(5)
			.threadsAllowedToBlockForConnectionMultiplier(10)
			// Default is 5
			.maxWaitTime(120000)
			// Default is 120000
			.socketKeepAlive(true).socketTimeout(0).connectTimeout(30000)
			.cursorFinalizerEnabled(true).build();

	public MongoClient getMongoClient(String ip, int port, String userName,
			String authDBName, String pwd) {

		ServerAddress serverAddress = new ServerAddress(ip, port);
		List addrsList = new ArrayList();
		addrsList.add(serverAddress);

		MongoCredential credential = MongoCredential.createScramSha1Credential(
				userName, authDBName, pwd.toCharArray());
		List credentials = new ArrayList();
		credentials.add(credential);

		return new MongoClient(addrsList, credentials, MONGO_CLIENT_OPTIONS);

	}

	public MongoClient getMongoClient(List addsList, String userName,
			String authDBName, String pwd) {

		MongoCredential credential = MongoCredential.createScramSha1Credential(
				userName, authDBName, pwd.toCharArray());
		List credentials = new ArrayList();
		credentials.add(credential);

		return new MongoClient(addsList, credentials, MONGO_CLIENT_OPTIONS);

	}

    /**
    * 根据条件删除数据，可多条件
    */
	public void deleteManyByCondition(Map dbPara,Document doc){

		MongoDBUtil util = new MongoDBUtil();
        MongoClient mongoClient = util.getMongoClient(dbPara.get("ip").toString(),
                Integer.parseInt(dbPara.get("port").toString()),
                dbPara.get("userName").toString(), dbPara.get("authDBName")
                        .toString(), dbPara.get("pwd").toString());
        MongoDatabase mongoDatabase = mongoClient.getDatabase(dbPara.get("usedb")
                .toString());

        MongoCollection collection = mongoDatabase.getCollection(dbPara.get("collectionName").toString());
        collection.deleteMany(doc);
	}

    /**
     * 根据条件删除数据，可多条件
     */
	public void updateManyByCondition(Map dbPara, Document filterDoc, Document updateDoc,boolean isUpsert){
		MongoDBUtil util = new MongoDBUtil();
		MongoClient mongoClient = util.getMongoClient(dbPara.get("ip").toString(),
				Integer.parseInt(dbPara.get("port").toString()),
				dbPara.get("userName").toString(), dbPara.get("authDBName")
						.toString(), dbPara.get("pwd").toString());
		MongoDatabase mongoDatabase = mongoClient.getDatabase(dbPara.get("usedb")
				.toString());

		MongoCollection collection = mongoDatabase.getCollection(dbPara.get("collectionName").toString());

		Document setDoc = new Document("$set", updateDoc);
		collection.updateMany(filterDoc,setDoc, new UpdateOptions().upsert(isUpsert));
	}

	public static void main(String[] args) {
		Map dbPara = new HashMap();
		dbPara.put("ip", "120.77.40.80");
        dbPara.put("port", Integer.valueOf("28018"));
        dbPara.put("userName", "NewCarReadNWrite");
        dbPara.put("pwd", "readnwriteshuding1101");
        dbPara.put("authDBName", "newcar");
        dbPara.put("usedb", "newcar");
        dbPara.put("collectionName","tco_price_standard");

        Document doc = new Document("Year","2017").append("Month", "1");

        MongoDBUtil dbUtil = new MongoDBUtil();
        dbUtil.deleteManyByCondition(dbPara, doc);
	}
}