基于C#语言MDB,XLS,DBF,VFPDBF,DOC,TXT,XML,HTML导入(导出)数据公用方法

18 篇文章 0 订阅
9 篇文章 0 订阅
//Excel导入到数据库
public ActionResult impdr()
        {
            HttpPostedFileBase filepost= Request.Files["info"];
            string flag = "", msg = "";
            if (filepost==null||filepost.ContentLength == 0 || filepost.FileName == "")
            {
                return Json(new { flag = 0, msg = "请选择要上传的文件!" }, "text/html", JsonRequestBehavior.AllowGet);
            }
            List<string> typelist = new List<string>(){
                ".xls",".xlsx"
            };
            string filetype = Path.GetExtension(filepost.FileName);
            if (!typelist.Contains(filetype))
            {
                return Json(new { flag = 0, msg = "请选择excel格式的文件!" }, "text/html", JsonRequestBehavior.AllowGet);
            }
            string path = Server.MapPath("~/files/");
            string filename = Path.GetFileName(filepost.FileName);
            string filepath = Path.Combine(path, filename);
            filepost.SaveAs(filepath);
            DbFileMapDataSet dsTool = new DbFileMapDataSet(path, filename, DbFileMapDataSet.TableType.XLS);
            DataTable dt = dsTool.Convert().Tables[0];
            if (dt.Rows.Count == 0)
            {
                return Json(new { flag = 0, msg = "选择的文件没有数据!" }, "text/html", JsonRequestBehavior.AllowGet);
            }
            if (System.IO.File.Exists(filepath))
            {
                System.IO.File.Delete(filepath);
            }
            //操作把数据存入到数据库(略,根据自己的写法写)
        }

 

//导出dbf文件
public ActionResult dcsj()
        {
            DataSet ds = dal.GetList(" 1=1 ")
            DbFileMapDataSet obj = new DbFileMapDataSet(Server.MapPath("../out/"), "sbss", DbFileMapDataSet.TableType.DBF);
            if (obj.Convert(ds))
            {
                string path = Server.MapPath("../out/") + "sbss.dbf";
                byte[] data = System.IO.File.ReadAllBytes(path);
                MemoryStream ms = new MemoryStream(data);
                System.IO.File.Delete(path);
                return File(ms, "application/octet-stream", Server.UrlEncode("sj.dbf"));
            }
            else
            {
                Response.Write("<script>alert('没有数据可以导出')</script>");
                return View();
            }
        }
using System;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Text;
using System.IO;
using System.Data.SqlClient;
namespace Common
{
	/// <summary>
	/// DbFileMapDataSet 的摘要说明。
	/// </summary>
	public class DbFileMapDataSet
	{
		/// <summary>
		/// 传入的文件变量
		/// </summary>
		private DataSet my_Ds;//存放文件的数据集
		//	private string my_Err;//错误信息
		private string my_TableName;//传入的文件名
		private TableType my_TableType;//传入的文件类型
		private string my_TablePath;//传入的文件路径
        OleDbCommandBuilder my_Builder;//命令串
        private string my_DbfTemplatePath;//DBF模版存放路径

		/// <summary>
		/// 数据库连接变量
		/// </summary>
		private string my_StrConnection;//连接字符串
		private string my_StrSelect;//select语句
   
		/// <summary>
		/// 可以处理的文件类型
		/// </summary>
		public enum TableType
		{
			MDB,XLS,DBF,VFPDBF,DOC,TXT,XML,HTML
		}
  
		public DbFileMapDataSet(string TablePath,string TableName,TableType TableType)
		{
			///<summary>
			///获得传入的路径,文件名及文件类型;
			///</summary>
			this.my_TablePath=TablePath;//路径
			this.my_TableName=TableName;//文件名
			this.my_TableType=TableType;//文件类型
		}

        public DbFileMapDataSet(string TablePath, string TableName, TableType TableType, string DbfTemplatePath)
        {
            ///<summary>
            ///获得传入的路径,文件名及文件类型;
            ///</summary>
            this.my_TablePath = TablePath;//路径
            this.my_TableName = TableName;//文件名
            this.my_TableType = TableType;//文件类型
            this.my_DbfTemplatePath = DbfTemplatePath;//模版路径
        }

		public DataSet Convert()
		{
			DataSet iRtn_Ds=new DataSet();
			switch (this.my_TableType)
			{
				case TableType.VFPDBF:
					iRtn_Ds = this.VFPDbfToDs();
					break;
				case TableType.DBF:
					iRtn_Ds = this.DbfToDs();
					break;

				case TableType.MDB:
					iRtn_Ds = this.MdbToDs();
					break;

				case TableType.XLS:
					iRtn_Ds = this.XlsToDs();
					break;

				case TableType.XML:
					iRtn_Ds = this.XmlToDs();
					break;
			}
			return iRtn_Ds;
		}

		public bool Convert(DataSet ds)
		{
			bool returnvalue = false; 
			switch (this.my_TableType)
			{
				 
				case TableType.DBF:
					returnvalue = this.DsToDbf(ds);
					break;
 
			}
			return returnvalue;
        }
        /// <summary>
        /// 将DataTable导出至DBF模版
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool Convert(DataTable dt)
        {
            bool returnvalue = false;
            switch (this.my_TableType)
            {

                case TableType.DBF:
                    returnvalue = this.DtToDbf(dt);
                    break;

            }
            return returnvalue;
        }
        /// <summary>
        /// 将DataTable导出至指定DBF模版
        /// </summary>
        /// <param name="dt">内存中数据的一个表</param>
        /// <param name="dbftemplatePath">指定DBF模版所在路径</param>
        /// <returns></returns>
        private bool DtToDbf(DataTable dt)
        {
            //删除文件
            try
            {
                File.Delete(this.my_TablePath + this.my_TableName + ".dbf");
                File.Copy(this.my_DbfTemplatePath + this.my_TableName + ".dbf", this.my_TablePath + this.my_TableName + ".dbf");
            }
            catch { }
            //数据库连接定义
            OdbcConnection my_conn; //数据连接 
            OdbcCommand my_Command;

            OdbcDataAdapter my_Adapter;//数据适配器

            //数据库连接
            this.my_StrConnection = "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=" + this.my_TablePath;

            this.my_StrSelect = "SELECT * FROM " + this.my_TableName;
            my_conn = new OdbcConnection(this.my_StrConnection);
            my_Command = new OdbcCommand("", my_conn);
            try
            {
                my_conn.Open();
                my_Adapter = new OdbcDataAdapter(this.my_StrSelect, my_conn);
                this.my_Ds = new DataSet();

                //填充数据集
                my_Adapter.Fill(this.my_Ds, this.my_TableName);
                string[] ColumnName = new string[my_Ds.Tables[0].Columns.Count];
                //my_Command.CommandText = "DELETE ALL ";
                //my_Command.ExecuteNonQuery();
                //my_Command.CommandText = "PACK ";
                //my_Command.ExecuteNonQuery();
                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    i = 0;
                    my_Command.CommandText = "insert into " + this.my_TableName + "(";
                    foreach (System.Data.DataColumn column in my_Ds.Tables[0].Columns)
                    {
                        my_Command.CommandText += column.ColumnName + ",";
                        ColumnName[i] = column.ColumnName;
                        i++;
                    }
                    my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ") values ( ";
                    for (i = 0; i < ColumnName.Length; i++)
                    {
                        my_Command.CommandText += "'" + dr[ColumnName[i].ToString()].ToString().Replace("'", "“") + "',";
                    }
                    my_Command.CommandText = my_Command.CommandText.Substring(0, my_Command.CommandText.Length - 1) + ") ";
                    my_Command.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                string a = ex.Message;
                //LogHelper.WriteLog(a);
                return false;
            }
            finally
            {
                my_conn.Close();
            }
        }
		/// <summary>
		/// 将DataSet 表的内容导入到DBF
		/// </summary>
		/// <param name="ds"></param>
		/// <returns></returns>
		private bool DsToDbf(DataSet ds)
		{ 
			 	//数据库连接定义
			OdbcConnection my_conn; //数据连接 
			OdbcCommand my_Command;

			//数据库连接
			this.my_StrConnection= "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=" + this.my_TablePath;
			 my_conn = new OdbcConnection(this.my_StrConnection);
			my_conn.Open();
			//				 try
			//				{            
			my_Command = new OdbcCommand("",my_conn);
			//删除文件
			try
			{
				File.Delete(this.my_TablePath+this.my_TableName+".dbf" );
			}
			catch{}
			Int32[] columnSize = new Int32[ds.Tables[0].Columns.Count];
			Int32[] rowSize =new Int32[ds.Tables[0].Rows.Count];
			int i=0;
			//取字段最大长度
			foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
			{ 
				//数组初值
				columnSize[i]=0;
				i++;
			} 
			//取行数
			i=0;
			foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
			{ 
				//数组初值
				rowSize[i]=0;
				i++;
			}  

			foreach (System.Data.DataRow dr1 in ds.Tables[0].Rows)
			{
				i=0;
				foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
				{
					Byte[] a = Encoding.Unicode.GetBytes(dr1[column.ColumnName].ToString());
					if (a.Length>columnSize[i])
					{
						columnSize[i]=a.Length;  
					}

					i++;
				}
			}
			//创建新表
			my_Command.CommandText =" create table "+this.my_TableName+"( ";
			System.Text.StringBuilder sql=new System.Text.StringBuilder(); 
			i=0;
			foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
			{  
				if(columnSize[i]>0)//
				{
					if(columnSize[i]>220)
                      my_Command.CommandText += column.ColumnName+" varchar(220),";
					else
                      my_Command.CommandText += column.ColumnName+" varchar("+columnSize[i].ToString()+"),"; 
				}
				else
					my_Command.CommandText += column.ColumnName+" varchar(20),"; 
				i++;

			}
			my_Command.CommandText=my_Command.CommandText.Substring(0,my_Command.CommandText.Length-1)+")"; 
			my_Command.ExecuteNonQuery();
//			my_Command.CommandText = "delete from xj ";
//			my_Command.ExecuteNonQuery(); 

			foreach (System.Data.DataRow dr1 in ds.Tables[0].Rows)
			{
				my_Command.CommandText = "insert into "+this.my_TableName+" (";
				foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
				{
			 		my_Command.CommandText += column.ColumnName+",";

				}
				my_Command.CommandText=my_Command.CommandText.Substring(0,my_Command.CommandText.Length-1)+") ";
				my_Command.CommandText+=" values( ";
				i=0;
				foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
				{
                    my_Command.CommandText += "'" + dr1[column.ColumnName].ToString().Replace("'", "“") + "',";
					Byte[] a = Encoding.Unicode.GetBytes(dr1[column.ColumnName].ToString()); 
					if (a.Length>columnSize[i])
						columnSize[i]=a.Length;
					i++;
				}
				my_Command.CommandText=my_Command.CommandText.Substring(0,my_Command.CommandText.Length-1)+") "; 
				my_Command.ExecuteNonQuery();
			}
//			i=0;
//			foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
//			{
//				my_Command.CommandText = "ALTER TABLE "+this.my_TableName +" ALTER COLUMN "+column.ColumnName+" VARCHAR("+columnSize[i].ToString()+")  ";
//				my_Command.ExecuteNonQuery();
//				i++;
//			}
			my_conn.Close(); 
			return true; 
			
	
		
		}
		///<summary>
		///将DBF文件放入DataSet
		///</summary>
		private DataSet DbfToDs()
		{

			//数据库连接定义
			OdbcConnection my_conn; //数据连接
			OdbcDataAdapter my_Adapter;//数据适配器

			//数据库连接
		     this.my_StrConnection= "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277;Dbq=" + this.my_TablePath;
	
			    //this.my_StrConnection= "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + this.my_TablePath;
			this.my_StrSelect="SELECT * FROM " + this.my_TableName;
			my_conn = new OdbcConnection(this.my_StrConnection);
			try
			{            
				my_conn.Open();
				my_Adapter = new OdbcDataAdapter(this.my_StrSelect,my_conn);
				this.my_Ds=new DataSet();
   
				//填充数据集
				my_Adapter.Fill(this.my_Ds,this.my_TableName);
				my_conn.Close();
			}
			catch 
			{
				return  VFPDbfToDs();
			}
			finally
			{
				my_conn.Close();
			}

			return this.my_Ds;
		}
                
		///<summary>
		///将VFPDBF文件放入DataSet
		///</summary>
		private DataSet VFPDbfToDs()
		{
			//数据库连接定义
			OdbcConnection my_conn; //数据连接
			OdbcDataAdapter my_Adapter;//数据适配器

			//数据库连接
	
			this.my_StrConnection= "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + this.my_TablePath;
			this.my_StrSelect="SELECT * FROM " + this.my_TableName;
			my_conn = new OdbcConnection(this.my_StrConnection);
			my_conn.Open();
			my_Adapter = new OdbcDataAdapter(this.my_StrSelect,my_conn);
			this.my_Ds=new DataSet();
   
			//填充数据集
			my_Adapter.Fill(this.my_Ds,this.my_TableName);

			my_conn.Close();

			return this.my_Ds;
		}
		///<summary>
		///将MDB文件放入DataSet
		///</summary>
		private DataSet MdbToDs()
		{
			//数据库连接定义
			OleDbConnection my_conn;
			OleDbDataAdapter my_Adapter;

			//数据库连接
			this.my_StrConnection= "Provider=Microsoft.JET.OLEDB.4.0;data source=" + this.my_TablePath;
			this.my_StrSelect="SELECT * FROM " + this.my_TableName;
			my_conn = new OleDbConnection(this.my_StrConnection);
			my_conn.Open();
			my_Adapter = new OleDbDataAdapter(this.my_StrSelect,my_conn);
			this.my_Ds=new DataSet();
   
			//填充数据集
			my_Adapter.Fill(this.my_Ds,this.my_TableName);

			my_conn.Close();

			return this.my_Ds;
		}

		///<summary>
		///将XML文件放入DataSet
		///</summary>
		private DataSet XmlToDs()
		{

			//填充数据集
			this.my_Ds=new DataSet();
			this.my_Ds.ReadXml(this.my_TablePath+this.my_TableName,XmlReadMode.ReadSchema);
			this.my_Ds.DataSetName="XmlData";
			return this.my_Ds;
		}

		///<summary>
		///将Excel文件放入DataSet
		///</summary>
        private DataSet XlsToDs()
        {
            OleDbConnection my_conn;
            OleDbDataAdapter my_Adapter;

            //数据库连接
            //this.my_StrConnection= "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;data source="+this.my_TablePath+this.my_TableName;
            this.my_StrConnection = "Provider=Microsoft.Ace.OleDb.12.0;Extended Properties='Excel 12.0; HDR=Yes; IMEX=1';data source=" + this.my_TablePath + this.my_TableName;
            this.my_StrSelect = "SELECT * FROM [SHEET1$]";
            my_conn = new OleDbConnection(this.my_StrConnection);
            my_conn.Open();
            my_Adapter = new OleDbDataAdapter(this.my_StrSelect, my_conn);
            this.my_Builder = new OleDbCommandBuilder(my_Adapter);
            this.my_Ds = new DataSet();
            try
            {
                //填充数据集
                my_Adapter.Fill(this.my_Ds, "ExcelData");
                my_conn.Close();

                return this.my_Ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                my_conn.Close();
            }
        }
	}
}

 

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值