一、导出Excel
<span style="white-space:pre"> </span>public void CreateExcel(DataTable dt, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
//定义表对象与行对象,同时用DataSet对其值进行初始化
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = SqlHelper.ExecuteDataTable("select name from t");
string name = "" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + ".xls";
CreateExcel(dt,name);
}
二、导入Excel
/// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
string fileName = fileId.FileName;
string savePath = Server.MapPath("~/file/");
FileOperatpr(fileName, savePath);
fileId.SaveAs(savePath + fileName);
DataOperator(fileName, savePath);
}
/// <summary>
/// 数据操作
/// </summary>
/// <param name="fileName"></param>
/// <param name="savePath"></param>
private void DataOperator(string fileName, string savePath)
{
//string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + savePath + fileName + ";Extended Properties=Excel 8.0;";此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
string myString = "Provider=Microsoft.Ace.OleDb.12.0; Data Source =" + savePath + fileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
// "HDR=yes;"是说Excel文件的第一行是列名而不是数据,"HDR=No;"正好与前面的相反。
// "IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
OleDbConnection oconn = new OleDbConnection(myString);
oconn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn);
oda.Fill(ds);
oconn.Close();
DataSetOperator(ds, savePath + fileName);
}
/// <summary>
/// 数据集操作
/// </summary>
/// <param name="ds"></param>
private void DataSetOperator(DataSet ds, string filePath)
{
//SqlConnection conn = new SqlConnection("server=.\\sqlexpress;database=HuaGongDB;User Id=sa;Password=sasa");
SqlConnection conn = null;
conn = SqlHelper.OpenConnection();
SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断
int k = 0;
if (ds.Tables[0].Rows.Count < 1)
{
Response.Write("<script >alert('没有数据!')</script>");
// --></mce:script>");
return;
}
try
{
string s = "";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//string sqlStr = "insert into IntoExcel(Tname,Tage,Taddress) values";
//sqlStr +="('"+ ds.Tables[0].Rows[i][0].ToString()+"',";
//sqlStr += ds.Tables[0].Rows[i][1].ToString()+",";
//sqlStr +="'" +ds.Tables[0].Rows[i][2].ToString()+"')";
s += ds.Tables[0].Rows[i][0].ToString();
this.Label1.Text = s;
string sqlStr = "insert into t values";
sqlStr += "('" + ds.Tables[0].Rows[i][0].ToString() + "')";
sqlStr += ds.Tables[0].Rows[i][1].ToString() + ",";
sqlStr += "'" + ds.Tables[0].Rows[i][2].ToString() + "')";
SqlCommand cmd = new SqlCommand(sqlStr, conn, str);
cmd.Transaction = str;
k += cmd.ExecuteNonQuery();
}
str.Commit();
}
catch (Exception ex)
{
Response.Write("发生异常,数据已回滚/n信息/n" + ex.Message);
str.Rollback();
}
finally
{
Response.Write("上传成功" + k + "条");
File.Delete(filePath);
}
}
/// <summary>
/// 文件操作
/// </summary>
/// <param name="fileName"></param>
/// <param name="savePath"></param>
private void FileOperatpr(string fileName, string savePath)
{
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
if (File.Exists(savePath + fileName))
{
File.Delete(savePath + fileName);
}
}