用第三方组件：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(,,,); //Excel Header
var properties = new string[] { "Id", "FirstName", "LastName", "Age", "Gender" }; ICellStyle styleHeader = GetNPOIExcelHeaderStyle(workBook); currRow = workSheet.CreateRow();
for(int i=; i < properties.Length; i++)
{
currCell = currRow.CreateCell(i);
currCell.SetCellValue(properties[i]);
currCell.CellStyle = styleHeader;
} //Excel的正文
int row = ;
int col = ;
foreach(var customer in customerList)
{
col = ;
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,,bytes.Length);
fs.Flush();
}
Console.ReadKey();
}
}
main函数
从数据库中读取数据到Excel中，已经实现