Series C# Hay Ho: EPPlus – Thư Viện Excel “bá đạo” – Phần 1

Lang thang dạo qua các group trên facebook, mình thấy có nhiều bạn hỏi cách để xuất file Excel trên MVC, hoặc đọc nội dung từ file Excel trong C#. Bài viết này sẽ giới thiệu EPPLus, một thư viện C# khá “bá đạo”, có khả năng xử lý tuốt tuồn tuột những thứ liên quan đến Excel.

Bài này được viết bởi khách mời Phạm Hồng Sang, một bạn nam dễ thương cùng trường FPT và đồng nghiệp ASWIG với mình. Văn phong của Sang gãy gọn đơn giản chứ không lòng vòng hài hước như mình, bạn nào thấy giọng văn lạ thì đừng thắc mắc nhé.

Như cái tiêu đề ở trên thì trong series này mình sẽ hướng dẫn các bạn cách xử lý file excel trong C# một cách đơn giản nhất với thư viện EPPlus. Bạn có thể tải về tại đây, hoặc cũng có thể tìm EPPlus trên Nuget và import vào. Toàn bộ source code của bài demo: https://github.com/ToiDiCodeDaoSampleCode/EPPLus-Sample

Đầu tiên, chúng ta sẽ một class đơn giản, chứa thông tin các hàng trong Excel để thuận tiện cho việc demo.

public class TestItemClass { public int Id { get; set; } public string FullName { get; set; } public double Money { get; set; } public string Address { get; set; } }

Tiếp theo là tạo một list các item từ class mà ta đã tạo ở trên, các bạn cũng có thể sự dụng EF để lấy dữ liệu từ database cũng được. Để đơn giản và tiết kiệm thời gian, mình sẽ tạo một list data giả như sau:

private List<TestItemClass> CreateTestItems() { var resultsList = new List<TestItemClass>(); for (int i = 0; i < 15; i++) { var a = new TestItemClass() { Id = i, Address = "Test Excel Address at " + i, Money = 20000 + i * 10, FullName = "Pham Hong Sang " + i }; resultsList.Add(a); } return resultsList; }

Các bước chuẩn bị đã xong, bây giờ chúng ta bắt đầu chế biến món ăn :D.

Tạo File Excel

Chúng ta sẽ tạo file step by step như trong đoạn code dưới đây:

1. Truyền vào một stream hoặc Memory Stream để thao tác với file Excel.
2. Thiết lập các properties cho file Excel.
3. Cuối cùng là đổ data vào file excel thông qua hàm LoadFromCollection với params truyền vào là 3 tham số :
   • Collection: List các items
   • PrintHeader: True or False để in thêm cái header ra file excel
   • TableStyle: Chọn style cho table mà các bạn muốn 😀
4. Cuối cùng Save Sheet đó lại.

private Stream CreateExcelFile(Stream stream = null) { var list = CreateTestItems(); using (var excelPackage = new ExcelPackage(stream ?? new MemoryStream())) { // Tạo author cho file Excel excelPackage.Workbook.Properties.Author = "Hanker"; // Tạo title cho file Excel excelPackage.Workbook.Properties.Title = "EPP test background"; // thêm tí comments vào làm màu excelPackage.Workbook.Properties.Comments = "This is my fucking generated Comments"; // Add Sheet vào file Excel excelPackage.Workbook.Worksheets.Add("First Sheet"); // Lấy Sheet bạn vừa mới tạo ra để thao tác var workSheet = excelPackage.Workbook.Worksheets[1]; // Đổ data vào Excel file workSheet.Cells[1, 1].LoadFromCollection(list, true, TableStyles.Dark9); // BindingFormatForExcel(workSheet, list); excelPackage.Save(); return excelPackage.Stream; } }

Export file Excel

Sau khi đã tạo xong File Excel thì bây giờ chúng ta sẽ export file Excel đó ra và xem thành phẩm nhé. Bạn thêm hàm Export phía dưới vào HomeController, sau đó truy xuất tới đường link /Home/Export/ để xuất file excel ra.

[HttpGet] public ActionResult Export() { // Gọi lại hàm để tạo file excel var stream = CreateExcelFile(); // Tạo buffer memory strean để hứng file excel var buffer = stream as MemoryStream; // Đây là content Type dành cho file excel, còn rất nhiều content-type khác nhưng cái này mình thấy okay nhất Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // Dòng này rất quan trọng, vì chạy trên firefox hay IE thì dòng này sẽ hiện Save As dialog cho người dùng chọn thư mục để lưu // File name của Excel này là ExcelDemo Response.AddHeader("Content-Disposition", "attachment; filename=ExcelDemo.xlsx"); // Lưu file excel của chúng ta như 1 mảng byte để trả về response Response.BinaryWrite(buffer.ToArray()); // Send tất cả ouput bytes về phía clients Response.Flush(); Response.End(); // Redirect về luôn trang index 😀 return RedirectToAction("Index"); }

Đây là thành phẩm, trông hơi cùi bắp và khá chuối phải không =))). Ở bước tiếp theo, mình sẽ hướng dẫn các bạn format file excel sao cho đẹp mắt hơn.

Tút lại nhan sắc cho file Excel

Dưới đây là đoạn code để format cho file Excel, các bạn nhớ uncomment đoạn BindingFormatForExcel trong hàm CreateExcelFile nhé.

private void BindingFormatForExcel(ExcelWorksheet worksheet, List<TestItemClass> listItems) { // Set default width cho tất cả column worksheet.DefaultColWidth = 10; // Tự động xuống hàng khi text quá dài worksheet.Cells.Style.WrapText = true; // Tạo header worksheet.Cells[1, 1].Value = "ID"; worksheet.Cells[1, 2].Value = "Full Name"; worksheet.Cells[1, 3].Value = "Address"; worksheet.Cells[1, 4].Value = "Money"; // Lấy range vào tạo format cho range đó ở đây là từ A1 tới D1 using (var range = worksheet.Cells["A1:D1"]) { // Set PatternType range.Style.Fill.PatternType = ExcelFillStyle.DarkGray; // Set Màu cho Background range.Style.Fill.BackgroundColor.SetColor(Color.Aqua); // Canh giữa cho các text range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Set Font cho text trong Range hiện tại range.Style.Font.SetFromFont(new Font("Arial", 10)); // Set Border range.Style.Border.Bottom.Style = ExcelBorderStyle.Thick; // Set màu ch Border range.Style.Border.Bottom.Color.SetColor(Color.Blue); } // Đỗ dữ liệu từ list vào for (int i = 0; i < listItems.Count; i++) { var item = listItems[i]; worksheet.Cells[i + 2, 1].Value = item.Id + 1; worksheet.Cells[i + 2, 2].Value = item.FullName; worksheet.Cells[i + 2, 3].Value = item.Address; worksheet.Cells[i + 2, 4].Value = item.Money; // Format lại color nếu như thỏa điều kiện if (item.Money > 20050) { // Ở đây chúng ta sẽ format lại theo dạng fromRow,fromCol,toRow,toCol using (var range = worksheet.Cells[i + 2, 1, i + 2, 4]) { // Format text đỏ và đậm range.Style.Font.Color.SetColor(Color.Red); range.Style.Font.Bold = true; } } } // Format lại định dạng xuất ra ở cột Money worksheet.Cells[2, 4, listItems.Count + 4, 4].Style.Numberformat.Format = "$#,##.00"; // fix lại width của column với minimum width là 15 worksheet.Cells[1, 1, listItems.Count + 5, 4].AutoFitColumns(15); // Thực hiện tính theo formula trong excel // Hàm Sum worksheet.Cells[listItems.Count + 3, 3].Value = "Total is :"; worksheet.Cells[listItems.Count + 3, 4].Formula = "SUM(D2:D" + (listItems.Count + 1) + ")"; // Hàm SumIf worksheet.Cells[listItems.Count + 4, 3].Value = "Greater than 20050 :"; worksheet.Cells[listItems.Count + 4, 4].Formula = "SUMIF(D2:D" + (listItems.Count + 1) + ",\">20050\")"; // Tinh theo % worksheet.Cells[listItems.Count + 5, 3].Value = "Percentatge: "; worksheet.Cells[listItems.Count + 5, 4].Style.Numberformat.Format = "0.00%"; // Dòng này có nghĩa là ở column hiện tại lấy với địa chỉ (Row hiện tại - 1)/ (Row hiện tại - 2) Cùng một colum worksheet.Cells[listItems.Count + 5, 4].FormulaR1C1 = "(R[-1]C/R[-2]C)"; }

Bây giờ thì bạn hãy build lại rồi Export filed excel đó ra thử nhé :D. Bạn đã export thành công 1 file excel rồi đó.

Ở phần sau, mình sẽ hướng dẫn các bạn cách thao tác với các Formula trong Excel khi sử dụng EPPlus, cùng với cách lấy data trong file Excel (Read data from Excel file) để thao tác các business khác. Link tham khảo : https://www.paragon-inc.com/resources/blogs-posts/easy_excel_interaction_pt5.

Discover more from Từ coder đến developer - Tôi đi code dạo

Subscribe to get the latest posts sent to your email.

Type your email…

Subscribe

Rate this:

Like Loading...

Related