Total N mg/L Height Total N Corr.Ht Total N Flag Total N Time Total N Type2 Identity2
2.9702 21704 0 IW 125 IW Initial Wash
5.3324 21853 63 N 1920 T Tracer
3.9275 21901 26 A 2120 D Drift
2.9702 21961 0 N 2302 W Wash
1.5448 22610 -38 A 2597 S1 Standard
0.7708 22911 -59 A 2796 S2 Standard
0.2641 23024 -73 N 2999 S3 Standard
-0.5492 22091 -94 A 3126 S4 Standard
5.4988 37458 68 A 3397 S5 Standard
4.4707 37377 40 N 3597 S6 Standard
上面是我从我要读取的文件中摘出一部分格式,假设我需要第一列和第五列的数据。我应该如何实现呢????
谢谢!!!
2.9702 21704 0 IW 125 IW Initial Wash
5.3324 21853 63 N 1920 T Tracer
3.9275 21901 26 A 2120 D Drift
2.9702 21961 0 N 2302 W Wash
1.5448 22610 -38 A 2597 S1 Standard
0.7708 22911 -59 A 2796 S2 Standard
0.2641 23024 -73 N 2999 S3 Standard
-0.5492 22091 -94 A 3126 S4 Standard
5.4988 37458 68 A 3397 S5 Standard
4.4707 37377 40 N 3597 S6 Standard
上面是我从我要读取的文件中摘出一部分格式,假设我需要第一列和第五列的数据。我应该如何实现呢????
谢谢!!!
#include <odbcinst.h> 2. 通过ODBC直接创建Excel文件并在表中插入数据(暂定文件名:Demo.xls) //创建并写入Excel文件
void CRWExcel::WriteToExcel()
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
CString sExcelFile = "c:\\demo.xls"; // 要建立的Excel文件
CString sSql;
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 demo (Name TEXT,Age NUMBER)";
database.ExecuteSQL(sSql); // 插入数值
sSql = "INSERT INTO demo (Name,Age) VALUES (''徐景周'',26)";
database.ExecuteSQL(sSql); sSql = "INSERT INTO demo (Name,Age) VALUES (''徐志慧'',22)";
database.ExecuteSQL(sSql); sSql = "INSERT INTO demo (Name,Age) VALUES (''郭徽'',27)";
database.ExecuteSQL(sSql);
} // 关闭数据库
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Excel驱动没有安装: %s",sDriver);
}
END_CATCH_ALL;
} 3. 通过ODBC直接读取Excel文件(暂定文件名:Demo.xls) // 读取Excel文件
void CRWExcel::ReadFromExcel()
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "Demo.xls"; // 将被读取的Excel文件名
// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
AfxMessageBox("没有安装Excel驱动!");
return;
}
// 创建进行存取的字符串
sDsn.Format("ODBC;DRIVER={%s};DSN='''';DBQ=%s", sDriver, sFile); TRY
{
// 打开数据库(既Excel文件)
database.Open(NULL, false, false, sDsn);
CRecordset recset(&database); // 设置读取的查询语句.
sSql = "SELECT Name, Age "
"FROM demo "
"ORDER BY Name ";
// 执行查询语句
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly); // 获取查询结果
while (!recset.IsEOF())
{
//读取Excel内部数值
recset.GetFieldValue("Name ", sItem1);
recset.GetFieldValue("Age", sItem2); // 移到下一行
recset.MoveNext();
} // 关闭数据库
database.Close();
}
CATCH(CDBException, e)
{
// 数据库操作产生异常时...
AfxMessageBox("数据库错误: " + e->m_strError);
}
END_CATCH;
} 4. 获取ODBC中Excel驱动的函数 CString CRWExcel::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver; // 获取已安装驱动的名称(涵数在odbcinst.h里)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
// 检索已安装的驱动是否有Excel...
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//发现 !
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, ''\0'') + 1;
}
while (pszBuf[1] != ''\0''); return sDriver;
}
#include <odbcinst.h>
CString GetExcelDrive()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver; // 获取已安装驱动的名称(涵数在odbcinst.h里)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
// 检索已安装的驱动是否有Excel...
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//发现 !
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0'); return sDriver;
}
读取Excel表格中的数据相当于从数据库的一个表中读取数据。
CDatabase database;
CString sSql;
CString sDriver;sDriver = GetExcelDrive();
if (sDriver.IsEmpty())
{
// 没有发现Excel驱动
MessageBox("没有安装Excel驱动");
return;
}
// 创建进行存取的字符串
//sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, PathName);
sDsn.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, m_InfileName, m_InfileName);
TRY
{
// 打开数据库(既Excel文件)
//database.Open(NULL, false, false, sDsn);
database.OpenEx(sDsn,CDatabase::noOdbcDialog);
CRecordset recset(&database);
// 设置读取的查询语句.
sSql.Format("SELECT * FROM [%S$A1:IV65536]",m_WorkSheet);//m_WorkSheet为表名
// 执行查询语句
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
CString m_RecName1,m_RecName2;
m_RecName1 = recset.m_rgODBCFieldInfos[0].m_strName;//第一列的名称
m_RecName2 = recset.m_rgODBCFieldInfos[1].m_strName;//第二列的名称
while (!recset.IsEOF())
{
//读取Excel内部数值
CString sItem1, sItem2;
recset.GetFieldValue(m_RecName1, sItem1);读取表格第一列中的数据
recset.GetFieldValue(m_RecName2, sItem2);读取表格第二列中的数据
}研究一下这段代码就行了。
图形的界面,在把数据从EXECL,TEXT导入ORACLE的时候很方便.不用自己再写什么东西.
void CGetExcelDataView::OnInitialUpdate()
{
CoInitialize(NULL);
m_strSheetName=_T("");
m_pSqlDB=&(((CPowerCompanyApp *)AfxGetApp())->m_SqlDB);
SYSTEMTIME UpperTime,LowerTime;
GetLocalTime(&UpperTime);
LowerTime = UpperTime;
LowerTime.wYear--;
m_MonthCtrl.SetRange(&LowerTime,&UpperTime);
}void CGetExcelDataView::OnOk()
{
BOOL SystemClockOK=((CPowerCompanyApp *)AfxGetApp())->CheckSystemClock();
if (SystemClockOK==FALSE)
{
HWND hWnd=AfxGetApp()->GetMainWnd()->m_hWnd;
::SendMessage(hWnd,WM_CLOSE,0,0);
return ;
}
SYSTEMTIME SelectDate;
m_MonthCtrl.GetCurSel(&SelectDate);
m_strUserSelectDate.Format(_T("%d"),SelectDate.wYear*100+SelectDate.wMonth);
m_pSqlDB->ExecuteSQL(_T("exec CreateTmpTable"));//调用存贮过程生成临时表
((CPowerCompanyApp *)AfxGetApp())->WriteOperation("exec CreateTmpTable");
CString strFilter= "Worksheet Files (*.xls)|*.xls||";
CFileDialog filedlg(TRUE,"xls",NULL,OFN_HIDEREADONLY|OFN_LONGNAMES|OFN_FILEMUSTEXIST|OFN_ENABLESIZING ,strFilter,this);
filedlg.m_ofn.lpstrTitle=_T("选择存有水电费的电子表格文件");
BOOL Ret=filedlg.DoModal();
if (Ret==IDCANCEL)
return;
/*CRect rt;
GetClientRect(rt);
InvalidateRect(rt);
Sleep(1000);*/
CString FileName=filedlg.GetPathName();
m_strYearMonth=_T("");
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_Application oApp;
oApp.SetVisible(TRUE);
oApp.CreateDispatch("Excel.Application");
// 窗口
Workbooks oBooks;
_Workbook oBook;
COleVariant Password((long)DISP_E_PARAMNOTFOUND, VT_ERROR); //打开带密码的EXCEL时送入密码准备
Password.SetString("helloh",VT_BSTR);
oBooks = oApp.GetWorkbooks();
oBook = oBooks.Open(FileName,
covOptional,
covOptional,
covOptional,
covOptional,
//Password ,//打开带有密码的电子表格
covOptional,
//Password,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional); /*COleVariant Fn((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
Fn.SetString(FileName,VT_BSTR);//存盘时,设置密码
COleVariant Fmt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
Fmt.SetString("Excel 5.0",VT_BSTR);//格式不对
oBook.SaveAs(Fn,
Fmt,//covOptional,
Password,//covOptional,
Password,//covOptional,
covOptional,
covOptional,
(long)0,
covOptional,
covOptional,
covOptional,
covOptional);
*/
Worksheets oSheets;
oSheets = oBook.GetWorksheets();
int nSheetCount=oSheets.GetCount();
BOOL bSuccess=FALSE;
int SheetIndex=1;
GetYearMonthFromExcel(SheetIndex,oSheets);
if (m_strYearMonth.IsEmpty())
{//表中无日期
int RetValue;
CString TmpStr;
TmpStr.Format(_T("第 %d 张表 %s 中无日期信息,是否以刚选择的 %s 作为本次水电费的日期?"),SheetIndex,m_strSheetName,m_strUserSelectDate);
RetValue=MessageBox(TmpStr,"缺少时间信息",MB_YESNO|MB_ICONQUESTION);
if(RetValue==IDYES)
m_strYearMonth=m_strUserSelectDate;
else
{
MessageBox("本次导入水电费数据未能成功,请稍候再试!","提示",MB_OK|MB_ICONINFORMATION);
goto OVER;
}
}
if (m_strYearMonth.Compare(m_strUserSelectDate))
{ //表中日期与用户输入日期不一致
CString TmpStr;
TmpStr.Format(_T("第 %d 张表 '%s' 中的日期 '%s' 与刚选择的日期 '%s' 不相等,\r\n请重新选择日期或修改表中的日期"),SheetIndex,m_strSheetName,m_strYearMonth,m_strUserSelectDate);
MessageBox(TmpStr,"时间不一致",MB_OK|MB_ICONWARNING);
goto OVER;
}
SheetIndex++;
GetWaterElectFeeTotal(SheetIndex,oSheets);
for (SheetIndex=3;SheetIndex<=nSheetCount;SheetIndex++)
ExcelToSqlServer(SheetIndex,oSheets);
bSuccess=TRUE;
OVER:
oApp.Quit();
oApp.ReleaseDispatch();
m_ProgCtrl.ShowWindow(SW_HIDE);
m_MessStatic.ShowWindow(SW_HIDE);
if (bSuccess)
{
CString TmpStr;
TmpStr.Format(_T("导入 %s 月的数据到临时表中"),m_strYearMonth);
((CPowerCompanyApp *)AfxGetApp())->WriteOperation(TmpStr);
MessageBox("数据传递成功,程序即将进行数据核对!","请点击确定按钮",MB_OK|MB_ICONINFORMATION);
LPARAM lParam=1;
HWND hWnd=AfxGetApp()->GetMainWnd()->m_hWnd;
::SendMessage(hWnd,WM_OUTBAR_NOTIFY,NM_OB_ITEMCLICK,lParam);
// CoUninitialize();
}
}
void CGetExcelDataView::ExcelToSqlServer(int nSheetIndex,Worksheets oSheets)
{
_Worksheet oSheet;
oSheet = oSheets.GetItem(COleVariant((short)nSheetIndex));
m_strSheetName=oSheet.GetName();
COleVariant ItemID;
_Application pApp;
COleVariant row, col, value;
Range oRange;
oRange = oSheet.GetUsedRange();//获得Worksheet已使用的范围
oRange = oRange.GetRows(); //获得总行数(LPDISPATCH类型)
long nRow = oRange.GetCount(); //即可获得已使用的行数了。
oRange = oSheet.GetUsedRange();//获得Worksheet已使用的范围
oRange = oRange.GetColumns();
long nCol = oRange.GetCount();
m_ProgCtrl.ShowWindow(SW_NORMAL);
m_ProgCtrl.SetRange(1,nRow);
CString Mess;
Mess.Format(_T("正在向数据库导入 %s 第%d张表:%s 的数据"),m_strYearMonth,nSheetIndex,m_strSheetName);
m_MessStatic.SetWindowText(Mess);
m_MessStatic.ShowWindow(SW_NORMAL);
oRange = oSheet.GetCells();
CString text, temp,SqlStr,strFieldList=_T("");
int ColArr[32];
int ExitFlag=0;
int nFindTimes=0;
int MaxCol=nCol;
//excel的规定:不能有REF!
for(int r=1;r<7;r++)//excel的规定
{
m_ProgCtrl.SetPos(r);
for(int c=1;c<=MaxCol;c++)
{
row = (short)r;
col = (short)c;
ItemID = oRange.GetItem(row,col);
pApp.AttachDispatch(ItemID.pdispVal,TRUE);
text = pApp.GetValue();
text.TrimLeft();
text.TrimRight();
if(text.IsEmpty()) continue;
if (m_strYearMonth.IsEmpty())
m_strYearMonth=GetYearMonth(text);
if (!text.Left(8).Compare(_T("楼栋编号")))//excel的规定
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList=_T("BuildNo,");
}
else if (!text.Left(8).Compare(_T("住户姓名")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("UserName,");
}
else if (!text.Left(4).Compare(_T("单位")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Dept,");
}
else if (!text.Left(8).Compare(_T("本月电费")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("ElectFee,");
}
else if (!text.Compare(_T("水表1用量")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water1_Count,");
}
else if (!text.Compare(_T("水表2起码")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water2_Begin,");
}
else if (!text.Compare(_T("水表2止码")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water2_End,");
}
else if (!text.Compare(_T("水表2用量")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water2_Count,");
}
else if (!text.Compare(_T("水表3起码")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water3_Begin,");
}
else if (!text.Compare(_T("水表3止码")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water3_End,");
}
else if (!text.Compare(_T("水表3用量")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("Water3_Count,");
}
else if (!text.Compare(_T("抄电表起点")))
{
ColArr[nFindTimes]=c;
nFindTimes++; }
else if (!text.Compare(_T("本月水量")))
{
ColArr[nFindTimes]=c;
nFindTimes++;
strFieldList+=_T("WaterCountTotal,");
}
}
// if (ExitFlag==1)
// break;
}
/* if (nFindTimes<6)
{
//MessageBox("sheetxx所提供的数据不全,本次导入数据未能成功!","提示信息",MB_ICONWARING|MB_OK);
//sheetxx 所提供的数据不全,本次导入数据未能成功
return;
}*/
for (;r<nRow;r++)
{
row = (short)r;
col = (short)ColArr[0];
ItemID = oRange.GetItem(row,col);
pApp.AttachDispatch(ItemID.pdispVal,TRUE);
text = pApp.GetValue();
if ( text.IsEmpty()==FALSE && (text.GetLength()>=6) && (atoi(text)>=100000) )
{
break;
}
}
//excel的第r行之后有数据
BOOL BuildNo;//楼栋编号
for( ;r<=nRow;r++)
{
BuildNo=TRUE;
SqlStr=_T("Insert Into TempWETable ("+strFieldList+"YearMonth) Values(");
for(int i=0;i<nFindTimes;i++)
{
row = (short)r;
col = (short)ColArr[i];
ItemID = oRange.GetItem(row,col);
pApp.AttachDispatch(ItemID.pdispVal,TRUE);
text = pApp.GetValue();
switch(i)
{
case 0:
if( (text.IsEmpty()|| (text.GetLength()<6) || (atoi(text)<=100000) ) && i==0 )//i==0表示楼栋编号
{
BuildNo=FALSE;
break;
}
else
{
SqlStr+=_T("'")+text+_T("',"); }
break;
case 1:
case 2:
SqlStr+=_T("'")+text+_T("',");
break;
case 3:
case 4:
...............................
case 30:
case 31:
if (text.Compare(_T("")))
SqlStr+=text+_T("*100,");
else
SqlStr+=_T("0,");
}
}
if (BuildNo==TRUE)//数据库规定“楼栋编号”不能为空
{
SqlStr+=m_strYearMonth+_T(")");
m_pSqlDB->ExecuteSQL(SqlStr);
//Sleep(100);
}
m_ProgCtrl.SetPos(r); }
m_ProgCtrl.SetPos(nRow);
}