How to add graph to Excel file generated via TMS Flexcel?

Question

I have an xlsx file which serves as a template for TMS Flexcel. I populate it with data, it renders it in nice table. All is fine so far. Problem is when I want to add a graph.

The generated xlsx file appears to be damaged, and Excel tries to recover it when I open it. Unfortunately it fails, so the resulting file is without the graph.

Has anyone encountered it? How to work around it?

The error message I got is this

 Excel completed file level validation and repair. 
 Some parts of this workbook may have been repaired or discarded.
 Removed Part: Drawing shape.

Link to Flexcel site: http://www.tmssoftware.com/site/default.asp

score 1 · Accepted Answer · answered Aug 07 '15 at 18:56

1

What we do with some success is: 1. Add some dummy-data to the table where your actual data will be stored. 1. Create a graph from the dummy data and format it the way you want.

When the data gets exported to Excel, it will replace the dummy-data and appear in the graph.

TMS also has very limited support for xlsx files. Try using an xls file instead.

answered Aug 07 '15 at 18:56

GrizzlyManBear

647
8
16

Thanks, I will give it a try – David Votrubec Aug 09 '15 at 08:14
I will accept it as valid answer since it is possible to generate graph in .xls (and not in xlsx). My graph is a bit complicated, and the data are too dynamic. So I will instead generate graph as an image and then insert into excel. Like here http://stackoverflow.com/questions/1823884/generate-image-with-microsoft-net-chart-controls-library-without-control – David Votrubec Aug 12 '15 at 12:48

score 0 · Answer 2 · answered Sep 09 '15 at 12:17

David Voltrubec. We had a similar problem where we wanted to draw graph for 2 tables in the same sheet one below the other. Flexcel doesn't support graph drawing dynamically as it doesn't has a property to set the range for a graph. What I did was 1. Write the table in XLSX file and save it. 2. Open it using a 3rd party Library (MIT license) which uses Open XML SDK and specify the range and type of graph to be drawn.

This way I could generate graphs on dynamic tables as well.

Below is a code extract where I used SpreadSheetLight library. List<DrawExcelChart.DynamicExcelChart> is generic list to hold the startRange and GetColumnAlphabetAndNumber was to convert Column/Rows into Excel Alphanumeric Cell references.

private void DrawChartInExcelUsingSpreadSheetLight(string astrFileName, string astrFilePath, out string astrAlteredFileName)
{
    List<DrawExcelChart.DynamicExcelChart> ActualCellStartEndRanges;
    string finalFilePath = string.Empty;
    int WorkSheetsCount = 0;
    string newFilePath = string.Empty;
    double ChartYpositionStart = 0;
    double ChartYpositionEnd = 0;
    string endPointAlphabet = string.Empty;
    string endPointNumber = string.Empty;
    string startPointAlphabet = string.Empty;
    string startPointNumber = string.Empty;

    DrawExcelChart myExcelChartObject = new DrawExcelChart();
    SLChart SSLChartObject = null;
    SLDocument SSLDocumentObject = null;
    try
    {
        DecideCellStartEndRanges(out ActualCellStartEndRanges);
        finalFilePath = astrFilePath + "\\" + astrFileName;
        newFilePath = astrFilePath + "\\" + astrFileName.Replace("haschart", "");

        using (SSLDocumentObject = new SLDocument(finalFilePath))
        {
            WorkSheetsCount = SSLDocumentObject.GetSheetNames().Count;
            for (int workSheetCount = 0; workSheetCount < WorkSheetsCount; workSheetCount++)
            {
                SSLDocumentObject.SelectWorksheet(SSLDocumentObject.GetSheetNames()[workSheetCount]);

                myExcelChartObject.GetColumnAlphabetAndNumber(ActualCellStartEndRanges[workSheetCount].FirstChart.startRange, out startPointAlphabet, out startPointNumber);
                myExcelChartObject.GetColumnAlphabetAndNumber(ActualCellStartEndRanges[workSheetCount].FirstChart.endRange, out endPointAlphabet, out endPointNumber);

                ChartYpositionStart = Convert.ToInt32(endPointNumber) + 1;
                ChartYpositionEnd = Convert.ToInt32(endPointNumber) + 2;

                SSLDocumentObject.SetRowHeight(Convert.ToInt32(startPointNumber), Convert.ToInt32(endPointNumber) + 1, 15);

                SSLChartObject = SSLDocumentObject.CreateChart(ActualCellStartEndRanges[workSheetCount].FirstChart.startRange, ActualCellStartEndRanges[workSheetCount].FirstChart.endRange);
                SSLChartObject.SetChartType(SpreadsheetLight.Charts.SLLineChartType.LineWithMarkers);
                SSLChartObject.SetChartPosition(ChartYpositionStart, 1, ChartYpositionEnd, 14);
                SSLDocumentObject.SetRowHeight(Convert.ToInt32(ChartYpositionEnd), 180);
                SSLChartObject.SetChartStyle(SpreadsheetLight.Charts.SLChartStyle.Style2);
                SSLChartObject.Border.SetAutomaticColor();
                SSLChartObject.Legend.LegendPosition = DocumentFormat.OpenXml.Drawing.Charts.LegendPositionValues.Right;
                SSLDocumentObject.InsertChart(SSLChartObject);
            }
            SSLDocumentObject.SelectWorksheet(SSLDocumentObject.GetSheetNames()[0]);
            SSLDocumentObject.SaveAs(newFilePath);
        }

        astrAlteredFileName = astrFileName.Replace("haschart", "");
    }
    catch (Exception obj_Except)
    {
        astrAlteredFileName = string.Empty;
    }
    finally
    {
        ActualCellStartEndRanges = null;
        finalFilePath = string.Empty;
        WorkSheetsCount = 0;
        newFilePath = string.Empty;
        ChartYpositionStart = 0;
        ChartYpositionEnd = 0;
        endPointAlphabet = string.Empty;
        endPointNumber = string.Empty;
        startPointAlphabet = string.Empty;
        startPointNumber = string.Empty;
        myExcelChartObject = null;
        SSLChartObject = null;
        SSLDocumentObject = null;
    }
}

How to add graph to Excel file generated via TMS Flexcel?

2 Answers2