1. A row has smart markers in some of the columns and has formulas defined for the other columns. If I load multiple row data using designer.Process() by setting a datatable as the datasource, how do I get the formulas/validations etc to be retained for the corresponding cell in the new rows that get created as a result.
2. I have an excel file which has smart markers defined in some of the sheets. Can I load data using the designer as well as by not using the designer (for other sheets or columns which do not have smart markers) simultaneously.
3. A cell has a validation defined to pick from a list. How do I define a smart marker for that cell
3. How do I copy a row or a cell (which has formula defined in it) and paste (or insert) it in a location of choice and also preserve the formula.
1. Please try to use "noadd" options to not pushing down your formulas and validations. If I don't understand your need correctly, please elaborate it with a sample.
2. Yes.
3. What do you want to do to define a smart marker for validation? Could you give me more detail information on this need?
4. Please try to use Cells.CopyRow method.
I am using an excel file to import budget details from a database into the sheet. A user will work on the sheet(change some values) and will export the data back into the database. macros are also written to provide some functionality
1. The first 15 columns of the first row on the sheet have smart markers defined to load data from database. The columns 4 , 7 ,9 have validations defined so that the user should be able to change the value by picking from a list. Columns 16 to 25 have formulas. Now, suppose 10 data rows are filled into the sheet, the rest 9 rows below the top row does not have the validations on columns 4, 7 ,9. How do I have the validation from the top row carried over to all the rows.
Also in one of the cell (14) I am using a function as =TEXT(F1,"ddd"). How do I make this function applied to the other rows when loading the data(...it give me an error if i say &=&=TEXT(F{r},"ddd")...).
The above described operation show work as if i am copying the first row 9 times below it and then filling the values into cell. Is it possible?
2. I have a cell on another sheet with a name range defined and also it has a smart marker. Now i need to fill in 10 values starting from the cell vertically. How does the name range extend to include all the 10 values. I am using the smart marker to fill in the values.
3. I have an array of double. How do I set the designer.SetDataSource to load the array. How do I refer to the array in the sheet.
For 1, please try this attached fix.
For 2, you can re-define a named range to replace this named range. You can use Cells.CreateRange method to create a new range and assign the range name to it.
For example, this range's name is "MyRange":
Range range = cells.CreateRange(...);
range.Name = "MyRange";
For 3, please check Smart Markers.
You can define a smart marker in your template file, such as "&=$MyArray", then in your code:
designer.SetDataSource("MyArray", doubleArray);
The new Aspose.Cells.dll you sent is giving an excel error 'Unable to read file' when I try to open the file after importing data into it. It works fine with the older version except for the problem(no.1) discussed.
Could u please verify.
Thanks
Please post your file and sample code here. I will check it ASAP.
I have attached the excel file. If I remove the first 2 and last 3 sheets and the 'Summary' sheet then it works fine.
I am not loading any data into those sheets.
Sample code:
SrcTemplate = Server.MapPath(@"..\Templates") + @"\Aspose_Designer.xls";
ExcelDesigner designer = new ExcelDesigner();
designer.Open(SrcTemplate);
//Set the data source and process the smart markers
FillCodesInDesigner(designer); //Loads data to Dates&Codes sheet
DataSet dsItems = GetItemDataForSheet("Hotl");
designer.SetDataSource(dsItems);
dsItems = GetItemDataForSheet("Food");
designer.SetDataSource(dsItems);
dsItems = GetItemDataForSheet("Air");
designer.SetDataSource(dsItems);
dsItems = GetItemDataForSheet("Othr");
designer.SetDataSource(dsItems);
designer.Process();
designer.Save(DestFile,FileFormatType.Excel2000);
I don’t find the attached file. Please attache it again.
Thanks..it works. A few more problems.
A #VALUE! is populated in the column F(Date) instead of date in all 4 sheets (Hotl, F&B, Air, Other).
The data is not displayed properly in the 'Dates&Codes' sheet. Extra cells are being inserted in some of the columns(eg: column B,H). I have name ranges defined for all the cells on row 7 and i need them to extend vertically to include the data in its list. If i use 'NOADD' the range doesn't get extended automatically as it happens in Excel.
I have attached 2 files(Input and Output)
For formula problem, please try this attached fix.
For extra rows problem, I will add a new smart marker to allow you to push down rows on one single column. It will take about 1-2 week to make this new feature.
Now you can try to use Cells.DeleteRange to remove extra cells.
Tried with the given fix. The error 'Cannot access ’ occurs.
What’s the detail error message? Have you rebuilt your program?
I get the error when I try to open the excel file and that's the only message displayed.
It works fine with older version(3.7.2.3) but the new version(3.7.2.8) creates this problem.
The problem happens on opening the template file or on opening the generated file in MS Excel? Please try this attached version.
I can open the designer template file without any problem.