S.l.e!ep.¢%

像打了激速一样,以四倍的速度运转,开心的工作
简单、开放、平等的公司文化;尊重个性、自由与个人价值;
posts - 1098, comments - 335, trackbacks - 0, articles - 1
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

The Class of Excel Operation

Posted on 2009-10-05 09:27 S.l.e!ep.¢% 阅读(570) 评论(0)  编辑 收藏 引用 所属分类: Office Automation

The Class of Excel Operation, but
1. 未做异常处理
2. 大部份代码还未验证过

/* EclOp.h*/

#pragma once

//#include "StdAfx.h"
#include <afxwin.h>
#include <iostream>

using namespace std ;

#define NULLSTR _T("")


#import "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\mso.dll" rename("RGB", "MSRGB")

#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" raw_interfaces_only, \
rename("Reference", "ignorethis"), rename("VBE", "JOEVBE")

#import "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" exclude("IFont", "IPicture") \
 rename("RGB", "ignorethis"), rename("DialogBox", "ignorethis"), rename("VBE", "JOEVBE"), \
 rename("ReplaceText", "JOEReplaceText"), rename("CopyFile","JOECopyFile"), \
rename("FindText", "JOEFindText"), rename("NoPrompt", "JOENoPrompt")

using namespace Office;
using namespace VBIDE;
using namespace Excel ;

class CEclOp
{
public:
 CEclOp(void);
 ~CEclOp(void);
public:
 
 BOOL InsertAChart(short nSheet);
 BOOL DeleteASheetByNO(short nSheet);
 BOOL DeleteASheetByName(CString strSheet);
 CString GetCellText(short nSheet, int Row, int Col);
 void DeleteCells(short nSheet, CString strRange);
 void DeleteCells(short nSheet, unsigned int nFRow, unsigned int nFCol, unsigned int nTRow, unsigned int nTCol);
 BOOL Initialization(CString strFilePath = NULLSTR);
 void SetCellText(short nsheet,int row, int col, _variant_t content);
 void SetCellText(short nSheet,CString strCell, _variant_t Content);
 void InsertPicsFromFile(LPCSTR path, short nsheet, double left, double top);
 void InsertSheetsAfter(short nsheet, int num);
 void SetColumnWidth(int nColF, int nColT, double dWidth);
 void SaveQuit(CString strPath);
 void ReleaseAllInterfaces();
 void SetCellColor(short nSheet, CString Range, COLORREF rgb);
 void SetCellColor(short nSheet, int row, int col, COLORREF rgb);
 
private:
 int m_nTotalSheets;
 int m_nCurSheet;
public:
 _ApplicationPtr pApplication;
 _WorkbookPtr pThisWorkbook;
 _WorksheetPtr pThisWorksheet;
 RangePtr pThisRange;
 _variant_t vt ;
 Excel::XlFileFormat vFileFormat ;
 Excel::XlSaveAsAccessMode vSaveAsAccessMode ;
 Excel::XlSaveConflictResolution vSaveConflictResolution ;
public:
 SheetsPtr pThisSheets;
 
};


/*EclOp.cpp*/

//#include "stdafx.h"
#include "EclOp.h"


CEclOp::CEclOp(void)
{
 
}

CEclOp::~CEclOp(void)
{
 
 pThisRange.Release();                      
 pThisWorksheet.Release();                                      
 pThisSheets.Release();                                        
 pThisWorkbook.Release();                              
 pApplication.Release();
 ::CoUninitialize();
}

void CEclOp::ReleaseAllInterfaces()
{
 if (pThisRange != NULL)
  pThisRange.Release();                                     
 if (pThisWorksheet != NULL)
  pThisWorksheet.Release();                                        
 if (pThisSheets != NULL)
  pThisSheets.Release();                                         
 if (pThisWorkbook != NULL)
  pThisWorkbook.Release();                                       
 if (pApplication != NULL)
  pApplication.Release();
 
 ::CoUninitialize();
}
BOOL CEclOp::Initialization(CString strPath)
{
 
 if(FAILED(::CoInitialize(NULL)))
 {
  AfxMessageBox("Initializing Excel Failed!");
  return FALSE;
 }
 pApplication = NULL;
 pThisWorkbook = NULL;
 pThisWorksheet = NULL;
 pThisSheets = NULL;
 pThisRange = NULL;
 
 if (pApplication.CreateInstance("Excel.Application") != S_OK)
  return FALSE;
 // pApplication->PutVisible (0,VARIANT_TRUE);
 pThisWorkbook = pApplication->GetWorkbooks()->Add((_variant_t)strPath) ;
 pThisSheets = pThisWorkbook->GetWorksheets() ;
 return TRUE;
}

void CEclOp::SetColumnWidth(int nColF, int nColT, double dWidth)
{
 CString strRange;
 strRange.Format("%c%d:%c%d", nColF + 'A' - 1, 1, nColT + 'A'- 1, 1 );
 pThisRange = pThisWorksheet->GetRange((_variant_t)strRange);
 pThisRange->GetEntireColumn()->ColumnWidth = dWidth;
 
}


void CEclOp::SetCellText(short nSheet,CString strCell, _variant_t Content)
{
 
 // pThisWorksheet = pThisSheets->GetItem(nSheet);
 // pThisRange = pThisWorksheet->GetRange(COleVariant(strCell),   COleVariant(strCell));
 
 CString strRange;
 
 pThisWorksheet = pThisSheets->GetItem(nSheet);
 
 // strRange.Format("%c%d:%c%d", Col + 'A' - 1, Row, Col + 'A'- 1, Row );//need to confirm;
 
 strRange = strCell + ":" + strCell;
 
 pThisRange = pThisWorksheet->GetRange((_variant_t)strRange);
 
 pThisRange->PutItem((long)1, (long)1, Content);
 
}

CString CEclOp::GetCellText(short nSheet, int Row, int Col)
{
 CString strRet;
 CString strRange;
 pThisWorksheet = pThisSheets->GetItem(nSheet);
 
 if (Col <= 26)
  strRange.Format("%c%d:%c%d", Col + 'A' - 1, Row, Col + 'A'- 1, Row );//need to confirm;
 else //if (Col > 26 && Col <= 256)//max columns of an excel sheet is 256
 {
  char chCol = Col % 26 + 'A' - 1 < 'A' ? 'Z':Col % 26 + 'A' - 1;
  strRange.Format("%c%c%d:%c%c%d", (Col-1) / 26 + 'A' - 1, chCol, Row, (Col-1)/ 26 + 'A' - 1, chCol, Row);
 }
 
 pThisRange = pThisWorksheet->GetRange((_variant_t)strRange);
 
 //strRet = pThisRange->GetText();
 
 return strRet;
}

void CEclOp::SetCellText(short nSheet,int Row, int Col, _variant_t Content)
{
 
 CString strRange;
 int nColStart = 1;
 
 pThisWorksheet = pThisSheets->GetItem(nSheet);
 
 if (Col <= 26)
  strRange.Format("%c%d:%c%d", Col + 'A' - 1, Row, Col + 'A'- 1, Row );//need to confirm;
 else //if (Col > 26 && Col <= 256)//max columns of an excel sheet is 256
 {
  char chCol = Col % 26 + 'A' - 1 < 'A' ? 'Z':Col % 26 + 'A' - 1;
  strRange.Format("%c%c%d:%c%c%d", (Col-1) / 26 + 'A' - 1, chCol, Row, (Col-1)/ 26 + 'A' - 1, chCol, Row);
 }
 
 pThisRange = pThisWorksheet->GetRange((_variant_t)strRange);
 pThisRange->PutItem((long)1, (long)nColStart, Content);
 
}

void CEclOp::InsertPicsFromFile(LPCSTR path, short nSheet, double left, double top)
{
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 
 PicturesPtr pics = pThisWorksheet->Pictures();
 
 pics->Insert(path, VARIANT_FALSE);
 pics->PutLeft(left);
 pics->PutTop(top);
 
}

void CEclOp::InsertSheetsAfter(short nSheet, int nNum)
{
 _WorksheetPtr pSheet = pThisSheets->GetItem(nSheet);
 VARIANT var;
 
 var.vt = VT_DISPATCH;
 var.pdispVal = pSheet;
 
 pThisSheets->Add(vtMissing,var, (long)nNum);
 
}

void CEclOp::SaveQuit(CString strPath)
{
 
 vSaveAsAccessMode = xlNoChange ;
 vFileFormat = xlWorkbookNormal ;
 vSaveConflictResolution = xlLocalSessionChanges ;
 
 pThisWorkbook->SaveAs(_variant_t(strPath), (long)vFileFormat,_variant_t(""),_variant_t(""), _variant_t(false),
        _variant_t(false), vSaveAsAccessMode, (long)vSaveConflictResolution, _variant_t(false)) ;
 pThisWorkbook->Close();
 pApplication->Quit();
}

void CEclOp::SetCellColor(short nSheet, CString strRange, COLORREF rgb)
{
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 pThisWorksheet->GetRange((_variant_t)strRange)->GetInterior()->PutColor((long)rgb);
}

void CEclOp::SetCellColor(short nSheet, int row, int col, COLORREF rgb)
{
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 CString strRange;
 strRange.Format("%c%d:%c%d", 'A' + col - 1, row, 'A' + col - 1, row);
 pThisWorksheet->GetRange((_variant_t)strRange)->GetInterior()->PutColor((long)rgb);
 
}

void CEclOp::DeleteCells(short nSheet, CString strRange)
{
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 
 pThisRange = pThisWorksheet->GetRange((_variant_t)strRange);
 
 pThisRange->Delete();
}

void CEclOp::DeleteCells(short nSheet, unsigned int nFRow, unsigned int nFCol, unsigned int nTRow, unsigned int nTCol)
{
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 
 CString strRange;
 
 if (nTCol <= 26)
  strRange.Format("%c%d:%c%d", nFCol + 'A' - 1, nFRow, nTCol + 'A'- 1, nTRow);//need to confirm;
 else //if (Col > 26 && Col <= 256)//max columns of an excel sheet is 256
 {
  char chTCol = nTCol % 26 + 'A' - 1 < 'A' ? 'Z':nTCol % 26 + 'A' - 1;
  strRange.Format("%c%d:%c%c%d", nFCol + 'A' - 1, nFRow, (nTCol-1)/ 26 + 'A' - 1, chTCol, nTRow);
 }
 
 
 // strRange.Format("%c%d:%c%d", 'A' + nFCol - 1, nFRow, 'A' + nTCol - 1, nTRow);
 pThisRange = pThisWorksheet->GetRange((_variant_t)strRange);
 // pThisRange = pThisWorksheet->GetRange("H13:DB1004");
 pThisRange->Delete();
 
 
}

BOOL CEclOp::DeleteASheetByNO(short nSheet)
{
 _WorksheetPtr pSheet = pThisSheets->GetItem(nSheet);
 pApplication->DisplayAlerts[0] = FALSE;
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 pThisWorksheet->Delete();
 return TRUE;
}

BOOL CEclOp::DeleteASheetByName(CString strSheet)
{
 
 pApplication->DisplayAlerts[0] = FALSE;
 //pThisWorksheet->AttachDispatch(pThisSheets->GetItem(COleVariant("Plot")));
 pThisWorksheet = pThisSheets->GetItem("Plot");
 pThisWorksheet->Delete();
 return TRUE;
 
}

BOOL CEclOp::InsertAChart(short nSheet)
{
 _ChartPtr pChart;
 _WorksheetPtr pSheet = pThisSheets->GetItem(nSheet);
 
 //_ChartPtr pChart = pThisWorkbook->Charts>Add();
 pChart = pThisWorkbook->Charts->Add();
 pChart->PutChartType(xlXYScatterLinesNoMarkers);
 
 SeriesCollectionPtr pSeriesCollection;
 SeriesPtr pSeries;
 RangePtr pRange;
 pThisWorksheet = pThisSheets->GetItem((_variant_t)nSheet);
 pSeriesCollection= pChart->SeriesCollection();
 pSeries = pSeriesCollection->NewSeries();
 pRange = pThisWorksheet->Range["F2"]["F50"];
 pSeries->PutXValues((Range*)pRange);
 
 pChart->Location(xlLocationAsObject, pSheet->GetName());
 
 return TRUE;
 
}


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理