Free Support Forum - aspose.com

Is it possible to store a portion of the excel(either cell range or chart) in database as byte array?

#1

Hi,
We are evaluating your API for our project needs.

We are happy that your api covers our requirement pretty much.
Our requirement is to store a portion of the excel sheet into our database as byte[], it can be either a table(cell range) or a chart. I found one way of doing it, i.e, once i get the cell range or chart from the excel sheet, then i am converting into image by calling(.ToImage()) methods of “SheetRender” and “Chart” class objects. and then converting this BitMap object to byte[] and storing into our database.

Now i just wanted to know, are there any other ways, where i can directly store the “SheetRender” or “Chart” objects into my database after converting them to byte[].
Since both of these classes are not serializable, i am ruling out Serializing these objects and storing in our database.

Please let us know your thoughts and views on this as soon as you can.

Regards,
Prathap S V

#2

@PrathapSV,

Thanks for your query.

Well, you may simply/directly save to streams using ToImage method (of SheetRender and Chart object), please choose appropriate overloaded method which involves stream.

I am afraid, using Aspose.Cells, you may save to streams, then you have to write your own code (you may try using relevant .NET APIs) to first create a byte array from streams and then save to database/datasource for your needs.

#3

Thanks for the response, but I think you are missing the point here.

I am aware of this approach, if am not mistaken, here we will be capturing the “image in stream object” instead of capturing the image itself(earlier approach).
What we want to know is, can we capture the “SheetRender” or “Chart” objects itself and save into database.

Regards,
Prathap

#4

@PrathapSV,

I have tried different options to convert these objects to byte array like BinaryFormatter and using Marshal. None of these options works as for BinaryFormatter object shall be serializable and similarly it cannot be marshaled as an unmanaged structure; no meaningful size or offset can be computed. I am afraid that it is not possible to directly save them to byte array.

#5

Even i tried and didn’t find anything,

Thanks for the clarification, will stick with our earlier approach.

Let us know, if there are any ideas to make those classes Serializable in future.

Thanks,
Prathap

#6

@PrathapSV,

Well, Aspose.Cells classes are not serializable and might not be altered. So, you may use the devised approach to accomplish the task. For example, below is a sample code on how to serialize/deserialize workbook object:
e.g
Sample code:

    public static void TestSerialization()

    {
    Workbook book = new Workbook(“sample.xls”);
    byte[] bytesBook = SerializeWorkbook(book);
    Workbook newBook = DeSerializeWorkbook(bytesBook);
    newBook.Save(“output.xls”);
    }
    public static byte[] SerializeWorkbook(Workbook workbook)
    {
    //Create a memory stream 
    MemoryStream ms = new MemoryStream();
    //Save the workbook which contains all excel objects 
    //into memory stream
    workbook.Save(ms, SaveFormat.Excel97To2003);
    //Read bytes from memory stream
    ms.Position = 0;
    byte[] bytesWorkbook = new byte[ms.Length];
    ms.Read(bytesWorkbook, 0, bytesWorkbook.Length);
    return bytesWorkbook;
    }
    public static Workbook DeSerializeWorkbook(byte[] bytesWorkbook)
    {
    MemoryStream ms = new MemoryStream();
    ms.Write(bytesWorkbook, 0, bytesWorkbook.Length);
    Workbook workbook = new Workbook(ms);

    ms.Dispose();
    return workbook;
    }
#7

We don’t want to save the entire excel work book into our database as it seems heavy and unnecessary, as we are dealing only with the portion of the excel(cell range or chart).
So we cannot go with this approach, but to stick with our earlier approach (saving just image to database).

#8

@PrathapSV,

Sure, you may implement the scheme as per your requirement and feel free to write us back if you have any other query related to Aspose.Cells.

#9

@PrathapSV,

You can also try to copy the desired range or sheet into a new workbook and then save the new workbook to stream, then save the array got from the stream to database.

#10

We could try this option, but when i copy the desired cell range or a chart into a new workbook,does it copies all the dependancies (like formula behind the cells or any other similar dependencies)?

If yes, then could you share me the code to copy the cell range or chart into a new workbook?

#12

@PrathapSV,

Yes, Range.Copy method would copy everything in the source range of cells. See the document with example on how to copy chart to other sheet for your reference:
https://docs.aspose.com/display/cellsnet/Copy+Shapes+between+Worksheets#CopyShapesbetweenWorksheets-CopyaChartfromoneWorksheettoAnother

Also, see the document on how to copy range of cells for your reference:
https://docs.aspose.com/display/cellsnet/Copy+Range+Data+with+Style

Kindly refer to the documents with examples and write your own code since you need to copy ranges and charts from one workbook to other workbook.

#13

Hi,

I followed these approach and found an issue when i create the image back from the Deserialized Workbook object. This is happening only with 3d charts as of now. The chart is not as per the excel sheet, the blue bars will disappear.

I have replicated this issue in a sample project(“AsposeCells_SupportSample.zip”). Attached screenshots named "change inputs like this.png" and “result 3d image not coming.png” will help you to input values before generating the image(generate image) button click.

For your ease, “InvMetricsAsposeCells” class has a method called “GetImageFromWorkBookBytes()” is where we are deserializing workbook and getting the image.(Line no. 217). You can debug and check.

Please let us know, the reason and fix for this behaviour. Also please let us know, if we may face any other similar issues if we go with this approach.(Saving object as byte[] in db, instead of image as byte[]).

Regards,
Prathap S V

#14

Also In addition to the above, We found one more issue related to setting height and width of the output image.
In the same sample, if you set width and height for tabular data (when Excel Data Type: Tabular data is selected), it works fine. It crops the image as per the values specified in Desired Width and Desired Height text boxes.

But for Chart Data (when Excel Data Type: Chart data is selected), both height and width are ignored.

Please also look into this issue and provide us a fix.

Regards,
Prathap

#15

@PrathapSV,

I could not find your attachments. It is better you should simplify your sample project and make simple console demo application (it should be runnable and should not have any external dependencies on database or data sources, etc.), zip the project and attach it here (you may exclude Aspose.Cells assembly to minimize the size of the VS.NET project) to reproduce the issue, we will check it soon.

It would be much better if you could create a new thread (with runnable samples and other artifacts) for the issue as it will be easy to manage the issues precisely (one issue per thread) to consequently figure out soon.

#16

I removed the aspose cells dll. now it’s getting attached i think.
You can directly run this , it doesn’t have any dependencies.

AsposeCells_SupportSample.zip (430.8 KB)

#17

You can ignore other issue,(3d images are not proper) as it is not required for us, as we are not going with this approach.
Will be saving just image as bytes in our database.

Please focus only on this issue, may need to resize the image with keeping proportions in order to fit our report pages.

#18

@PrathapSV,

Thanks for the sample project with template file.

After an initial test, I am able to reproduce the issue (for your mentioned 3d chart(s)) by using your sample project with your template file. I found 3d charts are not rendered fine when saving chart images as byte arrays into database. I have logged a ticket with an id “CELLSNET-46638” for your issue. We need to evaluate your issue thoroughly using your sample code segments first. We will do it soon.

Once we have an update on it, we will let you know here.

#19

@PrathapSV,

Please save the chart to byte directly using chart.ToImage() method. See the sample code segment for your reference:
e.g
Sample code:

Stream chartStream = new MemoryStream();
sourceChart.ToImage(chartStream, GetImageOrPrintOptionsSettings(reportLinkedExcelObject));
reportLinkedExcelObject.ChartImageByteData = StreamToBytes(chartStream);
reportLinkedExcelObject.ChartImage = ByteToImage2(reportLinkedExcelObject.ChartImageByteData);

See the attached .cs file for your reference.
InvMetricsAsposeCells.zip (3.6 KB)

PS. We do not support Perspective effects(Right-click chart int Excel–> 3-D Rotation --> Right Angle Axes).