Suggestions

[Vladimir_Chumak]

Hello

I have some problems with your FlexXel component.

1.

We design etl tools and have to work with very large Excel files.

xlsx file it is just a zip file.
sheets are stored as xml files in xl\worksheets folder

I do not understand why it is necessary to load entire excel file into memory just to get  the list of sheets?

2.

We work with one sheet at the time.
We are not interested in formulas, formats, pictures, shapes etc.

We do not want to recalculate excel file

We just want to get the data stored in one sheet as the user see it when it open the file

Is there any way to load just one sheet instead of all?

It will be faster and it will require less memory.
May be it will be possible to stream it?

3.
I was not able to find conversion functions from R1C1 format col row format

Apart from that your component is great.

Mike,
www,el-tools.com

[adrian]

Hi,

We do provide a "Virtual Mode" that allows you to read the values as the file is opened (for both xls and xlsx). I am not sure if you are in .net or delphi: If it is .net, you can look at the "22.Virtual Mode" demo. If in delphi, virtual mode is not completely finished yet, but it will in the coming weeks.

Sadly we can't provide a simpler component that doesn't deal for example with formulas, since it would mean 2 components, and really normally FlexCel is fast enough even if it is doing more work. So is Excel. You can turn off calculation if you wish with

XlsFile.RecalcMode

Same happens wihtt he list of sheets, we could indeed get the list of sheets (both in xls and xlsx) without loading the full file, but in 99.99% of the cases, this would have no use since the user needs to open the file anyway. We don't want to make the codebase more complex than what it currently is by adding corner cases. What we will be adding in the near future is a "virtual mode" for writing, currently we only support reading. 

About R1C1, we provide full support for it, but there are many places you can look:

1) If you want the generated xls/xlsx file to be in R1C1, then you can change

xls.OptionsR1C1

This will change how the spreadsheet looks when you open it in Excel, but you will still need to enter formulas as A1

To change how you enter formulas in FlexCel (but not how the spreadsheet opens when reading it), you can use:

xls.FormulaReferenceStyle

From the Help in FormulaReferenceStyle:

  /// <summary>
    /// Specifies which reference style to use when entering formulas: A1 or R1C1. Note that this property is different from
    /// <see cref="OptionsR1C1"/>. OptionsR1C1 modifies a property of the file, that handles how references will show in Excel.
    /// <br></br> This property modifies how FlexCel parses or returns the formulas, and has no effect at all in the file generated.
    /// <br></br><br></br>Also note that R1C1 and A1 modes are completely equivalent, and formulas are <b>always stored as A1</b> inside the
    /// generated files. This property only affects the parsing of the formulas, the file generated will be exactly the same no
    /// matter the value of this property. And Excel will show it in A1 or R1C1 mode depsnding only in <see cref="OptionsR1C1"/>
    /// </summary>

 And the help in OptionsR1C1:

	/// <summary>
	/// Use this property to change the reference system used in the file. Note that this option <b>only changes how Excel and
    /// FlexCel will display the file.</b> Internally, the formulas will always be stored in A1 format, and converted by Excel
    /// to and from R1C1 if this property is true. FlexCel will also use this property to render the file when it is set to print formulas.
    /// <br></br><br></br>Also, this property doesn't change how FlexCel will parse or return the formula text in the cells or names. 
    /// By default, even if this property is true, you will need to enter the formulas in FlexCel in A1 mode. To change the
    /// entry mode in FlexCel, please use <see cref="FormulaReferenceStyle"/>
	/// </summary>

Besides those 2 properties, to convert cell references you can use the TCellAddress class
You can set its CellRefR1C1 and read its CellRef property or viceversa, to convert between them. You can also set the cellref and read the row and column, or set the row and column and read the cellref

[Vladimir_Chumak]

OK Thank for the explanation

We use delphi and there is no "22.Virtual Mode"

You probably have not ported it yet

Mike

[adrian]

Yes, virtual mode is not yet ported in delphi, but it will be in the coming weeks. Everything is working here, it just needs a little more testing.

[Vladimir_Chumak]

Can we do our own testing?

Can you email this particular sample to us please?

Mike

[adrian]

Sorry, not right now, you would need also our internal version 0.6 of FlexCel that supports virtual mode. 

0.6 should be release next week, so I think the best right now is just to wait, creating an interim 0.6 would just delay the release of the official version.