public class SaveToEx<T> where T : new()
{
public void SaveToExcel(List<T> listOfOutput, string path)
{
// check if path is correct
if (!IsValidPath(path)) return;
List<string> columnNames = new List<string>();
// get the property names from model (or any object) and save them for column names
T tObj = new T();
PropertyInfo[] pInfo = tObj.GetType().GetProperties(); // using reflection
pInfo.ToList().ForEach(item => columnNames.Add(item.Name));
// save the data to excel
Application excel = new Application();
// Add a new workbook
Workbooks wbs = excel.Workbooks;
wbs.Add();
_Worksheet workSheet = excel.ActiveSheet;
char columnIndex = 'A';
try
{
// create header columns
columnNames.ForEach(item =>
{
workSheet.Cells[1, columnIndex.ToString()] = item;
columnIndex++;
});
int row = 2;
// enter data
listOfOutput.ForEach(item =>
{
columnIndex = 'A';
columnNames.ForEach(item2 =>
{
workSheet.Cells[row, columnIndex.ToString()] = item.GetType().GetProperty(item2).GetValue(item);
columnIndex++;
});
row++;
});
// Save this data as a file
workSheet.SaveAs(@path);
Console.WriteLine("Saved");
Console.ReadKey();
}
catch (Exception) { }
finally
{
// release all com objects memory
excel.Quit();
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
catch (Exception) { }
excel = null;
wbs = null;
workSheet = null;
GC.Collect();
}
}
private bool IsValidPath(string path, bool allowRelativePaths = false)
{
bool isValid = true;
try
{
string fullPath = Path.GetFullPath(path);
if (allowRelativePaths)
{
isValid = Path.IsPathRooted(path);
}
else
{
string root = Path.GetPathRoot(path);
isValid = string.IsNullOrEmpty(root.Trim(new char[] { '\\', '/' })) == false;
}
}
catch (Exception)
{
isValid = false;
}
return isValid;
}
}
Example Usage :
class Program
{
static void Main(string[] args)
{
List<Output> outputs = new List<Output> { new Output { UnitNumber = 1 }, new Output { UnitNumber = 1 }, new Output { UnitNumber = 1 }, };
SaveToEx<Output> saveToEx = new SaveToEx<Output>();
saveToEx.SaveToExcel(outputs, @"E:\output.xlsx");
}
}
Where Output Class is :
public class Output
{
public int UnitNumber { get; set; }
public string ContentId { get; set; }
public string Url { get; set; }
public string title { get; set; }
public string statusCode { get; set; }
public string name { get; set; }
}