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

--- Bài mới hơn ---

  • Làm Cách Nào Để Chèn Hoặc Nhúng Thư Outlook Vào Excel?
  • Cách Xem Nhanh Tệp Tin Đính Kèm Trong Outlook
  • Những Lưu Ý Sử Dụng Bộ Microsoft Office Cho Windows 10
  • Hướng Dẫn Lập Dự Toán Xây Dựng Công Trình Trên Excel
  • Tải File Về Không Mở Được Trên Điện Thoại
  • 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 Javacá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 SpadSheet 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 SpadSheet 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) { 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 2022.3.1jbrbinjava.exe” “-javaagent:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2022.3.1libidea_rt.jar=58581:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2022.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");[email protected]");

    Kết quả:

    “C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2022.3.1jbrbinjava.exe” “-javaagent:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2022.3.1libidea_rt.jar=58597:C:Program FilesJetBrainsIntelliJ IDEA Community Edition 2022.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().

    --- Bài cũ hơn ---

  • Đọc Ghi Excel File Sử Dụng Apache Poi
  • Thao Tác Với Excel File Bằng Apache Poi
  • Hàm Vlookup Với Một Số Ví Dụ Cơ Bản Và Nâng Cao Trong Excel
  • Làm Cách Nào Để Đếm / Chỉ Tính Tổng Các Số Dương Hoặc Số Âm Trong Excel?
  • Hướng Dẫn Cách Dùng Hàm Nối Ký Tự Trong Excel
  • Hướng Dẫn Xuất Dữ Liệu Lớn Ra File Excel Với Thư Viện Apache Poi

    --- Bài mới hơn ---

  • Đọc Ghi File Ms Word Bằng Java Với Apache Poi
  • Đọc Ghi File Excel Trong Java Với Jxl
  • Đọc Dữ Liệu Từ File Excel
  • Tổng Hợp Các Hàm Excel Thường Dùng Trong Kế Toán Kho 2022
  • Làm Kế Toán Bằng Phần Mềm Excel Có Phù Hợp Với Doanh Nghiệp Của Bạn?
  • Trong bài viết trước, tôi đã hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI. Với lượng dữ liệu ít khoảng vài nghìn dòng trở lại, khi xuất excel và .xlsx chúng ta có thể sử dụng các lớp có tiếp đầu ngữ HSSF, XSSF để xuất dữ liệu ra file excel mà không ảnh hưởng nhiều đến hiệu suất của chương trình. Tuy nhiên, với dữ liệu rất lớn khoảng vài chục nghìn dòng trở lên thì thời gian xử lý sẽ tương đối chậm, tốn nhiều bộ nhớ. May mắn là thư viện Apache POI còn thêm một class khác là SXSSF giúp chúng ta giải quyết vấn đề này.

    SXSSF (Streaming version of XSSFWorkbook) là một phần mở rộng API của XSSF, được sử dụng khi xuất các file excel lớn và có bộ nhớ heap sapce hạn chế. Do SXSSF mở rộng từ XSSF nên chỉ hỗ trợ xuất file có phần mở rộng là .xlsx ( Microsoft Excel 2007 trở về sau).

    Trong phần tiếp theo của bài này, tôi sẽ hướng dẫn các bạn đọc và ghi file excel sử dụng lớp có tiếp đầu ngữ là SXSSF. Nếu bạn chưa biết cách xuất dữ liệu ra file excel sử dụng thư viện Apache POI thì hãy xem bài viết Hướng dẫn đọc và ghi file excel trong Java sử dụng thư viện Apache POI.

    Để tiện theo dõi, tôi sẽ sử dụng lại ví dụ của bài viết trước, chỉ thay đổi các lớp có tiếp đầu ngữ HSSF, XSSF bằng SXSSF.

    Khởi tạo SXSSF

    Trước khi đi vào phần ví dụ, chúng ta hãy tìm hiểu cách khởi tạo SXSSF:

    // Khởi tạo với giá trị rowAccessWindowSize mặc định 100 SXSSFWorkbook workbook = new SXSSFWorkbook(); // Khởi tạo với giá trị rowAccessWindowSize tùy chỉnh: 50 SXSSFWorkbook workbook = new SXSSFWorkbook(50); // Khởi tạo với giá trị rowAccessWindowSize không giới hạn SXSSFWorkbook workbook = new SXSSFWorkbook(-1);

    Lưu ý:

    • rowAccessWindowSize : xác định số lượng hàng (row) có thể được truy cập nhiều nhất thông qua SXSSFSheet.getRow. Khi một hàng (row) mới được tạo ra thông qua SXSSFSheet.createRow và nếu tổng số các bản ghi vượt quá giá trị được chỉ định (rowAccessWindowSize), khi đó hàng (row) với giá trị chỉ mục thấp nhất sẽ được làm mới (flushed) và không thể được truy cập thông qua SXSSFSheet.getRow nữa.
    • Các thao tác trên SXSSF như: createRow, getRow, autoSizeColumn, … chỉ ảnh hưởng đến các record trong phạm vi rowAccessWindowSize được chỉ định.
    • Phương thức autoSizeColumn: chỉ tự động điều chỉnh cỡ trong phạm vi rowAccessWindowSize được chỉ định. Để có thể autoSizeColumn đúng trên tất cả các record, cần đánh dấu theo dõi các cột trong bảng để tự động điều chỉnh định cỡ. Việc xác định độ rộng phù hợp nhất cho một ô rất đắt, điều này có thể ảnh hưởng đến hiệu suất chương trình.

    // Đánh dấu theo dõi thay đổi độ rộng của một cột sheet.trackColumnForAutoSizing(columnIndex); // Đánh dấu theo dõi độ rộng của tất cả các cột sheet.trackAllColumnsForAutoSizing();

    Ví dụ sử dụng SXSSF

    Book.java

    package com.gpcoder.apachepoi; public class Book { private Integer id; private String title; private Integer quantity; private Double price; private Double totalMoney; public Book() { super(); } public Book(Integer id, String title, Integer quantity, double price) { super(); this.id = id; this.title = title; this.quantity = quantity; this.price = price; } @Override public String toString() { return "Book args) throws IOException { final String excelFilePath = "C:/demo/books_large.xlsx"; writeExcel(books, excelFilePath); } // Create Workbook SXSSFWorkbook workbook = new SXSSFWorkbook(); // Create sheet SXSSFSheet sheet = workbook.createSheet("Books"); // Create sheet with sheet name // register the columns you wish to track and compute the column width sheet.trackAllColumnsForAutoSizing(); int rowIndex = 0; // Write header writeHeader(sheet, rowIndex); // Write data rowIndex++; for (Book book : books) { // Create row SXSSFRow row = sheet.createRow(rowIndex); // Write data on row writeBook(book, row); rowIndex++; } // Write footer writeFooter(sheet, rowIndex); // Auto resize column witdth int numberOfColumn = 5; // sheet.getRow(0).getPhysicalNumberOfCells(); autosizeColumn(sheet, numberOfColumn); // Create file excel createOutputFile(workbook, excelFilePath); System.out.println("Done!!!"); } // Create dummy data Book book; for (int i = 1; i <= 5; i++) { book = new Book(i, "Book " + i, i * 2, i * 1000); listBook.add(book); } return listBook; } // Write header with format private static void writeHeader(SXSSFSheet sheet, int rowIndex) { // create CellStyle CellStyle cellStyle = createStyleForHeader(sheet); // Create row SXSSFRow row = sheet.createRow(rowIndex); // Create cells SXSSFCell cell = row.createCell(COLUMN_INDEX_ID); cell.setCellStyle(cellStyle); cell.setCellValue("Id"); cell = row.createCell(COLUMN_INDEX_TITLE); cell.setCellStyle(cellStyle); cell.setCellValue("Title"); cell = row.createCell(COLUMN_INDEX_PRICE); cell.setCellStyle(cellStyle); cell.setCellValue("Price"); cell = row.createCell(COLUMN_INDEX_QUANTITY); cell.setCellStyle(cellStyle); cell.setCellValue("Quantity"); cell = row.createCell(COLUMN_INDEX_TOTAL); cell.setCellStyle(cellStyle); cell.setCellValue("Total money"); } // Write data private static void writeBook(Book book, SXSSFRow row) { if (cellStyleFormatNumber == null) { // Format number short format = (short) BuiltinFormats.getBuiltinFormat("#,##0"); // DataFormat df = workbook.createDataFormat(); // short format = df.getFormat("#,##0"); // Create CellStyle SXSSFWorkbook workbook = row.getSheet().getWorkbook(); cellStyleFormatNumber = workbook.createCellStyle(); cellStyleFormatNumber.setDataFormat(format); } SXSSFCell cell = row.createCell(COLUMN_INDEX_ID); cell.setCellValue(book.getId()); cell = row.createCell(COLUMN_INDEX_TITLE); cell.setCellValue(book.getTitle()); cell = row.createCell(COLUMN_INDEX_PRICE); cell.setCellValue(book.getPrice()); cell.setCellStyle(cellStyleFormatNumber); cell = row.createCell(COLUMN_INDEX_QUANTITY); cell.setCellValue(book.getQuantity()); // Create cell formula // totalMoney = price * quantity cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA); cell.setCellStyle(cellStyleFormatNumber); int currentRow = row.getRowNum() + 1; String columnPrice = CellReference.convertNumToColString(COLUMN_INDEX_PRICE); String columnQuantity = CellReference.convertNumToColString(COLUMN_INDEX_QUANTITY); cell.setCellFormula(columnPrice + currentRow + "*" + columnQuantity + currentRow); } // Create CellStyle for header private static CellStyle createStyleForHeader(Sheet sheet) { // Create font Font font = sheet.getWorkbook().createFont(); font.setFontName("Times New Roman"); font.setBold(true); font.setFontHeightInPoints((short) 14); // font size font.setColor(IndexedColors.WHITE.getIndex()); // text color // Create CellStyle CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setBorderBottom(BorderStyle.THIN); return cellStyle; } // Write footer private static void writeFooter(SXSSFSheet sheet, int rowIndex) { // Create row SXSSFRow row = sheet.createRow(rowIndex); SXSSFCell cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA); cell.setCellFormula("SUM(E2:E6)"); } // Auto resize column width private static void autosizeColumn(SXSSFSheet sheet, int lastColumn) { for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) { sheet.autoSizeColumn(columnIndex); } } // Create output file private static void createOutputFile(SXSSFWorkbook workbook, String excelFilePath) throws IOException { try (OutputStream os = new FileOutputStream(excelFilePath)) { workbook.write(os); } } }

    Thực thi chương trình trên, một file books_large.xlsx được tạo ra trong thư mục C:/demo như sau:

    So sánh hiệu xuất chương trình khi sử dụng SXSSF và XSSF

    Để so sánh hiệu suất của chương trình khi sử dụng SXSSF và XSSf, tôi sử dụng lại ví dụ WriteExcelUsingSXSSF ở trên, và ví dụ WriteExcelExample ở bài viết trước.

    Trong ví dụ bên dưới, tôi sử dụng lớp StopWatch của thư viện Apache Common Lang để đo thời gian thực thi của chương trình

    Bây giờ, hãy tăng số lượng dữ liệu cần xuất ra khoảng 100.000 dòng (thay đổi trong phương thức getBooks). Xem kết quả thực thi của 2 chương trình như sau:

    Bây giờ hãy thử xóa bỏ các đoạn code autoresize column ở 2 chương trình, vẫn kiểm tra 100.000 record.

    WriteExcelUsingSXSSF:

    // sheet.trackAllColumnsForAutoSizing(); // int numberOfColumn = sheet.getRow(rowIndex-1).getPhysicalNumberOfCells(); // autosizeColumn(sheet, numberOfColumn);

    Kết quả thực thi chương trình trên:

    // int numberOfColumn = sheet.getRow(rowIndex-1).getPhysicalNumberOfCells(); // autosizeColumn(sheet, numberOfColumn);

    Kết quả thực thi chương trình trên:

    Khi cần xuất dữ liệu lớn ra file .xlsx và không có yêu cầu về autoresize column thì nên sử dụng SXSSF để đạt được hiệu suất tốt hơn.

    Cám ơn các bạn đã quan tâm và theo dõi bài viết, hẹn gặp lại ở các bài viết tiếp theo.

    --- Bài cũ hơn ---

  • Đọc Ghi File Excel Bằng Java Sử Dụng Apache Poi
  • Làm Thế Nào Để Hợp Nhất Nhiều Tệp Excel Thành Một
  • Sử Dụng Excel Làm Máy Tính Tay Của Bạn
  • Cách Sử Dụng Excel Online Trên Máy Tính Của Bạn
  • Những Hàm Cơ Bản Nhất Trong Excel
  • Cấu Trúc Dữ Liệu Trong Java

    --- Bài mới hơn ---

  • Bài 3:tự Học Java: Viết Chương Trình Đầu Tiên “hello World” Bằng Java
  • Chương Trình Java Đầu Tiên
  • Office 2022: Sửa Mọi Lỗi Word, Excel Chậm Chạm (Cực Nhanh)
  • Cách Xoay Chữ Trong Excel, Xoay Ngang, Dọc
  • Hướng Dẫn Hàm Đổi Số Thành Chữ Trong Excel 2007, 2010, 2013, 2022 1/2021
  • Các cấu trúc dữ liệu cung cấp bởi các package tiện ích của Java rất mạnh mẽ và thực hiện các tính năng rộng rãi. Những cấu trúc dữ liệu này bao gồm những interface và class.

    Để hiểu sâu hơn các khái niệm được trình bày trong chương này, mời bạn tham khảo loạt bài: .

    Lớp Enumeration trong Java

    Interface Enumeration bản thân nó không phải là cấu trúc dữ liệu, nhưng rất quan trong bên trong ngữ cảnh sử dụng các cấu trúc dữ liệu khác. Interface Enumeration định nghĩa để nhận các thành phần kế tiếp từ cấu trúc dữ liệu.

    Ví dụ, Enumeration định nghĩa phương thức gọi là nextElement được sử dụng để lấy các thành phần tiếp theo trong cấu trúc dữ liệu chứa nhiều thành phần.

    Để tìm hiểu chi tiết về interface này, bạn truy cập link sau: .

    Lớp BitSet trong Java

    Lớp BitSet trong Java triển khai một nhóm các bit hoặc flag mà có thể được thiết lập và xóa một cách riêng rẽ.

    Class này rất hữu dụng trong trường hợp bạn muốn lưu trữ một tập các giá trị Boolean và chỉ muốn gắn từng bit các giá trị và thiết lập hoặc xóa nó thích hợp.

    Để tìm hiểu chi tiết về class này, bạn truy cập link sau: .

    Lớp Vector trong Java

    Lớp Vector trong Java là tương tự như các mảng dữ liệu Java truyền thống, ngoại trừ việc có thể tăng lưu trữ cho các thành phần mới.

    Giống như mảng, các thành phần trong đối tượng Vector có thể truy cập bởi index.

    Một điều tốt về việc sử dụng Vector là bạn không phải lo lắng về việc cài đặt nó cho một kích cỡ cụ thể ngoài việc tạo ra nó, nó có thể tăng và giảm độ lớn khi cần thiết.

    Để tìm hiểu chi tiết về class này, bạn truy cập link sau: .

    Lớp Stack trong Java

    Lớp Stack trong Java triển khai một last-in-first-out (LIFO) stack các phần tử.

    Bạn có thể nghĩ về stack như một ngăn xếp thẳng đứng các đối tượng, khi bạn thêm một đối tượng mới, bạn lấy nó ở phần đầu các thành phần khác.

    Khi bạn lấy một thành phần trên stack, nó lấy từ trên đỉnh xuống. Theo cách nói khác, thành phần cuối cùng mà bạn thêm vào stack sẽ là thành phần đầu tiên khi lấy ra và ngược lại.

    Để tìm hiểu chi tiết về class này, bạn truy cập link sau: .

    Lớp Dictionary trong Java

    Lớp Dictionary là một abstract class để định nghĩa cấu trúc dữ liệu cho việc liên kết giữa các key tới value.

    Nó thực sự hữu ích trong các trường hợp khi bạn muốn có thể truy cập dữ liệu thông qua một key cụ thể thay vì sử dụng một integer index.

    Khi lớp Dictionary là abstract, nó chỉ cung cấp framework cho một cấu trúc dữ liệu so khớp key thay vì một sự triển khai cụ thể.

    Để tìm hiểu chi tiết về class này, bạn truy cập link sau: .

    Lớp Hashtable trong Java

    Lớp Hashtable cung cấp các ý nghĩa về mặt tổ chức dữ liệu dựa vào cấu trúc mà người dùng định nghĩa key.

    Ví dụ, một danh sách địa chỉ bạn có thể lưu trữ và xếp thứ tự dựa và key như zip code hơn là việc sử dụng tên người.

    Để tìm hiểu chi tiết về class này, bạn truy cập link sau: .

    Lớp Properties trong Java

    Lớp properties là lớp con của Hashtable. Nó được sử dụng để duy trì danh sách các giá trị trong đó key là String và value cũng là một String.

    Lớp Properties được sử dụng bởi nhiều class khác trong Java. Ví dụ, bạn có một kiểu đối tượng trả về bởi System.getProperties() để lấy về các biến môi trường.

    Để tìm hiểu chi tiết về class này, bạn truy cập link sau: .

    Mọi người cho thể tham gia khóa học thứ 6 của vietjackteam (đang tuyển sinh) vào đầu tháng 03/2018 do anh Nguyễn Thanh Tuyền, admin chúng tôi trực tiếp giảng dạy tại Hà Nội. Chi tiết nội dung khóa học tham khỏa link : .Các bạn học CNTT, điện tử viễn thông, đa phương tiện, điện-điện tử, toán tin có thể theo học khóa này. Số lượng các công việc Java hoặc .NET luôn gấp ít nhất 3 lần Android hoặc iOS trên thị trường tuyển dụng.

    Các bạn ở xa học không có điều kiện thời gian có thể tham dự khóa Java online để chủ động cho việc học tập. Trong tháng 4/2018, VietJack khuyến mại giá SỐC chỉ còn 150k cho khóa học, liên hệ facebook admin chúng tôi để thanh toán chuyển khoản hoặc thẻ điện thoại, khóa học bằng Tiếng Việt với gần 100 video, các bạn có thể chủ động bất cứ lúc nào, và xem mãi mãi. Thông tin khóa học tại . Khóa học có rating 4.7/5 trên udemy từ nhận xét của các bạn học viên.

    Mọi người có thể xem demo nội dung khóa học tại địa chỉ

    Loạt bài hướng dẫn của chúng tôi dựa một phần trên nguồn tài liệu của: Tutorialspoint.com

    Follow fanpage của team hoặc facebook cá nhân Nguyễn Thanh Tuyền để tiếp tục theo dõi các loạt bài mới nhất về Java,C,C++,Javascript,HTML,Python,Database,Mobile…. mới nhất của chúng tôi.

    Bài học Java phổ biến tại vietjack.com:

    --- Bài cũ hơn ---

  • Kiểu Dữ Liệu Dictionary Trong Python
  • Hướng Dẫn Cách Thêm Comment, Bỏ Comment Trong Word Đơn Giản Nhất
  • Cách Chèn Các Ghi Chú Trong Word
  • Cách Chèn Ghi Chú Ở Chân Trang Trong Word, Tạo Footnotes Word
  • Cách Chèn Ghi Chú Trong Word ? Hướng Dẫn Hoàn Chỉnh
  • Đọc Dữ Liệu Từ File Excel

    --- Bài mới hơn ---

  • Tổng Hợp Các Hàm Excel Thường Dùng Trong Kế Toán Kho 2022
  • Làm Kế Toán Bằng Phần Mềm Excel Có Phù Hợp Với Doanh Nghiệp Của Bạn?
  • 20 Hàm Excel Thường Dùng Trong Kế Toán
  • 10 Tuyệt Chiêu Excel Cho Kế Toán Quản Trị
  • Các Bước Thiết Lập File Quản Lý Kho Vật Tư Bằng 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/ /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:

    1. setExcelFile: Đọc file và xác định data ở sheet nào trong file excel
    2. findCells: Tìm vị trí các cell có chứa data trong sheet
    3. 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 DataProper 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 dataProper 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ài cũ hơn ---

  • Đọc Ghi File Excel Trong Java Với Jxl
  • Đọc Ghi File Ms Word Bằng Java Với Apache Poi
  • Hướng Dẫn Xuất Dữ Liệu Lớn Ra File Excel Với Thư Viện Apache Poi
  • Đọc Ghi File Excel Bằng Java Sử Dụng Apache Poi
  • Làm Thế Nào Để Hợp Nhất Nhiều Tệp Excel Thành Một
  • Cách Link Dữ Liệu Từ File Excel Này Sang File Excel Khác

    --- Bài mới hơn ---

  • Cách Thêm Các Liên Kết Vào Excel Trong 60 Giây
  • Cách Dùng Hàm Filter Để Lọc Dữ Liệu Trong Office 365
  • Hướng Dẫn Cách Dùng Bộ Lọc Tự Động (Autofilter) Trên Excel
  • Hướng Dẫn Cách Dùng Bộ Lọc Filter Trong Excel Cơ Bản
  • Hướng Dẫn Cách Dùng Bộ Lọc Filter Cho Màu Sắc, Ngày Tháng Và Văn Bản Trong Excel
  • Chắc hẳn bạn đều biết Microsoft Excel là ứng dụng xử lý bảng tính nằm trong bộ MS Office của Microsoft, giúp người dùng trình bày các thông tin xử lý dưới dạng bảng dữ liệu, thực hiện việc tính toán, xây dựng các số liệu thống kê trực quan, hiệu quả. Ngoài những tính năng chính hay dùng thì việc sao chép, copy dữ liệu từ file Excel này sang file Excel khác cũng được thực hiện khá dễ dàng, qua đó giúp người dùng tìm kiếm, sắp xếp dữ liệu nhanh chóng, thuận tiện nhất.

    Là một nhân viên bán hàng hay một quản lý, bạn chắc hẳn không xa lạ gì với Excel. Nó được coi là một trong những công cụ quản lý bán hàng, quản lý công nhân viên,…. vô cùng phổ biến.

    Trong bài viết này, sẽ giới thiệu đến bạn cách di chuyển, copy sheet, link từ file Excel này sang file Excel khác sử dụng Excel 2022. Các phiên bản Excel khác như Excel 2013, 2010 các bạn thực hiện tương tự.

    Để lấy dữ liệu từ file excel này sang file excel khác chúng ta cần thực hiện qua 7 bước sau đây:

    Bước 1: Đầu tiên, bạn mở cả hai file Excel mà bạn cần xử lý lên. Ví dụ ở đây là File Excel 1 và File Excel 2

    Bước 2: Mở nơi lưu file excel cần lấy dữ liệu ( ví dụ ở đây là file excel 1). Nhấp phải chuột vào file đó chọn properties.

    Bước 3: Copy đường dẫn Location chọn OK

    Bước 4: Dán đường dẫn vào file excel cần lấy dữ liệu

    Bước 5: Thêm file excel chứa dữ liệu như bên dưới hình

    Bước 6: Copy đường dẫn =’C:UsersAdminDesktopSheet1′! như bên dưới hình

    Nhấn Enter để xem kết quả.

    Tương tự, nếu bạn có những sheet khác mà cần chuyển sang file Excel khác thì bạn thực hiện tương tự như thao tác hướng dẫn trên. Nếu 2 sheet cùng tên (ví dụ sheet copy là sheet 1 và trong file Excel kia cũng có 1 sheet hiện hành là sheet 1) khi được copy sang thì nó sẽ hiển thị là Sheet1 (2).

    Việc sao chép, chuyển sheet trong Excel ở trên, về cơ bản các thao tác để người dùng thực hiện khá đơn giản. Người dùng có thể di chuyển dữ liệu trong cùng một sheet hoặc có thể di chuyển hẳn sheet đó sang một file mới.

    Theo Nhanh.vn

    --- Bài cũ hơn ---

  • Cách Sử Dụng Hàm Dcount Và Dcounta Trong Microsoft Excel
  • Giới Thiệu Cách Sử Dụng Phím Tắt Để Thực Hiện Việc Xóa Dữ Liệu Trùng Lặp Trong Excel
  • Hướng Dẫn So Sánh Hai Cột Dữ Liệu Trong Excel Để Xóa, Highlight, Tô Màu Dữ Liệu Trùng Lặp
  • Lọc Dữ Liệu Trùng Lặp Trong Google Sheets 2022
  • How To Duplicate A Sheet In Excel
  • Cách Import Dữ Liệu Từ File Excel Này Vào File Excel Khác

    --- Bài mới hơn ---

  • Csv Là Gì? Cách Chuyển Đổi File .csv Sang Excel .xlv
  • Chuyển Đổi Csv Sang Excel: Cách Nhập Tệp Tin Csv Vào Bảng Tính Excel
  • Import Và Export Dữ Liệu Từ Excel Vào R
  • Các Kiểu Dữ Liệu Trong Excel
  • Các Kiểu Dữ Liệu Cơ Bản Trong Bảng Microsoft Access
  • 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.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.BangDo’!A1.

    Để thực hành chức năng này, các bạn có thể download dữ liệu file excel:

    --- Bài cũ hơn ---

  • Hướng Dẫn Import Dữ Liệu Vào Database Từ Tập Tin Excel Trong C#
  • Cách Sử Dụng Hàm Int
  • Hàm Int Và Cách Dùng Hàm Int Trong Excel
  • Hàm Iferror Trong Excel Là Gì? Cách Dùng Đúng Nhất
  • Hàm Iferror Trong Excel Là Gì Và Cách Sử Dụng Như Thế Nào?
  • Export Dữ Liệu Ra Excel Dùng Poi

    --- Bài mới hơn ---

  • Hướng Dẫn Làm Kế Toán Trên Excel
  • Quy Trình Hạch Toán Nghiệp Vụ Bán Hàng Trên Excel
  • Hướng Dẫn Công Việc Của Kế Toán Bán Hàng Chi Tiết
  • Vai Trò Của Excel Trong Doanh Nghiệp Mà Mọi Nhân Viên Nên Nắm Rõ
  • Những Lợi Ích Mà Excel Mang Lại Khi Bạn Giỏi Công Cụ Này
  • 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.wordpss.com; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class ConnectDB { //connection kết nối cơ sở dữ liệu 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.ppareStatement(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.ppareStatement(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.wordpss.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 { //workbook ta làm việc ~ excel file 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"; } } //tạo worksheet Sheet sheet = wb.createSheet(sheetName); //Lấy metadata làm column header ResultSetMetaData rsmd=rs.getMetaData(); //tạo dòng tiêu đề Row row = sheet.createRow((short)0); int colNum=rsmd.getColumnCount();//số fields có trong bảng //duyệt qua các fields lấy làm column header for (int i = 0; i < colNum; i++) { String col=rsmd.getColumnName(i+1); Cell cell=row.createCell(i); cell.setCellValue(col); } //điền giá trị vào các hàng while(rs.next()){ //tạo 1 hàng mới row=sheet.createRow(rs.getRow()); for (int i = 0; i < colNum; i++) { String val=rs.getString(i+1); //tạo 1 ô mới 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 args) throws Exception{ System.out.println("waiting for export..."); long before=System.currentTimeMillis(); //demo 01-export toàn bộ cơ sở dữ liệu thành 1 file excel với mỗi bảng là 1 sheet Workbook wb=new HSSFWorkbook(); Export2Office ep=new Export2Office(wb); ep.ExportAllDBFiles("school_DB.mdb", "ex1.xls"); //demo 02- export 1 bảng ra file excel Workbook wb2=new HSSFWorkbook(); Export2Office ep2=new Export2Office(wb2); ep2.ExportTable("school_DB.mdb","tblClass", "ex2.xls"); //demo 03-export kết quả của 1 câu seleect sql bất kỳ ra excel 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(); //finish 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ỉ!

    --- Bài cũ hơn ---

  • Sử Dụng Apache Poi Để Đọc, Ghi Dữ Liệu Từ File Excel Trong Selenium
  • Thủ Thuật Tìm Kiếm Nhanh File, Settings Trong Windows 10
  • Nhân Sự Hay Dùng Những Hàm Excel Gì ?
  • Nhân Sự Hay Dùng Những Hàm Excel Gì
  • Các Hàm Excel Thông Dụng Nhất Trong Kế Toán Kiểm Toán
  • Đọc Ghi File Excel Trong Java Với Jxl

    --- Bài mới hơn ---

  • Đọc Dữ Liệu Từ File Excel
  • Tổng Hợp Các Hàm Excel Thường Dùng Trong Kế Toán Kho 2022
  • Làm Kế Toán Bằng Phần Mềm Excel Có Phù Hợp Với Doanh Nghiệp Của Bạn?
  • 20 Hàm Excel Thường Dùng Trong Kế Toán
  • 10 Tuyệt Chiêu Excel Cho Kế Toán Quản Trị
  • Tạo và ghi file Excel với Jxl

    Bước 1: Tạo đối tượng WritableWorkbook “trỏ” đến file của bạn. Lưu ý là nếu file của bạn đã tồn tại thì nó sẽ bị xóa đi và tạo lại.

    WritableWorkbook workbook = Workbook.createWorkbook(new File(fileName));

    Bước 2: Tạo WritableSheet – sheet bạn cần ghi dữ liệu:

    WritableSheet sheet = workbook.createSheet("name sheet", 0);

    Lưu ý: trong hàm createSheet có 2 đối số, đối số thứ nhất là chuỗi tên sheet, đối số thứ 2 là một số nguyên chỉ vị trí của sheet, vị trí sheet bắt đầu bằng 0.

    Bước 3: Tiếp theo chúng ta sẽ thêm các dạng dữ liệu vào các ô bằng phương thức addCell. Để viết dữ liệu vào các ô, chúng ta sẽ có 3 dạng chính: Chuỗi, Số và Công thức lần lượt được tạo bằng Label, Number, Formula. Ví dụ:

    sheet.addCell(new Label(0, 0, "Add a String to cell")); // add a String to cell A1 sheet.addCell(new Number(0, 1, 100)); // add number 100 to cell A2 sheet.addCell(new Formula(0, 3, "IF(A1=1,"one", "two")")); // add number 100 to cell A3

    Bước 4: Sau khi chúng ta đã thực hiện xong bước 3, chúng ta cần thực hiện lệnh writeclose để hoàn tất việc ghi dữ liệu

    workbook.write(); workbook.close();

    Đọc file Excel với Jxl

    Bước 1: Tạo Workbook “trỏ” đến file của bạn.

    Workbook workbook = Workbook.getWorkbook(new File(fileName));

    Bước 2: Lấy Sheet bạn muốn đọc. Bạn có thể lấy theo vị trí sheet hoặc tên Sheet

    Sheet sheet = workbook.getSheet(0);

    Bước 3: Đọc nội dung từng ô trong bảng tính. Nếu bạn muốn lấy nội dung của một ô nào đó bạn có thể làm như sau: sheet.getCell(col, row).getContents(). Tuy nhiên nếu bạn muốn đọc toàn bộ các ô trong bảng tính hãy lấy hàng và cột cuối cùng chứa dữ liệu bằng sheet.getRows()sheet.getColumns(), và dùng vòng lặp for để đọc từng ô. Sau khi đọc xong, chúng ta cũng cần close workbook như khi viết dữ liệu

    for (int row = 0; row < rows; row++) { for (int col = 0; col < cols; col++) { Cell cell = sheet.getCell(col, row); System.out.print(cell.getContents() + "t"); } System.out.println("n"); } workbook.close();

    Mở và ghi thêm dữ liệu vào Excel với Jxl

    Để mở và ghi thêm dữ liệu vào file Excel, trước tiên chúng ta cần lấy Workbook từ file Excel cần viết thêm giống như khi chúng ta đọc. Sau đó tạo một WritableWorkbook đến chính workbook vừa lấy và chúng ta sẽ làm việc với WritableWorkbook này bình thường.

    Workbook workbook = Workbook.getWorkbook(new File(fileName)); WritableWorkbook writeWorkbook = Workbook.createWorkbook(new File(fileName), workbook);

    Demo code

    package vietSource.net.IOFile; import java.io.File; import java.io.IOException; import java.util.Scanner; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; import jxl.write.Formula; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; /** */ public class ReadWriteExcel { private final String fileName = "/home/nguyenvanquan7826/Desktop/nguyenvanquan7826.xls"; // data to write file private Object data = { { "STT", "Họ và tên", "Điểm", "Xếp loại" }, { "1", "Nguyễn Văn Quân", "9.0", "" }, { "2", "Phạm Thị Hà", "8.0", "" }, { "3", "Nguyễn Bá Cường", "8.5", "" }, { "4", "Vũ Công Tịnh", "9.0", "" }, { "5", "Phạm Trọng Khang", "8", "" }, { "6", "Mai Văn Tài", "8", "" } }; // create and write new file *.xls private void writeFileExcel() { WritableWorkbook workbook; // create workbook try { workbook = Workbook.createWorkbook(new File(fileName)); // create sheet WritableSheet sheet1 = workbook.createSheet("KTPM K10B", 0); // create Label and add to sheet sheet1.addCell(new Label(0, 0, "DANH SÁCH SINH VIÊN TIÊU BIỂU")); // row begin write data int rowBegin = 2; int colBegin = 0; for (int row = rowBegin, i = 0; row < data.length + rowBegin; row++, i++) { for (int col = colBegin, j = 0; col < data args) { ReadWriteExcel rwExcel = new ReadWriteExcel(); while (true) { rwExcel.showMenu(); Scanner scan = new Scanner(System.in); int select = Integer.parseInt(scan.nextLine()); switch (select) { case 1: rwExcel.writeFileExcel(); break; case 2: rwExcel.readFileExcel(); break; case 3: rwExcel.openAndWriteFileExcel(); break; default: scan.close(); break; } } } }

    --- Bài cũ hơn ---

  • Đọc Ghi File Ms Word Bằng Java Với Apache Poi
  • Hướng Dẫn Xuất Dữ Liệu Lớn Ra File Excel Với Thư Viện Apache Poi
  • Đọc Ghi File Excel Bằng Java Sử Dụng Apache Poi
  • Làm Thế Nào Để Hợp Nhất Nhiều Tệp Excel Thành Một
  • Sử Dụng Excel Làm Máy Tính Tay Của Bạn
  • Cách Sửa Lỗi Ngày Tháng Của Dữ Liệu Được Trích Xuất Từ Phần Mềm Ra Excel Chi Tiết Nhất

    --- Bài mới hơn ---

  • Hướng Dẫn Cách Làm Tròn Số Trong Excel Đơn Giản, Hiệu Quả
  • Làm Cách Nào Để Làm Tròn / Làm Tròn Lên / Làm Tròn Thời Gian Đến Một Phần Tư / Nửa Giờ Gần Nhất Trong Excel?
  • Làm Thế Nào Để Làm Tròn Thời Gian Đến Giờ / Phút / Giây Gần Nhất Trong Excel?
  • Hướng Dẫn Định Dạng Giờ Phút Giây Trong Excel
  • Cách Tính Giá Trị Trung Bình Kết Hợp Làm Tròn Trong Excel
  • Khi trích xuất dữ liệu từ các phần mềm khác (ví dụ như phần mềm chấm công) ra excel, chúng ta thường gặp phải 1 vấn đề đó là dữ liệu ngày tháng, thời gian thường bị lỗi, không thể sử dụng để tính toán ngay. Trong bài viết này chúng tôi sẽ hướng dẫn các bạn cách sửa lỗi ngày tháng của dữ liệu được trích xuất từ phần mềm ra excel.

    Đặc điểm dữ liệu thời gian trích xuất từ phần mềm

    Trong file ví dụ chúng ta có bảng dữ liệu được trích xuất từ phần mềm chấm công như sau:

    Trong đó các cột DateInTime là dữ liệu dạng NgàyGiờ. Tuy nhiên những dữ liệu này lại không dùng để tính toán ngay được vì nó không phải dữ liệu đúng chuẩn dạng thời gian mà Excel quy định.

    Chúng ta có thể kiểm tra bằng cách sử dụng hàm IsNumber cho cột B và C như sau:

    Như vậy về bản chất dữ liệu trong ô B2 là Text chứ không phải Number. Do đó không dùng trong tính toán được.

    Cách sửa lỗi định dạng thời gian

    Hàm VALUE giúp chuyển dữ liệu từ dạng Text về dạng Number. Về bản chất, dữ liệu thời gian trong Excel là dạng Number.

    Cách làm như sau:

    Bước 1: Tại cột E (cột phụ), chúng ta dùng hàm VALUE, đối tượng của hàm VALUE là giá trị trong cột B

    Ví dụ: E2=VALUE(B2)

    Kết quả của đoạn text 1/Jun/2018 khi chuyển sang Number có giá trị là 43252

    Bước 2: Copy kết quả của hàm Value, dán trở lại vào cột B

    Bước 3: Định dạng dữ liệu bằng Format cells

    Chúng ta sẽ định dạng lại dữ liệu trong cột B để đưa về đúng định dạng dd/mm/yyyy bằng cách:

    • Chọn vùng dữ liệu trong cột B
    • Mở Format Cells (phím tắt Ctrl + 1)
    • Trong thẻ Number chọn Custome, trong mục Type nhập dd/mm/yyyy

    Như vậy kết quả thu được sẽ là dữ liệu dạng thời gian có dạng ngày/tháng/năm đúng tiêu chuẩn của Excel.

    Cách 2: Dùng công cụ TextToColumn

    TextToColumn là một công cụ khá hữu ích trong Excel. Ngoài việc tách dữ liệu ra nhiều cột thì nó còn có tác dụng chuyển đổi dữ liệu về đúng định dạng tiêu chuẩn trong Excel. Cách thực hiện như sau:

    Bước 1: Chọn bảng dữ liệu cần chuyển đổi, trong thẻ Data chọn TextToColumn

    Bước 2: Trong cửa sổ TextToColumn (step 1 of 3) bấm Next để sang Step 2 of 3

    Bước 3: Bấm Next một lần nữa để sang Step 3 of 3

    Tại bước này, chúng ta bấm chọn mục Date và chọn tương ứng cách hiển thị thời gian theo dạng DMY (D là ngày, M là tháng, Y là năm)

    Sau đó bấm Finish để hoàn thành.

    • Cả 2 cách đều áp dụng được cho dữ liệu Ngày, Giờ
    • Cách dùng hàm VALUE sẽ tuân thủ theo thiết lập ngày tháng mặc định trong Region của máy tính (Control pannel/ Region/ Date-Time)
    • Cách dùng TextToColumn cho phép tự do lựa chọn cách hiển thị Tháng trước Ngày sau hay Ngày trước tháng sau một cách linh hoạt hơn. Kết quả trả về ngay tại vị trí ban đầu, không cần copy/paste như cách dùng hàm VALUE

    Tìm hiểu thêm các nội dung tương tự:

    Hướng dẫn chuyển kiểu định dạng dữ liệu từ số (Number) sang chữ (Text) trong Excel Hướng dẫn cách chuyển từ ngày tháng (Date) về định dạng số (Number) hoặc chữ (Text) trong Excel Sử dụng hàm VALUE và các tùy chọn khác để chuyển đổi định dạng của số từ text sang number

    Tài liệu kèm theo bài viết

    Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

    Gitiho tự hào khi được đồng hành cùng:

      50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

    --- Bài cũ hơn ---

  • Cách Sử Dụng Hàm Index Để Lấy Mảng Tham Chiếu Trong Excel
  • How To Find Column Index Number From Table In Excel
  • Hướng Dẫn Giải Bài Tập Dùng Hàm Index Kết Hợp Hàm Match Có File Mẫu
  • Hướng Dẫn Cách Dùng Hàm Countif Trong Công Việc Kế Toán Trên Excel
  • Công Thức Excel: Đếm Các Ô Bằng X Và Y
  • Đọc Ghi File Excel Trong Java Sử Dụng Apache Poi

    --- Bài mới hơn ---

  • Hướng Dẫn Đọc Và Ghi File Excel Trong Java Sử Dụng Thư Viện Apache Poi
  • Thao Tác Với File Excel Trong Java Sử Dụng Api Apache Poi
  • Cách Cài Đặt Email Trong Outlook
  • Cách Cài Đặt Và Sử Dụng Excel Trên Linux
  • Cách Thêm Các Số 0 Vào Đầu Các Giá Trị Trong Excel
  • 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:

    // Tạo cell có kiểu FORMULA cell = row.createCell(rowIndex, CellType.FORMULA); // Sét công thức. 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 đó.

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

    --- Bài cũ hơn ---

  • Hàm Vlookup Cách Sử Dụng Và Bài Tập Áp Dụng Vlookup
  • Lập Báo Cáo Tổng Hợp Tự Động Với Excel Và Vba
  • Hàm Concatenate, Hàm Nối Các Chuỗi Ký Tự Trong Excel
  • Hàm Concatenate Trong Excel, Ứng Dụng Để Nối Các Chuỗi, Ô, Mảng Và Cột
  • 3 Hàm Nối Chuỗi Trong Excel Cực Hay Và Đơn Giản Được Sử Dụng Nhất
  • Web hay
  • Links hay
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100