java搞定excel导入数据到SqlServer

我的需求是：

导入用户=》数据库中有的用户不用导入：：没有账号的导入姓名对应的拼音

在这里插入图片描述

解决步骤

Excel解决空值方式

excel定位空值

Excel中文转换拼音

excel中文转拼音

java中文转换拼音

需要导入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("汉字转换为拼音").toUpperCase()); //转为首字母大写
            System.out.println(ToPinyin("汉字转换为拼音")); 
        }
        /**
         * 获取字符串拼音的第一个字母
         * @param chinese
         * @return
         */
        public static String ToFirstChar(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].charAt(0);  
                    } catch (BadHanyuPinyinOutputFormatCombination e) {  
                        e.printStackTrace();  
                    }  
                }else{  
                    pinyinStr += newChar[i];  
                }  
            }  
            return pinyinStr;  
        }  
       
        /**
         * 汉字转为拼音
         * @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输出数据

需要导入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");
    }

    /**
     * 查询指定目录中电子表格中所有的数据
     * 
     * @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++) {
                    // 第一个是列数，第二个是行数
                    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连接数据库

需要导入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 {
                // 连接数据库
                conn = DriverManager.getConnection(url, user, password);
                // 建立Statement对象
                stmt = conn.createStatement();
                // 执行数据库查询语句
                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("数据库连接失败");
            }
        }
}

搭建关联

将上面的方法建立下联系就完成