注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天边云E家 shaoruisky

IT博文共享,知识的海洋

 
 
 

日志

 
 

ASP.NET Excel导入导出SQL Server  

2015-02-02 11:10:29|  分类: C# |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
导出Excel:添加引用(com):Microsoft Office 11.0 Object Library
1.Excel导入
在页面层添加FileUpload控件,控件位置:Standard里面,Calendar下面两个。设置其ID。
<asp:FileUpload ID="inputFile" runat="server" />

代码

protected void btnUpload_Click(object sender, EventArgs e)
{
DataSet ds = GetExcelData();
InsertDB(ds);
}


/// <summary>
/// 该方法实现从Excel中导出数据到DataSet中,其中filepath为Excel文件的绝对路径,sheetname为表示那个Excel表,此用Sheet1;
/// </summary>
/// <param name="ds">ds</param>
private void InsertDB(DataSet ds)
{
SqlConnection _con = new SqlConnection(@"Data Source=STKWX028\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = _con;
StringBuilder sb = new StringBuilder();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sb.Append(" INSERT INTO bookInfo(bookID,bookName,author,publisher,price,readerID,readerType,bookStatus) VALUES('");
sb.Append(ds.Tables[0].Rows[i].ItemArray[0].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[1].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[2].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[3].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[4].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[5].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[6].ToString() + "','");
sb.Append(ds.Tables[0].Rows[i].ItemArray[7].ToString() + "' ) ");
cmd.CommandText = sb.ToString();
}
}
_con.Open();
int j = cmd.ExecuteNonQuery();
_con.Close();
if (j > 0)
{
lblMessage.Text = "Insert into DB table Sucessfully!";
}
}
/// <summary>
/// get data source from excel file
/// </summary>
/// <returns>dataset ds</returns>
private DataSet GetExcelData()
{
DataSet ds = new DataSet();
string filePath = inputFile.PostedFile.FileName;
string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
string queryStr = "SELECT * FROM [Sheet1$]";
OleDbConnection conn03 = new OleDbConnection(connStr03);
OleDbConnection conn07 = new OleDbConnection(connStr07);
if (inputFile.HasFile)
{
string fileExt = System.IO.Path.GetExtension(inputFile.FileName);
if (fileExt == ".xls")
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
myAdapter.Fill(ds);
}
else if (fileExt == ".xlsx")
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
myAdapter.Fill(ds);
}
else
{
lblMessage.Text = "The file is not exist!";
}

}
return ds;
}

2.
导出Excel
代码

/// <summary>
        /// put data source into dataset
        /// </summary>
        /// <returns>DataSet</returns>
        private static DataSet PutInDataSet()
        {
            string connStr = @"Data Source=STKWX028\SQLEXPRESS;Initial Catalog=Library;Integrated Security=True";
            string queryStr = @"SELECT * FROM bookInfo";
            SqlConnection _con = new SqlConnection(connStr);
            SqlDataAdapter adapter = new SqlDataAdapter(queryStr, connStr);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
        /// <summary>
        /// save the data as excel file
        /// </summary>
        /// <param name="ds"></param>
        private void SaveAsExcel(DataSet ds)
        {
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            if(excelApp != null)            {
              Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Add(Missing.Value);
              Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//get the sheet index
              for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
              {
                for (int col = 0; col < ds.Tables[0].Columns.Count; col++)
                {
                    worksheet.Cells[row + 2, col + 1] = ds.Tables[0].Rows[row][col];
                }
              }
            }          else
            {
                lblMessage.Text = "Fail to export because there's no excel installation!";
            }            excelApp.Quit();        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataSet ds = PutInDataSet();
            SaveAsExcel(ds);
            lblMessage.Text = "Saved";
        }
  评论这张
 
阅读(196)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017