C# export generic data via Microsoft.Office.Interop.Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.IO;

namespace Framework.Utility
{
    public class ExportHelper<T> where T:class,new()
    {
        public static void ExportData(IEnumerable<T> dataList,ref string fileName, bool isAutoDownload = false)
        {
            try
            { 
                if (dataList == null || !dataList.Any())
                {
                    MessageBox.Show("The data source is null");
                    return;
                }
                var excelApp = new Excel.Application();
                var workBook = excelApp.Workbooks.Add(); 
                var workSheet = (Worksheet)workBook.Worksheets[1];
                workSheet.Columns.AutoFit();
                var firstRowData = dataList.FirstOrDefault();
                var pis = firstRowData.GetType().GetProperties().Where(x => !x.GetMethod.IsVirtual).ToArray();
                if (pis != null && pis.Any())
                {
                    for (int i = 0; i < pis.Count(); i++)
                    {
                        workSheet.Cells[1, i + 1] = pis[i].Name;                        
                    }

                    int row = 1;
                    foreach (var data in dataList)
                    {
                        row++;
                        for (int i = 0; i < pis.Count(); i++)
                        {
                            var objValue = pis[i].GetValue(data);
                            workSheet.Cells[row, i + 1] = objValue?.ToString();
                        }
                    } 
                }
                //SaveFileDialog sfd = new SaveFileDialog();
                //sfd.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                //sfd.FileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssffff")}";

                //if (sfd.ShowDialog()==DialogResult.OK)
                //{                     
                //    workBook.SaveCopyAs(sfd.FileName);
                //}
                //string fileName= $"{DateTime.Now.ToString("yyyyMMddHHmmssffff")}.xlsx"; 
                fileName = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), $"{DateTime.Now.ToString("yyyyMMddHHmmssffff")}.xlsx");
                workBook.SaveCopyAs(fileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace);
            } 
        }
    }
}

 

ExportHelper<SalesOrderDetail>.ExportData(SalesList.ToList(), ref fileName);

 

上一篇:图像标注


下一篇:浏览器辅助神器