{
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=Report.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
//Response.ContentType ="vnd.ms-excel.numberformat:@"; this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}这个是我的代码，我想把已经查出来的数据插入excel中，但excel每页只能插入3000条，怎么能实现插入更多的数据？，我想可以分页就是分别写道sheet1,sheet2..中但我不知道该怎么改，希望各位指教！
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=Report.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
//Response.ContentType ="vnd.ms-excel.numberformat:@"; this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}这个是我的代码，我想把已经查出来的数据插入excel中，但excel每页只能插入3000条，怎么能实现插入更多的数据？，我想可以分页就是分别写道sheet1,sheet2..中但我不知道该怎么改，希望各位指教！
namespace ExportToExcel
{
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Runtime.InteropServices; /***********************************************************************************
****Class Name : ExcelManger
****Author: zihe ****CopyRight: Reserve this info if you want to User this Class
***********************************************************************************/
public class ExcelManager:IDisposable
{
Excel.Range m_objRange = null;
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.QueryTable m_objQryTable = null;
object m_objOpt = System.Reflection.Missing.Value;
//DataBase-used variable
private System.Data.SqlClient.SqlConnection sqlConn = null;
private string strConnect = string.Empty;
private System.Data.SqlClient.SqlCommand sqlCmd = null; //Sheets variable
private double dbSheetSize = 65535;//the hight limit number in one sheet
private int intSheetTotalSize = 0;//total record can divied sheet number
private double dbTotalSize = 0;//record total number
/// <summary>
/// 建构函数
/// </summary>
public ExcelManager(){} /// <summary>
/// 建构函数
/// </summary>
/// <param name="dbHL">一个Excel表格的最大记录数</param>
/// <param name="dbTotal">该数据库表共查询出多少条记录</param>
/// <param name="intDivide">查询出的记录可分成几个Excel</param>
/// <param name="conn">sqlConnection</param>
public ExcelManager(Double dbHL,Double dbTotal,int intDivide,SqlConnection conn )
{
dbSheetSize = dbHL;
intSheetTotalSize = intDivide;
dbTotalSize = dbTotal;
sqlConn = conn;
}
/// <summary>
/// 建构函数
/// </summary>
/// <param name="dbHL">一个Excel表格的最大记录数</param>
/// <param name="strTableName">需查询的数据库的表名</param>
/// <param name="conn">sqlConnection</param>
public ExcelManager(Double dbHL,string strTableName,SqlConnection conn)
{
dbSheetSize = dbHL;
sqlConn = conn;
intSheetTotalSize = GetTotalSize(strTableName,sqlConn);
} public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if(disposing)
{
// Dispose managed resources.
Marshal.FinalReleaseComObject(m_objExcel);
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
}
}
/// <summary>
/// 取得总记录数跟可分成几个Excel sheet.
/// </summary>
/// <param name="strTableName">被查询的数据库的表名</param>
/// <param name="sqlConn">sqlConnection</param>
/// <returns>可分成Excel Sheet的个数</returns>
private int GetTotalSize(string strTableName,SqlConnection sqlConn)
{
//sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
sqlCmd = new System.Data.SqlClient.SqlCommand("Select Count(*) From "+strTableName, sqlConn);
if(this.sqlConn.State == ConnectionState.Closed) sqlConn.Open();
dbTotalSize = (int)sqlCmd.ExecuteScalar();
sqlConn.Close();
return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize);
} /// <summary>
/// 新建一个Excel实例
/// </summary>
/// <param name="strTitle">Excel表头上的文字</param>
public void DeclareExcelApp(string[] strTitle,string strSql,string strTableName,string strMastTitle)
{
m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = true;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
if (intSheetTotalSize <= 3)
{
if (this.dbTotalSize <= this.dbSheetSize)
{
this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
return;
}
else if (this.dbTotalSize <= this.dbSheetSize * 2)
{
this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle );
return;
}
else
{
this.ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
this.ExportDataByQueryTable(2, true,strTitle,strSql,strTableName,strMastTitle );
this.ExportDataByQueryTable(3, true,strTitle,strSql,strTableName,strMastTitle );
return;
}
}
for (int i = 3; i < intSheetTotalSize; i++)
{
m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
}
ExportDataByQueryTable(1, false,strTitle,strSql,strTableName,strMastTitle );
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(i, true,strTitle,strSql,strTableName,strMastTitle );
}
}
/// <summary>
/// 以用户输入的文件名保存文件
/// </summary>
public void SaveExcelApp()
{
string excelFileName = string.Empty;
SaveFileDialog sf = new SaveFileDialog();
sf.Filter = "*.xls|*.*";
if (sf.ShowDialog() == DialogResult.OK)
{
excelFileName = sf.FileName;
}
else
{
return;
}
m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt,m_objOpt);
if (m_objExcel != null)
m_objExcel = null;
}
/// <summary>
/// 利用Excel的QueryTable导出数据
/// </summary>
/// <param name="intSheetNumber">导出第几个sheet</param>
/// <param name="blIsMoreThan">余下的数据是否大于指定的每个Sheet的最大记录数</param>
/// <param name="strTitle">表头,需与查询sql语句对齐一致。</param>
/// <param name="strSql">查询的sql语句，表头的文字需与该sql语句对齐一致。</param>
/// <param name="strTablName">查询的表名</param>
/// <param name="strMastTitle">主标题</param>
/// </summary>
public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan,string[] strTitle,string strSql,string strTablName,string strMastTitle)
{
string strQuery = string.Empty;
if (blIsMoreThan)
{
strQuery = "Select Top " +
this.dbSheetSize + strSql + " From " + strTablName + " Where Not OrderID In (Select Top " +
dbSheetSize * (intSheetNumber - 1) + " OrderID From " + strTablName + ")";
}
else
{
strQuery = "Select Top " + this.dbSheetSize + strSql+ " From "+strTablName; }
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber)); m_objSheet.Cells[1,1] = strMastTitle;
m_objSheet.Cells[2,1] = "打印日期"+DateTime.Now.ToShortDateString();
for(int i = 1;i<=strTitle.Length;i++)
{
m_objSheet.Cells[4,i] = strTitle[i-1].ToString();
}
m_objRange = m_objSheet.get_Range("A5", m_objOpt);
m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery);
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.FieldNames = false;
m_objQryTable.Refresh(false);
}
}
}
private void button2_Click(object sender, EventArgs e)
{
#region ExcelManager封装类导出Excel
String strConnet ="Data Source='localhost';Password = ;User ID=sa;Initial Catalog=Northwind";
System.Data.SqlClient.SqlConnection sqlConn =
new System.Data.SqlClient.SqlConnection(strConnet);
ExcelManager exc = new ExcelManager(65530, "Orders", sqlConn);
try
{
exc.DeclareExcelApp(new string[] { "编号","供应商编号" }, " OrderID,CustomerID ", "Orders", "报表标题");
//exc.SaveExcelApp();
}
catch(Exception E)
{
MessageBox.Show(E.ToString());
}
finally
{
exc.Dispose();
}
#endregion
}