#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();
}