java c s 差入数据到excel总_java搞定excel导入数据到SqlServer

## 我的需求是：

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


## 解决步骤

### Excel解决空值方式

[excel定位空值](https://blog.csdn.net/swazer_z/article/details/72845525)

### Excel中文转换拼音

[excel中文转拼音](https://jingyan.baidu.com/article/0a52e3f45025d6ff63ed7204.html)

### java中文转换拼音

需要导入pinyin4j.jar包。


```java

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包


```java

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包


```java

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("数据库连接失败");

}

}

}

```

### 搭建关联

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

0.0分

1 人评分