private Excel.Application m_objExcel=null;
private Excel.Range m_objRange=null;
private Excel.Workbooks m_objBooks=null;
private Excel._Workbook m_objBook=null;
private Excel.Sheets m_objSheets=null;
private Excel._Worksheet m_objSheet=null;
private Excel.QueryTable m_objQryTable = null;
private object m_objOpt = System.Reflection.Missing.Value;
private double dbSheetSize = 65535;//the hight limit number in one sheet
public void DeclareExcelApp()
{
m_objExcel = new Excel.ApplicationClass();
m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks;
m_objBook = (Excel._Workbook) (m_objBooks.Add(m_objOpt));
m_objSheets = (Excel.Sheets) m_objBook.Worksheets;
ArrayList Tlist=new ArrayList ();
StringBuilder sb=new StringBuilder ();
sb.Append("select name from sysobjects where name like '%NeedTran' and xtype='U'");
DataTable dt=new DataTable ();
dt=GetDataBySQL(sb.ToString ());
ExportDataByQueryTable(dt);
}
private void ExportDataByQueryTable(DataTable dt)
{
if(dt.Rows .Count <=3)
{
for(int i=0;i<dt.Rows .Count ;i++)
{
string dtname=dt.Rows [i][0].ToString ();
ExportDataByQueryTable(dtname,i+1);
}
}
for (int i = 3; i < dt.Rows .Count; i++)
{
m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
}
ExportDataByQueryTable(dt.Rows[0][0].ToString (), 1);
for (int i = 2; i <= m_objSheets.Count; i++)
{
ExportDataByQueryTable(dt.Rows[i-1][0].ToString (),i);
}
}
public void ExportDataByQueryTable(string dtname,int i)
{
string strCon=String.Format ("Data Source='{0}';Password = {1};User ID={2};Initial Catalog={3}",LocaServer,LocaLogPass,LocaLogName,LocaDataBase);
DataTable recordsdt=new DataTable() ;
StringBuilder sb=new StringBuilder ();
sb.AppendFormat ("select count(0) from dbo.{0}",dtname);
recordsdt=GetDataBySQL(sb.ToString() );
int totalRecords=Convert.ToInt32 (recordsdt.Rows [0][0]);
string strQuery=String.Format ("select * from {0}",dtname);
string [] val=dtname.Split(new char []{'_'});
m_objSheet = (Excel._Worksheet)m_objSheets[i];
m_objSheet.Cells[1, 1] = val[2];
m_objSheet.Cells[1, 2] = val[2]+"_en";
m_objSheet.Cells[1, 3] = "myid";
if(totalRecords < dbSheetSize)
{
if(dtname.Length <31)
{
m_objSheet.Name =dtname;
}
else
{
m_objSheet.Name =val[2]+"NeedTran" ;
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + strCon, m_objRange, strQuery);
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.FieldNames = false;
m_objQryTable.Refresh(true);
}
else
{
//提示
}
}
public void SaveExcelApp()
{
string excelFileName=string.Empty ;
SaveFileDialog dlog=new SaveFileDialog ();
dlog.Filter ="Excel 文件(*.xls)|*.xls|All files(*.*)|*.*";
dlog.AddExtension =true;
dlog.FilterIndex =1;
dlog.RestoreDirectory =true;
if(dlog.ShowDialog ()==DialogResult.OK )
{
excelFileName = dlog.FileName;
}
else
{
return;
}
try
{
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);
MessageBox.Show ("导出成功!");
}
catch
{
MessageBox.Show ("保存出错!");
}
finally
{
//退出Excel进程
m_objExcel.Quit ();
m_objRange=null;
m_objBooks=null;
m_objBook=null;
m_objSheets=null;
m_objSheet=null;
}
}