Click here to Skip to main content
15,891,976 members
Search within:


Articles / Desktop Programming / MFC
 

Printing to Excel

Rate me:
Please Sign up or sign in to vote.
4.00/5 (8 votes)
20 Apr 20022 min read 252.6K   3.9K   55  
This article shows how to send data to Excel instead of a printer.
 
/////////////////////////////////////////////////////////////////////////////
//	Implementation for CPrintExcel

#include "stdafx.h"
#include <afxctl.h>

#include "PrintExcel.h" 


#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif



#pragma message ("Make sure you go to Tools.Options.Directories and add the paths to mso97.dll and vbeext1.olb.  Mso97.dll will usually be in c:\\\"Program Files\"\\\"Microsoft Office\"\\Office, and vbeext1.olb will be in c:\\\"Program Files\"\\\"Common Files\"\\\"Microsoft Shared\"\\VBA")
#import "mso97.dll" no_namespace rename("DocumentProperties", "DocumentPropertiesXL")   
#import "vbeext1.olb" no_namespace   
#import "excel8.olb" rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") rename("DocumentProperties", "DocumentPropertiesXL") no_dual_interfaces

#pragma warning (disable:4192)

#define	ID_EXCEL_MAXROWS	10

// Constructor
CPrintExcel::CPrintExcel () {
	m_nCurrentRow=0;
	m_nCurrentCol=1;
	m_nSorted=FALSE;
	m_nTop=-1;
	m_nBottom=-1;
	m_nError = FALSE;
	
	// decimalsign and thousand sign are in german different
	m_nGerman = FALSE;
}

// destructor
CPrintExcel::~CPrintExcel () {
	CPoint	*pPoint;

	// Free memory
	while (m_ExcelCols.GetSize () > 0) {
		pPoint = (CPoint *) m_ExcelCols.GetAt (0);
		delete pPoint;
		m_ExcelCols.RemoveAt(0);
	}
	while (m_ExcelRows.GetSize () > 0) {
		pPoint = (CPoint *) m_ExcelRows.GetAt (0);
		delete pPoint;
		m_ExcelRows.RemoveAt(0);
	}
}

///////////////////////////////////////////////////////////////////////
// transform CRect to columns
int	 CPrintExcel::CalcExcelCols (CRect &rect) {
	int	nN, nFound;
	CPoint	*pPoint;

	nFound = FALSE;
	for (nN = 1; nN <= m_ExcelCols.GetSize (); nN++) {
		pPoint = (CPoint *) m_ExcelCols.GetAt (nN - 1);
		if (pPoint->x == rect.left && pPoint->y == rect.right) {
			nFound = TRUE;
			break;
		}
	}

	if (!nFound) {
		pPoint = new (CPoint);
		pPoint->x = rect.left;
		pPoint->y = rect.right;
		m_ExcelCols.Add ((CObject *) pPoint);
		// used to calculate blank lines
		m_nExcelRowHeight = rect.bottom - rect.top;
	}

	return TRUE;
}

///////////////////////////////////////////////////////////////////////
// Get the current row and  col
int CPrintExcel::ExcelGetRowCol (CRect &rect, 
								   int	*pnCol, 
								   int	*pnRow,
								   int	*pnSorted,
								   float	*pfWidth,
								   char	*cCell,
								   int	nArray,
								   int	nUseTop) {

	int	nN, nFound;
	CPoint	*pPoint, *pPoint2;


	// First of all the different cols must be sorted
	if (!(*pnSorted)) {
		(*pnSorted) = TRUE;
		nFound = TRUE;
		while (nFound) {
			nFound = FALSE;
			for (nN = 0; nN < m_ExcelCols.GetUpperBound(); nN++) {
				pPoint = (CPoint *)(m_ExcelCols).GetAt (nN);
				pPoint2 = (CPoint *)(m_ExcelCols).GetAt (nN +1);

				// compare
				if (pPoint->x > pPoint2->x) {
					nFound = TRUE;
					// swap
					m_ExcelCols.SetAt (nN, (CObject *) pPoint2);
					m_ExcelCols.SetAt (nN +1, (CObject *)pPoint);
				}
			}
		}
	}
			


	(*pfWidth) = -1;
	nFound = FALSE;
	for (nN = 1; nN <= m_ExcelCols.GetSize (); nN++) {
		pPoint = (CPoint *) m_ExcelCols.GetAt (nN - 1);
		if (pPoint->x >= rect.left) {
			// When the next field is equal to right, it is better to use this column
			if (nN < m_ExcelCols.GetSize () 
				&& pPoint->x > rect.left) {	// but x is not equl with left
				pPoint = (CPoint *) m_ExcelCols.GetAt (nN);
				if (pPoint->y == rect.right) nN++;
			}
			nFound = TRUE;
			// transform the width to column width, used in Excel
			(*pfWidth) = ((float)(pPoint->y - pPoint->x))/10;
			break;
		}
	}

	
	if (!nFound) {
		// and append the array
		pPoint = new (CPoint);
		pPoint->x = rect.left;
		pPoint->y = rect.left +60;	// 60 is a standard width
		m_ExcelCols.Add ((CObject *) pPoint);
		// transform the width to column width, used in Excel
		(*pfWidth) = ((float)(pPoint->y - pPoint->x))/10;
			
	}
	else {
		*pnCol = nN;
	}

	if (nArray) {	// when using lines I have to use an array of columns, not only one column
		nFound = FALSE;
		for (nN = 1; nN <= m_ExcelCols.GetSize (); nN++) {
			pPoint = (CPoint *) m_ExcelCols.GetAt (nN - 1);
			if (pPoint->x >= rect.right) {
				nFound = TRUE;
				nArray = nN;	// used later
				break;
			}
		}
		// use a line horizontaly for the whole page 
		if (!nFound) nArray = m_ExcelCols.GetSize();
	}

	// search for the row
	nFound = FALSE;
	for (nN = 1; nN <= m_ExcelRows.GetSize (); nN++) {
		pPoint = (CPoint *) m_ExcelRows.GetAt (nN - 1);
		if (nUseTop) {
			if (pPoint->y == rect.top ||
				pPoint->y == (rect.top +1) ||		// a small tolerance
				((pPoint->y)+1) == rect.top) {		// a small tolerance
				nFound = TRUE;
				break;
			}
		}
		else {
			if (pPoint->x == rect.bottom ||
				pPoint->x == (rect.bottom +1) ||		// a small tolerance
				((pPoint->x)+1) == rect.bottom) {		// a small tolerance
				nFound = TRUE;
				break;
			}
		}
	}
	// no line found -> a new line will be created
	if (!nFound) {
		// shiften
		if (m_ExcelRows.GetSize () == ID_EXCEL_MAXROWS) {
			// shift the rows
			pPoint2 = (CPoint *) m_ExcelRows.GetAt (0);
			for (nN = 1; nN <= m_ExcelRows.GetUpperBound(); nN++) {
				pPoint = (CPoint *)m_ExcelRows.GetAt (nN);
				m_ExcelRows.SetAt (nN -1, (CObject *) pPoint);
			}
			pPoint2->x = rect.bottom;
			pPoint2->y = rect.top;
			m_ExcelRows.SetAt (ID_EXCEL_MAXROWS -1, (CObject *)pPoint2);
		}		
		else {
			pPoint = new (CPoint);
			pPoint->x = rect.bottom;
			pPoint->y = rect.top;
			m_ExcelRows.Add ((CObject *) pPoint);
		}
		nN = m_ExcelRows.GetUpperBound();
		if (nN > 0) {
			pPoint = (CPoint *)m_ExcelRows.GetAt (nN);
			pPoint2 = (CPoint *)m_ExcelRows.GetAt (nN-1);
			nN = pPoint->y - pPoint2->y;
			// are there blank lines?
			nN = nN/m_nExcelRowHeight;
			if (nN < 1) nN = 1;
			(*pnRow) = (*pnRow) + nN;
		}
		else (*pnRow)++;
			
	}

	// transform col and row to A## - ZZ##
	if (nArray > 0) {	// e. g. A2:ZZ2
		if (nArray < (*pnCol)) nArray = *pnCol;
		if ((*pnCol) > 26) {
			sprintf (cCell, "A%c%d:A%c%d", (*pnCol) -1 -26 + 'A', (*pnRow), nArray -1 -26 + 'A', (*pnRow));
		}	
		else {
			sprintf (cCell, "%c%d:%c%d", (*pnCol) -1 + 'A', (*pnRow), nArray -1 + 'A', (*pnRow));
		}

	}
	else {	// one cell e. g. B5
		if ((*pnCol) > 26) {
			sprintf (cCell, "A%c%d", (*pnCol) -1 -26 + 'A', (*pnRow));
		}	
		else {
			sprintf (cCell, "%c%d", (*pnCol) -1 + 'A', (*pnRow));
		}
	}


	return TRUE;
}

// If this is placed in the scope of the smart pointers, they must be
// explicitly Release(d) before CoUninitialize() is called.  If any reference
// count is non-zero, a protection fault will occur.
struct StartOle {
    StartOle() { CoInitialize(NULL); }
    ~StartOle() { CoUninitialize(); }
} _inst_StartOle;


///////////////////////////////////////////////////////////////////////
// send data to Excel
int CPrintExcel::PrintExcel (int		nFunction, 
						  CRect		*pRect, 
						  const char *cText,
						  COLORREF	cref,
						  LOGFONT	*pLogFont,
						  int		nFormat) {

	// Static-Variablen
	CPoint		*pPoint;
	float		fWidth;
	CString		str;
	char		cCell[10];
	

	// Variables used for excel
using namespace Excel;
	static		_ApplicationPtr pXL=NULL;
	static		WorkbooksPtr pBooks;
	static		_WorkbookPtr pBook;
	static		WorksheetsPtr pSheets;
	static		_WorksheetPtr pSheet;
	static		nErrorAufgetreten=FALSE;
	

	FontPtr			pFont;
	RangePtr		pRange;
	PageSetupPtr	pageSetup;
	BordersPtr		pBorders;
	BorderPtr		pBorder;

		
	COleVariant	covTrue((short)TRUE),
				covFalse((short)FALSE),
				covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);



   try {  // This one will fail

	switch (nFunction) {
	case ID_EXCEL_INIT:
		if (pXL != NULL) PrintExcel (ID_EXCEL_CLOSE);
		// Init is made by the conctructor
		// m_nCurrentRow = 0;
		// m_nCurrentCol = 0;
		// m_nSorted = FALSE;
		// m_nError = FALSE;
		// m_nTop = -1; m_nBottum = -1;

		// Start Excel and get Application object.
		pXL.CreateInstance(L"Excel.Application");

		//Get a new workbook.
		pBooks = pXL->Workbooks;	
		pBook = pBooks->Add (covOptional);

		//Get the first sheet.
		pSheets = pBook->GetSheets();
		
		pSheet = pSheets->GetItem(COleVariant((short)1));

		// Show Excel after sending the data is ready
		// for testing its better to set "Visible" TRUE
		// pXL->Visible = VARIANT_TRUE;	

			

		break;

	case ID_EXCEL_CLOSE:
		if (pXL) {
			if (cText)
				pSheet->Name = cText;	// Name of the sheet

			pXL->Visible = VARIANT_TRUE;	// show Excel
		
			//enum XlPageOrientation
			//{
				//xlLandscape = 2,
				//xlPortrait = 1
			//};
			// set portrait or landscape
			if (nFormat == ID_FORMAT_LANDSCAPE)
				pSheet->GetPageSetup()->PutOrientation (xlLandscape);
			else
				pSheet->GetPageSetup()->PutOrientation (xlPortrait);
		}

		pXL = NULL;	// deactivate
	
		break;

	case ID_EXCEL_TYPTEXT: 
		if (m_nError) return FALSE;
		if (pXL == NULL) PrintExcel (ID_EXCEL_INIT);
		if (pXL == NULL) return FALSE;

		ExcelGetRowCol ((*pRect), &m_nCurrentCol, &m_nCurrentRow,
			&m_nSorted, &fWidth, cCell, FALSE, 
			FALSE);
		m_nTop = pRect->top;	// save it for later use
		m_nBottom = pRect->bottom;

		if(fWidth>=0)	// set the width for the column
			pSheet->Range[COleVariant(cCell)]->ColumnWidth=COleVariant (fWidth);

		
		switch (nFormat) {
		case ID_FORMAT_NUMBER0:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0"));
			break;
		case ID_FORMAT_NUMBER1:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,0"));
			break;
		case ID_FORMAT_NUMBER2:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,00"));
			break;
		case ID_FORMAT_NUMBER3:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,000"));
			break;
		case ID_FORMAT_NUMBER4:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,0000"));
			break;
		case ID_FORMAT_NUMBER5:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,00000"));
			break;
		case ID_FORMAT_NUMBER6:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,000000"));
			break;
		case ID_FORMAT_RIGHT:
			pSheet->Range[COleVariant(cCell)]->HorizontalAlignment = (short) -4152;	// Zentriert
			break;
		case ID_FORMAT_CENTER:
			pSheet->Range[COleVariant(cCell)]->HorizontalAlignment = (short) -4108;	// Zentriert
			break;
		default:
			pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("@"));
			break;
		
		}

		if (strlen(cText) == 0) break;	// nothing to print

		switch (nFormat) {
		case ID_FORMAT_NUMBER0:
		case ID_FORMAT_NUMBER1:
		case ID_FORMAT_NUMBER2:
		case ID_FORMAT_NUMBER3:
		case ID_FORMAT_NUMBER4:
		case ID_FORMAT_NUMBER5:
		case ID_FORMAT_NUMBER6:
			str = cText;
			// Germans my change the decimalsign!!
			if (m_nGerman) {		
				str.Replace (".", "");	// remove thousand sign
				str.Replace (",", ".");	// chnge decimal sign
			}
			else {
				str.Replace (".", "");	// remove thousand sign
			}
			strcpy ((char *) cText, str);
			break;
		}

		pSheet->Range[COleVariant(cCell)]->Value = cText;


		break;


	case ID_EXCEL_TYPBACKGROUND:
		if (m_nError) return FALSE;
		if (pXL == NULL) PrintExcel (ID_EXCEL_INIT);
		if (pXL == NULL) return FALSE;

		if (cref == RGB (255, 255, 255)) return TRUE;	// white must not be changed
		ExcelGetRowCol ((*pRect), &m_nCurrentCol, &m_nCurrentRow,
				&m_nSorted, &fWidth, cCell, TRUE); // Allways use array;

		pSheet->Range[COleVariant(cCell)]->GetInterior()->PutColor((long) cref);

		break;

	case ID_EXCEL_TYPTEXTFONT:
		if (m_nError) return FALSE;
		if (pXL == NULL) PrintExcel (ID_EXCEL_INIT);
		if (pXL == NULL) return FALSE;

		ExcelGetRowCol ((*pRect), &m_nCurrentCol, &m_nCurrentRow,
				&m_nSorted, &fWidth, cCell, FALSE);

		pFont = pSheet->Range[COleVariant(cCell)]->GetFont (); 
		if (pLogFont->lfWeight != 400)
			pFont->PutBold(VARIANT_TRUE);
		
		pFont->Name = pLogFont->lfFaceName;
		pFont->Italic = COleVariant (pLogFont->lfItalic);
		
		// lfHeight = -MulDiv(PointSize, GetDeviceCaps(hDC, LOGPIXELSY), 72);
		// transform height of the font
		pFont->Size = COleVariant ((short) (-(pLogFont->lfHeight*72/96)));
		

		switch (pLogFont->lfUnderline) {
		case FALSE:	// nothing to do, it is standard
			// pFont->Underline = (short) -4142;
			break;
		case TRUE:
			pFont->Underline = (short) 2;
			break;
		}
		pFont->Strikethrough = COleVariant (pLogFont->lfStrikeOut);
		// black is standard
		if (cref != RGB (0, 0, 0))
			pFont->Color = COleVariant ((long) cref);


		break;



	case ID_EXCEL_TYPLINESINGLETOP:		
	case ID_EXCEL_TYPLINESINGLEBOTTOM:	
	case ID_EXCEL_TYPLINESINGLELEFT:	
	case ID_EXCEL_TYPLINESINGLERIGHT:	
	case ID_EXCEL_TYPLINEDOUBLETOP:	
	case ID_EXCEL_TYPLINEDOUBLEBOTTOM:
	case ID_EXCEL_TYPLINEDOUBLELEFT:
	case ID_EXCEL_TYPLINEDOUBLERIGHT:

		if (nErrorAufgetreten) return FALSE;
		if (pXL == NULL) PrintExcel (ID_EXCEL_INIT);
		if (pXL == NULL) return FALSE;


		ExcelGetRowCol ((*pRect), &m_nCurrentCol, &m_nCurrentRow,
				&m_nSorted, &fWidth, cCell, TRUE); // Allways use array


	// xlInsideHorizontal = 12,
    // xlInsideVertical = 11,
    // xlDiagonalDown = 5,
    // xlDiagonalUp = 6,
    // xlEdgeBottom = 9,
    // xlEdgeLeft = 7,
    // xlEdgeRight = 10,
    // xlEdgeTop = 8

//		enum XlBorderWeight {
//    xlHairline = 1,
  //  xlMedium = -4138,
    //xlThick = 4,
//    xlThin = 2
// };
//		enum XlLineStyle {
//    xlContinuous = 1,
    //xlDash = -4115,
    //xlDashDot = 4,
    //xlDashDotDot = 5,
    //xlDot = -4118,
    //xlDouble = -4119,
    //xlSlantDashDot = 13,
    //xlLineStyleNone = -4142
// };

		pBorders = pSheet->Range[COleVariant(cCell)]->GetBorders();
		
		switch (nFunction) {
		case ID_EXCEL_TYPLINESINGLETOP:
		case ID_EXCEL_TYPLINEDOUBLETOP:
			pBorder = pBorders->GetItem(xlEdgeTop);
			break;
		case ID_EXCEL_TYPLINESINGLEBOTTOM:
		case ID_EXCEL_TYPLINEDOUBLEBOTTOM:
			pBorder = pBorders->GetItem(xlEdgeBottom);
			break;
		case ID_EXCEL_TYPLINESINGLELEFT:
		case ID_EXCEL_TYPLINEDOUBLELEFT:
			pBorder = pBorders->GetItem(xlEdgeLeft);
			break;
		case ID_EXCEL_TYPLINESINGLERIGHT:
		case ID_EXCEL_TYPLINEDOUBLERIGHT:
			pBorder = pBorders->GetItem(xlEdgeRight);
			break;
		}
		
		pBorder->PutWeight ((short) xlThin);
		if (nFunction == ID_EXCEL_TYPLINESINGLETOP ||
			nFunction == ID_EXCEL_TYPLINESINGLEBOTTOM ||
			nFunction == ID_EXCEL_TYPLINESINGLELEFT ||			
			nFunction == ID_EXCEL_TYPLINESINGLERIGHT) {
			pBorder->PutLineStyle ((short) xlContinuous);
		}
		if (nFunction == ID_EXCEL_TYPLINEDOUBLETOP ||
			nFunction == ID_EXCEL_TYPLINEDOUBLEBOTTOM ||
			nFunction == ID_EXCEL_TYPLINEDOUBLELEFT ||			
			nFunction == ID_EXCEL_TYPLINEDOUBLERIGHT) {
			pBorder->PutLineStyle ((short) xlDouble);
		}
			
		break;

	
	}

    } catch (_com_error &e) {
        // dump_com_error(e);
		pXL->Visible = VARIANT_TRUE;	// show excel
		pXL = NULL;
		m_nError = TRUE;

		// memory is freed by the destructor
	}



	return TRUE;
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Franz Maerzinger
Austria Austria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 20 Apr 2002

Article Copyright 2002 by Franz Maerzinger
Everything else Copyright © CodeProject, 1999-2024

Web04 2.8:2024-04-02:1