I've used many methods derived data to Excel, export process is roughly same, is a banding structure parameters of the INSERT statement first, then set parameters, then fill in the data, then fill in the next record data and so on INSERT multiple records, in addition I opposed joining together the way SQL statements,
There are multiple ways of tried:
(1) VC call ODBC function, problem, in this there is no way (3)
(2) c # WinForm called ADO.NET OleDbXXX class, there is no way (3) the same question,
(3) in the VC call ADO class, insert the first record wouldn't matter, from the beginning of the second record, the first field should be a string, the result found that the field after open the Excel workbook into a value of 1,
The code is as follows:
_variant_t vtMissing (DISP_E_PARAMNOTFOUND VT_ERROR);
//CConnection CCommand0 CParameters, CParameter is used to add the MFC class "in the" TypeLib import msado15. DLL automatically generated class,
CConnection cn;
CCommand0 CMD.//because VC has been in existence in a class called CCommand, to prevent conflicts, VS2010 automatically change name,
CParameters pars.
CParameter par1;
CParameter par2;
CParameter par3;
CParameter par4.

Cstrings STR.
BOOL isOpened=FALSE;

//STR=...//Excel connection string, format, 2003 and 2007 as long as the switch between the connection string, the code behind the same
//2003 format ". The Provider=Microsoft Jet. The OLEDB. 4.0; The Data Source=\ [XLS file path] \ ""; Extended Properties='Excel 8.0; HDR=Yes; IMEX=0 '"
//2007 format "Provider=Microsoft. ACE. The OLEDB. 12.0; The Data Source=\ [XLSX file path] \ ""; Extended Properties='Excel 12.0; HDR=Yes; IMEX=0 '"
if(! Cn. CreateDispatch (L "ADODB. Connection", NULL))
{
return;
}
if(! Mand CMD. CreateDispatch (L "ADODB.Com", NULL))
{
Cn. ReleaseDispatch ();
return;
}
Par1. CreateDispatch (L "ADODB library. The Parameter", NULL);
Par2. CreateDispatch (L "ADODB library. The Parameter", NULL);
Par3. CreateDispatch (L "ADODB library. The Parameter", NULL);
Par4. CreateDispatch (L "ADODB library. The Parameter", NULL);

TRY
{
Cn. Open (STR, L ", "L" ", adConnectUnspecified);
IsOpened=TRUE;
CMD. Put_ActiveConnection (_variant_t (cn));
CMD. Put_CommandText (L "insert into [Sheet1 $], [name], [class], [Chinese], [math]) values (@ p1, @ p2, @ p3, @ p4);" );
CMD. Put_CommandTimeout (30);
CMD. Put_CommandType (adCmdText);
CMD. Put_NamedParameters (VARIANT_TRUE);
Pars. AttachDispatch (CMD) get_Parameters (), TRUE);

Par1. Put_Name (L "@ p1");
Par1. Put_Type (adVarWChar);
Par1. Put_Direction (adParamInput);
Par1. Put_Size (15);
Pars. Append (par1);

Par2. Put_Name (L "@ p2");
Par2. Put_Type (adVarWChar);
Par2. Put_Direction (adParamInput);
Par2. Put_Size (15);
Pars. Append (par2);

Par3. Put_Name (L "@ p3");
Par3. Put_Type (adInteger);
Par3. Put_Direction (adParamInput);
Par3. Put_Size (4);
Pars. Append (par3);

Par4. Put_Name (L "@ p4");
Par4. Put_Type (adInteger);
Par4. Put_Direction (adParamInput);
Par4. Put_Size (4);
Pars. Append (par4);

Cn. BeginTrans ();
//the first record completely normal
Par1. Put_Value (_variant_t (L "zhang"));
Par2. Put_Value (_variant_t (L "high (1) classes"));
Par3. Put_Value (_variant_t (80) (long));
Par4. Put_Value (_variant_t (78) (long));
CMD. The Execute (& amp; VtMissing, & amp; VtMissing, adCmdText | adExecuteNoRecords);
//the second record start out
Par1. Put_Value (_variant_t (L "bill"));// open the Excel workbook find the field into a value of 1!
Par2. Put_Value (_variant_t (L "high (2) classes"));//but the second field and so on field is normal,
Par3. Put_Value (_variant_t (71) (long));
Par4. Put_Value (_variant_t (85) (long));
CMD. The Execute (& amp; VtMissing, & amp; VtMissing, adCmdText | adExecuteNoRecords);

Cn.Com mitTrans ();
}
The CATCH (CException, pEx)
{
PEx - & gt; ReportError (MB_OK | MB_ICONSTOP);
}
END_CATCH
If (isOpened)
{
cn.Close();
IsOpened=FALSE;
}
Par4. ReleaseDispatch ();
Par3. ReleaseDispatch ();
Par2. ReleaseDispatch ();
Par1. ReleaseDispatch ();
Pars. ReleaseDispatch ();
CMD. ReleaseDispatch ();
Cn. ReleaseDispatch ();