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

LaGir

Client
Регистрация
01.10.2015
Сообщения
211
Благодарностей
849
Баллы
93
Приветствую всех!

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

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

1 Что имеем2.png
2 Что хотим2.png


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

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

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

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

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

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

Область данных.png


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

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


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

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

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

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


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


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

Теперь нам нужно подключить к нашему проекту саму библиотеку EPPlus.
Наверняка многие умеют подключать dll-библиотеки, но на всякий случай разберём под спойлером по шагам.
1. Скачиваем библиотеку с её официальной странички, http://epplus.codeplex.com/.
dll1.png


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

dll3.png


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


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


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


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

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

1. Получаем внутренние таблицы проекта, предварительно заполненные нужными данными (которые спарсили/сгенерировали/получили из БД/ещё от куда-либо).
C#:
//Получаем временные таблицы с нужными данными
var table1 = project.Tables["Таблица 1"];
var table2 = project.Tables["Таблица 2"];
2. Определяем значения стартовых рядов и столбцов. Например, если первый ряд листа занимают заголовки столбцов, а со второго начинаются сами данные – значением стартового ряда будет 2. Со столбцами аналогично.
C#:
//Определяем ряд итоговой таблицы, с которого будем вставлять данные
int startRowT1 = 3;  //Для первого листа
int startRowT2 = 4;  //Для второго листа
//Определяем столбец итоговой таблицы, с которого будем вставлять данные
int startColT1 = 1;  //Для первого листа
int startColT2 = 1;  //Для второго листа
Если нужно получить начальный ряд или столбец из переменной проекта, определяем переменные следующим образом:
C#:
int startRowT1 = int.Parse(project.Variables["Peremennaya"].Value);
3. Объявляем вспомогательные переменные для типов данных в ячейках итоговой таблицы – они понадобятся нам чуть позже.
C#:
DateTime dt;
double d;
int i;
4. Получаем наш файл-шаблон Template.xlsx из директории проекта. Сразу делаем проверку на его наличие.
C#:
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. Отныне весь дальнейший код будем писать внутри данных фигурных скобок.
C#:
using (ExcelPackage exPack = new ExcelPackage(template, true))
{
  //Тут будет весь дальнейший код. А именно:
  //Работа с 1 листом
  //Работа со 2 листом
  //Сохранение файла
}
6. Прежде чем начать работу с листами, их нужно определить/получить из шаблона. Сделаем это по их порядковым номерам.
C#:
ExcelWorksheet ws1 = exPack.Workbook.Worksheets[1];
ExcelWorksheet ws2 = exPack.Workbook.Worksheets[2];
7. Далее, нам нужно записать в первый лист данные из первой таблицы проекта. Это можно сделать в двойном цикле, вот так:
C#:
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-файл данные именно тех типов, которые нам нужны, и мы этим воспользуемся.
C#:
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».
C#:
//Определяем диапазон области данных
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. Переходим ко второму листу. По аналогии вставляем в него данные из второй таблицы.
По уму, конечно, стоит оформлять подобные вещи в метод/процедуру, но у нас сейчас задача показать именно работу с функционалом библиотеки.
Для разнообразия выберем немного иной формат даты.
C#:
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.
C#:
//Определяем диапазон ячеек, в которой будет работать формула
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. По аналогии с первым листом делаем форматирование области данных, теперь только уже вместе со столбцом «Всего». Дополнительно для последнего включаем особое форматирование.
C#:
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.
C#:
Byte[] bin = exPack.GetAsByteArray();
string resPath = project.Directory + @"\Result.xlsx";
File.WriteAllBytes(resPath, bin);
project.SendInfoToLog("Сохранили итоговый Excel-файл по адресу: "+resPath, true);
Вот, собственно, и всё. После выполнения такого сниппета в папке с проектом появится итоговый файл. В тестовом проекте для этой статьи получаем такие таблички.

Итог1.png

Итог2.png


Данный проект-пример прикрепляю во вложения к посту.
 
Тема статьи
Генерация
Номер конкурса статей
Шестой конкурс статей

Вложения

Для запуска проектов требуется программа ZennoPoster или ZennoDroid.
Это основное приложение, предназначенное для выполнения автоматизированных шаблонов действий (ботов).
Подробнее...

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

LaGir

Client
Регистрация
01.10.2015
Сообщения
211
Благодарностей
849
Баллы
93
Дополнение к статье

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

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

1. Для начала сделаем дополнительное обрамление и разделение области данных границами.
C#:
//Средняя граница вокруг области данных (по ранее определённому диапазону таблицы 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);
Таким образом визуально мы слегка отделяем столбцы «Дата» и «Всего».
Доп - границы.png

2. Выводы по общим продажах по дням делаются по столбцу «Всего», поэтому применим к нему условное форматирование для большей наглядности. Допустим, если за день продано меньше 80 смартфонов – показатель продаж низкий, если от 80 до 100 – средний, если выше 100 – высокий.
C#:
//Определяем диапазон ячеек, в котором будет работать условное форматирование (столбец "Всего")
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);
Доп - УФ.png

3. Можно поднять наглядность данных и по отдельным производителям. Давайте рассмотрим пару вариантов – цветовые шкалы и значки.
C#:
//Добавляем дополнительное форматирование к основным данным
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);
Доп - цветовые шкалы.png

Доп - значки.png

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

Доп - значки.png


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

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

GoodX

Client
Регистрация
20.03.2016
Сообщения
270
Благодарностей
102
Баллы
43
Приветствую всех!

Наверняка многие из вас создавали шаблоны-парсеры. И, как правило, результаты в этом случае на выходе помещали в 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-библиотеки, но на всякий случай разберём под спойлером по шагам.
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. Получаем внутренние таблицы проекта, предварительно заполненные нужными данными (которые спарсили/сгенерировали/получили из БД/ещё от куда-либо).
C#:
//Получаем временные таблицы с нужными данными
var table1 = project.Tables["Таблица 1"];
var table2 = project.Tables["Таблица 2"];
2. Определяем значения стартовых рядов и столбцов. Например, если первый ряд листа занимают заголовки столбцов, а со второго начинаются сами данные – значением стартового ряда будет 2. Со столбцами аналогично.
C#:
//Определяем ряд итоговой таблицы, с которого будем вставлять данные
int startRowT1 = 3;  //Для первого листа
int startRowT2 = 4;  //Для второго листа
//Определяем столбец итоговой таблицы, с которого будем вставлять данные
int startColT1 = 1;  //Для первого листа
int startColT2 = 1;  //Для второго листа
Если нужно получить начальный ряд или столбец из переменной проекта, определяем переменные следующим образом:
C#:
int startRowT1 = int.Parse(project.Variables["Peremennaya"].Value);
3. Объявляем вспомогательные переменные для типов данных в ячейках итоговой таблицы – они понадобятся нам чуть позже.
C#:
DateTime dt;
double d;
int i;
4. Получаем наш файл-шаблон Template.xlsx из директории проекта. Сразу делаем проверку на его наличие.
C#:
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. Отныне весь дальнейший код будем писать внутри данных фигурных скобок.
C#:
using (ExcelPackage exPack = new ExcelPackage(template, true))
{
  //Тут будет весь дальнейший код. А именно:
  //Работа с 1 листом
  //Работа со 2 листом
  //Сохранение файла
}
6. Прежде чем начать работу с листами, их нужно определить/получить из шаблона. Сделаем это по их порядковым номерам.
C#:
ExcelWorksheet ws1 = exPack.Workbook.Worksheets[1];
ExcelWorksheet ws2 = exPack.Workbook.Worksheets[2];
7. Далее, нам нужно записать в первый лист данные из первой таблицы проекта. Это можно сделать в двойном цикле, вот так:
C#:
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-файл данные именно тех типов, которые нам нужны, и мы этим воспользуемся.
C#:
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».
C#:
//Определяем диапазон области данных
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. Переходим ко второму листу. По аналогии вставляем в него данные из второй таблицы.
По уму, конечно, стоит оформлять подобные вещи в метод/процедуру, но у нас сейчас задача показать именно работу с функционалом библиотеки.
Для разнообразия выберем немного иной формат даты.
C#:
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.
C#:
//Определяем диапазон ячеек, в которой будет работать формула
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. По аналогии с первым листом делаем форматирование области данных, теперь только уже вместе со столбцом «Всего». Дополнительно для последнего включаем особое форматирование.
C#:
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.
C#:
Byte[] bin = exPack.GetAsByteArray();
string resPath = project.Directory + @"\Result.xlsx";
File.WriteAllBytes(resPath, bin);
project.SendInfoToLog("Сохранили итоговый Excel-файл по адресу: "+resPath, true);
Вот, собственно, и всё. После выполнения такого сниппета в папке с проектом появится итоговый файл. В тестовом проекте для этой статьи получаем такие таблички.

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

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

DenisK

Client
Регистрация
28.06.2016
Сообщения
592
Благодарностей
289
Баллы
63
Спасибо интересно будет почитать...

Хорошо оформлен топик. Качество к концу конкурса заметно растет и статей и авторов, что радует... :ay:
 
  • Спасибо
Реакции: masterLomaster и LaGir

Sanekk

Client
Регистрация
24.06.2016
Сообщения
987
Благодарностей
388
Баллы
63
Интересная статья,применение конечно специфическое,но для расширения опыта почитать однозначно стоит,а может и пригодится когда-нить на практике!!!
И оформлена очень старательно,сразу видно что ТС серьезно подошел к делу!
 
  • Спасибо
Реакции: masterLomaster и LaGir

AgentRassilok

Известная личность
Регистрация
08.11.2016
Сообщения
1 272
Благодарностей
466
Баллы
83
здорово, молодец )
 
  • Спасибо
Реакции: LaGir

AloneSlamerNeverDie

Пользователь
Регистрация
14.12.2016
Сообщения
18
Благодарностей
3
Баллы
8

Обращаем Ваше внимание на то, что данный пользователь заблокирован.
Не рекомендуем проводить с AloneSlamerNeverDie какие-либо сделки.

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

lzlmrf

Client
Регистрация
14.08.2015
Сообщения
487
Благодарностей
148
Баллы
43
спасибо!
 
  • Спасибо
Реакции: LaGir

Hannes

Client
Регистрация
03.02.2016
Сообщения
494
Благодарностей
233
Баллы
43
Шикарная статья!

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

Semyon

Client
Регистрация
19.09.2016
Сообщения
43
Благодарностей
36
Баллы
18
Спасибо большое за статью! Недавно начал изучать C#, такое применение прям захлестывает мотивацию.
 
  • Спасибо
Реакции: LaGir

VerBin

Client
Регистрация
28.05.2016
Сообщения
555
Благодарностей
454
Баллы
63
Полезная техническая статья. Некоторым возможно пригодится.
Хотя всеже лучше использовать базы данных.
Из них можно брать данные, и впоследствии уже оформлять как угодно, к примеру на php+html+css
 
Последнее редактирование:
  • Спасибо
Реакции: Semyon, DenisK и LaGir

LaGir

Client
Регистрация
01.10.2015
Сообщения
211
Благодарностей
849
Баллы
93
Хотя всеже лучше использовать базы данных. Из них можно брать данные в последствии и уже оформлять как угодно к примеру на php+html+css
Получение данных из внутренних таблиц проекта рассмотрено чисто как базовый пример, так как новички обычно начинают именно с них.
Исходил из того, что для тех, кто активно использует БД - либо не составит труда добавить несколько строчек кода для получения данных напрямую с базы, либо у них уже есть свои готовые решения по выводу красивых табличек (что, думаю, более вероятно).
 

piarche

Пользователь
Регистрация
28.04.2015
Сообщения
141
Благодарностей
5
Баллы
18
Спасибо, очень пригодилось!!!
 
  • Спасибо
Реакции: LaGir
Регистрация
29.03.2013
Сообщения
39
Благодарностей
12
Баллы
8
А можно как-то с графиками в этом направлении поработать? Например сделать себе админку как на ПП и туда агрегировать информацию с разных ПП?
 

Semyon

Client
Регистрация
19.09.2016
Сообщения
43
Благодарностей
36
Баллы
18
А можно как-то с графиками в этом направлении поработать? Например сделать себе админку как на ПП и туда агрегировать информацию с разных ПП?
а что мешает всю информацию подтягивать в базу(с разных ПП), а потом на своем сайте её загружая из базы обрабатывать уже стилями.(через тот же JS, ну или сразу через PHP) Что на JS+CSS это реализовать не сложно или через PHP+CSS, что с базой работать не сложно(сам недавно начал).
 
  • Спасибо
Реакции: LaGir

bizzon

Client
Регистрация
08.09.2015
Сообщения
1 075
Благодарностей
125
Баллы
63
Вот если бы в Google Таблицах еще.
 

VerBin

Client
Регистрация
28.05.2016
Сообщения
555
Благодарностей
454
Баллы
63
  • Спасибо
Реакции: bizzon

LaGir

Client
Регистрация
01.10.2015
Сообщения
211
Благодарностей
849
Баллы
93
А можно как-то с графиками в этом направлении поработать? Например сделать себе админку как на ПП и туда агрегировать информацию с разных ПП?
С графиками можно, но решение в статье подразумевает чисто вывод в файлы Excel (то бишь, лучше применять, когда нужна именно отчётность). Если нужно какое-либо взаимодействие, работа с админкой - как писали выше, лучше сразу всё реализовывать на PHP+HTML+CSS.
 

arhip1985

Client
Регистрация
31.10.2011
Сообщения
2 943
Благодарностей
775
Баллы
113
тема понравилась - проголосовал за твою
 
  • Спасибо
Реакции: LaGir

Hannes

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

Потратил часа три разбираясь, буду рад если сэкономлю кому-то время. :-)
 
  • Спасибо
Реакции: ZSHab и LaGir

Serjio Leone

Client
Регистрация
20.09.2017
Сообщения
114
Благодарностей
84
Баллы
28
@LaGir
Доброго времени! Кто может поделиться библиотекой EPPlus.dll на сайте не смог скачать. И актуальна ли тема на данный момент?
 

LaGir

Client
Регистрация
01.10.2015
Сообщения
211
Благодарностей
849
Баллы
93
@Serjio Leone, приветствую!
По ссылке в шапке написано, что библиотека переехала на GitHub, оттуда и можно скачать.
Про актуальность темы не совсем понял. Вроде как техническая статья, а не манимейкерская схема, к примеру, особо нечему становиться неактуальным. :-)
 
  • Спасибо
Реакции: Mikhail B. и Serjio Leone

moRL

Client
Регистрация
18.07.2016
Сообщения
101
Благодарностей
69
Баллы
28

Hannes

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

Вложения

moRL

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

Mikhail B.

Moderator
Регистрация
23.12.2014
Сообщения
14 336
Благодарностей
5 421
Баллы
113
@LaGir помоги, пожалуйста, разобраться - у меня 43к строк переносятся примерно 1ч. Таблица привязана к файлу стандартным экшеном "Операции с таблицами - привязать к файлу", так как постоянно с новым названием. Как можно ускорить процесс?
Откуда переносится? Попробуй сохранять данные в список. А потом через экшн операции над текстом (в таблицу) импортировать данные.
 

moRL

Client
Регистрация
18.07.2016
Сообщения
101
Благодарностей
69
Баллы
28
Откуда переносится? Попробуй сохранять данные в список. А потом через экшн операции над текстом (в таблицу) импортировать данные.
В процессе парсинга создается таблица csv без форматирования, этот файл привязывается к таблице и оттуда уже идет формирование xls файла с форматированием.
 

Кто просматривает тему: (Всего: 1, Пользователи: 0, Гости: 1)