how to define such variables using loop

Question

0.00/5 (No votes)

See more:

Hi all, I come across a problem, In my program, I use MFC to open a Excel,I want to write numbers to Excel. In Excel, A1,A2,A3...are the names of a column of cells, How can I use loop to make the following code more readable. I mean how to represent A1,A2,A3...using something like Ai

C++

range=sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
range.SetValue2(COleVariant(num[1]));

range=sheet.GetRange(COleVariant("A2"),COleVariant("A2"));
range.SetValue2(COleVariant(num[2]));
...
range=sheet.GetRange(COleVariant("A30"),COleVariant("A30"));
range.SetValue2(COleVariant(num[30]));

I want to write it in this way:

C++

for(int i=1;i<=30;i++)
{
   range=sheet.GetRange(COleVariant("A？？？"),COleVariant("A？？？"));
   range.SetValue2(COleVariant(num[i]));

}

Posted 1-Nov-12 21:42pm

Angela2012

Add a Solution

2 solutions

Solution 2

You can write a function that converts row and column indices to strings / variants that can be passed. Here is an example:

C++

// Create cell string.
// Note that indices begin at 1!
// Size of lpszCell must be >= 8.
LPCTSTR CExcelDispatch::SetCellString(LPTSTR lpszCell, int nRow, int nCol) const
{
    LPTSTR lpszOut = lpszCell;
    if (--nCol > 25) // make it 0 based and check for column > "Z"
    {
        *lpszOut = _T('A') + static_cast<TCHAR>(nCol / 26 - 1);
        ++lpszOut;
        nCol %= 26;
    }
    *lpszOut = _T('A') + static_cast<TCHAR>(nCol);
    _itot(nRow, ++lpszOut, 10);
    return lpszCell;
}

// Get a range of cells for a worksheet.
// Pass -1 for nEndRow and nEndCol to select a single cell.
// Note that indices start at 1!
Range CExcelDispatch::GetSheetRange(_Worksheet& Worksheet, 
    int nStartRow, int nStartCol, 
    int nEndRow /*= -1*/, int nEndCol /*= -1*/)
{
    ASSERT(Worksheet.m_lpDispatch);
    ASSERT(nStartRow > 0);              // indices are 1 based!
    ASSERT(nStartCol > 0);              // indices are 1 based!
    ASSERT(nEndRow > 0 || nEndCol < 0); // nEndRow must be > 0 or nEndRow and nEndCol < 0
    ASSERT(nEndCol > 0 || nEndRow < 0); // nEndCol must be > 0 or nEndRow and nEndCol < 0
    ASSERT(nStartCol < 26 * 26);        // max. column ZZ
    ASSERT(nEndCol < 26 * 26);          // max. column ZZ

    TCHAR lpszCell[32];
    COleVariant vCell1(SetCellString(lpszCell, nStartRow, nStartCol));
    COleVariant vCell2((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    if (nEndRow > 0 && nEndCol > 0 && 
        (nEndRow != nStartRow || nEndCol != nStartCol))
    {
        vCell2 = COleVariant(SetCellString(lpszCell, nEndRow, nEndCol));
    }
    return Worksheet.GetRange(vCell1, vCell2);
}

Posted 1-Nov-12 22:03pm

Jochen Arndt

Comments

nv3 2-Nov-12 4:45am

These are nice little helpers. You answered much more than the questioner asked for, but I think he or she will have good use for these little functions. +5.

Jochen Arndt 2-Nov-12 5:14am

Thank you. Just a copy and paste from an existing project. But I thought like you that such a general function might be useful when the project becomes bigger.

Angela2012 2-Nov-12 5:04am

I always learns a lot when I visit this forum,There are so many kind helpers, Thank you.

Jochen Arndt 2-Nov-12 5:10am

You are welcome.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Accepted Answer · 2012-11-01T22:00:00

Solution 1

C++

for(int i=1;i<=30;i++)
{
   char cell[10];
   sprintf(cell, "A%d", i);
   range=sheet.GetRange(COleVariant(cell),COleVariant(cell));
   range.SetValue2(COleVariant(num[i]));
}

Posted 1-Nov-12 22:00pm

CPallini

Comments

Angela2012 2-Nov-12 4:12am

Thank you very much! Your solution is excellent, it works well.

CPallini 2-Nov-12 4:52am

You are welcome.