none
Edit Embedded Chart in Worksheet Template

    Question

  • Setting:

    • Language - C#
    • Library - OfficeOpenXml (DocumentFormat.OpenXML)
    • Microsoft Office 2010

    Hi guys,

    I'm developing a project that deals with YTD reports.  The application uses workbook templates that are to be edited programmatically.  So, basically, one workbook template for one kind of report.  

    In a workbook template, there is a worksheet template that has pre-embedded charts in it.  This worksheet template is to be copied over a number of times, and then deleted so that the client won't see it in the generated report.

    I open the generated report, and Excel 2010 tells me that the file has unreadable content and asks me if I wish to have them recovered on the fly.  I click Yes.  It pops out a small window that says "Excel was able to open the file by repairing or removing the unreadable content.  Removed Feature: Named range from /xl/workbook.xml part (Workbook)", and I close it.  The values in the generated report seems accurate and I think it's not affected by the error message shown earlier.  I save and overwrite the file, and when I open it again, the error message does not show up because it is saved as a repaired file now.

    I tried commenting out the part that copies the worksheet template over a number of times then deletes it, and opening the generated report doesn't show up an error message.  I asked the client if that would be okay since the values are not affected, but they insist it would be much better if the end-user sees no error message at all.

    Kindly assess the code below:

    // 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 very much appreciate any light shed on this matter.  Frustrations are running high.  Haha.

    Thanks,

    • Moved by Mike Feng Wednesday, April 17, 2013 11:39 AM
    Saturday, April 13, 2013 5:17 PM

All replies

  • Hello,

    A better place for support would be under Open XML Format forum here on MSDN.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, April 14, 2013 12:01 AM
  • Oh, i'm sorry.  Thanks for the info.
    Monday, April 15, 2013 11:44 AM
  • No need to be sorry :-) Just pointing you in a better pathway.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, April 15, 2013 12:08 PM