List+Control中的数据写入Excel表格----+数据库操作法

将List Control中的数据写入Excel表格---- 数据库操作法
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动CString sExcelFile,sPath;
CString sSql;
//获取主程序所在路径,存在sPath中
GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);
sPath.ReleaseBuffer ();
int nPos;
nPos=sPath.ReverseFind ('\\');
sPath=sPath.Left (nPos + 1);
CString sFileName;
CFileDialog fDlg(TRUE, "xls", sPath + "aaaa.xls",
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
"xls File (*.xls)|*.xls|");
if(fDlg.DoModal()==IDOK)
{
sFileName = fDlg.GetPathName();
}
else
return;
sExcelFile = sFileName; // 要建立的Excel文件
try
{
// 创建进行存取的字符串
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\ ";DBQ=%s",sDriver, sExcelFile, sExcelFile);
// 创建数据库(既Excel表格文件)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// 创建表结构(姓名、年龄)
//sSql = "CREATE TABLE Exceldemo (Name TEXT,Age NUMBER)";
sSql = "create table Report (ID AUTOINCREMENT, 省份char(50), 开始时间date,"
"结束时间date, 宽带用户数Integer, 本周用户数Integer, 本周增长数Integer, 本周注销数Integer, "
"本周净增数Integer, 用户渗透率real,收费用户数Integer, 营销增长用户Integer, 营销注销用户Integer,"
"市场动作char(255),产品情况TEXT)";
database.ExecuteSQL(sSql);
int nItem = 0;
CString sID, sProvices, sStartTime, sEndTime, sKdUser,sWeekUser,sWeekAddUser, sWeekReleaseUser,sWeekRealAddUser, sUserRate, sPayUser, sYxAddUser, sYxReleaseUser, sScdz, sCpqk;
while (nItem < m_List.GetItemCount())
{
sID = m_List.GetItemText(nItem, 0);
sProvices = m_List.GetItemText(nItem, 1);
CString strCString = m_List.GetItemText(nItem, 2); //CString--->COleDateTime if (strCString != "")
{
COleVariant vtime(strCString);
vtime.ChangeType(VT_DATE);
COleDateTime time = vtime;
SYSTEMTIME systime;
VariantTimeToSystemTime(time, &systime);
CTime tm(systime);
sStartTime = tm.Format("%Y-%m-%d");
sStartTime = "#" + sStartTime + "#";
}
else
sStartTime = "";
strCString = m_List.GetItemText(nItem, 3); //CString--->COleDateTime
if (strCString != "")
{
COleVariant vtime2(strCString);
vtime2.ChangeType(VT_DATE);
COleDateTime time2 = vtime2;
SYSTEMTIME systime2;
VariantTimeToSystemTime(time2, &systime2);
CTime tm2(systime2);
sEndTime = tm2.Format("%Y-%m-%d");
sEndTime = "#" + sEndTime + "#";
}
else
sEndTime = "null";
sKdUser = m_List.GetItemText(nItem, 4);
if (sKdUser == "")
sKdUser = "0";
sWeekUser = m_List.GetItemText(nItem, 5);
if (sWeekUser == "")
sWeekUser = "0";
sWeekAddUser = m_List.GetItemText(nItem, 6);
if (sWeekAddUser == "")
sWeekAddUser = "0";
sWeekReleaseUser = m_List.GetItemText(nItem, 7);
if (sWeekReleaseUser == "")
sWeekReleaseUser = "0";
sWeekRealAddUser = m_List.GetItemText(nItem, 8);
if (sWeekRealAddUser == "")
sWeekRealAddUser = "0";
sUserRate = m_List.GetItemText(nItem, 9);
if (sUserRate == "")
sUserRate = "0";
else
{
sUserRate = sUserRate.Left(sUserRate.GetLength() -1);
float f = atof(sUserRate)/100;
sUserRate.Format("%.4f", f);
}
sPayUser = m_List.GetItemText(nItem, 10);
if (sPayUser == "")
sPayUser = "0";
sYxAddUser = m_List.GetItemText(nItem, 11);
if (sYxAddUser == "")
sYxAddUser = "0";
sYxReleaseUser = m_List.GetItemText(nItem, 12);
if (sYxReleaseUser == "")
sYxReleaseUser = "0";
sScdz = m_List.GetItemText(nItem, 13);
sCpqk = m_List.GetItemText(nItem, 14);
CString sValue;
// 插入数值
sSql = "INSERT INTO Report (ID,省份,开始时间,结束时间, 宽带用户数, 本周用户数," "本周增长数, 本周注销数,本周净增数,用户渗透率,收费用户数,营销增长用户,"
"营销注销用户,市场动作,产品情况) "
"VALUES (";
sValue.Format("%s, '%s',%s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,'%s','%s')",
sID, sProvices, sStartTime, sEndTime, sKdUser, sWeekUser, sWeekAddUser, sWeekReleaseUser, sWeekRealAddUser,sUserRate, sPayUser, sYxAddUser, sYxReleaseUser, sScdz, sCpqk);
// + sID + ",'" + sProvices + "',#"
// + sStartTime + "#,#"
// + sEndTime + "#," + sKdUser + "," + 1,1,1,1,0.10,1,1,1,'快速','绿色上网')"; sSql += sValue;
AfxMessageBox(sSql);
database.ExecuteSQL(sSql);
++nItem;
}
}
// 关闭数据库
database.Close();
AfxMessageBox("Excel文件写入成功！"); }
catch( _com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}。