"dimension" tag is hard coded as "A1", is there a way to get it to be correct?

[Tony Kennedy]

I've got some code that reads in a XLSX file, but it relies on the "dimension" tag being correct.

In CWorksheet::Init, it's hard coded to be "A1". Is there an easy way of getting this updated before the worksheet is written to disk?

Thanks in advance,

Tony.

[Alexandr Belyak]

Hi Tony,
I tried to do automatic detection of the data area. Due to the specifics of the library implementation, the "dimension" tag was added after the data itself. This is correct for XML files, but Excel 2010 gave a message about a corrupted file.
Therefore, I had only one option left - through additional parameters when creating a CWorksheet (the CWorksheet::SParams structure). To do this, you need to know the size of the data area in advance.
Please check if this option is suitable for you. Updated library in the attachment. Below is a simple usage example.

#include <iostream>

#include <Xlsx/Workbook.h>

using namespace SimpleXlsx;

int main()
{
    CWorkbook book;
    CWorksheet::SParams Params;
    Params.LastUsedCell = CellCoord( 2, 1 );    // Be careful with the value, it must be correct
    CWorksheet & Sheet = book.AddSheet( "Test", Params );
    Sheet.BeginRow().AddCell( 123 ).EndRow();
    Sheet.BeginRow().AddCell().AddCell( 456 ).EndRow();
    if( book.Save( "Test.xlsx" ) )
        std::cout << "The book has been saved successfully" << std::endl;
    else std::cout << "The book saving has been failed" << std::endl;
    return 0;
}

[Tony Kennedy]

Thanks for this. I should be able to work out the number of rows/cols before the sheet is created (even if I have to create the sheet twice to calculate the counts).

One note. Would it be possible to store the position in the file where the dimensions are, and then rewind to it to insert the correct dimension?

Something like the following?

long nCurrentXMLWriterOffset = m_XMLWriter->GetBufferPosition();

..magic happens...
..calculate correct dimensions...

m_XMLWriter->SetWriterOffset(nCurrentXMLWriterOffset);
m_XMLWriter->InsertTag( "dimension" ).Attr( "ref", "A1:Z100" ).EndL();

[Tony Kennedy]

Turns out it's difficult for me to work out the ranges.

Would it be straightforward to configure the XMLWriter to write to a string before outputting to disk? That way you could insert the correct dimensions. The drawback would be the amount of memory required would increase.

[E.Naumovich]

Likely it can be done using ScratchTable?
Sheet should be constructed after ScratchTable became filled and required parameters might be calculated in ScratchTable.

[Alexandr Belyak]

I think it's a good idea. If I understand correctly, then for this it is necessary to use the initial part of the clsScratchTable::InsertTo function to obtain the data area range?
The CWorksheet::SParams structure may be extended in the future as many more parameters are hard-coded.

[E.Naumovich]

Very bold way is just to search Cells vector for max Cells[i].row Cells[i].col values for cases when Cells[i].priority<=0. I cannot do it in few days because I have some urgent business at work and my home PC is under construction.

[E.Naumovich]

Quick and dirty solution for the ScratchTable:

 void clsScratchTable::GetDim(size_t &rw, size_t & cl) const  {
 rw=0;
 cl=0;
 for(const auto &c:Cells)
     if(c.priority<1){
        if(c.row>rw) rw=c.row; 
        if(c.col>cl) cl=c.col;
     }

};

not tested yet , sorry.

[Tony Kennedy]

If it helps, I've made some changes that now give the correct range (changed files attached). It's not that elegant, but it does work and does not require the replacement of the XML writer. You're welcome to incorporate it in SimpleXlsxWriter if you want.

To summarise, store the dimension tag location, write as before to a string instead of a file stream, at the end, insert the dimension tag at the correct place and write.

All I've done is this.
- Change the output stream into a std::stringstream, and create a new std::ofstream to replace it.
- In CWorksheet::Init, store the position of the marker in the stream for use later.
- In CWorksheet::Save, get the output string from the stream, insert the "dimension" tag at the correct place, then set the string in the stream to this new string with the "dimension" tag.
- In XMLWriter::EndAll, write the string from the stringstream to the output stream.

Other changes.
In CWorksheet, created new m_nMaxColIndex and m_nMaxRowIndex variables to store the max row/column numbers, new function (UpdateCellBounds) which is called each time AddCell is called to update these numbers.

[Alexandr Belyak]

Thank you for your solution. It can help some people.
But I will not include it in the main code, as this will contradict one of the main features of this library:
"The main feature of this library is that it uses C++ standard file streams. On the one hand it results in almost unnoticeable memory and CPU resources consumption while processing (that may be very useful at saving a large data arrays), but on the other hand it makes unfeasible to edit data that were written.".

Thank you again for your cooperation!

Best regards,
Alexandr

[Tony Kennedy]

Yes, I understand.

Is there space to have two modes? One where you allow auto calculation of the range, the other where you specify it in advance using SParams?

Tony.

[Alexandr Belyak]

I apologize for not answering at all due to lack of free time.
I think that E.Naumovich suggested the best solution and it should be the main one.

[E.Naumovich]

Dear all, please check solution using ScratchTable (see example in Samples, SimpleScratch.cpp ), and generated SimpleScratch.xlsx (with similar zip to )

[Tony Kennedy]

Can you summarise what changes have been made (and how to use them)?

[E.Naumovich]

There is a helper class, clsScratchTable, which is used to organize matrix-like filling of the xlsx. Its usage can be found in example "Samples\SimpleScratch.cpp"
In general you have to open worksheet, to prepare styles for you needs, than you can make few ScratchTable's, for example one for each sheet you want to use. You can add cells to sheet, delete or replace added cell. When ScratchTable is finished, you can request its dimensions and then use them for filling xlsx file. Simple example can be found in SimpleScratch.cpp, below line 125.
I did not change anything in main library, only added method for clsScratchTable to calculated boundaries. It can be found in file Source\scratch\clsScratchTable.cpp, line 171 and below.

[Tony Kennedy]

Will these mods be added to the offical SimpleXlsxWriter ?

[Alexandr Belyak]

Yes, these mods are already official.

[Tony Kennedy]

I've made a start to look at implementing this. There is actually a lot of rework to existing code to implement this, I've pretty much got to re-write everything.

It's not immediately clear how to actually use it. All my existing code has things like
m_Sheet->BeginRow();
m_Sheet->AddCell(value, style_id);
m_Sheet->AddCell(value, style_id);
m_Sheet->AddCell(value, style_id);
m_Sheet->EndRow();

How would I do this with the new clsScratchTable? How do you add a new row?

Thanks in advance,

Tony.

[E.Naumovich]

There is an example, SimpleScratch.cpp, starting from line 125.
You dont need to add row, you should prepare cell with desired coordinates and than add it to clsScratchTable.

clsScratchTable Tbl;
     clsCell cl;
     cl.Set(0,2,CurTime,CustomDateTimeStyleIndex);
     Tbl.Add(cl);
     cl.Set(0,1,"Entry");
     Tbl.Add(cl);
     cl.Set(0,0,"0:0");
     Tbl.Add(cl);
     cl.Set(2,2,"2:2");
     Tbl.Add(cl);
     cl.Set(3,0,"0:3, Long");
     Tbl.Add(cl);
     cl.SetLong(3,1,std::numeric_limits<int64_t>::max());
     Tbl.Add(cl);
      size_t Nr,Nc;
     CWorksheet::SParams Params;
     Tbl.GetDim(Nr,Nc);
      Params.LastUsedCell = CellCoord( Nr, Nc );
     CWorksheet & Sheet1 = book.AddSheet( "tbltst", Params);

This approach is different compare to direct operation with Sheet, but is much more convenient for complicated reports.

[Tony Kennedy]

I see. Implementing this would require a complete reworking for me, I'll just patch with my own mods.

[Alexandr Belyak]

It seems I have found a solution. First, "A1" is written to the dimension attribute in the XML file, then many spaces are added. Additionally, the position in the XML file is saved. This format suits for Excel 2010. After the XML file is saved, it reopens and overwrites the value of the dimension attribute.

If it's not difficult, check the work of the updated library for yourself. Excel should open files correctly.

Tony, do you read XML files yourself or do you use some special library for Excel files? This information may be useful to other people.

[Tony Kennedy]

Yes, I do read Excel sheets, that's how I discovered the problem originally.

I originally tried doing exactly what you have done, but couldn't make it work reliably (the sheets were often unreadable). This is why I ended up writing to a buffer, storing the location (as you have done), then inserting the correct dimensions in the string before writing to the output stream.

I'll give the 0.41 beta a test.

[Tony Kennedy]

Sadly it's not compiling for me, Visual Studio is complaining about the following.

m_UsedCellFirst.row = m_UsedCellFirst.col = std::numeric_limits< uint32_t >::max();

and

m_UsedCellFirst.row = std::min( m_UsedCellFirst.row, m_row_index );
m_UsedCellFirst.col = std::min( m_UsedCellFirst.col, Col );
m_UsedCellLast.row = std::max( m_UsedCellLast.row, m_row_index );
m_UsedCellLast.col = std::max( m_UsedCellLast.col, Col );

I get the following errors.

SimpleXlsx\Source\Xlsx\Worksheet.cpp(156,82): warning C4003: not enough arguments for function-like macro invocation 'max'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(156,82): error C2589: '(': illegal token on right side of '::'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(156,82): error C2062: type 'unknown-type' unexpected
SimpleXlsx\Source\Xlsx\Worksheet.cpp(156,82): error C2059: syntax error: ')'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(357,32): error C2589: '(': illegal token on right side of '::'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(357,32): error C2062: type 'unknown-type' unexpected
SimpleXlsx\Source\Xlsx\Worksheet.cpp(357,32): error C2059: syntax error: ')'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(358,32): error C2589: '(': illegal token on right side of '::'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(358,32): error C2062: type 'unknown-type' unexpected
SimpleXlsx\Source\Xlsx\Worksheet.cpp(358,32): error C2059: syntax error: ')'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(359,31): error C2589: '(': illegal token on right side of '::'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(359,31): error C2062: type 'unknown-type' unexpected
SimpleXlsx\Source\Xlsx\Worksheet.cpp(359,31): error C2059: syntax error: ')'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(360,31): error C2589: '(': illegal token on right side of '::'
SimpleXlsx\Source\Xlsx\Worksheet.cpp(360,31): error C2062: type 'unknown-type' unexpected
SimpleXlsx\Source\Xlsx\Worksheet.cpp(360,31): error C2059: syntax error: ')'

[Alexandr Belyak]

This turned out to be a feature of Visual Studio. Please check the updated archive.

[E.Naumovich]

In case of the two-pass preparation of the sheet, usage of the memory buffer rather than file looks as more straight option.