[Part 2] C#(.NET) - Tương tác với file Excel

5 năm trước · 9 phút đọc

Lời Ngỏ

Chào mọi người, Nếu ở phần một mình đã trình bày phần import dữ liệu từ file excel. Ở phần này, mình sẽ giới thiệu tiếp phần xuất dữ liệu => Xuất dữ liệu file excel.

Ở phần này, để tăng độ khó mình xin sửa lại yêu cầu một chút nhé.

Nội Dung

4. Thực hiện yêu cầu

4.1. Đặt vấn đề

Tải nhanh file dữ liệu tại Google Drive.

Giải thích một số trường trong file:

STT	Column Name	Description
1	ID	ID của một cuộc gọi
2	Date	Thời gian thực hiện gọi
3	Source	Số gọi đi
4	Destination	Số người nhận
5	Status	Trạng thái cuộc gọi
6	Duration	Thời gian cuộc gọi
7	Recording	Nơi lưu trữ file ghi âm cuộc gọi

Từ File excel có sẵn thực hiện tính năng sau:

Đọc file excel và lưu nó thành DataTable - Tự giải quyết - tương tự phần 1.
Đọc file mẫu và thực hiện yêu cầu sau:
- Thống kê số lượng trả lời, bận, không trả lời, lỗi theo tỉ lệ số lượng, phần trăm làm tròn 2 chữ số thập phân.
- Thống kê tỉ lệ đã trả lời theo từng tháng từ tháng 1 => tháng 9.
- Thống kê cuộc gọi từ nhà mạng Viettel.
- Xuất File Excel theo mẫu cho sẵn.
- Thể hiện quá trình xử lý

4.2. Lên ý tưởng và giải quyết vấn đề

Mẫu cần xuất như sau:

Dữ liệu cần xuất là: tổng số lượng cuộc gọi của trả lời, bận, không trả lời, lỗi.
Số điện thoại từ nhà mạng Viettel => Các số có 3 số đầu là: 086, 096, 097, 098, 032, 033, 034, 035, 036, 037, 038, 039
Xuất dữ liệu Excel theo mẫu ==> Xem mẫu và format theo.
Thể hiện quá trình xử lý (làm cho ngầu thôi chớ xử lý không tốt => ảnh hưởng performance lắm).

4.3. Xử lý yêu cầu số 1

Yêu cầu bài toán: Thống kê số lượng trạng thái trả lời, bận, không trả lời, lỗi => theo tỉ lệ số lượng & phần trăm.

Hướng xử lý: Dựa vào trường "Status" thống kê theo yêu cầu.

ANSWERED => Đã trả lời.
NO ANSWER => Không liên lạc được.
BUSY => Máy bận.
FAILED => Lỗi.
Tính phần trăm số lượng của một phần tử => (Tổng tất cả là 100%) => %ANSWERED = (ANSWERED x 100) / Tổng

Để tìm số lượng số cuộc gọi đã trả lời ta thực hiện như sau:

//Read(String Đường dẫn file,String Tên trang tính) là hàm để đọc dữ liệu từ file excel
DataTable dataUser = Read(pathFile, "Report_PhoneNumber");
int sumAnswered = dataUser.AsEnumerable().Where(call=> call["Status"].ToString() == "ANSWERED").Count();

Để tính tỉ lệ phần trăm của số cuộc gọi đã trả lời ta xử lý như sau:

 int sumAllCalls = sumAnswered + sumNoAnswered + sumBusy + sumFailed;
double percentAnswered = Math.Round((sumAnswered * 100.0) / sumAllCalls,2); ;

Vì sao lại là 100.0 => Vì Int * Int/ Int * 2 => Int. Mà chúng ta cần kết quả thuộc dạng số thực - double.

4.4. Xử lý yêu cầu số 2 - Tự xử

Yêu cầu: Thống kê tỉ lệ đã trả lời theo từng tháng từ tháng 1 => tháng 9.

Gợi ý: Lọc điều kiện theo cột Date, Status => tính số lượng từng tháng => tính tỉ lệ từng tháng.

4.5. Xử lý yêu cầu số 3

Yêu cầu: Thống kê cuộc gọi từ nhà mạng Viettel.

Hướng xử lý: Dựa vào trường "Source" và "Destination" thống kê theo yêu cầu.

Để lấy ra số lượng cuộc gọi đi (Source) nhà mạng Viettel ta xử lý như sau:

Sử dụng cấu trúc truy vấn LINQ trên DataTable.
Lọc kết quả thông qua where.

Phần lấy cuộc gọi đến xử lý tương tự.

Code tham khảo:

var totalSource_Viettel = (from row in dataUser.Rows.OfType<DataRow>()
                        where listNumberPhoneViettel.Contains(int.Parse(row[2].ToString().Substring(0,2)))
                        select row).Count();

4.6. Xử lý yêu cầu số 4

Yêu cầu: Xuất File Excel theo mẫu cho sẵn.

Mẫu cần xuất như sau:

Tải nhanh file mẫu từ Google Drive.

Để tương tác với file excel ta cần quan tâm 3 đối tượng sau đây:

 Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
  // creating new WorkBook within Excel application  
 Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook  
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

Trong quá trình xử lý, bạn muốn theo dõi chương trình Excel hoạt động như thế nào? Bạn có thể thêm dòng code sau: app.Visible = true;

4.6.1. Xử lý dòng số 1

Để tạo trang tính và đặt tên ta sử dụng câu lệnh sau:

 worksheet = workbook.Sheets["Result"];
 worksheet = workbook.ActiveSheet;
//Hoặc bạn có thể sử dụng Property Name của worksheet 
// worksheet.Name ="Result";

Để thêm dữ liệu cho một cell ta thực hiện câu lệnh sau:

//Cú pháp: worksheet.Cells[<Số Dòng>, "<Tên cột>"] = "<Dữ liệu>";
worksheet.Cells[1, "A"] = "Thống kê cuộc gọi từ tháng 1 đến tháng 9 năm 2021";

Để gộp các ô (Merge cell) ta thực hiện câu lệnh sau:

//Cú pháp: worksheet.Range[Cell1, Cell2].Merge();
worksheet.Range[worksheet.Cells[1, "A"], worksheet.Cells[1, "D"]].Merge();
//Hoặc có thể sử dụng method get_Range
//  worksheet.get_Range("A1", "D1")..Merge();

Thay đổi font chữ như sau:

 worksheet.get_Range("A1", "D1")

Chung quy, ở dòng một ta thiết lập như sau:

//Row 1
//Set Value
worksheet.Cells[1, "A"] = "Thống kê cuộc gọi từ tháng 1 đến tháng 9 năm 2021";
//Merge Cell
worksheet.Range[worksheet.Cells[1, "A"], worksheet.Cells[1, "M"]].Merge();
//Set font style
worksheet.get_Range("A1", "M1").Font.Bold = true;
worksheet.get_Range("A1", "M1").Font.Size = 16;
//Set Alignment 
worksheet.get_Range("A1", "M1").Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.get_Range("A1", "M1").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;Center;

Kết quả nè:

4.6.2. Xử lý vùng dữ liệu từ ô A2:G16

4.6.2.1 Xử lý dòng 2

//Thiết lập giá trị 
worksheet.get_Range("A2").Value = "Bảng thống kê tỉ lệ đã trả lời từ tháng 1 đến tháng 9";
worksheet.get_Range("A2", "G2").Merge(); //Gộp ô
worksheet.get_Range("A2", "G2").Font.Bold = true;
worksheet.get_Range("A2", "G2").Font.Size = 14;
//Căn chỉnh lề
worksheet.get_Range("A2", "G2").Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

4.6.2.1. Xử lý dòng 3 - Tự xử

4.6.2.2. Xử lý dòng 4 => 12

Ở đây, mình sử dụng vòng lặp để tái sử dụng code.

 int i = 4;
foreach (var item in totalCallsMonthly)
{
    worksheet.get_Range(string.Format("A{0}",i)).Value = item.Key;
    worksheet.get_Range(string.Format("B{0}", i)).Value = string.Format("{0:0,0}", item.Value) ;
    worksheet.get_Range(string.Format("C{0}", i)).Value = string.Format("{0:0.0}%", Math.Round((item.Value * 100.0) / sumAllCalls, 2)) ;
    i++;
}

4.6.2.3. Xử lý dòng 13 => 16

Mình giới thiệu cách lòng một hàm vào cell như sau: worksheet.get_Range("B13").Formula="=SUM(B4:B12)"

//Row 13 => 16
worksheet.get_Range("A13").Value = "Tổng";
//worksheet.get_Range("B13").Value = string.Format("{0:0,0}", sumAllCalls) ;
worksheet.get_Range("B13").Formula = "=SUM(B4:B12)";
worksheet.get_Range("A15").Value = string.Format("Trong đó tổng cuộc gọi đi từ nhà mạng Viettel là {0:0,0} cuốc.", totalSource_Viettel);
worksheet.get_Range("A15", "G15").Merge();
worksheet.get_Range("A16").Value = string.Format("Trong đó tổng cuộc gọi đến từ nhà mạng Viettel là {0:0,0} cuốc.", totalDestination_Viettel);
worksheet.get_Range("A16", "G16").Merge();
}

4.6.3. Xử lý vùng dữ liệu từ ô H2:M5 - Tự xử

Gợi ý: Vì cái này tương tự những ô khác. Bạn xem style của các ô và tự mò và làm theo nhé.

Kết quả như hình:

4.7. Xử lý yêu cầu số 5 - Tự xử

Gợi ý: Bạn có thể dùng Control Label để xử lý hoặc kết hợp với Control ProgressBar để giao diện ưa nhìn hơn.

4.8. Lưu file

Bạn sử dụng method "SaveAs(<FileName>)" của đối tượng workbook. Sau đó nhớ đóng application lại nhé. Code tham khảo:

workbook.SaveAs(dlg.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application  
app.Quit();

Kết quả tham khảo:

5. Bonus

5.1. Để tìm real column và real row

Mục đích: Khắc phục ở bước tìm cột và hàng ở Part 1. Cách này sẽ tìm tới chính xác ô chứa dữ liệu => Nếu ô đó chỉ chứa định dạng => Bị bỏ qua.

Bạn có thể tham khảo code dưới đây:

 Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[sheetName];
int rows = objSHT.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
// Find the last real column
int cols = objSHT.Cells.Find("*", System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;

5.2. Thiết lập Auto Resize cho cột

Trước khi gọi method Save của workbook ==> Bạn gọi hàm AutoFit.

**Code tham khảo: **

// Resize Columns
worksheet.Columns.AutoFit();

5.3. Mở lại file excel