ru-RU | создано: 18.08.2011 | опубликовано: 18.08.2011 | обновлено: 22.06.2020 | просмотров за всё время: 31696 | комментариев: 62
При работе над очередным проектом, потребовалось реализовать экспорт данных в MS Excel. Решил поделиться информацией о том, как это можно сделать без особых усилий. Сборка создана согласно спецификации XML Spreadsheet Reference.
Я создал простой тестовый проект на 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-пакет, потому что считаю, что это очень удобно (да и людям приятно). Для установки пакета 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). Добавлены новые возможности: ширина колонок и авторазмер для ширины колонок, сквозные ячейки при печати нескольких страниц и многое другое.
По просьбе некоторых активных читателей блога (привет тебе Алексей О.) работоспособность демонстрационного проекта восстановлена:
Скачать демонстрационный проект для Visual Studio 2012 (обновление от 09.05.2013 года, кстати, с праздником! С Днем победы! Ура!)
Проект выложен в github. Nuget-пакет обновился до .NET 4.5.2. Сборка создана согласно спецификации XML Spreadsheet Reference.
Новая сборка для ASP.NET Core (NetStandard2.1)
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();
Не удается скачать файл
)))) Все работает!
List<Regseminar> people = db.Regseminar.Where(s=>s.SeminarId==seminar.SeminarId).ToList();
Просто был бы ум!!! )) Оказалось в базе в таблице было пару строк где SeminarId был null вот фильтр и не пускал.
Теперь еще один маленький вопрос почему-то именно хром не качает!?
Да и в хроме перечеркнутый https на localhost.
Может кто знает в чем дело?
И еще раз огромное спасибо за Вашу работу!!!
Нашла комментарий по интересующеу меня вопросу:
Ок, сделал вручную. Может кому пригодится:
// 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 документа.
Напишите пожалуйста адрес электронной почты, чтобы я мог описать по подробнее описать проблему.
И если у Вас будет возможность, то посмотрите, что можно сделать и почему не работает выгрузка.
С уважением, Рустам.