Trong các ứng dụng hiện tại, việc kết xuất dữ liệu ra file excel là rất phổ biến. Ưu điểm của việc đưa dữ liệu ra file excel: đơn giản, người dùng có thể chỉnh sửa nội dung của file, file excel có thể được download về file client dễ dàng.v.v.
Trong phần này sẽ giới thiệu các thao tác đọc/ghi dữ liệu lên 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
1. Khởi động Excel Application
· Application excelApp = new ApplicationClass();
·
2. 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
3. Tạo một file mới
· Workbook excelWorkbook = this.excelApp.Workbooks.Add(missing_value);
·
4. 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
5. Đó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
6. Đóng ứng dụng excel
· this.excelApp.Quit();
·
7. Lấy về tất cả các sheet trong excel
· Sheets excelSheets = excelWorkbook.Worksheets;
·
8. 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;
·
9. Lấy giá trị
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();
· }
·
10. 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;
·
11. 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
value: object. Có thể là một mảng 1 chiều, mảng 2 chiều, string, int, DateTime ...
12. 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);
·
14. Xóa dòng
· Range excelRange =
· excelWorksheet.get_Range(range, Type.Missing).EntireRow;
· excelRange.Delete(XlDeleteShiftDirection.xlShiftUp);
·
15. Chèn thêm cột
· Range excelRange =
· excelWorksheet.get_Range(range, Type.Missing).EntireColumn;
· excelRange.Insert(XlInsertShiftDirection.xlShiftToRight, Type.Missing);
·
16. Xóa cột
· Range excelRange =
· excelWorksheet.get_Range(range, Type.Missing).EntireColumn;
· excelRange.Delete(XlDeleteShiftDirection.xlShiftToLeft);
·
17. 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;
·
18. 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”;
·
19. 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.
//-----------------------------------------------------------------------------------------------------------------
// Author: By Nguyễn Minh Quý - quynm@utehy.edu.vn; Hưng Yên Aptech
// 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:\\nwind.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();
}
}
}
}
Không có nhận xét nào:
Đăng nhận xét