// aplExcel.cpp : implementation file
//

#include "stdafx.h"
#include "apl_Excel.h"
#include <afxtempl.h>
#include <aplAggr.h>

#ifdef _DEBUG
#define new DEBUG_NEW
#endif

// Импорт библиотек для Ofice XP
#import "..\..\_const\OFFICE\MSO9.DLL" rename("RGB","_RGB") 
#import "..\..\_const\OFFICE\VBE6EXT.OLB"
#import "..\..\_const\\OFFICE\EXCEL9.OLB" \
	rename("DialogBox","_DialogBox") \
	rename("RGB","_RGB") \
	rename("CopyFile","_CopyFile") \
	rename("ReplaceText","_ReplaceText") \
	exclude("IFont","IPicture")

/*
// Импорт библиотек для Ofice 2010
// Если убрать "no_implementation", то программу убивает Windows Defender
#import "..\..\_const\OFFICE\MSO14\MSO.DLL"  no_implementation  rename("RGB","_RGB") 
#import "..\..\_const\OFFICE\MSO14\VBE6EXT.OLB" no_implementation
#import "..\..\_const\\OFFICE\MSO14\EXCEL.EXE" \
	rename("DialogBox","_DialogBox") \
	rename("RGB","_RGB") \
	rename("CopyFile","_CopyFile") \
	rename("ReplaceText","_ReplaceText") \
	exclude("IFont","IPicture")
*/

using namespace Excel;

// CaplExcel
#pragma warning( disable : 4996 )

//#define APL_EXCEL		(*((_ApplicationPtr *)m_pExcel))
#define APL_EXCEL_SHEET (*((_WorksheetPtr*)m_pWorkSheet))
#define APL_EXCEL_CHART_SHEET (*((_WorksheetPtr*)chart_sheet->m_pWorkSheet))
#define APL_EXCEL_BOOK	(*((_WorkbookPtr*)m_pWorkBook))


static _ApplicationPtr *g_pExcel=0;
static CArray <CaplExcelFile*, CaplExcelFile*> g_aplExcelFiles;

CaplExcelFile::CaplExcelFile()
{
	m_pWorkBook=0;
	m_bValid = true;
	g_aplExcelFiles.Add(this);
}
//*******************************************************************************
CaplExcelFile::~CaplExcelFile()
{
	Close();
	int i;
	for(i=0;i<g_aplExcelFiles.GetSize();i++)
	{
		if(this==g_aplExcelFiles[i])
		{
			g_aplExcelFiles.RemoveAt(i);
			break;
		}
	}
	if(g_aplExcelFiles.GetSize()==0)
	{
		if(0!=g_pExcel)
		{
			_ApplicationPtr* pExcel = g_pExcel;
			g_pExcel = 0;
			try
			{
				if (dynamic_cast<_ApplicationPtr*>(pExcel))
				{
					(*pExcel)->Quit();
					delete pExcel;
				}
			}
			catch (...)
			{
				return;
			}
		}
	}
}
//*******************************************************************************
bool CaplExcelFile::InitExcel()
{
	if(0!=g_pExcel) return true;
	try
	{
		g_pExcel=new _ApplicationPtr ("Excel.Application");
	}
	catch(...)
	{
		return false;
	}

	if(0==g_pExcel) return false;

	(*g_pExcel)->Visible[0] = false;
	(*g_pExcel)->PutDisplayAlerts(0, FALSE);
	return true;
}

//*******************************************************************************
bool CaplExcelFile::Create()
{
	if (!m_bValid)return false;
	if(!InitExcel())return false;
	if(0==g_pExcel)return false;

	Close();
	_WorkbookPtr *pWorkBook=new _WorkbookPtr;

	*pWorkBook = (*g_pExcel)->Workbooks->Add();

	if(0==*pWorkBook) {delete pWorkBook; return false;}
		
	m_pWorkBook=pWorkBook;
	return true;
}
//*******************************************************************************
bool CaplExcelFile::Open(LPCTSTR fname, bool bReadOnly, CString *err) // Открывает файл
{
	if (!m_bValid)
	{
		return false;
	}
	if(!InitExcel())
	{
		return false;
	}
	if(0==g_pExcel)
	{
		return false;
	}
	if(0==fname) 
	{
		return false;
	}
	if('\0'==fname[0])
	{
		return false;
	}

	Close();

	_WorkbookPtr *pWorkBook=new _WorkbookPtr;
	try
	{
		BOOL bUpdateLink=TRUE;
		BOOL bRO=false;
		if(bReadOnly)bRO=TRUE;
		*pWorkBook = (*g_pExcel)->Workbooks->Open(fname,bUpdateLink,bRO);
		m_pWorkBook=pWorkBook;
	}
	catch(...)
	{
		delete pWorkBook;
		m_pWorkBook = 0;
		m_bValid = false;
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelFile::Close()
{
	if (!m_bValid) return false;
	if(0==g_pExcel) return false;
	if(0!=m_pWorkBook)
	{
		APL_EXCEL_BOOK->Close();
		delete (_WorkbookPtr*)m_pWorkBook;
		m_pWorkBook = 0;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelFile::SetVisible(bool bVisible)
{
	if(0==g_pExcel) 
	{
		if(!InitExcel()) return false;
	}
	(*g_pExcel)->Visible[0] = bVisible;
	return true;
}

//*******************************************************************************
bool CaplExcelFile::Save()
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkBook) return false;
	if (m_bValid)
	{
		APL_EXCEL_BOOK->Save();
	}
	return true;
}

//*******************************************************************************
bool CaplExcelFile::SaveAs(LPCTSTR fname)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkBook) return false;
	if(0==fname) return false;
	if(_T('\0')==fname[0])return false;
	if (!m_bValid) return false;

	HRESULT hr=APL_EXCEL_BOOK->SaveAs(fname,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,xlExclusive);
	return (S_OK == hr);
}

//*******************************************************************************
//*******************************************************************************
//*******************************************************************************
//*******************************************************************************
//*******************************************************************************
CaplExcelSheet::CaplExcelSheet()
{
	m_pWorkSheet=0;
	SetDashInsteadZero(false);
	m_iBufferRowStart= 0;
	m_iBufferColStart= 0;
	m_iBufferRowEnd= 0;
	m_iBufferColEnd= 0;
	m_pBufferArr = NULL;
	m_pBufferFormatArr = NULL;
	m_pBufferMergeHorizArr = NULL;
	m_pBufferMergeVertArr = NULL;
}
//*******************************************************************************
CaplExcelSheet::~CaplExcelSheet()
{
	Close();
};
//*******************************************************************************
void CaplExcelSheet::Close()
{
	if(0==m_pWorkSheet) return;	
	delete (_WorksheetPtr*)m_pWorkSheet;
	m_pWorkSheet=0;
}
//*******************************************************************************
bool CaplExcelSheet::DeleteSheet(CaplExcelFile *file, int iNum)
{
	if(0==file)return false;
	if(0==g_pExcel) return false;
	
	try
	{
		_WorksheetPtr ptr=(*g_pExcel)->Worksheets->Item[iNum];
		ptr->Delete();		
	}
	catch(...)
	{		
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::HideSheet(CaplExcelFile *file, int iNum)
{
	if(0==file)return false;
	if(0==g_pExcel) return false;
	
	try
	{
		_WorksheetPtr ptr=(*g_pExcel)->Worksheets->Item[iNum];

		// за первый параметр я не уверен, что тут хотели получить
		ptr->PutVisible(0, xlSheetHidden);				
	}
	catch(...)
	{		
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::GetSheetsCount()
{
	if(0==g_pExcel) return false;
	return (*g_pExcel)->Worksheets->Count;
}
//*******************************************************************************
bool CaplExcelSheet::AddSheet(CaplExcelFile *file, LPCTSTR sheet_name, bool bActivate, int iNum)
{
	//if(0==file)return false;
	if(0==g_pExcel) return false;
	if(sheet_name==0) return false;
	if(sheet_name[0]=='\0') return false;

	Close();

	_WorksheetPtr *pWorkSheetNew=new _WorksheetPtr;
	try
	{
		if(0==iNum) *pWorkSheetNew= (*g_pExcel)->Worksheets->Add();
		else
		{
			int num=(*g_pExcel)->Worksheets->Count;
			if(iNum<0 || iNum>num) iNum=num; // счет идет с 1
			_WorksheetPtr ptr=(*g_pExcel)->Worksheets->Item[iNum];
			*pWorkSheetNew= (*g_pExcel)->Worksheets->Add(vtMissing, ptr.GetInterfacePtr());
		}
		(*pWorkSheetNew)->Name=sheet_name;
		if(bActivate) (*pWorkSheetNew)->Activate();
		m_pWorkSheet=pWorkSheetNew;
	}
	catch(...)
	{
		delete pWorkSheetNew;
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::OpenSheet(CaplExcelFile *file, int sheet_num, bool bActivate)
{
	if(0==file)return false;
	if(0==g_pExcel) return false;
	if(sheet_num<1) return false;
	Close();
	_WorksheetPtr *pWorkSheetNew=new _WorksheetPtr;
	try
	{
		*pWorkSheetNew=(*g_pExcel)->Worksheets->Item[sheet_num]; // Вообще надо брать у файла
		if(bActivate) (*pWorkSheetNew)->Activate();
		m_pWorkSheet=pWorkSheetNew;
	}
	catch(...)
	{
		delete pWorkSheetNew;
		return false;
	}

	return true;
}
//*******************************************************************************
bool CaplExcelSheet::OpenSheet(CaplExcelFile *file, LPCTSTR sheet_name, bool bActivate)
{
	if(0==file)return false;
	if(0==g_pExcel) return false;

	if(sheet_name==0) return false;
	if(sheet_name[0]=='\0') return false;

	Close();
	_WorksheetPtr *pWorkSheetNew=new _WorksheetPtr;
	try
	{
		*pWorkSheetNew= (*g_pExcel)->Worksheets->GetItem(sheet_name); // Вообще надо брать у файла
		//_WorkbookPtr *pWB=(_WorkbookPtr*)(file->m_pWorkBook);
		//in k=pWB->Sheets->Count;
		//_WorksheetPtr *pWorkSheetNew=new _WorksheetPtr;
		//pWorkSheetNew=(*()->Sheets.getItem(sheet_name)
		//(*((_WorkbookPtr*)(file->m_pWorkBook)))
		if(bActivate) (*pWorkSheetNew)->Activate();
		m_pWorkSheet=pWorkSheetNew;
	}
	catch(...)
	{
		delete pWorkSheetNew;
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::Activate()
{
	if(0==m_pWorkSheet) return false;
	try
	{
		APL_EXCEL_SHEET->Activate();
	}
	catch(...)
	{
		return false;
	}
	 return true;
}
//*******************************************************************************
bool CaplExcelSheet::SetName(LPCSTR newName)
{
	if(0==m_pWorkSheet) return false;
	try
	{
		APL_EXCEL_SHEET->Name=newName;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

//*******************************************************************************
bool CaplExcelSheet::GetName(CString &sName) // Получает текущее имя листа
{
	sName=_T("");
	if(0==m_pWorkSheet) return false;
	try
	{
		sName=(LPCWSTR)(APL_EXCEL_SHEET->Name);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

//*******************************************************************************
bool CaplExcelSheet::CopyFrom(CaplExcelSheet *sheet) 
{
	if(0==sheet)return false;
	if(0==g_pExcel) return false;
	if(sheet==0) return false;
	if(0==sheet->m_pWorkSheet)return false;
	Close();
	_WorksheetPtr *pWorkSheetNew=new _WorksheetPtr;
	try
	{
		int num=(*g_pExcel)->Worksheets->Count;
		_WorksheetPtr ptrLast=(*g_pExcel)->Worksheets->Item[num];
		_WorksheetPtr *ptrSrc= (_WorksheetPtr*)(sheet->m_pWorkSheet);
		(*ptrSrc)->Copy(vtMissing,ptrLast.GetInterfacePtr()); // в конец
		(*pWorkSheetNew)=(*g_pExcel)->Worksheets->Item[num+1];
		m_pWorkSheet=pWorkSheetNew;
	}
	catch(...)
	{
		delete pWorkSheetNew;
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::GetRowsCount()
{
	if(0==m_pWorkSheet)  return -1;
	int iCols=-1;
	try
	{
		RangePtr pRange = APL_EXCEL_SHEET->UsedRange;
		RangePtr pCell = pRange->SpecialCells(xlCellTypeLastCell);
		iCols = pCell->Row;		
	}
	catch(...)
	{
	}
	return iCols;

}
//*******************************************************************************
int CaplExcelSheet::GetColunmsCount()
{
	if(0==m_pWorkSheet)  return -1;
	int iCols=-1;
	try
	{
		RangePtr pRange = APL_EXCEL_SHEET->UsedRange;
		RangePtr pCell = pRange->SpecialCells(xlCellTypeLastCell);
		iCols = pCell->Column;		
	}
	catch(...)
	{
	}
	return iCols;
}

//*******************************************************************************
bool CaplExcelSheet::SetCellWordBreak(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell;
		pCell = pRange->Item[row][col]; 
		
		pCell->PutWrapText(true);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

//*******************************************************************************
bool CaplExcelSheet::SetCell(LPCTSTR cell,  LPCTSTR val)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	try
	{
		if(m_dashInsteadZero && IfZeroValue(val))
			val = _T("-");

		APL_EXCEL_SHEET->Range[cell]->Value2=val;
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::SetCell(int row, LPCTSTR col, LPCTSTR val)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(row<1)return false;
	if(col==0) return false;
	if(col[0]=='\0') return false;
	
	int i=0;
	TCHAR cell_name[12]; // ну типа очень большой запас

	while (col[i]!='\0')
	{
		TCHAR c=col[i];
		if(c>='a' && c<='z') c+='A' - 'a';
		if(c<'A' && c>'Z')return false;
		cell_name[i]=c;
		i++;
	}
	__itoa(row,&(cell_name[i]),10);
	return SetCell(cell_name,val);
}
//*******************************************************************************
bool CaplExcelSheet::SetCell(int row, int col, LPCTSTR val/*=NULL*/, LPCTSTR sFormat/*=NULL*/)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(col<1 || row<1) return false;
	try
	{
		if(m_dashInsteadZero && IfZeroValue(val))
			val = _T("-");

		if(GetBufferArray()== NULL)
		{
			Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
			Excel::RangePtr pCell;

			pCell = pRange->Item[row][col];
			pCell->Value2 = val;

			if (sFormat != NULL && (CString(sFormat)).IsEmpty() == false)
				pCell->NumberFormat = sFormat;
		}
		else 
		{
			bstr_t bstVal = val;
			if (sFormat != NULL && (CString(sFormat)).IsEmpty() == false)
			{
				bstr_t bstFormat = sFormat;
				FillSafeArray(bstVal, row, col, GetBufferArray(),  bstFormat, GetBufferFormatArray());
			}
			else
			{
				FillSafeArray(bstVal, row, col, GetBufferArray());
			}
				
			
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::GetCellText(int row, int col, CString &val)
{
	val.Empty();
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(col<1 || row<1) return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell;

		pCell = pRange->Item[row][col]; 
		val = pCell->Value2;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}

void CaplExcelSheet::FillSafeArray(_variant_t val, int iRow, int iCol,
	COleSafeArray* saValue, OLECHAR FAR* valFormat/*=NULL*/, COleSafeArray* saFormat/*=NULL*/)
{

	VARIANT v;
	long index[2];
	if(iRow>0)//SAFEARRAY c нуля, а координаты в Excel - c 1
		iRow--;
	if (iCol > 0)
		iCol--;

	index[0] = iRow;
	index[1] = iCol;

	VariantInit(&v);
	v.vt = VT_VARIANT;
	v.pvarVal = &val;
	saValue->PutElement(index, v.pvarVal);
	VariantClear(&v);
	//формат
	if (saFormat != NULL)
	{
		VARIANT vFormat;
		VariantInit(&vFormat);
		vFormat.vt = VT_BSTR;
		vFormat.bstrVal = SysAllocString(valFormat);
		saFormat->PutElement(index, vFormat.bstrVal);
		SysFreeString(vFormat.bstrVal);
		VariantClear(&vFormat);
	}
}

// void CaplExcelSheet::FillSafeArray(_variant_t val, int iRow1, int iCol1,
// 	COleSafeArray* saValue, OLECHAR FAR* valFormat/*=NULL*/, COleSafeArray* saFormat/*=NULL*/)
// {
// 
// 	VARIANT v;
// 	long index[2];
// 
// 	index[0] = iRow;
// 	index[1] = iCol;
// 
// 	VariantInit(&v);
// 	v.vt = VT_VARIANT;
// 	v.pvarVal = &val;
// 	saValue->PutElement(index, v.pvarVal);
// 	VariantClear(&v);
// 	//формат
// 	if (saFormat != NULL)
// 	{
// 		VARIANT vFormat;
// 		VariantInit(&vFormat);
// 		vFormat.vt = VT_BSTR;
// 		vFormat.bstrVal = SysAllocString(valFormat);
// 		saFormat->PutElement(index, vFormat.bstrVal);
// 		SysFreeString(vFormat.bstrVal);
// 		VariantClear(&vFormat);
// 	}
// }
//*******************************************************************************
bool CaplExcelSheet::SetCell(int row, int col, _variant_t val, LPCTSTR sFormat/*=NULL*/)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(col<1 || row<1) return false;
	try
	{
		if (GetBufferArray() == NULL)
		{
			Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
			if (sFormat != NULL && (CString(sFormat)).IsEmpty() == false)
			{
				pRange->NumberFormat = sFormat;
			}
			Excel::RangePtr pCell;
			pCell = pRange->Item[row][col];
			pCell->Value2 = val;
		}
		else
		{
			if (sFormat != NULL && (CString(sFormat)).IsEmpty() == false)
			{
				bstr_t bstFormat = sFormat;
				FillSafeArray(val, row, col, GetBufferArray(), bstFormat, GetBufferFormatArray());
			}
			else
			{
				FillSafeArray(val, row, col, GetBufferArray());
			}
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}
// pRange->PutNumberFormat(,)
// pRange->PutNumberFormatLocal()
// pRange->PutValue()
// pRange->PutValue2()
// pRange
// _variant_t var;
// var

//*******************************************************************************
bool CaplExcelSheet::GetCell(LPCTSTR cell, CString &val)
{
	val="";
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	try
	{
		val=APL_EXCEL_SHEET->Range[cell]->Value;
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::GetCell(int row, LPCTSTR col, CString &val)
{
	val="";
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(row<1)return false;
	if(col==0) return false;
	if(col[0]=='\0') return false;

	int i=0;
	TCHAR cell_name[12]; // ну типа очень большой запас

	while (col[i]!='\0')
	{
		TCHAR c=col[i];
		if(c>='a' && c<='z') c+='A' - 'a';
		if(c<'A' && c>'Z')return false;
		cell_name[i]=c;
		i++;
	}
	__itoa(row,&(cell_name[i]),10);
	return GetCell(cell_name,val);
}


bool CaplExcelSheet::GetCell(int row, int col, CString &val, _variant_t* varTval/*=NULL*/, bool bAsText /*= false*/)
{
	val="";
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(col<1 || row<1) return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];
		val = bAsText?pCell->Text:pCell->Value; 
		if(varTval!=NULL)
		{
			*varTval= pRange->Item[row][col]; 
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::GetColumnBN(LPCSTR name,int max_col)
{
	if(0==g_pExcel) return -1;
	if(0==m_pWorkSheet)  return -1;

	CString buf,sLowName;
	sLowName=name; 
	sLowName.MakeLower();
	for(int i=1;i<max_col;i++)
	{
		if(!GetCell(1,i,buf)) return -1;
		buf.MakeLower();
		if(buf==sLowName) return i;
	}
	return -1;
}
//*******************************************************************************
int CaplExcelSheet::HowMergedColumns(int row, int col)
{
	if(0==g_pExcel) return 0;
	if(0==m_pWorkSheet)  return 0;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];
		return pCell->MergeArea->Columns->Count;		
	}
	catch(...)
	{
		return 0;
	}
	return 0;	
}
//*******************************************************************************
int CaplExcelSheet::HowMergedRows(int row, int col)
{
	if(0==g_pExcel) return 0;
	if(0==m_pWorkSheet)  return 0;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];
		return pCell->MergeArea->Rows->Count;		
	}
	catch(...)
	{
		return 0;
	}
	return 0;	
}
//*******************************************************************************
bool CaplExcelSheet::IsCellMerged(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];
		int count = pCell->MergeArea->Columns->Count;
		return pCell->MergeCells;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::Merge(LPCTSTR range, bool bConvertFromR1C1)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(0==range) return false;
	if('\0'==range[0])return false;
	try
	{
		COleVariant v_range=range;
		if(bConvertFromR1C1) v_range=(*g_pExcel)->ConvertFormula(v_range,xlR1C1,xlA1);
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Range[v_range];
		pRange->Merge();
	}
	catch(...)
	{
		return false;
	}
	return true;
}

void CaplExcelSheet::UseBufferArray(int rowStart/*=1*/, int colStart/*=1*/, int rowEnd/*=1000*/, int colEnd/*=100*/, bool bUse/*= true*/)
{
	if (0 == g_pExcel) return;
	if (0 == m_pWorkSheet)  return;

	if(bUse=false)
	{
		ClearBufferArrayPtrs();
		return;
	}
	COleSafeArray* saVal= new COleSafeArray();
	COleSafeArray* saFormat= new COleSafeArray();
	// 	COleSafeArray* saMergeHoriz= new COleSafeArray();
	// 	COleSafeArray* saMergeVert= new COleSafeArray();
	try
	{
		//значения
		DWORD numElements[] = { (DWORD)(rowEnd-rowStart+1), (DWORD)(colEnd-colStart+1) };
		saVal->Create(VT_VARIANT, 2, numElements);
		//формат ячеек
		saFormat->Create(VT_BSTR, 2, numElements);
		//объединение ячеек по горизонтали

	// 	saMergeHoriz->Create(VT_VARIANT, 2, numElements);
		//объединение ячеек по вертикали
	// 	saMergeVert->Create(VT_VARIANT, 2, numElements);
		
	}
	catch (...)
	{
		delete saVal;
		delete saFormat;
		//delete saMergeHoriz;
		//delete saMergeVert;
		return;	//если не удалось выделить память, работаем без буфера 
	}
	SetBufferArray(rowStart, colStart, rowEnd, colEnd, saVal, saFormat, NULL/*saMergeHoriz*/, NULL/*saMergeVert*/);

}

bool CaplExcelSheet::SetBufferArray(int row1, int col1, int row2, int col2, COleSafeArray* pValArr, COleSafeArray* pFormatArr/*=NULL*/, COleSafeArray* pMergeHorizArr/*=NULL*/, COleSafeArray* pMergeVertArr/*=NULL*/)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;

	try
	{
		m_iBufferRowStart= row1;
		m_iBufferColStart= col1;
		m_iBufferRowEnd= row2;
		m_iBufferColEnd= col2;
		m_pBufferArr= pValArr;
		m_pBufferFormatArr= pFormatArr;
		m_pBufferMergeHorizArr= pMergeHorizArr;
		m_pBufferMergeVertArr= pMergeVertArr;
	}
	catch (...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetCellsByArray(int row1, int col1, int row2, int col2 , COleVariant& arr)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;
		APL_EXCEL_SHEET->get_Range(pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->PutValue(COleVariant(arr));
	}
	catch (...)
	{
		return false;
	}
	return true;
}

void CaplExcelSheet::MergeCellsByBuffer()
{
	if (GetBufferMergeHorizArray()==NULL ||
		GetBufferMergeVertArray()==NULL)
	{
		return;
	}
	_ApplicationPtr appPtr = APL_EXCEL_SHEET->GetApplication();
	Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
	
	for (UINT iRow = m_iBufferRowStart; iRow < m_iBufferRowEnd; ++iRow)
	{
		for (UINT iCol = m_iBufferColStart; iCol < m_iBufferColEnd; ++iCol)
		{
			VARIANT vR;
			VARIANT vC;
			long index[2];
			index[0] = iRow;
			index[1] = iCol;

			long rowBound = 0;
			GetBufferMergeHorizArray()->GetUBound(1, &rowBound);
			long colBound = 0;
			GetBufferMergeHorizArray()->GetUBound(2, &colBound);
			if (iRow >= (UINT) rowBound)
			{
				continue;
			}
			if (iCol >= (UINT) colBound)
			{
				continue;
			}
			GetBufferMergeHorizArray()->GetElement(index, &vR);
			GetBufferMergeVertArray()->GetElement(index, &vC);

			if ((int)vR.pvarVal > 0 ||
				(int)vC.pvarVal > 0)
			{
				Excel::RangePtr pRange1;
				APL_EXCEL_SHEET->get_Range(pRange->Item[iRow][iCol], pRange->Item[iRow + (int)vR.pvarVal][iCol + (int)vC.pvarVal], &pRange1);
				pRange1->Merge();
			}
		}
	}
}

bool CaplExcelSheet::SetCellsByBufferArray()
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (NULL == GetBufferArray())  return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pRet;
		APL_EXCEL_SHEET->get_Range(pRange->Item[m_iBufferRowStart][m_iBufferColStart], pRange->Item[m_iBufferRowEnd][m_iBufferColEnd], &pRet);

		if(GetBufferMergeHorizArray() &&
		   GetBufferMergeVertArray())
		{
			MergeCellsByBuffer();
		}
		if (GetBufferFormatArray())
		{
			pRet->PutNumberFormat(COleVariant(GetBufferFormatArray()));
			pRet->PutFormula(pRet->GetFormula());
		}
		pRet->PutValue(COleVariant(GetBufferArray()));

		
	}
	catch (...)
	{
		return false;
	}
	return true;
}

void CaplExcelSheet::ClearBufferArrayPtrs()
{
	if(m_pBufferArr)
	{
		m_pBufferArr->Destroy();
		delete m_pBufferArr;
	}
	if (m_pBufferFormatArr)
	{
		m_pBufferFormatArr->Destroy();
		delete m_pBufferFormatArr;
	}
	if (m_pBufferMergeHorizArr)
	{
		m_pBufferMergeHorizArr->Destroy();
		delete m_pBufferMergeHorizArr;
	}
	if (m_pBufferMergeVertArr)
	{
		m_pBufferMergeVertArr->Destroy();
		delete m_pBufferMergeVertArr;
	}
	m_pBufferArr = NULL;
	m_pBufferFormatArr = NULL;
	m_pBufferMergeHorizArr = NULL;
	m_pBufferMergeVertArr = NULL;
}

//*******************************************************************************
bool CaplExcelSheet::MakeAlignCenter( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	
		
		pCell->HorizontalAlignment = xlHAlignCenter;
		pCell->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeVAlignCenter( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	
				
		pCell->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
bool CaplExcelSheet::MakeVAlignCenter( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range(pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeVAlignTop( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	

		pCell->VerticalAlignment = xlVAlignTop;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
bool CaplExcelSheet::MakeVAlignTop( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range(pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->VerticalAlignment = xlVAlignTop;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeVAlignBottom( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	

		pCell->VerticalAlignment = xlVAlignBottom;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
bool CaplExcelSheet::MakeVAlignBottom( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range(pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->VerticalAlignment = xlVAlignBottom;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeAlignCenter( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->HorizontalAlignment = xlHAlignCenter;
		pRet->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeAlignLeft( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	

		pCell->HorizontalAlignment = xlHAlignLeft;
		pCell->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::SetCellTextHAlign(int row1, int col1, int row2, int col2, int align)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->HorizontalAlignment = align;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::GetCellTextHAlign(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	

		return pCell->HorizontalAlignment;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::SetCellTextVAlign(int row1, int col1, int row2, int col2, int align)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->VerticalAlignment = align;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
int CaplExcelSheet::GetCellTextVAlign(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	

		return pCell->VerticalAlignment;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeAlignLeft( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->HorizontalAlignment = xlHAlignLeft;
		pRet->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;	
}
//*******************************************************************************
bool CaplExcelSheet::MakeAlignRight(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		Excel::RangePtr pCell = pRange->Item[row][col];	

		pCell->HorizontalAlignment = xlHAlignRight;
		pCell->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeAlignRight(int row1, int col1, int row2, int col2)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->HorizontalAlignment = xlHAlignRight;
		pRet->VerticalAlignment = xlVAlignCenter;		
	}
	catch(...)
	{
		return false;
	}
	return true;	
}
//*******************************************************************************
bool CaplExcelSheet::Merge( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{

		if (GetBufferMergeHorizArray() == NULL || 
			GetBufferMergeVertArray()== NULL)
		{
			Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

			Excel::RangePtr pRet;
			APL_EXCEL_SHEET->get_Range(pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

			pRet->HorizontalAlignment = xlHAlignCenter;
			pRet->VerticalAlignment = xlVAlignCenter;
			pRet->Merge();
		}
		else
		{
			FillSafeArray(row2-row1, row1, col1, GetBufferMergeHorizArray());
			FillSafeArray(col2-col1, row1, col1, GetBufferMergeVertArray());
		}


	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::InsertRow(int RowNum)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(RowNum<1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Rows->GetItem(RowNum);
		pRange->Insert(xlDown);
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::InsertCol(int ColNum)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(ColNum<1)  return false;
	ColNum=-1;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Columns->GetItem(ColNum);
		pRange->Insert(xlToRight);
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::DeleteRow(int RowNum)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (RowNum < 1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Rows->GetItem(RowNum);
		pRange->Delete(xlUp);
	}
	catch (...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::DeleteCol(int ColNum)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (ColNum < 1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Columns->GetItem(ColNum);
		pRange->Delete(xlToLeft);
	}
	catch (...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool  CaplExcelSheet::MakeCellBorder(int row1, int col1, int row2, int col2, bool mediumWeight /*= false*/)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);		
		pRet->Borders->LineStyle = xlContinuous;

		if(mediumWeight)
			pRet->Borders->Weight = xlMedium;
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool  CaplExcelSheet::MakeCellBorder(int row, int col, bool mediumWeight /*= false*/)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];		
		pCell->Borders->LineStyle = xlContinuous;

		if(mediumWeight)
			pCell->Borders->Weight = xlMedium;
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool  CaplExcelSheet::SetCellBorder(int row1, int col1, int row2, int col2, long value)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);		

		pRet->Borders->LineStyle = value;				
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
long  CaplExcelSheet::GetCellBorder(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];		

		return pCell->Borders->LineStyle;				
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool  CaplExcelSheet::IsCellBorder(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];		

		return (int)pCell->Borders->LineStyle == xlContinuous;				
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool  CaplExcelSheet::AddIndentToCell(int row, int col, long indent)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(0==indent) return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	
		
		pCell->InsertIndent(indent);
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool  CaplExcelSheet::SetCellFontSize(int row, int col, int size)
{
	return SetCellFontSize(row, col, row, col, size);
}

bool CaplExcelSheet::SetCellFontSize( int row1, int col1, int row2, int col2, int size )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);		

		Excel::FontPtr pFont = pRet->GetFont();
		pFont->PutSize(size);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

int CaplExcelSheet::GetCellFontSize(int row, int col)
{
	if(0==g_pExcel) return 0;
	if(0==m_pWorkSheet)  return 0;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		Excel::FontPtr pFont = pCell->GetFont();
		return pFont->GetSize();
	}
	catch(...)
	{
		return 0;
	}
	return 0;
}

//*******************************************************************************
bool CaplExcelSheet::SetCellBold( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		Excel::FontPtr pFont = pCell->GetFont();
		pFont->PutBold(true);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetCellBold( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		Excel::FontPtr pFont = pRet->GetFont();
		pFont->PutBold(true);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

//*******************************************************************************
bool CaplExcelSheet::IsCellBold( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		Excel::FontPtr pFont = pCell->GetFont();
		return pFont->GetBold();
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::IsCellUnderLine( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		Excel::FontPtr pFont = pCell->GetFont();
		return (int)pFont->Underline == xlUnderlineStyleSingle;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::SetCellUnderline(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		Excel::FontPtr pFont = pCell->GetFont();
		pFont->PutUnderline(true);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetCellUnderline( int row1, int col1, int row2, int col2 )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		Excel::FontPtr pFont = pRet->GetFont();
		pFont->PutUnderline(true);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

//*******************************************************************************
bool CaplExcelSheet::SetWrapText(int row, int col)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	
		pCell->PutWrapText(true);
		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::SetVertiacalText( int row, int col )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	
		pCell->PutOrientation(90);

	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::SetWrapText(int row1, int col1, int row2, int col2)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);				
		pRet->PutWrapText(true);
		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
unsigned long CaplExcelSheet::createRGB(int r, int g, int b)
{   
	// ЭТО НЕ ОШИБКА. ТОЧНЕЕ В EXCEL ЕСТЬ ОШИБКА, КОТОРАЯ ПУТАЕТ МЕСТАМИ "R" И "B"
	std::swap(r, b);

	return ((r & 0xff) << 16) + ((g & 0xff) << 8) + (b & 0xff);
}
//*******************************************************************************
bool CaplExcelSheet::MakeTextColorRGB(int row, int col, int r, int g, int b)
{
	return MakeTextColorRGB(row, col, row, col, r, g, b);
}
//*******************************************************************************
bool CaplExcelSheet::MakeTextColorRGB(int row, int col, COLORREF color)
{
	return MakeTextColorRGB(row, col, row, col, color);
}
//*******************************************************************************
bool CaplExcelSheet::MakeTextColorRGB(int row1, int col1, int row2, int col2, unsigned long colorInHex)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);								

		pRet->Font->Color = colorInHex;
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeTextColorRGB(int row1, int col1, int row2, int col2, int r, int g, int b)
{
	return MakeTextColorRGB(row1, col1, row2, col2, createRGB(r, g, b));
}
//*******************************************************************************
unsigned long CaplExcelSheet::GetCellColorRGB(int row, int col)
{
	if(0==g_pExcel) return 0;
	if(0==m_pWorkSheet)  return 0;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		return pCell->Interior->Color;
	}
	catch(...)
	{
		return 0;
	}
	return 0;
}
//*******************************************************************************
bool CaplExcelSheet::MakeCellColorRGB(int row1, int col1, int row2, int col2, unsigned long colorInHex)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);								
				
		pRet->Interior->Color = colorInHex;
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::MakeCellColorRGB(int row1, int col1, int row2, int col2, int r, int g, int b)
{
	return MakeCellColorRGB(row1, col1, row2, col2, createRGB(r, g, b));	
}
//*******************************************************************************
bool  CaplExcelSheet::MakeCellColorRGB(int row, int col, int r, int g, int b)
{
	return MakeCellColorRGB(row, col, row, col, r, g, b);
}
//*******************************************************************************
bool CaplExcelSheet::MakeCellColorRGB(int row, int col, COLORREF color)
{
	return MakeCellColorRGB(row, col, row, col, color);
}
//*******************************************************************************
bool  CaplExcelSheet::MakeCellColor(int row, int col, int colorIndex)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];	

		pCell->Interior->ColorIndex = colorIndex;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::ApplyStyleToTable(STableStyle &style)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	try
	{
		// заголовок
		MakeCellColorRGB(style._row1, style._col1, style._row1+style._headerRows-1, style._col2, style._headerColor);
		MakeTextColorRGB(style._row1, style._col1, style._row1+style._headerRows-1, style._col2, style._headerFontColor);

		// итого
		if(style._footerRows > 0)
			MakeCellColorRGB(style._row2-style._footerRows+1, style._col1, style._row2, style._col2, style._footerColor);

		// череда строк		
		for(int n=0, i=style._row1+style._headerRows; i<style._row2-style._footerRows+1; ++i, ++n)
		{
			if(n / 2 * 2 == n)
			{
				MakeCellColorRGB(i, style._col1, i, style._col2, style._line1Color);
			}
			else
			{
				MakeCellColorRGB(i, style._col1, i, style._col2, style._line2Color);
			}
		}		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::ApplyStyleBlueGray( int row1, int col1, int row2, int col2, int headerRows, int footerRows )
{
	STableStyle style;

	style._row1 = row1;
	style._col1 = col1;
	style._row2 = row2;
	style._col2 = col2;

	style._headerRows = headerRows;
	style._footerRows = footerRows;

	style._headerColor = createRGB(22, 141, 207);
	style._footerColor = createRGB(197, 201, 204);

	style._line1Color = createRGB(215, 219, 220);
	style._line2Color = createRGB(250, 250, 250);

	style._headerFontColor = createRGB(255, 255, 255);

	return ApplyStyleToTable(style);
}
//*******************************************************************************
bool CaplExcelSheet::ApplyStyleGreen( int row1, int col1, int row2, int col2, int headerRows /*= 1*/, int footerRows /*= 1*/ )
{
	STableStyle style;

	style._row1 = row1;
	style._col1 = col1;
	style._row2 = row2;
	style._col2 = col2;

	style._headerRows = headerRows;
	style._footerRows = footerRows;

	style._headerColor = createRGB(167, 208, 124);
	style._footerColor = createRGB(122, 146, 96);

	style._line1Color = createRGB(255, 255, 255);
	style._line2Color = createRGB(240, 249, 231);

	style._headerFontColor = createRGB(0, 0, 0);

	return ApplyStyleToTable(style);
}
//*******************************************************************************
bool CaplExcelSheet::ApplyStyleOrangeGray( int row1, int col1, int row2, int col2, int headerRows /*= 1*/, int footerRows /*= 1*/ )
{
	STableStyle style;

	style._row1 = row1;
	style._col1 = col1;
	style._row2 = row2;
	style._col2 = col2;

	style._headerRows = headerRows;
	style._footerRows = footerRows;

	style._headerColor = createRGB(250, 191, 143);
	style._footerColor = createRGB(235, 241, 222);

	style._line1Color = createRGB(255, 255, 255);
	style._line2Color = createRGB(240, 240, 240);

	style._headerFontColor = createRGB(0, 0, 0);

	return ApplyStyleToTable(style);
}
//*******************************************************************************
bool CaplExcelSheet::ApplyStyleGrayGray( int row1, int col1, int row2, int col2, int headerRows /*= 1*/, int footerRows /*= 1*/ )
{
	STableStyle style;

	style._row1 = row1;
	style._col1 = col1;
	style._row2 = row2;
	style._col2 = col2;

	style._headerRows = headerRows;
	style._footerRows = footerRows;

	style._headerColor = createRGB(242, 242, 242);
	style._footerColor = createRGB(242, 242, 242);

	style._line1Color = createRGB(255, 255, 255);
	style._line2Color = createRGB(240, 240, 240);

	style._headerFontColor = createRGB(0, 0, 0);

	return ApplyStyleToTable(style);
}
//*******************************************************************************
bool  CaplExcelSheet::MakeCellColor(int row1, int col1, int row2, int col2, int colorIndex)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;		

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		Excel::RangePtr pRet;	
		APL_EXCEL_SHEET->get_Range( pRange->Item[row1][col1], pRange->Item[row2][col2], &pRet);

		pRet->Interior->ColorIndex = colorIndex;		
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
//*******************************************************************************
bool CaplExcelSheet::AutoFitColumns()
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	
	try
	{
		APL_EXCEL_SHEET->Columns->AutoFit();					
	}
	catch(...)
	{
		return false;
	}
	return true;
}

//*******************************************************************************
bool  CaplExcelSheet::Select(LPCTSTR range, bool bConvertFromR1C1)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(range==0) return false;
	if(range[0]=='\0') return false;

	try
	{
		COleVariant v_range=range;
		if(bConvertFromR1C1) v_range=(*g_pExcel)->ConvertFormula(v_range,xlR1C1,xlA1);
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Range[v_range];
		pRange->Select();
		pRange->Activate();
	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::CopyRange(LPCTSTR from, LPCTSTR to, aplXlDirection direction, bool bConvertFromR1C1)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(from==0) return false;
	if(from[0]=='\0') return false;
	if(to==0) return false;
	if(to[0]=='\0') return false;

	try
	{
		COleVariant from_range=from, to_range=to;
		if(bConvertFromR1C1)
		{
			from_range=(*g_pExcel)->ConvertFormula(from_range,xlR1C1,xlA1);
			to_range=(*g_pExcel)->ConvertFormula(to_range,xlR1C1,xlA1);
		}
		Excel::RangePtr pRangeFrom = APL_EXCEL_SHEET->Range[from_range];
		pRangeFrom->Select();
		pRangeFrom->Activate();
		pRangeFrom->Copy();

		Excel::RangePtr pRangeTo = APL_EXCEL_SHEET->Range[to_range];
		pRangeTo->Insert((XlDirection)direction);

	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::CopyRangeWithoutShift(LPCTSTR from, LPCTSTR to, bool bConvertFromR1C1)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(from==0) return false;
	if(from[0]=='\0') return false;
	if(to==0) return false;
	if(to[0]=='\0') return false;

	try
	{
		COleVariant from_range=from, to_range=to;
		if(bConvertFromR1C1)
		{
			from_range=(*g_pExcel)->ConvertFormula(from_range,xlR1C1,xlA1);
			to_range=(*g_pExcel)->ConvertFormula(to_range,xlR1C1,xlA1);
		}
		Excel::RangePtr pRangeFrom = APL_EXCEL_SHEET->Range[from_range];
		pRangeFrom->Select();
		pRangeFrom->Activate();
		pRangeFrom->Copy();

		Excel::RangePtr pRangeTo = APL_EXCEL_SHEET->Range[to_range];
		pRangeTo->PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone);

	}
	catch(...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::CopyRangeWithoutShift(int source_row1, int source_col1, int source_row2, int source_col2, int dest_row1, int dest_col1, int dest_row2, int dest_col2)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (source_row1 < 0)  return false;
	if (source_col1 < 0)  return false;
	if (source_row2 < 0)  return false;
	if (source_col2 < 0)  return false;
	if (dest_row1 < 0)  return false;
	if (dest_col1 < 0)  return false;
	if (dest_row2 < 0)  return false;
	if (dest_col2 < 0)  return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pRangeFrom, pRangeTo;
		APL_EXCEL_SHEET->get_Range(pRange->Item[source_row1][source_col1], pRange->Item[source_row2][source_col2], &pRangeFrom);
		pRangeFrom->Select();
		pRangeFrom->Activate();
		pRangeFrom->Copy();
		APL_EXCEL_SHEET->get_Range(pRange->Item[dest_row1][dest_col1], pRange->Item[dest_row2][dest_col2], &pRangeTo);

		pRangeTo->PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone);

	}
	catch (...)
	{
		return false;
	}
	return true;
}
//*******************************************************************************
bool CaplExcelSheet::DeleteRange(LPCTSTR range, aplXlDirection direction, bool bConvertFromR1C1)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(range==0) return false;
	if(range[0]=='\0') return false;

	try
	{
		COleVariant v_range=range;
		if(bConvertFromR1C1) v_range=(*g_pExcel)->ConvertFormula(v_range,xlR1C1,xlA1);
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Range[v_range];
		pRange->Delete((XlDirection)direction);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetRowHeight(int rowNum, double &height)
{
	height=0;
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(rowNum<1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Rows->GetItem(rowNum);
		COleVariant val=pRange->GetRowHeight();
		height=val.dblVal;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetRowHeightInPx(int rowNum, double &height)
{
	height=0;
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(rowNum<1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Rows->GetItem(rowNum);
		COleVariant val=pRange->GetHeight();
		height=val.dblVal;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetRangeHeightInPx(int rowFrom, int rowTo, double &height)
{
	height=0;
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(rowFrom<1 || rowTo<1)  return false;
	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[rowFrom][1], pRange->Item[rowTo][1], &pRet);

		COleVariant val=pRet->GetHeight();
		height=val.dblVal;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetRangeWidthInPx(int colFrom, int colTo, double &width)
{
	width=0;
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(colFrom<1 || colTo<1)  return false;
	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		APL_EXCEL_SHEET->get_Range( pRange->Item[1][colFrom], pRange->Item[1][colTo], &pRet);

		COleVariant val=pRet->GetWidth();
		width=val.dblVal;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetColumnWidthInPx(int ColNum, double &width)
{
	width=0;
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(ColNum<1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Columns->GetItem(ColNum);
		COleVariant val=pRange->GetWidth();
		width=val.dblVal;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetColumnWidth(int ColNum, double &width)
{
	width=0;
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(ColNum<1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Columns->GetItem(ColNum);
		COleVariant val=pRange->GetColumnWidth();
		width=val.dblVal;
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetColumnWidth(int ColNum, double width)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(ColNum<1)  return false;
	if(width<=0)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Columns->GetItem(ColNum);
		COleVariant val=width;
		pRange->PutColumnWidth(val);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetRowHeight(int rowNum, double height)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(rowNum<1)  return false;
	if(height<=0)  return false;

	try
	{
		//Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		//Excel::RangePtr pCell = pRange->Item[rowNum][1];
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Rows->GetItem(rowNum);
		COleVariant val = height;
		pRange->PutRowHeight(val);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetRowsHeight(int rowNum1, int rowNum2, double height)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(rowNum1<1 || rowNum2<1) return false;
	if(height<=0)  return false;

	try
	{
		Excel::RangePtr pRng = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pRange;
		APL_EXCEL_SHEET->get_Range(pRng->Item[rowNum1][1], pRng->Item[rowNum2][1], &pRange);
		COleVariant val = height;
		pRange->PutRowHeight(val);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::AddChart(CChartInfo &info)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{	
		_ApplicationPtr appPtr = APL_EXCEL_SHEET->GetApplication();		

		// область с данными
		Excel::RangePtr pRet;	
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;		
		APL_EXCEL_SHEET->get_Range( pRange->Item[info._rowStart][info._colStart], 
			pRange->Item[info._rowEnd][info._colEnd], &pRet);

		// создание диаграммы
		ChartObjectsPtr chartObjects = (ChartObjectsPtr)(APL_EXCEL_SHEET->ChartObjects(vtMissing));		
		ChartObjectPtr chartObject = chartObjects->Add(	info._chartRect.left, 
														info._chartRect.top,
														info._chartRect.Width(),
														info._chartRect.Height());
		// делаем ее активной и задаем область данных
		chartObject->Activate();		
		appPtr->ActiveChart->ChartType = (XlChartType)info._chartType;
		appPtr->ActiveChart->SetSourceData(pRet);						
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::AddChartEx(CChartInfo &info, CaplExcelSheet* chart_sheet /*= NULL*/)
{
	if (chart_sheet==NULL)
		chart_sheet = this;
	if(0==g_pExcel) return false;
	if(0==chart_sheet->m_pWorkSheet)  return false;

	try
	{	
		Excel::RangePtr pRange = APL_EXCEL_CHART_SHEET->Cells;		
		_ApplicationPtr appPtr = APL_EXCEL_SHEET->GetApplication();		

		// создание диаграммы
		ChartObjectsPtr chartObjects = (ChartObjectsPtr)(APL_EXCEL_SHEET->ChartObjects(vtMissing));		
		ChartObjectPtr chartObject = chartObjects->Add(	info._chartRect.left, 
														info._chartRect.top,
														info._chartRect.Width(),
														info._chartRect.Height());

		chartObject->Activate();		
		appPtr->ActiveChart->ChartType = (XlChartType)info._chartType;	
		appPtr->ActiveChart->PutHasTitle(1, TRUE);
		appPtr->ActiveChart->ChartTitle->PutText(info._chartTitle.GetBuffer());
		appPtr->ActiveChart->Legend->PutPosition( (XlLegendPosition)info._legendPosition );				
		

		// делаем ее активной и задаем область данных из разных range		
		for(unsigned int i=0; i<info._seriesNames.size(); ++i)
		{			
			SeriesCollectionPtr series = appPtr->ActiveChart->SeriesCollection();
			SeriesPtr data = series->NewSeries();			
			data->Name = info._seriesNames[i].GetBuffer();			

			// значения по X (собираем переданные интервалы воедино)
			if(info._seriesXValues.size() > 0)
			{
				Excel::RangePtr pRetXValues = 0;	
				for(unsigned int j=0; j<info._seriesXValues[i].size(); ++j)
				{
					Excel::RangePtr pTmpXValues;
					APL_EXCEL_CHART_SHEET->get_Range( pRange->Item[info._seriesXValues[i].at(j)._row1][info._seriesXValues[i].at(j)._col1], 
						pRange->Item[info._seriesXValues[i].at(j)._row2][info._seriesXValues[i].at(j)._col2], 
						&pTmpXValues);

					if(pRetXValues == 0)
					{
						pRetXValues = pTmpXValues;
					}				
					else
					{
						pRetXValues = appPtr->Union(pRetXValues, pTmpXValues);					
					}
				}
				data->XValues = _variant_t((IDispatch*)pRetXValues);
			}			
			
			// значения по Y (собираем переданные интервалы воедино)
			if(info._seriesYValues.size() > 0)
			{
				Excel::RangePtr pRetYValues = 0;	
				for(unsigned int j=0; j<info._seriesYValues[i].size(); ++j)
				{
					Excel::RangePtr pTmpYValues;
					APL_EXCEL_CHART_SHEET->get_Range( pRange->Item[info._seriesYValues[i].at(j)._row1][info._seriesYValues[i].at(j)._col1], 
						pRange->Item[info._seriesYValues[i].at(j)._row2][info._seriesYValues[i].at(j)._col2], 
						&pTmpYValues);

					if(pRetYValues == 0)
					{
						pRetYValues = pTmpYValues;
					}				
					else
					{
						pRetYValues = appPtr->Union(pRetYValues, pTmpYValues);					
					}
				}
				data->Values = _variant_t((IDispatch*)pRetYValues);
			}	

			// некоторые дефолтные стили для определнного типа графиков
			if(info._chartType == xlPie)
			{
				appPtr->ActiveChart->ApplyDataLabels(xlDataLabelsShowPercent);				
			}

			if(info._chartType == xlXYScatterLines || 
				info._chartType == xlXYScatterSmoothNoMarkers)
			{
				appPtr->ActiveChart->ApplyDataLabels(xlDataLabelsShowValue);	
				//мин. значение по оси X (категорий)
				Excel::AxisPtr ptrAx= appPtr->ActiveChart->Axes(xlCategory, xlPrimary);
				if(info._bXMaxValueIsAuto==false)
				{
					ptrAx->MaximumScale = info._dMaxXValue;
				}
				if(info._bXMinValueIsAuto==false)
				{
					ptrAx->MinimumScale = info._dMinXValue;
				}
			}
	
			// прочие стили, когда данные уже добавлены			
			if (info._labelPosition!=xlLabelPositionNone)
			{
				DataLabelPtr label = data->DataLabels(0);
				if(0 != label)
					label->Position = (XlDataLabelPosition)info._labelPosition;
			}
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetColontileHeader( LPCTSTR text, aplXlColontilePosition pos/* = aplXlPosCenter*/)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();		
		if (pos == aplXlPosLeft)
		{
			pageSetup->PutLeftHeader(text);
		}
		else if (pos == aplXlPosCenter)
		{
			pageSetup->PutCenterHeader(text);
		}
		else if (pos == aplXlPosRight)
		{
			pageSetup->PutRightHeader(text);
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetColontileFooter( LPCTSTR text, aplXlColontilePosition pos/* = aplXlPosCenter*/)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();	
		if (pos == aplXlPosLeft)
		{
			pageSetup->PutLeftFooter(text);
		}
		else if (pos == aplXlPosCenter)
		{
			pageSetup->PutCenterFooter(text);
		}
		else if (pos == aplXlPosRight)
		{
			pageSetup->PutRightFooter(text);
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::FitColumnsInOnePage()
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();		
		pageSetup->PutZoom(false);		
		pageSetup->PutFitToPagesTall(false);
		pageSetup->PutFitToPagesWide(1);		
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetThroughColumns( LPCTSTR columns )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();		

		pageSetup->PutPrintTitleColumns(columns);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetThroughRows( LPCTSTR rows )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();		

		pageSetup->PutPrintTitleRows(rows);
	}
	catch(...)
	{
		return false;
	}
	return true;
}


bool CaplExcelSheet::FitContentOnePage()
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();		
		
		pageSetup->PutZoom(false);		
		pageSetup->PutFitToPagesTall(1);
		pageSetup->PutFitToPagesWide(1);		
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetLandScapeOrientation()
{
	return SetOrientation(xlLandscape);
}

bool CaplExcelSheet::SetPortraitOrientation()
{
	return SetOrientation(xlPortrait);
}

bool CaplExcelSheet::SetOrientation(int orientation)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	
	try
	{
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();
		if (orientation == xlLandscape)
			pageSetup->PutOrientation(xlLandscape);
		if (orientation == xlPortrait)
			pageSetup->PutOrientation(xlPortrait);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

/** например range = $A$1, повторяет первую строку*/
bool CaplExcelSheet::RepeatRangeOnPrint( LPCTSTR range )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{		
		PageSetupPtr pageSetup = APL_EXCEL_SHEET->GetPageSetup();
		pageSetup->PutPrintTitleRows(range);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::GetVPageBreaks(std::vector<int> &breaks)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		VPageBreaksPtr PageBreak = APL_EXCEL_SHEET->GetVPageBreaks();
		for(int i = 0; i < PageBreak->Count; i++)
		{
			VPageBreakPtr br = PageBreak->Item[i+1];
			RangePtr r = br->Location;
			if (r->Columns->Count > 0)
			{
				RangePtr c = r->Columns->GetItem(r->Columns->Count);
				breaks.push_back(c->Column);
			}
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetFreezeArea(LPCSTR range)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		(*g_pExcel)->GetActiveWindow()->PutFreezePanes(false);
		APL_EXCEL_SHEET->Range[range]->Select();
		(*g_pExcel)->GetActiveWindow()->PutFreezePanes(true);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

using namespace Office;

bool CaplExcelSheet::AddImage( CRect &rect, CString &path )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		_bstr_t fileName = path.GetBuffer();						
		APL_EXCEL_SHEET->Shapes->AddPicture(fileName, 
									Office::msoTrue, 
									Office::msoTrue, 
									static_cast<float>(rect.left), 
									static_cast<float>(rect.top), 
									static_cast<float>(rect.Width()), 
									static_cast<float>(rect.Height()));				
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SortRangeByColumnDescending( SRangeFormula &range )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;

	try
	{
		Excel::RangePtr pRet;	
		Excel::RangePtr pRangeAll = APL_EXCEL_SHEET->Cells;		

		APL_EXCEL_SHEET->get_Range( pRangeAll->Item[range._row1][range._col1], 
									pRangeAll->Item[range._row2][range._col2], 
									&pRet);		

		Excel::RangePtr pColumn = APL_EXCEL_SHEET->Columns->GetItem(10);

			pRet->SortSpecial(xlPinYin,
				_variant_t((IDispatch*)pColumn),
				xlDescending,
				vtMissing, _variant_t((IDispatch*)pColumn), xlDescending, _variant_t((IDispatch*)pColumn), xlDescending, xlNo, vtMissing, false, xlSortRows);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::IfZeroValue( CString value )
{
	if(value.IsEmpty())
		return false;

	if(value[0] != '0')
		return false;

	return (__atof(value) == 0.0);
}

bool CaplExcelSheet::Find(LPCTSTR val, int row1, int col1, int row2, int col2, std::set<std::pair<int, int>>& out_row_col)
{
	out_row_col.clear();

	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	bool bRet = false;
	Excel::RangePtr* pRet = new Excel::RangePtr;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;

		APL_EXCEL_SHEET->get_Range(pRange->Item[row1][col1], pRange->Item[row2][col2], &(*pRet));

		bRet = Find(val, pRet, out_row_col);
	}
	catch(...)
	{
		delete pRet;
		return false;
	}
	delete pRet;
	return bRet;
}

bool CaplExcelSheet::Find(LPCTSTR val, std::set<std::pair<int, int>>& out_row_col)
{
	out_row_col.clear();

	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	

	bool bRet = false;
	Excel::RangePtr* pRange = new Excel::RangePtr;
	try
	{
		*pRange = APL_EXCEL_SHEET->Cells;
		bRet = Find(val, pRange, out_row_col);
	}
	catch(...)
	{
		delete pRange;
		return false;
	}
	delete pRange;
	return bRet;
}

bool CaplExcelSheet::Find(LPCTSTR val, void* pRange, std::set<std::pair<int, int>>& out_row_col)
{
	out_row_col.clear();

	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	
	if(0==pRange)  return false;	

	try
	{
		Excel::RangePtr* pInRange = (Excel::RangePtr*)pRange;

		Excel::RangePtr pOutRange = (*pInRange)->Find(val, vtMissing, xlValues, xlWhole, vtMissing, xlNext);
		if (pOutRange != 0)
		{
			std::pair<int, int> new_val = std::make_pair(pOutRange->Row, pOutRange->Column);
			while (out_row_col.find(new_val) == out_row_col.end())
			{
				out_row_col.insert(new_val);

				_variant_t xlRng = (*pInRange)->Item[new_val.first][new_val.second];
				pOutRange = (*pInRange)->FindNext(xlRng);
				if (pOutRange == 0) break;

				new_val = std::make_pair(pOutRange->Row, pOutRange->Column);
			}
		}
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::AddPageBreak( int row )
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;	
	if(row<0)  return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][1];

		Excel::HPageBreakPtr pPageBreak = APL_EXCEL_SHEET->HPageBreaks->Add(pCell);
	}
	catch(...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::AddPageBreakVert(int col)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (col < 0)  return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[1][col];

		Excel::VPageBreakPtr pPageBreak = APL_EXCEL_SHEET->VPageBreaks->Add(pCell);
	}
	catch (...)
	{
		return false;
	}
	return true;
}


bool CaplExcelSheet::SetPageZoom(int zoom)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (zoom <= 0)  return false;

	try
	{
		PageSetupPtr pPtr = APL_EXCEL_SHEET->GetPageSetup();
		pPtr->PutZoom(zoom);
	}
	catch (...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::SetViewMode(aplXlWindowView xlViewMode)
{
	if (0 == g_pExcel) return false;

	try
	{
		(*g_pExcel)->GetActiveWindow()->PutView((XlWindowView)xlViewMode);
	}
	catch (...)
	{
		return false;
	}
	return true;
}

bool CaplExcelSheet::AddComment(int row, int col, LPCTSTR text)
{
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (row < 0 || col < 0)  return false;

	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		Excel::RangePtr pCell = pRange->Item[row][col];
		pCell->AddComment(text);
	}
	catch (...)
	{
		return false;
	}
	return true;
}

/*
bool CaplExcelSheet::SetCellWidth(int row, int col, double width)
{
	if(0==g_pExcel) return false;
	if(0==m_pWorkSheet)  return false;
	if(col<1 || row<1) return false;
	try
	{
		double ddd = 200.0;

		// пробуем через колонку
		Excel::RangePtr pColumn = APL_EXCEL_SHEET->Columns->GetItem(1);
		pColumn->ColumnWidth = ddd;
		pColumn->PutColumnWidth(ddd);

		// пробуем через range
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Cells;
		pRange->ColumnWidth = ddd;
		pRange->PutColumnWidth(ddd);

		// у range берем колонку и пробуем
		Excel::RangePtr EntireColumn = pRange->EntireColumn;
		EntireColumn->ColumnWidth = ddd;
		EntireColumn->PutColumnWidth(ddd);		
	}
	catch(...)
	{
		return false;
	}
	return true;
}*/
/*
try
{

}
catch(...)
{
	return false;
}
*/

bool CaplExcelSheet::GetRowTopInPx(int rowNum, double &dTop)
{
	dTop = 0;
	if (0 == g_pExcel) return false;
	if (0 == m_pWorkSheet)  return false;
	if (rowNum < 1)  return false;
	try
	{
		Excel::RangePtr pRange = APL_EXCEL_SHEET->Rows->GetItem(rowNum);
		COleVariant val = pRange->GetTop();
		dTop = val.dblVal;
	}
	catch (...)
	{
		return false;
	}
	return true;
}

int CaplExcelSheet::GetRowUnderPx(int nPix, int nBeginRow /* = 1 */)
{
	int nRow = nBeginRow;
	double dTop;
	if (!GetRowTopInPx(nRow, dTop))
		return nRow;

	if (dTop > nPix)
		return nRow;

	return GetRowUnderPx(nPix, ++nRow);
}

