Export Graphics to Microsoft Excel

Good day, dear Khabrovchane.

I am a beginner SharePoint developer, and today I would like to talk about solving an interesting problem. It was necessary to create a web part for SharePoint, which should process a table with data, display a graph on the screen, and also be able to export the table and graph to Microsoft Excel. Under cat, my solution, snippets of web part code and a link to the project.

Theoretical solution

1. Graphing on the page.

This is most conveniently done using MS Chart Control . The process itself is quite simple (although, of course, depends on the type of schedule you need). The only requirement: the first column in the table is the values ​​for the X axis in the graph.

2. Export a table to Excel

There are quite a few export methods, but I decided to use the simplest one (in my opinion). As you know, Office 2007/2010 uses the Open XML format for .docx, .xlsx, pptx files, that is, these files are a simple ZIP archive with XML files. Working with ZIP archives is simple, and in this way you can write data directly to XML files.
However, it turned out that writing a label with column names was not so simple. Excel writes digital fields directly to sheet1.xml, and writes text fields to sharedStrings.xml, and only links to them go to sheet1.xml. Fortunately, I found a wonderful project that implements writing text values ​​to an xlsx file.

3. Exporting a graph to Excel

Naturally, simply inserting a picture with a chart will not work, it should be a real Excel chart. Google did not tell me anything worthwhile - either it does not fit, or it is too difficult. Having worked a little with the graphs in Excel, I noticed an interesting feature - if you set data areas from which the graph will take values, and then change the cell value, the graph changes automatically. The idea came by itself - to create an empty file with a graph, then write a table to it, then also specify data areas for the graph using XML editing (in this project I had a dynamic table, so the sizes were calculated in the code).

Practical solution

For example, I decided to create a simple web part with a tablet, two buttons and a graph (it is initially hidden). Since we will need to export the names of the table columns, we need to enter them in the first row, and disable ShowHeader.
DataTable dt = new DataTable();
protectedvoidPage_Load(object sender, EventArgs e)
        {
            for (int i = 0; i < 4;i++ )
                dt.Columns.Add();
            dt.Rows.Add(newstring[] { "Номер недели", "Печеньки", "Чай", "Сахар" });
            dt.Rows.Add(newstring[] { "1", "17", "5", "8" });
            dt.Rows.Add(newstring[] { "2", "18", "4", "10" });
            dt.Rows.Add(newstring[] { "3", "15", "6", "9" });
            dt.Rows.Add(newstring[] { "4", "19", "7", "10" });
            dt.Rows.Add(newstring[] { "5", "13", "4", "7" });
            GridView1.DataSource = dt;
            GridView1.DataBind();
            GridView1.Width = 300;
            GridView1.ShowHeader = false;
        }

Now we draw the graph. We take into account that the data starts from the 2nd row, and the 1st column is the values ​​for the X axis.
            Series[] series = new Series[dt.Columns.Count - 1];
            for (int i = 0; i < series.Length; i++)
            {
                series[i] = new Series(dt.Columns[i + 1].ColumnName);
                series[i].ChartType = SeriesChartType.Column;
                for (int k = 1; k < dt.Rows.Count; k++)
                    series[i].Points.AddXY((double.Parse((string)dt.Rows[k][0])), double.Parse((string)dt.Rows[k][i + 1]));
                Chart1.Series.Add(series[i]);
            }
            ChartArea chartArea = new ChartArea();
            chartArea.AxisX.Minimum = double.Parse(dt.Rows[1][0].ToString());
            chartArea.AxisX.Maximum = double.Parse(dt.Rows[dt.Rows.Count - 1][0].ToString());
            Chart1.ChartAreas.Add(chartArea);
            Chart1.Width = 300;
            Chart1.Visible = true;

Let's start exporting. The first thing you need is a template (an empty file with a chart). We create a new workbook in Excel, insert the graph, indicate the data areas. Save, close. You can edit it a bit directly via xml - delete cached values, if the table is not a fixed size - then you can substitute your numbers as data areas (I cheated so - then I simply replace these numbers with Replace with the necessary ones). The template must be uploaded to the root folder of the Sharepoint node.
Using the aforementioned project , we are finalizing it for our purposes - first we will create a copy of the 1st row of the table (column names). Using a HashTable, create links to this text data, and write the data itself to sharedStrings.xml.
publicstatic ArrayList CreateStringTables(DataTable data, out Hashtable lookupTable)
        {
            ArrayList stringTable = new ArrayList();
            lookupTable = new Hashtable();
            foreach (DataRow row in data.Rows)
                foreach (DataColumn column in data.Columns)
                    if (column.DataType == typeof(string))
                    {
                        string val = (string)row[column];
                        if (!lookupTable.Contains(val))
                        {
                            lookupTable.Add(val, stringTable.Count);
                            stringTable.Add(val);
                        }
                    }
            return stringTable;
        }
        publicstaticvoidWriteStringTable(Stream output, ArrayList stringTable)
        {
            using (XmlTextWriter writer = new XmlTextWriter(output, Encoding.UTF8))
            {
                writer.WriteStartDocument(true);
                writer.WriteRaw("<sst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" count=\"" + stringTable.Count.ToString() + "\" uniqueCount=\"" + stringTable.Count.ToString() + "\">");
                foreach (string str in stringTable)
                {
                    writer.WriteRaw("<si><t>" + str + "</t></si>");
                }
                writer.WriteRaw("</sst>");
            }
        }

Now we will transform the data table into xml-code, simultaneously inserting links to text data. We write everything to the sheet1.xml file.
publicstaticvoidWriteWorksheetData(XmlTextWriter writer, DataTable dt, Hashtable lookupTable)
        {
            int rowsCount = dt.Rows.Count;
            int columnsCount = dt.Columns.Count;
            string relPos = RowIndexToName(0);
            writer.WriteRaw("<row r=\"" + relPos + "\" spans=\"1:" + columnsCount.ToString()+"\">");
            for (int column = 0; column < columnsCount; column++)
            {
                relPos = RowColumnToPosition(0, column);
                writer.WriteRaw("<c r=\"" + relPos + "\" t=\"s\">");
                string val = lookupTable[dt.Rows[0][column]].ToString();
                writer.WriteRaw("<v>" + val + "</v>");
                writer.WriteRaw("</c>");
            }
            writer.WriteRaw("</row>");
            for (int row = 1; row < rowsCount; row++)
            {
                relPos = RowIndexToName(row);
                writer.WriteRaw("<row r=\"" + relPos + "\" spans=\"1:" + columnsCount.ToString() + "\">");
                for (int column = 0; column < columnsCount; column++)
                {
                    relPos = RowColumnToPosition(row, column);
                    writer.WriteRaw("<c r=\"" + relPos + "\">");
                    string val = dt.Rows[row][column].ToString();
                    writer.WriteRaw("<v>" + val + "</v>");
                    writer.WriteRaw("</c>");
                }
                writer.WriteRaw("</row>");
            }
        }
publicstaticvoidWriteWorksheet(Stream output, DataTable dt, Hashtable lookupTable)
        {
            using (XmlTextWriter writer = new XmlTextWriter(output, Encoding.UTF8))
            {
                writer.WriteStartDocument(true);
                writer.WriteRaw("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">");
                string lastCell = RowColumnToPosition(dt.Rows.Count - 1, dt.Columns.Count - 1);
                writer.WriteRaw("<dimension ref=\"A1:" + lastCell + "\" />");
                writer.WriteRaw("<sheetViews>");
                writer.WriteRaw("<sheetView tabSelected=\"1\" workbookViewId=\"0\" />");
                writer.WriteRaw("</sheetViews>");
                writer.WriteRaw("<sheetFormatPr defaultRowHeight=\"15\" />");
                writer.WriteRaw("<sheetData>");
                WriteWorksheetData(writer, dt, lookupTable);
                writer.WriteRaw("</sheetData>");
                writer.WriteRaw("<pageMargins left=\"0.7\" right=\"0.7\" top=\"0.75\" bottom=\"0.75\" header=\"0.3\" footer=\"0.3\" />");
                writer.WriteRaw("<drawing r:id=\"rId1\" />");
                writer.WriteRaw("</worksheet>");
            }
        }

It remains to remember to change the data area. Since in the initial project I could have no more than five columns - I created a template with five columns, and then deleted the extra ones. The number of rows is calculated simply from the table.
publicstaticvoidFieldEdit(Stream xmlFile, int rowsCount, int columnsCount)
        {
            XmlDocument document = new XmlDocument();
            document.Load(xmlFile);
            XmlNodeList xmlColumns = document.GetElementsByTagName("c:ser");
            XmlNode xmlChart = xmlColumns[0].ParentNode;
            for (int i = xmlColumns.Count - 1; i > columnsCount - 2; i--)
                xmlColumns[i].ParentNode.RemoveChild(xmlColumns[i]);
            XmlNodeList xmlRows = document.GetElementsByTagName("c:f");
            for (int i = 0; i < xmlRows.Count; i++)
                xmlRows[i].InnerText = xmlRows[i].InnerText.Replace("15", rowsCount.ToString());
            MemoryStream ms = new MemoryStream();
            document.Save(ms);
            xmlFile.SetLength(ms.Length);
            xmlFile.Position = 0;
            document.Save(xmlFile);
            ms.Close();
        }

And now our file is ready! We give it to the user for saving / loading using HttpContext.Current.Response.
publicstaticvoidSendContent(byte[] fileContent, string outFileName)
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.AddHeader("Content-Type", "application/force-download");
            HttpContext.Current.Response.AddHeader("Content-Disposition", String.Format("attachment; filename={0}", outFileName.Replace(" ", "_")));
            HttpContext.Current.Response.AddHeader("Content-Length", fileContent.Length.ToString());
            HttpContext.Current.Response.OutputStream.Write(fileContent, 0, fileContent.Length);
            HttpContext.Current.Response.OutputStream.Flush();
            HttpContext.Current.Response.OutputStream.Close();
            HttpContext.Current.Response.Flush();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

I did not publish the whole code here - it is too large, and the whole project (with detailed comments and an empty template) can be downloaded here .

Notes:

  • Initially, the project was made for SP2007 in VS2008, and was ported to 2010 specifically for Habr. Therefore, not all the features of the 2010 version are used.
  • The code is written as an example, as simple as possible, without unnecessary (in this case) try-catch, etc. The main thing was to show the idea, not the implementation.
  • This method can be used not only with SharePoint, but it is the most common bundle with Excel.
  • I am just studying, so I will be happy to hear your advice and guidance on my mistakes.

Thanks for attention.

Also popular now: