Excel file generated from WinForm app via template is sometimes "InVisible"

We have a WinForm application that uses VSTO to generate an Excel file from a Template.

Occasionally (frequently) the file opens (because you can use the cursor keys and the formula and cell changes) but it is invisible. Even if you save the file and open it back up, it is still invisible.

The users use Excel 2007 and pretty much all (6 - 8) of our users experience this problem.

The (temporary) fix I have given them is to choose "Arrange All". When this is done, the worksheet pops into view.

I have seen several causes and fixes for this on the web. Everything from using a graphic in the template that was in Excel 2003, but isn't in Excel 2007.

The template is pretty "simple". It has formulas, fonts and colors and that is about it.

The template (and WinForm application) is deployed to the users via Click Once.

Here is the code from the "ThisWorkbook.cs" file:

public string TemplateTableName;
public string TemplateSelectStatement;

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
    Properties.Settings.Default.myConnectionString = Registry.GetValue("myConnectionString", Properties.Settings.Default.appConnectionString).ToString();
    TemplateTableName = Registry.GetValue("TemplateTableName", string.Empty).ToString();
    TemplateSelectStatement = Registry.GetValue("TemplateSelectStatement", string.Empty).ToString();

    AppLog.ConnectionString = Properties.Settings.Default.myConnectionString;
}

Here is the code from the "Sheet1.cs" file:

private const int StartingDataRow = 4;

private int LastRow;
private int NextAvailableColumn = 18;

DataSet myDS = new DataSet();

Dictionary<string, object[,]> xlsColumnData = new Dictionary<string, object[,]>();


private void Sheet1_Startup(object sender, System.EventArgs e)
{
    try
    {
        if (Globals.ThisWorkbook.TemplateTableName == string.Empty) throw new Exception("TemplateTableName is not set in the registry.");
        if (Globals.ThisWorkbook.TemplateSelectStatement == string.Empty) throw new Exception("TemplateSelectStatement is not set in the registry.");

        Application.ScreenUpdating = false;

        if (Globals.ThisWorkbook.TemplateTableName.Length > 31)
            this.Name = Globals.ThisWorkbook.TemplateTableName.Substring(0, 31);
        else
            this.Name = Globals.ThisWorkbook.TemplateTableName;


        LoadTableData();
        LoadDataArrays();
        BindDataToColumns();
        ApplyFormulas();
        ApplyFormatting();

        this.Range["B4", missing].Select();

        Application.ScreenUpdating = true;

        AppLog.WriteEvent(DateTime.Now, Environment.UserName, Environment.MachineName, Globals.ThisWorkbook.TemplateTableName, TraceEventType.Information, "Creating customer list");

        Globals.ThisWorkbook.RemoveCustomization();
    }
    catch (Exception ex)
    {
        AppLog.Show(ex.Message, "Sheet1_Startup", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, TraceEventType.Error);
    }
    finally
    {
        Application.ScreenUpdating = true;
    }
}

private void LoadTableData()
{
    try
    {
        Application.Cursor = XlMousePointer.xlWait;

        string selectCommandText = Globals.ThisWorkbook.TemplateSelectStatement.Replace("[Bind_Type]", "dbo.GetBindingCodeDescription([Bind_Type]) AS Binding_Description");

        SqlDataAdapter da = new SqlDataAdapter(selectCommandText, Public_No_Holdings.Properties.Settings.Default.myConnectionString);
        da.SelectCommand.CommandTimeout = 60;

        if (da.SelectCommand.Connection.State != ConnectionState.Closed) da.SelectCommand.Connection.Close();
        da.Fill(this.myDS);

        LastRow = (StartingDataRow + this.myDS.Tables[0].Rows.Count) - 1;
    }
    catch开发者_如何学编程 (Exception ex)
    {
        AppLog.Show(ex.Message, "Loading Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        Application.Cursor = XlMousePointer.xlDefault;
    }
}
private void LoadDataArrays()
{
    System.Data.DataTable dt = this.myDS.Tables[0];

    // insert the data into the object[,]
    object[,] rowData;
    dt.Columns["Imprint"].ColumnName = "Publisher"; //Alias the Imprint dataset column to populate the "Publisher" xls column
    for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
    {
        rowData = new object[dt.Rows.Count, 1];

        for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
        {                   
            switch (dt.Columns[iCol].ColumnName)
            {
                case "EAN":
                    rowData[iRow, 0] = "'" + dt.Rows[iRow][iCol];
                    break;

                case "IPage_Link":
                    rowData[iRow, 0] = String.Format("=HYPERLINK(\"{0}\", \"{1}\")", dt.Rows[iRow][iCol], "iPage");
                    break;

                default:
                    rowData[iRow, 0] = dt.Rows[iRow][iCol];
                    break;
            }
        }

        xlsColumnData.Add(dt.Columns[iCol].ColumnName, rowData);
    }
}

private void BindDataToColumns()
{
    NamedRange nr;
    Range rng;

    foreach (KeyValuePair<string, object[,]> kvp in xlsColumnData)
    {
        try
        {
            if (this.Controls.Contains(kvp.Key))
            {
                nr = (NamedRange)this.Controls[kvp.Key];

                // Reduce range (remove header rows)
                rng = this.Range[this.Cells[StartingDataRow, nr.Column], this.Cells[LastRow, nr.Column]];
                rng.Value2 = kvp.Value;
            }
            else
            {
                this.Cells[StartingDataRow - 1, NextAvailableColumn].Value2 = kvp.Key;
                rng = this.Range[this.Cells[StartingDataRow, NextAvailableColumn], this.Cells[LastRow, NextAvailableColumn]];
                rng.Value2 = kvp.Value;
                NextAvailableColumn++;
            }
        }
        catch (Exception ex)
        {
            AppLog.Show(ex.Message, "BindDataToColumns - " + kvp.Key, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

}

private void ApplyFormulas()
{
    Range rng;
    int iCol;

    // Book Invoice
    iCol = this.BookInvoice.Column;
    rng = this.Range[this.Cells[StartingDataRow, iCol], this.Cells[LastRow, iCol]];
    rng.Select();
    rng.FillDown();

    // Your Cost
    iCol = this.YourCost.Column;
    rng = this.Range[this.Cells[StartingDataRow, iCol], this.Cells[LastRow, iCol]];
    rng.Select();
    rng.FillDown();
}

private void ApplyFormatting()
{
    // For some reason Hyperlink columns get reset
    this.IPage_Link.Font.Name = this.EAN.Font.Name;
    this.IPage_Link.Font.Size = this.EAN.Font.Size;

    ((Range)this.Cells[StartingDataRow, 1]).EntireRow.Select();
    Application.ActiveWindow.FreezePanes = true;
}