在项目实现中,要操作excel 对表格进行读写,或者是将listcontrol控件里面的内容导入到表格中,为此在网上找了很多代码作为参考,但是都没有达到自己想要的效果!
在此,将自己在项目开发中遇到的问题和解决方法一一列出,希望能帮到其他人。
问题1
出现range等类的重定义,报错100多项
出错原因
主要是对于excel类库的多次添加导致,可以将类库删除后,重新添加。注意:在添加类中,应该添加自己需要的类,最好不要将类全部添加,否则程序运行会很慢。
同时,对于excel类库中类的操作,要遵循一定的顺序!如下:
Excel的层次结构:
Application
Workbooks
Workbook
......
Worksheets
Worksheet
......
Range
Font
Borders
......
.......
问题2:
程序针对03excel导入成功,但是针对07excel导入会出错,但是测试发现数据已经全部导入到数据库中
问题原因猜测:
可能是在做类库导入时,导入的是03类库,03不能向上兼容07(在没有安装插件包)。但是07能向下兼容03版本
问题解决思路:
直接针对高版本的office开发,也许会兼容低版本的office。
(结果,等实验完成后再做出结论)
下面是相应代码:
(1) 打开对应的文件
CFileDialog bjdrdlg(TRUE,_T("XLS"),_T("*.xls"),OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,"Xls文件(*.xls)|*.xls"); if (bjdrdlg.DoModal()==IDOK) { m_path=bjdrdlg.GetPathName(); UpdateData(false); }
(2) 创建服务
1 CString strFilePath=m_path; 2 _Application m_oExcelApp; // Excel程序 3 _Worksheet m_oWorkSheet; // 工作表 4 _Workbook m_oWorkBook; // 工作簿 5 Workbooks m_oWorkBooks; // 工作簿集合 6 Worksheets m_oWorkSheets; // 工作表集合 7 Range m_oCurrRange; // 使用区域 8 9 10 if (!m_oExcelApp.CreateDispatch( _T( "Excel.Application" ), NULL ) ) 11 { 12 ::MessageBox( NULL, _T( "创建Excel服务失败!" ), _T( "错误提示!" ), MB_OK | MB_ICONERROR); 13 exit(1); 14 } 15 16 //设置为显示 17 m_oExcelApp.SetVisible(FALSE); 18 m_oWorkBooks.AttachDispatch( m_oExcelApp.GetWorkbooks(), TRUE ); //没有这条语句,下面打开文件返回失败。 19 20 LPDISPATCH lpDisp = NULL; 21 COleVariant covTrue((short)TRUE); 22 COleVariant covFalse((short)FALSE); 23 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); 24 Range oCurCell; 25 26 // 打开文件 27 lpDisp = m_oWorkBooks.Open( strFilePath, 28 covOptional, covOptional, covOptional, covOptional, covOptional, 29 covOptional, covOptional, covOptional, covOptional, covOptional, 30 covOptional, covOptional,covOptional, covOptional); 31 // 获得活动的WorkBook( 工作簿 ) 32 m_oWorkBook.AttachDispatch( lpDisp, TRUE ); 33 // 获得活动的WorkSheet( 工作表 ) 34 m_oWorkSheet.AttachDispatch( m_oWorkBook.GetActiveSheet(), TRUE ); 35 // 获得使用的区域Range( 区域 ) 36 m_oCurrRange.AttachDispatch( m_oWorkSheet.GetUsedRange(), TRUE ); 37 38 // 获得使用的行数 39 int lgUsedRowNum = 0; 40 m_oCurrRange.AttachDispatch( m_oCurrRange.GetRows(), TRUE ); 41 lgUsedRowNum = m_oCurrRange.GetCount(); 42 // 获得使用的列数 43 int lgUsedColumnNum = 0; 44 m_oCurrRange.AttachDispatch( m_oCurrRange.GetColumns(), TRUE ); 45 lgUsedColumnNum = m_oCurrRange.GetCount(); 46 // 读取Sheet的名称 47 CString strSheetName = m_oWorkSheet.GetName(); 48 49 //得到全部Cells,此时,CurrRange是cells的集合 50 m_oCurrRange.AttachDispatch( m_oWorkSheet.GetCells(), TRUE );
(3)遍历表格,获取字段信息
oCurCell.AttachDispatch( m_oCurrRange.GetItem( COleVariant( (long)(i + 1)), COleVariant( (long)1) ).pdispVal, TRUE ); VARIANT area_temp = oCurCell.GetText(); str_area= (LPCTSTR)(_bstr_t)area_temp;
(4)数据库操作
(5) 最后关闭服务,这很关键,如果没有的话,excel进程会一直存在服务中,导致下次程序会出现异常
1 m_oWorkBook.Close( covOptional, COleVariant( strFilePath ), covOptional ); 2 m_oWorkBooks.Close(); 3 // 释放 4 m_oCurrRange.ReleaseDispatch(); 5 m_oWorkSheet.ReleaseDispatch(); 6 m_oWorkSheets.ReleaseDispatch(); 7 m_oWorkBook.ReleaseDispatch(); 8 m_oWorkBooks.ReleaseDispatch(); 9 m_oExcelApp.ReleaseDispatch(); 10 m_oExcelApp.Quit(); // 这条语句是推出Excel程序,任务管理器中的EXCEL进程会自动结束。
以下是参考别人文件以及代码:
使用MFC操作EXCEL文件
一、加载
1、 在VC6.0里创建一个MFC工程
2、打开MFC ClassWizard窗口(查看—>建立类向导),选择Automation,单击Add Class按钮,选择From a type library...,弹出文件选择对话框,之后定位到C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE,在生成类中添加所有的对象(其实添加需要的即可,为了简便,不出错保留了冗余),如下图。
3、返回编辑器,查看工程文件,可发现多了EXCEL.H及EXCEL.CPP两个文件,拷贝出来,放在VS2005需要使用excel的工程文件中。
4. 打开stdafx.h头文件确保包含如下头文件:
#include <afxdisp.h>(这个一般有了)
#include "excel.h" (手动添加这个即可)
5. 打开TestExcel.cpp文件,修改CTestExcelApp::InitInstance(),加入如下代码:
if( !AfxOleInit() ){
AfxMessageBox("初始化Ole出错!");
return FALSE;
}
为保证编译时不产生重复定义错误(可以验证一下是否成功加载,没有也能正常执行),我编译时出现了很多“类重复定义”异常,打开excel.h文件,在文件开始位置加入如下代码:
#if !defined _HEAD_FILE_EXCEL9_
#define _HEAD_FILE_EXCEL9_
相应的,在文件末尾加入:
#endif
成功
二、操作EXCEL文件
1. 新建一个excel表,并填充两个单元格的实例
void CTestExcelDlg::OnButton1()
{
//Workbooks—>Workbook —>Worksheets—>Worksheet —>Range
_Application app; //Excel应用程序接口
Workbooks books; //工作薄集合
_Workbook book; //工作薄
Worksheets sheets; //工作表集合
_Worksheet sheet; //工作表
Range range; //Excel中针对单元格的操作都应先获取其对应的Range对象
Font font;
Range cols;
/*
COleVariant类为VARIANT数据类型的包装,在自动化程序中,通常都使用
VARIANT数据类型进行参数传递。故下列程序中,函数参数都是通过COleVariant
类来转换了的。
*/
//covOptional 可选参数的VARIANT类型
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if( !app.CreateDispatch("Excel.Application") ){
this->MessageBox("无法创建Excel应用!");
return;
}
//获取工作薄集合
books=app.GetWorkbooks();
//添加一个工作薄
book=books.Add(covOptional);
//获取工作表集合
sheets=book.GetSheets();
//获取第一个工作表
sheet=sheets.GetItem(COleVariant((short)1));
//选择工作表中A1:A1单元格区域
range=sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
//设置A1=HELLO EXCEL!"
range.SetValue(COleVariant("HELLO EXCEL!"));
//调整格式,设置粗体
font=range.GetFont();
font.SetBold(COleVariant((short)TRUE));
//选择A2单元格,插入一个公式"=RAND()*100000",并设置A2数字格式为货币形
式
range=sheet.GetRange(COleVariant("A2"),COleVariant("A2"));
range.SetFormula(COleVariant("=RAND()*100000"));
range.SetNumberFormat(COleVariant("$0.00"));
//选择A:A列,设置宽度为自动适应
cols=range.GetEntireColumn();
cols.AutoFit();
//显示Excel表格,并设置状态为用户可控制
app.SetVisible(TRUE);
app.SetUserControl(TRUE);
2. 打开一个已有的excel表格实例
CString
strPath;
strPath += "C:\\template.xlt"; // 模板的路径
CFileFind filefind;
if( !filefind.FindFile( strPath ) )
{
AfxMessageBox(
"没有找到模版文档,请其查找" );
return;
}
LPDISPATCH lpDisp; //接口指针
books=app.GetWorkbooks();
lpDisp = books.Open(m_filepath,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional
); //与的不同,是个参数的,直接在后面加了两个covOptional成功了
book.AttachDispatch(lpDisp);
3. 保存一个excel文件实例
book.SetSaved(TRUE);
4. 另存一个excel文件实例
book.SaveAs(COleVariant(m_filename),covOptional,
covOptional,covOptional,
covOptional,covOptional,(long)0,
covOptional,covOptional,covOptional,
covOptional,covOptional); //与的不同,是个参数的,直接在后面加了两个covOptional成功了
5. 释放一个excel文件实例
经试验证实,不释放第二次使用excel时会中断,放在类的析构里面有时调用不到,主动调用最保险。(有没有AttachDispatch()过都要释放,否则报错)
//释放对象(相当重要!)
Rang.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
//退出程序
app.Quit();
//m_ExlApp一定要释放,否则程序结束后还会有一个Excel进程驻留在内存中,而且程序重复运行的时候会出错
app.ReleaseDispatch();
6. 修改一个excel单元格
range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col)));
range.SetValue2(COleVariant(value));
7. 取出一个excel单元格
实现Variant数据类型转换为CString类,这个只是一个示例,转换较为简单。
range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col)));
COleVariant rValue;
rValue=COleVariant(range.GetValue2());
rValue.ChangeType(VT_BSTR);
return CString(rValue.bstrVal);
8. 还有释放问题是最重要的问题:
首先变量必须全释放,无论当初是否绑定过;
其次,程序释放和程序关闭的顺序必须是
app.Quit();
app.ReleaseDispatch();
如果顺如颠倒如下:
app.ReleaseDispatch();
app.Quit();
出现的后果是程序关闭后,excel进程仍然运行,所以无法正常打开程序曾经打开excel表格。
附录 1 #include "../Stdafx.h"
2 3 #include "OptExcel.h" 4 5 #include "excel.h" 6 7 #include "comdef.h" 8 12 13 _Application app; 14 15 Workbooks books; 16 17 _Workbook book; 18 19 Worksheets sheets; 20 21 _Worksheet sheet; 22 23 Range range; 24 25 Range cell; 26 27 Font font; 28 29 30 31 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); 32 37 //////////////////////////////////////////////////////////////////////// 38 39 ///Function: COptExcel 40 41 ///Description: 初始化函数,初始化中附加excel应用程序 42 43 ///Call: app.CreateDispatch(_T("Excel.Application") 44 45 //////////////////////////////////////////////////////////////////////// 46 47 COptExcel::COptExcel(void) 48 49 { 50 51 if (::CoInitialize( NULL ) == E_INVALIDARG) 52 53 { 54 55 AfxMessageBox(_T("初始化Com失败!")); 56 57 return; 58 59 } 60 61 62 63 //验证office文件是否可以正确运行 64 65 66 67 if( !app.CreateDispatch(_T("Excel.Application")) ) 68 69 { 70 71 AfxMessageBox(_T("无法创建Excel应用!")); 72 73 return; 74 75 } 76 77 //在程序执行文件路径名中,剪掉执行文件名,得到程序路径,追加模板文件名,得到模板完整路径 78 79 80 81 82 83 } 84 89 //////////////////////////////////////////////////////////////////////// 90 91 ///Function: ~COptExcel 92 93 ///Description: 析构函数,释放对象,非常重要,不全部释放,占用内存,下 94 95 /// 一次使用此类时会中断 96 97 ///Call: ReleaseDispatch() 98 99 //////////////////////////////////////////////////////////////////////// 100 101 COptExcel::~COptExcel(void) 102 103 { 111 books.ReleaseDispatch(); 113 book.ReleaseDispatch(); 114 115 sheets.ReleaseDispatch(); 116 117 sheet.ReleaseDispatch(); 118 119 range.ReleaseDispatch(); 120 121 font.ReleaseDispatch(); 122 123 cell.ReleaseDispatch(); 127 app.Quit(); 131 app.ReleaseDispatch(); 132 133 ::CoUninitialize(); 134 135 } 136 137 138 139 140 141 //////////////////////////////////////////////////////////////////////// 142 143 ///Function: OpenExcelBook 144 145 ///Description: 打开表名为filename的文件,注意,文件路径非自动生成,以后 146 147 /// 考虑从下处理方法 148 149 ///Call: GetAppPath() 150 151 ///Input: CString filename 文件名 152 153 //////////////////////////////////////////////////////////////////////// 154 155 bool COptExcel::OpenExcelBook(CString filename) 156 157 { 158 159 CFileFind filefind; 160 161 if( !filefind.FindFile(filename) ) 162 163 { 164 165 AfxMessageBox(_T("文件不存在")); 166 167 return false; 168 169 } 170 171 LPDISPATCH lpDisp; //接口指针 172 173 books=app.GetWorkbooks(); 174 175 lpDisp = books.Open(filename, 176 177 covOptional, covOptional, covOptional, covOptional, 178 179 covOptional, covOptional, covOptional, covOptional, 180 181 covOptional, covOptional, covOptional, covOptional, 182 183 covOptional, covOptional 184 185 ); //与office 2000的不同,是个参数的,直接在后面加了两个covOptional成功了 186 187 book.AttachDispatch(lpDisp); 188 189 sheets=book.GetSheets(); 190 191 sheet=sheets.GetItem(COleVariant((short)1)); //与的不同,是个参数的,直接在后面加了两个covOptional成功了 192 193 return true; 194 195 } 196 197 void COptExcel::NewExcelBook() 198 199 { 200 201 books=app.GetWorkbooks(); 202 203 book=books.Add(covOptional); 204 205 sheets=book.GetSheets(); 206 207 sheet=sheets.GetItem(COleVariant((short)1)); //与的不同,是个参数的,直接在后面加了两个covOptional成功了 208 209 } 210 211 212 213 //////////////////////////////////////////////////////////////////////// 214 215 ///Function: OpenExcelApp 216 217 ///Description: 打开应用程序(要注意以后如何识别用户要打开的是哪个文件) 218 219 //////////////////////////////////////////////////////////////////////// 220 221 void COptExcel::OpenExcelApp(void) 222 223 { 224 225 app.SetVisible(TRUE); 226 227 app.SetUserControl(TRUE); 228 229 } 230 231 232 233 //////////////////////////////////////////////////////////////////////// 234 235 ///Function: SaveExcel 236 237 ///Description: 用于打开数据文件,续存数据后直接保存 238 239 //////////////////////////////////////////////////////////////////////// 240 241 void COptExcel::SaveExcel(void) 242 243 { 244 245 book.SetSaved(TRUE); 246 247 } 248 249 250 251 //////////////////////////////////////////////////////////////////////// 252 253 ///Function: SaveAsExcel 254 255 ///Description: 保存excel文件 256 257 //////////////////////////////////////////////////////////////////////// 258 259 void COptExcel::SaveAsExcel(CString filename) 260 261 { 262 263 book.SaveAs(COleVariant(filename),covOptional, 264 265 covOptional,covOptional, 266 267 covOptional,covOptional,(long)0,covOptional,covOptional,covOptional, 268 269 covOptional,covOptional); 270 271 } 272 273 274 275 276 277 //////////////////////////////////////////////////////////////////////// 278 279 ///Function: SetCellValue 280 281 ///Description: 修改单元格内的值 282 283 ///Call: IndexToString() 从(x,y)坐标形式转化为“A1”格式字符串 284 285 ///Input: int row 单元格所在行 286 287 /// int col 单元格所在列 288 289 /// int Align 对齐方式默认为居中 290 291 //////////////////////////////////////////////////////////////////////// 292 293 void COptExcel::SetCellValue(int row, int col,int Align) 294 295 { 296 297 range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col))); 298 299 range.SetValue2(COleVariant(value)); 300 301 cell.AttachDispatch((range.GetItem (COleVariant(long(1)), COleVariant(long(1)))).pdispVal); 302 303 cell.SetHorizontalAlignment(COleVariant((short)Align)); 304 305 } 306 307 308 309 //////////////////////////////////////////////////////////////////////// 310 311 ///Function: GetCellValue 312 313 ///Description: 得到的单元格中的值 314 315 ///Call: IndexToString() 从(x,y)坐标形式转化为“A1”格式字符串 316 317 ///Input: int row 单元格所在行 318 319 /// int col 单元格所在列 320 321 ///Return: CString 单元格中的值 322 323 //////////////////////////////////////////////////////////////////////// 324 325 CString COptExcel::GetCellValue(int row, int col) 326 327 { 328 329 range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col))); 330 331 COleVariant rValue; 332 333 rValue=COleVariant(range.GetValue2()); 334 335 rValue.ChangeType(VT_BSTR); 336 337 return CString(rValue.bstrVal); 338 339 } 340 341 //////////////////////////////////////////////////////////////////////// 342 343 ///Function: SetRowHeight 344 345 ///Description: 设置行高 346 347 ///Call: IndexToString() 从(x,y)坐标形式转化为“A1”格式字符串 348 349 ///Input: int row 单元格所在行 350 351 //////////////////////////////////////////////////////////////////////// 352 353 void COptExcel::SetRowHeight(int row, CString height) 354 355 { 356 357 int col = 1; 358 359 range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col))); 360 361 range.SetRowHeight(COleVariant(height)); 362 363 } 364 365 //////////////////////////////////////////////////////////////////////// 366 367 ///Function: SetColumnWidth 368 369 ///Description: 设置列宽 370 371 ///Call: IndexToString() 从(x,y)坐标形式转化为“A1”格式字符串 372 373 ///Input: int col 要设置列宽的列 374 375 /// CString 宽值 376 377 //////////////////////////////////////////////////////////////////////// 378 379 void COptExcel::SetColumnWidth(int col,CString width) 380 381 { 382 383 int row = 1; 384 385 range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col))); 386 387 range.SetColumnWidth(COleVariant(width)); 388 389 } 390 391 392 393 //////////////////////////////////////////////////////////////////////// 394 395 ///Function: SetRowHeight 396 397 ///Description: 设置行高 398 399 ///Call: IndexToString() 从(x,y)坐标形式转化为“A1”格式字符串 400 401 ///Input: int row 单元格所在行 402 403 //////////////////////////////////////////////////////////////////////// 404 405 CString COptExcel::GetColumnWidth(int col) 406 407 { 408 409 int row = 1; 410 411 range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col))); 412 413 VARIANT width = range.GetColumnWidth(); 414 415 CString strwidth; 416 417 strwidth.Format(CString((LPCSTR)(_bstr_t)(_variant_t)width)); 418 419 return strwidth; 420 421 } 422 423 424 425 //////////////////////////////////////////////////////////////////////// 426 427 ///Function: GetRowHeight 428 429 ///Description: 设置行高 430 431 ///Call: IndexToString() 从(x,y)坐标形式转化为“A1”格式字符串 432 433 ///Input: int row 要设置行高的行 434 435 /// CString 宽值 436 437 //////////////////////////////////////////////////////////////////////// 438 439 CString COptExcel::GetRowHeight(int row) 440 441 { 442 443 int col = 1; 444 445 range=sheet.GetRange(COleVariant(IndexToString(row,col)),COleVariant(IndexToString(row,col))); 446 447 VARIANT height = range.GetRowHeight(); 448 449 CString strheight; 450 451 strheight.Format(CString((LPCSTR)(_bstr_t)(_variant_t)height)); 452 453 return strheight; 454 455 } 456 457 458 459 460 461 //////////////////////////////////////////////////////////////////////// 462 463 ///Function: IndexToString 464 465 ///Description: 得到的单元格在EXCEL中的定位名称字符串 466 467 ///Input: int row 单元格所在行 468 469 /// int col 单元格所在列 470 471 ///Return: CString 单元格在EXCEL中的定位名称字符串 472 473 //////////////////////////////////////////////////////////////////////// 474 475 CString COptExcel::IndexToString( int row, int col ) 476 477 { 478 479 CString strResult; 480 481 if( col > 26 ) 482 483 { 484 485 strResult.Format(_T("%c%c%d"),'A' + (col-1)/26-1,'A' + (col-1)%26,row); 486 487 } 488 489 else 490 491 { 492 493 strResult.Format(_T("%c%d"), 'A' + (col-1)%26,row); 494 495 } 496 497 return strResult; 498 499 } 500 501 502 503 //////////////////////////////////////////////////////////////////////// 504 505 ///Function: LastLineIndex 506 507 ///Description: 得到表格总第一个空行的索引 508 509 ///Return: int 空行的索引号 510 511 //////////////////////////////////////////////////////////////////////// 512 513 int COptExcel::LastLineIndex() 514 515 { 516 int i,j,flag=0; 517 518 CString str; 519 520 for(i=1;;i++) 521 522 { 523 524 flag = 0; 525 526 //粗略统计,认为前列都没有数据即为空行 527 528 for(j=1;j<=5;j++) 529 530 { 531 532 str.Format(_T("%s"),this->GetCellValue(i,j).Trim()); 533 534 if(str.Compare(_T(""))!=0) 535 536 { 537 538 flag = 1; 539 break; 540 541 } 542 543 } 544 545 if(flag==0) 546 547 return i; 548 549 } 550 551 }