1.把grid的内容输出成excel
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
2从EXCEL中读取数据
/// <summary>
///
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public DataSet ExcelToDS(string Path, string sheetName)
{
//-----------------------------------连接
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + //提供者
"Data Source=C:\\Inetpub\\wwwroot\\contacts.xls;" + //路径
"Extended Properties=Excel 8.0;"; //扩充属性
OleDbConnection conn = new OleDbConnection(strConn); //取得连接
//-----------------------------------打开
conn.Open(); //打开连接
//取得EXCEL的所有Sheet信息
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//取得sheet名Rows[i][2]
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strExcel = "select * from [" + tableName + "$]"; //命令语句
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds;
}
3把数据写入EXCEL文件中
/// <summary>
/// 写入Excel文件
/// </summary>
/// <param name="Path">路径</param>
/// <param name="oldds">数据源</param>
public void DSToExcel(string Path, DataSet oldds)
{
//先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "select * from [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
builder.QuotePrefix = "["; //获取insert语句中保留字符（起始位置）
builder.QuoteSuffix = "]"; //获取insert语句中保留字符（结束位置）
DataSet newds = new DataSet();
myCommand.Fill(newds, "Table1");
for (int i = 0; i < oldds.Tables[0].Rows.Count; i++)
{
// 在这里不能使用ImportRow方法将一行导入到news中，
//因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
//在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added
DataRow nrow = newds.Tables["Table1"].NewRow();
for (int j = 0; j < newds.Tables[0].Columns.Count; j++)
{
nrow[j] = oldds.Tables[0].Rows[i][j];
}
newds.Tables["Table1"].Rows.Add(nrow);
}
myCommand.Update(newds, "Table1");
myConn.Close();
}