Создание файла excel с помощью библиотеки ClosedXML
Библиотека - Assembly: ClosedXML, Version=0.93.1.0, Culture=neutral, PublicKeyToken=null.
Ее можно установить с помощью Nuget менеджера в visual studio.
Создаём сам генератор excel:
public class FileDataRepresentativeExcelGenerator
{
public virtual byte[] Generate(FileDataRepresentative data, string workSheetName)
{
var amountDocuments = data.ValuesArray.Length;
var sum = data.ValuesArray.Sum(s => decimal.Parse(s["DocumentSumPrice"]));
using (IXLWorkbook workbook = new XLWorkbook())
{
using (var worksheet = workbook.AddWorksheet(workSheetName))
{
var columnMap = new Dictionary<string, int>();
var columnCounter = 0;
foreach (var def in data.ColumnDefinitions)
// создаём колонку из определений только если она есть в каком-то объекте
if (data.ValuesArray.Any(r => r.Keys.Contains(def.ColumnName)))
columnMap.Add(def.ColumnName, ++columnCounter);
// Имя отчёта
var row = worksheet.Row(1);
row.Cell(1).SetValue(data.Name);
worksheet.Range(1, 1, 1, 6).Merge().Style
.Font.SetFontSize(18.0)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
row = row.RowBelow();
// Описание применённых фильтров из ColumnDefinitions
foreach (var filterDescription in data.ColumnDefinitions
.Where(cd => !string.IsNullOrWhiteSpace(cd.FilterDescription))
.Select(cd => cd.FilterDescription))
{
row = row.RowBelow();
row.Cell(1).SetValue(filterDescription).Style.Font.SetItalic(true);
}
var numberOfRowFilterStart = row.RowNumber() + 1;
// Описание применённых фильтров из FilterDefinitions
if (data.FilterDefinitions != null)
foreach (var filterDefinition in data.FilterDefinitions
.Where(fd => !string.IsNullOrEmpty(fd.Value)))
{
row = row.RowBelow();
row.Cells().Style.Alignment.SetWrapText(true);
worksheet.Range(row.RowNumber(), 3, row.RowNumber(), 10).Merge();
worksheet.Range(row.RowNumber(), 1, row.RowNumber(), 2).Merge();
row.Cell(1).SetValue(filterDefinition.Name).Style.Font.SetBold(true).Alignment
.Horizontal = XLAlignmentHorizontalValues.Right;
row.Cell(3).SetValue(filterDefinition.Value).Style.Alignment.Horizontal =
XLAlignmentHorizontalValues.Left;
var width = row.Cell(3).WorksheetColumn().Width;
worksheet.Range(row.RangeAddress.FirstAddress.RowNumber, 1,
row.RangeAddress.FirstAddress.RowNumber,
3).Style
.Alignment.SetWrapText(true)
.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
row.Worksheet.Columns().AdjustToContents();
}
var numberOfRowFilterStop = row.RowNumber();
row = row.RowBelow();
row = row.RowBelow();
worksheet.Range(row.RowNumber(), 1, row.RowNumber(), 2).Merge();
row.Cell(1).SetValue("Всего накладных, шт.:").Style.Font.SetBold(true).Alignment
.Horizontal =
XLAlignmentHorizontalValues.Right;
row.Cell(3).SetValue(amountDocuments).Style.Font.SetBold(true).Alignment.Horizontal =
XLAlignmentHorizontalValues.Right;
row = row.RowBelow();
worksheet.Range(row.RowNumber(), 1, row.RowNumber(), 2).Merge();
row.Cell(1).SetValue("Сумма по накладным, ₽:").Style.Font.SetBold(true).Alignment
.Horizontal =
XLAlignmentHorizontalValues.Right;
row.Cell(3).SetValue(sum.ToString("N")).Style.Font.SetBold(true).Alignment.Horizontal =
XLAlignmentHorizontalValues.Right;
row = row.RowBelow();
// Заголовок таблицы
row = row.RowBelow();
var headerRow = row.RangeAddress.FirstAddress.RowNumber;
foreach (var columnInfo in columnMap)
row.Cell(columnInfo.Value)
.SetValue(
data.ColumnDefinitions.First(c => c.ColumnName == columnInfo.Key).SemanticName).Style
.Font
.SetBold(true);
// Данные
foreach (var element in data.ValuesArray)
{
row = row.RowBelow();
foreach (var field in element)
row.Cell(columnMap[field.Key]).SetValue(field.Value).Style.Alignment.Horizontal =
XLAlignmentHorizontalValues.Center;
}
// Ширина столбцов
foreach (var columnInfo in columnMap)
{
// Минимальную ширину делаем по длине самого длинного слова в столбце, чтобы не было страшных переносов
var words = new List();
words.AddRange(data.ValuesArray.Select(r => r[columnInfo.Key])
.SelectMany(s => s.Split(new[] {' '}, StringSplitOptions.RemoveEmptyEntries)));
words.AddRange(data.ColumnDefinitions.First(c => c.ColumnName == columnInfo.Key)
.SemanticName.Split(new[] {' '}, StringSplitOptions.RemoveEmptyEntries));
var maxWordLength = words.Max(w => CalculateLengthInPixels(w));
var minWidth = PixelsToExcelUnits(maxWordLength) * 1.05; // что-то не всегда влезает
// Максимальную ширину устанавливаем чтобы строки средней длины влезали без переноса
var averageWidth = data.ValuesArray.Average(r => CalculateLengthInPixels(r[columnInfo.Key]));
var maxWidth = PixelsToExcelUnits(averageWidth);
worksheet.Column(columnInfo.Value)
.AdjustToContents(minWidth, maxWidth > minWidth ? maxWidth : minWidth);
}
// Форматирование заголовка таблицы
worksheet.Range(headerRow, 1, headerRow, columnMap.Count).Style
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center)
.Alignment.SetWrapText(true)
.Border.SetTopBorder(XLBorderStyleValues.Medium)
.Border.SetRightBorder(XLBorderStyleValues.Medium)
.Border.SetBottomBorder(XLBorderStyleValues.Medium)
.Border.SetLeftBorder(XLBorderStyleValues.Medium);
// Форматирование списка
worksheet.Range(headerRow + 1, 1, headerRow + 1 + data.ValuesArray.Length - 1,
columnMap.Max(kvp => kvp.Value)).Style
.Alignment.SetWrapText(true)
.Alignment.SetVertical(XLAlignmentVerticalValues.Top)
.Border.SetTopBorder(XLBorderStyleValues.Thin)
.Border.SetRightBorder(XLBorderStyleValues.Thin)
.Border.SetBottomBorder(XLBorderStyleValues.Thin)
.Border.SetLeftBorder(XLBorderStyleValues.Thin);
IXLRow rowOfFilter;
var widthRandgedCells = worksheet.Column(3).Width+ worksheet.Column(4).Width + worksheet.Column(5).Width + worksheet.Column(6).Width +
worksheet.Column(7).Width + worksheet.Column(8).Width + worksheet.Column(9).Width + worksheet.Column(10).Width;
for (int i = numberOfRowFilterStart; i <= numberOfRowFilterStop; i++)
{
rowOfFilter = worksheet.Row(numberOfRowFilterStart);
rowOfFilter.Height = MeasureTextHeight(rowOfFilter.Cell(3).Value.ToString(), new Font(
XLWorkbook.DefaultStyle.Font.FontName,
(float) XLWorkbook.DefaultStyle.Font.FontSize), widthRandgedCells);
}
using (var ms = new MemoryStream())
{
workbook.SaveAs(ms);
return ms.ToArray();
}
}
}
}
private static double PixelsToExcelUnits(float pixels)
{
if (pixels < 0)
return 0;
if (pixels > 10000)
return ColumnWidth.MAX;
var zerosString = string.Empty;
do
{
//Ширина столбца в экселе это количество символов дефолтного шрифта, которое поместится в эту ширину
zerosString += '0';
} while (pixels > CalculateLengthInPixels(zerosString));
return zerosString.Length;
}
private static float CalculateLengthInPixels(string s)
{
if (string.IsNullOrEmpty(s))
return 0;
var fontName = XLWorkbook.DefaultStyle.Font.FontName;
var fontSize = XLWorkbook.DefaultStyle.Font.FontSize;
var bitmap = new Bitmap(1, 1);
using (var graphics = Graphics.FromImage(bitmap))
{
var width = MeasureDisplayStringWidth(graphics, s, new Font(fontName, (float) fontSize));
return width;
}
}
private static int MeasureDisplayStringWidth(Graphics graphics, string text, Font font)
{
var format = new StringFormat();
var rect = new RectangleF(0, 0,
1000, 1000);
CharacterRange[] ranges = {new CharacterRange(0, text.Length)};
format.SetMeasurableCharacterRanges(ranges);
var regions = graphics.MeasureCharacterRanges(text, font, rect, format);
rect = regions[0].GetBounds(graphics);
return (int) (rect.Right + 1.0f);
}
//Определение высоты строки по содержимому
public double MeasureTextHeight(string text, Font font, double width)
{
if (string.IsNullOrEmpty(text)) return 0.0;
using (var bitmap = new Bitmap(1, 1))
{
using (var graphics = Graphics.FromImage(bitmap))
{
var pixelWidth = Convert.ToInt32(width * 7); //7 pixels per excel column width
var fontSize = font.Size * 1.01f;
var drawingFont = new Font(font.Name, fontSize);
var size = graphics.MeasureString(text, drawingFont, pixelWidth,
new StringFormat {FormatFlags = StringFormatFlags.MeasureTrailingSpaces});
//72 DPI and 96 points per inch. Excel height in points with max of 409 per Excel requirements.
return Math.Min(Convert.ToDouble(size.Height) * 72 / 96, 409);
}
}
}
}
Тепрерь класс, который ожидает генератор в первом параметре:
public class FileDataRepresentative: IFileDataRepresentative
{
public string Name { get; set; }
public FilterDefinition[] FilterDefinitions { get; set; }
public ColumnDefinition[] ColumnDefinitions { get; set; }
public Dictionary<string, string>[] ValuesArray { get; set; }
}
public class FilterDefinition
{
public string Name { get; set; }
public string Value { get; set; }
}
public class ColumnDefinition
{
public string ColumnName { get; set; }
public string ColumnType { get; set; }
public string ColumnFormat { get; set; }
public string SemanticName { get; set; }
public string FilterDescription { get; set; }
}
Для создания экземпляра и вызов метода генератора с параметрами используем, тесты microsoft:
[TestClass()]
public class FileDataRepresentativeExcelGeneratorTests
{
[TestMethod()]
public void GenerateTest()
{
//Arrange
var columnsRepresentation = JsonConvert.DeserializeObject(File.ReadAllText("../../StaticFiles/FileDataRepresentative.json", Encoding.UTF8));
//Action
byte[] result =
new FileDataRepresentativeExcelGenerator().Generate(columnsRepresentation, "Тестовый рабочий лист.");
File.WriteAllBytes("FileDataRepresentative.xlsx", result);
//Assert
Assert.IsTrue(File.Exists("FileDataRepresentative.xlsx"));
//для того чтобы файл удалялся после прохождения, раскоментировать.
//Assert.IsTrue(DeleteFile("FileDataRepresentative.xlsx"));
}
private bool DeleteFile(string path)
{
try
{
if (File.Exists(path))
{
File.Delete(path);
return true;
}
return false;
}
catch
{
return false;
}
}
}
Содержание файла json, который серриализуется в нужный объект ниже:
{
"Name": "Сведения территорям средиземноморья",
"ColumnDefinitions": [
{
"ColumnName": "DocumentNumber",
"ColumnType": "text",
"ColumnFormat": null,
"SemanticName": "Номер доки",
"FilterDescription": null
},
{
"ColumnName": "Supplier",
"ColumnType": "text",
"ColumnFormat": null,
"SemanticName": "Эльф.поставщик",
"FilterDescription": null
},
{
"ColumnName": "Receiver",
"ColumnType": "text",
"ColumnFormat": null,
"SemanticName": "Получатель",
"FilterDescription": null
},
{
"ColumnName": "DocumentCreated",
"ColumnType": "datetime",
"ColumnFormat": "dd.MM.yyyy",
"SemanticName": "Дата накладной",
"FilterDescription": null
},
{
"ColumnName": "Loaded",
"ColumnType": "datetime",
"ColumnFormat": "dd.MM.yyyy",
"SemanticName": "Дата загрузки документа",
"FilterDescription": null
},
{
"ColumnName": "Accepted",
"ColumnType": "datetime",
"ColumnFormat": "dd.MM.yyyy",
"SemanticName": "Дата активации",
"FilterDescription": null
},
{
"ColumnName": "DocumentSumPrice",
"ColumnType": "decimal",
"ColumnFormat": null,
"SemanticName": "Сумма по накладной",
"FilterDescription": null
}
],
"ValuesArray": [
{
"Supplier": "РАС ОГАУ «Гномодом»",
"Receiver": "ГБУЗ «МОЦ профилактики и борьбы с Санта Клаусом»»",
"DocumentNumber": "1",
"DocumentCreated": "25.01.2017",
"Loaded": "25.01.2017",
"Accepted": "25.01.2017",
"DocumentSumPrice": "161.61"
},
{
"Supplier": "РАС ОГАУ «Слизерен 2»",
"Receiver": "ГБУЗ «МОЦ профилактики и борьбы с Санта Клаусом»",
"DocumentNumber": "1",
"DocumentCreated": "25.01.2017",
"Loaded": "25.01.2017",
"Accepted": "25.01.2017",
"DocumentSumPrice": "100.09"
}
],
"FilterDefinitions": [
{
"Name": "Муниципальное образование:",
"Value": "Урюписк, Мордор, Шир, Тихий район, Тартарский район, Урюписк, Мордор, Шир, Тихий район, Тартарский район,Урюписк, Мордор, Шир, Тихий район, Тартарский район,Урюписк, Мордор, Шир, Тихий район, Тартарский район,Урюписк, Мордор, Шир, Тихий район, Тартарский район,"
},
{
"Name": "Дата накладной с-по:",
"Value": "10.10.2015 - 10.10.2020"
}
]
}
Результат Excel:
