gridview 导出到Execl的难题 在数据邦定gridview的时候只能将当前页的数据导出到execl中,但是后面几页的数据却导不出来,必须要翻页才行,请问各位怎么样才能把数据全部导出到execl中? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 用绑定GridView的数据源导出到Excel,不要直接用GridView 那怎么用数据源导出Execl呢 你可以选择将dataset 或者datatable的数据导出来(给你的代码肯定是可以用的,具体格式你得自己去加,操作cell就可以)using System;using System.Collections;using System.Data;//namespace TransferToExcelnamespace realbroader.Export{ /// <summary> /// Class1 的摘要说明。 /// </summary> public class ToExcel { public ToExcel() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 计算DataTable中第几列对应Excel中的列名 /// </summary> /// <param name="n">DataTable中第几列</param> /// <returns>对应Excel中的列名</returns> private string ExlColumn(int n) { if(n > 256) { return ">256"; } int total26 = (int)(n/26); int num = n%26; string result = ""; if(n == 26) { result += (char)(90); } else { for(int i = 0;i <= 9; i++) { if(total26 == 0) { result += (char)(num + 64); return result; } else { if(total26 == i) { if(num != 0) { result += (char)(i + 64); result += (char)(num + 64); } else { result += (char)(i-1 + 64); result += (char)(90); } } } } } return result; } /// <summary> /// 将DataTable保存成Excel /// </summary> /// <param name="table">要转成Excel的DataTable</param> /// <param name="fileName">保存Excel的路径</param> /// <param name="columnName">是否包含类名</param> /// <returns>返回的错误信息,成功返回空串</returns> public string SaveAsExcel(DataTable table,string fileName,bool columnName) { string thisExcelPID = ""; object m_Obj=null; try { /// Creating new Excel.Application; System.Diagnostics.Process[] pBefore = System.Diagnostics.Process.GetProcesses(); string[] beforeApp = new string[pBefore.Length]; for(int i = 0;i<pBefore.Length;i++) { beforeApp[i] = pBefore[i].Id.ToString(); } Excel.Application app = new Excel.Application(); System.Diagnostics.Process[] pAfter = System.Diagnostics.Process.GetProcesses(); for(int ii = 0;ii<pAfter.Length;ii++) { bool hasContain = false; for(int j = 0;j < beforeApp.Length;j++) { if(beforeApp[j] == pAfter[ii].Id.ToString()) { hasContain = true; break; } } if(hasContain == false) { if(pAfter[ii].ProcessName.ToString().Substring(0,5).ToLower() == "excel") { thisExcelPID = pAfter[ii].Id.ToString(); } } } if (app == null) { //无法调用Excel。请确定您的计算机上装了Excel,或Excel是否工作正常 return "无法调用Excel。请确定您的计算机上装了Excel,或Excel是否工作正常"; } /// Making application visible; //app.Visible = true; ///Getting the workbooks collection; Excel.Workbooks workbooks = app.Workbooks; Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Sheets sheets = workbook.Worksheets; Excel.Range range = null; Excel.Range range1 = null; Excel._Worksheet worksheet = (Excel._Worksheet) sheets.get_Item(1); if (worksheet == null) { //Excel内部出错! return "Excel内部出错!"; } int tableColumnsCount = table.Columns.Count; int tableRowssCount = table.Rows.Count; object[] values = new object[table.Columns.Count]; if(columnName) { range = worksheet.get_Range("A1", ExlColumn(tableColumnsCount) + "1"); range.Font.Name="宋体"; range.Font.Bold = true; for(int i=0;i<tableColumnsCount;i++) { values[i] = "'" + table.Columns[i].ColumnName; range.Value = values; } int startrow = 2; for(int row=0;row<tableRowssCount;row++) { for(int col=0;col<tableColumnsCount;col++) { values[col] = "'" + table.DefaultView[row][col].ToString(); } range1 = worksheet.get_Range("A" + startrow.ToString() ,ExlColumn(tableColumnsCount) + startrow.ToString()); range1.Value = values; startrow++; } } else { int startrow = 1; for(int row=0;row<tableRowssCount;row++) { for(int col=0;col<tableColumnsCount;col++) { values[col] = "'" + table.DefaultView[row][col].ToString(); } range1 = worksheet.get_Range("A" + startrow.ToString() ,ExlColumn(tableColumnsCount) + startrow.ToString()); range1.Value = values; startrow++; } } m_Obj = null; workbook.SaveAs(fileName,Excel.XlFileFormat.xlExcel9795 ,m_Obj ,m_Obj ,m_Obj ,m_Obj ,Excel.XlSaveAsAccessMode.xlNoChange ,Excel.XlSaveConflictResolution.xlLocalSessionChanges,m_Obj,m_Obj,m_Obj); workbook.Close(false,fileName,null); app.Quit(); range = null; range1 = null; worksheet = null; workbook = null; app = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); if(thisExcelPID!="") { for(int ii = 0;ii<pAfter.Length;ii++) { if(pAfter[ii].Id.ToString() == thisExcelPID) { pAfter[ii].Kill(); } } } return ""; } catch(Exception ee) { return ee.Message; } } }} 邮箱名规则 全部是什么? 学习MVC中,碰到问题:新增删除有效,更新无效,求解~~ 我想实现逐个字段赋值给GridView,报错“索引超出范围……” 如何将 JavaScript 放到单独的文件 (.js) 中? 做ASP.NET开发的有想换工作的请进! 删除服务器上的文件问题 在C/S中,Internet环境下,服务端如何传递消息给客户端? 关于广告图片切换显示的效果 ASP.NET 中页面编码的问题? 页面设有控件验证,但我想一个按钮的 Click 事件绕开验证控件的验证?请问如何实现? 关于datagrid的问题 二级域名下的ASP.NET网站无法访问?
using System;
using System.Collections;
using System.Data;//namespace TransferToExcel
namespace realbroader.Export
{
/// <summary>
/// Class1 的摘要说明。
/// </summary>
public class ToExcel
{
public ToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
} /// <summary>
/// 计算DataTable中第几列对应Excel中的列名
/// </summary>
/// <param name="n">DataTable中第几列</param>
/// <returns>对应Excel中的列名</returns>
private string ExlColumn(int n)
{
if(n > 256)
{
return ">256";
}
int total26 = (int)(n/26);
int num = n%26;
string result = "";
if(n == 26)
{
result += (char)(90);
}
else
{
for(int i = 0;i <= 9; i++)
{
if(total26 == 0)
{
result += (char)(num + 64);
return result;
}
else
{
if(total26 == i)
{
if(num != 0)
{
result += (char)(i + 64);
result += (char)(num + 64);
}
else
{
result += (char)(i-1 + 64);
result += (char)(90);
}
}
}
}
}
return result;
} /// <summary>
/// 将DataTable保存成Excel
/// </summary>
/// <param name="table">要转成Excel的DataTable</param>
/// <param name="fileName">保存Excel的路径</param>
/// <param name="columnName">是否包含类名</param>
/// <returns>返回的错误信息,成功返回空串</returns>
public string SaveAsExcel(DataTable table,string fileName,bool columnName)
{
string thisExcelPID = "";
object m_Obj=null;
try
{
/// Creating new Excel.Application;
System.Diagnostics.Process[] pBefore = System.Diagnostics.Process.GetProcesses();
string[] beforeApp = new string[pBefore.Length];
for(int i = 0;i<pBefore.Length;i++)
{
beforeApp[i] = pBefore[i].Id.ToString();
}
Excel.Application app = new Excel.Application();
System.Diagnostics.Process[] pAfter = System.Diagnostics.Process.GetProcesses();
for(int ii = 0;ii<pAfter.Length;ii++)
{
bool hasContain = false;
for(int j = 0;j < beforeApp.Length;j++)
{
if(beforeApp[j] == pAfter[ii].Id.ToString())
{
hasContain = true;
break;
}
}
if(hasContain == false)
{
if(pAfter[ii].ProcessName.ToString().Substring(0,5).ToLower() == "excel")
{
thisExcelPID = pAfter[ii].Id.ToString();
}
}
}
if (app == null)
{
//无法调用Excel。请确定您的计算机上装了Excel,或Excel是否工作正常
return "无法调用Excel。请确定您的计算机上装了Excel,或Excel是否工作正常";
}
/// Making application visible;
//app.Visible = true;
///Getting the workbooks collection;
Excel.Workbooks workbooks = app.Workbooks;
Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Sheets sheets = workbook.Worksheets;
Excel.Range range = null;
Excel.Range range1 = null; Excel._Worksheet worksheet = (Excel._Worksheet) sheets.get_Item(1);
if (worksheet == null)
{
//Excel内部出错!
return "Excel内部出错!";
}
int tableColumnsCount = table.Columns.Count;
int tableRowssCount = table.Rows.Count;
object[] values = new object[table.Columns.Count];
if(columnName)
{
range = worksheet.get_Range("A1", ExlColumn(tableColumnsCount) + "1");
range.Font.Name="宋体";
range.Font.Bold = true;
for(int i=0;i<tableColumnsCount;i++)
{
values[i] = "'" + table.Columns[i].ColumnName;
range.Value = values;
} int startrow = 2;
for(int row=0;row<tableRowssCount;row++)
{
for(int col=0;col<tableColumnsCount;col++)
{
values[col] = "'" + table.DefaultView[row][col].ToString();
}
range1 = worksheet.get_Range("A" + startrow.ToString() ,ExlColumn(tableColumnsCount) + startrow.ToString());
range1.Value = values;
startrow++;
}
}
else
{
int startrow = 1;
for(int row=0;row<tableRowssCount;row++)
{
for(int col=0;col<tableColumnsCount;col++)
{
values[col] = "'" + table.DefaultView[row][col].ToString();
}
range1 = worksheet.get_Range("A" + startrow.ToString() ,ExlColumn(tableColumnsCount) + startrow.ToString());
range1.Value = values;
startrow++;
}
}
m_Obj = null;
workbook.SaveAs(fileName,Excel.XlFileFormat.xlExcel9795 ,m_Obj ,m_Obj ,m_Obj ,m_Obj ,Excel.XlSaveAsAccessMode.xlNoChange ,Excel.XlSaveConflictResolution.xlLocalSessionChanges,m_Obj,m_Obj,m_Obj);
workbook.Close(false,fileName,null);
app.Quit();
range = null;
range1 = null;
worksheet = null;
workbook = null;
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
if(thisExcelPID!="")
{
for(int ii = 0;ii<pAfter.Length;ii++)
{
if(pAfter[ii].Id.ToString() == thisExcelPID)
{
pAfter[ii].Kill();
}
}
}
return "";
}
catch(Exception ee)
{
return ee.Message;
} }
}
}