C / C++ / MFC

How can write sheet 2 in xlsx with excel automation?

7-Mar-11 12:53

I honestly don't know. I was debugging the CustomDraw message for some other reason, noticed the uItemState was set and cleared it. At which point I also said "Holy ****".

Le@rner6-Mar-11 18:29

Re: How can write sheet 2 in xlsx with excel automation?

6-Mar-11 18:29

Hi all,

i m writing a excel sheet with use os excel automation and i m using this code its forking fine.

but the data alwayz write in sheet one ,i want to write in sheet 2 and also want to rename the sheet.

please tell me how can i do this.

thanks in advance.

HRESULT AutoWrap_Exp(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) 
{
    // Begin variable-argument list...
    va_list marker;
    va_start(marker, cArgs);

    if(!pDisp) 
	{
		//MessageBox(NULL, "NULL IDispatch passed to AutoWrap_Exp()", "Error", 0x10010);
        //_exit(0);
		return 0;
    }

    // Variables used...
    DISPPARAMS dp = { NULL, NULL, 0, 0 };
    DISPID dispidNamed = DISPID_PROPERTYPUT;
    DISPID dispID;
    HRESULT hr;
    char buf[200];
    char szName[200];
    
    // Convert down to ANSI
    WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
    
    // Get DISPID for name passed...
    hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
    if(FAILED(hr)) 
	{
        sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
		OutputDebugStringA(buf);
		//MessageBox(NULL, buf, "AutoWrap_Exp()", 0x10010);
        //_exit(0);
        return hr;
    }
    
    // Allocate memory for arguments...
    VARIANT *pArgs = new VARIANT[cArgs+1];
    // Extract arguments...
    for(int i=0; i<cArgs; i++) 
	{
        pArgs[i] = va_arg(marker, VARIANT);
    }
    
    // Build DISPPARAMS
    dp.cArgs = cArgs;
    dp.rgvarg = pArgs;
    
    // Handle special-case for property-puts!
    if(autoType & DISPATCH_PROPERTYPUT) 
	{
        dp.cNamedArgs = 1;
        dp.rgdispidNamedArgs = &dispidNamed;
    }
    
    // Make the call!
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
    if(FAILED(hr)) 
	{
        sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
		OutputDebugStringA(buf);
		//MessageBox(NULL, buf, "AutoWrap_Exp()", 0x10010);
        //_exit(0);
        return hr;
    }
    // End variable-argument section...
    va_end(marker);
    
    delete [] pArgs;
    
    return hr;
}

void CTest_ExcelDlg::OnBnClickedButton2()
{
	try
	{	
		CString OpenFile_Name=_T("E:\\xl.xlsx");
		CWaitCursor cwt;

		DeleteFile(OpenFile_Name);

		BOOL flag=FALSE;

		int nRow=4;
		int nCol=2;

		CString locount;
		locount.Format(_T("%d"),nRow+1);
		CString rowp="";
		
		rowp="A1:B";
		
		rowp+=locount;

		CString column="";
		CString item="";

		// Initialize COM for this thread...
		CoInitialize(NULL);

		// Get CLSID for our server...
		CLSID clsid;
		HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

		if(FAILED(hr)) 
		{
			::MessageBox(NULL, _T("CLSIDFromProgID() failed"), _T("Error"), 0x10010);
			
			//MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
			return ;

		}

		// Start server and get IDispatch...
		IDispatch *pXlApp;
		hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
		if(FAILED(hr)) 
		{
			::MessageBox(NULL, _T("Excel not registered properly"), _T("Error"), 0x10010);
			

			//MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
			return ;

		}

		// Make it visible (i.e. app.visible = 1)
		{
			VARIANT x;
			x.vt = VT_I4;
			x.lVal = 0;
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
		}

		// Get Workbooks collection
		IDispatch *pXlBooks;
		{
			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
			
			pXlBooks = result.pdispVal;
		}

		// Call Workbooks.Add() to get a new workbook...
		IDispatch *pXlBook;
		{
			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
			
			pXlBook = result.pdispVal;
		}

		// Create a 15x15 safearray of variants...
		VARIANT arr;
		arr.vt = VT_ARRAY | VT_VARIANT ;
		{
			SAFEARRAYBOUND sab[2];
			sab[0].lLbound = 1; sab[0].cElements = nRow+1;
			sab[1].lLbound = 1; sab[1].cElements = nCol;
			arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
		}

		// Fill safearray with some values...
		for(int i=1; i<=nRow+1; i++) 
		{
			for(int j=1; j<=nCol; j++) 
			{
				// Create entry value for (i,j)
				VARIANT tmp;

				if(i==1)
				{	
					if(j==1)
						item=_T("Column 1");
					else
						item=_T("Column 2");
				}
				else
				{
					item.Format("Row :: %d and Column :: %d",i-2,j-1);
				}
						
				tmp.bstrVal = _bstr_t(item);
				tmp.vt = VT_BSTR;

				 //tmp.lVal = i*j;
				 // Add to safearray...
				 long indices[] = {i,j};
				 SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
			}
		}

		// Get ActiveSheet object
		IDispatch *pXlSheet;
		{
			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0)!=S_OK)			
			{
				flag=TRUE;
				goto error;
			}
			
			pXlSheet = result.pdispVal;
		}

		// Get Range object for the Range A1:O15...
		IDispatch *pXlRange;
		{			   
			VARIANT parm;
			parm.vt = VT_BSTR;
			parm.bstrVal = ::SysAllocString(_bstr_t(rowp));

			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
			VariantClear(&parm);

			pXlRange = result.pdispVal;
		}

		// Set range with our safearray...
		if(AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr)!=S_OK)
		{
			flag=TRUE;
			goto error;
		}

		// Wait for user...
		//   ::MessageBox(NULL, "All done.", "Notice", 0x10000);

		// Set .Saved property of workbook to TRUE so we aren't prompted
		// to save when we tell Excel to quit...
		{
			VARIANT x;
			x.vt = VT_I4;
			x.lVal = 1;
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}

			VARIANT result;
			VariantInit(&result);
			VARIANT fname;
			fname.vt = VT_BSTR;
			fname.bstrVal=::SysAllocString(_bstr_t(OpenFile_Name));
			//VARIANT fformat;
			//fformat.vt = VT_I4;
			//fformat.lVal=-4143;
			
			if(AutoWrap_Exp(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
		}

		// Tell Excel to quit (i.e. App.Quit)
		if(AutoWrap_Exp(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0)!=S_OK)
		{
			flag=TRUE;
			goto error;
		}

error:
		if(flag==TRUE)
		{
			// Tell Excel to quit (i.e. App.Quit)
			AutoWrap_Exp(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
			{
			}

			// Release references...
			pXlRange->Release();
			pXlSheet->Release();
			pXlBook->Release();
			pXlBooks->Release();
			pXlApp->Release();
			VariantClear(&arr);

			// Uninitialize COM for this thread...
			CoUninitialize();			

			MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
			return ;
		}

		// Release references...
		pXlRange->Release();
		pXlSheet->Release();
		pXlBook->Release();
		pXlBooks->Release();
		pXlApp->Release();
		VariantClear(&arr);

		// Uninitialize COM for this thread...
		CoUninitialize();
	}
	catch(...)
	{
		MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
		return ;
	}

	MessageBox(_T("Done."),_T("Message"),MB_ICONINFORMATION);
}

Roger Broomfield7-Mar-11 1:34

Re: How can write sheet 2 in xlsx with excel automation?

7-Mar-11 1:34

Instead of getting the "ActiveSheet" of pXLApp, get the "Worksheets" collection of pXLBook, then get "Item" 2 of that collection, that should give you the "sheet 2". Use a propertyput on the "Name" property of the Worksheet to change its name.

Le@rner7-Mar-11 1:57

Re: How can write sheet 2 in xlsx with excel automation?

7-Mar-11 1:57

can u please explain it with example.

Roger Broomfield7-Mar-11 4:36

Re: How can write sheet 2 in xlsx with excel automation? [modified]

7-Mar-11 4:36

I use the Excel Dispatch Wrapper classes, which makes it much easier, this is exactly what I do in my own code

_Workbook           m_excel_workbook;
Worksheets          m_excel_worksheets;
_Worksheet          m_excel_worksheet;

// stuff happens here to add a new workbook just like you do

LPDISPATCH pdispatch;
LPCTSTR szSheetName = _T("name of sheet");
COleVariant varIndex((short)2);
pdispatch = m_excel_workbook.GetWorksheets();
if (pdispatch != NULL)
{
    m_excel_worksheets.AttachDispatch(pdispatch);
    pdispatch = m_excel_worksheets.GetItem(varIndex);
    if (pdispatch != NULL)
    {
        m_excel_worksheet.AttachDispatch(pdispatch);

        m_excel_worksheet.SetName(szSheetName);
    }
}

Trying to fit that to your code would look something like this, I think

IDispatch* pXlSheets;
VARIANT result;
VariantInit(&result);
AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlBook, L"Worksheets", 0);
pXlSheets = result.pdispVal;

IDispatch* pXlSheet2;
VARIANT item;
result.vt = VT_EMPTY;
result.pdispVal = NULL;
item.vt = VT_I4;
item.lVal = 2;
AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, item);
pXlSheet2 = result.pdispVal;

VARIANT name;
name.vt = VT_BSTR;
name.bstrVal = ::SysAllocString("name of your sheet");
AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlSheet2, L"Name", 1, name);

Le@rner7-Mar-11 17:22

Re: How can write sheet 2 in xlsx with excel automation?

7-Mar-11 17:22

Thanks its done with your help.

just one more query for this its working fine for default 3 sheets,
how can add new sheet and work for it?
thanks in advance.

modified on Monday, March 7, 2011 11:52 PM

Roger Broomfield7-Mar-11 20:34

Re: How can write sheet 2 in xlsx with excel automation? [modified]

7-Mar-11 20:34

How I do it :-

COleVariant varOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
COleVariant varCount((short)(2),VT_I2); //
COleVariant varType((short)xlWorksheet,VT_I2); //-4167
pdispatch = m_excel_worksheets.Add(varOptional/*Before*/,varOptional/*After*/,varCount,varType);
pdispatch->Release();

Fitted to your code, ( I think this will work given the quick comparison I did of your AutoWrap_Exp and MFC's InvokeHelper ) :-

IDispatch* pXlSheet2;
VARIANT optional;
result.vt = VT_ERROR;
result.lVal = (long)DISP_E_PARAMNOTFOUND;
VARIANT count;
count.vt = VT_I2;
count.iVal = 2; // number of sheets to add
VARIANT type;
type.vt = VT_I2;
type.iVal = -4167;
VARIANT result;
VariantInit(&result);
AutoWrap_Exp(DISPATCH_METHOD, &result, pXlSheets, L"Add", 4, optional, optional, count, type);
result.pdispVal->Release();

Access the extra sheets as before using propertyget "Item". Using optional for both Before and After means, if I recall correctly, the 2 new sheets are added to the end of the sheets collection.

Le@rner7-Mar-11 21:03

Re: How can write sheet 2 in xlsx with excel automation?

7-Mar-11 21:03

thanks.

if i know how many row write in excel than its working fine and successfully write the excel file.

but when i write excel file from reading a text file so i dont know how many rows are need to be written so i am unable to create this array to assign value.

VARIANT arr;
		arr.vt = VT_ARRAY | VT_VARIANT ;
		{
			SAFEARRAYBOUND sab[2];
			sab[0].lLbound = 1; sab[0].cElements = nRow+1;
			sab[1].lLbound = 1; sab[1].cElements = nCol;
			arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
		}

please tell me this case how can i write the value in excel file

thanks in advance.

modified on Tuesday, March 8, 2011 3:10 AM

Roger Broomfield7-Mar-11 21:30

Re: How can write sheet 2 in xlsx with excel automation? [modified]

7-Mar-11 21:30

You could either :-

read the file once, counting the rows, create suitably sized array, read the file again filling in the values

or

read the file once, adding a single row to excel for each row in the file, stopping when there are no more rows in the file

Le@rner7-Mar-11 21:42

VS 2008 Configuration For Libraries

7-Mar-11 21:42

thanks.

when the data of column is in form of multiline than its automatically display with wrap text option,how can i disable this.

modified on Wednesday, March 9, 2011 5:09 AM

encode char?

Nguyen Sy Bang6-Mar-11 17:55

Nguyen Sy Bang

6-Mar-11 17:55

I have a difficult question. In WM_CHAR: If i use IME: EN, i will receive corect code input char.(a:97). I use IME:Japan, Result is so(corect). When I select IME:VietNamese, Tamil...I received ?(code:63). Please help me.(I set location:japan)

Mike Certini6-Mar-11 17:53

Mike Certini

6-Mar-11 17:53

I just recently tried to create a library and then use the library functions within a simple console application. I followed the instructions at:

http://www.functionx.com/visualc/libraries/staticlib.htm

After copying the "lib" and "h" file into the source file directory for the program, I got a linking error. I added these files to the project via "Add Existing Item" menu selection. The error I got was the following:

A custom build rule to build files with extension 'lib' could not be found.

Would you like to create a new rule to define a custom build rule to build files with this extension?

I would think that using library functions would be a standard functionality for Visual C. Is there a setting somewhere that has to be adjusted to allow for this functionality? If not, does anyone know the rule that needs to be used?

--------------------------------------------------------------------------------

Michael Dunn6-Mar-11 18:12

Michael Dunn

6-Mar-11 18:12

You don't add the .lib file to the project, since it isn't a source file. Add the filename to the linker options, so it looks there for the library functions that you call.

--Mike--
Dunder-Mifflin, this is Pam.

Mike Certini6-Mar-11 18:31

Mike Certini

6-Mar-11 18:31

Mike,

Thank you for your reply. Where do you add it? I added it to configuration properties\linker\general\additional library directories. I though am now getting LINK2019 for each of the functions. See below:

Exercise.obj : error LNK2019: unresolved external symbol "double __cdecl Max(double const *,int)" (?Max@@YANPBNH@Z) referenced in function _main
1>Exercise.obj : error LNK2019: unresolved external symbol "double __cdecl Min(double const *,int)" (?Min@@YANPBNH@Z) referenced in function _main
1>Exercise.obj : error LNK2019: unresolved external symbol "double __cdecl Average(double const *,int)" (?Average@@YANPBNH@Z) referenced in function _main
1>Exercise.obj : error LNK2019: unresolved external symbol "double __cdecl Sum(double const *,int)" (?Sum@@YANPBNH@Z) referenced in function _main
1>MSVCRTD.lib(crtexew.obj) : error LNK2019: unresolved external symbol _WinMain@16 referenced in function ___tmainCRTStartup

tagopi6-Mar-11 19:53

tagopi

6-Mar-11 19:53

hello,

in your project properties, linker/input, add your .lib file name.
for eg.
yourlibfile.lib

this will solve your issue.

Regards,
A. Gopinath.

Mike Certini8-Mar-11 5:43

Mike Certini

8-Mar-11 5:43

tagopi,

This is the solution that was posted by WayneAKing in the Visual C++ Developer Center forum. His posting is in response to an error listing that I posted. This guy knows everything. My program is now working.

I added the directory to the project configuration

>properties - linker - general tab.

That's half the requirements.

What did I say to add, and where did I say to add it?
Look under Linker->Input for "Additional Dependencies"
and add your .lib filename to the list.

>int main()

You are making a console program.

>error LNK2019: unresolved external symbol _WinMain@16
>referenced in function ___tmainCRTStartup

WinMain is used with Win32 GUI applications, not console apps.
How did you create the project? Which project template did
you use? You should have selected "Win32 Console Application".

Look under Linker->System for "SubSystem" and set it to
"Console (/SUBSYSTEM:CONSOLE)"

- Wayne

Something really crazy

csrss6-Mar-11 15:03

csrss

6-Mar-11 15:03

So here is something really crazy. I have a class, which looks something like this:

protected:
HWND hWindow;
/*
here are 100 of other variables.
*/

HWND hChildren[100]; // i am collecting children windows handles here

private:
/*
here are 100 private functions.
*/

public:
/*
here goes constructor and destructor, and some public functions
*/

Everything works fine. But if i change hChildren[100] to hChildren[1000] - so now i can add up to 1000 children, class starts acting mental. Consider the following:

void function1(){}
void function2(){}

I am calling function1(),

myClass *cl = new myClass();
cl->function1();

instead of that, the function, which gets called - is function2(). This happens if i change size of hChildren from 100 to 1000. If size is 100, the one which executes - is function1() (which is OK). Have you ever experienced code behavior like this? Its like, some undesirable polymorphism occurred D'Oh! | :doh:

011011010110000101100011011010000110100101101110
0110010101110011

Hans Dietrich6-Mar-11 15:51

Hans Dietrich

6-Mar-11 15:51

The first thing I do for "crazy, can't happen" stuff is rebuild the whole solution. Oh, and you might try deleting the .ncb file too.

Best wishes,
Hans

[Hans Dietrich Software]

Emilio Garavaglia6-Mar-11 21:23

Emilio Garavaglia

6-Mar-11 21:23

Although Hans is mosssst likely right (this sort of things happens when you copy some files around and the timestamps of the object in the project are anymore coherent, so you rebuild only partially something that should be rebuilt all)consider if your design needs a review.

An array of 1000 HWND is 4 or KBytes long, and fits all into the object storage that may be used in the stack as a local variable. The space on the stack is not "as wide as the computer is", so think to dynamic allocation for this kind of bulks (for example: std::vector<hwnd> don't have this problem).

Also: a window with 1000 active children is kick in the ass for the operating system: do you really need all of them? Can't you create only the ones that are really visible?

2 bugs found.
> recompile ...
65534 bugs found.
D'Oh! | :doh:

CPallini6-Mar-11 21:44

CPallini

6-Mar-11 21:44

Emilio Garavaglia wrote:
An array of 1000 HWND is 4 or KBytes long, and fits all into the object storage that may be used in the stack as a local variable.

Isn't the stack set to 1 Mb by default?

If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.

This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke

[My articles]

Emilio Garavaglia7-Mar-11 4:10

Emilio Garavaglia

7-Mar-11 4:10

It is a parameter defined at link time. The stack is allocated when a thread is created, based on the parameter the shell find in the exe, just before starting the process.
It is 1MB for MS compilers, but other may be different.
But the point is how many of those object can exist on the stack in case of a recursive call: 1MB is not a soooo big space.
If you know that will never happen, OK.

2 bugs found.
> recompile ...
65534 bugs found.
D'Oh! | :doh:

csrss7-Mar-11 0:47

csrss

7-Mar-11 0:47

Container of size 1000 never gets really filled, nor does even that one of size 100, so its not that bad, trust me Wink | ;)

Basically, i would never go with an app which has 1000 child controls present at a time - it was rather like "testing here, testing there, changing some value here and there, see what can happen", but as well it was a quick check if i can theoretically have an array of 1000 controls. So, thank you for an advice on vector, i haven't considered this, but now will try Smile | :)

Thanks

011011010110000101100011011010000110100101101110
0110010101110011

Richard MacCutchan6-Mar-11 22:29

Richard MacCutchan

6-Mar-11 22:29

1000 HWNDs on your system? Any idea of the overhead that would place on the Operating System? No wonder things go crazy. I would suggest that even 100 on the average PC is far too many.

I must get a clever new signature for 2011.