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

3 posts, 1 answers
  1. Dexter
    Dexter avatar
    34 posts
    Member since:
    Dec 2012

    Posted 10 Sep 2014 Link to this post

    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. Dexter
    Dexter avatar
    34 posts
    Member since:
    Dec 2012

    Posted 11 Sep 2014 in reply to Dexter Link to this post

    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.
  3. UI for WPF is Visual Studio 2017 Ready
  4. Answer
    Anna
    Admin
    Anna avatar
    99 posts

    Posted 15 Sep 2014 Link to this post

    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.
     
Back to Top