这篇文章主要介绍的是如何向Access数据库中导入复杂的表结构的Excel表，平时我们可以直接导入Access数据库当中的Excel表的结构都是和数据库当中表结构一致的，才可以直接导入到数据库当中，那么当碰到表结构复杂，而且数据繁多的Excel表，那么我们只能就是利用代码的形式来进行导入，下面就是详细的代码，这里用的C#语言来对数据库做一个导入。
1.主页面的方法：
//导入数据页面：
ImportBLL importBLL = new ImportBLL();
private void btn_Click(object sender, EventArgs e)
{
//这里的type指的是你查表的时候的条件
string type = this.cmbType.Text;
if (type == "--请选择--")
{
MessageBox.Show("请选择xx");
return;
}
OpenFileDialog ofd = new OpenFileDialog();
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件(*.*)|*.*";
if (ofd.ShowDialog() == DialogResult.OK)
{
string path = ofd.FileName;
if (path == null || path == "" || string.IsNullOrEmpty(path)) return;
//导入数据方法，传入路径和查表的条件
int count=importBLL.ImportData(path, type);
if (count > 0)
{
MessageBox.Show("成功导入" + count + "条记录");
}
else
{
MessageBox.Show("导入失败");
}
}
}
2.导入数据页面：
ImportBLL importBLL = new ImportBLL();
public class ImportBLL
{
//向数据库当中插入数据
ImportDAL importDAL = new ImportDAL();
/// <summary>
/// 表导入
/// </summary>
/// <param name="path"></param>
public int ImportData(string path,string type)
{
int rcount=0;
try
{
DataTable dt= importDAL.GetExcelTableData(path);
//将数据录入到access数据库
string weightTitle = string.Empty;
string heightTitle = string.Empty;
string height=string.Empty;
string category = string.Empty;
string time=string.Empty;
string fuel=string.Empty;
string dist=string.Empty;
int ias = 0;//0
if (dt.Rows[1][3] != null)
weightTitle = dt.Rows[1][3].ToString();
if (dt.Rows[0][0] != null)
heightTitle = dt.Rows[0][0].ToString();
//重量单位转换值
double wUnits = UnitConvertHelper.GetWeightToKgUnit(weightTitle);
//高度单位转换值
double hUnits = UnitConvertHelper.GetFLToFTUnit(heightTitle);
//获取重量列表
List<string> gwList = GetWeightList(dt.Rows[2]);
List<InterpolateBase> interpolateList_all = new List<InterpolateBase>();
int count = dt.Rows.Count;//总行数
string sql = string.Empty;
int h = 0;//HEIGHT
int t = 0;//TIME
int f = 0;//FUEL
for (int i = 3; i < count; i++)
{
//表包含的每一列
DataRow Row = dt.Rows[i];
if (Row[1] != null && !string.IsNullOrEmpty(Row[1].ToString()))
{
time = Row[1].ToString();
t = int.Parse(time);
}
if (Row[2] != null && !string.IsNullOrEmpty(Row[2].ToString()))//设置油耗fuel
{
fuel=Row[2].ToString();
f = int.Parse(fuel);
}
//构造表
List<InterpolateBase> interpolateList = MakeList(gwList,hUnits,wUnits,type,t,f);
if(Row[0]!=null&&!string.IsNullOrEmpty(Row[0].ToString()))
{
height = Row[0].ToString();
h = int.Parse(height);
SetInterpolateListHeight(interpolateList,h);
}
if(dt.Rows[0][3]!=null)
category=dt.Rows[0][3].ToString();
if(category.ToUpper().Trim().Contains("DIST"))
{
SetValue(3, h, SegParamEnum.DIST, interpolateList, interpolateList_all, Row, gwList);
}
AddToListAll(interpolateList, interpolateList_all);
}
rcount = importDAL.AddData(interpolateList_all,type);
}
catch(Exception ex)
{
Trace.WriteLine(ex.Message);
}
return rcount;
}
/// <summary>
/// 获取重量列表
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private List<string> GetWeightList(DataRow dr)
{
List<string> gwList = new List<string>();
int count = dr.ItemArray.Length;
for (int i = 0; i < count; i++)
{
if (dr[i] != null && !string.IsNullOrEmpty(dr[i].ToString()))
gwList.Add(dr[i].ToString());
}
return gwList;
}
//根据重量构造表
private List<InterpolateBase> MakeList(List<string> gwList, double hUnits, double wUnits, string type,int time,int fuel)
{
List<InterpolateBase> descentList = new List<InterpolateBase>();
int count = gwList.Count;
if(count>0)
{
for (int i = 0; i < count; i++)
{
DescentEntity entity = new DescentEntity();
if(!string.IsNullOrEmpty(gwList[i]))
{
entity.Weight = int.Parse(gwList[i]);
entity.WUnits = wUnits;
entity.HUnits = hUnits;
entity.Type = type;
entity.Time = time;
entity.FF = fuel;
}
descentList.Add(entity);
}
}
return descentList;
}
/// <summary>
/// 给表设置高度数据
/// </summary>
/// <param name="interpolateList"></param>
private void SetInterpolateListHeight(List<InterpolateBase> interpolateList,int height)
{
foreach (InterpolateBase entity in interpolateList)
{
entity.Height = height;
}
}
//给表设置具体的参数值
private void SetValue(int startCol, int height, SegParamEnum segParamEnum, List<InterpolateBase> interpolateList, List<InterpolateBase> interpolateList_all, DataRow Row, List<string> gwList)
{
if ((Row != null && Row.ItemArray.Length > startCol)&&(gwList!=null&&gwList.Count>0))
{
int count = gwList.Count;
for (int i = 0; i < count;i++ )
{
int row_col = i + startCol;
string weightStr = gwList[i];
if (string.IsNullOrEmpty(weightStr)) continue;
int weight = int.Parse(weightStr);
InterpolateBase temp = interpolateList.Find((InterpolateBase entity) => { return entity.Weight == weight; });
DescentEntity descentEntity = temp as DescentEntity;
//从总列表中根据高度和重量查询对应列表，看是否之前已经添加，如果已添加，则更新总列表内容
InterpolateBase interpolate_already = interpolateList_all.Find((InterpolateBase entity) =>
{
return entity.Weight == weight && entity.Height == height;
});
DescentEntity descentEntity_already = interpolate_already as DescentEntity;
if(segParamEnum==SegParamEnum.DIST)
{
if (Row[i] != null && !string.IsNullOrEmpty(Row[row_col].ToString()))
{
int dist = int.Parse(Row[row_col].ToString());
descentEntity.Dist = dist;
if(descentEntity_already!=null)
{
descentEntity_already.Dist = dist;
}
}
}
}
}
}
/// <summary>
/// 将集合添加到总列表中
/// </summary>
/// <param name="interpolateList"></param>
/// <param name="interpolateList_all"></param>
private void AddToListAll(List<InterpolateBase> interpolateList, List<InterpolateBase> interpolateList_all)
{
if (interpolateList == null) return;
foreach (InterpolateBase entity in interpolateList)
{
InterpolateBase temp = interpolateList_all.Find((InterpolateBase ib)=>{
return ib.Weight == entity.Weight && ib.Height == entity.Height;
});
if (temp == null)
{
interpolateList_all.Add(entity);
}
}
}
3.导入数据的类
public class ImportDAL
{
OleDbConnection conn = null;
public ImportDAL()
{
//完成数据库连接初始化
string connectionString = string.Empty;
connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Data\dbFlightWeather.mdb";
//connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\work\FOC\FOCClient\DPS\Data\dbFlightWeather.mdb";
try
{
conn = new OleDbConnection(connectionString);
conn.Open();
conn.Close();
}
catch
{
connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Data\dbFlightWeather.mdb";
//connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\work\FOC\FOCClient\DPS\Data\dbFlightWeather.mdb";
conn = new OleDbConnection(connectionString);
}
}
/// <summary>
/// 获取数据库表数据
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable GetExcelTableData(string path)
{
string tname = string.Empty;
DataTable dt = new DataTable();
string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;IMEX=1;'";
if (System.IO.Path.GetExtension(path).ToUpper() == ".XLSX")
{
strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
}
OleDbConnection conn = new OleDbConnection(strconn);
conn.Open();
DataTable sdt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
int scount = sdt.Rows.Count;
if (scount <= 0) return dt;
for (int j = 0; j < scount; j++)
{
tname = sdt.Rows[j][2].ToString().Trim();
string sql = "select * from [" + tname + "]";
OleDbDataAdapter da = new OleDbDataAdapter(sql, strconn);
dt = new DataTable();
da.Fill(dt);
}
conn.Close();
return dt;
}
//向数据库当中添加表数据
public int AddData(List<InterpolateBase> interpolateList,string type)
{
string tablename = "tbDescent";
//判断数据库中是否包含此机型所对应的表数据，如果有删除，插入新数据
if (GetHoldAndDescentTableData(tablename, type) != 0)
{
int count = DeleteHoldAndDescentTableData(tablename, type);
}
if (interpolateList == null) return 0;
int ret = 0;
foreach (InterpolateBase entity in interpolateList)
{
DescentEntity descent =entity as DescentEntity;
string sql = "insert into Data(HEIGHT,WEIGHT,[TIME],DIST,FUEL,WUnits,HUnits,TYPE) values(@height,@weight,@time,@dist,@fuel,@wunits,@hunits,@type);";
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.Add(new OleDbParameter("@height", OleDbType.Integer));
cmd.Parameters["@height"].Value = descent.Height;
cmd.Parameters.Add(new OleDbParameter("@weight", OleDbType.Integer));
cmd.Parameters["@weight"].Value = descent.Weight;
cmd.Parameters.Add(new OleDbParameter("@time", OleDbType.Integer));
cmd.Parameters["@time"].Value = descent.Time;
cmd.Parameters.Add(new OleDbParameter("@dist", OleDbType.Integer));
cmd.Parameters["@dist"].Value = descent.Dist;
cmd.Parameters.Add(new OleDbParameter("@fuel", OleDbType.Integer));
cmd.Parameters["@fuel"].Value = descent.FF;
cmd.Parameters.Add(new OleDbParameter("@wunits", OleDbType.Double));
cmd.Parameters["@wunits"].Value = descent.WUnits;
cmd.Parameters.Add(new OleDbParameter("@hunits", OleDbType.Double));
cmd.Parameters["@hunits"].Value = descent.HUnits;
cmd.Parameters.Add(new OleDbParameter("@type", OleDbType.VarChar));
cmd.Parameters["@type"].Value = descent.Type;
conn.Open();
ret += cmd.ExecuteNonQuery();
conn.Close();
}
return ret;
}
4.单位转换类：（这里的单位主要是重量为KG、高度为FL）
public class UnitConvertHelper
{
/// <summary>
/// 获取重量单位转换量，系统基本单位采用kg
/// </summary>
/// <param name="weight"></param>
/// <returns></returns>
public static double GetWeightToKgUnit(string weight)
{
double unit = 1;
try
{
string regexStr = @"[(]\w+[)]";
Match match = Regex.Match(weight, regexStr);
string value = match.Value;
value = value.Replace("(", "").Replace(")", "").Replace("（", "").Replace("）", "");
if (value.ToUpper().Contains("KG"))
{
string numStr = value.Replace("KG", "");
if (!string.IsNullOrEmpty(numStr))
{
unit = double.Parse(numStr);
}
}
else if (value.ToUpper().Contains("LB"))
{
string numStr = value.Replace("LB", "");
double num = 1;
if (!string.IsNullOrEmpty(numStr))
{
num = double.Parse(numStr);
}
unit = num * Constants.KG_LB;
}
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
return unit;
}
/// <summary>
/// 获取高度单位转换量，系统基本单位采用ft
/// </summary>
/// <param name="height"></param>
/// <returns></returns>
public static double GetFLToFTUnit(string height)
{
double unit = 1;
try
{
if (height.ToUpper().Contains("FT"))
{
string numStr = height.Replace("FT", "");
if (!string.IsNullOrEmpty(numStr))
{
unit = double.Parse(numStr);
}
}
else if (height.ToUpper().Contains("FL"))
{
string numStr = height.Replace("FL", "");
double num = 1;
if (!string.IsNullOrEmpty(numStr))
{
double.Parse(numStr);
}
unit = num * Constants.FL_FT;
}
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
return unit;
}
}
}
5.实体类信息
参数：
[Description("参数")]
public enum SegParamEnum
{
FF,
TIME,
DIST
}
实体类：
1）实体类基类
public class InterpolateBase
{
public override string ToString()
{
return "基类";
}
private string id = string.Empty;//ID
private int height = 0;//高度，默认单位ft
private int weight = 0;//重量，默认单位kg
private double hUnits = 1;//单位转换被乘数
private double wUnits = 1;//重量单位转换乘数
private string type = string.Empty;//XX
下面是get和set方法
}
[Serializable]
public class DescentEntity:InterpolateBase
{
public override string ToString()
{
return "具体表实体类";
}
private double? time = 0;
private double? dist = 0;
private double? ff = 0;
private double? ias = 0;
下面是get和set方法
}
6.excel表结构
这里的excel文件是以.xls结尾的文件格式来保存的。
后续还会更新导出为word文档的。。。
497
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
