问题:在GridView结果集中,选中某些行前的<CheckBox>,然后导出想要的数据到Excel,请问如何做?
希望有详细代码参考,谢谢!!!
<asp:GridView ID="GridView1" runat="server"
BackColor="White" AllowPaging="true" PageSize="12"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4"
CssClass="css" Width="490px" AutoGenerateColumns="False">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns>
<asp:TemplateField HeaderText="选择">
<ItemTemplate><asp:CheckBox ID="chk" runat="server" />
</ItemTemplate>
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" Wrap="False" />
</asp:TemplateField>
<asp:BoundField DataField="BmID" HeaderText="编码">
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="PartName" HeaderText="名称">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="Standard" HeaderText="规格">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="Price" HeaderText="价格">
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="Posi" HeaderText="使用部位">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
</Columns>
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
<table border="0" cellpadding="0" cellspacing="0" class="style10"
style="width: 500px; height: 30px">
<tr>
<td align="center" class="style11">
<input id="Button1" type="button" value="导出数据到Excel" /></td>
</tr>
</table>
希望有详细代码参考,谢谢!!!
<asp:GridView ID="GridView1" runat="server"
BackColor="White" AllowPaging="true" PageSize="12"
BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4"
CssClass="css" Width="490px" AutoGenerateColumns="False">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns>
<asp:TemplateField HeaderText="选择">
<ItemTemplate><asp:CheckBox ID="chk" runat="server" />
</ItemTemplate>
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" Wrap="False" />
</asp:TemplateField>
<asp:BoundField DataField="BmID" HeaderText="编码">
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="PartName" HeaderText="名称">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="Standard" HeaderText="规格">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="Price" HeaderText="价格">
<HeaderStyle Wrap="False" />
<ItemStyle HorizontalAlign="Center" Wrap="False" />
</asp:BoundField>
<asp:BoundField DataField="Posi" HeaderText="使用部位">
<HeaderStyle Wrap="False" />
<ItemStyle Wrap="False" />
</asp:BoundField>
</Columns>
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
<table border="0" cellpadding="0" cellspacing="0" class="style10"
style="width: 500px; height: 30px">
<tr>
<td align="center" class="style11">
<input id="Button1" type="button" value="导出数据到Excel" /></td>
</tr>
</table>
for(girdview的每一行)
checkbox cb=gridview1.Rows[i].Findcontrol("Checkbox1") as checkbox
if(!cb.checked)
{
gridview1.Rows[i].visibel=false;
}
/// 生成EXCEL文件
/// _til是表格的标题,_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称
/// </summary>
public void SaveToExcel(string _til, int _row, int _col, DataTable _dt, string _filename)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
//合并单元格
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).set_MergeCells(true);
// xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);
xlsheet.ActiveSheet.Cells[1, 1] = _til;
//字体加粗
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Bold(true);
//单元格文本水平居中对齐
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
//设置字体大小
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Size(14);
//设置列宽
//xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50); //画边框线
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous); //定义一个2维数组用来存储DATATABLE里的数据
object[,] dataArray = new object[_row, _col];
//添加列名
for (int k = 0; k < _dt.Columns.Count; k++)
{
xlsheet.ActiveSheet.Cells[2, k + 1] = _dt.Columns[k].Caption.ToString();
}
//把DATATABLE里的数据导到2维数组中
for (int i = 0; i < _row; i++)
{
for (int j = 0; j < _col; j++)
{
dataArray[i, j] = _dt.Rows[i][j];
}
}
//把2维数组中的数据导到EXCEL中
xlsheet.get_Range("A3", xlsheet.Cells[_row + 2, _col]).Value2 = dataArray;
xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
}
/// <summary>
/// 导入EXCEL表
/// </summary>
/// <param name="strFileName"> </param>
/// <returns> </returns>
public DataSet CreateDataSource(string strFileName, string Sqlstr)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter(Sqlstr, olecon);
DataSet myds = new DataSet();
myda.Fill(myds);
return myds;
}
这个可以我用过的。你看看行不?
protected void ButtonExcel_Click(object sender, EventArgs e)
{
foreach (GridViewRow dr in GridView1.Rows)
{
// cell[i]就是CheckBox所在的列,从0开始
if ((dr.Cells[0].FindControl("CheckBox1") as CheckBox).Checked)
{
dr.Visible = true;
}
else
{
dr.Visible = false;
}
}
ToExcelClk();
} private void ToExcelClk()
{
GridView1.AllowPaging = false;
GridView1.AllowSorting = false; ImportExcel(GridView1, FileName()); GridView1.DataBind();
GridView1.AllowSorting = true;
GridView1.AllowPaging = true;
} public void ImportExcel(System.Web.UI.WebControls.GridView gv, string strFileName)
{
System.Web.HttpContext obj = System.Web.HttpContext.Current;
obj.Response.Clear();
obj.Response.Buffer = true;
obj.Response.Charset = "UTF-8";
obj.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8) + ".xls");
obj.Response.ContentEncoding = System.Text.Encoding.UTF8;
obj.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
//obj.Response.ContentType = "application/vnd.ms-excel"; System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
gv.RenderControl(htw);
obj.Response.Write(sw.ToString());
obj.Response.End();
} public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
public string FileName()
{
return DateTime.Now.ToString("yyyyMMddhhmmssfff");
}
试试看
protected void btnexcel_Click(object sender, EventArgs e)
{
SqlConnection strcon = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strcon"]);
strcon.Open();
for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
CheckBox cbox = (CheckBox)GridView1.Rows[i].FindControl("chk");
if (cbox.Checked == true)
{
string strsql = "select * from Fault ID='" + GridView1.DataKeyNames[i].value + "'";
SqlCommand scd = new SqlCommand(strsql, strcon);
scd.ExecuteNonQuery(); ToExcel();
}
}
}
protected void ToExcel()
{
???
}
请问怎么在ToExcel获取查询到的数据,并把它导出到Excel?
自己写格式吧.自己写好格式然后导入 我以前这么做的.
在导入到execl中
http://hi.baidu.com/boblong/blog/item/1e156d271355d409918f9df2.html
你定义一个全局变量,用list接收每一次选择后的行, 通过list得到datatable,传到链接里面的方法中就可以,我试过了
List<Fault> list = new List<Fault>();
private static List<Fault> listFault = new List<Fault>();
scd.ExecuteNonQuery() 改成 SqlDataReader reader = scd.ExecuteReader()
while(reader.Read())
{
// 这里面存选中的值
Fault fault = new Fault();
// 这个自己写
fault.**=reader[""].ToString();
list.add(fault); // 这个list 找个全局变量接收
listFault = list; // 把这个listFault当做参数传给datatable
}//懒得改了,大概就这个意思 protected DataTable GetDataTable(List<Fault> list)
{
DataTable dt = new DataTable(); DataColumn dc1 = new DataColumn("operateMan", Type.GetType("System.String"));
DataColumn dc2 = new DataColumn("operateName", Type.GetType("System.String"));
DataColumn dc3 = new DataColumn("operateTime", Type.GetType("System.DateTime")); dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3); dt.Columns[0].ColumnName = "操作人";
dt.Columns[1].ColumnName = "操作名称";
dt.Columns[2].ColumnName = "操作时间"; if (null != list)
{
foreach (OperateLog ol in list) // 这个是把list里面的值循环存到datatable里面
{
DataRow dr = dt.NewRow();
dr[0] = ol.operateMan;
dr[1] = ol.OperateName;
dr[2] = ol.OperateTime;
dt.Rows.Add(dr);
}
return dt;
}
else
{
return null;
}
}