.net数据库实现Excel的导入与导出

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

参考路径：https://www.cnblogs.com/splendidme/archive/2012/01/05/2313314.html

1.default.aspx文件

2.default.aspx.cs文件

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
using JJoobb.Web;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
string strConn = SiteSetting.ConnectionString;
string sqlSelectALL = SiteSetting.sqlSelALL;
string exToDB = SiteSetting.ExToDB;
protected void Page_Load(object sender, EventArgs e)
{ }
protected void readFromDB_Click(object sender, EventArgs e)
{
Bind();
}
public void btnOut_Click(object sender, EventArgs e)
{
try
{
CreateExcel(getds());
Response.Write("<script>alert('数据导出成功!')</script>");
}
catch
{
Response.Write("<script>alert('数据导出失败!')</script>");
}
}
public void CreateExcel(DataSet ds)
{
string outPutPath=SiteSetting.OutPutPath +DateTime.Now.ToString ("yyyyMMddHHmmss-")+SiteSetting.FileName;
FileStream file = new FileStream(outPutPath, FileMode.Create);
StreamWriter sw = new StreamWriter(file,Encoding.Default);//设置编码为当面页面编码
string colHeaders = "", ls_item = "";
//定义表对象与行对象，同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = ;
int cl = dt.Columns.Count;
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for (i = ; i < cl; i++)
{
if (i == (cl - ))//最后一列，加\n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
sw.Write(colHeaders);
foreach (DataRow row in myRow)
{
//当前行数据写入输出流，并且置空ls_item以便下行数据
for (i = ; i < cl; i++)
{
if (i == (cl - ))
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
sw.Write(ls_item);
ls_item = "";
}
sw.Flush();
sw.Close();
file.Close();
} //从数据库取出要导出的Detset数据集
private DataSet getds()
{
SqlConnection conns = new SqlConnection(strConn);
SqlDataAdapter da = new SqlDataAdapter(sqlSelectALL, conns);
DataSet ds = new DataSet();
da.Fill(ds);
conns.Close();
conns.Dispose();
return ds;
}
//绑定数据
public void Bind()
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection())
{
SqlDataAdapter sda = new SqlDataAdapter(sqlSelectALL, strConn);
sda.Fill(ds, "ex_test");
}
GridView1.DataSource = ds.Tables["ex_test"];
GridView1.DataBind();
}
//导入数据
protected void Import_Click(object sender, EventArgs e)
{
string getErrMsg = "";
DataSet excelDs = new DataSet();
if (FileUpload1.PostedFile.FileName == "")
{
Response.Write("<script language=javascript>alert('请选择要上传的文件！');</script>");
return;
}
//从Excel读取数据
string filePath = FileUpload1.PostedFile.FileName;
string connString = exToDB + filePath;
OleDbConnection excelConn = new OleDbConnection(connString);
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);
try
{
ExcelDA.Fill(excelDs, "ex_test");
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
excelConn.Close();
excelConn = null;
}
//将数据写入数据库
if (excelDs.Tables[].Rows.Count != )
{
SqlConnection sqlConn = new SqlConnection(strConn);
sqlConn.Open();
SqlCommand myCommand = sqlConn.CreateCommand();
SqlTransaction myTrans = sqlConn.BeginTransaction();
myCommand.Transaction = myTrans;
try
{
for (int i = ; i < excelDs.Tables[].Rows.Count; i++)
{
string sql = "insert into ex_test(id, name, sex,email,address) values('" + excelDs.Tables[].Rows[i]["编号"].ToString() + "','" + excelDs.Tables[].Rows[i][" 姓名"].ToString() + "','" + excelDs.Tables[0].Rows[i]["性别"].ToString() + "','" + excelDs.Tables[].Rows[i]["邮箱"].ToString() + "','" + excelDs.Tables[].Rows[i]["地址"].ToString() + "')";
myCommand.CommandText = sql; myCommand.ExecuteNonQuery();
}
myTrans.Commit();
}
catch (Exception ex)
{
getErrMsg = ex.Message.ToString();
Response.Write(ex.Message.ToString());
myTrans.Rollback();
}
finally
{
sqlConn.Close();
sqlConn = null;
} }
//返回提示信息
if (getErrMsg == "" || getErrMsg == null)
{
Response.Write("<script language='Javascript'>alert('导入成功！') </script>");
Bind();
}
else
{
Response.Write("<script language='Javascript'>alert('导入失败！') </script>");
return;
}
} }

3.SiteSetting.cs

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text; /// <summary>
/// SiteSetting 的摘要说明
/// </summary>
namespace JJoobb.Web
{
public class SiteSetting
{
//数据库连接字符串
public static string ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; //导出文件名称
public static string FileName = "OutPut.xls"; //导出文件地址
public static string OutPutPath = "C:\\Documents and Settings\\Administrator\\桌面\\excleFile\\"; //sql语句
public static string sqlSelALL = "select id,title,contents,userName,phone,email,CONVERT(varchar(), AddDate, ) AddDate,ReContents from Feedback"; //从excel读数据
public static string ExToDB = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=";
}
}