Hi
We use aspose excel in an web environment. The user get a page where they can select their excel file and upload it to the server.
There it is directly loaded into memory and some calculations are done. After that the in-memory object is posted back to the user as a stream.
This did work fine with version 3.0.4.0 but doesn’t with version 3.4.0.0.
The error returned by the Aspose.Excel object is the following : Aspose.Excel can only directly open stream which supports seeking.
Why the change in functionality? And how do I fix this? It’s not very clear when/how to feed a seeking-supported stream to the object.
The reason for the upgrade is that we have data validation in those excel files, as drop down lists. With version 3.0.4.0 the validation rules are “shifted” after saving the object to a file. For example,hen we have as Formula1 =$IU$60000:$IU$60005 it changes to =$IU$59988:$IU$59993 after the save…
:-S always shifts 12 positions in rows.
Kind regards
Frederick D’hont
Hi Frederick,
Could you please post your sample code here? I will check this issue ASAP.
in the upload aspx
<upload.aspx>
if(upload.PostedFile != null && upload.PostedFile.ContentLength > 0)
{
// there is an upload file present.
uploadedFile = upload.PostedFile;
fileLength = uploadedFile.ContentLength;
byte[] dataStream = new byte[fileLength];
uploadedFile.InputStream.Read(dataStream,0,fileLength);
// load xls file as datastream
uploadParser.Load(dataStream);
</upload.aspx>
in the uploadParse object
public void Load(byte[] datastream)
{
System.IO.MemoryStream memoryFile = new MemoryStream(datastream);
memoryFile.Close();
excelFile.Open(memoryFile); //excelFile is the Aspose.Excel object. Error throws here.
}
System.IO.MemoryStream memoryFile = new MemoryStream(datastream);
memoryFile.Close();
excelFile.Open(memoryFile); //excelFile is the Aspose.Excel object. Error throws here.
Why close the memory stream before loading it? Should it be:
System.IO.MemoryStream memoryFile = new MemoryStream(datastream);
excelFile.Open(memoryFile); //excelFile is the Aspose.Excel object. Error throws here.
memoryFile.Close();
That indeed fixes the problem of loading.
Strangely enough this worked fine for the previous version. It has been a long time, but I’m sure I did close the file there for a valid reason.
Either way, it fixes the problem of the uploading but this version 3.4.0.0 makes all the rest fail.
The excel files processed and saved now cause the Excel client to crash. When Excel tries an auto repair it throws everything that has to do with list-type validation out of the excel file (excel 2003).
I can forward you the input excel and resulted output excel.
Just to make things clear, validation rules are unaltered when parsing/changing the input excel file.
The excel file which makes the client crash is zipped and attached.
Is this file your resulted output file? Could you please post your input file here? Thank you.
This is the input file.
Thank you for your help.
Kind regards
Frederick D’hontwww.finarch.com
I did a small test just out of curiosity.
I opened the input filestream in the Aspose.Excel object and immidiatly did write it away as a file without any altering.
Unforunatily it has the same effect. The Excel file cannot be opened again without the Excel file restoring the error and thus throwing all validation out of the file.
I saw some invalid validation settings in your template file. Please check the attached screenshot. How do you create it? The start date and end date should be set.
aha
First of all thanks for the fast responses. It’s truely worthy to be called “support” in any meaning of the word.
So you realy need to enter dates? even if you just want the input to be checked on validity as a date?
The result I want to have is the same as right-click a cell/Format Cells/Number/Date (formatting the cell as a date), but I could find another way to forse displaying values as a date.
Excel has the tendency that if you enter integers in a row and then suddenly enters a date, the date will be transformed to its value as an integer. And we don’t want that. We want to format the cell as a date. I could be mistaken, I hope so infact, but I can’t find any way or method in the help to force this.
The only thing that I found that came near was using the following code
Validation valid = ws.Validations[ws.Validations.Add()];
valid.Type = ValidationType.Date;
CellArea arealist;
arealist.StartColumn = Convert.ToByte(column);
arealist.EndColumn = Convert.ToByte(column);
arealist.StartRow = 12;
arealist.EndRow = 65535;
valid.AreaList.Add(arealist);
…The result I want to have is the same as right-click a cell/Format Cells/Number/Date (formatting the cell as a date), but I could not find another way to forse displaying values as a date…
And if you still want to set the validation, please set all required fields:
Validation valid = ws.Validations[ws.Validations.Add()];
valid.Operator = OperatorType.Between;
valid.Type = ValidationType.Date;
valid.Formula1 = “1900-1-1”;
valid.Formula2 = “2900-1-1”;
CellArea arealist;
arealist.StartColumn = Convert.ToByte(column);
arealist.EndColumn = Convert.ToByte(column);
arealist.StartRow = 12;
arealist.EndRow = 65535;
valid.AreaList.Add(arealist);
I noticed when I used the following code
int rowStart = 12;
int rowEnd = 59999;
Style st = excel.Styles[excel.Styles.Add()];
Range range = ws.Cells.CreateRange(rowStart,Convert.ToByte(column),rowEnd,1);st.Custom = “dd-mm-yyyy”;
range.Style = st;
that applying the style takes a lot of time and the resulted file is now 4.8Mb instead of 51 kb.
Although that using the Custom style lets you allow using custom dates. Since our customers have strong international presence, and our current environment allows changing of date formats, it would be logical to use the styling approuch. Is there a error in the way I use the style, which makes it so big?
Kind regards
Frederick D’hont www.finarch.com
I just tested the code example you provided, by setting the dates in the formules and that doesn’t force the values to be shown as dates.
The customer will still see integers.
You have been a great help until now. If you could just push me over this one I would be forever greatfull.
Kind regards
Frederick D’hont
Please try to use the following code to format a column:
Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;
cells[0, 5].PutValue(DateTime.Now);
cells.Columns[5].Style.Custom = "dd-mm-yyyy";
excel.Save("d:\\book1.xls");