Xuất Dữ Liệu Ra File Excel Trong Java / Top 7 # Xem Nhiều Nhất & Mới Nhất 2/2023 # Top View | Hoisinhvienqnam.edu.vn

Java: Cách Tạo Và Chèn Dữ Liệu Vào File Excel

Trong Java, việc đọc tệp excel và ghi tệp excel có một chút khó khăn vì trang tính Excel có các ô để lưu trữ dữ liệu. Java không cung cấp API trực tiếp để đọc hoặc viết các tài liệu Microsoft Excel hoặc Word. Ta sẽ phải dựa vào thư viện của bên thứ ba là Apache POI. Trong phần này, chúng ta sẽ học cách tạo một tệp excel bằng Java và cách ghi hoặc chèn dữ liệu vào tệp excel bằng thư viện Apache POI Java.

1. Thư viện POI Java Apache

Apache POI (Thực hiện giải mã kém) là một API Java để đọc và ghi Tài liệu Microsoft. Nó chứa các lớp và giao diện. Thư viện Apache POI cung cấp hai cách triển khai để đọc hoặc ghi tệp excel:

Triển khai HSSF (Horrible SpreadSheet Format): Nó biểu thị một API đang hoạt động với Excel 2003 hoặc các phiên bản cũ hơn.

Triển khai XSSF (XML SpreadSheet Format): Nó biểu thị một API đang hoạt động với phiên bản Excel 2007 trở lên.

Trong phần này sẽ sẽ sử dụng triển khai HSSF.

2. Tạo file Excel trong Java

Bước 1: Tạo một dự án Java với tên CreateExcelFile từ IntelliJ.

Bước 2: Tạo một lớp tên CreateExcelFileExample1.

Bước 2: Tải xuống thư viện Apache POI ( poi-3.17.jar).

Bước 5: Nhấp chọn Libraries sau đó nhấn dấu + và chọn Java như hình dưới:

Bước 6: Tìm đến nơi chứa file chúng tôi rồi chọn và nhấp vào nút OK. Điều này sẽ thêm tệp JAR vào dự án. Sau đó, nhấp vào nút Apply để áp dụng các thay đổi rồi nhấn nút OK.

Sau khi đã hoàn thành tất cả các bước trên, cấu trúc dự án sẽ giống như sau:

Giờ ta sẽ tiếp tục với các đoạn mã:

Trong chương trình sau, ta sử dụng thư viện Apache POI để tạo một file excel. Thư viện cung cấp lớp có tên HSSFWorkbook được định nghĩa trong gói org.apache.poi.hssf.usermodel.

CreateExcelFileExample1.java ​

import java.io.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; public class CreateExcelFileExample1 { public static void main(String[] args) throws IOException {

Kết quả:

“C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1jbrbinjava.exe” “-javaagent:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1libidea_rt.jar=58544:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1bin” -Dfile.encoding=UTF-8 -classpath E:CoursesJavaCreateExcelFileoutproductionCreateExcelFile;E:CoursesJavaCreateExcelFilepoi-3.17.jar CreateExcelFileExample1 File Excel đã được tạo thành công.

Ta đã tạo được một file excel trống tại vị trí được chỉ định.

Giờ ta tạo một chương trình Java khác để tạo một tệp excel.

CreateExcelFileExample2.java​

import java.io.*; public class CreateExcelFileExample2 { public static void main(String[] args) { try { String filename = "C: \ Users \ Anubhav \ Desktop \ CustomersDetail.xlsx"; FileOutputStream fileOut = new FileOutputStream(filename); fileOut.close(); System.out.println("File Excel được tạo thành công."); } catch (Exception e) { e.printStackTrace(); } } }

Kết quả:

“C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1jbrbinjava.exe” “-javaagent:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1libidea_rt.jar=58581:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1bin” -Dfile.encoding=UTF-8 -classpath E:CoursesJavaCreateExcelFileoutproductionCreateExcelFile;E:CoursesJavaCreateExcelFilepoi-3.17.jar CreateExcelFileExample2 File Excel được tạo thành công.

Ta đã tạo được một tệp excel trống tại vị trí được chỉ định.

3. Tạo và chèn dữ liệu vào file Excel

CreateExcelFileExample3.java

import java.io.*; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFRow; public class CreateExcelFileExample3 { public static void main(String[] args) { try { ""//khai báo tên file muốn tạo String filename = "E: \ Courses \ Java \ CreateExcelFile \ Excel3.xlsx";//tạo một đối tượng của lớp HSSFWorkbook HSSFWorkbook workbook = new HSSFWorkbook();//gọi phương thức creatSheet() và truyền tên file muốn tạo HSSFSheet sheet = workbook.createSheet("January");//tạo hàng thứ 0 sử dụng phương thức createRow() HSSFRow rowhead = sheet.createRow((short) 0);//tạo ô bằng cách sử dụng phương thức createCell() và thiết lập giá trị cho ô bằng cách sử dụng phương thức setCellValue() rowhead.createCell(0).setCellValue("S.No."); rowhead.createCell(1).setCellValue("Customer Name"); rowhead.createCell(2).setCellValue("Account Number"); rowhead.createCell(3).setCellValue("e-mail"); rowhead.createCell(4).setCellValue("Balance");//tạo hàng thứ 1 HSSFRow row = sheet.createRow((short) 1);//chèn dữ liệu vào hàng thứ 1 row.createCell(0).setCellValue("1"); row.createCell(1).setCellValue("John William"); row.createCell(2).setCellValue("9999999"); row.createCell(3).setCellValue(row.createCell(4).setCellValue("700000.00");//tạo hàng thứ 2 HSSFRow row1 = sheet.createRow((short) 2);//chèn dữ liệu vào hàng thứ 2 row1.createCell(0).setCellValue("2"); row1.createCell(1).setCellValue("Mathew Parker"); row1.createCell(2).setCellValue("22222222"); row1.createCell(3).setCellValue(row1.createCell(4).setCellValue("200000.00"); FileOutputStream fileOut = new FileOutputStream(filename); workbook.write(fileOut);//đóng stream fileOut.close();//đóng workbook workbook.close();//in thông báo tạo thành công System.out.println("File Excel đã được tạo thành công."); } catch (Exception e) { e.printStackTrace(); } } }[email protected]");[email protected]");

Kết quả:

“C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1jbrbinjava.exe” “-javaagent:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1libidea_rt.jar=58597:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2019.3.1bin” -Dfile.encoding=UTF-8 -classpath E:CoursesJavaCreateExcelFileoutproductionCreateExcelFile;E:CoursesJavaCreateExcelFilepoi-3.17.jar CreateExcelFileExample3 File Excel đã được tạo thành công.

Nó tạo một tệp excel tại vị trí được chỉ định với các giá trị mà ta đã chèn bằng cách sử dụng phương thức setCellValue().

Đọ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.

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 Ghi File Excel Trong Java Sử Dụng Apache Poi

1- Apache POI là gì?

Apache POI là một thư viện mã nguồn mở Java, được cung cấp bởi Apache, nó là một thư viện đầy sức mạnh giúp bạn làm việc với các tài liệu của Microsoft như Word, Excel, Power point, Visio,…

POI là viết tắt của“Poor Obfuscation Implementation”. Các định dạng file của Microsoft được giấu kín. Những kỹ sư của Apache phải cố gắng để tìm hiểu nó, và họ thấy rằng Microsoft đã tạo ra các định dạng phức tạp một cách không cần thiết. Và cái tên thư viện bắt nguồn từ sự hài ước.

Poor Obfuscation Implementation: Sự thực hiện cái nghèo nàn ngu muội. (Tạm dịch là vậy).

Trong tài liệu này tôi hướng dẫn các bạn sử dụng Apache POI để làm việc với Excel.

Apache POI hỗ trợ bạn làm việc với các định dạng của Microsoft, các class của nó thường có tiếp đầu ngữ HSSF, XSSF, HPSF, … Nhìn vào tiếp đầu ngữ của một class bạn có thể biết được class đó hỗ trợ loại định dạng nào.

Chẳng hạn để làm việc với các định dạng Excel (XLS) bạn cần các class:

HSSFWorkbook

HSSFSheet

HSSFCellStyle

HSSFDataFormat

HSSFFont

Apache POI cung cấp cho bạn các interfaceWorkbook,Sheet,Row,Cell,… và các class thể hiện (implementation) tương ứng là HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell,…

Nếu project của bạn sử dụng Maven, bạn chỉ cần khai báo thư viện một cách đơn giản trong chúng tôi :

Nếu bạn không sử dụng Maven bạn có thể download thư viện Apache POI tại:

Download về và giải nén, để làm việc với Excel bạn cần ít nhất 3 file jar:

poi-**.jar

lib/commons-codec-**.jar

lib/commons-collections4-**.jar

Trong tài liệu này, tôi tạo một Project Maven đơn giản có tên ApachePOIExcel

Group ID: org.o7planning

Artifact ID: ApachePOIExcel

Microsoft Office các phiên bản trước đây (97-2003) các file excel có định dạng XLS và các phiên bản mới thường sử dụng định dạng XSLX. Để thao tác với các file XSL bạn cần sử dụng các class có tiếp đầu ngữ HSSF. Còn đối với các file định dạng XSLX cần sử dụng các class có tiếp đầu ngữ XSSF.

Chú ý: Trong tài liệu này tôi đang sử dụng Apache POI 3.15, API có nhiều thay đổi so với phiên bản cũ hơn. Có nhiều phương thức sẽ bị loại bỏ trong phiên bản tương lai (Apache POI 4.x). POI đang hướng tới sử dụng Enum thay thế cho các hằng số trong API của nó.

7- Cập nhập file Excel có sẵn

Trong ví dụ này, tôi đọc file excel chúng tôi và cập nhập các giá trị cho cột Salary tăng lên 2 lần.

Kết quả sau khi cập nhập:

Nếu bạn có kiến thức về Excel, sẽ không khó để bạn thiết lập một công thức. Với Apache POI bạn có thể tạo một Cell có kiểu CellType.FORMULA, giá trị của nó được tính dựa trên một công thức.

SUM

Ví dụ: Tính tổng các ô trên cùng cột “C” từ dòng thứ 2 tới dòng thứ 4:

cell = row.createCell(rowIndex, CellType.FORMULA); cell.setCellFormula("SUM(C2:C4)");

Công thức từ các ô riêng lẻ:

cell = row.createCell(rowIndex, CellType.FORMULA); cell.setCellFormula("0.1*C2*D3");

Với một cell có kiểu FORMULA bạn có thể in ra công thức của nó và sử dụng FormulaEvaluator để tính toán giá trị của ô cho bởi công thức đó.

String formula = cell.getCellFormula(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); CellValue cellValue = evaluator.evaluate(cell); double value = cellValue.getNumberValue(); String value = cellValue.getStringValue(); boolean value = cellValue.getBooleanValue();