Cập nhật thông tin chi tiết về Import Và Export Dữ Liệu Từ Excel Vào R mới nhất trên website Trucbachconcert.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Để có thể thực hiện phân tích dữ liệu, trước tiên chúng ta cần phải có dữ liệu, mà dữ liệu có thể có từ nhiều nguồn khác nhau và được lưu trữ dưới các định dạng file khác nhau như .txt, .csv, .xlsx, hay database… Trong bài viết này, Ranalytics sẽ hướng dẫn các bạn cách import và export dữ liệu từ file Excel vào R.
Để import file Excel vào R, chúng ta có thể sử dụng package readxl.
library(readxl) library(dplyr)Giả sử, chúng ta đang có 1 file Excel datasets.xlsx có sẵn trong thư mục package readxl trong đường dẫn sau:
path <- readxl_example("datasets.xlsx") path ## [1] "E:/Rlibrary/readxl/extdata/datasets.xlsx"File Excel datasets.xlsx có 4 sheets: iris, mtcars, chickwts, quakes.
Bây giờ để import dữ liệu iris (sheet đầu tiên trong file Excel này) vào R, chúng ta sẽ sử dụng hàm read_excel() với câu lệnh như sau:
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species ## 1 5.1 3.5 1.4 0.2 setosa ## 2 4.9 3.0 1.4 0.2 setosa ## 3 4.7 3.2 1.3 0.2 setosa ## 4 4.6 3.1 1.5 0.2 setosa ## 5 5.0 3.6 1.4 0.2 setosa ## 6 5.4 3.9 1.7 0.4 setosaTương tự như vậy, nếu chúng ta muốn import dữ liệu mtcars (sheet thứ 2 trong file Excel) vào R, chúng ta chỉ cần chọn option sheet = 2.
## mpg cyl disp hp drat wt qsec vs am gear carb ## 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 ## 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 ## 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 ## 4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1 ## 5 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2 ## 6 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1Như vậy, để import dữ liệu của tất cả các sheet trong file Excel vào R, chúng ta có thể import từng sheet trong file Excel đó vào R bằng việc sử dụng câu lệnh ở trên. Tuy nhiên, việc làm “manual” đó chỉ thích hợp với trường hợp file Excel chỉ gồm 1 vài sheet, còn đối với những trường hợp file Excel bao gồm cả hàng chục hoặc hàng trăm sheet thì chúng ta cần một giải pháp khác thông minh hơn để xử lý vấn đề trên.
Để giải quyết vấn đề trên, chúng ta có thể dùng cách sau:
library(openxlsx) # package cần dùng wb_obj <- loadWorkbook(path) # thống kê những sheets có trong file excel sheet_names <- sheets(wb_obj) # tên của các sheets for (i in 1:length(sheet_names)) { # Đối với lần lượt từng sheet trong file excel, câu lệnh sẽ import thành 1 data frame trong R với tên tương ứng theo từng sheet assign(sheet_names[i], readWorkbook(wb_obj, sheet = i)) }Kết quả: Tất cả các sheet trong file Excel sẽ được import vào R thành các data frame tương ứng (trong Global Environment).
Như vậy, chúng ta đã biết làm thế nào để import dữ liệu từ file Excel vào R. Câu hỏi đặt ra lúc này là: Vậy làm thế nào để export dữ liệu từ R ra file Excel?
Câu trả lời là: Dùng hàm write.xlsx() trong package xlsx.
library(xlsx) write.xlsx(iris, # tên data file = paste0(getwd(),"/","iris_data.xlsx"), # đường dẫn row.names = F )Tuy nhiên, với cách sử dụng xlsx, máy tính đòi hỏi phải cài đặt Java. Do đó, hiện này có một cách thuận tiện hơn, đó là sử dụng package writexl
library(writexl) writexl::write_xlsx(iris, path = "./iris_data.xlsx")Kết quả: Dữ liệu iris đã được export từ R sang Excel (file iris_data.xlsx).
Cách Import Dữ Liệu Từ File Excel Này Vào File Excel Khác
Trong quá trình làm việc nhiều lúc các bạn cần phải import dữ liệu từ một file excel này vào file excel khác, có nhiều cách để các bạn có thể thực hiện yêu cầu này. Hôm nay, hướng dẫn cho các bạn cách import đơn giản mà rất hiệu quả. Việc import dữ liệu qua lại giữa các file excel khác nhau sẽ giúp bạn không phải mất thời gian sao chép hoặc lấy giá trị của từng ô tương ứng cho file excel đích. Điều này tiết kiệm rất nhiều thời gian cho các bạn trong công việc hằng ngày.
Bước 1: Các bạn nên đưa cả file cần lấy dữ liệu ( trong ví dụ là file DuLieuThi.xlsx) và file dữ liệu ( trong ví dụ là file BangDonGia.xlsx) để trong cùng 1 thư mục.
Bước 2: Các bạn mở cả 2 file excel lên. Và lưu ý nhớ rằng trong chúng tôi có sheet cần lấy là sheet BangDo
Bước 4: Dán đường dẫn này vào chính file excel cần lấy dữ liệu (DuLieuThi.xlsx) tại ô cần đưa dữ liệu vào. Lưu ý là phải đặt dấu =’ vào trước đường dẫn. Trong ví dụ này là =’C:UsersAdminDesktopImport excel
Bước 5: Thêm vào sau đường dẫn trên tên của file excel dữ liệu (BangDonGia.xlsx) và sheet chứa dữ liệu theo cú pháp: =’đường dẫn của file cần lấy dữ liệu[tên file excel dữ liệu.xlsx].tên sheet chứa dữ liệu’!
Trong ví dụ này là: =’C:UsersAdminDesktopImport excel[BangDonGia.xlsx].BangDo’!
Lưu ý: Cần phải thêm dấu ‘! vào sau tên của sheet chứa dữ liệu.
Copy đường dẫn =’C:UsersAdminDesktopImport excel[BangDonGia.xlsx].BangDo’! Sau đó vào file excel chứa dữ liệu (cụ thể là file BangDo.xlsx). Dán vào ô đầu tiên của bảng cần lấy dữ liệu.
Bước 7: Sau đó vào lại file excel cần lấy dữ liệu (file DuLieuThi.xlsx) và dán lại đường dẫn và thêm vào sau địa chỉ của ô chứa dữ liệu cần lấy trong BangDonGia.xlsx. Đường dẫn là: =’C:UsersAdminDesktopImport excel[BangDonGia.xlsx].BangDo’!A1.
Để thực hành chức năng này, các bạn có thể download dữ liệu file excel:
Export Dữ Liệu Ra Excel Dùng Poi
Một vấn đề mà tôi nhận được nhiều request nữa đó là làm sao để kết xuất dữ liệu ra Excel nhằm các mục đích báo cáo, tương tác cơ bản người dùng,…
Nhận thấy đó cũng là điều rất cần thiết nên tôi viết bài này nhằm hỗ trợ các bạn gặp khúc mắc vê vấn đề này.
Bạn có thể dùng Eclipse hay IDE nào đó cũng được miễn là bạn reference đến thư viện này. Ở đây tôi dùng Eclipse.
Trong ví dụ này tôi kết nối đến cơ sở dữ liệu Access(bạn có thể khác) để lấy dữ liệu sau đó kết xuất ra excel. Cơ sở dữ liệu này có cấu trúc như sau:
Code của chương trình như sau: Lớp ConnectDB.java
package vovanhai.wordpress.com; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class ConnectDB { private Connection con; public ConnectDB(String accessDBName) throws Exception{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+accessDBName; con=DriverManager.getConnection(url); } /** * Lấy 1 bảng cơ sở dữ liệu * @param tableName là bảng cơ sở dữ liệu cần lấy * @return * @throws Exception */ public ResultSet getTable(String tableName)throws Exception{ String sql="select * from "+tableName; PreparedStatement pstmt=con.prepareStatement(sql); return pstmt.executeQuery(); } /** * Lấy 1 resultset từ câu truy vấn * @param sql là câu truy vấn * @return * @throws Exception */ public ResultSet getTableFromSQL(String sql)throws Exception{ PreparedStatement pstmt=con.prepareStatement(sql); return pstmt.executeQuery(); } /** * Lấy 1 connection * @return */ public Connection getConnection(){ return con; } /** * Đóng kết nối * @throws Exception */ public void CloseConnection()throws Exception{ if(con!=null) con.close(); } }Lớp Export2Office .java
package vovanhai.wordpress.com; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class Export2Office { private Workbook wb; public Export2Office(Workbook wb) { this.wb=wb; } /** * Export 1 result set ra file excel * @param rs là result set cần xuất * @param sheetName là tên của sheet trong workbook * @throws Exception */ public void Export(ResultSet rs, String sheetName)throws Exception{ int nos=wb.getNumberOfSheets(); for (int i = 0; i < nos; i++) { String sn=wb.getSheetName(i); if(sn.equalsIgnoreCase(sheetName)){ sheetName+="$1"; } } Sheet sheet = wb.createSheet(sheetName); ResultSetMetaData rsmd=rs.getMetaData(); Row row = sheet.createRow((short)0); int colNum=rsmd.getColumnCount();//số fields có trong bảng for (int i = 0; i < colNum; i++) { String col=rsmd.getColumnName(i+1); Cell cell=row.createCell(i); cell.setCellValue(col); } while(rs.next()){ row=sheet.createRow(rs.getRow()); for (int i = 0; i < colNum; i++) { String val=rs.getString(i+1); Cell cell=row.createCell(i); cell.setCellValue(val); } } } /** * Lấy tất cả các bảng của cơ sở dữ liệu sau đó export ra thành các sheet trong 1 file excel * @param AccessDBFilePath là đường dẫn tuyệt đối đến file access * @param excelFilePath là đường dẫn đến file excel kết xuất * @throws Exception */ public void ExportAllDBFiles(String AccessDBFilePath,String excelFilePath)throws Exception{ ConnectDB condb=new ConnectDB(AccessDBFilePath); Connection con=condb.getConnection(); DatabaseMetaData dbm=con.getMetaData(); String []types={"TABLE"}; ResultSet rsm= dbm.getTables(null, null, null, types); while(rsm.next()){ String tableName=rsm.getString("TABLE_NAME"); ResultSet rs=condb.getTable(tableName); Export(rs,tableName); } FileOutputStream fos=new FileOutputStream(excelFilePath,true); wb.write(fos); fos.close(); condb.CloseConnection(); } /** * Export 1 table * @param AccessDBFilePath la file access cần export * @param tableName là bảng cần export * @param excelFilePath đường dẫn đến file excel cần tạo * @throws Exception */ public void ExportTable(String AccessDBFilePath,String tableName,String excelFilePath)throws Exception{ ConnectDB condb=new ConnectDB(AccessDBFilePath); ResultSet rs=condb.getTable(tableName); Export(rs,tableName); String fileName=excelFilePath; FileOutputStream fos=new FileOutputStream(fileName,true); wb.write(fos); fos.close(); condb.CloseConnection(); } }Lớp Export2Office_Driver.java
package vovanhai.wordpress.com; import java.io.FileOutputStream; import java.sql.ResultSet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; public class Export2Office_Driver { public static void main(String[] args) throws Exception{ System.out.println("waiting for export..."); long before=System.currentTimeMillis(); Workbook wb=new HSSFWorkbook(); Export2Office ep=new Export2Office(wb); ep.ExportAllDBFiles("school_DB.mdb", "ex1.xls"); Workbook wb2=new HSSFWorkbook(); Export2Office ep2=new Export2Office(wb2); ep2.ExportTable("school_DB.mdb","tblClass", "ex2.xls"); Workbook wb3=new HSSFWorkbook(); ConnectDB condb=new ConnectDB("school_DB.mdb"); Export2Office ep3=new Export2Office(wb3); String sql="select * from tblClass c inner join tblStudents s on s.classID=c.classID "+ "where s.classID='cdth4a'"; ResultSet rs=condb.getTableFromSQL(sql); ep3.Export(rs, "Join"); FileOutputStream fos=new FileOutputStream("ex3.xls",true); wb3.write(fos); fos.close(); condb.CloseConnection(); long after=System.currentTimeMillis(); System.out.println("finished after "+(after-before)+" milliseconds"); } }Chạy thử ứng dụng, sau đó chọn project, nhấn F5 để refresh lại project, ta có kết quả
Mở thử các file excel để thưởng thức nhỉ!
Đọc Dữ Liệu Từ File Excel
I. Mở đầu
Mình ít khi sử dụng đến cách làm này, nên cóp nhặt trên mạng đoạn code này, bạn nào dùng có thể copy thẳng vào trong project.
Yêu cầu:
Giải nén và add các tất cả các file jar vào project ngoại trừ log4j.
Lưu ý là phải add cả những file jar ở trong folder /lib/ và /ooxml-lib/
II. Class đọc file Excel
Mình đặt cái class này ở trong package utilities.
Trong class có 3 method chính:
setExcelFile: Đọc file và xác định data ở sheet nào trong file excel
findCells: Tìm vị trí các cell có chứa data trong sheet
getTestData: Lấy data và lưu vào các array
III. Cách inject data vào trong các test method
1. Cấu trúc file Excel
File Excel phù hợp với Class trên có dạng như sau:
2. Cách lấy đường dẫn đến file linh hoạt
File data nên được đặt trong project luôn, đừng để ở vị trí nào đó ở trên máy ngoài project vì nếu có ai đó làm chung với bạn thì đường dẫn đến file sẽ bị sai. Tất nhiên là đường dẫn đến project ở mỗi máy lại khác nhau nên chúng ta phải có cách để dynamic cái đường dẫn đó để lúc nào cũng đúng dù ở bất kỳ máy nào.
Cái File.separator chính là cái ký tự “” hoặc “/”, sẽ tùy vào hệ điều hành để hiển thị.
3. Thực hiện việc đọc file excel vào lúc nào?
Tùy vào mục đích sử dụng để xác định thời điểm đọc file Excel:
Đọc file 1 lần và sẽ run trước tất cả các test
Test nào cần data thì chỉ đọc file ở test đó.
Class test Login sẽ như sau:
Trong đó: sử dụng annotation DataProdiver của TestNG để làm việc inject data vào trong method test.
Một số điểm lưu ý:
Nếu cái dataProdiver method mà bạn đặt ở Class khác thì trên chỗ method Test bạn cần phải khai báo cái class chứa method DataProvider.
Bắt buộc cái method Test phải có parameter trùng với số cột được định nghĩa trong file excel (ko tính 2 cái từ ở góc dùng để đánh dấu). Giả sử cái file data có thêm 1 cột nữa là email thì ở trong Method Test cũng phải có thêm parameter Email
Không nên định nghĩa pass/fail ở trong file data vì 2 TH pass và fail sẽ có cách check kết quả khác nhau
Càng không nên lưu pass/fail ra file excel vì bạn đã có 1 cái report của TestNG rồi, giờ còn phải mò mẫm vào file excel để xem kết quả pass fail thì quá khổ.
Và đây là kết quả sau khi run test, mình chỉ làm demo nên giá trị đầu vào nó ko đúng với tiêu chí test lắm.
Bạn đang xem bài viết Import Và Export Dữ Liệu Từ Excel Vào R trên website Trucbachconcert.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!