页面导入和导出到EXCEL

利用ASP.NET输出指定内容的WORD、EXCEL、TXT、HTM等类型的文档很容易的。主要分为三步来完成。

一、定义文档类型、字符编码

        Response.Clear();
        Response.Buffer= true;
        Response.Charset="utf-8";

        //下面这行很重要，  attachment 参数表示作为附件下载，您可以改成 online在线打开

       //filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc || .xls || .txt ||.htm

        Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls");
        Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");

        //Response.ContentType指定文件类型 可以为application/ms-excel || application/ms-word || application/ms-txt || application/ms-html || 或其他浏览器可直接支持文档

        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;

二、定义一个输入流

        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

三、将目标数据绑定到输入流输出

        this.RenderControl(oHtmlTextWriter);

        //this 表示输出本页，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件

        Response.Write(oStringWriter.ToString());
        Response.End();

 

导入导出类

public class DataUtil
{
public DataUtil()
{
}

#region 导入数据到GridView

/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileName"> 源Excel文件 </param>
/// <param name="gridview"> 目标GridView </param>
/// <param name="keyColIndex"> 导入主键列 </param>
/// <param name="rtnMsg"> 返回消息 </param>
/// <returns> 返回没有导入的数据DataSet </returns>
public static DataSet ExcelToGridView( string fileName, GridView gridview, int keyColIndex, string rtnMsg)
{
DataSet ds = null ;
string errMsg = string .Empty;
try
{
if ( ! fileName.EndsWith( " .xls " ))
{
errMsg += " 只能导入Excel格式的文件。 " ;
}

ds = ExcelHelper.ExcelToDS(fileName);
DataTable dt = ds.Tables[ 0 ];
for ( int i = 0 ; i < gridview.Rows.Count; i ++ )
{
if (gridview.Rows[i].RowType != DataControlRowType.DataRow)
{
continue ;
}
string key = gridview.Rows[i].Cells[keyColIndex].Text;
if (String.IsNullOrEmpty(key))
{
continue ;
}
int k = 0 ;
for (; k < dt.Rows.Count; k ++ )
{
if (dt.Rows[k][gridview.Columns[keyColIndex].HeaderText].ToString().Replace( " " , "" ).Replace( " 　 " , "" ) == key)
{
break ;
}
}
if (k == dt.Rows.Count)
{
continue ;
}
else
{
for ( int j = 0 ; j < gridview.Columns.Count; j ++ )
{
if (gridview.Columns[j].Visible == false )
{
continue ;
}
if (gridview.Rows[i].Cells[j].Controls.Count == 0 )
{
continue ;
}
for ( int m = 0 ; m < gridview.Rows[i].Cells[j].Controls.Count; m ++ )
{
if (gridview.Rows[i].Cells[j].Controls[m].Visible == false )
{
continue ;
}
if (gridview.Rows[i].Cells[j].Controls[m] is TextBox)
{
if ( ! dt.Columns.Contains(gridview.Columns[j].HeaderText))
{
continue ;
}
TextBox txt = gridview.Rows[i].Cells[j].Controls[m] as TextBox;
string temp = dt.Rows[k][gridview.Columns[j].HeaderText].ToString();
try
{
// int intTemp = int.Parse(temp);
// txt.Text = intTemp.ToString();
txt.Text = temp; // Translate(temp);
}
catch (Exception)
{
}
break ;
}
}
}
dt.Rows[k].Delete();
dt.AcceptChanges();
}
}
}
catch (Exception ex)
{
errMsg += " 导入失败. " + ex.Message ;
}
rtnMsg = errMsg;
return ds;
}

/// <summary>
/// 导入数据到GridView
/// </summary>
/// <param name="fileUpload"> 文件上传控件对象 </param>
/// <param name="filePath"> 要存放文件的服务器路径 </param>
/// <param name="gridview"> 目标GridView </param>
/// <param name="keyColIndex"> 用于导入的主键列 </param>
/// <returns> 返回没有导入的数据DataSet </returns>
public static DataSet ExcelToGridView(FileUpload fileUpload, string filePath, GridView gridview, int keyColIndex, string strMsg)
{
string errMsg = string .Empty;
DataSet ds = null ;
try
{
if ( ! filePath.EndsWith( " \\ " ))
{
filePath += " \\ " ;
}
string fileName = fileUpload.FileName.Replace( " .xls " , "" ) + " _ " + DateTime.Now.ToString( " yyyyMMddhhmmss " ) + " .xls " ;
string fullFileName = filePath + fileName;
fileUpload.SaveAs(fullFileName);
ds = ExcelToGridView(fullFileName, gridview, keyColIndex,errMsg);
if ( errMsg != string .Empty)
{
errMsg += " 将数据导入GridView失败. " + errMsg ;
};
}
catch (Exception ex)
{
errMsg += " 上传excel文件失败. " ;
}
strMsg = errMsg;
return ds ;
}

#endregion

/// <summary>
/// 选定主键列后，用这方法将重复行用黄色背景标志
/// </summary>
/// <param name="gridview"> 目标GridView </param>
/// <param name="keyColIndex"> 导入主键列 </param>
public static void MarkRepetitionRows(GridView gridView, int keyColIndex)
{
for ( int i = 0 ; i < gridView.Rows.Count; i ++ )
{
for ( int j = i + 1 ; j < gridView.Rows.Count; j ++ )
{
if (gridView.Rows[i].Cells[keyColIndex].Text == gridView.Rows[j].Cells[keyColIndex].Text)
{
gridView.Rows[i].BackColor = Color.Yellow;
gridView.Rows[j].BackColor = Color.Yellow;
}
}
}
}

}

 

转载于:https://www.cnblogs.com/wsmltyl/archive/2010/05/22/1741412.html