Экспорт графика в Microsoft Excel

Доброго времени суток, уважаемые Хабровчане.

Я — начинающий разработчик SharePoint, и сегодня хотелось бы рассказать о решении одной интересной задачи. Нужно было сделать веб-часть для SharePoint, которая должна обрабатывать таблицу с данными, выводить график на экран, а так же уметь экспортировать таблицу и график в Microsoft Excel. Под катом мой способ решения, отрывки кода веб-части и ссылка на проект.

Теоретическое решение

1. Построение графика на странице.

Удобнее всего это делать с помощью MS Chart Control. Сам процесс достаточно прост (хотя, конечно, зависит от необходимого Вам вида графика). Единственное требование: первый столбец в таблице — это значения для оси X на графике.

2. Экспорт таблицы в Excel

Существует достаточно много способов экспорта, но я решил воспользоваться самым простым (на мой взгляд). Как известно, в Office 2007/2010 для файлов .docx, .xlsx, pptx используется формат Open XML, то есть эти файлы представляют из себя простой ZIP-архив с XML-файлами. Работать с ZIP-архивами просто, и таким образом можно записать данные напрямую в XML-файлы.
Однако оказалось, что записать табличку с названиями столбцов не так то просто. Excel записывает цифровые поля напрямую в sheet1.xml, а текстовые поля записывает в sharedStrings.xml, и в sheet1.xml идут только ссылки на них. К счастью, я нашел замечательный проект, который и реализует запись текстовых значений в xlsx-файл.

3. Экспорт графика в Excel

Естественно, простая вставка картинки с графиком не подойдет, это должен быть настоящий Excel'евский график. Гугл не подсказал мне ничего стоящего — либо не подходит, либо слишком сложно. Немного поработав c графиками в Excel'е, я заметил интересную особенность — если задать области данных, из которых график будет брать значения, а потом менять значение ячейки — график меняется автоматически. Идея пришла сама собой — создать пустой файл с графиком, затем записывать в него таблицу, потом так же с помощью редактирования XML указывать области данных для графика (в настоящем проекте у меня была динамическая таблица, поэтому размеры вычислялись в коде).

Практическое решение

Для примера я решил создать простую веб-часть с табличкой, двумя кнопками и графиком (изначально он скрыт). Так как нам нужно будет экспортировать названия столбцов таблицы, то необходимо вписать их первой строкой, и отключить 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;
        }

Теперь делаем вывод графика. Учитываем, что данные начинаются со 2-й строки, а 1-й столбец — это значения для оси X.

            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;

Приступим к экспорту. Первое что понадобится — шаблон (пустой файл с графиком). Создаем в Excel новую книгу, вставляем график, указываем области данных. Сохраняем, закрываем. Можно немного отредактировать напрямую через xml — удалить кэшированные значения, если таблица не фиксированного размера — то в качестве областей данных можно подставить свои числа (я так схитрил — потом просто через Replace заменяю эти числа на необходимые). Шаблон надо закинуть в корневую папку узла Sharepoint.
Используя вышеупомянутый проект, дорабатываем его под наши цели — сначала создадим копию 1й строки таблицы (названия столбцов). С помощью HashTable создадим ссылки на эти текстовые данные, а сами данные запишем в 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>");
            }
        }

Теперь преобразуем таблицу данных в xml-код, попутно вставляя ссылки на текстовые данные. Записываем все в файл sheet1.xml.

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("<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>");
            }
        }
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>");
            }
        }

Осталось не забыть изменить области данных. Так как в начально проекте у меня могло быть не более пяти столбцов — я создал шаблон с пятью столбцами, а потом удалял лишние. Кол-во строк рассчитывается просто из таблицы.

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();
        }

И вот наш файл готов! Выдаем его пользователю для сохранения/загрузки с помощью 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();
        }

Я не стал публиковать здесь весь код — он слишком большой, и весь проект (с подробными комментариями и пустым шаблоном) можно скачать здесь.

Примечания:

• Изначально проект делался для SP2007 в VS2008, и портировался на 2010 специально для Хабра. Поэтому использованы не все возможности 2010 версии.
• Код написан как пример, максимально просто, без ненужных (в данном случае) try-catch и т.д. Главное было показать идею, а не реализацию.
• Этот способ можно использовать не только с SharePoint, но это наиболее часто встречающаяся связка с Excel.
• Я только учусь, так что с удовольствием выслушаю Ваши советы и указания на мои ошибки.

Спасибо за внимание.