我的需求是：

導(dǎo)入用戶=》數(shù)據(jù)庫中有的用戶不用導(dǎo)入 ：：沒有賬號的導(dǎo)入姓名對應(yīng)的拼音

在這里插入圖片描述

解決步驟

Excel解決空值方式

excel定位空值

Excel中文轉(zhuǎn)換拼音

excel中文轉(zhuǎn)拼音

java中文轉(zhuǎn)換拼音

需要導(dǎo)入pinyin4j.jar包。

image.png

package www.yzq.com.tool;

import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;

public class HanToPin {

        /**
         * 測試main方法
         * @param args
         */
        public static void main(String[] args) {
            System.out.println(ToFirstChar("漢字轉(zhuǎn)換為拼音").toUpperCase()); //轉(zhuǎn)為首字母大寫
            System.out.println(ToPinyin("漢字轉(zhuǎn)換為拼音")); 
        }
        /**
         * 獲取字符串拼音的第一個字母
         * @param chinese
         * @return
         */
        public static String ToFirstChar(String chinese){         
            String pinyinStr = "";  
            char[] newChar = chinese.toCharArray();  //轉(zhuǎn)為單個字符
            HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat(); 
            defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);  
            defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);  
            for (int i = 0; i < newChar.length; i++) {  
                if (newChar[i] > 128) {  
                    try {  
                        pinyinStr += PinyinHelper.toHanyuPinyinStringArray(newChar[i], defaultFormat)[0].charAt(0);  
                    } catch (BadHanyuPinyinOutputFormatCombination e) {  
                        e.printStackTrace();  
                    }  
                }else{  
                    pinyinStr += newChar[i];  
                }  
            }  
            return pinyinStr;  
        }  
       
        /**
         * 漢字轉(zhuǎn)為拼音
         * @param chinese
         * @return
         */
        public static String ToPinyin(String chinese){          
            String pinyinStr = "";  
            char[] newChar = chinese.toCharArray();  
            HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();  
            defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);  
            defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);  
            for (int i = 0; i < newChar.length; i++) {  
                if (newChar[i] > 128) {  
                    try {  
                        pinyinStr += PinyinHelper.toHanyuPinyinStringArray(newChar[i], defaultFormat)[0];  
                    } catch (BadHanyuPinyinOutputFormatCombination e) {  
                        e.printStackTrace();  
                    }  
                }else{  
                    pinyinStr += newChar[i];  
                }  
            }  
            return pinyinStr;  
        }  
        
}

java連接excel輸出數(shù)據(jù)

需要導(dǎo)入jxl.jar包

image.png

package www.yzq.com.tool;

import java.io.File;

import jxl.Sheet;
import jxl.Workbook;

public class ExcelImport {
    public static void main(String[] args) {
        ExcelImport excelImport = new ExcelImport();
        excelImport.getAllByExcel("c://dfs.xls");
    }

    /**
     * 查詢指定目錄中電子表格中所有的數(shù)據(jù)
     * 
     * @param file
     *            文件完整路徑
     * @return
     */
    public static void getAllByExcel(String file) {
        try {
            Workbook rwb = Workbook.getWorkbook(new File(file));
            Sheet rs = rwb.getSheet(0);// 或者rwb.getSheet(0)
            int clos = rs.getColumns();// 得到所有的列
            int rows = rs.getRows();// 得到所有的行

            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < clos; j++) {
                    // 第一個是列數(shù)，第二個是行數(shù)
                    String id = rs.getCell(j++, i).getContents();// 默認最左邊編號也算一列 所以這里得j++
                    String name = rs.getCell(j++, i).getContents();
                    String sex = rs.getCell(j++, i).getContents();
                    String num = rs.getCell(j, i).getContents();
                    
                    System.out.println("id:" + id + " name:" + name + " sex:" + sex + " num:" + num);
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

java連接數(shù)據(jù)庫

需要導(dǎo)入jar包

image.png

package www.yzq.com.tool;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class LinkSqlserver {
        public static void main(String[] args) {
            String user = "cczu";
            String password = "cczucczu";
            Connection conn;
            Statement stmt;
            ResultSet rs;
            String url = "jdbc:sqlserver://192.168.1.99:1433;DatabaseName=QY_NTXC;";
            String sql = "select * from t_user";
            try {
                // 連接數(shù)據(jù)庫
                conn = DriverManager.getConnection(url, user, password);
                // 建立Statement對象
                stmt = conn.createStatement();
                // 執(zhí)行數(shù)據(jù)庫查詢語句
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    String id = rs.getString("LOGIN_NAME");
                    String name = rs.getString("NAME");
                    String score = rs.getString("EMAIL");
                    String sex = rs.getString("PHONE");
                    System.out.println("登錄名： "+id+"昵稱"+name+"郵箱 "+score+"電話"+sex);
                }
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("數(shù)據(jù)庫連接失敗");
            }
        }
}

搭建關(guān)聯(lián)

將上面的方法建立下聯(lián)系就完成