用第三方组件:NPOI来实现
先去官网:http://npoi.codeplex.com/下载需要引入dll(可以选择.net2.0或者.net4.0的dll),然后在网站中添加引用。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
创建一个实体类:
新建一个类Customer
[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中
从List读取数据到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中,已经实现