//private SheetData CreateSheetData<T>(List<T> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
        //{
        //    SheetData sheetData = new SheetData();

        //    if (objects != null)
        //    {
        //        List<string> fields = ObjUtility.GetPropertyInfo<T>();

        //        var az = new List<Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());

        //        List<Char> headers = az.GetRange(0, fields.Count);

        //        Row header = new Row();

        //        int index = 1;

        //        header.RowIndex = (uint)index;

        //        foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
        //        {
        //            HeaderCell c = new HeaderCell(headers[headerTitles.ToList().IndexOf(keyValuePair)].ToString(),
        //                                            keyValuePair.Value,
        //                                            index,
        //                                            stylesPart.Stylesheet,
        //                                            System.Drawing.Color.DodgerBlue,
        //                                            12,
        //                                            true);

        //            header.Append(c);
        //        }

        //        sheetData.Append(header);

        //        for (int i = 0; i < objects.Count; i++)
        //        {
        //            index++;

        //            var selectedObj = objects[i];

        //            var r = new Row { RowIndex = (uint)index };

        //            int headerIndex = 0;

        //            foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
        //            {
        //                PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);

        //                if (myf != null)
        //                {
        //                    object obj = myf.GetValue(selectedObj, null);
        //                    if (obj != null)
        //                    {
        //                        if (obj.GetType() == typeof(string))
        //                        {
        //                            TextCell c = new TextCell(headers[headerIndex].ToString(),
        //                            obj.ToString(),
        //                            index);

        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(bool))
        //                        {
        //                            string value = (bool)obj ? "Yes" : "No";
        //                            TextCell c = new TextCell(headers[headerIndex].ToString(),
        //                            value,
        //                            index);

        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(DateTime))
        //                        {
        //                            string value = ((DateTime)obj).ToOADate().ToString();
        //                            DateCell c = new DateCell(headers[headerIndex].ToString(),
        //                            (DateTime)obj,
        //                            index);

        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double))
        //                        {
        //                            FormatedNumberCell c = new FormatedNumberCell(headers[headerIndex].ToString(),
        //                            obj.ToString(),
        //                            index);

        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(Dictionary<string, string>) && keyValuePair.HeaderType == ExcelHeaderType.ListProperty)
        //                        {
        //                            Dictionary<string, string> objList = (obj as Dictionary<string, string>);

        //                            foreach (var VARIABLE in objList)
        //                            {
        //                                TextCell c = new TextCell(
        //                                    headers[headerIndex].ToString(),
        //                                    VARIABLE.Value,
        //                                    index);
        //                                r.Append(c);

        //                                HeaderCell hc = new HeaderCell(
        //                                    headers[headerIndex].ToString(),
        //                                    VARIABLE.Key,
        //                                    1,
        //                                    stylesPart.Stylesheet,
        //                                    System.Drawing.Color.Brown,
        //                                    12,
        //                                    true);

        //                                header.Append(hc);

        //                                headerIndex++;
        //                            }

        //                        }
        //                        else
        //                        {
        //                            long value;
        //                            if (long.TryParse(obj.ToString(), out value))
        //                            {
        //                                NumberCell c = new NumberCell(headers[headerIndex].ToString(),
        //                                obj.ToString(),
        //                                index);

        //                                r.Append(c);
        //                            }
        //                            else
        //                            {
        //                                TextCell c = new TextCell(headers[headerIndex].ToString(),
        //                                obj.ToString(),
        //                                index);

        //                                r.Append(c);
        //                            }
        //                        }
        //                    }
        //                }
        //                headerIndex++;
        //            }
        //            sheetData.Append(r);
        //        }

        //        index++;
        //    }

        //    return sheetData;
        //}

        private SheetData CreateSheetData(IReadOnlyList <object> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
        {
            var sheetData = new SheetData();

            if (objects != null)
            {
                var header = new Row();
                var index  = 1;

                header.RowIndex = (uint)index;

                foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
                {
                    HeaderCell c = new HeaderCell(Utility.IntToAlpha(headerTitles.ToList().IndexOf(keyValuePair) + 1),
                                                  keyValuePair.Value,
                                                  index,
                                                  stylesPart.Stylesheet,
                                                  System.Drawing.Color.DodgerBlue,
                                                  12,
                                                  true);

                    header.Append(c);
                }

                sheetData.Append(header);

                for (int i = 0; i < objects.Count; i++)
                {
                    index++;

                    var selectedObj = objects[i];

                    var row = new Row {
                        RowIndex = (uint)index
                    };

                    int headerIndex = 1;

                    foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
                    {
                        PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);

                        if (myf != null)
                        {
                            object obj = myf.GetValue(selectedObj, null);
                            if (obj != null)
                            {
                                CreateDataCell(row, obj, headerIndex, ref index);
                                headerIndex++;
                            }
                        }
                    }
                    sheetData.Append(row);
                }

                index++;
            }

            return(sheetData);
        }
Example #2
0
 public static List <T> ReadObjFromExel <T>(Stream stream, ExcelHeaderList pattern) where T : new()
 {
     return(ReadObjFromExel <T>(stream, pattern, ""));
 }
Example #3
0
        // key      : custom header name
        // value    : property original name
        public static List <T> ReadObjFromExel <T>(Stream stream, ExcelHeaderList pattern, string sheetName) where T : new()
        {
            var result = new List <T>();

            var columnHeaders = GetRowValues(stream, sheetName, "1");

            var hasHeader = columnHeaders.Any(header => pattern != null && pattern.Any(i => i.Key.ToLower().Trim() == header.ToLower().Trim()));

            var excelHeaders = new List <string>();

            for (var i = 1; i <= columnHeaders.Count; i++)
            {
                excelHeaders.Add(Utility.IntToAlpha(i));
            }

            var rowsCount = excelHeaders.Select(header => GetColumnValues(stream, sheetName, header.ToString()).Count).Concat(new[] { 0 }).Max();

            for (var i = hasHeader ? 1 : 0; i < rowsCount; i++)
            {
                var obj = new T();

                #region has header
                if (hasHeader)
                {
                    foreach (var propName in columnHeaders.Where(p => pattern.Any(c => c.Key.ToLower() == p.ToLower())))
                    {
                        var prop = "";

                        var singleOrDefault = pattern.SingleOrDefault(k => k.Key.ToLower() == propName.ToLower());
                        if (singleOrDefault != null)
                        {
                            prop = singleOrDefault.Value;
                        }

                        var value = GetCellData(stream, sheetName,
                                                string.Format("{0}{1}", excelHeaders[columnHeaders.IndexOf(propName)],
                                                              i + 1));

                        if (string.IsNullOrEmpty(prop))
                        {
                            continue;
                        }
                        try
                        {
                            var propertyInfo = obj.GetType().GetProperty(prop);
                            propertyInfo.SetValue(obj, value, null);
                        }
                        catch (Exception)
                        { }
                    }
                }
                #endregion

                #region has not hrader
                else
                {
                    var values = GetRowValues(stream, sheetName, (i + 1).ToString());

                    for (var j = 0; j < pattern.Count; j++)
                    {
                        var prop = pattern[j].Value;

                        try
                        {
                            var propertyInfo = obj.GetType().GetProperty(prop);
                            propertyInfo.SetValue(obj, values[j], null);
                        }
                        catch (Exception) { }
                    }
                }
                #endregion

                result.Add(obj);
            }

            return(result);
        }
Example #4
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="sheetName">generated sheet name, Do not set empty value for this parameters</param>
        /// <param name="headerNames"></param>
        /// <returns></returns>
        public Stream Do <T>(List <T> objects, string sheetName, ExcelHeaderList headerNames)
        {
            var stream = new MemoryStream();

            sheetName = string.IsNullOrEmpty(sheetName) ? "Mayhedi_Sheet" : sheetName;

            using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                var workbookPart  = document.AddWorkbookPart();
                var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(sheetName);

                // Create Styles and Insert into Workbook
                var        stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
                Stylesheet styles     = new CustomStylesheet();
                styles.Save(stylesPart);

                var relId = workbookPart.GetIdOfPart(worksheetPart);

                var workbook    = new Workbook();
                var fileVersion = new FileVersion {
                    ApplicationName = "Microsoft Office Excel"
                };

                var data = objects.Select(o => (object)o).ToList();

                if (headerNames == null)
                {
                    headerNames = new ExcelHeaderList();
                    foreach (var o in ObjUtility.GetPropertyInfo(objects[0]))
                    {
                        headerNames.Add(o, o);
                    }
                }

                var sheetData = CreateSheetData(data, headerNames, stylesPart);

                var worksheet = new Worksheet();

                var numCols = headerNames.Count;
                var width   = 20;//headerNames.Max(h => h.Length) + 5;

                var columns = new Columns();
                for (var col = 0; col < numCols; col++)
                {
                    var c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);

                    if (c != null)
                    {
                        columns.Append(c);
                    }
                }
                worksheet.Append(columns);

                var sheets = new Sheets();
                var sheet  = new Sheet {
                    Name = sheetName, SheetId = 1, Id = relId
                };

                sheets.Append(sheet);
                workbook.Append(fileVersion);
                workbook.Append(sheets);

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                document.WorkbookPart.Workbook = workbook;
                document.WorkbookPart.Workbook.Save();
                document.Close();
            }

            return(stream);
        }
        //private SheetData CreateSheetData<T>(List<T> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
        //{
        //    SheetData sheetData = new SheetData();
        //    if (objects != null)
        //    {
        //        List<string> fields = ObjUtility.GetPropertyInfo<T>();
        //        var az = new List<Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
        //        List<Char> headers = az.GetRange(0, fields.Count);
        //        Row header = new Row();
        //        int index = 1;
        //        header.RowIndex = (uint)index;
        //        foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
        //        {
        //            HeaderCell c = new HeaderCell(headers[headerTitles.ToList().IndexOf(keyValuePair)].ToString(),
        //                                            keyValuePair.Value,
        //                                            index,
        //                                            stylesPart.Stylesheet,
        //                                            System.Drawing.Color.DodgerBlue,
        //                                            12,
        //                                            true);
        //            header.Append(c);
        //        }
        //        sheetData.Append(header);
        //        for (int i = 0; i < objects.Count; i++)
        //        {
        //            index++;
        //            var selectedObj = objects[i];
        //            var r = new Row { RowIndex = (uint)index };
        //            int headerIndex = 0;
        //            foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
        //            {
        //                PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);
        //                if (myf != null)
        //                {
        //                    object obj = myf.GetValue(selectedObj, null);
        //                    if (obj != null)
        //                    {
        //                        if (obj.GetType() == typeof(string))
        //                        {
        //                            TextCell c = new TextCell(headers[headerIndex].ToString(),
        //                            obj.ToString(),
        //                            index);
        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(bool))
        //                        {
        //                            string value = (bool)obj ? "Yes" : "No";
        //                            TextCell c = new TextCell(headers[headerIndex].ToString(),
        //                            value,
        //                            index);
        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(DateTime))
        //                        {
        //                            string value = ((DateTime)obj).ToOADate().ToString();
        //                            DateCell c = new DateCell(headers[headerIndex].ToString(),
        //                            (DateTime)obj,
        //                            index);
        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double))
        //                        {
        //                            FormatedNumberCell c = new FormatedNumberCell(headers[headerIndex].ToString(),
        //                            obj.ToString(),
        //                            index);
        //                            r.Append(c);
        //                        }
        //                        else if (obj.GetType() == typeof(Dictionary<string, string>) && keyValuePair.HeaderType == ExcelHeaderType.ListProperty)
        //                        {
        //                            Dictionary<string, string> objList = (obj as Dictionary<string, string>);
        //                            foreach (var VARIABLE in objList)
        //                            {
        //                                TextCell c = new TextCell(
        //                                    headers[headerIndex].ToString(),
        //                                    VARIABLE.Value,
        //                                    index);
        //                                r.Append(c);
        //                                HeaderCell hc = new HeaderCell(
        //                                    headers[headerIndex].ToString(),
        //                                    VARIABLE.Key,
        //                                    1,
        //                                    stylesPart.Stylesheet,
        //                                    System.Drawing.Color.Brown,
        //                                    12,
        //                                    true);
        //                                header.Append(hc);
        //                                headerIndex++;
        //                            }
        //                        }
        //                        else
        //                        {
        //                            long value;
        //                            if (long.TryParse(obj.ToString(), out value))
        //                            {
        //                                NumberCell c = new NumberCell(headers[headerIndex].ToString(),
        //                                obj.ToString(),
        //                                index);
        //                                r.Append(c);
        //                            }
        //                            else
        //                            {
        //                                TextCell c = new TextCell(headers[headerIndex].ToString(),
        //                                obj.ToString(),
        //                                index);
        //                                r.Append(c);
        //                            }
        //                        }
        //                    }
        //                }
        //                headerIndex++;
        //            }
        //            sheetData.Append(r);
        //        }
        //        index++;
        //    }
        //    return sheetData;
        //}
        private SheetData CreateSheetData(IReadOnlyList<object> objects, ExcelHeaderList headerTitles, WorkbookStylesPart stylesPart)
        {
            var sheetData = new SheetData();

            if (objects != null)
            {
                var header = new Row();
                var index = 1;

                header.RowIndex = (uint)index;

                foreach (ExcelHeader keyValuePair in headerTitles.Where(i => i.HeaderType == ExcelHeaderType.SingleProperty))
                {
                    HeaderCell c = new HeaderCell(Utility.IntToAlpha(headerTitles.ToList().IndexOf(keyValuePair) + 1),
                                                    keyValuePair.Value,
                                                    index,
                                                    stylesPart.Stylesheet,
                                                    System.Drawing.Color.DodgerBlue,
                                                    12,
                                                    true);

                    header.Append(c);
                }

                sheetData.Append(header);

                for (int i = 0; i < objects.Count; i++)
                {
                    index++;

                    var selectedObj = objects[i];

                    var row = new Row { RowIndex = (uint)index };

                    int headerIndex = 1;

                    foreach (ExcelHeader keyValuePair in headerTitles.OrderBy(h => (byte)h.HeaderType))
                    {
                        PropertyInfo myf = selectedObj.GetType().GetProperty(keyValuePair.Key);

                        if (myf != null)
                        {
                            object obj = myf.GetValue(selectedObj, null);
                            if (obj != null)
                            {
                                CreateDataCell(row, obj, headerIndex, ref index);
                                headerIndex++;
                            }
                        }
                    }
                    sheetData.Append(row);
                }

                index++;
            }

            return sheetData;
        }