Absolute cell references

[E.Naumovich]

I propose next modifications:

   std::string CellCoord::ToString(const bool abscol=false,
                                                                                                  const bool absrow=false) const
        {
            const int32_t iAlphLen = 26;
            const char * szAlph = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            std::string strCol;
            if(abscol) strCol="$";
            strCol.append( 1, szAlph[ col % iAlphLen] ); // last
            int32_t div = col / iAlphLen;
            while( true )
            {
                if( div == 0 ) break;

                div--;
                strCol.insert( strCol.begin(), szAlph[ div % iAlphLen ] );
                div /= iAlphLen;
            }
          if(absrow) strCol += "$"
            //return strCol + std::to_string( row );
            std::ostringstream ConvStream;
            ConvStream <<row;
            return strCol + ConvStream.str();
        }

Also: is it possible to assign "names" for the cells?

[Alexandr Belyak]

Hi,
Thanks for the proposal. Do I understand correctly that this modification for CChart::CellRangeString method?

is it possible to assign "names" for the cells?

Please clarify this idea. Do you want to write a value in a cell by its name (for example, "B3")?

[E.Naumovich]

This is modification is for making tunable reports. For example, you make a report, where one parameter may be varied for massive range of data. So, you can produce address like $B$5 for cell with this parameter and use it in formulas.
In Excel it is possible to assing name to the cell in special field (see attach.). This is convinuent option to make constants.

[Alexandr Belyak]

Thanks for explanation and example.
I will try to add this feature soon.

[E.Naumovich]

I found an error in my previous proposition concerning abs. addresses. This is separated function, look like it works:

std::string CellCrd2str(const SimpleXlsx::CellCoord & crd, const bool abscol, const bool absrow ){
{
            const int32_t iAlphLen = 26;
            const char * szAlph = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            std::string strCol;
            strCol.append( 1, szAlph[ crd.col % iAlphLen] ); // last
            int32_t div = crd.col / iAlphLen;
            while( true )
            {
                if( div == 0 ) break;

                div--;
                strCol.insert( strCol.begin(), szAlph[ div % iAlphLen ] );
                div /= iAlphLen;
            }
            if(abscol) strCol="$"+strCol;
            if(absrow) strCol+="$";
            return strCol + std::to_string( crd.row );

        }

}

[Alexandr Belyak]

I modified your first proposition and left it for now.
In the attachment is an updated version of the library with support for defined names. I would appreciate your testing and comments.
A small example:

#include <iostream>

#include <Xlsx/Workbook.h>

using namespace SimpleXlsx;

int main()
{
    CWorkbook Book( "Incognito" );
    CWorksheet & Sheet = Book.AddSheet( "Sheet 1" );
    CWorksheet & SecondSheet = Book.AddSheet( "Sheet 2" );

    Book.AddDefinedName( "HalfRad", 0.5, "Half radian" ).AddDefinedName( "MySin", "sin(HalfRad)" );
    Book.AddDefinedName( "MyCell", Sheet, CellCoord( 1, 0 ) );
    Book.AddDefinedName( "TwoCells", Sheet, CellCoord( 1, 0 ), CellCoord( 2, 0 ) );
    Book.AddDefinedName( "MyScope", Sheet, CellCoord( 1, 0 ), "", & SecondSheet );

    Sheet.AddSimpleRow( "=HalfRad" ).AddSimpleRow( "=MySin" );
    Sheet.AddSimpleRow( "=MyCell" ).AddSimpleRow( "=sum(TwoCells)" );

    SecondSheet.AddSimpleRow( "=MyScope" );

    if( Book.Save( "Defined.xlsx" ) ) std::cout << "The book has been saved successfully" << std::endl;
    else std::cout << "The book saving has been failed" << std::endl;
    return 0;
}

[E.Naumovich]

Looks like it works.
Thanks!

[Alexandr Belyak]

Thank you again for the idea and testing!
By the way, I reworked the CellCoord::ToString method and now it is up to three times faster than the old version (case with std::string).

[E.Naumovich]

Thanks! Nice job!