How to read/include excel file (*.xls ) to VC++

Comments

• dwccgc

  June 2004

  : Excuse me:
  : I'd like to know How can I read/include Excel file (.xls) into VC++ ?
  :
  : I mean, is there a API or class to access it ?
  :
  : thankx
  :
  Maybe this site can help you.
  http://www.codeproject.com/database/excel_odbc.asp
  
• TribalTech

  June 2004

  : Excuse me:
  : I'd like to know How can I read/include Excel file (.xls) into VC++ ?
  :
  : I mean, is there a API or class to access it ?
  :
  : thankx
  :
  Yes, I have done this before. It involves connecting to Excel thru ActiveX. You can manipulate Excel spread sheets via your C++ application. Want to know more ?
  
• s07452

  June 2004

  yeap ^_^
  thank again!
  
• TribalTech

  June 2004

  : yeap ^_^
  : thank again!
  :
  I have the project at work. I'll zip the project. I'll need an e-mail address to send it to. The project will be a bare bones ActiveX connection exercise. There is enough functionality in it to understand the fundamentals of doing what you need to do. It is not the simplest thing in the world, but do-able. I have used this project as a starting point for a few applications and then expanded it into my new application. This should get you up the learning curve pretty fast.
  ->TribalTech<-
  
• s07452

  July 2004

  I am deeply appreciated your help ^_^
  
  [email protected]
  
• dshan

  July 2004

  : : yeap ^_^
  : : thank again!
  : :
  : I have the project at work. I'll zip the project. I'll need an e-mail address to send it to. The project will be a bare bones ActiveX connection exercise. There is enough functionality in it to understand the fundamentals of doing what you need to do. It is not the simplest thing in the world, but do-able. I have used this project as a starting point for a few applications and then expanded it into my new application. This should get you up the learning curve pretty fast.
  : ->TribalTech<-
  :
  
  Hi, TribalTech
  
  I have the same problem and like to learn the ActiveX way to read Excel file and insert data into SQL. Basically I need read excel file to VC++.
  
  Thank you a lot. Can you also zip your project and send to me? I am not sure how big is your project? I worry the email system can not handle this big size. You can also cc to this following account: [email protected]
  
  dshan
  
• getcode

  July 2004

  Can u send me ur project to [email protected] .
  
  does it consume a lot of time to get the connection (consider i am a novice). Does it allow to read nd write on to the excel file.
  
  thnx u
  
  
  
  : I have the project at work. I'll zip the project. I'll need an e-mail address to send it to. The project will be a bare bones ActiveX connection exercise. There is enough functionality in it to understand the fundamentals of doing what you need to do. It is not the simplest thing in the world, but do-able. I have used this project as a starting point for a few applications and then expanded it into my new application. This should get you up the learning curve pretty fast.
  : ->TribalTech<-
  :
  
  
• TribalTech

  July 2004

  : Thank you a lot. Can you also zip your project and send to me? I am not sure how big is your project? I worry the email system can not handle this big size. You can also cc to this following account: [email protected]
  :
  : dshan
  :
  Yes, I can zip the project to you. Should I use the [email protected] account or something else. The project is 660k.
  
  TribalTech
  
• TribalTech

  July 2004

  : Can u send me ur project to [email protected] .
  :
  : does it consume a lot of time to get the connection (consider i am a novice). Does it allow to read nd write on to the excel file.
  :
  : thnx u
  Once your application loads Excel and an .xls file, accessing spreadsheets is relatively quick. You can read and write to spreadsheets. In fact you can do almost anything you would do as if you were sitting in front of Excel working it directly.
  
  TribalTech
  
• s07452

  July 2004

  //there is just a example...enjoy it..
  // The system calls this to obtain the cursor to display while the user drags
  // the minimized window.
  HCURSOR CAutoProjectDlg::OnQueryDragIcon()
  {
  return (HCURSOR) m_hIcon;
  }
  
  void CAutoProjectDlg::OnRun()
  {
  // TODO: Add your control notification handler code here
  
  TRACE("test 111");
  _tprintf(_T("Oops - hit an error!
  "));
  
  //OLE variant for optional
  COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
  
  _Application objApp;
  _Workbook objBook; //COleDispatchDriver
  Workbooks objBooks;
  Worksheets objSheets;
  _Worksheet objSheet;
  Range objRange;
  VARIANT ret; //struct
  
  //instantiate excel and open an existing workbook
  objApp.CreateDispatch("Excel.Application");
  objBooks= objApp.GetWorkbooks();
  objBook= objBooks.Open("C:\test.xls",
  VOptional, VOptional, VOptional, VOptional,
  VOptional, VOptional, VOptional, VOptional,
  VOptional, VOptional, VOptional, VOptional);
  objSheets= objBook.GetWorksheets();
  objSheet= objSheets.GetItem(COleVariant((short)1));
  
  //get the range object for which you wish to retrieve the data
  // and then retrieve the data (as a variant array , ret)
  objRange= objSheet.GetRange(COleVariant("A1"), COleVariant("C8"));
  ret= objRange.GetValue();
  
  //create the SAFEARRAY from the Variant ret
  COleSafeArray sa(ret);
  
  //determine the array's dimension
  long lNumRows;
  long lNumCols;
  
  //sa.GetUBound(0, &lz); //無效的索引
  sa.GetUBound(1, &lNumRows); //retrieve the upper bound for any dimension of a safe array
  sa.GetUBound(2, &lNumCols); //範圍由上面的_Worksheet.GetRange()決定
  
  //display the elements in the SAFEARRAY
  long index[2];
  VARIANT val;
  int r, c;
  
  s_test1 testf[9];
  double buf[1];
  char sbuf[20];
  LPCTSTR sstr; //pointer
  CString str1(""); //class
  char* pszFileName = "c:\11.dat";
  CFile myFile;
  CFileException fileException;
  CArchive ar( &myFile, CArchive::store);
  
  if ( !myFile.Open( pszFileName, CFile::modeCreate |
  CFile::modeReadWrite ), &fileException )
  {
  TRACE( "Can't open file %s, error = %u
  ",
  pszFileName, fileException.m_cause );
  }
  
  
  TRACE("Contents of SafeArray
  ");
  TRACE("=====================
  ");
  for (c=1; c<=lNumCols; c++)
  {
  TRACE(" Col %d", c);
  }
  TRACE("
  ");
  for (r=1; r<=lNumRows; r++)
  {
  TRACE("Row %d", r);
  for (c=1; c<=lNumCols; c++)
  {
  index[0]= r;
  index[1]= c;
  sa.GetElement(index, &val); //Retrieves a single element of the safe array
  switch(val.vt)
  {
  case VT_R8:
  {
  TRACE(" %1.2f", val.dblVal);
  //testf[r].m_int1= val.dblVal;
  //buf[0]= val.dblVal;
  //myFile.Write(buf, sizeof(buf));
  if( ar.IsStoring() )
  ar<< val.dblVal;
  break;
  }
  case VT_BSTR:
  {
  TRACE(" %s", (CString)val.bstrVal);
  //testf[r].m_str1= (CString)val.bstrVal;
  //TRACE(" %s", testf[r].m_str1);
  //sbuf= (CString)val.bstrVal;
  //sstr= (CString)val.bstrVal;
  //myFile.Write(sstr, 20);
  
  ZeroMemory(sbuf, 20);
  int ii= ((CString)val.bstrVal).GetLength();
  for (int li=0; li< ii; li++)
  sbuf[li]= ((CString)val.bstrVal).GetAt(li);
  //str1= (CString)val.bstrVal;
  //ZeroMemory(sbuf, 20);
  //int i=0;
  /*while(str1[i]!= NULL)
  {
  sbuf[i]= str1[i];
  i++;
  } */
  if( ar.IsStoring() )
  //ar.WriteString(sstr);
  //ar.Write((CString)val.bstrVal, 20);
  ar.Write(sbuf, 20);
  break;
  }
  case VT_EMPTY:
  {
  TRACE(" <empty>");
  break;
  }
  }//end switch
  }//end for
  TRACE("
  ");
  }//end for
  
  ar.Close();
  myFile.Close();
  
  //close the workbook without saving changes
  //and quit microsoft excel
  objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);
  objApp.Quit();
  
  
  
  
  /*
  //Ole variant for optional
  COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
  
  _Application objApp;
  
  _Workbook objBook;
  Workbooks objBooks;
  
  Worksheets objSheets;
  _Worksheet objSheet;
  
  Range range;
  
  if (!UpdateData(TRUE))
  {
  return;
  }
  
  //instantiate excel and start a new workbook
  objApp.CreateDispatch("Excel.Application");
  objBooks= objApp.GetWorkbooks();
  objBook= objBooks.Add(VOptional);
  objSheets= objBook.GetWorksheets();
  objSheet= objSheets.GetItem(COleVariant((short)1));
  
  //get the range where the starting cell has the address
  //m_sStartingCell and it's dimensions are m_iNumRows*m_iNumCols
  range= objSheet.GetRange(COleVariant(m_sStartingCell), COleVariant(m_sStartingCell));
  range= range.GetResize(COleVariant(m_iNumRows), COleVariant(m_iNumCols));
  
  //fill the range with an array of value
  //create the SAFEARRAY
  COleSafeArray saRet;
  DWORD numElements[2];
  
  numElements[0]= m_iNumRows; //number of rows in the range
  numElements[1]= m_iNumCols; //number of columns in the range
  
  if (m_bFillWithStrings)
  {
  saRet.Create(VT_BSTR, 2, numElements);
  }
  else
  {
  saRet.Create(VT_R8, 2, numElements);
  }
  
  //fill the SAFEARRAY
  long index[2];
  long iRow;
  long iCol;
  
  for (iRow=0; iRow<=m_iNumRows-1; iRow++)
  {
  for (iCol=0; iCol<=m_iNumCols-1; iCol++)
  {
  index[0]= iRow;
  index[1]= iCol;
  if (m_bFillWithStrings) //fill with strings
  {
  VARIANT v;
  CString s;
  
  VariantInit(&v);
  v.vt= VT_BSTR;
  s.Format("r%dc%d", iRow, iCol);
  v.bstrVal= s.AllocSysString();
  saRet.PutElement(index, v.bstrVal);
  SysFreeString(v.bstrVal);
  VariantClear(&v);
  }
  else
  {
  double d;
  
  d= (iRow* 1000)+ iCol;
  saRet.PutElement(index, &d);
  }
  }
  }
  
  //set the range value to SAFEARRAY
  range.SetValue(COleVariant(saRet));
  saRet.Detach();
  
  //return control of excel to the user
  objApp.SetVisible(TRUE);
  objApp.SetUserControl(TRUE);
  */
  
  /*
  _Application app; //excel _Application object
  
  //statr excel and get application object
  if (!app.CreateDispatch("Excel.Application"))
  {
  AfxMessageBox("Couldn't statr excel");
  }
  else
  {
  //Make exvel visible and display a message
  app.SetVisible(TRUE);
  AfxMessageBox("Excel is running");
  }
  */
  /*
  //commonly used OLE variants
  COleVariant covTrue((short)TRUE),
  covFalse((short)FALSE),
  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
  
  _Application app;
  Workbooks books;
  _Workbook book;
  
  Worksheets sheets;
  _Worksheet sheet;
  Range range;
  Font font;
  Range cols;
  
  //start excel and get application object
  
  if (!app.CreateDispatch("Excel.Application"))
  {
  AfxMessageBox("Couldn't start excel and get applocation object");
  return;
  }
  
  //get a new work book
  books= app.GetWorkbooks();
  book= books.Add(covOptional);
  
  //get the first sheet
  sheets= book.GetSheets();
  sheet= sheets.GetItem(COleVariant((short)1));
  
  //fill cells A1, B1, C1, and D1 one cell at a time with "headers"
  range= sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
  range.SetValue(COleVariant("Fist name"));
  range= sheet.GetRange(COleVariant("B1"), COleVariant("B1"));
  range.SetValue(COleVariant("Last name"));
  range= sheet.GetRange(COleVariant("C1"), COleVariant("C1"));
  range.SetValue(COleVariant("Full name"));
  range= sheet.GetRange(COleVariant("D1"), COleVariant("D1"));
  range.SetValue(COleVariant("Salary"));
  
  //format A1:D1 as bold, vertical alignment= center
  range= sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
  font= range.GetFont();
  font.SetBold(covTrue);
  range.SetVerticalAlignment(COleVariant((short)-4108)); //xlVAlignCenter=-4108
  
  //fill A2:B6 with an array of values (first & last name)
  {
  COleSafeArray saRet;
  DWORD numElements[]= {5, 2}; //5*2 element array
  
  saRet.Create(VT_BSTR, 2, numElements);
  
  //fill the 5*2 safearray with the following data
  //John Smith
  //Tom Brown
  //Sue Thomas
  //Jane Jones
  //Adam Johnson
  
  FillSafeArray(L"John", 0, 0, &saRet);
  FillSafeArray(L"Smith", 0, 1, &saRet);
  FillSafeArray(L"Tom", 1, 0, &saRet);
  FillSafeArray(L"Brown", 1, 1, &saRet);
  FillSafeArray(L"Sue", 2, 0, &saRet);
  FillSafeArray(L"Thomas", 2, 1, &saRet);
  FillSafeArray(L"Jane", 3, 0, &saRet);
  FillSafeArray(L"Jones", 3, 1, &saRet);
  FillSafeArray(L"Adam", 4, 0, &saRet);
  FillSafeArray(L"Johnson", 4, 1, &saRet);
  
  range= sheet.GetRange(COleVariant("A2"), COleVariant("B6"));
  range.SetValue(COleVariant(saRet));
  
  saRet.Detach();
  }
  
  //fill C2:C6 with a relative formula (=A2 & " " & B2)
  range= sheet.GetRange(COleVariant("C2"), COleVariant("C6"));
  range.SetFormula(COleVariant("=A2 & "" & B2"));
  
  //fill D2:D6 with a formula (=RAND()*100000) and apply a number format
  range= sheet.GetRange(COleVariant("D2"), COleVariant("D6"));
  range.SetFormula(COleVariant("=RAND()*100000"));
  range.SetNumberFormat(COleVariant("$0.00"));
  
  //autofit columns A:D
  range= sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
  cols= range.GetEntireColumn();
  cols.AutoFit();
  
  //manipulate a varialbe number of columns for quarterly sales data
  {
  short NumQtrs;
  CString msg;
  Range resizedrange;
  Interior interior;
  Borders borders;
  
  //determine how many quarters to display data for
  for (NumQtrs=1; NumQtrs<=3; NumQtrs++)
  {
  msg.Format("Enter sales data for %d quarter(s)?", NumQtrs);
  if (AfxMessageBox(msg, MB_YESNO)== IDYES)
  {
  break;
  }
  }
  msg.Format("Display data for %d quarters.", NumQtrs);
  AfxMessageBox(msg);
  
  //starting at E1, fill headers for the number of columns selected
  range= sheet.GetRange(COleVariant("E1"), COleVariant("E1"));
  resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
  resizedrange.SetFormula(COleVariant("="Q" & COLUMN()-1 & CHAR(10) & "Sales""));
  
  //change the orientation and wraptext properties for the headers
  resizedrange.SetOrientation(COleVariant((short)38));
  resizedrange.SetWrapText(covTrue);
  
  //fill the interior color of the headers
  interior= resizedrange.GetInterior();
  interior.SetColorIndex(COleVariant((short)36));
  
  //fill the columns with a formula and apply a number format
  range= sheet.GetRange(COleVariant("E2"), COleVariant("E6"));
  resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
  resizedrange.SetFormula(COleVariant("=RAND()*100"));
  resizedrange.SetNumberFormat(COleVariant("$0.00"));
  
  //apply borders to the sales data headers
  range= sheet.GetRange(COleVariant("E1"), COleVariant("E6"));
  resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
  borders= resizedrange.GetBorders();
  borders.SetWeight(COleVariant((short)2)); //xlThin= 2
  
  //add a totals formula for the quarterly sales data and apply a border
  range= sheet.GetRange(COleVariant("E8"), COleVariant("E8"));
  resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
  resizedrange.SetFormula(COleVariant("=SUM(E2:E6)"));
  borders= resizedrange.GetBorders();
  {
  Border bottomborder;
  
  bottomborder= borders.GetItem((long)9);
  bottomborder.SetLineStyle(COleVariant((short)-4119)); //xlDouble= -4119
  bottomborder.SetWeight(COleVariant((short)4)); //xlThick= 4
  }
  }
  
  //make the application visible and give the user control of Microsoft Excel
  app.SetVisible(TRUE);
  app.SetUserControl(TRUE);
  */
  
  }
  
• dshan

  July 2004

  : : Thank you a lot. Can you also zip your project and send to me? I am not sure how big is your project? I worry the email system can not handle this big size. You can also cc to this following account: [email protected]
  : :
  : : dshan
  : :
  : Yes, I can zip the project to you. Should I use the [email protected] account or something else. The project is 660k.
  :
  : TribalTech
  :
  
  Hi, TribalTech
  
  Yes, my yahoo account has 100MB space. Feel free to send that zipped project to my [email protected] account.
  
  Thank you a lot.
  
  
• xyliu99

  July 2004

  TribalTech:
  
  I need to do the same thing, and would you please send me a copy of your zipped project too to [email protected]?
  
  Thanks a lot for your help!
  
  : : Can u send me ur project to [email protected] .
  : :
  : : does it consume a lot of time to get the connection (consider i am a novice). Does it allow to read nd write on to the excel file.
  : :
  : : thnx u
  : Once your application loads Excel and an .xls file, accessing spreadsheets is relatively quick. You can read and write to spreadsheets. In fact you can do almost anything you would do as if you were sitting in front of Excel working it directly.
  :
  : TribalTech
  :
  
  
• rodolfosr

  November 2004

  : TribalTech:
  
  I really need this project too. Would you like to send me a copy ?
  [email protected]
  
  Thanks !!
  
  Rodolfo
  
  
  
  
  : TribalTech:
  :
  : I need to do the same thing, and would you please send me a copy of your zipped project too to [email protected]?
  :
  : Thanks a lot for your help!
  :
  : : : Can u send me ur project to [email protected] .
  : : :
  : : : does it consume a lot of time to get the connection (consider i am a novice). Does it allow to read nd write on to the excel file.
  : : :
  : : : thnx u
  : : Once your application loads Excel and an .xls file, accessing spreadsheets is relatively quick. You can read and write to spreadsheets. In fact you can do almost anything you would do as if you were sitting in front of Excel working it directly.
  : :
  : : TribalTech
  : :
  :
  :
  
  
• prabhakar_er

  August 2010

  Hi ,
  I got your mail id from http://www.programmersheaven.com/mb/mfc_coding/264413/264413/how-to-readinclude-excel-file-xls--to-vc++/
  
  I need to do the same thing, and would you please send me a copy of your zipped project too me.
  
  Thanks a lot for your help!
  
• dmytro

  August 2010

  Maybe this library can help you - [link=http://www.libxl.com]http://www.libxl.com[/link]