ExcelHelper

 #region Assembly DocumentFormat.OpenXml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

/*-----------------------------------------------------------------

File Name       :  ExcelHelper.cs

Created By      :  

Created on      :  

Revision History:  

------------------------------------------------------------------*/

#endregion

using System;

using System.Data;

using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using System.Collections.Generic;

using System.Linq;

using System.Reflection;

namespace InsiderTrading.Infrastructure.ExcelHelper

{

    public sealed class ExcelHelper : IExcelHelperContract

    {

        #region     Variable   and   Objects 

        DataSet dataSet;

        ExcelStatus status = new ExcelStatus();

        #endregion

        #region  Public Methods 

        /// <summary>

        /// programmatically insert a worksheet to WorkBook

        /// </summary>

        /// <param name="fileName">Pass the location of the WorkBook</param>

        /// <param name="NewSheetName">Pass the New Name of the Worksheet</param>

        /// <returns>If addition of new sheet success than true else false</returns>

        public bool AddNewSheetToExcel(string fileName, string NewSheetName)

        {

            try

            {

                // Open the document for editing.

                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))

                {

                    // Add a blank WorksheetPart.

                    WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();

                    newWorksheetPart.Worksheet = new Worksheet(new SheetData());

                    Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();

                    string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);

                    // Get a unique ID for the new worksheet.

                    uint sheetId = 1;

                    if (sheets.Elements<Sheet>().Count() > 0)

                    {

                        sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;

                    }

                    // Give the new worksheet a name.

                    NewSheetName = NewSheetName + sheetId;

                    // Append the new worksheet and associate it with the workbook.

                    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = NewSheetName };

                    sheets.Append(sheet);

                    return status.Success;

                }

            }

            catch (Exception ex)

            {

                status.Message = ex.Message;

            }

            return status.Success;

        }

        /// <summary>

        /// Write excel file of a list of object as T Assume that maximum of 24 columns

        /// </summary>

        /// <typeparam name="T">Object type to pass in</typeparam>

        /// <param name="fileName">Full path of the file name of excel spreadsheet</param>

        /// <param name="objectsData">list of the object type</param>

        /// <param name="sheetName">Sheet names of Excel File</param>

        /// <param name="headerNames">Header names of the object</param>

        /// <returns>return true if excel created successfully else false</returns>

        public bool CreateExcelSheet<T>(string fileName, DataSet dataSet, string sheetName, List<string> headerNames, List<string> propertyList)

        {

            try

            {

                //Using SpreadsheetDocument class we are creating SpreadsheetDocumentType Workbook

                using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))

                {

                    //AddWorkbookPart :Create the WorkbookPart and add it to this document.

                    WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();

                    //AddNewPart :Add a new part of type T

                    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                    // Create Styles and Insert into Workbook

                    var stylesPart = myWorkbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();

                    //Custom settings will be generate by CustomExcelHelper Class(UserDefined class)

                    Stylesheet styles = new CustomExcelHelper();

                    //Save styles for workbook

                    styles.Save(stylesPart);

                    string relId = workbookPart.GetIdOfPart(worksheetPart);

                    var workbook = new Workbook();

                    var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

                    var worksheet = new Worksheet();

                    int numCols = headerNames.Count;

                    var columns = new Columns();

                    //Add Header Name

                    for (int col = 0; col < numCols; col++)

                    {

                        int width = headerNames[col].Length + 5;

                        Column c = new CustomColumn((uint)col + 1, (uint)numCols + 1, width);

                        columns.Append(c);

                    }

                    //Append Coulmn's to Worksheet

                    worksheet.Append(columns);

                    var sheets = new Sheets();

                    var sheet = new Sheet { Name = sheetName, SheetId = 1, Id = relId };

                    //Append coulmn to Sheet

                    sheets.Append(sheet);

                    //Appending file version

                    workbook.Append(fileVersion);

                    //Appending sheets to workbook

                    workbook.Append(sheets);

                    //CreateSheetData :It will write data to sheet

                    SheetData sheetData = WriteSheetData<T>(dataSet, headerNames, propertyList);

                    //Appending written data to worksheet

                    worksheet.Append(sheetData);

                    worksheetPart.Worksheet = worksheet;

                    //Save worksheet

                    worksheetPart.Worksheet.Save();

                    myWorkbook.WorkbookPart.Workbook = workbook;

                    //Save Workbook

                    myWorkbook.WorkbookPart.Workbook.Save();

                    //Close workbook

                    myWorkbook.Close();

                    return status.Success;

                }

            }

            catch (Exception ex)

            {

                //Message is a property of ExcelStatus class

                status.Message = ex.Message;

                return false;

            }

        }

        public void AddStyle(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet)

        {

            DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet;

            stylesheet.InsertAt<DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 0);

            stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.Font>(new DocumentFormat.OpenXml.Spreadsheet.Font()

            {

                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize()

                {

                    Val = 25

                },

                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName()

                {

                    Val = "Calibri"

                }

            }, 0);

            ///////////fill

            stylesheet.InsertAt<DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 1);

            stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill()

            {

                PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill()

                {

                    PatternType = new DocumentFormat.OpenXml.EnumValue<DocumentFormat.OpenXml.Spreadsheet.PatternValues>()

                    {

                        Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None

                    }

                }

            }, 0);

            ///////////////Border

            stylesheet.InsertAt<DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 2);

            stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border()

            {

                LeftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),

                RightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),

                TopBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),

                BottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),

                DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()

            }, 0);

            ////////////////////////cell format

            stylesheet.InsertAt<DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 3);

            stylesheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt<DocumentFormat.OpenXml.Spreadsheet.CellFormat>(new DocumentFormat.OpenXml.Spreadsheet.CellFormat()

            {

                NumberFormatId = 0,

                FontId = 0,

                FillId = 0,

                BorderId = 0

            }, 0);

            var a = stylesheet.CellStyleFormats.ChildElements.Count;

        }

        /// <summary>

        /// Create Excel sheet with data

        /// </summary>

        /// <typeparam name="T">Class Type</typeparam>

        /// <param name="objectsData"></param>

        /// <param name="headerNames">Header Name of Excel sheet</param>

        /// <returns></returns>

        public SheetData WriteSheetData<T>(DataSet dataSet, List<string> headerNames, List<string> propertyLists)

        {

            var sheetData = new SheetData();

            try

            {

                if (dataSet != null)

                {

                    //Get fields names of object

                    List<string> fields = GetPropertyInfo<T>();

                    List<string> az = new List<string>();

                    int dividend;

                    string columnName = string.Empty;

                    int modulo;

                    for (dividend = 1; dividend <= fields.Count; dividend++)

                    {

                        if (dividend <= 26)

                        {

                            modulo = (dividend - 1) % 26;

                            columnName = Convert.ToChar(65 + modulo).ToString();

                        }

                        if (dividend > 26 && dividend <= 52)

                        {

                            modulo = (dividend - 1) % 26;

                            columnName = Convert.ToChar(65 + modulo).ToString();

                            columnName = Convert.ToChar(65).ToString() + columnName;

                        }

                        if (dividend > 52 && dividend <= 78)

                        {

                            modulo = (dividend - 1) % 26;

                            columnName = Convert.ToChar(65 + modulo).ToString();

                            columnName = Convert.ToChar(66).ToString() + columnName;

                        }

                        az.Add(columnName);

                    }

                    //A to E number of columns 

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

                    // int numRows = dataSet.Count;

                    int numCols = headerNames.Count;//fields.Count;

                    if (propertyLists.Count > 0)

                    {

                        numCols = propertyLists.Count;

                    }

                    else

                    {

                        numCols = fields.Count;

                    }

                    var header = new Row();

                    int index = 1;

                    header.RowIndex = (uint)index;

                    List<string> columns = new List<string>();

                    //for (int col = 0; col < numCols; col++)

                    //{

                    //    //  var c = new HeaderCell(headers[col].ToString(), headerNames[col], index);

                    //     var c = new TextCell(headers[col].ToString(), headerNames[col], index);

                    //    columns.Add("");

                    //    c.StyleIndex = 11;

                    //    //columns.Add(c);

                    //    header.Append(c);

                    //}

                    //sheetData.Append(header);

                    DataTable table = dataSet.Tables[0];

                    //foreach (DataTable table in dataSet.Tables)

                    //{

                    int i = 0;

                    Row headerRow = new Row();

                    foreach (DataColumn column in table.Columns)

                    {

                        columns.Add(column.ColumnName);

                        Cell cell = new Cell();

                        cell.DataType = CellValues.String;

                        cell.CellValue = new CellValue(headerNames[i++]);

                        cell.StyleIndex = 11;

                        headerRow.AppendChild(cell);

                    }

                    //headerRow.StyleIndex = 11;

                    sheetData.AppendChild(headerRow);

                    foreach (DataRow dsrow in table.Rows)

                    {

                        Row newRow = new Row();

                        foreach (string col in columns)

                        {

                            Cell cell = new Cell();

                            cell.DataType = CellValues.String;

                            cell.CellValue = new CellValue(dsrow[col].ToString()); //

                            cell.StyleIndex = 1;

                            newRow.AppendChild(cell);

                        }

                        //newRow.StyleIndex = 11;

                        sheetData.AppendChild(newRow);

                    }

                    index++;

                }

            }

            catch (Exception ex)

            {

                status.Message = ex.Message;

            }

            return sheetData;

        }

        /// <summary>

        /// We can write bulk of data using this method.

        /// </summary>

        /// <param name="fileName">Pass filename with full path</param>

        /// <param name="ds">object of dataset has to pass</param>

        /// <returns>it will return true if data written in excel else false</returns>

        public bool FillSpreadSheet(string fileName, DataSet dataSet, List<string> headerNames)

        {

            try

            {

                using (var workbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))

                {

                    var workbookPart = workbook.AddWorkbookPart();

                    workbook.WorkbookPart.Workbook = new Workbook();

                    workbook.WorkbookPart.Workbook.Sheets = new Sheets();

                    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                    var stylesPart = workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();

                    Stylesheet styles = new CustomExcelHelper();

                    styles.Save(stylesPart);

                    int i = 0;

                    var worksheet = new Worksheet();

                    int numCols = headerNames.Count;

                    var clm = new Columns();

                    //Add Header Name

                    for (int col = 0; col < numCols; col++)

                    {

                        int width = headerNames[col].Length + 5;

                        Column c = new CustomColumn((uint)col + 1, (uint)numCols + 1, width);

                        clm.Append(c);

                    }

                    //Append Coulmn's to Worksheet

                    worksheet.Append(clm);

                    //  DataTable table = dataSet.Tables[0];

                    foreach (DataTable table in dataSet.Tables)

                    {

                        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();

                        var sheetData = new SheetData();

                        sheetPart.Worksheet = new Worksheet(sheetData);

                        Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();

                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                        uint sheetId = 1;

                        if (sheets.Elements<Sheet>().Count() > 0)

                        {

                            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;

                        }

                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };

                        sheets.Append(sheet);

                        Row headerRow = new Row();

                        List<string> columns = new List<string>();

                        foreach (DataColumn column in table.Columns)

                        {

                            columns.Add(column.ColumnName);

                            Cell cell = new Cell();

                            cell.DataType = CellValues.String;

                            cell.CellValue = new CellValue(headerNames[i++]);

                            cell.StyleIndex = 11;

                            headerRow.AppendChild(cell);

                        }

                        //headerRow.StyleIndex = 11;

                        sheetData.AppendChild(headerRow);

                        foreach (DataRow dsrow in table.Rows)

                        {

                            Row newRow = new Row();

                            foreach (string col in columns)

                            {

                                Cell cell = new Cell();

                                cell.DataType = CellValues.String;

                                cell.CellValue = new CellValue(dsrow[col].ToString()); //

                                cell.StyleIndex = 1;

                                newRow.AppendChild(cell);

                            }

                            //newRow.StyleIndex = 11;

                            sheetData.AppendChild(newRow);

                        }

                    }

                }

                return status.Success;

            }

            catch (Exception ex)

            {

                status.Message = ex.Message;

            }

            return status.Success;

        }

        /// <summary>

        /// Read the collection of data from excelsheet

        /// </summary>

        /// <param name="fileName">Pass the filename with fullpath</param>

        /// <returns>Return the data into datatable object</returns>

        public DataTable ReadAsDataTable(string fileName)

        {

            try

            {

                using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))

                {

                    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;

                    IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();

                    string relationshipId = sheets.First().Id.Value;

                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);

                    Worksheet workSheet = worksheetPart.Worksheet;

                    SheetData sheetData = workSheet.GetFirstChild<SheetData>();

                    IEnumerable<Row> rows = sheetData.Descendants<Row>();

                    foreach (Cell cell in rows.ElementAt(0))

                    {

                        status.DataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));

                    }

                    foreach (Row row in rows)

                    {

                        DataRow dataRow = status.DataTable.NewRow();

                        for (int i = 0; i < row.Descendants<Cell>().Count(); i++)

                        {

                            dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));

                        }

                        status.DataTable.Rows.Add(dataRow);

                    }

                }

                status.DataTable.Rows.RemoveAt(0);

                return status.DataTable;

            }

            catch (Exception ex)

            {

                status.Message = ex.Message;

            }

            return status.DataTable;

        }

        /// <summary>

        ///  Returns all the public properties of the current System.Type.

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <returns>Returns the list of string</returns>

        public List<string> GetPropertyInfo<T>()

        {

            List<string> lstprop = null;

            try

            {

                PropertyInfo[] propertyInfos = typeof(T).GetProperties();

                // write property names

                lstprop = propertyInfos.Select(propertyInfo => propertyInfo.Name).ToList();

                return lstprop;

            }

            catch (Exception ex)

            {

                status.Message = ex.Message;

            }

            return lstprop;

        }

        /// <summary>

        /// Reading cell value from excel

        /// </summary>

        /// <param name="document">Pass the SpreadsheetDocument instance</param>

        /// <param name="cell">Passing cell of the excel sheet</param>

        /// <returns></returns>

        public string GetCellValue(SpreadsheetDocument document, Cell cell)

        {

            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;

            string value = cell.InnerText;

            try

            {

                if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)

                {

                    return stringTablePart.SharedStringTable.ChildElements[int.Parse(value)].InnerText;

                }

                else

                {

                    return value;

                }

            }

            catch (Exception ex)

            {

                status.Message = ex.Message;

            }

            return value;

        }

        #endregion

    }

}