//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();
}
}
}
}