Генерация красивых Excel-отчётов по шаблону

[LaGir]

Приветствую всех!

Наверняка многие из вас создавали шаблоны-парсеры. И, как правило, результаты в этом случае на выходе помещали в Excel-таблицы. Функционала ZennoPoster хватает для осуществления этих задач, за исключением несколько нюансов. Например, отсутствуют возможности для стилизации выходных таблиц, работать можно исключительно с первым листом, записывать данные можно только в строковом формате. Нет прочих различных плюшек, доступных в Excel.

Согласитесь, куда приятнее, когда на выходе получаешь вторую табличку, а не первую (см. скриншоты ниже). А если делаем парсер на заказ, а не для своих личных целей – и говорить нечего. Именно об этом и пойдёт речь в статье – как максимально просто получить красивый Excel-отчёт в ZennoPoster, с нуля до конкретного результата.

Давайте рассмотрим возможные способы реализации.

Недавно, с версии 5.10.0.0, со стороны ZennoPoster лёд чуть-чуть тронулся – появились методы для редактирования стилей ячеек через C#-сниппеты. Однако, пока сложно сказать, что это хотя бы отчасти решило проблему. Возможности самые базовые, опять же только стили, и в любом случае их надо задавать через код – для тех, кто использует чисто кубики, по сути ничего не изменилось.

Поэтому, данный способ нам не совсем подходит. Что же делать?

Думаю, многим из вас известен альтернативный метод – использование сторонней dll-библиотеки, с помощью которой можно воплотить большую часть возможностей Excel. Однако, есть одна существенная проблема – всё оформление таблиц опять же надо писать через C#-код, и даже для той сравнительно простой желанной таблички с картинки выше – его нужно написать достаточно много. Ну и конечно, для этого надо уметь в нём неплохо разбираться. На форуме, кстати, есть статья об использовании такой библиотеки, но, к сожалению, там опять же описаны только самые азы применения стилей и форматирования – те же, которые появились в ZP v5.10.

Тем не менее, мы остановимся именно на этом способе – так как у библиотеки, которую мы будем использовать (а именно известный и могучий EPPlus), есть одна шикарная фича – создание таблиц по шаблону.

Что это значит? То, что мы можем создать xlsx-файл, нарисовать в нём вручную в Excel всё оформление – а в ZennoPoster использовать его как шаблон стилей для результирующего файла. Всё, что нам останется – вставить в область данных результаты парсинга.

Без кода тут, конечно, всё равно не обойтись, но его совсем немного. Главное и самое сложное - оформление - мы делаем руками в Excel, соответственно - нам не потребуются программерские способности и не придётся убить кучу времени на копание в документации библиотеки.

Вводную часть закончили, приступим к делу.

Подготовка шаблона оформления​

Собственно, рисуем в Excel заголовки и их стили, выравниваем всё, добавляем другие вещи по желанию (например, строку фильтра).

Так как стандартными средствами ZennoPoster можно работать только с первым листом Excel-файла (тоже порой весьма неприятное ограничение), рассмотрим пример с заполнением 2 листов.

Для примера нарисовал такие таблички. Также сразу добавил фильтры, закрепил области заголовков, кое-где добавил примечания. Над тематикой содержимого особо не заморачивался и взял первое что пришло в голову, соответственно, по этому поводу просьба не пинаться – всё это чисто для примера.

Сохраняем файл в папке с проектом, я назвал его Template.xlsx.

Подключение библиотеки​

Теперь нам нужно подключить к нашему проекту саму библиотеку EPPlus.
Наверняка многие умеют подключать dll-библиотеки, но на всякий случай разберём под спойлером по шагам.

Спойлер: Подключение EPPlus

1. Скачиваем библиотеку с её официальной странички, http://epplus.codeplex.com/.

2. Находим в архиве нужный файл EPPlus.dll, кидаем в папку ExternalAssemblies в директории с установленным ZennoPoster.

3. Открываем наш шаблон, добавляем блоки «Ссылки из GAC» и «Директивы using»

4. Заходим в «Ссылки из GAC» и добавляем библиотеку.

5. Заходим в «Директивы using» и вписываем туда директивы со скриншота.

Сниппет создания Excel-файла по шаблону​

Переходим к основным действиям. В первую очередь, создаём кубик C#-кода, в который поместим весь описанный ниже код.

1. Получаем внутренние таблицы проекта, предварительно заполненные нужными данными (которые спарсили/сгенерировали/получили из БД/ещё от куда-либо).

//Получаем временные таблицы с нужными данными
var table1 = project.Tables["Таблица 1"];
var table2 = project.Tables["Таблица 2"];

2. Определяем значения стартовых рядов и столбцов. Например, если первый ряд листа занимают заголовки столбцов, а со второго начинаются сами данные – значением стартового ряда будет 2. Со столбцами аналогично.

//Определяем ряд итоговой таблицы, с которого будем вставлять данные
int startRowT1 = 3;  //Для первого листа
int startRowT2 = 4;  //Для второго листа
//Определяем столбец итоговой таблицы, с которого будем вставлять данные
int startColT1 = 1;  //Для первого листа
int startColT2 = 1;  //Для второго листа

Если нужно получить начальный ряд или столбец из переменной проекта, определяем переменные следующим образом:

int startRowT1 = int.Parse(project.Variables["Peremennaya"].Value);

3. Объявляем вспомогательные переменные для типов данных в ячейках итоговой таблицы – они понадобятся нам чуть позже.

DateTime dt;
double d;
int i;

4. Получаем наш файл-шаблон Template.xlsx из директории проекта. Сразу делаем проверку на его наличие.

FileInfo template = new FileInfo(project.Directory + @"\Template.xlsx");
if (!template.Exists){  //Делаем проверку - если Template.xlsx отсутствует - выходим по красной ветке
  project.SendErrorToLog("Упс! Файл Excel-шаблона 'Template.xlsx' отсутствует в директории проекта.", true);
  return null;
}

5. Создаём объект нашего Excel-документа на базе Template.xlsx. Отныне весь дальнейший код будем писать внутри данных фигурных скобок.

using (ExcelPackage exPack = new ExcelPackage(template, true))
{
  //Тут будет весь дальнейший код. А именно:
  //Работа с 1 листом
  //Работа со 2 листом
  //Сохранение файла
}

6. Прежде чем начать работу с листами, их нужно определить/получить из шаблона. Сделаем это по их порядковым номерам.

ExcelWorksheet ws1 = exPack.Workbook.Worksheets[1];
ExcelWorksheet ws2 = exPack.Workbook.Worksheets[2];

7. Далее, нам нужно записать в первый лист данные из первой таблицы проекта. Это можно сделать в двойном цикле, вот так:

for (int row = startRowT1; row < table1.RowCount+startRowT1; row++){
  for (int col = startColT1; col < table1.ColCount+startColT1; col++){
    ws1.Cells[row,col].Value = table1.GetCell(col-startColT1, row-startRowT1);
  }
}

…но мы этого делать не будем, так как в этом случае все данные запишутся в обычном строковом формате (все данные стандартных таблиц Zenno хранятся как строки). Библиотека позволяет нам записывать в Excel-файл данные именно тех типов, которые нам нужны, и мы этим воспользуемся.

for (int row = startRowT1; row < table1.RowCount+startRowT1; row++){
  for (int col = startColT1; col < table1.ColCount+startColT1; col++){
    //Пробуем распознать тип вставляемых данных - дату, целое число, дробное число
    if (DateTime.TryParse(table1.GetCell(col-startColT1, row-startRowT1), out dt)){
    //Если распознали, например, дату (тип DateTime) - сразу меняем формат ячейки
      ws1.Cells[row,col].Style.Numberformat.Format = "dd.MM.yyyy";
      //Вставляем распознанное значение в формате, заданном в предыдущей строчке
      ws1.Cells[row,col].Value = dt;
    }else if (int.TryParse(table1.GetCell(col-startColT1, row-startRowT1), out i)){
      ws1.Cells[row,col].Style.Numberformat.Format = "0";
      ws1.Cells[row,col].Value = i;
    }else if (double.TryParse(table1.GetCell(col-startColT1, row-startRowT1), out d)){
      ws1.Cells[row,col].Style.Numberformat.Format = "0.00";
      ws1.Cells[row,col].Value = d;
    }else  //Если не распознали - записываем в общем формате
      ws1.Cells[row,col].Value = table1.GetCell(col-startColT1, row-startRowT1);
  }
}

Получилось слегка посложнее, но зато теперь у нас корректно будут записываться данные.

8. Отформатируем полученную табличку в более приглядный вид. Стили таблиц аналогичны тем, которые можно увидеть в Excel, только английскими названиями. «Light1» соответствует «Светлый1», «Medium1» – «Средний1», «Dark1» – «Темный1». Конкретно для первого листа я посчитал подходящим стиль «Светлый19».

//Определяем диапазон области данных
ExcelRange rangeT1 = ws1.Cells[startRowT1, startColT1, table1.RowCount+startRowT1-1, table1.ColCount+startColT1-1];
//Определяем этот диапазон как таблицу
ExcelTable tableT1 = ws1.Tables.Add(rangeT1, ws1.Name.Replace(" ",String.Empty));
//Задаём стиль таблицы. Стили аналогичны стилям в Excel, только названия на английском
tableT1.TableStyle = TableStyles.Light19;
//Отключаем заголовки и фильтр, в нашем случае они не нужны
tableT1.ShowFilter = false;
tableT1.ShowHeader = false;

9. Переходим ко второму листу. По аналогии вставляем в него данные из второй таблицы.
По уму, конечно, стоит оформлять подобные вещи в метод/процедуру, но у нас сейчас задача показать именно работу с функционалом библиотеки.
Для разнообразия выберем немного иной формат даты.

for (int row = startRowT2; row < table2.RowCount+startRowT2; row++){
  for (int col = startColT2; col < table2.ColCount+startColT2; col++){
    if (DateTime.TryParse(table2.GetCell(col-startColT2, row-startRowT2), out dt)){
      ws2.Cells[row,col].Style.Numberformat.Format = "d MMM yy";
      ws2.Cells[row,col].Value = dt;
    }else if (int.TryParse(table2.GetCell(col-startColT2, row-startRowT2), out i)){
      ws2.Cells[row,col].Style.Numberformat.Format = "0";
      ws2.Cells[row,col].Value = i;
    }else if (double.TryParse(table2.GetCell(col-startColT2, row-startRowT2), out d)){
      ws2.Cells[row,col].Style.Numberformat.Format = "0.00";
      ws2.Cells[row,col].Value = d;
    }else
      ws2.Cells[row,col].Value = table2.GetCell(col-startColT2, row-startRowT2);
  }
}

10. Как вы могли заметить, в нарисованных заголовках второго листа был последний столбец под названием «Всего», и для него я не создал данных для заполнения. Рассмотрим такую ситуацию, что в ячейки этого столбца нужно получить суммы чисел для каждой из строк.
Для этого мы воспользуемся формулами – иными словами, зададим столбцу формулу, как мы это обычно делаем в Excel.

//Определяем диапазон ячеек, в которой будет работать формула
ExcelRange formulaRange = ws2.Cells[startRowT2,table2.ColCount+startColT2,table2.RowCount+startRowT2-1,table2.ColCount+startColT2];
//Задаём в диапазоне саму формулу. Формулы пишутся как в самом Excel (англ. версии), только без знака "="
formulaRange.Formula = String.Format("SUM(B{0}:I{0})", startRowT2);
formulaRange.Calculate();

Библиотека поддерживает большинство других формул Excel, единственное, рекомендуется их писать в английском варианте и без знака «=».

11. По аналогии с первым листом делаем форматирование области данных, теперь только уже вместе со столбцом «Всего». Дополнительно для последнего включаем особое форматирование.

ExcelRange rangeT2 = ws2.Cells[startRowT2, startColT2, table2.RowCount+startRowT2-1, table2.ColCount+startColT2];
ExcelTable tableT2 = ws2.Tables.Add(rangeT2, ws2.Name.Replace(" ",String.Empty));
tableT2.TableStyle = TableStyles.Light16;
tableT2.ShowFilter = false;
tableT2.ShowHeader = false;
tableT2.ShowLastColumn = true;  //Для последнего столбца ("Всего") дополнительно включаем особый стиль

12. Сохраняем полученный файл в папку проекта, называем его Result.xlsx.

Byte[] bin = exPack.GetAsByteArray();
string resPath = project.Directory + @"\Result.xlsx";
File.WriteAllBytes(resPath, bin);
project.SendInfoToLog("Сохранили итоговый Excel-файл по адресу: "+resPath, true);

Вот, собственно, и всё. После выполнения такого сниппета в папке с проектом появится итоговый файл. В тестовом проекте для этой статьи получаем такие таблички.

Данный проект-пример прикрепляю во вложения к посту.

 

[LaGir]

Дополнение к статье​

В качестве дополнительной части рассмотрим пару плюшек из возможностей библиотеки, на примере того же 2 листа из проекта-примера – возможно, кому-то пригодится.

Код ниже используем в сниппете после формирования второго листа, но до сохранения.

1. Для начала сделаем дополнительное обрамление и разделение области данных границами.

//Средняя граница вокруг области данных (по ранее определённому диапазону таблицы rangeT2)
rangeT2.Style.Border.BorderAround(ExcelBorderStyle.Medium);
//Дополнительная обводка области без дат и итогов
ws2.Cells[startRowT2, startColT2+1, table2.RowCount+startRowT2-1, table2.ColCount+startColT2-1].Style.Border.BorderAround(ExcelBorderStyle.Medium);

Таким образом визуально мы слегка отделяем столбцы «Дата» и «Всего».

Спойлер: Результат

2. Выводы по общим продажах по дням делаются по столбцу «Всего», поэтому применим к нему условное форматирование для большей наглядности. Допустим, если за день продано меньше 80 смартфонов – показатель продаж низкий, если от 80 до 100 – средний, если выше 100 – высокий.

//Определяем диапазон ячеек, в котором будет работать условное форматирование (столбец "Всего")
var excAddress = new ExcelAddress(startRowT2, table2.ColCount+startColT2, table2.RowCount+startRowT2-1, table2.ColCount+startColT2);
//Делаем фон ячейки светло-зелёным, если её значение больше или равно 100
var excCondition = ws2.ConditionalFormatting.AddExpression(excAddress);
excCondition.Style.Fill.BackgroundColor.Color = Color.LightGreen;  //Устанавливаем цвет
excCondition.Formula = String.Format("IF(J{0}>=100,1,0)", startRowT2);  //Устанавливаем формулу условия
//Делаем фон ячейки розовым, если её значение меньше 80
excCondition = ws2.ConditionalFormatting.AddExpression(excAddress);
excCondition.Style.Fill.BackgroundColor.Color = Color.Pink;
excCondition.Formula = String.Format("IF(J{0}<80,1,0)", startRowT2);
//Делаем фон ячейки жёлтым, если её значение больше или равно 80 и меньше 100
excCondition = ws2.ConditionalFormatting.AddExpression(excAddress);
excCondition.Style.Fill.BackgroundColor.Color = Color.Yellow;
excCondition.Formula = String.Format("IF(AND(J{0}>=80,J{0}<100),1,0)", startRowT2);

Спойлер: Результат

3. Можно поднять наглядность данных и по отдельным производителям. Давайте рассмотрим пару вариантов – цветовые шкалы и значки.

//Добавляем дополнительное форматирование к основным данным
string condForm = "Значки";  //Выбираем тип форматирования
//Определяем диапазон основных данных
var excAddress2 = new ExcelAddress(startRowT2, startColT2+1, table2.RowCount+startRowT2-1, table2.ColCount+startColT2-1);
if (condForm=="Цветовые шкалы")  //Если выбраны цветовые шкалы, делаем соответствующее форматирование
  ws2.ConditionalFormatting.AddThreeColorScale(excAddress2);
else if (condForm=="Значки")  //Если выбраны значки, делаем соответствующее форматирование
  ws2.ConditionalFormatting.AddThreeIconSet(excAddress2, eExcelconditionalFormatting3IconsSetType.TrafficLights1);

Спойлер: Результат

Мне больше понравился вариант со значками, поэтому я остановился на нём.

Согласитесь, табличка стала гораздо нагляднее. Только взглянув, за пару секунд можно понять, какие смартфоны продаются хорошо, какие не особо.

На этом всё, благодарю за внимание. Искренне надеюсь, что материал данной статьи окажется вам полезным.
Всем удачи и с наступающим!

 

[GoodX]

Приветствую всех!

Наверняка многие из вас создавали шаблоны-парсеры. И, как правило, результаты в этом случае на выходе помещали в Excel-таблицы. Функционала ZennoPoster хватает для осуществления этих задач, за исключением несколько нюансов. Например, отсутствуют возможности для стилизации выходных таблиц, работать можно исключительно с первым листом, записывать данные можно только в строковом формате. Нет прочих различных плюшек, доступных в Excel.

Согласитесь, куда приятнее, когда на выходе получаешь вторую табличку, а не первую (см. скриншоты ниже). А если делаем парсер на заказ, а не для своих личных целей – и говорить нечего. Именно об этом и пойдёт речь в статье – как максимально просто получить красивый Excel-отчёт в ZennoPoster, с нуля до конкретного результата.

Посмотреть вложение 17266 Посмотреть вложение 17267

Давайте рассмотрим возможные способы реализации.

Недавно, с версии 5.10.0.0, со стороны ZennoPoster лёд чуть-чуть тронулся – появились методы для редактирования стилей ячеек через C#-сниппеты. Однако, пока сложно сказать, что это хотя бы отчасти решило проблему. Возможности самые базовые, опять же только стили, и в любом случае их надо задавать через код – для тех, кто использует чисто кубики, по сути ничего не изменилось.

Поэтому, данный способ нам не совсем подходит. Что же делать?

Думаю, многим из вас известен альтернативный метод – использование сторонней dll-библиотеки, с помощью которой можно воплотить большую часть возможностей Excel. Однако, есть одна существенная проблема – всё оформление таблиц опять же надо писать через C#-код, и даже для той сравнительно простой желанной таблички с картинки выше – его нужно написать достаточно много. Ну и конечно, для этого надо уметь в нём неплохо разбираться. На форуме, кстати, есть статья об использовании такой библиотеки, но, к сожалению, там опять же описаны только самые азы применения стилей и форматирования – те же, которые появились в ZP v5.10.

Тем не менее, мы остановимся именно на этом способе – так как у библиотеки, которую мы будем использовать (а именно известный и могучий EPPlus), есть одна шикарная фича – создание таблиц по шаблону.

Что это значит? То, что мы можем создать xlsx-файл, нарисовать в нём вручную в Excel всё оформление – а в ZennoPoster использовать его как шаблон стилей для результирующего файла. Всё, что нам останется – вставить в область данных результаты парсинга.

Посмотреть вложение 17268

Без кода тут, конечно, всё равно не обойтись, но его совсем немного. Главное и самое сложное - оформление - мы делаем руками в Excel, соответственно - нам не потребуются программерские способности и не придётся убить кучу времени на копание в документации библиотеки.

Вводную часть закончили, приступим к делу.

Подготовка шаблона оформления​

Собственно, рисуем в Excel заголовки и их стили, выравниваем всё, добавляем другие вещи по желанию (например, строку фильтра).

Так как стандартными средствами ZennoPoster можно работать только с первым листом Excel-файла (тоже порой весьма неприятное ограничение), рассмотрим пример с заполнением 2 листов.

Для примера нарисовал такие таблички. Также сразу добавил фильтры, закрепил области заголовков, кое-где добавил примечания. Над тематикой содержимого особо не заморачивался и взял первое что пришло в голову, соответственно, по этому поводу просьба не пинаться – всё это чисто для примера.
Посмотреть вложение 17269 Посмотреть вложение 17270

Сохраняем файл в папке с проектом, я назвал его Template.xlsx.

Подключение библиотеки​

Теперь нам нужно подключить к нашему проекту саму библиотеку EPPlus.
Наверняка многие умеют подключать dll-библиотеки, но на всякий случай разберём под спойлером по шагам.

Спойлер: Подключение EPPlus

1. Скачиваем библиотеку с её официальной странички, http://epplus.codeplex.com/.
Посмотреть вложение 17271

2. Находим в архиве нужный файл EPPlus.dll, кидаем в папку ExternalAssemblies в директории с установленным ZennoPoster.
Посмотреть вложение 17272
Посмотреть вложение 17273

3. Открываем наш шаблон, добавляем блоки «Ссылки из GAC» и «Директивы using»
Посмотреть вложение 17274

4. Заходим в «Ссылки из GAC» и добавляем библиотеку.
Посмотреть вложение 17275

5. Заходим в «Директивы using» и вписываем туда директивы со скриншота.
Посмотреть вложение 17276

Сниппет создания Excel-файла по шаблону​

Переходим к основным действиям. В первую очередь, создаём кубик C#-кода, в который поместим весь описанный ниже код.

1. Получаем внутренние таблицы проекта, предварительно заполненные нужными данными (которые спарсили/сгенерировали/получили из БД/ещё от куда-либо).

//Получаем временные таблицы с нужными данными
var table1 = project.Tables["Таблица 1"];
var table2 = project.Tables["Таблица 2"];

2. Определяем значения стартовых рядов и столбцов. Например, если первый ряд листа занимают заголовки столбцов, а со второго начинаются сами данные – значением стартового ряда будет 2. Со столбцами аналогично.

//Определяем ряд итоговой таблицы, с которого будем вставлять данные
int startRowT1 = 3;  //Для первого листа
int startRowT2 = 4;  //Для второго листа
//Определяем столбец итоговой таблицы, с которого будем вставлять данные
int startColT1 = 1;  //Для первого листа
int startColT2 = 1;  //Для второго листа

Если нужно получить начальный ряд или столбец из переменной проекта, определяем переменные следующим образом:

int startRowT1 = int.Parse(project.Variables["Peremennaya"].Value);

3. Объявляем вспомогательные переменные для типов данных в ячейках итоговой таблицы – они понадобятся нам чуть позже.

DateTime dt;
double d;
int i;

4. Получаем наш файл-шаблон Template.xlsx из директории проекта. Сразу делаем проверку на его наличие.

FileInfo template = new FileInfo(project.Directory + @"\Template.xlsx");
if (!template.Exists){  //Делаем проверку - если Template.xlsx отсутствует - выходим по красной ветке
  project.SendErrorToLog("Упс! Файл Excel-шаблона 'Template.xlsx' отсутствует в директории проекта.", true);
  return null;
}

5. Создаём объект нашего Excel-документа на базе Template.xlsx. Отныне весь дальнейший код будем писать внутри данных фигурных скобок.

using (ExcelPackage exPack = new ExcelPackage(template, true))
{
  //Тут будет весь дальнейший код. А именно:
  //Работа с 1 листом
  //Работа со 2 листом
  //Сохранение файла
}

6. Прежде чем начать работу с листами, их нужно определить/получить из шаблона. Сделаем это по их порядковым номерам.

ExcelWorksheet ws1 = exPack.Workbook.Worksheets[1];
ExcelWorksheet ws2 = exPack.Workbook.Worksheets[2];

7. Далее, нам нужно записать в первый лист данные из первой таблицы проекта. Это можно сделать в двойном цикле, вот так:

for (int row = startRowT1; row < table1.RowCount+startRowT1; row++){
  for (int col = startColT1; col < table1.ColCount+startColT1; col++){
    ws1.Cells[row,col].Value = table1.GetCell(col-startColT1, row-startRowT1);
  }
}

…но мы этого делать не будем, так как в этом случае все данные запишутся в обычном строковом формате (все данные стандартных таблиц Zenno хранятся как строки). Библиотека позволяет нам записывать в Excel-файл данные именно тех типов, которые нам нужны, и мы этим воспользуемся.

for (int row = startRowT1; row < table1.RowCount+startRowT1; row++){
  for (int col = startColT1; col < table1.ColCount+startColT1; col++){
    //Пробуем распознать тип вставляемых данных - дату, целое число, дробное число
    if (DateTime.TryParse(table1.GetCell(col-startColT1, row-startRowT1), out dt)){
    //Если распознали, например, дату (тип DateTime) - сразу меняем формат ячейки
      ws1.Cells[row,col].Style.Numberformat.Format = "dd.MM.yyyy";
      //Вставляем распознанное значение в формате, заданном в предыдущей строчке
      ws1.Cells[row,col].Value = dt;
    }else if (int.TryParse(table1.GetCell(col-startColT1, row-startRowT1), out i)){
      ws1.Cells[row,col].Style.Numberformat.Format = "0";
      ws1.Cells[row,col].Value = i;
    }else if (double.TryParse(table1.GetCell(col-startColT1, row-startRowT1), out d)){
      ws1.Cells[row,col].Style.Numberformat.Format = "0.00";
      ws1.Cells[row,col].Value = d;
    }else  //Если не распознали - записываем в общем формате
      ws1.Cells[row,col].Value = table1.GetCell(col-startColT1, row-startRowT1);
  }
}

Получилось слегка посложнее, но зато теперь у нас корректно будут записываться данные.

8. Отформатируем полученную табличку в более приглядный вид. Стили таблиц аналогичны тем, которые можно увидеть в Excel, только английскими названиями. «Light1» соответствует «Светлый1», «Medium1» – «Средний1», «Dark1» – «Темный1». Конкретно для первого листа я посчитал подходящим стиль «Светлый19».

//Определяем диапазон области данных
ExcelRange rangeT1 = ws1.Cells[startRowT1, startColT1, table1.RowCount+startRowT1-1, table1.ColCount+startColT1-1];
//Определяем этот диапазон как таблицу
ExcelTable tableT1 = ws1.Tables.Add(rangeT1, ws1.Name.Replace(" ",String.Empty));
//Задаём стиль таблицы. Стили аналогичны стилям в Excel, только названия на английском
tableT1.TableStyle = TableStyles.Light19;
//Отключаем заголовки и фильтр, в нашем случае они не нужны
tableT1.ShowFilter = false;
tableT1.ShowHeader = false;

9. Переходим ко второму листу. По аналогии вставляем в него данные из второй таблицы.
По уму, конечно, стоит оформлять подобные вещи в метод/процедуру, но у нас сейчас задача показать именно работу с функционалом библиотеки.
Для разнообразия выберем немного иной формат даты.

for (int row = startRowT2; row < table2.RowCount+startRowT2; row++){
  for (int col = startColT2; col < table2.ColCount+startColT2; col++){
    if (DateTime.TryParse(table2.GetCell(col-startColT2, row-startRowT2), out dt)){
      ws2.Cells[row,col].Style.Numberformat.Format = "d MMM yy";
      ws2.Cells[row,col].Value = dt;
    }else if (int.TryParse(table2.GetCell(col-startColT2, row-startRowT2), out i)){
      ws2.Cells[row,col].Style.Numberformat.Format = "0";
      ws2.Cells[row,col].Value = i;
    }else if (double.TryParse(table2.GetCell(col-startColT2, row-startRowT2), out d)){
      ws2.Cells[row,col].Style.Numberformat.Format = "0.00";
      ws2.Cells[row,col].Value = d;
    }else
      ws2.Cells[row,col].Value = table2.GetCell(col-startColT2, row-startRowT2);
  }
}

10. Как вы могли заметить, в нарисованных заголовках второго листа был последний столбец под названием «Всего», и для него я не создал данных для заполнения. Рассмотрим такую ситуацию, что в ячейки этого столбца нужно получить суммы чисел для каждой из строк.
Для этого мы воспользуемся формулами – иными словами, зададим столбцу формулу, как мы это обычно делаем в Excel.

//Определяем диапазон ячеек, в которой будет работать формула
ExcelRange formulaRange = ws2.Cells[startRowT2,table2.ColCount+startColT2,table2.RowCount+startRowT2-1,table2.ColCount+startColT2];
//Задаём в диапазоне саму формулу. Формулы пишутся как в самом Excel (англ. версии), только без знака "="
formulaRange.Formula = String.Format("SUM(B{0}:I{0})", startRowT2);
formulaRange.Calculate();

Библиотека поддерживает большинство других формул Excel, единственное, рекомендуется их писать в английском варианте и без знака «=».

11. По аналогии с первым листом делаем форматирование области данных, теперь только уже вместе со столбцом «Всего». Дополнительно для последнего включаем особое форматирование.

ExcelRange rangeT2 = ws2.Cells[startRowT2, startColT2, table2.RowCount+startRowT2-1, table2.ColCount+startColT2];
ExcelTable tableT2 = ws2.Tables.Add(rangeT2, ws2.Name.Replace(" ",String.Empty));
tableT2.TableStyle = TableStyles.Light16;
tableT2.ShowFilter = false;
tableT2.ShowHeader = false;
tableT2.ShowLastColumn = true;  //Для последнего столбца ("Всего") дополнительно включаем особый стиль

12. Сохраняем полученный файл в папку проекта, называем его Result.xlsx.

Byte[] bin = exPack.GetAsByteArray();
string resPath = project.Directory + @"\Result.xlsx";
File.WriteAllBytes(resPath, bin);
project.SendInfoToLog("Сохранили итоговый Excel-файл по адресу: "+resPath, true);

Вот, собственно, и всё. После выполнения такого сниппета в папке с проектом появится итоговый файл. В тестовом проекте для этой статьи получаем такие таблички.

Посмотреть вложение 17278
Посмотреть вложение 17279

Данный проект-пример прикрепляю во вложения к посту.

На мой взгляд, статья - эталон качественных материалов

 

[DenisK]

Спасибо интересно будет почитать...

Хорошо оформлен топик. Качество к концу конкурса заметно растет и статей и авторов, что радует...

 

[Sanekk]

Интересная статья,применение конечно специфическое,но для расширения опыта почитать однозначно стоит,а может и пригодится когда-нить на практике!!!
И оформлена очень старательно,сразу видно что ТС серьезно подошел к делу!

 

[AgentRassilok]

здорово, молодец )

 

[AloneSlamerNeverDie]

Хоть статья мне не интересна так как я уже давно перешел на базы но лайкну.

 

[backoff]

 

[hostcredit]

Хорошая статья и оформлена красиво, лайкнул.

 

[lzlmrf]

спасибо!

 

[Hannes]

Шикарная статья!

Летом так же пытался реализовать красивые таблицы. В итоге сделал через html и js, генеря таблицы и заливая на локалхост.
Ваше решение понравилось больше.

 

[Semyon]

Спасибо большое за статью! Недавно начал изучать C#, такое применение прям захлестывает мотивацию.

 

[VerBin]

Полезная техническая статья. Некоторым возможно пригодится.
Хотя всеже лучше использовать базы данных.
Из них можно брать данные, и впоследствии уже оформлять как угодно, к примеру на php+html+css

 

[LaGir]

Хотя всеже лучше использовать базы данных. Из них можно брать данные в последствии и уже оформлять как угодно к примеру на php+html+css

Получение данных из внутренних таблиц проекта рассмотрено чисто как базовый пример, так как новички обычно начинают именно с них.
Исходил из того, что для тех, кто активно использует БД - либо не составит труда добавить несколько строчек кода для получения данных напрямую с базы, либо у них уже есть свои готовые решения по выводу красивых табличек (что, думаю, более вероятно).

 

[piarche]

Спасибо, очень пригодилось!!!

 

[Плутишка]

А можно как-то с графиками в этом направлении поработать? Например сделать себе админку как на ПП и туда агрегировать информацию с разных ПП?

 

[DRIVONT]

+

 

[Semyon]

А можно как-то с графиками в этом направлении поработать? Например сделать себе админку как на ПП и туда агрегировать информацию с разных ПП?

а что мешает всю информацию подтягивать в базу(с разных ПП), а потом на своем сайте её загружая из базы обрабатывать уже стилями.(через тот же JS, ну или сразу через PHP) Что на JS+CSS это реализовать не сложно или через PHP+CSS, что с базой работать не сложно(сам недавно начал).

 

[bizzon]

Вот если бы в Google Таблицах еще.

 

[VerBin]

Вот если бы в Google Таблицах еще.

Я недавно организовывал подобный функционал. Там несколько сложнее.

 

[LaGir]

А можно как-то с графиками в этом направлении поработать? Например сделать себе админку как на ПП и туда агрегировать информацию с разных ПП?

С графиками можно, но решение в статье подразумевает чисто вывод в файлы Excel (то бишь, лучше применять, когда нужна именно отчётность). Если нужно какое-либо взаимодействие, работа с админкой - как писали выше, лучше сразу всё реализовывать на PHP+HTML+CSS.

 

[arhip1985]

тема понравилась - проголосовал за твою

 

[Hannes]

Таблицы созданные в LibreOffice не работают, хоть и сохраняются с расширением .xlsx. Даже если банально открыть таблицу Template.xlsx из архива в первом посте и пересохранить, то всё перестает работать.
Используйте Excel или другие таблицы.

Потратил часа три разбираясь, буду рад если сэкономлю кому-то время.

 

[Serjio Leone]

@LaGir
Доброго времени! Кто может поделиться библиотекой EPPlus.dll на сайте не смог скачать. И актуальна ли тема на данный момент?

 

[LaGir]

@Serjio Leone, приветствую!
По ссылке в шапке написано, что библиотека переехала на GitHub, оттуда и можно скачать.
Про актуальность темы не совсем понял. Вроде как техническая статья, а не манимейкерская схема, к примеру, особо нечему становиться неактуальным.

 

[moRL]

@Serjio Leone, приветствую!
По ссылке в шапке написано, что библиотека переехала на GitHub, оттуда и можно скачать.

Искал-искал, но так и не нашел. Скачивал архивы оттуда и тоже ничего.

Спойлер: Скрин

PS: Нашел в этом архиве

 

[Hannes]

moRL, тоже искал EPPlus несколько дней назад. Та ещё проблема была)
Нашел у себя на винчестере поиском по всему винчестеру)
На всякий случай продублирую ещё так. Вдруг Роскомпозор и гитхаб заблокирует)

 

[moRL]

@LaGir помоги, пожалуйста, разобраться - у меня 43к строк переносятся примерно 1ч. Таблица привязана к файлу стандартным экшеном "Операции с таблицами - привязать к файлу", так как постоянно с новым названием. Как можно ускорить процесс?

 

[Mikhail B.]

@LaGir помоги, пожалуйста, разобраться - у меня 43к строк переносятся примерно 1ч. Таблица привязана к файлу стандартным экшеном "Операции с таблицами - привязать к файлу", так как постоянно с новым названием. Как можно ускорить процесс?

Откуда переносится? Попробуй сохранять данные в список. А потом через экшн операции над текстом (в таблицу) импортировать данные.

 

[moRL]

Откуда переносится? Попробуй сохранять данные в список. А потом через экшн операции над текстом (в таблицу) импортировать данные.

В процессе парсинга создается таблица csv без форматирования, этот файл привязывается к таблице и оттуда уже идет формирование xls файла с форматированием.