Exemplo n.º 1
0
        /// <summary>
        /// Creates a new sheet with headers based on objects in data list.
        /// </summary>
        /// <param name="xl">ExcelPackage for creating worksheet</param>
        /// <param name="sheet">The XlSheet object containing the sheet data.</param>
        /// <returns>The newly created worksheet</returns>
        public static ExcelWorksheet CreateSheet(ExcelPackage xl, XlSheet sheet)
        {
            // Create worksheet, set header style
            var worksheet   = xl.Workbook.Worksheets.Add(sheet.Name);
            var headerStyle = worksheet.Row(1).Style;

            headerStyle.Font.Bold           = true;
            headerStyle.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            var data = sheet.Data().ToList();

            // Gets object properties based on type.
            var type  = data[0].GetType();
            var props = type.GetProperties().Where(p => !p.XlIgnore(type)).ToList();

            // Loops through properties and adds property name or Display Name as column header.
            for (var col = 1; col <= props.Count; col++)
            {
                var prop = props[col - 1];

                var displayName = prop.GetDisplayName(type);
                if (!string.IsNullOrEmpty(displayName))
                {
                    worksheet.Cells[1, col].Value = displayName;
                }
                else
                {
                    worksheet.Cells[1, col].Value = props[col - 1].Name;
                }
            }

            return(worksheet);
        }
Exemplo n.º 2
0
        /// <summary>
        /// Adds rows of data to sheet
        /// </summary>
        /// <param name="worksheet">The worksheet object to add rows to.</param>
        /// <param name="data">The data to add to the sheet.</param>
        /// <returns>The resulting worksheet.</returns>
        private ExcelWorksheet AddRows(ExcelWorksheet worksheet, XlSheet data)
        {
            var sheetList = data.Data().ToList();
            var baseType  = sheetList[0].GetType();
            var props     = baseType.GetProperties().Where(p => !p.XlIgnore(baseType)).ToList();

            var firstBlank = worksheet.Dimension.Rows + 1;
            var row        = firstBlank;

            for (var i = 0; i < sheetList.Count; i++)
            {
                for (var col = 1; col <= props.Count; col++)
                {
                    worksheet.Cells[row, col].Value = props[col - 1].GetValue(sheetList[row - firstBlank]);
                }
                row++;
            }

            var formatCol = 1;

            foreach (var prop in props)
            {
                var dataType = prop.GetDataType(baseType);
                if (dataType == DataType.Date || dataType == DataType.DateTime)
                {
                    worksheet.Cells[2, formatCol, sheetList.Count + 1, formatCol].Style.Numberformat.Format = "mm/dd/yyyy hh:mm";
                }
                formatCol++;
            }

            return(worksheet);
        }