求助将excel文件写入数据库中

[w352546881]

我在开发一个网站，其中要用到上传下载功能。
这个功能我准备把上传的附件（主要是excel文件）直接用流写入到数据库中。想请教下，具体的代码。

[H_Gragon]

我这有直接将excel中的数据批量导入数据库的例子（你可以参考一下）：地址

[骑着蜗牛去爬山]

数据库中直接存路径不行吗？

[杰拉尔]

net Excel数据导入到数据库
.net Excel数据导入到数据库
一.数据库向导本来就可以这样导入,在Excel建的字段，必须在table表中也得有字段.

二.读取excel文件中的数据

先在类中定义一个方法名为ExecleDs的方法,用于将Excel表里的数据填充到DataSet中,代码如下：
public DataSet ExecleDs(string filenameurl,string table)
          {
             string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" +filenameurl+ ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
              OleDbConnection conn = new OleDbConnection(strConn);
       
              OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]",conn);
              DataSet ds = new DataSet();
              odda.Fill(ds,table);
      
             return ds;

          }

当点击添加按钮时激发事件,代码如下
protected void Button1_Click(object sender, EventArgs e)
  {
       if (FileUpload1.HasFile == false)
      {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
           return;//当无文件时,返回
        }
       string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
        if (IsXls != ".xls")
      {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
          return;//当选择的不是Excel文件时,返回
        }
       string error = null;
       Access.Class1 ac = new Access.Class1();
       SqlConnection cn = ac.myConnection();
        cn.Open();
       string strpath = FileUpload1.PostedFile.FileName.ToString();   //获取Execle文件路径
       string filename = FileUpload1.FileName;                       //获取Execle文件名
         DataSet ds = ac.ExecleDs(strpath,filename);
       DataRow[] dr = ds.Tables[0].Select();                        //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
      if (rowsnum == 0)                                
      {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
       }
     else
      {
         for (int i = 0; i < dr.Length; i++)
           {
               string YHMC = dr[i]["YongHuMingCheng"].ToString();
            string YHMM = dr[i]["YongHuMiMa"].ToString();
                string DQRQ = dr[i]["DaoQiRiQi"].ToString();
              string ZT = dr[i]["ZhuangTai"].ToString();
                string TJSJ = dr[i]["TianJiaShiJian"].ToString();
               string JXDM = dr[i]["JiaXiaoDaiMa"].ToString();
               string sqlcheck = "select count(*) from DC_YongHuLieBiao where YongHuMingCheng='" + YHMC + "'And JiaXiaoDaiMa='" + JXDM + "'";  //检查用户是否存在
                bool ch = ac.check(sqlcheck);   
             if (ch == true)
              {
                   string insertstr = "insert into DC_YongHuLieBiao(YongHuMingCheng,YongHuMiMa,DaoQiRiQi,ZhuangTai,TianJiaShiJian,JiaXiaoDaiMa) values('" +
                                         YHMC + "','" + YHMM + "','" + DQRQ + "','" + ZT + "','" + TJSJ + "','" + JXDM + "')";
                    SqlCommand cmd = new SqlCommand(insertstr,cn);
              try
                  {
                     cmd.ExecuteNonQuery();
                    }     catch (MembershipCreateUserException ex)       //捕捉异常
                    {
                        Response.Write("<script>alert('创建用户:"+ex.Message+"')</script>");
                    }
              }
              else
             {
                    error += "<em style='color:red;font-sixe:25px'>"+YHMC+"</em>用户已存在,此行记录无法插入!请修改用户再进行插入 <br>";   //若用户存在,将已存在用户信息打出,并提示此用户无法插入
                   continue;
                 }
           }
           Response.Write("<script>alert('Excle表导入成功!')</script>");
            Label1.Text = error;
         }
         cn.Close();
    }

[w352546881]

我的意思是。。将excel文件整体上传到数据库中。。现在求一段能够读出文件并且做成下载文件的代码呢。谢谢哈

[杰拉尔]

/// <summary>
/// 获取指定目录中所有文件列表
/// </summary>
/// <param name="directoryPath">指定目录的绝对路径</param>
public static string[] GetFileNames( string directoryPath )
{
//如果目录不存在，则抛出异常
if ( !IsExistDirectory( directoryPath ) )
{
throw new FileNotFoundException();
}

//获取文件列表
return Directory.GetFiles( directoryPath );
}

读出文件

//WriteFile实现下载
protected void Button2_Click(object sender, EventArgs e)
{
string fileName ="asd.txt";//客户端保存的文件名
string filePath=Server.MapPath("DownLoad/aaa.txt");//路径
FileInfo fileInfo = new FileInfo(filePath);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
}
下载文件

[zhaochanglong]

以前做过 代码页不记得了
帮顶了...!!

[w352546881]

/// <summary>
/// 读取上传文件转换成二进制流
///
/// </summary>
/// <param name="fp"></param>
/// <returns></returns>
protected byte[] convert(FileUpload fp)
{
string strFilePathName = FileUpload1.PostedFile.FileName;

string strFileName = Path.GetFileName(strFilePathName);
int FileLength = FileUpload1.PostedFile.ContentLength;
///图象文件临时储存Byte数组
Byte[] FileByteArray = new Byte[FileLength];
///建立数据流对像
///
Stream StreamObject = FileUpload1.PostedFile.InputStream;
///读取图象文件数据，FileByteArray为数据储存体，0为数据指针位置、FileLnegth为数据长度
StreamObject.Read(FileByteArray, 0, FileLength);
int size = FileByteArray.GetLength(0);
return FileByteArray;

}

下载部分
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
String fileId = Request.QueryString["id"];
//string strCnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Image2Access.mdb";
//System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(strCnString);
//String strSql = "select * from FileTable Where FileId=@FileId";
//System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(strSql, myConnection);
//command.Parameters.AddWithValue("@FileId", fileId);
//打开连接，执行查询
//myConnection.Open();
//System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader();
Maticsoft.Model.Auditing_Finicial modlel;
modlel = bll.GetModel(Convert.ToInt32(fileId));
if (modlel != null)
{
Response.ClearContent();
//Response.ContentType = modlel.Contenttype;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(modlel.FileName));

int size = modlel.UploadFile.GetLength(0);
Response.OutputStream.Write((byte[])modlel.UploadFile, 0, size);

Response.End();
}
//(Byte[])
}
}