vs2010导入导出excel表格代码

#include "CApplication.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"

#include <map>
#include <utility>

using namespace std;

//data storage
map<pair<long,long>,CString> strResult;
pair<long,long> strResultNum;

void CdataDlg::OnClickedButton1()
{
 ::CoInitialize(NULL);
 CApplication app;
 CRange range;
 CWorkbooks books;
 CWorksheets sheets;
 CWorkbook book;
 CWorksheet sheet;

 //star excel
 if (!app.CreateDispatch("Excel.Application"))
 {
  AfxMessageBox("无法启动Excel启动器");
  return ;
 }
 //open *.xls
 books.AttachDispatch(app.get_Workbooks());
  //get file path
 LPCTSTR szFilter = _T("txt(*.txt)|*.txt|excel(*.xls)|*.xls|All Filter(*.*)|*.*||");
 CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY,szFilter);
 if (IDOK != dlg.DoModal())
 {
  return ;
 }
 CString strPath = dlg.GetPathName();

 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
 LPDISPATCH lpDisp = books.Open(strPath,covOptional,covOptional,covOptional,
         covOptional,covOptional,covOptional,covOptional,
         covOptional,covOptional,covOptional,covOptional,
         covOptional,covOptional,covOptional);
 //get workbook
 book.AttachDispatch(lpDisp);
 
 //get worksheets
 sheets.AttachDispatch(book.get_Worksheets());

 //get the current active sheet
 lpDisp = book.get_ActiveSheet();
 sheet.AttachDispatch(lpDisp);

 //get used regional information
 CRange UsedRange;
 UsedRange.AttachDispatch(sheet.get_UsedRange());

 //get used line numbers
 range.AttachDispatch(UsedRange.get_Rows());
 long iRowNum = range.get_Count();

 //get used column numbers
 range.AttachDispatch(UsedRange.get_Columns());
 long iColNum = range.get_Count();

 //read the starting line and column
 long iStarRow = UsedRange.get_Row();
 long iStarCol = UsedRange.get_Column();

 //read the table values
 

 for (;iStarRow <= iRowNum;iStarRow++)
 {
  for (iStarCol = UsedRange.get_Column();iStarCol <= iColNum;iStarCol++)
  {
   range.AttachDispatch(sheet.get_Cells());
   range.AttachDispatch(range.get_Item(COleVariant(iStarRow),COleVariant(iStarCol)).pdispVal);
   COleVariant vResult = range.get_Value2();
   CString str;
   if (vResult.vt == VT_BSTR)//character string
   {
    str = vResult.bstrVal;
   }
   else if (vResult.vt == VT_R8)//8 byte of digital
   {
    str.Format("%f",vResult.dblVal);
   }
   else if (vResult.vt == VT_DATE)//date time
   {
    SYSTEMTIME st;
    VariantTimeToSystemTime(vResult.date,&st);
   }
   else if (vResult.vt == VT_EMPTY)//blank space
   {
    str = "";
   }
   strResultNum.first = iStarRow;
   strResultNum.second = iStarCol;
   strResult[strResultNum] = str;
  }
 }

 //release
 sheet.ReleaseDispatch();
 sheets.ReleaseDispatch();
 book.Close(covOptional,COleVariant(strPath),covOptional);
 books.Close();
 app.Quit();
 //CoUninitialize();
}


void CdataDlg::OnBnClickedOk()
{
//  // TODO: 在此添加控件通知处理程序代码
//  CDialogEx::OnOK();
 ::CoInitialize(NULL);
 //export to excel

 //get the export file path
 CFileDialog dlg(FALSE,NULL,NULL,OFN_HIDEREADONLY,"excel(*.xls)|*.xls|txt(*.txt)|*.txt|All Filter(*.*)|*.*||");
 if (IDOK != dlg.DoModal())
 {
  return ;
 }
 CString fname = dlg.GetPathName();

 //define the objects
 CApplication objApp;
 CWorkbooks objBooks;
 CWorkbook objBook;
 CWorksheets objSheets;
 CWorksheet objSheet;
 CRange objRange;

 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
 COleVariant covTrue((short)TRUE),
    covFalse((short)FALSE),
    varFormat((short)-4143),
    varCenter((short)-4108),
    varLeft((short)-4131),
    varText("TEXT",VT_BSTR);

 //creat excel
 objApp.m_bAutoRelease = TRUE;
 if (!objApp.CreateDispatch("Excel.Application"))
 {
  AfxMessageBox("Failed to connect to excel!");
  return ;
 }

 //get Workbooks
 objBooks = objApp.get_Workbooks();

 //open excel file
 objBook.AttachDispatch(objBooks.Add(_variant_t("")));

 //get worksheets
 objSheets = objBook.get_Worksheets();

 //get worksheet
 objSheet = objSheets.get_Item((_variant_t)short(1));

 //set worksheet name
 CString sheetname = "sheetname";
 objSheet.put_Name(sheetname);

 //write to cells
 CString s1;
 CString s;
 pair<long,long> sNum;
 CRange objRange1;
 int row,col;
 CString strRow;
 CString strName = fname;
 char cCell;
 for (row = 1;row <= strResultNum.first;row++)
 {
  for (col = 1;col <= strResultNum.second;col++)
  {
   //get the unit head
   cCell = 'A' + col - 1;
   strName.Format(_T("%c"),cCell);
   strRow.Format(_T("%d"),row);
   strName += strRow;

   s1.Format(strName);
   objRange1 = objSheet.get_Range(_variant_t(s1),_variant_t(s1));

   //get the unit value
   sNum.first = row;
   sNum.second = col;
   s = strResult[sNum];
   objRange1.put_FormulaR1C1(_variant_t(s));
  }
 }

 //save
 objBook.SaveAs(_variant_t(fname),varFormat,covOptional,covOptional,covOptional,covOptional,0,
     covOptional,covOptional,covOptional,covOptional,covOptional);

 //release
 objApp.Quit();
 objRange.ReleaseDispatch();
 objSheet.ReleaseDispatch();
 objSheets.ReleaseDispatch();
 objBook.ReleaseDispatch();
 objBooks.ReleaseDispatch();
}

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值