Dịch vụ excel còn lại api

Excel là định dạng tệp biến rất phổ biến được tạo bởi Microsoft. Thông thường, các ứng dụng Java sử dụng thư viện Apache POI để đọc và ghi tệp Excel. Trong bài này, tôi sẽ hướng dẫn cách đọc và ghi các tập tin Excel sử dụng API của thư viện Apache POI

Giới thiệu về Apache POI?

Apache POI là một thư viện mã nguồn mở Java, được cung cấp bởi Apache. Thư viện này cung cấp các API (phương thức) làm việc với các tài liệu của Microsoft như Word, Excel, Power point, Visio,…

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

• HSSF (Định dạng bảng tính khủng khiếp). Đọc và ghi tệp định dạng Microsoft Excel (XLS – định dạng hỗ trợ của Excel 2003)
• XSSF (Định dạng bảng tính XML). Đọc và ghi định dạng tệp Open Office XML (XLSX – định dạng hỗ trợ của Excel 2007 trở lên)
• SXSSF (Phiên bản phát trực tuyến của XSSFWorkbook). SXSSF là một API mở rộng của XSSF, được sử dụng khi xuất các tệp excel lớn và có giới hạn bộ nhớ heap sapce.

Tổng quan Apache POI Excel

Microsoft Excel hiện có 2 phần mở rộng

• xls. tương thích với phiên bản Microsoft Excel 2003 trở về trước. Định dạng này được Apache POI hỗ trợ bởi các lớp java với ngôn ngữ tiếp theo là HSSF
• xlsx. tương thích với phiên bản Microsoft Excel 2007 trở về sau. Định dạng này được Apache POI hỗ trợ bởi các lớp java với ngôn ngữ tiếp theo là XSSF, SXSSF

Một số khái niệm cơ bản của API Apache

Apache POI cung cấp cho bạn các giao diện Workbook, Sheet, Row, Cell,… và các class có thể hiện (implementation) tương ứng

• Sách bài tập. đại diện cho một tập tin Excel. Nó được phát triển dưới hai lớp là. HSSFWorkbook and XSSFWorkbook match for format. xls and. xlsx
• Tờ giấy. đại diện cho một bảng tính Excel (một file Excel có thể có nhiều Sheet). Nó có 2 lớp là HSSFSheet và XSSFSheet
• Hàng ngang. đại diện cho một hàng trong một bảng tính (Sheet). Nó có 2 lớp là HSSFrow và XSSFRow
• Tế bào. đại diện cho một ô trong một hàng (Hàng). Tương tự nó cũng có 2 lớp là HSSFCell và XSSFCell

Khai báo thư viện Apache POI

Tạo Maven project và khai báo thư viện trong file pom. xml of project as after

    org.apache.poi
    poi
    3.17

 



    org.apache.poi
    poi-ooxml
    3.17

Lưu ý. Phiên bản cũ của Apache POI chỉ hỗ trợ các định dạng tệp nhị phân như doc, xls, ppt, …. Word version 3. 5 trở đi, POI hỗ trợ các định dạng file OOXML của MS-Office như docx, xlsx, pptx,…

Ví dụ đọc và ghi tệp Excel

Microsoft Office các phiên bản trước đây (97-2003) các tệp excel có định dạng. xls và các phiên bản mới hơn (2007 trở về sau) thường sử dụng định dạng. xlsx. To thao tác với các tập tin. xls cần sử dụng các lớp có tiếp đầu ngữ HSSF. Còn đối với các định dạng tệp. xlsx cần sử dụng các lớp có tên XSSF tiếp theo

Ví dụ ghi file excel (. xl,. xlsx)

Sách. java

package com.gpcoder.apachepoi;
 
public class Book {
    private Integer id;
    private String title;
    private Integer quantity;
    private Double price;
    private Double totalMoney;
}

ViếtVí dụExcel. java

package com.gpcoder.apachepoi;
 
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class WriteExcelExample {
    public static final int COLUMN_INDEX_ID         = 0;
    public static final int COLUMN_INDEX_TITLE      = 1;
    public static final int COLUMN_INDEX_PRICE      = 2;
    public static final int COLUMN_INDEX_QUANTITY   = 3;
    public static final int COLUMN_INDEX_TOTAL      = 4;
    private static CellStyle cellStyleFormatNumber = null;
     
    public static void main(String[] args) throws IOException {
        final List books = getBooks();
        final String excelFilePath = "C:/demo/books.xlsx";
        writeExcel(books, excelFilePath);
    }
 
    public static void writeExcel(List books, String excelFilePath) throws IOException {
        // Create Workbook
        Workbook workbook = getWorkbook(excelFilePath);
 
        // Create sheet
        Sheet sheet = workbook.createSheet("Books"); // Create sheet with sheet name
 
        int rowIndex = 0;
         
        // Write header
        writeHeader(sheet, rowIndex);
 
        // Write data
        rowIndex++;
        for (Book book : books) {
            // Create row
            Row row = sheet.createRow(rowIndex);
            // Write data on row
            writeBook(book, row);
            rowIndex++;
        }
         
        // Write footer
        writeFooter(sheet, rowIndex);
 
        // Auto resize column witdth
        int numberOfColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        autosizeColumn(sheet, numberOfColumn);
 
        // Create file excel
        createOutputFile(workbook, excelFilePath);
        System.out.println("Done!!!");
    }
 
    // Create dummy data
    private static List getBooks() {
        List listBook = new ArrayList<>();
        Book book;
        for (int i = 1; i <= 5; i++) {
            book = new Book(i, "Book " + i, i * 2, i * 1000);
            listBook.add(book);
        }
        return listBook;
    }
 
    // Create workbook
    private static Workbook getWorkbook(String excelFilePath) throws IOException {
        Workbook workbook = null;
 
        if (excelFilePath.endsWith("xlsx")) {
            workbook = new XSSFWorkbook();
        } else if (excelFilePath.endsWith("xls")) {
            workbook = new HSSFWorkbook();
        } else {
            throw new IllegalArgumentException("The specified file is not Excel file");
        }
 
        return workbook;
    }
 
    // Write header with format
    private static void writeHeader(Sheet sheet, int rowIndex) {
        // create CellStyle
        CellStyle cellStyle = createStyleForHeader(sheet);
         
        // Create row
        Row row = sheet.createRow(rowIndex);
         
        // Create cells
        Cell 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, Row row) {
        if (cellStyleFormatNumber == null) {
            // Format number
            short format = (short)BuiltinFormats.getBuiltinFormat("#,##0");
            // DataFormat df = workbook.createDataFormat();
            // short format = df.getFormat("#,##0");
             
            //Create CellStyle
            Workbook workbook = row.getSheet().getWorkbook();
            cellStyleFormatNumber = workbook.createCellStyle();
            cellStyleFormatNumber.setDataFormat(format);
        }
         
        Cell 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(Sheet sheet, int rowIndex) {
        // Create row
        Row row = sheet.createRow(rowIndex);
        Cell cell = row.createCell(COLUMN_INDEX_TOTAL, CellType.FORMULA);
        cell.setCellFormula("SUM(E2:E6)");
    }
     
    // Auto resize column width
    private static void autosizeColumn(Sheet sheet, int lastColumn) {
        for (int columnIndex = 0; columnIndex < lastColumn; columnIndex++) {
            sheet.autoSizeColumn(columnIndex);
        }
    }
     
    // Create output file
    private static void createOutputFile(Workbook 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 tập tin sách. xlsx được tạo ra trong thư mục C. /demo as after

Ví dụ đọc file excel (. xl,. xlsx)

package com.gpcoder.apachepoi;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ReadExcelExample {
    public static final int COLUMN_INDEX_ID = 0;
    public static final int COLUMN_INDEX_TITLE = 1;
    public static final int COLUMN_INDEX_PRICE = 2;
    public static final int COLUMN_INDEX_QUANTITY = 3;
    public static final int COLUMN_INDEX_TOTAL = 4;
 
    public static void main(String[] args) throws IOException {
        final String excelFilePath = "C:/demo/books.xlsx";
        final List books = readExcel(excelFilePath);
        for (Book book : books) {
            System.out.println(book);
        }
    }
 
    public static List readExcel(String excelFilePath) throws IOException {
        List listBooks = new ArrayList<>();
 
        // Get file
        InputStream inputStream = new FileInputStream(new File(excelFilePath));
 
        // Get workbook
        Workbook workbook = getWorkbook(inputStream, excelFilePath);
 
        // Get sheet
        Sheet sheet = workbook.getSheetAt(0);
 
        // Get all rows
        Iterator iterator = sheet.iterator();
        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            if (nextRow.getRowNum() == 0) {
                // Ignore header
                continue;
            }
 
            // Get all cells
            Iterator cellIterator = nextRow.cellIterator();
 
            // Read cells and set value for book object
            Book book = new Book();
            while (cellIterator.hasNext()) {
                //Read cell
                Cell cell = cellIterator.next();
                Object cellValue = getCellValue(cell);
                if (cellValue == null || cellValue.toString().isEmpty()) {
                    continue;
                }
                // Set value for book object
                int columnIndex = cell.getColumnIndex();
                switch (columnIndex) {
                case COLUMN_INDEX_ID:
                    book.setId(new BigDecimal((double) cellValue).intValue());
                    break;
                case COLUMN_INDEX_TITLE:
                    book.setTitle((String) getCellValue(cell));
                    break;
                case COLUMN_INDEX_QUANTITY:
                    book.setQuantity(new BigDecimal((double) cellValue).intValue());
                    break;
                case COLUMN_INDEX_PRICE:
                    book.setPrice((Double) getCellValue(cell));
                    break;
                case COLUMN_INDEX_TOTAL:
                    book.setTotalMoney((Double) getCellValue(cell));
                    break;
                default:
                    break;
                }
 
            }
            listBooks.add(book);
        }
 
        workbook.close();
        inputStream.close();
 
        return listBooks;
    }
 
    // Get Workbook
    private static Workbook getWorkbook(InputStream inputStream, String excelFilePath) throws IOException {
        Workbook workbook = null;
        if (excelFilePath.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else if (excelFilePath.endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("The specified file is not Excel file");
        }
 
        return workbook;
    }
 
    // Get cell value
    private static Object getCellValue(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();
        Object cellValue = null;
        switch (cellType) {
        case BOOLEAN:
            cellValue = cell.getBooleanCellValue();
            break;
        case FORMULA:
            Workbook workbook = cell.getSheet().getWorkbook();
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            cellValue = evaluator.evaluate(cell).getNumberValue();
            break;
        case NUMERIC:
            cellValue = cell.getNumericCellValue();
            break;
        case STRING:
            cellValue = cell.getStringCellValue();
            break;
        case _NONE:
        case BLANK:
        case ERROR:
            break;
        default:
            break;
        }
 
        return cellValue;
    }
}

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

Book [id=1, title=Book 1, quantity=2, price=1000.0, totalMoney=2000.0]
Book [id=2, title=Book 2, quantity=4, price=2000.0, totalMoney=8000.0]
Book [id=3, title=Book 3, quantity=6, price=3000.0, totalMoney=18000.0]
Book [id=4, title=Book 4, quantity=8, price=4000.0, totalMoney=32000.0]
Book [id=5, title=Book 5, quantity=10, price=5000.0, totalMoney=50000.0]
Book [id=null, title=null, quantity=null, price=null, totalMoney=110000.0]

Lưu ý

Các kiểu dữ liệu khi đọc từ tệp excel sẽ có giá trị là kiểu kép

Nguồn. https. //gpcoder. com/3144-huong-dan-doc-va-ghi-file-excel-trong-java-su-dung-thu-vien-apache-poi/