All infos - Создание файла excel с помощью библиотеки ClosedXML
08
авг

Создание файла 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:

excel example

Share this post

Оставить комментарий

Убедитесь, что Вы ввели всю требуемую информацию, в поля, помеченные звёздочкой (*). HTML код не допустим.