package com.example.database;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import jxl.Cell;
import jxl.read.biff.BiffException;
import android.app.Activity;
import android.os.Bundle;
public class MainActivity extends Activity {
jxl.Workbook wb=null;
String s=" ";
ArrayList<String> innerAList = null;
ArrayList<ArrayList<String>> outerAlist = new ArrayList<ArrayList<String>>();
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
ArrayList<ArrayList<String>> outerAlist1 = new ArrayList<ArrayList<String>>();
String path="D:\\MOI\\User.xls";
File file=new File(path);
outerAlist1=ReadDataFromExcel(file);
WriteDataToSQLite(outerAlist1);
}
public static ArrayList<ArrayList<String>> ReadDataFromExcel(File file) {
ArrayList<String> innerAList = null;
ArrayList<ArrayList<String>> outerAlist = new ArrayList<ArrayList<String>>();
jxl.Workbook wb = null;
try{
wb = jxl.Workbook.getWorkbook(file); //创建一个工作簿对象wb,该对象的引用指向某个待读取的Excel文件
jxl.Sheet sheet = wb.getSheet(0); // 创建一个工作簿wb中的工作表对象(工作簿、工作表的关系,就好比一个记账本和账本上某页记账表的关系)
int stRows = sheet.getRows(); //得到当前工作表中所有非空行号的数目;
int stColumns=sheet.getColumns();
for(int i=0;i<stRows;i++){
innerAList = new ArrayList<String>();
for(int j=0;j<stColumns;j++){
Cell cell = sheet.getCell(j, i); //创建一个单元格对象,来存放从sheet的(第j列,第i行)读取的单元格;
innerAList.add(cell.getContents()); //从当前单元格中获得一个已经转换为字符串类型的字符串,详见API
}
outerAlist.add(innerAList);
}
} catch (FileNotFoundException fnf){
fnf.printStackTrace();
} catch (IOException ioe){
ioe.printStackTrace();
} catch (BiffException be){
be.printStackTrace();
} finally {
wb.close();
}
return outerAlist;
}
public static void WriteDataToSQLite(ArrayList<ArrayList<String>> al) {
Connection conn = null;
Statement stm = null;
try{
Class cl = Class.forName("org.sqlite.JDBC") ;
conn = DriverManager.getConnection("jdbc:sqlite:d:/MOI/MOI.db");
stm =conn.createStatement();
stm.executeUpdate("create table if not exists Usermsg(" +
"_id integer primary key autoincrement," +
"userno text ," + "username text ,");
PreparedStatement prs = conn.prepareStatement(
"insert into Usermsg values (?,?,?);");
//将ArrayList中的数据,依次放进数据库
for(int i=0;i<al.size();i++){
for(int j=0;j<al.get(i).size();j++)
{
prs.setString(j+2,al.get(i).get(j));
}
}
prs.addBatch();//将这次的批处理命令添加到prs中去
conn.setAutoCommit(false);
prs.executeBatch();
conn.setAutoCommit(true);//这三个方法,可将缓存于prs中的一批sql执行命令,真正执行,并将之前一组数据,插入到数据库中去.
//for(outside);
} catch (SQLException sqe){
sqe.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
标注:jxl.jar,sqlitejdbc-v056.jar我已通过build path -configure build path添加到项目中
在线等各位高手 给看一下,多谢多谢。
sqlite excel android开发 excel内容读到sqlite数据库