Mr.Combet Webshell 🔥

View File Name : ExcelOperations.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mvc;
using ClosedXML.Excel;

namespace RER_Project.Web.Helpers
{
    public class ExcelOperations
    {
        public static FileStreamResult ListToExcel<T>(List<T> list, string reportname)
        {
            DataTable dt = new DataTable(typeof(T).Name);
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dt.Columns.Add(prop.Name);
            }
            foreach (T item in list)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {

                    values[i] = Props[i].GetValue(item, null);
                }
                dt.Rows.Add(values);
            }
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt);

                MemoryStream stream = new MemoryStream();
                try
                {
                    stream.Position = 0;
                    wb.SaveAs(stream);
                    stream.Position = 0;
                    return new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = reportname + ".xlsx" };
                }
                catch
                {
                    stream.Dispose();
                    throw;
                }
            }
        }


        public static DataTable UploadExcel(HttpPostedFileBase file, string path)
        {
            DataTable dt = new DataTable();

            using (XLWorkbook workbook = new XLWorkbook(path))
            {
                IXLWorksheet worksheet = workbook.Worksheet(1);
                bool FirstRow = true;
                //Range for reading the cells based on the last cell used.
                string readRange = "1:1";
                foreach (IXLRow row in worksheet.RowsUsed().Skip(1))
                {
                    //If Reading the First Row (used) then add them as column name
                    if (FirstRow)
                    {
                        //Checking the Last cellused for column generation in datatable
                        readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);
                        foreach (IXLCell cell in row.Cells(readRange))
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        FirstRow = false;
                    }
                    else
                    {
                        //Adding a Row in datatable
                        dt.Rows.Add();
                        int cellIndex = 0;
                        //Updating the values of datatable
                        foreach (IXLCell cell in row.Cells(readRange))
                        {
                            dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();
                            cellIndex++;
                        }
                    }
                }
                
            }

            return dt;
        }

    }
}