Problem manipulating Workshet that has been created by designer

Aspose.Cells Product Family
1

To Whom It May Concern:

here is the workflow
Designer designer = new Designer();
…
designer.Excel.open(“template.xls”);
Excel.Worksheets[0].cells[0,0].putValue(“Header”);
Excel.Worksheets[0].cells[1,0].putValue("&=[TableName].fieldName");
designer.setDataSource(table);
designer.process();

// up to this point, everything worked fine
// but after I’ve added the following code, I encountered a problem:
// for some reason,
// if the first row below header has special value, the line
// highlighted in bold does not work correctly. Instead of putting
// “anotehr_special_value” in the cell, it puts the text “&=[TableName].fieldName”
// in the cell.
// if the “special_value” is in some other cell, then it’s being correctly replaced
// with "another_special_value"

for(i=0;i<table.Rows.Count;i++)
if (Excel.Worksheets[0].cells[1+i,0].Equals(“special_value”))
Excel.Worksheets[0].cells[1+i,0].putValue(“another_special_value”);

Thanks!
Sincerely,
Igor

2

Hi Igor,

Could you please post your template file here? And you and download and try v3.1.1 at

3
Laurence wrote:
Hi Igor,

Could you please post your template file here? And you and download and try v3.1.1 at


Template is just an Excel file with blank worksheet.

We planning a release soon, and it would be best if we did not have to upgrade Aspose.Excel right now. Is there any workaround, that would not require an upgrade?
4

Which version are you using? This is a bug in the earlier version.

The following is my test code and all work fine.

ExcelDesigner designer = new ExcelDesigner();

designer.Excel.Open("d:\\template.xls");
Excel excel = designer.Excel;
excel.Worksheets[0].Cells[0,0].PutValue("Header");
excel.Worksheets[0].Cells[1,0].PutValue("&=[TableName].fieldName");

DataTable dt = new DataTable();
dt.TableName = "TableName";
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("fieldName", typeof(string));

DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = DateTime.Now;
dr[2] = "Hello world";

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 2;
dr[1] = DateTime.FromOADate(12345);
dr[2] = "Test";

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 3;
dr[1] = DateTime.FromOADate(2222.34);
dr[2] = "Laurence";

dt.Rows.Add(dr);

designer.SetDataSource(dt);
designer.Process();

for(int i=0;i<dt.Rows.Count;i++)
if (excel.Worksheets[0].Cells[1+i,0].StringValue.Equals("Laurence"))
excel.Worksheets[0].Cells[1+i,0].PutValue("another_special_value");

excel.Save("book1.xls", SaveType.OpenInExcel, FileFormatType.Default, this.Response);

Please try my test code. If it doesn't work in your place, please try a upgrade.

5

The problem is that it does not happen all the time. Sometimes the values replaced correctly, sometimes they are not. But the incorrect replacement is only happens with the first row, the one that has a smart marker in it.
the values are numberic. -1 supposed to be replaced with NM.

6

It must be a bug in the earlier version. Have you tested your program with v3.1.1?

7
Laurence wrote:
It must be a bug in the earlier version. Have you tested your program with v3.1.1?


I've upgraded to 3.1.1 and applied hotfix. This seemed to have solved the problem.
Thank you!

But now there is another problem,

for some reason, auto fit of row 0 makes it about 10 times higher then the height of the text in that row.
No Special formating has been applied to this cell in the designer worksheet.
I've tried to call AutoFitRow before designer.process(), but the result was the same

Thanks,
Igor
-------------------
private void importData(Worksheet w,DataGridColumnCollection columns,DataTable dataTable,int startRow, string title)
{
if (title != null)
{
w.Cells[0,0].Style.Font.IsBold=true;
w.Cells[0,0].Style.Font.Size=14;
w.Cells[0,0].PutValue(title);
// w.AutoFitRow(0);
// I've tried to call AutoFitRow before designer.process(), but the result was the same
}

int actualNumberOfColumns = col;

put smart markers in the cells

designer.SetDataSource(dataTable);

designer.Process();

if (title != null)
w.AutoFitRow(0);

replace special values

for (col=0;col<actualNumberOfColumns;col++)
w.AutoFitColumn((byte)col,startRow,startRow+dataTable.Rows.Count+1);

}

8

Could you please upload your template here? If you don’t want to make it public, please remove all contents except row 0.

Is “wrap text” property in cell A1 set to true in your template?

9
Laurence wrote:
Could you please upload your template here? If you don't want to make it public, please remove all contents except row 0.

Is "wrap text" property in cell A1 set to true in your template?


The worksheet in the template is blank. When creating the template I've just added a new worksheet and renamed it. I did not change contents/formating in that worksheet.

Attached workbook has two worksheets. The worksheet called "Search results" is the one I use.
10

PS
moving the code

if (title != null)
{
w.Cells[0,0].Style.Font.IsBold=true;
w.Cells[0,0].Style.Font.Size=14;
w.Cells[0,0].PutValue(title);
w.AutoFitRow(0);
}

to the end of the function(i.e. after the call to designer.process() ) solved the problem. But I am still curious what have caused the problem.

11

The following is my test code and it works fine.


ExcelDesigner designer = new ExcelDesigner();
designer.Open("d:\\test\\download.xls");
Worksheet w = designer.Excel.Worksheets[1];


w.Cells[0,0].Style.Font.IsBold=true;
w.Cells[0,0].Style.Font.Size=14;
w.Cells[0,0].PutValue("Header");
w.AutoFitRow(0);

w.Cells[1,0].PutValue("&=[TableName].fieldName");

DataTable dt = new DataTable();
dt.TableName = "TableName";
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("fieldName", typeof(string));

DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = DateTime.Now;
dr[2] = "Hello world";

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 2;
dr[1] = DateTime.FromOADate(12345);
dr[2] = "Test";

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 3;
dr[1] = DateTime.FromOADate(2222.34);
dr[2] = "Laurence";

dt.Rows.Add(dr);

designer.SetDataSource(dt);
designer.Process();

for(int i=0;i<dt.Rows.Count;i++)
if (w.Cells[1+i,0].StringValue.Equals("Laurence"))
w.Cells[1+i,0].PutValue("another_special_value");

designer.Save("d:\\test\\download1.xls");

Does it work in your machine? Is there any difference with your program? Could you please post your file with the extra row heigh? Thank you.