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.