用java怎么将excel表格数据导入到mysql数据库中

参考下面方法：

import javaioFileInputStream;­

import javaioFileNotFoundException;­

import javaioIOException;­

import orgapachecommonsloggingLog;­

import orgapachecommonsloggingLogFactory;­

import orgapachepoihssfusermodelHSSFCell;­

import orgapachepoihssfusermodelHSSFRow;­

import orgapachepoihssfusermodelHSSFSheet;­

import orgapachepoihssfusermodelHSSFWorkbook;­

public class TestExcel {­

//记录类的输出信息­

static Log log = LogFactorygetLog(TestExcelclass); ­

//获取Excel文档的路径­

public static String filePath = "D://excelxls";­

public static void main(String[] args) {­

try {­

// 创建对Excel工作簿文件的引用­

HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));­

// 在Excel文档中，第一张工作表的缺省索引是0

// 其语句为：HSSFSheet sheet = workbookgetSheetAt(0);­

HSSFSheet sheet = wookbookgetSheet("Sheet1");­

//获取到Excel文件中的所有行数­

int rows = sheetgetPhysicalNumberOfRows();

//遍历行­

for (int i = 0; i < rows; i++) {­

// 读取左上端单元格­

HSSFRow row = sheetgetRow(i);­

// 行不为空­

if (row != null) {­

//获取到Excel文件中的所有的列­

int cells = rowgetPhysicalNumberOfCells();­

String value = ""; ­

//遍历列­

for (int j = 0; j < cells; j++) {­

//获取到列的值­

HSSFCell cell = rowgetCell(j);­

if (cell != null) {­

switch (cellgetCellType()) {­

case HSSFCellCELL_TYPE_FORMULA:­

break;­

case HSSFCellCELL_TYPE_NUMERIC:­

value += cellgetNumericCellValue() + ","; ­

break; ­

case HSSFCellCELL_TYPE_STRING:­

value += cellgetStringCellValue() + ",";­

break;­

default:­

value += "0";­

break;­

}­

}

}­

// 将数据插入到mysql数据库中­

String[] val = valuesplit(",");­

TestEntity entity = new TestEntity();­

entitysetNum1(val[0]);­

entitysetNum2(val[1]);­

entitysetNum3(val[2]);­

entitysetNum4(val[3]);­

entitysetNum5(val[4]);­

entitysetNum6(val[5]);­

TestMethod method = new TestMethod();­

methodAdd(entity);­

}­

}­

} catch (FileNotFoundException e) {­

eprintStackTrace();­

} catch (IOException e) {­

eprintStackTrace();­

}­

}­

}­

你是说将java中集合的所有元素插入到数据库中吗？

List<String> list = new ArrayList<String>();

listadd("元素1");

listadd("元素2");

listadd("元素3");

listadd("元素4");

for(int i=0;i<listsize();i++){

String item = listget(i);

String sql = "insert into table test(test)values(item)";

int flag = save(sql);//如果flag == 1则表示数据插入数据库成功，否则表示插入失败

}

//注释：你只需要写一个插入数据到数据库的方法save(String sql)即可。

连接数据库简单步骤:

导入驱动包（mysql官网有下），并添加该包为库（add as Library）

注册驱动：ClassforName("commysqljdbcDriver");

获取连接对象：Connection conn=DirverManagergetConnection("jdbc:mysql://连接IP/数据库名","数据库用户名","密码");

获取sql语句执行对象：Statement stmt=conncreateStatement();

使用对象运行sql语句：如 stmtexecuteUpdate("Update A set a=b");

释放资源：stmtclose();connclose();

也可以用数据库连接池，获得连接，但是要下载相关的jar包，如：Druidjar

要想写更简单的代码，可以用spring框架，下载相应的jar包就可以了

Java中将txt文件导入到mysql基本的思路就是先使用I/O *** 作流获取到文件具体信息，然后将信息拼接成mysql插入到数据库中，示例如下：

1、先读取txt文件的内容，文件内容可以按照一定的规律进行排列，这样程序读取就方便。

import javaioIOException;

import javaioRandomAccessFile;

import javaioUnsupportedEncodingException;

import javatextSimpleDateFormat;

import javautilDate;

import javautilregexMatcher;

import javautilregexPattern;

import comconfigConstants;

import comutilsUUIDUtil;

/

txt文本数据 采集类

@see

/

public class UserDataGather {

public static final String TXT_FILE_PATH = "D://testUsertxt";

public static final String openFileStyle = "r";

public static final String fieldLimitChar = "";

public static final int fieldAllCount = 1;

public static final String default_password = "PTMD0309";

public Integer count = 0;

private String FltNum;

public String UUID;

/

功能：解析文本文件

/

public void loadFile() {

try {

RandomAccessFile raf = new RandomAccessFile(TXT_FILE_PATH, openFileStyle);

String line_record = rafreadLine();

while (line_record != null) {

// 解析每一条记录

parseRecord(line_record);

line_record = rafreadLine();

}

} catch (Exception e) {

eprintStackTrace();

}

}

/

功能：具体解析每一条记录，这里可以增加很多对记录的解析判断条件，如是否为字母、

/

@SuppressWarnings("static-access")

private void parseRecord(String line_record) throws Exception {

//拆分记录

// String[] fields = line_recordsplit(fieldLimitChar);

// Systemoutprintln(tranStr(line_record)+"Ok");

String temp = line_recordsubstring(line_recordindexOf(fieldLimitChar, 0), line_recordindexOf(" ", line_recordindexOf(fieldLimitChar, 0)));

// if (fieldslength == fieldAllCount) {

//

FltNum = tranStr(temp)trim()replace(fieldLimitChar,"")replace(" ","");

// Systemoutprintln(FltNum);

if(FltNumlength()>=4){

if(!isNumeric(FltNum)){

// Systemoutprintln(generateSql(FltNum)[0]toString());

// Systemoutprintln(generateSql(FltNum)[1]toString());

count++;

String[] temp1 = generateSql(FltNum);

MyFile mf = new MyFile();

mfcreatTxtFile("insertPinTuUserSql");

mfwriteTxtFile(temp1[0]toString());

mfcreatTxtFile("UUID");

mfwriteTxtFile(temp1[1]toString()+",");

}

}else if(FltNumlength() ==2 || FltNumlength() ==3){

if(!isNumeric(FltNum)){

if(!isTwoCharacter(FltNum)){

// Systemoutprintln(generateSql(FltNum)[0]toString());

// Systemoutprintln(generateSql(FltNum)[1]toString());

count++;

String[] temp2 = generateSql(FltNum);

MyFile mf = new MyFile();

mfcreatTxtFile("insertPinTuUserSql");

mfwriteTxtFile(temp2[0]toString());

mfcreatTxtFile("UUID");

mfwriteTxtFile(temp2[1]toString()+",");

}

}

}

// InsertDB db = new InsertDB();

//

// dbinsertDB(FltNum);

// }

}

@SuppressWarnings("static-access")

public String[] generateSql(String userName) throws IOException{

StringBuffer sbf = new StringBuffer();

String[] str = new String[2];

String uuid = UUIDUtilgetUUID();

sbfappend("insert into user values('"+uuid+"','" + userName +"','"+default_password+"',"+ConstantsENABLED+","+ConstantsNUllDELETE+","+ConstantsAUDITING+",'"+uuid+"@164com','"+formatDateTime()+"',"+ConstantsREGEDIT_USER+");/n");

sbfappend("insert into users values('"+uuid+"',"+ null+","+ConstantsMALE+","+null+","+null+",'6017636250','"+formatDateTime()+"',"+null+","+null+","+null+","+null+","+null+","+null+",0,"+null+","+null+",0,0,0,'"+formatDateTime()+"','1036',0,"+null+","+null+","+null+","+null+","+null+",'11',"+null+","+null+","+null+","+null+","+null+");/n");

sbfappend("insert into user_user_group values('"+uuid+"','"+ uuid +"','"+ConstantsPERSONAL_USER+"');/n");

UUID = uuid;

str[0]=sbftoString();

str[1]=UUID;

return str;

}

public String formatDateTime(){

Date date = new Date();

/

时间格式化2009-12-31 09:04:31

/

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

Systemoutprintln(sdfformat(date));

return sdfformat(date);

}

private String tranStr(String oldstr) {

String newstr = "";

try {

newstr = new String(oldstrgetBytes("ISO-8859-1"), "utf-8");

} catch (UnsupportedEncodingException e) {

eprintStackTrace();

}

return newstr;

}

public static boolean isNumeric(String str){

Pattern pattern = Patterncompile("[0-9]");

return patternmatcher(str)matches();

}

public static boolean isTwoCharacter(String str){

String regEx="[a-zA-Z0-9]{2,3}";

Pattern p=Patterncompile(regEx);

Matcher m=pmatcher(str);

return mfind();

}

}

2、连接数据库执行数据导入

import javasqlDriverManager;

import javasqlResultSet;

import commysqljdbcConnection;

import commysqljdbcStatement;

/

插入数据库 生成sql语句

created on Mar 8, 2010

@see

/

public class InsertDB {

private static final String user = "pintu";

private static final String pwd = "pintu";

private static final String url = "jdbc:mysql://192168106:3306/pintu";

private static final String driver = "commysqljdbcDriver";

public static Connection getCon() {

Connection con = null;

try {

ClassforName(driver)newInstance();

con = (Connection) DriverManagergetConnection(url, user, pwd);

if (con != null) {

Systemoutprintln("你已连接到数据库：" + congetCatalog());

}

} catch (Exception e) {

Systemoutprintln("连接数据库失败！");

eprintStackTrace();

}

return con;

}

public boolean insertDB(String FltNum) {

Connection con = null;

Statement stm = null;

boolean flag = false;

String sql = "insert into t_FltPsgInfo values('" + FltNum +

// "','"

// + FltLine + "','" + FltDate + "','" + PsgName + "','" + PsgType

//

// + "','" + PsgSex + "','" + PsgCab + "','" + PsgSeatNo + "','"

//

// + PsgInfo +

"')";

try {

con = getCon();

stm = (Statement) concreateStatement();

int i = stmexecuteUpdate(sql);

if (i > 0) {

flag = true;

Systemoutprintln(flag + "插入数据成功！");

}

} catch (Exception e) {

flag = false;

eprintStackTrace();

} finally {

close(null, stm, con);

}

return flag;

}

//关闭相关连接

public void close(ResultSet rs, Statement stm, Connection con) {

if (rs != null)

try {

rsclose();

} catch (Exception e) {

eprintStackTrace();

}

if (stm != null)

try {

stmclose();

} catch (Exception e) {

eprintStackTrace();

}

if (con != null)

try {

conclose();

} catch (Exception e) {

eprintStackTrace();

}

}

}

以上就是关于用java怎么将excel表格数据导入到mysql数据库中全部的内容，包括:用java怎么将excel表格数据导入到mysql数据库中、如何用java将集合的元素到入mysql、java怎么连接mysql数据库等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！