将gridview数据导出为excel [英] export gridview data to excel
问题描述
我有一个页面显示gridview中的记录现在我希望此记录优秀现在我已经使用一个按钮点击此按钮，你将获得你的数据到excel但我得到问题每当我把这个按钮和gridview在updatepanel控件它不会显示excel表，但每当我删除updatepanel它将显示putput但在我的情况下，我必须放置updatepanel，因为它对我的应用程序中的搜索按钮非常有用....
我的代码---------------
i have one page showing record in gridview now i want this record to excel now i have using one button click on this button and u will get your data into excel but i m getting problem whenever i m put this button and gridview in updatepanel control it will not show excel sheet but whenever i remove updatepanel it will show the putput but in my case i have to put updatepanel because it is very useful for searching button in my application....
MY code---------------
objBindCus.Mod = 2;
objBindCus.WhereClause = " Where 1 = 1";
if (txtName.Text != "")
{
objBindCus.WhereClause += " And Name Like '" + txtName.Text.Trim() + "' + '%'";
}
objBindCus.CurrentIndex = pager1.CurrentIndex;
objBindCus.Pagesize = pager1.PageSize;
using (DataSet ds = objBindCus.InsertCustomer())
{
if (ds.Tables.Count > 0)
{
GvCustReport.DataSource = ds.Tables[0];
GvCustReport.DataBind();
if (ds.Tables[0].Rows.Count > 0)
{
pager1.ItemCount = Convert.ToDouble(ds.Tables[1].Rows[0][0].ToString());
}
}
}
Response.Clear();
Response.ContentType = "application/vnd.xls";
Response.AddHeader("content-disposition",
"attachment;filename=CustomerRegistrationReport.xls");
Response.Charset = "";
StringWriter swriter = new StringWriter();
HtmlForm form1 = new HtmlForm();
HtmlTextWriter hwriter = new HtmlTextWriter(swriter);
Controls.Add(form1);
form1.Controls.Add(GvExcel);
form1.RenderControl(hwriter);
Response.Write(swriter.ToString());
Response.End();
请有人帮助我...........
please someone help me...........
推荐答案
首先添加
add first
using Excel_12 = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
Excel_12.Application oExcel_12 = null; // Excel_12应用
Excel_12.Workbook oBook = null; // Excel_12工作簿
Excel_12.Sheets oSheetsColl = null; // Excel_12工作表集合
Excel_12.Worksheet oSheet = null; // Excel_12工作表
Excel_12.Range oRange = null; //工作表中的单元格或范围
对象oMissing = System.Reflection.Missing.Value;
//创建Excel_12的实例。
oExcel_12 = new Excel_12.Application（）; < br $>
//让Excel_12对用户可见。
oExcel_12.Visible = true;
//设置UserControl属性所以Excel_12不会被关闭。
oExcel_12.UserControl = true;
// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo（en-US）;
//添加工作簿。
oBook = oExcel_12.Workbooks.Add（oMissing） ;
// Ge t工作表集合
oSheetsColl = oExcel_12.Worksheets;
< br $>
//获取工作表Sheet1
oSheet =（Excel_12.Worksheet）oSheetsColl.get_Item（Sheet1）;
//出口标题
for（ int j = 0; j< dataGridView1.Columns.Count; j ++）
{
oRange =（Excel_12.Range）oSheet.Cells [1，j + 1];
oRange.Value2 = dataGridView1.Columns [j] .HeaderText;
} < br $>
//出口数据
for（int i = 0; i< dataGridView1.Rows.Count - 1; i ++）
{
for（int j = 0; j< dataGridView1.Columns.Count; j ++）
{
oRange =（Excel_12.Range）oSheet.Cells [ i + 2，j + 1];
oRange.Value2 = dataGridView1 [j，i] .Value;
try
{
（（范围）oSheet.Columns [A，Type.Missing]）。AutoFit（）;
//（（范围）oShee t.Cells [i，j]）。EntireColumn.ColumnWidth = 200;
}
catch
{
//（（范围）oSheet.Cells [i，j]）。EntireColumn.ColumnWidth = 200;
}
}
< br $>
}
//释放变量。
//oBook.Close(false,oMissing,oMissing）;
oBook = null;
//oExcel_12.Quit（）;
oExcel_12 = null;
//收集垃圾。
GC.Collect（）;
Excel_12.Application oExcel_12 = null; //Excel_12 Application
Excel_12.Workbook oBook = null; // Excel_12 Workbook
Excel_12.Sheets oSheetsColl = null; // Excel_12 Worksheets collection
Excel_12.Worksheet oSheet = null; // Excel_12 Worksheet
Excel_12.Range oRange = null; // Cell or Range in worksheet
Object oMissing = System.Reflection.Missing.Value;
// Create an instance of Excel_12.
oExcel_12 = new Excel_12.Application();
// Make Excel_12 visible to the user.
oExcel_12.Visible = true;
// Set the UserControl property so Excel_12 won''t shut down.
oExcel_12.UserControl = true;
// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
// Add a workbook.
oBook = oExcel_12.Workbooks.Add(oMissing);
// Get worksheets collection
oSheetsColl = oExcel_12.Worksheets;
// Get Worksheet "Sheet1"
oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");
// Export titles
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Excel_12.Range)oSheet.Cells[1, j + 1];
oRange.Value2 = dataGridView1.Columns[j].HeaderText;
}
// Export data
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Excel_12.Range)oSheet.Cells[i + 2, j + 1];
oRange.Value2 = dataGridView1[j, i].Value;
try
{
((Range)oSheet.Columns["A", Type.Missing]).AutoFit();
//((Range)oSheet.Cells[i, j]).EntireColumn.ColumnWidth = 200;
}
catch
{
// ((Range)oSheet.Cells[i, j]).EntireColumn.ColumnWidth = 200;
}
}
}
// Release the variables.
//oBook.Close(false, oMissing, oMissing);
oBook = null;
//oExcel_12.Quit();
oExcel_12 = null;
// Collect garbage.
GC.Collect();
适用于多个栏目
Excel_12.Application oExcel_12 = null; // Excel_12应用
Excel_12.Workbook oBook = null; // Excel_12工作簿
Excel_12.Sheets oSheetsColl = null; // Excel_12工作表集合
Excel_12.Worksheet oSheet = null; // Excel_12工作表
Excel_12.Range oRange = null; //工作表中的单元格或范围
对象oMissing = System.Reflection.Missing.Value;
//创建Excel_12的实例。
oExcel_12 = new Excel_12.Application（）; < br $>
//让Excel_12对用户可见。
oExcel_12.Visible = true;
//设置UserControl属性所以Excel_12不会被关闭。
oExcel_12.UserControl = true;
// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo（en-US）;
//添加工作簿。
oBook = oExcel_12.Workbooks.Add（oMissing） ;
// Ge t工作表集合
oSheetsColl = oExcel_12.Worksheets;
< br $>
//获取工作表Sheet1
oSheet =（Excel_12.Worksheet）oSheetsColl.get_Item（Sheet1）;
//出口标题
for（ int j = 0; j< dataGridView1.Columns.Count; j ++）
{
oRange =（Excel_12.Range）oSheet.Cells [1，j + 1];
oRange.Value2 = dataGridView1.Columns [j] .HeaderText;
} < br $>
//出口数据
for（int i = 0; i< dataGridView1.Rows.Count - 1; i ++）
{
for（int j = 0; j< dataGridView1.Columns.Count; j ++）
{
oRange =（Excel_12.Range）oSheet.Cells [ i + 2，j + 1];
oRange.Value2 = dataGridView1 [j，i] .Value;
try
{
（（范围）oSheet.Columns [A，Type.Missing]）。AutoFit（）;
（（范围）oSheet。列[B，Type.Mi ssing]）。AutoFit（）;
（（范围）oSheet.Columns [C，Type.Missing]）。AutoFit（）;
（（范围） oSheet.Columns [D，Type.Missing]）。AutoFit（）;
（（范围）oSheet.Columns [E，Type.Missing]）。AutoFit（）;
//((Range)oSheet.Cells [i，j]）。EntireColumn.ColumnWidth = 200;
}
catch
{
//（（范围）oSheet.Cells [i，j]）。EntireColumn.ColumnWidth = 200;
}
}
}
//释放变量。
//oBook.Close(false,oMissing,oMissing）;
oBook = null;
//oExcel_12.Quit();
oExcel_12 = null;
//收集垃圾。
GC.Collect（）;
for more than one column
Excel_12.Application oExcel_12 = null; //Excel_12 Application
Excel_12.Workbook oBook = null; // Excel_12 Workbook
Excel_12.Sheets oSheetsColl = null; // Excel_12 Worksheets collection
Excel_12.Worksheet oSheet = null; // Excel_12 Worksheet
Excel_12.Range oRange = null; // Cell or Range in worksheet
Object oMissing = System.Reflection.Missing.Value;
// Create an instance of Excel_12.
oExcel_12 = new Excel_12.Application();
// Make Excel_12 visible to the user.
oExcel_12.Visible = true;
// Set the UserControl property so Excel_12 won''t shut down.
oExcel_12.UserControl = true;
// System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
// Add a workbook.
oBook = oExcel_12.Workbooks.Add(oMissing);
// Get worksheets collection
oSheetsColl = oExcel_12.Worksheets;
// Get Worksheet "Sheet1"
oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");
// Export titles
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Excel_12.Range)oSheet.Cells[1, j + 1];
oRange.Value2 = dataGridView1.Columns[j].HeaderText;
}
// Export data
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
oRange = (Excel_12.Range)oSheet.Cells[i + 2, j + 1];
oRange.Value2 = dataGridView1[j, i].Value;
try
{
((Range)oSheet.Columns["A", Type.Missing]).AutoFit();
((Range)oSheet.Columns["B", Type.Missing]).AutoFit();
((Range)oSheet.Columns["C", Type.Missing]).AutoFit();
((Range)oSheet.Columns["D", Type.Missing]).AutoFit();
((Range)oSheet.Columns["E", Type.Missing]).AutoFit();
//((Range)oSheet.Cells[i, j]).EntireColumn.ColumnWidth = 200;
}
catch
{
// ((Range)oSheet.Cells[i, j]).EntireColumn.ColumnWidth = 200;
}
}
}
// Release the variables.
//oBook.Close(false, oMissing, oMissing);
oBook = null;
//oExcel_12.Quit();
oExcel_12 = null;
// Collect garbage.
GC.Collect();
这篇关于将gridview数据导出为excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
