读取Excel文件中的数据，并写到MySQL数据库

della0887

读取Excel文件中的数据，并写到MySQL数据库

首先要下载相应的包，具体的包如下：
pinyin4j-2.5.0.jar
poi-3.2-FINAL-20081019.jar
poi-contrib-3.2-FINAL-20081019.jar
poi-scratchpad-3.2-FINAL-20081019.jar
mysql-connector-java-5.1.10-bin.jar


package org.wcy.test;

//Function Read data from Excel
//Author ATGC
//Date of compilation Oct 29,2004

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;

import org.apache.poi.hssf.record.formula.functions.Cell;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/*
* 该类为读取excel文件，并导入到数据库
*/
public class ReadExcelToDB {

public static String fileName= "E:\\text.xls";

@SuppressWarnings("deprecation")
public static void main(String argv[]) {
Connection con = null;
PreparedStatement ps = null;


try {
con = DBTools.getConnection();
//把一张*.xls的数据表读到workbook里面
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileName));
//读取第一个sheet工作表
HSSFSheet sheet = workbook.getSheetAt(0);

//获取工作表的最后一条记录
int rows = sheet.getLastRowNum();
for (int r = 0; r < rows+1; r++) {
//获取当前行数
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = 4;
String value = "";
for (short c = 0; c < cells; c++) {
//获取当前行的列数
HSSFCell cell = row.getCell(c);
if (cell != null) {
//判断cell的类型
switch (cell.getCellType()) {
//如果当前cell的数据类型为公式型
case HSSFCell.CELL_TYPE_FORMULA:
break;
// 如果当前Cell的数据类型为数值型  
case HSSFCell.CELL_TYPE_NUMERIC:
value += String.valueOf((long)cell.getNumericCellValue())+",";
value.toString();
break;
//如果当前cell的数据类型为字符串类型
case HSSFCell.CELL_TYPE_STRING:
value += cell.getStringCellValue() + ",";
break;
//如果当前cell的数据类型为空值
case HSSFCell.CELL_TYPE_BLANK:
break;
//默认
default:
value += ",";
}
}
}

HSSFCell cell = row.getCell((short)1);
int cell0 = ExcelUtils.getIntCellValue(row, 0);
int cell2 = ExcelUtils.getIntCellValue(row, 2);
//    下面可以获取汉字拼音的首字母
String firstPy = PinyinToolkit.cn2FirstSpell(cell.getStringCellValue()).toUpperCase();
System.out.println(firstPy);

sql = "insert into t_employ(code,name,provinceCode,provinceName,namePy) values(?,?,?,?,?)";
ps = con.prepareStatement(sql);

for(int i=0;i<4;i++){
if(ExcelUtils.getStringValue(row,i)==null){
System.out.println(ExcelUtils.getStringValue(row,i));
}else{
ps.setString(i+1, ExcelUtils.getStringValue(row,i));
ps.setString(5, firstPy);
}
ps.setInt(1, cell0);
ps.setInt(3, cell2);
System.out.print(ExcelUtils.getStringValue(row,i)+"/");
}
System.out.println("");
ps.executeUpdate();

System.out.println(value);
}
}
} catch (Exception e) {
System.out.println(e);
}
}
}


//数据库的连接

DBTools.java 类

package org.wcy.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBTools {

public static Connection getConnection() {
Connection con = null;
try {
String url = "jdbc:mysql://localhost:3306/testxls?useUnicode=true&charsetEncoding=UTF-8";
String username = "root";
String password = "123456";
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}

public static void close(Connection con) {
try {
if(con!=null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void close(ResultSet rs) {
try {
if(rs!=null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void close(PreparedStatement ps) {
try {
if(ps!=null) ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}




ExcelUtil.java 类

package org.wcy.test;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;

public class ExcelUtils {

public static int getIntCellValue(HSSFRow row,int index){
int rtn = 0;
try {
HSSFCell cell = row.getCell(index);
rtn = (int) cell.getNumericCellValue();
} catch (Exception e) {
}
return rtn;
}

public static String getStringValue(HSSFRow row,int index){
String rtn ="";
try {
HSSFCell cell = row.getCell(index);
rtn = cell.getRichStringCellValue().getString();
} catch (Exception e) {
}
return rtn;
}

}

//该类是用于取出汉字拼音的首字母

PinyinToolkit.java 类

package org.konghao.student.util;

import java.io.UnsupportedEncodingException;


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 PinyinToolkit {

/**
* 获取汉字串拼音首字母，英文字符不变
*
* @param chinese
*            汉字串
* @return 汉语拼音首字母
*/
public static String cn2FirstSpell(String chinese) {
StringBuffer pybf = new StringBuffer();
char[] arr = chinese.toCharArray();
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
for (int i = 0; i < arr.length; i++) {
if (arr > 128) {
try {
String[] _t = PinyinHelper.toHanyuPinyinStringArray(arr,
defaultFormat);
if (_t != null) {
pybf.append(_t[0].charAt(0));
}
} catch (BadHanyuPinyinOutputFormatCombination e) {
e.printStackTrace();
}
} else {
pybf.append(arr);
}
}
return pybf.toString().replaceAll("\\W", "").trim();
}

/**
* 获取汉字串拼音，英文字符不变
*
* @param chinese
*            汉字串
* @return 汉语拼音
*/
public static String cn2Spell(String chinese,String split) {
StringBuffer pybf = new StringBuffer();
char[] arr = chinese.toCharArray();
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
for (int i = 0; i < arr.length; i++) {
try {
if(isChinese(arr))
pybf.append(PinyinHelper.toHanyuPinyinStringArray(arr,
defaultFormat)[0]).append(split);
} catch (BadHanyuPinyinOutputFormatCombination e) {
e.printStackTrace();
}
}
return pybf.toString();
}

public static void main(String[] args) throws UnsupportedEncodingException {
String x = "SQL Server数据库技术（60) - 资源吧源码";//
System.out.println(cn2FirstSpell(x));
System.out.println(cn2Spell(x,","));
}
/**
* 判断是否为汉字
* @param c
* @return
*/
private static boolean isChinese(char c) {
if (Character.toString(c).matches("[\\u4E00-\\u9FA5]+"))
return true;
return false;
}
}