How to export data from c# console application to Excel

Question

i have some rows and columns in c# console output, i want to export this to excel.any advice? Thanks

Answer 1

I suggest the ClosedXML library. Here's a neat example:

XLWorkbook workbook = new XLWorkbook();
DataTable dt = new DataTable() { TableName = "New Worksheet" };
DataSet ds = new DataSet();

//input data
var columns = new[] { "column1", "column2", "column3" };
var rows = new object[][] 
{
     new object[] {"1", 2, false },
     new object[] { "test", 10000, 19.9 }
};

//Add columns
dt.Columns.AddRange(columns.Select(c => new DataColumn(c)).ToArray());

//Add rows
foreach (var row in rows)
{
    dt.Rows.Add(row);
}

//Convert datatable to dataset and add it to the workbook as worksheet
ds.Tables.Add(dt);
workbook.Worksheets.Add(ds);

//save
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string savePath = Path.Combine(desktopPath, "test.xlsx");
workbook.SaveAs(savePath, false);

Answer 2

I utilized the EPPlus Library from Nuget. Below is my dynamic implementation, that serves my needs. You can find simpler examples in their documentation. But that would allow you to actually create row / colum based on your Poco.

You could utilize a method such as:

    public void WriteFile<THeader, TEntity>(THeader header, params TEntity[] contents)
    {
        if (!File.Exists(FileInformation.FullName))
            File.Create(FileInformation.FullName).Close();

        using (var excel = new ExcelPackage())
        {
            ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(FileInformation.Name);
            CreateExcelWorksheetHeader(worksheet, header);

            var row = 2;
            Dictionary<int, object> mapping = BuildTableMap(worksheet, 1);
            foreach (TEntity content in contents)
            {
                MapEntityToRow(worksheet, mapping, row, content);
                row++;
            }

            excel.SaveAs(FileInformation);
        }
    }

    private static void CreateExcelWorksheetHeader<THeader>(ExcelWorksheet worksheet, THeader entity)
    {
        var index = 1;
        PropertyInfo[] properties = typeof(THeader).GetProperties();
        foreach (PropertyInfo property in properties)
        {
            worksheet.Cells[1, index].Value = property.GetValue(entity, null);
            index++;
        }
    }

    private static void MapEntityToRow<TEntity>(ExcelWorksheet worksheet, Dictionary<int, object> table, int row, TEntity entity)
    {
        IDictionary<string, string> properties = ObjectMapper.GetPropertyNameAndAttribute<TEntity>();
        foreach (KeyValuePair<int, object> column in table)
        {
            var matchColumnToProperty = properties.SingleOrDefault(property => string.Compare(property.Key, (string)column.Value, true) == 0).Key;
            var matchColumnToAttribute = properties.SingleOrDefault(property => string.Compare(property.Value, (string)column.Value, true) == 0).Value;

            if (matchColumnToProperty != null)
                worksheet.Cells[row, column.Key].Value =
                    typeof(TEntity).GetProperty(matchColumnToProperty).GetValue(entity, null);

            if (matchColumnToAttribute != null)
                worksheet.Cells[row, column.Key].Value =
                    typeof(TEntity).GetProperty(properties.SingleOrDefault(property => string.Compare(property.Value, matchColumnToAttribute, true) == 0).Key)
                    .GetValue(entity, null);
        }
    }