excelhandler.cpp

基础数据管理工具 主要针对政务系统的基础数据管理、分析、应用等功能！ 项目代码

// ExcelHandler.cpp: implementation of the CExcelHandler class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"

#include "ExcelHandler.h"

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CExcelHandler::CExcelHandler()
{

}

CExcelHandler::~CExcelHandler()
{

}

bool CExcelHandler::Instantiation()
{
	try
	{
		if(m_pApp != NULL)
			m_pApp.Release();

		m_pApp.CreateInstance("Excel.Application");
		m_pApp->PutVisible(0,FALSE);
		m_pApp->PutDisplayAlerts(0,FALSE); 
		m_pBooks = m_pApp->GetWorkbooks();
		m_pBook = m_pBooks->Add();
		m_pSheets = m_pBook->GetWorksheets();
		m_pSheet = m_pBook->GetActiveSheet();
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return false;
	}
	return true;
}
/*
bool CExcelHandler::Instantiation(LPCTSTR lsFilePath, LPCTSTR sSheetName,bool isvisual)
{
	try
	{
		if(m_pApp != NULL)
			m_pApp.Release();
		CString sheetname(sSheetName);

		m_pApp.CreateInstance("Excel.Application");
		if(isvisual)
			m_pApp->PutVisible(0,VARIANT_TRUE);
		else
			m_pApp->PutVisible(0,VARIANT_FALSE);

		m_pApp->PutDisplayAlerts(0,VARIANT_FALSE); 
		m_pBooks = m_pApp->GetWorkbooks();
		m_pBook = m_pBooks->Open ((LPCSTR)lsFilePath);
		m_pSheets = m_pBook->GetWorksheets();
		if(sheetname.IsEmpty())
			m_pSheet = m_pBook->GetActiveSheet();	
		else
		{
			SheetsPtr pSheets;
			GetSheet(sSheetName,m_pSheets,&m_pSheet);
		}
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return false;
	}
	return true;

}
*/
bool CExcelHandler::Instantiation(LPCTSTR lsFilePath, bool isvisual)
{
	try
	{
		if(m_pApp != NULL)
			m_pApp.Release();

		m_pApp.CreateInstance("Excel.Application");
		if(isvisual)
			m_pApp->PutVisible(0,VARIANT_TRUE);
		else
			m_pApp->PutVisible(0,VARIANT_FALSE);

		m_pApp->PutDisplayAlerts(0,VARIANT_FALSE); 
		m_pBooks = m_pApp->GetWorkbooks();
		m_pBook = m_pBooks->Open ((LPCSTR)lsFilePath);
		m_pSheets = m_pBook->GetWorksheets();
		m_pSheet = m_pBook->GetActiveSheet();	
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return false;
	}
	return true;
}

void CExcelHandler::Clean()
{
	if(m_pApp != NULL)
		m_pApp->Quit();
}

void CExcelHandler::GetSheet(LPCTSTR lsSheetName, SheetsPtr pxlSheets, _Worksheet **ppxlSheet)
{
	BOOL bSheet=FALSE;
	_WorksheetPtr xlSheet;
	SheetsPtr xlSheets= pxlSheets;

	for(int l=1;l<=xlSheets->GetCount();l++)
	{
		xlSheet= xlSheets->GetItem (COleVariant((short)l));
		CString	sName= (const TCHAR*)xlSheet->GetName();
		//TRACE( "name = %s\n", (const char *)sName );
		if (sName.CompareNoCase(lsSheetName) == 0)
		{
			bSheet=TRUE;
			xlSheet->Activate ();
			xlSheet->PutVisible (0,xlSheetVisible);
			break;
		}
	}
	if (!bSheet)
	{
		xlSheet=xlSheets->Add();
		xlSheet->Activate();
		xlSheet->PutVisible (0,xlSheetVisible);
		xlSheet->PutName(lsSheetName);
	}
	*ppxlSheet = xlSheet.Detach();
}

void CExcelHandler::SetCellItem(long lRow, long lCol, LPCTSTR lsValue, _WorksheetPtr pSheet)
{
	if(pSheet == NULL)
		pSheet = m_pSheet;
	Excel::RangePtr pRange = pSheet->GetCells();
	pRange->PutItem(
			COleVariant(lRow),
			COleVariant(lCol),
			COleVariant(lsValue) ); 
}

void CExcelHandler::SetCellBorder(Excel::RangePtr pRange, XlBorderWeight xlWeight, Constants xlColorIndex, XlLineStyle xlLineStyle)
{
	if(pRange == NULL)
		pRange = m_pSheet->GetUsedRange(0); 
	Excel::RangePtr xlRange= pRange;
    Excel::BordersPtr pbs=xlRange->GetBorders(); 
	
	pbs->PutLineStyle((COleVariant)(long) xlLineStyle);
    pbs->PutWeight((COleVariant)(long)xlWeight); 
	pbs->PutColorIndex ((COleVariant)(long)xlColorIndex);
}

void CExcelHandler::MergeCells(Excel::RangePtr pRange, LPCTSTR lsShowText)
{
	pRange->PutHorizontalAlignment((COleVariant)(long)xlCenter); 
	pRange->PutVerticalAlignment((COleVariant)(long)xlCenter); 
	pRange->PutWrapText((COleVariant)(long) FALSE);
	pRange->PutOrientation((COleVariant)(long)0);
    pRange->PutAddIndent((COleVariant)(long)FALSE);
    pRange->PutShrinkToFit((COleVariant)(long) TRUE);
    pRange->PutMergeCells( (COleVariant)(long)TRUE);
	pRange->PutValue ((COleVariant)lsShowText);	
}

void CExcelHandler::SaveBook(LPCTSTR lsFilePath, _WorkbookPtr pBook)
{
	if(pBook == NULL)
		pBook = m_pBook;
	pBook->SaveAs((COleVariant)(_bstr_t)lsFilePath,  
			 (COleVariant)(long)pBook->GetFileFormat(),
			 (COleVariant)(_bstr_t)"",
			 (COleVariant)(_bstr_t)"",
			 (COleVariant)(long)0,
			 (COleVariant)(long)0,
			 xlNoChange);
}

void CExcelHandler::SetCellBackColor(Excel::RangePtr pRange, long lRed, long lYellow, long lBlue)
{
	pRange->Interior->Color = (long)RGB(lRed,lYellow,lBlue);
}

void CExcelHandler::GetRange(long lStartCol, long lStartRow, long lEndCol, long lEndRow, Excel::Range **ppRange, _WorksheetPtr pSheet)
{
	if(pSheet == NULL)
		pSheet = m_pSheet;
	CString sStartCol = GetColumnCode(lStartCol);
	CString sEndCol = GetColumnCode(lEndCol);
	CString sStart,sEnd;
	sStart.Format("%s%d",sStartCol,lStartRow);
	sEnd.Format("%s%d",sEndCol,lEndRow);
	*ppRange=pSheet->GetRange(COleVariant(sStart),COleVariant(sEnd)).Detach();
}

CString CExcelHandler::GetColumnCode(int iCol)
{
	if(iCol <= 0)
		return CString();
	char buf[32];
	buf[31] = NULL;
	int i,pos = 30;
	do
	{
		i = iCol % 26;	
		if(i == 0)
			i = 26;
		buf[pos--] = 'A' + i - 1;
		iCol = (iCol - 1) / 26;
	}
	while(iCol != 0);
	return CString(buf + pos + 1);
}

void CExcelHandler::SetCellFont(LPCTSTR lsName, LPCTSTR lsStyle, long lsSize, Excel::RangePtr pRange)
{
	if(pRange == NULL)
		pRange = m_pSheet->GetUsedRange(0); 
	pRange->GetFont()->PutName(lsName);
	pRange->GetFont()->PutFontStyle(lsStyle);
	pRange->GetFont()->PutSize(lsSize);
}

void CExcelHandler::SetCulSheet(_WorksheetPtr pSheet)
{
	m_pSheet = pSheet;
}

void CExcelHandler::SetCulSheet(LPCTSTR lsSheetName)
{
	_WorksheetPtr pSheet;
	GetSheet(lsSheetName,m_pSheets,&pSheet);
	m_pSheet = pSheet;
}

void CExcelHandler::ShowApp()
{
	m_pApp->PutVisible(0,TRUE);
}

bool CExcelHandler::GetCurSheet(_Worksheet **ppxlSheet)
{
	try
	{
		*ppxlSheet = m_pSheet;
		(*ppxlSheet)->AddRef();
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
		return false;
	}
	return true;
}

_WorksheetPtr CExcelHandler::GetSheet()
{
	//if(m_pSheet!=NULL)
		return m_pSheet;
}

_variant_t CExcelHandler::GetCellItem(long IRow,long ICol)
{
	_variant_t vt;
	_WorksheetPtr pSheet;
	pSheet = m_pSheet;
	Excel::RangePtr pRange = pSheet->GetCells();
	vt=pRange->GetItem(IRow,ICol);
	return vt;
}

void CExcelHandler::SetAllSheetVisible(bool bVisible)
{
	try
	{
		for(int l=1;l<=m_pSheets->GetCount();l++)
		{
			_WorksheetPtr xlSheet= m_pSheets->GetItem (COleVariant((short)l));
			CString	sName= (const TCHAR*)xlSheet->GetName();
			if(sName == "Sheet1")
				return;
			if(bVisible)
				xlSheet->PutVisible (0,xlSheetVisible);
			else
				xlSheet->PutVisible (0,xlSheetHidden);
		}
	}
	catch(_com_error &e)
	{
		_bstr_t err = e.Description();
	}
}

int CExcelHandler::GetSheetCount()
{
	return(m_pSheets->GetCount());
}

CString CExcelHandler::GetSheetName(int iSheetIndex)
{
	_WorksheetPtr xlSheet= m_pSheets->GetItem (COleVariant((short)iSheetIndex));
	if(xlSheet == NULL)
		return "";
	return (const TCHAR*)xlSheet->GetName();
}