62,046
社区成员
发帖
与我相关
我的任务
分享SQLserver 2000导出Excel问题?????
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
/// <summary>
/// OutExcle 的摘要说明
/// Excel数据导出类
/// </summary>
namespace ExportToExcel
{
/***********************************************************************************
类 名:ExcelManger
作 者: 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;
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
#region 建构函数
/// <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);
}
#endregion
#region 强行销毁
/// <summary>
/// 强行销毁
/// </summary>
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;
}
}
#endregion
#region 取得总记录数跟可分成几个Excel sheet.
///<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);
}
#endregion
...全文
请发表友善的回复…
发表回复
张三疯1 2008-01-18
- 打赏
- 举报
应该权限问题啊,数据能导出成功!那怎么设置连接的访问权限????????
LutzMark 2008-01-18
- 打赏
- 举报
GetTotalSize（）方法有问题
张三疯1 2008-01-18
- 打赏
- 举报
你这种方法对小数量数据导出还是可以,但如果有10w条数据导出话就不行啦!而且一个工作表最多只能存65530条数据
hy_lihuan 2008-01-18
- 打赏
- 举报
你的数据库连接有问题？还是权限问题啊？这么多代码谁来看啊？
LGame 2008-01-18
- 打赏
- 举报
你的sql里不知有没有加权限！！
登陆认证是混合还是系统！！
是一点登陆就出现 输入 sqlserver用户名和密码的对话框？？？？？（类似的！！）
我上次是把guest用户启用了就好了！
登陆认证是混合还是系统！！
是一点登陆就出现 输入 sqlserver用户名和密码的对话框？？？？？（类似的！！）
我上次是把guest用户启用了就好了！
khijfv2008 2008-01-18
- 打赏
- 举报
我在三楼发的方法十W数据也最多两秒出完，通名取字段描述来做header
张三疯1 2008-01-18
- 打赏
- 举报
提示信息:当您试图进行 OLE DB 连接，对于要连接到 SQL Server 的驱动程序却没有指定足够的信息时，Microsoft(R) SQL Server(TM) OLE DB 提供者显示“SQL Server 登录”对话框。
khijfv2008 2008-01-17
- 打赏
- 举报
/// <summary>
/// 获取要查询的字段
/// </summary>
/// <returns></returns>
protected string GetCol()
{
string[] strColumn = new string[]{ "code", "name", "city_dj", "city","date_start", "domain", "vocation",
"locate", "economy_area1", "nature", "contents", "scale", "unit_amt", "investment", "investment2", "grade","way", "state" };//要查询的字段名
//下面是我取页面选择的字段的代码。
string strSql = "";
for (int i = 0; i < strColumn.Length; i++)
{
System.Web.UI.WebControls.CheckBox cbox = (System.Web.UI.WebControls.CheckBox)Page.FindControl(strColumn[i]);
if (cbox.Checked)
{
strSql += strColumn[i] + ",";
}
}
return strSql.Substring(0, strSql.Length - 1);
}
/// <summary>
/// 获取字段描述
/// </summary>
/// <returns></returns>
protected ArrayList getDes()
{
string[] strCoumn = GetCol().Split(',');
SqlDataReader desReader;
ArrayList list = new ArrayList();
int count = strCoumn.Length;
for (int i = 0; i < count; i++)
{
string sql = "SELECT so.name, sc.name AS Expr1, sp.value"
+ " FROM sys.extended_properties AS sp INNER JOIN"
+ " sys.syscolumns AS sc ON sp.major_id = sc.id AND"
+ " sp.minor_id = sc.colid LEFT OUTER JOIN"
+ " sys.sysobjects AS so ON so.id = sc.id"
+ " WHERE (so.name = 'project') AND (sc.name ='" + strCoumn[i] + "')";//project表名
desReader = pubValue.DataRead(sql);//返回SQlDataReader
if (desReader.Read())
{
list.Add(desReader["value"].ToString());
}
desReader.Close();
}
return list;
}
protected ArrayList WriteExce()
{
DataTable table = pubValue.GetDataSet(strChk).Tables[0];//返回dataset
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.UTF8Encoding.Default;
Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls");
//字段名绑定
ArrayList aList = getDes();
for (int i = 0; i < aList.Count; ++i)
{
Response.Write(aList[i].ToString());
Response.Write('\t');
}
Response.Write("\r\n");
//取要绑定的字段
string[] colList = GetCol().Split(',');
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < colList.Length; ++j)
{
Response.Write(table.Rows[i][colList[j].ToString()].ToString());
Response.Write('\t');
}
Response.Write("\r\n");
}
Response.End();
}张三疯1 2008-01-17
- 打赏
- 举报
上面导出Excel导出类，当导出数据时候老是提示SQL连接提示对话框是什么原因???
张三疯1 2008-01-17
- 打赏
- 举报
#region 新建一个Excel实例
///<summary>
/// 新建一个Excel实例
///</summary>
///<param name="strTitle">Excel表头上的文字</param>
///<param name="strSql">导出字段名字</param>
///<param name="strTableName">表名</param>
///<param name="strMastTitle">报表标题</param>
///<param name="strID">主键ID</param>
public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle, string strID)
{
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, strID);
return;
}
else if (this.dbTotalSize <= this.dbSheetSize * 2)
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle, strID);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle, strID);
return;
}
else
{
this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle, strID);
this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle, strID);
this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle, strID);
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, strID);
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle, strID);
}
}
#endregion
#region 以用户输入的文件名保存文件
///<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;
}
#endregion
#region 利用Excel的QueryTable导出数据
///<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 strID)
{
string strQuery = string.Empty;
if (blIsMoreThan)
{
strQuery = "Select Top " +
this.dbSheetSize + strSql + " From " + strTablName + " Where Not " + strID + " In (Select Top " +
dbSheetSize * (intSheetNumber - 1) + strID + " 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);
}
#endregion
}
}
