我写的从sqlserver--->excel(现要建立要导入的模版, 实际上是sql server---->预先定义好的excel模版----->复制到excel文件) // TODO: Add your control notification handler code here CFileDialog dlg(FALSE,"xls","exportdata.xls",OFN_OVERWRITEPROMPT,"Worksheet Files (*.xls)|*.xls|All Files (*.*)|*.*||",this); if(dlg.DoModal()!=IDOK) { //MessageBox("dlg error","error",MB_OK); return; } CCsisApp *pApp=(CCsisApp *)AfxGetApp(); // Copy the templat file to the temp file CString stringExcelTemplPath=pApp->m_stringExcelTemplPath; TRACE("Excel Template File's Path=%s\n",stringExcelTemplPath); BOOL bOk=::CopyFile(stringExcelTemplPath+"exporttempl.xls",stringExcelTemplPath+"export.xls",FALSE); if(!bOk) { MessageBox(stringExcelTemplPath,"测试",MB_OK); AfxMessageBox("复制模板文件出错!\n请检查Csis.ini文件中ExcelTemplPath是否正确设置。"); return; } // Get the data from the database C_Recordset rsResult=m_adodc.GetRecordset();////////这里有错误 // If there is no record,return and requery if(!rsResult.GetRecordCount()) { AfxMessageBox("没有导出的数据!"); return; } // Fill the temp file _ConnectionPtr pConnection;
// Open the Excel Data Source // hr=pConnection->Open(bstrConnectionString,"","",NULL); if(SUCCEEDED(hr)) { MessageBox("连接已经打开","测试",MB_OK);//////////////////////////////////////// rsResult.MoveFirst(); try { // Insert data CString stringSQL; int nRecCount=rsResult.GetRecordCount(); // Get the data _variant_t varRows(1L);// adBookFirst; VARIANT varOpt; varOpt.vt = VT_ERROR; varOpt.scode = DISP_E_PARAMNOTFOUND;
_variant_t varFields; varFields=rsResult.GetRows(adGetRowsRest,varRows,varOpt); COleSafeArray saResult(varFields); //Determine upper bounds for both dimensions long lNumRows; long lNumCols; saResult.GetUBound(1, &lNumRows); saResult.GetUBound(2, &lNumCols); //Display the elements in the SAFEARRAY. long index[2]; VARIANT val; //Determine upper bounds for both dimensions long r, c;
saResult.GetLBound(2, &c); for(; c <= lNumCols; c++ ) { saResult.GetLBound(1, &r); _variant_t varyear,vartown,vartotalarea,varpickarea,varjjmpy,varssmpy,varjjmnum,varssmnum; for(;r <= lNumRows; r++ ) { index[0]=r; index[1]=c; //retrieve each element of the safearray saResult.GetElement(index, &val); switch(r) { case 0: varyear=val; break; case 1: vartown=val; break; case 2: vartotalarea=val; break; case 3: varpickarea=val; break; case 4: varjjmpy=val; break; case 5: varssmpy=val; break; case 6: varjjmnum=val; break; case 7: varssmnum=val; break; } } stringSQL.Format("insert into [export$] (字段1,字段2,字段3,字段4,字段5,字段6,字段7,字段8) values (%ld,'%s',%f,%f,%f,%f,%f,%f)", (int)varyear.intVal, (CString)vartown.bstrVal, (double)vartotalarea.dblVal, (double)varpickarea.dblVal, (double)varjjmpy.dblVal, (double)varssmpy.dblVal, (double)varjjmnum.dblVal, (double)varssmnum.dblVal); TRACE("SQL=\n%s\n",stringSQL); _bstr_t bstrSQL=stringSQL; _variant_t varQty(0L); pConnection->Execute(bstrSQL,&varQty,adExecuteNoRecords); } } catch(_com_error &e) { CString stringErrMsg; stringErrMsg.Format("ErrorCode=%08lx\nCode meaning=%s\nSource=%s\nDescription=%s\n", e.Error(), e.ErrorMessage(), (LPCTSTR)_bstr_t(e.Source()), (LPCTSTR)_bstr_t(e.Description()) ); AfxMessageBox(stringErrMsg); } catch(...) { AfxMessageBox("出现未知错误"); } } } pConnection.Release(); // Copy the temp file to the target if(dlg.GetPathName()!=stringExcelTemplPath+"export.xls") { TRACE("Copy '%s' to '%s'\n",stringExcelTemplPath+"export.xls",dlg.GetPathName()); bOk=::CopyFile(stringExcelTemplPath+"export.xls",dlg.GetPathName(),FALSE); if(!bOk) AfxMessageBox("生成数据文件出错"); else AfxMessageBox("数据已导出"); }
建议你看看http://www.vckbase.com/里面有相关的文章!
输出文件写成如下格式:
<Tr><Td>
11111
</TD>
<TD>
2222
</Td></Tr>
保存文件的扩展名为.xls就可以了。(不是正规做法)
实际上是sql server---->预先定义好的excel模版----->复制到excel文件)
// TODO: Add your control notification handler code here
CFileDialog dlg(FALSE,"xls","exportdata.xls",OFN_OVERWRITEPROMPT,"Worksheet Files (*.xls)|*.xls|All Files (*.*)|*.*||",this);
if(dlg.DoModal()!=IDOK)
{
//MessageBox("dlg error","error",MB_OK);
return;
}
CCsisApp *pApp=(CCsisApp *)AfxGetApp();
// Copy the templat file to the temp file
CString stringExcelTemplPath=pApp->m_stringExcelTemplPath; TRACE("Excel Template File's Path=%s\n",stringExcelTemplPath); BOOL bOk=::CopyFile(stringExcelTemplPath+"exporttempl.xls",stringExcelTemplPath+"export.xls",FALSE);
if(!bOk)
{
MessageBox(stringExcelTemplPath,"测试",MB_OK);
AfxMessageBox("复制模板文件出错!\n请检查Csis.ini文件中ExcelTemplPath是否正确设置。");
return;
} // Get the data from the database
C_Recordset rsResult=m_adodc.GetRecordset();////////这里有错误
// If there is no record,return and requery
if(!rsResult.GetRecordCount())
{
AfxMessageBox("没有导出的数据!");
return;
}
// Fill the temp file
_ConnectionPtr pConnection;
HRESULT hr;
hr=pConnection.CreateInstance(__uuidof(Connection));
CString strexcelfile="f:\\work\\Csis\\export.xls";
if(SUCCEEDED(hr))
{
// Get the Excel DSN
MessageBox("创建实例成功","测试",MB_OK);////////////////////////
CString strOpen = _T("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=");
strOpen += strexcelfile; //excel file name
strOpen += _T("; Extended Properties=\"Excel 8.0;\"");
hr = pConnection->Open(strOpen.AllocSysString(), _T(""), _T(""),adOptionUnspecified);
//////////////////////////////////////////////////////////////////////
// CString stringConnectionString;
//stringConnectionString.Format("Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Data Source=%s;",pApp->m_stringExcelDSN);
MessageBox(pApp->m_stringExcelDSN,"测试",MB_OK);////////////////////////
//User ID=admin;
//TRACE("ConnectionString=\n%s\n",stringConnectionString); //bstr_t bstrConnectionString=stringConnectionString;
// Open the Excel Data Source
// hr=pConnection->Open(bstrConnectionString,"","",NULL);
if(SUCCEEDED(hr))
{
MessageBox("连接已经打开","测试",MB_OK);////////////////////////////////////////
rsResult.MoveFirst();
try
{
// Insert data
CString stringSQL;
int nRecCount=rsResult.GetRecordCount(); // Get the data
_variant_t varRows(1L);// adBookFirst; VARIANT varOpt;
varOpt.vt = VT_ERROR;
varOpt.scode = DISP_E_PARAMNOTFOUND;
_variant_t varFields;
varFields=rsResult.GetRows(adGetRowsRest,varRows,varOpt); COleSafeArray saResult(varFields); //Determine upper bounds for both dimensions
long lNumRows;
long lNumCols;
saResult.GetUBound(1, &lNumRows);
saResult.GetUBound(2, &lNumCols); //Display the elements in the SAFEARRAY.
long index[2];
VARIANT val; //Determine upper bounds for both dimensions
long r, c;
saResult.GetLBound(2, &c); for(; c <= lNumCols; c++ )
{
saResult.GetLBound(1, &r);
_variant_t varyear,vartown,vartotalarea,varpickarea,varjjmpy,varssmpy,varjjmnum,varssmnum; for(;r <= lNumRows; r++ )
{
index[0]=r;
index[1]=c; //retrieve each element of the safearray
saResult.GetElement(index, &val); switch(r)
{
case 0:
varyear=val;
break;
case 1:
vartown=val;
break;
case 2:
vartotalarea=val;
break;
case 3:
varpickarea=val;
break;
case 4:
varjjmpy=val;
break;
case 5:
varssmpy=val;
break;
case 6:
varjjmnum=val;
break;
case 7:
varssmnum=val;
break;
}
}
stringSQL.Format("insert into [export$] (字段1,字段2,字段3,字段4,字段5,字段6,字段7,字段8) values (%ld,'%s',%f,%f,%f,%f,%f,%f)",
(int)varyear.intVal,
(CString)vartown.bstrVal,
(double)vartotalarea.dblVal,
(double)varpickarea.dblVal,
(double)varjjmpy.dblVal,
(double)varssmpy.dblVal,
(double)varjjmnum.dblVal,
(double)varssmnum.dblVal);
TRACE("SQL=\n%s\n",stringSQL);
_bstr_t bstrSQL=stringSQL; _variant_t varQty(0L);
pConnection->Execute(bstrSQL,&varQty,adExecuteNoRecords); }
}
catch(_com_error &e)
{
CString stringErrMsg;
stringErrMsg.Format("ErrorCode=%08lx\nCode meaning=%s\nSource=%s\nDescription=%s\n",
e.Error(),
e.ErrorMessage(),
(LPCTSTR)_bstr_t(e.Source()),
(LPCTSTR)_bstr_t(e.Description())
); AfxMessageBox(stringErrMsg); }
catch(...)
{
AfxMessageBox("出现未知错误"); }
}
} pConnection.Release(); // Copy the temp file to the target
if(dlg.GetPathName()!=stringExcelTemplPath+"export.xls")
{
TRACE("Copy '%s' to '%s'\n",stringExcelTemplPath+"export.xls",dlg.GetPathName()); bOk=::CopyFile(stringExcelTemplPath+"export.xls",dlg.GetPathName(),FALSE);
if(!bOk)
AfxMessageBox("生成数据文件出错");
else
AfxMessageBox("数据已导出");
}