working with existing Excel files about bookfx HOT 9 CLOSED

Thanks.

I added the example and fixed one related bug (update to 0.5.1 please).

Existing Excel worksheets can be used by BookFx in a two ways:

1. As containers of prototypes, that is named ranges of cells, which will be pasted into a new worksheet together with other boxes.
2. As whole worksheets, which will be added into the new workbook entirely, as-is.

If you call the second way as "merging", there are a few words about it in the documentation:

Also BookFx supports adding whole spreadsheets from other workbooks:

Make.Sheet("New Sheet Name", protoBook, "Prototype Sheet Name");

"Prototype Sheet Name" spreadsheet will be copied from protoBook xlsx-file and then it will be renamed to "New Sheet Name". See also other overloads of Make.Sheet.

And now there is the example.

If you call as "merging" something else, specify please.

Anyway, maybe, documentation needs to be supplemented, and you are welcome to write a note about it for Prototyping section.

Have I answered your question?

from bookfx.

Hi,
I tried the sample prototyping sample and I'm not able to save the Proto in any way.

I receive always the error

BookFx.InvalidBookException: 'The book has following errors.
ProtoBox reference «Prototype1» not found.'

Follows the 3 versions of the code I used to do the testing (none working):

    public static byte[] Test_Proto_v1(byte[] excelFile)
    {
        ProtoBox proto = Make
            .Proto(excelFile, "Prototype1")
            .Add("Slot1", "Value1")
            .Add("Slot2", Make.Row("Value2", "Value3"));
        Sheet sheet = proto.ToSheet();
        return Make.Book().Add(sheet).ToBytes();
    }

    public static byte[] Test_Proto_v2(byte[] excelFile)
    {
        return Make
            .Proto(excelFile, "Prototype1")
            .Add("Slot1", "Value1")
            .Add("Slot2", Make.Row("Value2", "Value3")).ToSheet().ToBook().ToBytes();
    }

    public static byte[] Test_Proto_v3(byte[] excelFile)
    {
        var proto = Make
            .Proto(excelFile, "Prototype1")
            .Add("Slot1", "Value1")
            .Add("Slot2", Make.Row("Value2", "Value3"));

        //var xxx = Make.Stack(proto).ToSheet();  // in error also with this code
        //var xxx = Make.Row(proto).ToSheet();  // in error also with this code
        // var xxx = Make.Col(proto).ToSheet();    // in error also with this code
        var xxx = Make.Sheet(proto);

        var book = Make.Book(xxx);
        return book.ToBytes();
    }

Furthermore, I have found no way to add some values to the sheet created with the command
Make.Sheet("New Sheet Name", protoBook, "Prototype Sheet Name");
then I think the only way to edit a pre existing sheet is using the Proto object.

from bookfx.

I receive always the error

BookFx.InvalidBookException: 'The book has following errors.
ProtoBox reference «Prototype1» not found.'

Have the excelFile a Prototype1 named range? I'm going to add an example for Make.Proto, wait a little.

Furthermore, I have found no way to add some values to the sheet created with the command
Make.Sheet("New Sheet Name", protoBook, "Prototype Sheet Name");
then I think the only way to edit a pre existing sheet is using the Proto object.

It was initial design. If you want to modify a part of "Prototype Sheet Name", add a named range on spreadsheet content and use it as a proto.

from bookfx.

@stefano77it check the S6ProtoBox.cs please.

from bookfx.

Hi, I tested your code and S6 works as you said.
I found a little bug on S5 sample, the name "Data" on "First Sheet" is not copied, but I think you resolved it with the pull request #31 (I haven't tested the library from source, however).

However, when I think about reusing an existing sheet, I'm thinking about reading a sheet and being able to add rows or columns as required.

If the sheet is not made with BooxFx, that can have no defined names, a fallback should be to take the whole sheet without requiring a preexisting name (you can add a flag to activate this behavior, or could be automatic, the choice is up to you).

Furthermore, I wrote some code to write an Excel report I needed without using a too compact functional form (I reason better with loops and variables and I think that for users without a solid functional background some not-too-functional samples could be useful). I could send you the code to evaluate the opportunity to post another sample.

And I have some questions about styles (there is Wrap but not Shrink, dates are not available in YYYY-MM-DD format etc).

To prevent opening a ton of issues, and for a more flowing discussion, isn't better to have another channel to talk about this library?
In those days I'm using the JavaScript Jint library, and the developer has a channel on Gitter.
Feel free to ignore this proposal, and if you prefer I can open an issue for each question/suggestion.

Obviously, I'm always grateful for your work

from bookfx.

you resolved it with the pull request #31

Yes it is.

However, when I think about reusing an existing sheet, I'm thinking about reading a sheet and being able to add rows or columns as required.

In BookFx reusing an existing sheet implies reusing parts of the sheet as ProtoBoxes to composing a result sheet. I think it is difficult to implement adding of rows or columns into prototype because it is unnatural for BookFx. But it is possible to add rows or columns after or before a ProtoBox.

If the sheet is not made with BooxFx, that can have no defined names, a fallback should be to take the whole sheet without requiring a preexisting name (you can add a flag to activate this behavior, or could be automatic, the choice is up to you).

Good idea. It may be possible to teach Reference struct to understand a reference to whole sheet content.

Furthermore, I wrote some code to write an Excel report I needed without using a too compact functional form (I reason better with loops and variables and I think that for users without a solid functional background some not-too-functional samples could be useful). I could send you the code to evaluate the opportunity to post another sample.

You can post the code here.

And I have some questions about styles (there is Wrap but not Shrink, dates are not available in YYYY-MM-DD format etc).

Try to Wrap(false) and Format("YYYY-MM-DD").

To prevent opening a ton of issues, and for a more flowing discussion, isn't better to have another channel to talk about this library?
In those days I'm using the JavaScript Jint library, and the developer has a channel on Gitter.
Feel free to ignore this proposal, and if you prefer I can open an issue for each question/suggestion.

I've been waiting for suggestions to create a channel on Gitter. This is the first one. :) Jint is a much more popular library and so Jint Gitter channel is necessary. Maybe someday in the future I will create a channel for BookFx.

GitHub issues are a good way for suggestions and questions. As a result of this issue BookFx became better, and are you welcome to open more issues.

from bookfx.

When I wrote:

However, when I think about reusing an existing sheet, I'm thinking about reading a sheet and being able to add rows or columns as required.

I was thinking to add rows below and columns to the right of an existing sheet, then reusing a whole sheet as a read-only box is good to me.

Try to Wrap(false)

Wrap(false) do what it says, let overflow the text. Shrink-to-fit for me must be added as another bool flag processed in the file StyleRender.cs as follows

style.IsWrap.ForEach(wrap => excelRange.Style.WrapText = wrap);
style.IsShrinkToFit.ForEach(shrink => excelRange.Style.ShrinkToFit = shrink);  // new line of code
style.Rotation.ForEach(rotation => excelRange.Style.TextRotation = rotation);

Format("YYYY-MM-DD")

it works

You can post the code here

I'll open another issue to move there the discussion about the sample

from bookfx.

BoxStyle.Shrink was added. Thanks for the tip.

Are there any more suggestions on the subject?

from bookfx.

Thank you, you are very quick and open to suggestions!

Nothing to add on this topic, closing

from bookfx.