Apache poi tạo trang tính

Học cách đọc excel, viết excel, đánh giá các ô công thức và áp dụng định dạng tùy chỉnh cho các tệp excel được tạo bằng thư viện Apache POI với các ví dụ

Nếu chúng tôi đang xây dựng phần mềm cho lĩnh vực Nhân sự hoặc Tài chính, thường có yêu cầu tạo báo cáo excel giữa các cấp quản lý. Ngoài các báo cáo, chúng tôi cũng có thể mong đợi một số dữ liệu đầu vào cho các ứng dụng ở dạng trang tính excel và ứng dụng được mong đợi sẽ hỗ trợ yêu cầu này

Apache POI là một thư viện đáng tin cậy trong số nhiều thư viện nguồn mở khác để xử lý các trường hợp sử dụng như vậy liên quan đến các tệp excel. Xin lưu ý rằng, ngoài ra, chúng tôi cũng có thể đọc và ghi các tệp MS Word và MS PowerPoint bằng thư viện Apache POI

Hướng dẫn Apache POI này sẽ thảo luận về một số thao tác excel hàng ngày trong các ứng dụng thực tế

Mục lục

1. Phụ thuộc Maven

Nếu chúng tôi đang làm việc trên một dự án maven, chúng tôi có thể bao gồm các phụ thuộc Apache POI trong tệp

package com.howtodoinjava.demo.poi;
//import statements
public class WriteExcelDemo 
{
    public static void main(String[] args) 
    {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook(); 
         
        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");
          
        //This data needs to be written (Object[])
        Map data = new TreeMap();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});
          
        //Iterate over data and write to sheet
        Set keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
               Cell cell = row.createCell(cellnum++);
               if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

  org.apache.poi
  poi
  5.2.2



  org.apache.poi
  poi-ooxml
  5.2.2

2. Các lớp quan trọng trong thư viện POI

1. Các lớp HSSF, XSSF và XSSF

   Các lớp chính của Apache POI thường bắt đầu bằng HSSF, XSSF hoặc SXSSF

   • HSSF – là triển khai Java thuần túy của Dự án POI ở định dạng tệp Excel 97(-2007). e. g. , HSSFWorkbook, HSSFSheet
   • XSSF – là triển khai Java thuần túy của Dự án POI cho Excel 2007 OOXML (. định dạng tệp xlsx). e. g. , Sổ làm việc XSSF, Trang tính XSSF
   • SXSSF (từ 3. 8-beta3) – là tiện ích phát trực tuyến tương thích với API của XSSF được sử dụng khi phải tạo bảng tính lớn và dung lượng lưu trữ bị hạn chế. e. g. , SXSSFWorkbook, SXSSFSheet. SXSSF đạt được dung lượng bộ nhớ thấp bằng cách giới hạn quyền truy cập vào các hàng trong cửa sổ trượt, trong khi XSSF cấp quyền truy cập vào tất cả các hàng trong tài liệu

2. Hàng và Ô

   Ngoài các lớp trên, Hàng và Ô tương tác với một hàng cụ thể và một ô cụ thể trong một bảng excel

3. Các lớp liên quan đến tạo kiểu

   Một loạt các lớp như CellStyle, BuiltinFormats, So sánhOperator, ConditionalFormattingRule, FontFormatting, IndexedColors, PatternFormatting, SheetConditionalFormatting, v.v. được sử dụng khi bạn phải thêm định dạng vào trang tính, chủ yếu dựa trên một số quy tắc

4. Trình đánh giá công thức

   Một lớp FormulaEvaluator hữu ích khác được sử dụng để đánh giá các ô công thức trong một trang tính excel

3. Viết một tệp Excel

Tôi đang lấy ví dụ này trước để chúng ta có thể sử dụng lại bảng excel được tạo bởi mã này trong các ví dụ tiếp theo

Viết excel bằng POI rất đơn giản và bao gồm các bước sau

1. Tạo sổ làm việc
2. Tạo trang tính trong sổ làm việc
3. Tạo một hàng trong trang tính
4. Thêm ô vào trang tính
5. Lặp lại bước 3 và 4 để ghi thêm dữ liệu

Nó có vẻ rất đơn giản, phải không?

Chương trình Java viết file excel sử dụng thư viện Apache POI

package com.howtodoinjava.demo.poi;
//import statements
public class WriteExcelDemo 
{
    public static void main(String[] args) 
    {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook(); 
         
        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");
          
        //This data needs to be written (Object[])
        Map data = new TreeMap();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});
          
        //Iterate over data and write to sheet
        Set keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
               Cell cell = row.createCell(cellnum++);
               if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

Xem thêm. Nối các hàng vào Excel

4. Đọc một tệp Excel

Việc đọc một file excel bằng POI cũng rất đơn giản nếu chúng ta chia thành các bước

1. Tạo phiên bản sổ làm việc từ một trang tính excel
2. Đến trang tính mong muốn
3. Tăng số hàng
4. lặp lại trên tất cả các ô trong một hàng
5. lặp lại bước 3 và 4 cho đến khi tất cả dữ liệu được đọc

Hãy xem tất cả các bước trên trong mã. Tôi đang viết mã để đọc tệp excel được tạo trong ví dụ trên. Nó sẽ đọc tất cả các tên cột và các giá trị trong đó – từng ô một

Chương trình Java để đọc tệp excel bằng thư viện Apache POI

package com.howtodoinjava.demo.poi;
//import statements
public class ReadExcelDemo 
{
    public static void main(String[] args) 
    {
        try
        {
            FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));
 
            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);
 
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) 
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator cellIterator = row.cellIterator();
                 
                while (cellIterator.hasNext()) 
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) 
                    {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "t");
                            break;
                    }
                }
                System.out.println("");
            }
            file.close();
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

Đầu ra chương trình

ID      NAME        LASTNAME
1.0     Amit        Shukla  
2.0     Lokesh      Gupta   
3.0     John        Adwards 
4.0     Brian       Schultz 

Xem thêm. Apache POI – Đọc tệp Excel bằng Trình phân tích cú pháp SAX

5. Thêm và đánh giá các ô công thức

Khi làm việc trên các sheet excel phức tạp, chúng ta gặp nhiều ô có công thức để tính giá trị của chúng. Đây là những ô công thức. Apache POI cũng có hỗ trợ tuyệt vời để thêm các ô công thức và đánh giá các ô công thức đã có

Hãy xem một ví dụ về cách thêm các ô công thức trong excel?

Trang tính có bốn ô liên tiếp và ô thứ tư trong phép nhân của cả 3 hàng trước đó. Vậy công thức sẽ là. A2*B2*C2 (ở hàng thứ hai)

Chương trình Java để thêm công thức vào tệp excel bằng thư viện Apache POI

public static void main(String[] args) 
{
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");
  
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Pricipal");
    header.createCell(1).setCellValue("RoI");
    header.createCell(2).setCellValue("T");
    header.createCell(3).setCellValue("Interest (P r t)");
      
    Row dataRow = sheet.createRow(1);
    dataRow.createCell(0).setCellValue(14500d);
    dataRow.createCell(1).setCellValue(9.25);
    dataRow.createCell(2).setCellValue(3d);
    dataRow.createCell(3).setCellFormula("A2*B2*C2");
      
    try {
        FileOutputStream out =  new FileOutputStream(new File("formulaDemo.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("Excel with foumula cells written successfully");
          
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Tương tự, chúng tôi muốn đọc một tệp có các ô công thức và sử dụng logic sau để đánh giá các ô công thức

Chương trình Java để đánh giá công thức trong tệp excel bằng thư viện Apache POI

public static void readSheetWithFormula()
{
    try
    {
        FileInputStream file = new FileInputStream(new File("formulaDemo.xlsx"));
 
        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
 
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
         
        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
 
        //Iterate through each rows one by one
        Iterator rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) 
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator cellIterator = row.cellIterator();
             
            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                //Check the cell type after eveluating formulae
                //If it is formula cell, it will be evaluated otherwise no change will happen
                switch (evaluator.evaluateInCell(cell).getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "tt");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "tt");
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        //Not again
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }
}

Đầu ra chương trình

Pricipal        RoI         T       Interest (P r t)        
14500.0         9.25        3.0     402375.0  

6. Định dạng ô

Cho đến nay chúng ta đã thấy các ví dụ về đọc/ghi và các tệp excel bằng cách sử dụng Apache POI. Tuy nhiên, khi tạo báo cáo trong tệp excel, điều cần thiết là thêm định dạng trên các ô phù hợp với bất kỳ tiêu chí được xác định trước nào

Định dạng này có thể có màu khác dựa trên phạm vi giá trị cụ thể, giới hạn ngày hết hạn, v.v.

Trong các ví dụ dưới đây, chúng tôi đang lấy một số ví dụ về định dạng ô như vậy cho các mục đích khác nhau

6. 1. Giá trị ô trong một phạm vi cụ thể

Mã này sẽ tô màu bất kỳ ô nào trong phạm vi có giá trị nằm giữa phạm vi đã định cấu hình. [e. g. , giữa 50 và 70]

6. 2. Làm nổi bật các giá trị trùng lặp

Đánh dấu tất cả các ô có giá trị trùng lặp trong các ô được quan sát

static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);
 
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
 
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
 
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A2:A11")
    };
 
    sheetCF.addConditionalFormatting(regions, rule1);
 
    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  " +
            "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

6. 3. Các hàng màu thay thế với các màu khác nhau

Một mã đơn giản để tô màu từng hàng thay thế bằng một màu khác

static void shadeAlt(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
 
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
 
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A1:Z100")
    };
 
    sheetCF.addConditionalFormatting(regions, rule1);
 
    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
}

6. 4. Số lượng màu sắp hết hạn trong 30 ngày tới

Một mã tiện dụng cho các dự án tài chính theo dõi thời hạn

package com.howtodoinjava.demo.poi;
//import statements
public class WriteExcelDemo 
{
    public static void main(String[] args) 
    {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook(); 
         
        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");
          
        //This data needs to be written (Object[])
        Map data = new TreeMap();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});
          
        //Iterate over data and write to sheet
        Set keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
               Cell cell = row.createCell(cellnum++);
               if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

Tôi kết thúc hướng dẫn poi apache này tại đây để giữ cho bài đăng trong giới hạn

7. Phần kết luận

Trong hướng dẫn này, chúng ta đã học cách đọc excel, viết excel, đặt và đánh giá các ô công thức cũng như định dạng các ô bằng mã màu bằng thư viện Apache POI

Làm cách nào để tạo trang tính Excel bằng Apache POI?

Làm cách nào để ghi vào tệp Excel hiện có trong Java bằng POI?

Làm cách nào để tạo tệp XLSX mới trong Java?

Làm cách nào để tạo một tệp Excel động trong Java?