如何将excel转换成android下sqlite管理的数据库文件?
发布网友
发布时间：2022-04-29 09:22
共3个回答
热心网友
时间：2022-04-08 08:29
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.IO.Ports;
using System.Threading;
using System.Data.OleDb;
using System.Data.SQLite;
using DormitoryRating;
//***********************************************************************
//版权所有：AnnKiny
//文件名称：Form1.cs
//CLR 版本：9.0
//描 述：
//作 者：Hanlong Tu
//博 客：（CSDN） http://my.csdn.net/?ref=toolbar_logo QQ：136939236
//创建日期：2014-08-13 14:59:03
//修 改 人：
//修改日期：
//修改原因：
//备 注： 请尊重作者，保留作者信息。
//***********************************************************************
namespace Sign_In
{
public partial class FrmSignIN : Form
{
string checkFilePath = "";//选择路径
SerialPort comDevice = new SerialPort();//实例化串口
string[] coms = SerialPort.GetPortNames();//获取串口名称
string AppPath = "";//声明程序路径
public FrmSignIN()
{
InitializeComponent();
AppPath = Application.StartupPath;
}
private void FrmSignIN_Load(object sender, EventArgs e)
{
btnBrows.Focus();
}
private void button1_Click(object sender, EventArgs e)
{
MessageBox.Show(AppDomain.CurrentDomain.SetupInformation.CachePath);
}
private void btnChange_Click(object sender, EventArgs e)
{
if (File.Exists(txtCheckFile.Text.Trim()))
{
ChangeDB();
}
else
{
MessageBox.Show("你选择的文件不存在！", "错误：");
}
}
//转DB
private void ChangeDB()
{
//1.创建db文件2.创建表3.创建字段
string fileName = labName.Text.TrimEnd('.', 'x', 'l', 's', 'x') + ".db";//获取文件名
if (!File.Exists(AppPath))
{
SQLiteConnection.CreateFile(fileName);//创建文件
}
string connectionString = "data source=" + AppPath + "\\" + fileName + ";Pooling=true;FailIfMissing=false";//数据库连接字符串
SQLiteConnection conn = new SQLiteConnection(connectionString);
conn.Open();
string[] getTableNames = GetTablesName(txtCheckFile.Text.Trim());
//创建所有表和字段
for (int i = 0; i < getTableNames.Length; i++)
{
string[] getColumnNames = GetExcelColumnsName(txtCheckFile.Text.Trim(), getTableNames[i]);
string sqlCreate = "CREATE TABLE " + getTableNames[i] + "(" + getColumnNames[0] + " varchar(100))";
SQLiteCommand command = new SQLiteCommand(sqlCreate, conn);
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("文件已经存在！");
return;
}
for (int j = 1; j < getColumnNames.Length; j++)
{
string sqlAdd = "alter table " + getTableNames[i] + " add " + getColumnNames[j] + " varchar(100)";
SQLiteCommand commandAdd = new SQLiteCommand(sqlAdd, conn);
commandAdd.ExecuteNonQuery();
}
}
//插入所有数据
for (int i = 0; i < getTableNames.Length; i++)
{
DataSet DS = GetExcelData(txtCheckFile.Text.Trim(), getTableNames[i]);
DataTable DT = new DataTable();
DT = DS.Tables[0];
DataColumnCollection dcc = DT.Columns;
DataRowCollection drc = DT.Rows;
pgbChangeDB.Maximum = drc.Count;
pgbChangeDB.Value = 0;
StringBuilder sb = new StringBuilder();
for (int k = 0; k < drc.Count; k++)
{
for (int j = 0; j < dcc.Count; j++)
{
string xiegan = "\"";
sb.Append(xiegan + drc[k][j].ToString() + xiegan);
sb.Append(",");
}
string sqlInsert = "insert into " + getTableNames[i] + " values(" + sb.ToString().TrimEnd(',') + ")";
SQLiteCommand command = new SQLiteCommand(sqlInsert, conn);
command.ExecuteNonQuery();
Application.DoEvents();
pgbChangeDB.Value += 1;
sb.Length = 0;
}
}
conn.Close();
System.Data.SQLite.SQLiteConnection.ClearAllPools();
MessageBox.Show("转换成功！", "提示：");
}
public void GetExcelView(string Path)
{
string strConn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; //Excel连接字符串
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "select * from [" + listTablesName.SelectedItem.ToString() + "$]";
conn.Open();//打开数据库
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();//把数据加载到ds
myCommand.Fill(ds, "Table");
this.dataGridView1.DataSource = ds.Tables[0];
DataTable dt = ds.Tables[0];
DataColumnCollection dcct = dt.Columns;
conn.Close();
System.Data.SQLite.SQLiteConnection.ClearAllPools();
}
/// <summary>
/// 获取Excle表的表名称
/// </summary>
/// <param name="Path">路径</param>
/// <returns>Excle表格名称数组</returns>
public string[] GetTablesName(string Path)
{
List<string> tableNames = new List<string>();
string[] strTableNames = new string[10];// 表名称
listTablesName.Items.Clear();//清除列表
string strConn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; //Excel连接字符串
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取Excel的所有表
DataRowCollection dc = schemaTable.Rows;
int i = dc.Count;
for (int count = 0; count < i; count++)
{
listTablesName.Items.Add(schemaTable.Rows[count][2].ToString().TrimEnd('$'));
tableNames.Add(schemaTable.Rows[count][2].ToString().TrimEnd('$'));//赋值给表名称
strTableNames = tableNames.ToArray();
}//添加数据
conn.Close();
return strTableNames;
}
/// <summary>
/// 获取字段名称
/// </summary>
/// <param name="Path">路径</param>
/// <param name="TableName">表名</param>
/// <returns>字段名称数组</returns>
public string[] GetExcelColumnsName(string Path, string TableName)
{
List<string> columnsName = new List<string>();
string[] strcolumnsName;
string strConn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; //Excel连接字符串
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "select * from [" + TableName + "$]";
conn.Open();//打开数据库
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();//把数据加载到ds
myCommand.Fill(ds, "Table");
//this.dataGridView1.DataSource = ds.Tables[0];
DataTable dt = ds.Tables[0];
DataColumnCollection dcct = dt.Columns;
for (int i = 0; i < dcct.Count; i++)
{
columnsName.Add(dcct[i].ToString());
}
strcolumnsName = columnsName.ToArray();
conn.Close();
return strcolumnsName;//返回表格名称数组
}
/// <summary>
/// 获取Excel数据
/// </summary>
/// <param name="Path">Excle路径</param>
/// <param name="TableName">Excle表格名</param>
/// <returns>返回DataSet</returns>
public DataSet GetExcelData(string Path, string TableName)
{
List<string> columnsName = new List<string>();
string strConn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"; //Excel连接字符串
OleDbConnection conn = new OleDbConnection(strConn);
string strExcel = "select * from [" + TableName + "$]";
conn.Open();//打开数据库
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();//把数据加载到ds
myCommand.Fill(ds, "Table");
return ds;
}
热心网友
时间：2022-04-08 09:47
就算有工具可以把excel转换成sqlite数据库，你也要有应用来打开它，查看和编辑数据。
不知道你为什么会有这个需求，我觉得你是不是想在安卓手机上查看excel文件？追问我是要将数据库操作编进android程序，直接对excel进行操作比较麻烦嘛，sqlite要熟悉的多。
追答你可以写个这样的程序，呵呵。
用java操作excel比较方便的，转sqlite反而麻烦。
热心网友
时间：2022-04-08 11:21
