C#实现从数据库读取数据到Excel

用第三方组件：NPOI来实现

先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。

创建一个实体类：

[Table("Customer") ]
    public class Customer
    {
        [Key]
        public int Id { get; set; }
        public string FirstName { get; set; }

        public string LastName { get; set; }
       
        public int Age { get; set; }

        public int Gender { get; set; }

    }

新建一个类Customer

创建一个类去实现从List<Customer>中读取数据到Excel中

public class ExportToExcel
    {
        public void ExportCustomerToExcel(Stream stream, IList<Customer> customerList)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            ISheet workSheet = workBook.CreateSheet("Customer");
            IRow currRow;
            ICell currCell;

            workSheet.CreateFreezePane(0,1,0,1);

            //Excel Header
            var properties = new string[] { "Id", "FirstName", "LastName", "Age", "Gender" };

            ICellStyle styleHeader = GetNPOIExcelHeaderStyle(workBook);

            currRow = workSheet.CreateRow(0);
            for(int i=0; i < properties.Length; i++)
            {
                currCell = currRow.CreateCell(i);
                currCell.SetCellValue(properties[i]);
                currCell.CellStyle = styleHeader;
            }

            //Excel的正文
            int row = 1;
            int col = 0;
            foreach(var customer in customerList)
            {
                col = 0;
                currRow = workSheet.CreateRow(row);

                currRow.CreateCell(col).SetCellValue(customer.Id);
                col++;

                currRow.CreateCell(col).SetCellValue(customer.FirstName);
                col++;

                currRow.CreateCell(col).SetCellValue(customer.LastName);
                col++;

                currRow.CreateCell(col).SetCellValue(customer.Age);
                col++;

                currRow.CreateCell(col).SetCellValue(customer.Gender);
                col++;

                row++;
            }

            workBook.Write(stream);
        }

        private ICellStyle GetNPOIExcelHeaderStyle(IWorkbook workbook)
        {
            ICellStyle styleHeader = workbook.CreateCellStyle();
            IFont fontHeader = workbook.CreateFont();
            fontHeader.Boldweight = (short)FontBoldWeight.Bold;
            styleHeader.SetFont(fontHeader);
            return styleHeader;
        }
    }

从List读取数据到Excel中

在main函数方法中实现从数据库读取数据，并调用ExportToExcel类中的ExportCustomerToExcel方法，将数据写入到EXCEL中

class Program
    {
        static void Main(string[] args)
        {
            string filePath = @"E:\Customer_Test.xlsx";

            #region 从数据库读取数据
            IList<Customer> customerList = new List<Customer>();
            CodeFirstDBContext context = new CodeFirstDBContext();
            var customer = context.Customer.ToList();
            #endregion

            
            ExportToExcel export = new ExportToExcel();
            MemoryStream ms = new MemoryStream();
            export.ExportCustomerToExcel(ms, customer);
           
            using(FileStream fs = new FileStream(@"E:\Customer.xlsx",FileMode.Create,FileAccess.Write))
            {
                byte[] bytes = ms.ToArray();
                fs.Write(bytes,0,bytes.Length);
                fs.Flush();
            }
            Console.ReadKey();
        }
    }

main函数

从数据库中读取数据到Excel中，已经实现

转载于:https://www.cnblogs.com/Alice-Wang/p/5594614.html