Graph Export to Microsoft Excel
Good day, dear Habrovchane.
I am a beginner SharePoint developer, and today I would like to tell you about solving one interesting problem. It was necessary to make a web part for SharePoint, which should process the table with data, display the graph on the screen, and also be able to export the table and graph to Microsoft Excel. Under the cut is my solution, snippets of the web part code and a link to the project.
The most convenient way to do this is using MS Chart Control . The process itself is quite simple (although, of course, it depends on the type of graphics you need). The only requirement is that the first column in the table is the values for the x axis on the graph.
There are many ways to export, but I decided to use the simplest (in my opinion). As is known, in Office 2007/2010 for .docx, .xlsx, pptx files the Open XML format is used, that is, these files are a simple ZIP archive with XML files. Working with ZIP archives is simple, and thus you can write data directly into XML files.
However, it turned out that it was not so easy to write down a table with column names. Excel writes numeric fields directly to sheet1.xml, and writes text fields to sharedStrings.xml, and only references to them are sent to sheet1.xml. Fortunately, I found a wonderful project that implements writing text values to an xlsx file.
')
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 doesn’t fit or it’s too difficult. Having worked a bit with graphs in Excel, I noticed an interesting feature - if you specify 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 chart, then write a table to it, then use XML editing to specify the data areas for the chart (in this project I had a dynamic table, so the dimensions were calculated in code).
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, it is necessary to enter them in the first line, and disable ShowHeader.
Now we make a conclusion of the schedule We take into account that the data starts from the 2nd row, and the 1st column is the values for the X axis.
We proceed to export. The first thing you need is a template (an empty file with a graph). Create a new workbook in Excel, insert a graph, specify the data areas. Save, close. You can edit a little directly through xml - delete cached values, if the table is not of a fixed size - then you can substitute your numbers as data areas (I cheated so - then I simply replace these numbers with Replace with necessary ones). The template should be thrown into the root folder of the Sharepoint node.
Using the above project , we are developing it for our purposes - first we will create a copy of the 1st row of the table (column names). With the help of HashTable we will create links to this text data, and we will write the data in sharedStrings.xml.
Now we will transform the data table into xml-code, simultaneously inserting references to text data. We write everything in the sheet1.xml file.
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.
And our file is ready! We give it to the user for saving / loading using HttpContext.Current.Response.
I did not publish all the code here - it is too big, and the entire project (with detailed comments and an empty template) can be downloaded here .
Thanks for attention.
I am a beginner SharePoint developer, and today I would like to tell you about solving one interesting problem. It was necessary to make a web part for SharePoint, which should process the table with data, display the graph on the screen, and also be able to export the table and graph to Microsoft Excel. Under the cut is my solution, snippets of the web part code and a link to the project.
Theoretical solution
1. Construction of graphics on the page.
The most convenient way to do this is using MS Chart Control . The process itself is quite simple (although, of course, it depends on the type of graphics you need). The only requirement is that the first column in the table is the values for the x axis on the graph.
2. Export spreadsheet to Excel
There are many ways to export, but I decided to use the simplest (in my opinion). As is known, in Office 2007/2010 for .docx, .xlsx, pptx files the Open XML format is used, that is, these files are a simple ZIP archive with XML files. Working with ZIP archives is simple, and thus you can write data directly into XML files.
However, it turned out that it was not so easy to write down a table with column names. Excel writes numeric fields directly to sheet1.xml, and writes text fields to sharedStrings.xml, and only references to them are sent to sheet1.xml. Fortunately, I found a wonderful project that implements writing text values to an xlsx file.
')
3. Export graphics 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 doesn’t fit or it’s too difficult. Having worked a bit with graphs in Excel, I noticed an interesting feature - if you specify 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 chart, then write a table to it, then use XML editing to specify the data areas for the chart (in this project I had a dynamic table, so the dimensions were calculated in 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, it is necessary to enter them in the first line, and disable ShowHeader.
DataTable dt = new DataTable(); protected void Page_Load(object sender, EventArgs e) { for (int i = 0; i < 4;i++ ) dt.Columns.Add(); dt.Rows.Add(new string[] { " ", "", "", "" }); dt.Rows.Add(new string[] { "1", "17", "5", "8" }); dt.Rows.Add(new string[] { "2", "18", "4", "10" }); dt.Rows.Add(new string[] { "3", "15", "6", "9" }); dt.Rows.Add(new string[] { "4", "19", "7", "10" }); dt.Rows.Add(new string[] { "5", "13", "4", "7" }); GridView1.DataSource = dt; GridView1.DataBind(); GridView1.Width = 300; GridView1.ShowHeader = false; } Now we make a conclusion of the schedule 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; We proceed to export. The first thing you need is a template (an empty file with a graph). Create a new workbook in Excel, insert a graph, specify the data areas. Save, close. You can edit a little directly through xml - delete cached values, if the table is not of a fixed size - then you can substitute your numbers as data areas (I cheated so - then I simply replace these numbers with Replace with necessary ones). The template should be thrown into the root folder of the Sharepoint node.
Using the above project , we are developing it for our purposes - first we will create a copy of the 1st row of the table (column names). With the help of HashTable we will create links to this text data, and we will write the data in sharedStrings.xml.
public static 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; } public static void WriteStringTable(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 references to text data. We write everything in the sheet1.xml file.
public static void WriteWorksheetData(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("<cr=\"" + 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("<cr=\"" + relPos + "\">"); string val = dt.Rows[row][column].ToString(); writer.WriteRaw("<v>" + val + "</v>"); writer.WriteRaw("</c>"); } writer.WriteRaw("</row>"); } } public static void WriteWorksheet(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.
public static void FieldEdit(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 our file is ready! We give it to the user for saving / loading using HttpContext.Current.Response.
public static void SendContent(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 all the code here - it is too big, and the entire project (with detailed comments and an empty template) can be downloaded here .
Notes:
- Initially, the project was made for SP2007 in VS2008, and ported to 2010 specifically for Habr. Therefore, not all 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 combination with Excel.
- I'm just learning, so I’m happy to hear your advice and indications of my mistakes.
Thanks for attention.
Source: https://habr.com/ru/post/124079/
All Articles