This is a migrated thread and some comments may be shown as answers.

Always have to reassign worksheet to workbook.worksheet[0], else setting values to cells won't work

2 Answers 50 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Dexter
Top achievements
Rank 1
Dexter asked on 11 Sep 2014, 02:27 AM
Hello, I would like to enquire on a problem as per title. This is my worksheet:

const string filePath = @"C:\Users\Secure\Downloads\1211Template.xlsx";
                var webClient = new WebClient();
                webClient.OpenReadCompleted += (senders, eventArgs) =>
                {
                    var formatProvider = new XlsxFormatProvider();
                    sheet1211.Workbook = formatProvider.Import(eventArgs.Result);
                };
                webClient.OpenReadAsync(new Uri(filePath));
                sheet1211.VisibleSize = new SizeI(13, 45);
                worksheet = sheet1211.Workbook.Worksheets[0];
                worksheet.Cells[0, 0, 41, 12].SetFormat(new CellValueFormat("@"));

Apparently, if I want to set values to my cells programmatically, I have to do "worksheet = sheet1211.Workbook.Worksheets[0];" every single time within a new method, else nothing will happen, even though worksheet is no longer null.
Example:
worksheet = sheet1211.Workbook.Worksheets[0];
worksheet.Cells[1, 5].SetValue(DateTime.Parse(issueDate.SelectedDate.ToString()).ToString("ddMMyy"));
worksheet = sheet1211.Workbook.Worksheets[0];
worksheet.Cells[currentRow, columnSSN].SetValue(item.SSN);

2 Answers, 1 is accepted

Sort by
0
Dexter
Top achievements
Rank 1
answered on 11 Sep 2014, 06:15 AM
Oh and to add on, likewise for this code:
"worksheet.Cells[0, 0, 41, 12].SetFormat(new CellValueFormat("@"));"

I always have to reuse this code again and again when setting values.
0
Accepted
Anna
Telerik team
answered on 15 Sep 2014, 11:07 AM
Hello,

The reason why this does not work is that you are assigning the active worksheet to the worksheet variable before your file is loaded. Rearranging your code should help:

const string filePath = @"C:\Users\Secure\Downloads\1211Template.xlsx";
var webClient = new WebClient();
webClient.OpenReadCompleted += (senders, eventArgs) =>
{
    var formatProvider = new XlsxFormatProvider();
    sheet1211.Workbook = formatProvider.Import(eventArgs.Result);
    worksheet = sheet1211.Workbook.Worksheets[0];
    worksheet.Cells[0, 0, 41, 12].SetFormat(new CellValueFormat("@"));
};
 
webClient.OpenReadAsync(new Uri(filePath));
sheet1211.VisibleSize = new SizeI(13, 45);

This should also solve the problem with the format.

Regards,
Anna
Telerik
 
Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
 
Tags
Spreadsheet
Asked by
Dexter
Top achievements
Rank 1
Answers by
Dexter
Top achievements
Rank 1
Anna
Telerik team
Share this question
or