Hi!
We have the following requirements:
An Excel-document should be filled in with calculated values on the server, and sent to the client. (ASP.NET).
The user opens the document, and make adjustments/additions to the data in the Excel-document. The user then "sends" the data back to the server application. The application validates that the filled in data is correct, and stores it in SQL. If the user has filled in invalid data (such as invalid date-time format), the user should be informed about the error, and in which cell.
We have the most things working, but there are two things we still need to solve.
First of all, how should we implement "sending" the data to the server application? Since Aspose.Cells works on workbooks, one option would be to have a VBA script in the document which "SavesAs" the document to a location on the server. Through file-notifications, the server application could be made aware of that there is a new file to process.
Secondly, once the document has been transferred to the server (or a copy of the document), the server app needs a way to signal validation errors and other messages back to the user. I guess the "SaveAs"-method in VBA doesn't support sending back any error messages except Directory not found, etc. we need to different way to do this. One way we have been thinking about is to perform a web-query to the server app, after the SaveAs operation has completed. The web-query could return the error message, and other information, such as which cell contains the error.
Although both these solutions are possible (I think), they feel a little awkward. Could anyone come back with better solutions?
Thanks!
/Fredrik
Hi Fredrik,
Thanks for your inquiry.
Well, after reading your scenario a bit, we think you should set validations to the excel file using Aspose.Cells for .NET API, see the document: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net/data-filtering-and-validation.html , so when a user inputs the data into the file, MS Excel will alert him automatically. I think it would be better to let MS Excel check the validity of the data entered.
Thank you.
That is definitely something we are already doing, and the Aspose API makes it really simple.
However, some validations cannot be performed on the client, but need to be check on the server (some validations requires business logic, calls to the database, etc).
In that scenario, validations on the client is not enough. Of course, we could do some heavy VBA macro coding, but that's something we are trying to stay away from.
/Fredrik
Hi Fredrik,
We do not check whether the data is valid according to
business logic or not. As you are sending data to the server, we think you can
protect the worksheet, so the clients can only fill some specific cells. If
they fill the data, upload the file to the server, so, then you can open it
and check the data. Also, as you are sending data to the client as well, you can send the
file back to the client. If the data is invalid, please set an alert for formatting color
or attach some info in the comment.
Thank you.
Fredrik, we do something similar in our application. We cannot send you actual code or anything, but I can give you the idea of the Excel technologies used and some samples from my demo when I was evaluating Aspose.
We let the users choose some crtieria and download a file from an asp.net page. They can later go the application to upload the file, where server validation/DB storage takes place. One of the things I do is I utilize multiple worksheets, and if the server validation fails, the application returns the spreadsheet to the user with comments embedded in the cells (to let them know where the validation failed) as well as a worksheet of errors which contain a reference to the cell that’s wrong (hyperlinked for convenience) and the exact error message of why it failed validation.
As for Aspose itself, we use Data Validation and Conditional Formatting to find and present errors.
First, there’s DataValidation which Aspose supports through a Validations collection… here’s a generic sample I had when I was testing/evaulating aspose’s features.
int dvCurrentIndex = _this.Worksheets[“DataEntry”].Validations.Add();
Validation v = _this.Worksheets[“DataEntry”].Validations[dvCurrentIndex];
v.AlertStyle = ValidationAlertType.Information;
v.Type = ValidationType.List;
v.AreaList.Add(dvRange);
v.Formula1 = “=HasStateDropdown”;
v.InCellDropDown = true;
v.ShowInput = false;
v.ShowError = false;
You can also validate for Date, Time, Decimal, Length, Whole Numbers and then the custom validation function (which I think uses Validation.Operator)
And then there’s conditional formatting:
//Set data entry conditional formats
int formatIndex = _this.Worksheets[“Sheet1”].ConditionalFormattings.Add();
CellArea formatRange = new CellArea();
formatRange.StartRow = 1;
formatRange.StartColumn = 5;
formatRange.EndRow = 65535;
formatRange.EndColumn = 5;
int uglyIndex = _this.Styles.Add();
Style ugly = _this.Styles[uglyIndex];
ugly.Name = “ugly”;
ugly.BackgroundColor = System.Drawing.Color.Maroon;
ugly.ForegroundColor = System.Drawing.Color.Magenta;
ugly.Pattern = BackgroundType.DiagonalCrosshatch;
string formula = “=IF(UPPER(SUBSTITUTE(A2," ",""))="CERTAINVALUE",0,IF(SUBSTITUTE(F2," ","")="",0,1))”;
int conditionIndex = _this.Worksheets[“Sheet1”].ConditionalFormattings[formatIndex].AddCondition(FormatConditionType.Expression, OperatorType.None, formula, String.Empty);
_this.Worksheets[“Sheet1”].ConditionalFormattings[formatIndex][conditionIndex].Style = _this.Styles[“ugly”];
_this.Worksheets[“Sheet1”].ConditionalFormattings[formatIndex].AddArea(formatRange);
You’ll have to read the docs to get you through specific problems, but maybe that’ll help with an idea.
James, thanks a lot for your input!
A few follow-up questions:
1. How does the upload/return validation errors mechanism work? In other words, how do you return a document to the user after uploading? Are you using HTML upload and, if a validation error occurs, redirect to a page which automatically downloads the error-info document?
We have been thinking about adding a small VBA script in the documents, so that the user could upload the document from within Excel, but that wouldn't work with your solution, right?
2. The validation and conditional formatting is only used on the client-side, right?
3. Is there any chance to see an example of an Excel document with validation error-info, to borrow some ideas?
Thanks again!
/Fredrik