Create copies of a worksheet template with charts, then delete it after

Apr 15, 2013 at 12:04 PM
Setting:
  • Library: EPPlus
  • Language: C#
  • Microsoft Excel 2010
Hi guys,

I'm developing a YTD report generating application, and I'm having a problem with it.

A report template contains worksheet templates, and some of them have pre-embedded charts in it. I create copies of that worksheet template over a number of times, and then it's deleted after.

When I open the generated report, an error message appears saying that Excel found unreadable content (Removed Feature: Named range.....) and asks if I wish to recover/repair them. I click Yes, and it seems that the values in all worksheets are accurate. I save and overwrite the existing generated report as a repaired one. When I open it again, the error message doesn't show. The client insists that it would be better if the end-user won't see any error messages at all even though the values are all accurate.

I feel the error originates in the part where the template is reproduced then deleted because it has charts in it. I also opened the generated report using an archive explorer to see the xl/workbook.xml file. I saw that the copies of the worksheet template has named ranges that still has references to the deleted one.
// Get data from the database.
DataTable dtResults = Database.Read(@"SELECT [Retailer]
                                      FROM [" + Database.RETAILERS + @"]
                                      WHERE [Trade Class] = 'Department / Specialty'
                                      ORDER BY [Retailer] ASC");
if (dtResults.Rows.Count > 0)
{
    foreach (DataRow row in dtResults.Rows)
    {
        if (worker.CancellationPending)
        {
            booCancel = true;
        }
        else
        {
            intRowCounter += 1;
            // Display worksheet title in progress bar.
            worker.ReportProgress(((6 * 100) / intTotalReports), "[" + row["Retailer"].ToString() + "]");

            // Filter invalid characters in string used as worksheet name.
            string wsName = Util.formatAsExcelTabName(row["Retailer"].ToString() + " - Fragrance");
            // Copy the worksheet template with the validated name above.
            package.Workbook.Worksheets.Copy("Dept-Spec Retailer - Fragrance", wsName);
            // This is where the charts in the copied worksheet are edited.  Definition found below.
            ws = rDetail(package, "Fragrance", "Department / Specialty", row["Retailer"].ToString(), row["Retailer"].ToString().ToUpper() + " - FRAGRANCE");
            // Move the copied worksheet to the end of the workbook.
            package.Workbook.Worksheets.MoveToEnd(wsName);
        }
    }
}
// Delete the worksheet template after.
package.Workbook.Worksheets.Delete("Dept-Spec Retailer - Fragrance");

=====================================================================

protected static ExcelWorksheet rDetail(ExcelPackage p, string strMajorCategory, string strTradeClass, string strRetailer, string strSheetTitle)
{
    [...]

    string strSpacesToCenter = "                             ";
    ExcelChart chart = ((ExcelChart)ws.Drawings[0]);
    chart.Series.Chart.Locked = false;
    chart.Title.Text = strSpacesToCenter.Substring(0, strSpacesToCenter.Length - strSheetTitle.Length) + "Monthly Trend - \"" + strSheetTitle + "\"";
    chart.Series[0].Header = strSheetTitle;
    chart.Series[0].Series = "$C$89:$O$89";

    chart = ((ExcelChart)ws.Drawings[2]);
    chart.Series.Chart.Locked = false;
    chart.Title.Text = strSpacesToCenter.Substring(0, strSpacesToCenter.Length - strSheetTitle.Length) + "Monthly Blend - \"" + strSheetTitle + "\"";
    chart.Series[0].Series = "$C$93:$N$93";
    chart.Series[1].Series = "$C$94:$N$94";

    chart = ((ExcelChart)ws.Drawings[3]);
    chart.Series.Chart.Locked = false;
    chart.Title.Text = strSpacesToCenter.Substring(0, strSpacesToCenter.Length - strSheetTitle.Length) + "\"" + strSheetTitle + "\" - Last Year Share";
    chart.Series[0].Series = "$V$41:$V$" + (intRowCounter - 1).ToString();
    chart.Series[0].XSeries = "$S$41:$S$" + (intRowCounter - 1).ToString();

    chart = ((ExcelChart)ws.Drawings[1]);
    chart.Series.Chart.Locked = false;
    chart.Title.Text = strSpacesToCenter.Substring(0, strSpacesToCenter.Length - strSheetTitle.Length) + "\"" + strSheetTitle + "\" - This Year Share";
    chart.Series[0].Series = "$X$41:$X$" + (intRowCounter - 1).ToString();
    chart.Series[0].XSeries = "$T$41:$T$" + (intRowCounter - 1).ToString();

    [...]
}
I would appreciate any light shed on this matter.


Thanks,
Jan 7, 2015 at 9:57 PM
I have the exact same problem.. however I have an Image on my template worksheet, and not charts. I am using 4.0.1