Thao tác với file Excel

1. Thành phần cơ bản của excel application

Application: Chương tình Excel của Microsoft hoạt động như một dạng MDI Form, trong đó gồm Khung chứa, và các file excel sẽ nằm bên trong. Khung chứa chính là thể hiện của Application. Khi đóng Application, toàn bộ các file excel sẽ bị đóng.
Workbook: Đại diện cho mỗi file excel khi được mở.
Sheets: Tập hợp các Sheet trong file excel.
Worksheet: Đại diện cho một sheet trong Sheets
Cell: Đại diện cho một cell trong Worksheet
Range: Một cách tổng quát là một mảng 2 chiều, đại diện cho một vùng trong Worksheet.

2. Các đối tượng và phương thức thao tác với file excel Các đối tượng và phương thức thao tác với file excel nằm trong namespace: Microsoft.Office.Interop.Excel

a. Khởi động Excel Application

 Application excelApp = new ApplicationClass();

b. Mở 1 file excel nằm trên ổ cứng

 private object missing_value = System.Reflection.Missing.Value;
Workbook excelWorkbook = this.excelApp.Workbooks.Open(
fileName, update_links , read_only,
format, password, write_res_password,
ignore_read_only_recommend, origin,
delimiter, editable, notify, converter,
add_to_mru, local, corrupt_load);

Trong đó: filename:

• Tên file excel, gồm đầy đủ đường dẫn. Đây là tham số bắt buộc duy nhất, các tham số khác đều là optional. update_links: 1 read_only:
• true/false. True để thiết lập mở file excel trong chế độ read-only format:
• Khi mở file excel bằng 1 trình soạn thảo nào đó. Tham số sẽ xác định loại ký tự dùng để ngăn cách giữa các cell.

· Giá trị Dấu ngăn cách (Delimiter)

· 1 Tab

· 2 Dấu phảy

· 3 Trắng (space)

· 4 Dấu chấm phảy

· 5 Nothing

· 6 Ký tự đặc biệt (thiết lập trong thuộc

· tính Delimiter ở phần dưới)

· password:

• Password dùng để mởi file. Nếu không nhập password, sẽ không mở được file write_res_password:
• Password dùng để modify file. Nếu không nhập password, sẽ mở file ở chế độ read-only ignore_read_only_recommend:
• true/false. Thiết lập là True, excel sẽ không hiển thị cảnh báo “Read-Only” khi save 1 file đang ở trong chế độ read-only origin:
• missing_value. Không sử dụng delimiter:
• Khi tham số format = 6, delimiter sẽ xác định ký tự phân cách, vd: chr(9) – tabs editable:
• mising_value. Không sử dụng, mặc định là false notify:
• true/false. Nếu file excel không thể mở ở chế độ write-mode vì một lý do nào đó. Thiết lập thuộc tính notify là true, Excel sẽ thông báo là file sẽ được mở ở chế độ read-only mode. converter:
• missing_value. Khi file excel ở những định dạng hoặc version khác nhau, trình Converter sẽ có nhiệm vụ mở file này. Excel sẽ duyệt qua tất cả các trình Converter cho đến khi nào phù hợp. add_to_mru:
• true/false. Excel sẽ add workbook vào danh sách “recently used files”. Mặc định false. local:
• true/false. Mặc định là false corrupt_load:
• Liên quan đến việc load dữ liệu vào file. Có 3 chế độ: xlNormalLoad, xlRepairFile, và xlExtractData. Mặc định là xlNormalLoad, chế độ này có các trạng thái sau: +Normal: mở file bình thường +Safe load or data recovery: trong trường hợp dữ liệu chưa kịp save khi mất điện, ở lần mở file sau đó, hệ thống sẽ mở ở trạng thái khôi phục dữ liệu c. Tạo một file mới

Workbook excelWorkbook = this.excelApp.Workbooks.Add(missing_value);

d. Lưu nội dung file excel

Để lưu nội dung file excel, ta sử dụng 2 method:

• Save() : Lưu thông tin file đã tồn tại

this.excelWorkbook.Save();

SaveAs(): Lưu thông tin file mới

this.excelWorkbook.SaveAs(fileName, format, password, write_res_password,
read_only_recommend, create_backup, access_mode,
conflict_resolution, add_to_mru, text_code_page,
text_visual_layout, local);

Trong đó: filename:

• Tên file để lưu. Trường hợp không chỉ định đường dẫn, sẽ lưu trên thư mục hiện tại format:
• Định dạng dùng khi save file ví dụ: XlFileFormat.xlXMLSpreadsheet. Thường không sử dụng thuộc tính này password:
• Password dùng để mởi file. (giống với open file) write_res_password:
• Password dùng để modify file. (giống với open file) read_only_recommend:
• true/false. True để thiết lập thuộc tính file là Read-Only create_backup:
• true/false. Thiết lập true để tạo file backup access_mode:
• Kiểu truy cập file. Mặc định XlSaveAsAccessMode.xlNoChange conflict_resolution: Cách giải quyết conflict khi có nhiều user cập nhật vào 1 file. (Enum: XlSaveConflictResolution)

• xlLocalSessionChanges: Access những thay đổi user local
• xlOtherSessionChanges: Reject tất cả những thay đổi user local
• xlUserResolution: Hiển thị hộp thoại cho người dùng lựa chọn add_to_mru:

• true/false. Excel sẽ add workbook vào “recently used files”. Mặc định false. text_code_page:
• missing_value. Không sử dụng text_visual_layout: -missing_value. Không sử dụng local:
• true/false

e. Đóng file excel

this.excelWorkbook.Close(save_changes, fileName, route_workbook);

Trong đó: Save_changes:

• true/false. Xác định có lưu file trước khi đóng hay không filename:
• Tên file route_workbook:
• true/false

f. Đóng ứng dụng excel

this.excelApp.Quit();

g. Lấy về tất cả các sheet trong excel

Sheets excelSheets = excelWorkbook.Worksheets;

h. Lấy về Worksheet (theo tên, theo index, active)

// Theo tên:
Worksheet excelWorksheet = (Worksheet)this.excelSheets.get_Item(sheetName);

// Theo index (thứ tự sheet):
Worksheet excelWorksheet = (Worksheet)this.excelSheets.get_Item(sheetIndex);

// Theo Active (sheet đang được thao tác):
Worksheet excelWorksheet = (Worksheet)this.excelWorkbook.ActiveSheet;

i. Lấy giá trị trong Range

Range là một vùng trong excel, Range có thể được xác định theo 2 cách: C1:

Range excelRange = this.excelWorksheet.get_Range(startCell, endCell);
// startCell: string. Chỉ định cell đầu tiên trong range, vd: B3
// endCell: string. Chỉ định cell cuối cùng trong range, vd: E9

C2:

Range excelRange = this.excelWorksheet.get_Range(range, Type.Missing);
// range: string. Được biểu diễn theo dạng gộp, vd B3:E9

Ví dụ duyệt các giá trị trong Range.

Range excelRange = this.excelWorksheet.get_Range(range, Type.Missing);
System.Array cellArray = excelRange.Value2;
for (int i = 1; i <= cellArray.GetUpperBound(0); i++)
{
for (int j = 1; j <= cellArray.GetUpperBound(1); j++)
{
string value = cellArray.GetValue(i, j) != null ?
cellArray.GetValue(i, j).ToString() : string.Empty;
Console.Write(value);
Console.Write(" ");
}
Console.WriteLine();
}

j. Lấy giá trị trong Cell

Một cách tổng quát, Cell là một trường hợp đặc biệt của Range (chỉ có 1 hàng, 1 cột)

 Range excelRange =
(Range)this.excelWorksheet.Cells[rowIndex, columnIndex];
string str = (string)excelRange.Text;

1. Ghi giá trị vào Range

Range excelRange = this.excelWorksheet.get_Range(range, Type.Missing);
excelRange.Value2 = value;

Trong đó: range: string. Xác định kích thước của Range value: object. Có thể là một mảng 1 chiều, mảng 2 chiều, string, int, DateTime ...

k. Ghi giá trị vào Cell

Cũng tương tự như ghi dữ liệu vào Range.

Range excelRange =
(Range)this.excelWorksheet.Cells[rowIndex, columnIndex];
excelRange.Value2 = value;

Trong đó, value cũng có kiểu object. 13. Chèn thêm dòng

Range excelRange = excelWorksheet.get_Range(range, Type.Missing).EntireRow;
excelRange.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);

m. Xóa dòng

Range excelRange = excelWorksheet.get_Range(range, Type.Missing).EntireRow;
excelRange.Delete(XlDeleteShiftDirection.xlShiftUp);

n. Chèn thêm cột

Range excelRange =
excelWorksheet.get_Range(range, Type.Missing).EntireColumn;
excelRange.Insert(XlInsertShiftDirection.xlShiftToRight, Type.Missing);

l. Xóa cột

Range excelRange =
excelWorksheet.get_Range(range, Type.Missing).EntireColumn;
excelRange.Delete(XlDeleteShiftDirection.xlShiftToLeft);

o. Thiết lập font

Range excelRange = this.excelWorksheet.get_Range(range, Type.Missing);
excelRange.Font.Bold = true;
excelRange.Font.Italic = true;
excelRange.Font.Name = “Arial”;
excelRange.Font.Size = 10;
excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
excelRange.VerticalAlignment = XlVAlign.xlVAlignTop;

p. Thiết lập định dạng ngày tháng

Range excelRange = this.excelWorksheet.get_Range(range, Type.Missing);
excelRange.NumberFormat = “MM/dd/yyyy”;

q. Thiết lập border

excelRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle =
XlLineStyle.xlContinuous;
excelRange.Borders[XlBordersIndex.xlEdgeTop].Color =
Color.Blue.ToArgb();
excelRange.Borders[XlBordersIndex.xlEdgeTop].Weight = 2;

Trong đó: XlBordersIndex: enum. Chỉ định cạnh: Top, Bottom, Left, Right XlLineStyle: enum. Chỉ định các dạng đường: Continuous, Double, Dash ... Color: int. Chỉ định mầu Weight: int. Chỉ định độ rộng của đường, có giá trị từ 1 – 4.

//-----------------------------------------------------------------------------------------------------------------
// Note: Để chạy được ứng dụng, cần phải add Reference 2 thư viện (trong thẻ COM) là
// Microsoft Office 11 Object Library và Microsoft Excel Object Library
//-----------------------------------------------------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.OleDb;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel;

namespace Export2Excel

{

public partial class Form1 : Form

{

OleDbConnection Cn;

OleDbCommand Cmd;

public Form1()

{

InitializeComponent();

}

///

/// Nạp dữ liệu trong bảng Products của cơ sở dữ liệu nwind.mdb

/// (cơ sở dữ liệu này có khi cài .net hoặc Offfice,Nếu không có thì search trên Mạng)

///

private void LoadData()

{

//Tạo kết nối đến CSDL

Cn = new OleDbConnection();

Cn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; data source=c:
wind.mdb";

Cn.Open();

//Chọn các bản ghi trong bảng Products

Cmd = new OleDbCommand("Select * from Products", Cn);

OleDbDataAdapter Da = new OleDbDataAdapter(Cmd);

DataSet Ds = new DataSet();

//Điền vào bộ nhớ đệm DataSet

Da.Fill(Ds, "SanPham");

//Hiển thị trên DataGride

dgrProducts.DataSource = Ds;

dgrProducts.DataMember = "SanPham";

//Giải phóng Da, Cmd, Cn

Cmd.Dispose();

Da.Dispose();

Cn.Close();

}

private void Form1_Load(object sender, EventArgs e)

{

try

{

LoadData();

dgrProducts.AllowUserToAddRows = false;

}

catch

{

MessageBox.Show("Bạn cần copy file nwind.mdb vào thư mục C:");

}

}

//Xuất nội dung trong DataGrid ra file excel

private void cmdExport_Click(object sender, EventArgs e)

{

Excel.Application objExcelApp = new Excel.Application(); // tạo một đối tượng ứng dụng excel

Excel.Workbook objExcelWorkbook; // biến trỏ tới một workbook (ứng với tệp excel)

Excel.Worksheet objSheet; // biến trỏ tới một sheet

//Mở ứng dụng excel và hiển thị trên màn hình

objExcelApp.Visible = true;

//Tạo một tài liệu excel (workbook)

objExcelWorkbook = objExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

//Trỏ tới worksheet trong workbook hiện hành

objSheet = (Excel.Worksheet)objExcelWorkbook.Sheets[1];

int i, j;

// Điền tiêu đề của các trường vào dòng đầu tiên trong excel

// chú ý: Trong Excel hay ứng dụng Office nói chung, PHẦN TỬ ĐẦU TIÊN CÓ CHỈ SỐ LÀ 1

for (i = 0; i <>

{

objSheet.Cells[1, i + 1] = dgrProducts.Columns[i].Name.ToString();

}

//Điền dữ liệu vào các hàng tiếp theo

// chú ý: Trong Excel hay ứng dụng Office nói chung, PHẦN TỬ ĐẦU TIÊN CÓ CHỈ SỐ LÀ 1

for (i = 0; i <>

for (j = 0; j <>

{

objSheet.Cells[i + 2, j + 1] = dgrProducts.Rows[i].Cells[j].Value.ToString();

}

}

}

}

Nguồn: Aptech