ASP.NET MVC 3: Экспорт в Excel или что такое XMLExport?
ru-RU | создано: 18.08.2011 | опубликовано: 18.08.2011 | обновлено: 02.01.2018 | просмотров за всё время: 27016
При работе над очередным проектом, потребовалось реализовать экспорт данных в MS Excel. Решил поделиться информацией о том, как это можно сделать без особых усилий. Сборка создана согласно спецификации XML Spreadsheet Reference.
Тестовый проект “MvcExportToXML”
Я создал простой тестовый проект на ASP.NET MVC 3. Установил пакет с SampleData и на главной странице вывел список Person и добавил ссылку “Export to Excel”, вот эта ссылка и будет отправной точкой для моей статьи. Сборка создана согласно спецификации XML Spreadsheet Reference.
Это код контроллера (Controller):
public ActionResult Index()
{
ViewBag.Message = "Welcome to ASP.NET MVC!";
List<Person> people = People.GetPeople();
return View(people);
}
Это код представления (View)
@model IEnumerable<SampleData.Person>
@{
ViewBag.Title = "Home Page";
}
<h2>@ViewBag.Message</h2>
<p>
To learn more about ASP.NET MVC visit <a href=http://asp.net/mvc title="ASP.NET MVC Website">
http://asp.net/mvc</a>.
</p>
<p>@Html.ActionLink("Export to XML", "Export")</p>
<table>
<tr>
<th>
Name
</th>
<th>
Age
</th>
<th>
Gender
</th>
<th>
Weight
</th>
<th>
Country
</th>
</tr>
@foreach (SampleData.Person item in Model)
{
<tr>
<td>@item.Name</td>
<td>@item.Age</td>
<td>@item.Gender</td>
<td>@item.Weight</td>
<td>@item.Country</td>
</tr>
}
</table>
Это результат:
Начнем с Nuget-пакета
Устанавливаем экспортный Nuget-пакет. Да, я в очередной раз из простой сборки сделал Nuget-пакет, потому что считаю, что это очень удобно (да и людям приятно). Для установки пакета XmlExport вводим в консоли менеджера пакетов:
PM> Install-Package XmlExport Successfully installed 'XmlExport 0.1.1'. Successfully added 'XmlExport 0.1.1' to MvcExportToXML.
Готово! пакет установлен. Теперь перейдем к выгрузке отображаемых данных на странице в XML. Это не просто XML, этот XML с легкостью понимает MS Excel, а значит, данные можно будет увидеть в Excel-таблице.
Создадим новый метод в контроллере Home:
public ActionResult Export()
{
}
Добавляем namespace
using Calabonga.Xml.Exports;
Теперь надо сделать новый ExcelResult, унаследовав его от ActionResult, чтобы нажатие на кнопку экспорта пользователю предлагался сгенерированный файл для сохранения. Мой ExcelResult выглядит так:
/// <summary>
/// Выдает пользователю для загрузки файл Excel.
/// </summary>
public class ExcelResult : ActionResult
{
/// <summary>
/// Создает экземпляр класса, которые выдает файл Excel
/// </summary>
/// <param name="fileName">наименование файла для экспорта</param>
/// <param name="report">готовый набор данные для экпорта</param>
public ExcelResult(string fileName, string report)
{
this.Filename = fileName;
this.Report = report;
}
public string Report { get; private set; }
public string Filename { get; private set; }
public override void ExecuteResult(ControllerContext context)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.BufferOutput = true;
HttpContext.Current.Response.AddHeader("content-disposition",
string.Format("attachment; filename={0}", Filename));
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.Write(Report);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
Надо в методе Export указать возращаемый тип ExcelResult и сгенерировать данные для экспорта. Не буду расписывать “что и почем”, а просто приведу немного кода:
string result = string.Empty;
Workbook wb = new Workbook();
// properties
wb.Properties.Author = "Calabonga";
wb.Properties.Created = DateTime.Today;
wb.Properties.LastAutor = "Calabonga";
wb.Properties.Version = "14";
// options sheets
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
wb.ExcelWorkbook.WindowHeight = 800;
wb.ExcelWorkbook.WindowTopX = 0;
wb.ExcelWorkbook.WindowTopY = 0;
wb.ExcelWorkbook.WindowWidth = 600;
// create style s1 for header
Style s1 = new Style("s1");
s1.Font.Bold = true;
s1.Font.Italic = true;
s1.Font.Color = "#FF0000";
wb.AddStyle(s1);
// create style s2 for header
Style s2 = new Style("s2");
s2.Font.Bold = true;
s2.Font.Italic = true;
s2.Font.Size = 12;
s2.Borders.Add(new Border());
s2.Font.Color = "#0000FF";
wb.AddStyle(s2);
// First sheet
Worksheet ws = new Worksheet("Лист 1");
// adding headers
ws.AddCell(0, 0, "qwerty1", s1.UniqueName);
ws.AddCell(0, 1, "qwerty2", s1.UniqueName);
ws.AddCell(0, 2, "qwerty3", s1.UniqueName);
// adding row1
ws.AddCell(1, 0, 1);
ws.AddCell(1, 1, 2);
ws.AddCell(1, 2, 3);
// adding row2
ws.AddCell(2, 0, 4);
ws.AddCell(2, 1, 5);
ws.AddCell(2, 2, 6);
wb.AddWorksheet(ws);
// Second sheet
Worksheet ws2 = new Worksheet("Лист 2");
ws2.AddCell(0, 0, 1);
ws2.AddCell(1, 0, 2);
ws2.AddCell(2, 0, 3);
ws2.AddCell(3, 0, 4);
ws2.AddCell(4, 0, 5);
ws2.AddCell(5, 0, 6);
ws2.AddCell(6, 0, 7);
ws2.AddCell(7, 0, 8);
ws2.AddCell(8, 0, 9);
ws2.AddCell(9, 0, 10);
ws2.AddCell(10, 0, 11);
ws2.AddCell(11, 0, 12);
ws2.AddCell(0, 1, 13);
ws2.AddCell(1, 1, 14);
ws2.AddCell(2, 1, 15);
ws2.AddCell(3, 1, 16);
ws2.AddCell(4, 1, 17);
ws2.AddCell(5, 1, 18);
ws2.AddCell(6, 1, 19);
ws2.AddCell(7, 1, 20);
ws2.AddCell(8, 1, 21);
ws2.AddCell(9, 1, 22);
ws2.AddCell(10, 1, 23);
ws2.AddCell(11, 1, 24);
wb.AddWorksheet(ws2);
// Third sheet
Worksheet ws3 = new Worksheet("Пользователи");
// Adding Headers
ws3.AddCell(0, 0, "Наименование (Name)", s2.UniqueName);
ws3.AddCell(0, 1, "Возраст (Age)", s2.UniqueName);
ws3.AddCell(0, 2, "Пол (Gender)", s2.UniqueName);
ws3.AddCell(0, 3, "Вес (Weight)", s2.UniqueName);
ws3.AddCell(0, 4, "Страна (Country)", s2.UniqueName);
// get data
List<Person> people = People.GetPeople();
int totalRows = 0;
// appending rows with data
for (int i = 0; i < people.Count; i++)
{
ws3.AddCell(i + 1, 0, people[i].Name);
ws3.AddCell(i + 1, 1, people[i].Age);
ws3.AddCell(i + 1, 2, people[i].Gender);
ws3.AddCell(i + 1, 3, people[i].Weight);
ws3.AddCell(i + 1, 4, people[i].Country);
totalRows++;
}
totalRows++;
// appending footer with formulas
ws3.AddCell(totalRows, 0, string.Empty);
ws3.AddCell(totalRows, 1, 0, "=AVERAGE(R[-" + (totalRows - 1) + "]C:R[-1]C)", "");
ws3.AddCell(totalRows, 2, string.Empty);
ws3.AddCell(totalRows, 3, 0, "=SUM(R[-" + (totalRows - 1) + "]C:R[-1]C)", "");
ws3.AddCell(totalRows, 4, string.Empty);
wb.AddWorksheet(ws3);
// generate xml
string workstring = wb.ExportToXML();
// Send to user file
return new ExcelResult("Persons.xls", workstring);
Многовато получилось, но зато всё понятно. Правда? Особенно если учесть наличие комментариев. Единственное что осталось сделать, так это привести скриншоты каждого из трёх листов сформированной книги Person.xls.
Первый лист:
Второй лист:
И, наконец, то ради чего…
Обратите внимание, что формулы не просчитаны потому что книга показана в режиме “Защищенный просмотр”.
Но если нажать кнопку “Разрешить редактирование”, то сразу же увидим результат работы формул:
Вот и всё. Пишите в комментарии свои вопросы, дополнения, пожелания и замечания.
P.S.: После добавления некоторых необходимых классов и свойств для Worksheet и Workbook пришлось отказать от поддержки библиотеки под Silverlight 4.
Версия 1.1.6 от 30.09.2011: Добавлена возможно экспорта простого HTML в MS Word. Например рассмотрим такой пример. Есть текст какого-то договора, редактирование которого производится в html-редакторе (в моем случае это ckeditor). В своем приложении (MVC3) создал:
internal class WordResult: ActionResult
{
/// <summary>
/// Наименование файла для сохранения документа на стороне клиента.
/// </summary>
public string FileName { get; private set; }
/// <summary>
/// MVC url представления которой надо сохранить в MS Word
/// </summary>
public string Content { get; private set; }
/// <summary>
/// Заголовок HTML
/// </summary>
public string Title { get; private set; }
/// <summary>
/// Создает экземпляр WordResult
/// </summary>
/// <param name="filename">имя файла</param>
/// <param name="content">mvc url представления</param>
/// <param name="title">заголовок документа</param>
public WordResult(string filename, string title, string content)
{
this.Title= title;
this.FileName = filename;
this.Content = content;
}
public override void ExecuteResult(ControllerContext context)
{
string wordstring = WordDocument.CreateFromHtml(this.Content, "Dogovor");
HttpContext ctx = HttpContext.Current;
ctx.Response.AppendHeader("Content-Type", "application/msword");
ctx.Response.Clear();
ctx.Response.Charset = "Windows-1251";
//ctx.Response.ContentEncoding = Encoding.UTF8;
ctx.Response.AddHeader("content-disposition", "attachment;filename=" + this.FileName);
ctx.Response.Cache.SetCacheability(HttpCacheability.NoCache);
ctx.Response.ContentType = "application/ms-word";
ctx.Response.Write(wordstring);
ctx.Response.End();
ctx.Response.Flush();
ctx.Response.Clear();
}
}
}
Обратите внимание на строку номер 34. Именно в ней я и превращаю свой договор из формата HTML в формат, которой MS Word воспринимает как "родной". Естественно, что перед печатью договора, я подменяю нужные поля на требуемые значения (строка 6):
public ActionResult Print(int id)
{
Check.Argument.IsNotNegativeOrZero(id, "id");
Contract contract = contractRepository.Find(id);
Check.Argument.IsNotNull(contract, "contract");
string contractText = DataReplacer.Replace(contract.TypeOfDeal.ContractText, contract);
return new WordResult(String.Format("{0}_{1}.doc", contract.Number, contract.TypeOfDeal.Name), "Дововор услуги", contractText);
}
Вот и всё пока для этой библиотеки. Пишите комментарии.
Версия пакета 0.3.1 от 26.03.2013: Версия nuget-пакета обновилась (новая версия 0.3.1). Добавлены новые возможности: ширина колонок и авторазмер для ширины колонок, сквозные ячейки при печати нескольких страниц и многое другое.
Download
По просьбе некоторых активных читателей блога (привет тебе Алексей О.) работоспособность демонстрационного проекта восстановлена:
- обновлены nuget-пакеты, вернее их версии;
- установлена опция автоматического скачивания недостающих nuget-пакетов;
- адаптирован для версии Visual Studio 2012;
- поправлены ошибки.
Скачать демонстрационный проект для Visual Studio 2012 (обновление от 09.05.2013 года, кстати, с праздником! С Днем победы! Ура!)
Проект выложен в github. Nuget-пакет обновился до .NET 4.5.2. Сборка создана согласно спецификации XML Spreadsheet Reference.
Комментарии к статье (62)
SychevIgor,
В библиотеке не используется COM-объекты. Именно для того она выгружается не в .xls, а в .xml. Задумка была такова, чтобы можно было выгрузить данные в XML-формат, который "понимает" MS Excel, без необходимости присутствия на компьютере пользователя MS Excel. Прототипом для библиотеки послужила эта страница XML Spreadsheet Reference.
kubchik,
То что показано в статье характерно для всех версий (во всяком случае, с 2003-го офиса пробывал) Excel. То есть все они смогут понять и открыть экспортированный файл. А что касается непосредственно генерации формата документа MS EXCEL 2007, то тут нужно набраться терпения и выучить архитектуру MS Offiice 2007 и потом, после этого Вы с легкостью сможете сгенерировать что угодно.
sergey86, библиотека работает по простому принципу. В спецификации XML Spread есть такие типы полей:
public enum DataType
{
Number, DateTime, Boolean, String, Error
}
В соответствии с этим библиотека "делает" следующее:
switch (typeName)
{
case "String":
return DataType.String;
case "Short":
return DataType.Number;
case "Decimal":
return DataType.Number;
case "Float":
return DataType.Number;
case "Integer":
return DataType.Number;
case "Int16":
return DataType.Number;
case "Boolean":
return DataType.Boolean;
case "Int32":
return DataType.Number;
case "Double":
return DataType.Number;
case "DateTime":
return DataType.DateTime;
default:
return DataType.String;
}
Что-то не работает код этот у меня. Метод AddCell(int x, int y, object data, uint index); просит явно чё-то лишнее. чё за индекс - хз. пробовал передавать в него 1 или 0 - не добавляет записи. Даже если просто написать
Workbook wb = new Workbook();
Worksheet ws = new Worksheet("List1");
wb.AddWorksheet(ws);
string workstring = wb.ExportToXML();
return new ExcelResult("Export.xls", workstring);
в итоге xls-файл создаётся, только с вот таким содержимым http://www.cyberforum.ru/attachment.php?attachmentid=110879&stc=1&d=1322909289
Ок, сделал вручную. Может кому пригодится:
// generate xml
string workstring = wb.ExportToXML();
// Устанавливаем ширину колонок
workstring = workstring.Replace("<Column ss:AutoFitWidth=\"0\" ss:Width=\"75\" />", "<Column ss:AutoFitWidth=\"0\" ss:Width=\"\" />");
// Колонка1
int col1Width = 10;
workstring = workstring.Insert(workstring.IndexOf("<Column ss:AutoFitWidth=\"0\" ss:Width=\"\" />") + 38, (col1Width * 6.25).ToString());
// Колонка2
int col2Width = 11;
workstring = workstring.Insert(workstring.IndexOf("<Column ss:AutoFitWidth=\"0\" ss:Width=\"\" />") + 38, (col2Width * 6.25).ToString());
// Колонка3
int col3Width = 12;
workstring = workstring.Insert(workstring.IndexOf("<Column ss:AutoFitWidth=\"0\" ss:Width=\"\" />") + 38, (col3Width * 6.25).ToString());
Вообще я мержил ячейки. Проблема была слудущая, горизонтальный мерж проходит на ура, а вертикальный глючный ибо придобавлении новой строки нельзя было вставить ячейку куда хотелось под смерженные ячейки. И получалась пустая row до megred cells. Как-то так. В итоге из-за этого пришлось отказаться от фреймворка.
Странно, установил оба пакета: SampleData и XmlExport
using Calabonga.Xml.Exports;
using MvcExportToXML.Engine;
using SampleData;
Третий юзинг подключает нормально, а первые два вообще никак (естестенно что я использую там имя своего проекта, а не Calabonga). В чём может быть проблема? Пересобирал проект - тоже нуль эмоций...
Почитал комментарии так и не понял как запихнуть данные в отчет в word. в excel все нормально улетает. а как перекодить на word не догоняю?
выглядит так:
// First sheet
var ws = new Worksheet("Лист 1");
// adding headers
ws.AddCell(0, 0, "№", 0);
ws.AddCell(0, 1, "Форма работы", 0)
..............
for (int i = 0; i < publications.Count; i++)
ws.AddCell(i + 1, 0, i + 1, 0);
ws.AddCell(i + 1, 1, p.Title ?? "", 0);
ws.AddCell(i + 1, 2, p.SourceType.Title, 0);
........................
}
wb.AddWorksheet(ws);
// generate xml
var workstring = wb.ExportToXML();
// Send to user file
return new ExcelResult("Публикации.excel", workstring);
Значение ячейки в методе AddCell принимается как объект, в string переводить не надо, а числовое это значение или нет, определяется стилем ячейки (класс Style). В этом классе есть свойство - NumberFormat. Оно и определяет, что это за число - целое, дробное, сколько знаков после запятой и т.д. Один из параметров метода AddCell - это стиль.
А вообще в экселе можно сохранить файл как xml и посмотреть, каким образом задается ячейка.
Алексей, спасибо за ответ.
У меня еще один вопрос появился - выгрузка не работает, если я вызываю action с помощью ajax-post запроса. Можете подсказать, что делать? выгружать get-ом не представляется возможным, необходимо забирать с формы табличные данные в виде json-объектa, которые есть только на клиентской стороне.
Добрый день! Огромное спасибо за статью! В варианте когда нужно экспортировать всю таблицу все прекрасно работает, но мне нужно сделать фильтр. Например var regseminar = db.Regseminar.Where(s => s.SeminarId == seminar.SeminarId);
List<Regseminar> people = regseminar.ToList();
Не удается скачать файл
// get data
if (id == null)
{
return HttpNotFound();
}
Seminar seminar = await db.Seminar.FindAsync(id);
if (seminar == null)
{
return HttpNotFound();
}
var regseminar = db.Regseminar.Where(s => s.SeminarId == seminar.SeminarId);
List<Regseminar> people = regseminar.ToList();
Не удается скачать файл. Подскажите пожалуйста что делаю не так? И как можно это решить?
)))) Все работает!
List<Regseminar> people = db.Regseminar.Where(s=>s.SeminarId==seminar.SeminarId).ToList();
Просто был бы ум!!! )) Оказалось в базе в таблице было пару строк где SeminarId был null вот фильтр и не пускал.
Теперь еще один маленький вопрос почему-то именно хром не качает!?
Да и в хроме перечеркнутый https на localhost.
Может кто знает в чем дело?
И еще раз огромное спасибо за Вашу работу!!!
Я думаю оно тормозит здесь
XmlSerializer xmlSerializer = new XmlSerializer(typeof (Workbook));
XmlTextWriter xmlTextWriter = new XmlTextWriter((Stream) memoryStream1, Encoding.UTF8);
...
или вот тут
xmlSerializer.Serialize((XmlWriter) xmlTextWriter, (object) book);
MemoryStream memoryStream2 = (MemoryStream) xmlTextWriter.BaseStream;
Можете (хотите) ли вы предоставить pdb? Проводились ли какие нибудь нагрузочные тесты?
Нашла комментарий по интересующеу меня вопросу:
Ок, сделал вручную. Может кому пригодится:
// generate xml
string workstring = wb.ExportToXML();
// Устанавливаем ширину колонок
workstring = workstring.Replace("<Column ss:AutoFitWidth=\"0\" ss:Width=\"75\" />", "<Column ss:AutoFitWidth=\"0\" ss:Width=\"\" />");
// Колонка1
int col1Width = 10;
workstring = workstring.Insert(workstring.IndexOf("<Column ss:AutoFitWidth=\"0\" ss:Width=\"\" />") + 38, (col1Width * 6.25).ToString());
Но у меня нету такого текста: Где же вставить текст чтобы указать ширину столбца?
текст:
<?xml version="1.0" encoding="utf-8"?><?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Calabonga</Author>
<Created>23.11.2015 0:00:00</Created>
<LastAutor>Calabonga</LastAutor>
<Version>14</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG />
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Aligment ss:Vertical="Bottom" />
Доброго времени суток!
Прошу помощи. В своем проекте использовал Вашу систему выгрузки в Excel.
Недавно обновил Visual Studio с версии 2010 до Community 2015.
В результате возникла проблема с выгрузкой. Программа выдавала ошибку при создании шрифтов и стилей.
Описать код тут не получается (не вмещается количество символов), но полученный xml документ не открывается в Excel как раньше, а просто отображается структура xml документа.
Напишите пожалуйста адрес электронной почты, чтобы я мог описать по подробнее описать проблему.
И если у Вас будет возможность, то посмотрите, что можно сделать и почему не работает выгрузка.
С уважением, Рустам.