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");
}

Demo 1Đâ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 đó.

demo2
Ở 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.

Advertisements

10 thoughts on “Series C# hay ho: EPPlus – Thư viện Excel “bá đạo” – Phần 1”

  1. Bạn ơi làm việc với word thì bạn hay dùng thư viện nào? Cả Excel và Word mình đang dùng Aspose, nhưng hơi mệt về vấn đề bản quyền.

    Like

  2. Anh cho em hỏi em có dữ liệu load trên Girdview và có column có hình ảnh trên đó. Vậy em có thể export ra excel từ girdview đang có bao gồm hình ảnh luôn không anh?

    Like

  3. Anh cho em hỏi, hiện tại, em hiển thị dữ liệu từ database và xuất ra Excel sử dụng dll của Epplus. Hiện tại em xuất ra có dạng giống như hình A bên dưới, giờ em muốn merge cells lại giống như hình B. Anh giúp em với.

    Like

      1. Hiện tại em Merge Cell được, nhưng với trường hợp trên mình không biết khi nào mã sản phẩm nó từ 2 dòng trở lên và mình không biết vị trí dòng của nó thì làm sao hả anh, em co tìm trên mạng rồi chỉ toàn Merge Cell thường không à.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s