Apache POI 之 用Java读写Excel文件

通过示例使用Apache POI库,学习阅读excel,编写excel,评估公式单元格以及将自定义格式应用于生成的excel文件。

如果要为人力资源或财务领域构建软件,通常需要生成通常跨管理级别的excel报告。除了报告之外,您还可以期望应用程序的输入数据以excel表格的形式出现,并且期望应用程序能够支持它。

在许多其他开放源代码库中, Apache POI是值得信赖的库,可以处理涉及excel文件的此类用例。使用POI,您可以使用Java读写MS Excel文件。

请注意,此外,您还可以使用POI库读取和写入MS Word和MS PowerPoint文件。

在本Apache POI教程中,我们将讨论现实生活中的应用程序所需的一些常见excel操作。

目录:

Apache POI依赖项
POI类
编写Excel文件
读取Excel文件
使用Excel工作表中的公式
格式化单元格
源代码下载

Apache POI –依赖关系

如果您正在从事Maven项目,则可以pom.xml使用以下命令将POI依赖项包含在文件中:

pom.xml
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

如果您不使用maven,则可以从POI下载页面下载maven jar文件。包括以下jar文件,以运行本教程中给出的示例代码。

  • dom4j-1.6.1.jar
  • poi-3.9-20121203.jar
  • poi-ooxml-3.9-20121203.jar
  • poi-ooxml-schemas-3.9-20121203.jar
  • xmlbeans-2.3.0.jar

Apache POI –类

  1. HSSF,XSSF和XSSF类

    Apache POI主类通常以HSSFXSSFSXSSF开头

    • HSSF –是POI项目对Excel ’97(-2007)文件格式的纯Java实现。例如HSSFWorkbookHSSFSheet
    • XSSF –是POI项目对Excel 2007 OOXML(.xlsx)文件格式的纯Java实现。例如XSSFWorkbookXSSFSheet
    • SXSSF(自3.8-beta3起)–是XSSF的API兼容 Stream 扩展,可用于必须生成非常大的电子表格且堆空间有限的情况。例如SXSSFWorkbookSXSSFSheet。SXSSF 通过限制对滑动窗口内的行的访问来实现其低内存占用,而XSSF允许对文档中的所有行进行访问。
  2. 行和单元格

    除上述类外,单元格还用于与Excel工作表中的特定行和特定单元格进行交互。

  3. 样式类

    当您必须在工作表中添加格式时(主要基于某些规则),将使用诸如CellStyleBuiltinFormatsComparisonOperatorConditionalFormattingRuleFontFormattingIndexedColorsPatternFormattingSheetConditionalFormatting等广泛的类。

  4. 公式评估器

    另一个有用的类FormulaEvaluator用于评估excel工作表中的公式单元格。

Apache POI –编写一个Excel文件

我首先以该示例为例,以便我们可以重用此代码创建的excel工作表以在下一个示例中回读。

使用POI编写excel非常简单,涉及以下步骤:

  1. 创建工作簿
  2. 在工作簿中创建工作表
  3. 在工作表中创建一行
  4. 在工作表中添加单元格
  5. 重复步骤3和4以写入更多数据

看起来很简单,对吧?让我们看看执行这些步骤的代码。

使用apache POI库编写Excel文件的Java程序。

package com.how2codex.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<String, Object[]> data = new TreeMap<String, Object[]>();
        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<String> 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();
        }
    }
}

poi-demo-write-file

Apache POI –读取Excel文件

如果我们将其分为几步,则使用POI读取excel文件也非常简单。

  1. 从Excel工作表创建工作簿实例
  2. 到达所需的工作表
  3. 行数递增
  4. 遍历所有单元格
  5. 重复步骤3和4,直到读取了所有数据

让我们在代码中查看以上所有步骤。我正在编写代码以读取在以上示例中创建的excel文件。它将逐单元读取所有列名称和其中的值。

使用apache POI库读取Excel文件的Java程序。

package com.how2codex.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<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext())
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> 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();
        }
    }
}
Output:
ID      NAME        LASTNAME
1.0     Amit        Shukla 
2.0     Lokesh      Gupta  
3.0     John        Adwards
4.0     Brian       Schultz

Apache POI –添加和评估公式单元格

在处理复杂的Excel工作表时,我们会遇到许多具有公式以计算其值的单元格。这些是公式单元格。Apache POI对添加公式单元格和评估已经存在的公式单元格提供了出色的支持。

看看如何在excel中添加公式单元格的一个示例吗?

在此代码中,一行中有四个单元格,而所有前三行相乘则是第四个单元格。因此公式将为:(A2*B2*C2第二行)

使用apache POI库在Excel文件中添加公式的Java程序。

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();
    }
}

同样,我想读取一个包含公式单元格的文件,请使用以下逻辑来评估公式单元格

Java程序使用apache POI库在excel文件中评估公式。

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<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> 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();
    }
}
Output:
Pricipal        RoI         T       Interest (P r t)       
14500.0         9.25        3.0     402375.0

poi-demo-write-公式

Apache POI –格式化单元

因此,我们已经看到了使用apache POI进行读取/写入和excel文件的示例。但是,当我们在excel文件中创建报告时,在适合任何确定标准的单元格上添加格式变得至关重要。该格式可以是基于特定值范围,基于有效期限制等的不同颜色。

在下面的示例中,我将出于各种目的使用几个此类单元格格式化示例。

1)单元格值在一定范围内

这段代码将为值在配置范围内的范围内的任何单元格着色。[例如50至70]

static void basedOnValue(Sheet sheet)
{
    //Creating some random values
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(74);
    sheet.createRow(2).createCell(0).setCellValue(50);
    sheet.createRow(3).createCell(0).setCellValue(51);
    sheet.createRow(4).createCell(0).setCellValue(49);
    sheet.createRow(5).createCell(0).setCellValue(41);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    //Condition 1: Cell Value Is   greater than  70   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    //Condition 2: Cell Value Is  less than      50   (Green Fill)
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
    PatternFormatting fill2 = rule2.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A1:A6")
    };
    sheetCF.addConditionalFormatting(regions, rule1, rule2);
}

poi-demo-formatting-1

2)突出显示重复的值

突出显示在观察到的单元格中具有重复值的所有单元格。

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(falsetrue);
    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)");
}

poi-demo-formatting-2

3)用不同的颜色替换行

一个简单的代码,以不同的颜色为每个备用行着色。

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)");
}

poi-demo-formatting-3

4)颜色数量将在接下来的30天内到期

这对于跟踪截止日期的金融项目非常有用的代码。

static void expiryInNext30Days(Sheet sheet)
{
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));
    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");
    for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(falsetrue);
    font.setFontColorIndex(IndexedColors.BLUE.index);
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A2:A4")
    };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

poi-demo-formatting-4

我将在此处结束此apache poi教程,以限制帖子的数量。

源代码下载

单击下面的给定链接以下载以上示例的源代码。

在本教程中,我们学习了读取excel编写excel设置和评估公式单元格,以及使用APache POI库使用颜色编码设置单元格的格式

参考文献:

Apache POI入门指南
Apache POI API文档

saigon has written 1445 articles

Leave a Reply