导入-导出Excel表格

简单的导出

/// <summary>
/// <本方法是以Page或DataGrid为媒介导出Excel表格或者Word文档>
/// </summary>
public void ToExcel(System.Web.UI.Control ctl)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");//把filename里的.xls换成.doc就可以导出Word文档
HttpContext.Current.Response.Charset ="GB2312";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword

ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}

简单的显示

/// <summary>
/// Path:Excel 的路径
/// ss:Excel 的 表 名
/// </summary>

public DataSet ExcelToDS(string Path,string ss)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel="select * from ["+ss +"$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,"tablevalue");
if (conn != null)
{
conn.Close();
}
return ds;
}

//导入excel
private void Button1_Click(object sender, System.EventArgs e)
{
string aa=System.Web.HttpContext.Current.Server.MapPath(TextBox2.Text.Trim()); //得到服务器位置
string pathsave=System.Web.HttpContext.Current.Server.MapPath(TextBox1.Text.Trim()); //得到服务器位置

DataSet ds= ExcelToDS(aa,"a");//取得绑定的DataSet 可改DataSet为你的DataSet了.

Excel.Application ExcelApp=new Excel.ApplicationClass();
Excel.Workbook ExcelWorkBook=ExcelApp.Application.Workbooks.Add(true);
Excel.Worksheet ExcelWorkSheet=(Excel.Worksheet)ExcelWorkBook.Worksheets[1];
int rowcount,columncount;
rowcount=(int)ds.Tables["tablevalue"].Rows.Count;
columncount=(int)ds.Tables["tablevalue"].Columns.Count;
//开始填充//随意写内容了...
int ii,jj;
ExcelApp.Cells[1,1]="公司";
for ( ii=0;ii<rowcount;ii++)
{
for ( jj=0;jj<columncount;jj++)
{ ExcelApp.Cells[ii+4,jj+1]=ds.Tables["tablevalue"].Rows[ii][jj].ToString().Trim(); }
}
ExcelWorkBook.SaveCopyAs(pathsave);ExcelWorkSheet=null;
ExcelWorkBook.Close(false,null,null);
ExcelApp.Quit();
System.GC.Collect();

Label6.Text="导入完成!!!";
Button2.Visible=true;

}