Open Excel in VC++ and write data

I'm not familiar with any overall reference.

Hi Tom;

I want to set a value like '0000' in text mode. When do this; the cell value like '0' . But i want to set in a text format. How can i do?
Also i want to set cell value like 1,000.55 . But when i do this the cell like 1000.55 . That is i want to use excel option "Use 1000 separetor (.)" .
Do you know that how can i do this?


Thanks for your help..

Above I set the numberFormat for a range to "0.000".
Here if you set the NumberFormat to "@" BEFORE you enter 0000 it will treat the number as text and not remove the leading 0's.

Code:

---

{ 
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"@");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);
VariantClear(&result);
VariantClear(&frmt);

}

---

For commas precede the number format with #,##

for example "#,##0" for integers with commas
"#,##0.00" to have 2 decimals and use the comma seperators.

Hi Tom;

Thank you very much. Tom i have a problem with date format. I formatted excel cell for date like your code "dd-mm-yyyy". And then send date long value to excel. But Excel doesnt accept the date format given by my project. The reason that my regional setting is Turkish and in excel column format option there is an category named "custom" show the same format must be as "gg.aa.yyyy"

gg means in Turkish is dd (day)
aa means in Turkish is mm (month)

Do you have any solution for this problem? Because when the regional setting language is changed, the custom format characters for the excel must be change :( .

Instead of solve this problem we can set excel cell format category to Date.
Do you know how can i set excel cell format category to "Date" and "Time".
For "Time" formatting also same problem exist..

Thanks for your help..

The easiest way to find the characters for your system is to record a macro in Excel while you manually change to the desired format and then examine the characters in the recorded macro.

Hello, does anybody knows how can i fix that error?

"Assertion failed: !"bogus context in _ExceptionHandler()", file XX.CPP, line 2936"

I don't know why, but if anyone has found something similar, i'd be so pleased reading the way to fix it. That happens to me while i'm executing an excel's generator process.

Thanks.

Hi TOM,

Which translator you are using?, the "b2cse" from microsoft website?. Actually this is translating for Excel 97 right?.

Can you help me out how to get this statements in VC++?

Sheet1.cells(5,2) = "10"

Sheets("Sheet1").Copy After:=Sheets(3)

Sheets("Sheet1 (2)").Name = "dfds"

Any specific rules are there to form these.

The following were all done with Visual C++ 6.0. In Visual Studio.Net the function calls and class names often are slightly different. You can find out
the actual form by examining the clases created after importing the type library.

Copy a worksheet.
Copying a worksheet

Rename the first worksheet

Code:

---

_Application app;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
if (!app.CreateDispatch("Excel.Application"))
              {
              AfxMessageBox("Couldn't start Excel.");
              }

//VOptional is defined similarly in the example cited above.
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);   
books = app.GetWorkbooks();
book = books.Add(covOptional);
sheets =book.GetSheets();
sheet = sheets.GetItem(COleVariant((short)1));
sheet.SetName("Severity");

---

Set the value of a cell

Code:

---

range = sheet.GetRange(COleVariant("B5"), COleVariant("B5"));
range.SetValue(COleVariant("Filename"));

---

The following function is usefull in translating row and column number into the string name of the cell.

Code:

---

void IndToStr(int row,int col,char* strResult)
    {
    if(col>26)
          {
          sprintf(strResult,"%c%c%d\0",'A'+(col-1)/26-1,'A'+(col-1)%26,row);
          }else{
    sprintf(strResult,"%c%d\0",'A'+(col-1)%26,row);
    }
}

---

hey Tom Frohman,
i used your code for Excel writng i realy so much thanx!
but now i m having one more problem is that i want to set Height, Width to any pericular Cell. so plz help me for that.
u can send me the code for it on my mail account plz
[email protected]
Thanx in advance

Code:

---

IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
pXlRange1 = result.pdispVal;
}

{
VARIANT height;
height.vt=VT_BSTR;
height.bstrVal=::SysAllocString(L"30"); //height in points
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"RowHeight", 1, height);
VariantClear(&height);
}

{
VARIANT width;
width.vt=VT_BSTR;
width.bstrVal=::SysAllocString(L"40"); //width in characters
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"ColumnWidth", 1, width);
VariantClear(&width);
}

---

Hi TOM,

realy so much thanx for ur code!

i m sorry to late thanx.

coz i was out station.

now i hav another question to u is that

i want to set image to the xlRange given by us.

for example COleVariant(L"I4:M19");

so plz help me for this.............

once more thanx for ur code realy i helped it a lot.

i waiting for ur relpy.

[email protected]

Quote:

---

Originally Posted by DnyanAnand

Hi TOM,

realy so much thanx for ur code!

i m sorry to late thanx.

coz i was out station.

now i hav another question to u is that

i want to set image to the xlRange given by us.

for example COleVariant(L"I4:M19");

so plz help me for this.............

once more thanx for ur code realy i helped it a lot.

i waiting for ur relpy.

[email protected]

---

"Love starts with a smile, grows with a kiss and ends with a tear"

Previously posted was importing an image from a file. In it we positioned the image using the coordinates of the top,left, width and height of a picture. I repeat that cod at the end down below.

Given a range like "I4:M19" I would get the range

Code:

---

IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"I4:M19");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;

}

---

now I would try and get the top,left,width and height properties from the range object.

Code:

---

double thetop;
{
VARIANT result;
unsigned short vals;
vals=(unsigned short)0x0000007e;
AutoWrap(DISPATCH_PROPERTYGET,&result,pXlRange1,L"Top",0);
thetop=result.dblVal;
}

---

Repeat the above for Left, Width and Height.
use these number in add picture below.

Code:

---

IDispatch *pXlShapes;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
pXlShapes = result.pdispVal;
}
IDispatch *pXlShape;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"C:\\toad1.jpg\0");
VARIANT xpropf;
xpropf.vt=VT_BOOL;
xpropf.boolVal=FALSE;
VARIANT xpropt;
xpropt.vt=VT_BOOL;
xpropt.boolVal=TRUE;

VARIANT xtop;
xtop.vt=VT_R8;
xtop.dblVal=10.0;
VARIANT xleft;
xleft.vt=VT_R8;
xleft.dblVal=10.0;
VARIANT xwidth;
xwidth.vt=VT_R8;
xwidth.dblVal=200.0;
VARIANT xheight;
xheight.vt=VT_R8;
xheight.dblVal=200.0;

//I need to put the arguments in reverse order
AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"AddPicture", 7,xheight,xwidth,xleft,xtop,xpropt,xpropf,fname);
pXlShape = result.pdispVal;
}

---

Hi Tom,

I m so much thanx to u...

this code of seting image in range is helped me a lot.......

if i will get some doubt realy i will call u rather than anyone else......

hey i want to know that can u solve the problem of csharp also?

if then i will definately ask u......

i m realy new to this field...........

just having six month now wrking in one small scale company..........

Hi Tom,

i hav one more problem with this code ,

i have one file image in clipboard, so i hav to create a bitmap & then hav to

use in this code. it is tediuos task.

now i want to use the clipboard image directly to be paste in this range.

plz help me for this........


thanx in advance

Best Regards

DnyanAnand
(Budding software Engg.)

I won't worry about how you get the image into the clipboard. I started by copying a bitmap image into the clipboard.

From the previous post you can get the range and then get coordinates of the paste area.

You will A) paste the image to the excel sheet.
B) get the shapes class
C) get the particular shape to resize
D) reset the height and width to the desired values.

Code:

---

IDispatch *pXlRange1;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"H1:J3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
pXlRange1 = result.pdispVal;
}

{
VARIANT result;
AutoWrap(DISPATCH_METHOD,&result,pXlRange1,L"Select",0);
}

{
VARIANT result;
AutoWrap(DISPATCH_METHOD,&result,pXlSheet,L"Paste",0);
}

IDispatch *pXlShapes;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
pXlShapes = result.pdispVal;
}

//get the shape
//I assume the image is shape 1.

IDispatch *pXlShape;
{
VARIANT result;
VariantInit(&result);
VARIANT xitem;
xitem.vt=VT_I4;
xitem.lVal=1;
AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"Item", 1,xitem);
pXlShape = result.pdispVal;
}

//set the width
{
VARIANT result;
VariantInit(&result);
VARIANT xwidth;
xwidth.vt=VT_R8;
xwidth.dblVal=400.0;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlShape, L"Width", 1,xwidth);

}

---

sorry i would have send u the thing how to get the image from clipboard.

now plz see i have the code is like this

OpenClipboard();
HBITMAP hBmp = (HBITMAP)GetClipboardData(CF_BITMAP);

now i have to assing this bitmap to variant

like
Variant var;
var.vt = ? //which property shud i use...

if i use VT_CF it is for clipborad format directly.

then how should be assinged........plz specify it.

if i use the VT_BLOB it is for byte then how to use it

or shud i use any thing else to assing bitmap handle to Variant....


i will try some how with this ur code if possible then i will tell u........

but i know i cant think so briliant so plz help me for this.....

Thanx in advance

Hi Tom;

I want to format cell in the excel form.
I send a time value as a long to excel and want to see that value as time value.
Can you give an example for time format of the cell for excel.
I tried but Excel show me null value for each test.

Thanks.

Code:

---

IDispatch *pXlRange2;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"H8");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1,range);

pXlRange2 = result.pdispVal;

}

{ 
VARIANT value;
value.vt = VT_I4;
value.lVal = 39848;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange2, L"Value", 1,value);
}

{ 
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"mmmm d, yyyy");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange2, L"NumberFormat", 1, frmt);

VariantClear(&frmt);

}

---

Hi, I tried opening and writing data in excel as explained in this thread. Everything worked great until it got to the save as part. the file I opened was a txt file(tab delimited file) and I colored some cells. but after I saved the file as "output.xls", I reopened the file and all the colors were lost. It seems the format isn't changed at all.
My code:

VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal = ::SysAllocString(L"D:\\data\\output.xls");
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname );

I know there is a format that I should use. but when i put this


VARIANT fformat;
fformat.vt = VT_I4;
fformat.lVal=-4143;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 2, fformat, fname );

the code breaks at the last line and it is not working. Please help!

Hi TOM,

Plz I need your Help in,
1] Delete single Row.
2] Delete Single Column
3] Add Single Row
4] Add single Column
5] Add new Sheet in workBook

Thanx in Advance........I will be waiting for ur reply

[email protected]

First get the range of the cell where you want to insert or delete.

Code:

---

IDispatch *pXlRange2;
{ 
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"B2");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1,range);
pXlRange2 = result.pdispVal;
}

---

If you want to insert or delete a column get the EntireRow Or the EntireColumn.
To delete a row

Code:

---

IDispatch *pXlRow;

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange2, L"EntireRow",0);
pXlRow = result.pdispVal;
AutoWrap(DISPATCH_METHOD, &result, pXlRow, L"Delete", 0);
}

---

To insert a row

Code:

---

IDispatch *pXlRow;

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange2, L"EntireRow",0);
pXlRow = result.pdispVal;
AutoWrap(DISPATCH_METHOD, &result, pXlRow, L"Insert", 0);
}

---

Replace the word row above with column to and you have the methods for columns.

To delete a sheet.

Code:

---

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_BSTR;
itemn.bstrVal = ::SysAllocString(L"Sheet3");;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"Delete", 0);

}

---

To insert a sheet before Sheet3

Code:

---

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_BSTR;
itemn.bstrVal = ::SysAllocString(L"Sheet3");
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
}
IDispatch *pXlSheet2;
{
VARIANT result;
VariantInit(&result);
VARIANT before;
before.vt = VT_DISPATCH;
before.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 1, before);
pXlSheet2 = result.pdispVal;
}

---

Thank you very much! Tom

I need the code for after "sheet3" in the same way

VARIANT After;
After.vt = VT_DISPATCH;
After.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 1, After);

i will be witing for your reply!

Thanks In Advance.

Using an optional argument I have versions for before and after here.

Before

Code:

---

IDispatch *pXlSheet2;
{
VARIANT result;
VariantInit(&result);
VARIANT Optional;
Optional.vt=VT_ERROR;
//DISP_E_PARAMNOTFOUND defined in WINERROR.H
Optional.scode=(long)DISP_E_PARAMNOTFOUND;
VARIANT before;
before.vt = VT_DISPATCH;
before.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 1, before);
pXlSheet2 = result.pdispVal;
}

---

After

Code:

---

IDispatch *pXlSheet2;
{
VARIANT result;
VariantInit(&result);
VARIANT Optional;
Optional.vt=VT_ERROR;
//DISP_E_PARAMNOTFOUND defined in WINERROR.H
Optional.scode=(long)DISP_E_PARAMNOTFOUND;
VARIANT after;
after.vt = VT_DISPATCH;
after.pdispVal = pXlSheet;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Add", 2, after,Optional);
pXlSheet2 = result.pdispVal;
}

---

*************Thank you very much! Tom*************


Now i am coming with some new problem towords you.

That is i want to print the Whole active sheet. so plz help me for this stuff.


Thanks In Advance..........

Dnyan
You can't shake hands with a clenched fist............................

Code:

---

IDispatch *pXlRange2;
{ 
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"UsedRange", 0);
pXlRange2 = result.pdispVal;
AutoWrap(DISPATCH_METHOD,&result,pXlRange2,L"PrintOut",0);
}

---

Hi Tom i m inserting the Image at the range specified by the user


void InsertImage(CString strImagePath, CString strRange)//Image will set within the Range given
{
VARIANT result;
VariantInit(&result);

VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(strRange);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
pXlRange = result.pdispVal;


//Calculating the top, left, height & width of the Range
unsigned short vals;
vals=(unsigned short)0x0000007e;
double dTop,dLeft,dWidth,dHeight;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET,&result,pXlRange,L"Top",0);
dTop = result.dblVal;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET,&result,pXlRange,L"Left",0);
dLeft = result.dblVal;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET,&result,pXlRange,L"Width",0);
dWidth = result.dblVal;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET,&result,pXlRange,L"Height",0);
dHeight = result.dblVal;

IDispatch *pXlShapes;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
pXlShapes = result.pdispVal;

IDispatch *pXlShape;
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(strImagePath);
VARIANT xpropf;
xpropf.vt=VT_BOOL;
xpropf.boolVal=FALSE;
VARIANT xpropt;
xpropt.vt=VT_BOOL;
xpropt.boolVal=TRUE;

VARIANT xtop;
xtop.vt=VT_R8;
xtop.dblVal= dTop;
VARIANT xleft;
xleft.vt=VT_R8;
xleft.dblVal=dLeft;
VARIANT xwidth;
xwidth.vt=VT_R8;
xwidth.dblVal=dWidth;
VARIANT xheight;
xheight.vt=VT_R8;
xheight.dblVal=dHeight;

AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"AddPicture", 7,xheight,xwidth,xleft,xtop,xpropt,xpropf,fname);
pXlShape = result.pdispVal;
}

using the code given by you

the image is inserted but not at the exact location of the range given

so please let me know where i m wrong?

one more thing i want to ask that

i want to insert the image at Begining cell given(like L"B2")
with exact image size means(picture left, top, width & height) whatever it may be.

Thanks in advance

The arguments xleft and xtop are in the wrong positions: A typo in my code.

Replace this

Code:

---

AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"AddPicture", 7,xheight,xwidth,xleft,xtop,xpropt,xpropf,fname);

---

with this

Code:

---

AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"AddPicture", 7,xheight,xwidth,xtop,xleft,xpropt,xpropf,fname);

---

You can substitute the actual size of the image for xheight and xwidth if you don't want to force the image to be a certain size.

Or you can rescale the picture to the original size without knowing the original size using:

Code:

---

{
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VARIANT sratio;
sratio.vt=VT_R8;
sratio.dblVal=1.0;
AutoWrap(DISPATCH_METHOD, &result, pXlShape, L"ScaleWidth", 2,xprop,sratio);
AutoWrap(DISPATCH_METHOD, &result, pXlShape, L"ScaleHeight", 2,xprop,sratio);
}

---