вторник, 10 февраля 2015 г.

Simple Excel report generation with OpenXML.


I'd needed in simple solution to export a search results into Excel in .net application.

At first i create ViewModel using simple types only:
 public class MyReportItem
    {
        public string ObjectType{ get; set; }
        public string Name{ get; set; }
        public string Owner{ get; set; }
        public string District{ set; get; }
        public string Street{ set; get; }
        public string Address{ get; set; }
        public string ObjectContacts{ get; set; }
        public string Specilization{ get; set; }
    }

My objective - create a simple report so i don't want use document template. So i create excel document by OpenXML in stream.

       public MemoryStream CreateReport(List report)
       {
           using (var reader = new MemoryStream())
           {
               var spreadsheetDocument = SpreadsheetDocument.
                   Create(reader, SpreadsheetDocumentType.Workbook);

               var workbookpart = spreadsheetDocument.AddWorkbookPart();
               workbookpart.Workbook = new Workbook();

               var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart();
               stylesPart.Stylesheet = GenerateStyleSheet();

               stylesPart.Stylesheet.Save();

               var worksheetPart = workbookpart.AddNewPart();

               var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());

               var sheet = new Sheet
                   {
                   Id = spreadsheetDocument.WorkbookPart.
                       GetIdOfPart(worksheetPart),
                   SheetId = 1,
                   Name = "My sheet"
               };
               sheets.Append(sheet);

               var sd = new SheetData();
               var ws = new Worksheet();
               var columns = new Columns();


               columns.Append(CreateColumnData(1, 35, 30));
               ws.Append(columns);
               ws.Append(sd);

               worksheetPart.Worksheet = ws;
                GenerateColumnsCaptions(ws, report);

               workbookpart.Workbook.Save();

               spreadsheetDocument.Close();

               return reader;
           }

Part with columns not required but i want to set a columns width. and here is method to add Columnto Columns:

        public static Column CreateColumnData(UInt32 startColumnIndex, UInt32 endColumnIndex, double columnWidth)
        {
            var column = new Column
            {
                Min = startColumnIndex,
                Max = endColumnIndex,
                Width = columnWidth,
                CustomWidth = true
            };
            return column;
        }

Here is interesting thing. Columns in Excel table make only visual aspect of table, for setting widht of cells in this column. More, if your cell have a address A4 it's mean that cell located in fourth row, and in this row this cell located on foremost. Columns not binded this cell.

So now we have a empty document, let's fill it with data:

       public Worksheet GenerateColumnsCaptions(Worksheet ws, ReportExcel report)
       {
           var sheetData = ws.GetFirstChild();
           uint rowIntIter = 1; //row iterator
           var colCharIter = 'A'; //column iterator

           var row = new Row { RowIndex = 1 };

           var fields = typeof (MyReportItem).GetFields(
               BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
           var names = Array.ConvertAll(fields, field => field.Name).ToList();

           var trimedNames = names.Select(name => name.Substring(1, name.IndexOf(">", StringComparison.Ordinal)-1)).ToList();
           foreach (var fieldName in trimedNames)
           {
               var newCell = new Cell { CellReference = colCharIter + rowIntIter.ToString(CultureInfo.InvariantCulture), StyleIndex = 1 };
               row.AppendChild(newCell);
               newCell.CellValue = new CellValue(fieldName);
               newCell.DataType = new EnumValue(CellValues.String);

               colCharIter++; // next column
           }

           sheetData.Append(row);

           colCharIter = 'A'; // reset column iterator
           foreach (var объект in report.Объекты)
           {
               rowIntIter++; // next row
               var rowItem = new Row { RowIndex = rowIntIter };

               Type t = объект.Объект.GetType();
               foreach (PropertyInfo info in t.GetProperties())
               {
                   var value = info.GetValue(объект.Объект, null);
                   var valueStr = "-";

                   if (value != null)
                   {
                       valueStr = value.ToString();

                       if (value is DateTime)
                       {
                           var date = (DateTime)value;
                           valueStr = date.ToShortDateString();
                       }
                       if (value is bool)
                       {
                           var date = (bool)value;

                           valueStr = date ? "Yes" : "No";
                       }
                   }
                   rowItem.AppendChild(ReportsRoutines.CreateCell(valueStr, colCharIter + rowIntIter.ToString(CultureInfo.InvariantCulture)));
                   colCharIter++;
               }

               sheetData.Append(rowItem);

               colCharIter = 'A'; // finished with this object so reset column iterator
           }
           return ws;
       }

At first create row with columns captions:

      var colCharIter = 'A';
      var row = new Row { RowIndex = 1 };

      var fields = typeof (MyReportItem).GetFields(
      BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
      var names = Array.ConvertAll(fields, field => field.Name).ToList();

      var trimedNames = names.Select(name => name.Substring(1, name.IndexOf(">", StringComparison.Ordinal)-1)).ToList();
      foreach (var fieldName in trimedNames)
      {
         var newCell = new Cell { CellReference = colCharIter + rowIntIter.ToString(CultureInfo.InvariantCulture), StyleIndex = 1 };
         row.AppendChild(newCell);
         newCell.CellValue = new CellValue(fieldName);
         newCell.DataType = new EnumValue(CellValues.String);

         colCharIter++;
       }

       sheetData.Append(row);

Here i collect my view model's fields names and after trimming garabage setting it one by one in row's cells. Fields are taken in the order in which they appear in class. And if you waht to reorder columns you need just reorder fields in class.

After i getting started to fill document with data:

     colCharIter = 'A';
     foreach (var obj in report)
     {
        rowIntIter++;
        var rowItem = new Row { RowIndex = rowIntIter };

        Type t = obj .GetType();
        foreach (PropertyInfo info in t.GetProperties())
        {
           var value = info.GetValue(obj, null);
           var valueStr = "-";

           if (value != null)
           {
              valueStr = value.ToString();

              if (value is DateTime)
              {
                  var date = (DateTime)value;
                  valueStr = date.ToShortDateString();
               }
               if (value is bool)
               {
                  var date = (bool)value;

                  valueStr = date ? "Yes" : "No";
                }
            }
            rowItem.AppendChild(ReportsRoutines.CreateCell(valueStr, colCharIter + rowIntIter.ToString(CultureInfo.InvariantCulture)));
                   colCharIter++;
          }

          sheetData.Append(rowItem);

          colCharIter = 'A';
        }

Here i iterate through view model fileds and take it's values.

After you can save document using something like:

    var saveFileDialog = new SaveFileDialog { Filter = "*.xlsx|*.xlsx", Title = "Save as..." };
    File.WriteAllBytes(saveFileDialog.FileName, reader.ToArray());

Комментариев нет:

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