使用Apache POI读/写相同的excel文件

Question

我想阅读一个excel，并根据一些条件写到相同的excel。例如

     Empno  Name Salary
       1     jonh 2000
       2     Sam  3000
       3     Dean 7000

现在我的要求是

1）要根据列名读取数据，例如“名称”并获取该列下的所有数据

2)以同样的方式基于列名说出' name '我想再添加一行数据ie数据应该以下面的方式在相同的excel文件中添加新的一行

       Empno  Name Salary
       1     jonh  2000
       2     Sam   3000
       3     Dean  7000
       4     Smith  8000

如何使用Apache POI执行此操作。

有人能给我举个例子吗？我想读/读同一个excel，不想创建一个新的excel。

提前感谢。

Answer 1

您可以在下面参考我的示例程序，它可能会对您有所帮助。

package com.sam.test.excel;
/**
 * @author Saminathan
 *
 */
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.POIXMLProperties;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Sample Java program to read and write Excel file in Java using Apache POI
 * 
 */
public class FinalFormattingExcelWriting {
@SuppressWarnings("resource")
public static void main(String[] args) throws IOException,
        InvalidFormatException {
    FinalFormattingExcelWriting exl = new FinalFormattingExcelWriting();
    // adding student info into sheet
    exl.print("Student", "1", "Student1", "abc", 50, 78, 56, 55, 46);
    exl.print("Student", "2", "Student2", "def", 60, 54, 89, 66, 79);
    exl.print("Student", "3", "Student3", "ghi", 7, 96, 75, 88, 19);
    exl.print("Student", "4", "Student4", "jkl", 89, 15, 35, 44, 78);
    exl.print("Student", "5", "Student5", "mno", 40, 49, 46, 77, 46);
    exl.print("Student", "6", "Student6", "pqr", 56, 45, 57, 44, 44);
    exl.print("Student", "7", "Student7", "stu", 78, 61, 99, 66, 88);
    exl.print("Student", "8", "Student8", "vwx", 91, 39, 40, 22, 38);
    exl.print("Student", "9", "Student9", "yz", 45, 78, 49, 77, 59);
    exl.print("Student", "10", "Student10", "abc1", 77, 65, 77, 95, 46);
    System.out.println("Task Completed........................");
}

/*
 * Existing sheet and new sheet adding process
 */
@SuppressWarnings("resource")
public void print(String excelSheetName, String dataForRollNo,
        String dataForName, String dataForDept, int dataForSubj1,
        int dataForSubj2, int dataForSubj3, int dataForSubj4,
        int dataForSubj5) throws IOException {
    XSSFWorkbook workbook = null;
    File file = new File("D:/workspace/ex/examples/src/Student_Sheet.xlsx");
    FileOutputStream out = null;
    XSSFSheet excelSheet = null;
    CellStyle style = null;
    XSSFFont font = null;
    Map<String, Object[]> excelData = new TreeMap<String, Object[]>();
    // verifying file is present or not
    if (file.exists()) {
        FileInputStream inputStream = new FileInputStream(file);
        workbook = new XSSFWorkbook(inputStream);
        // verifying the sheet is available or not
        if (workbook.getSheet(excelSheetName) != null) {
            excelSheet = workbook.getSheet(excelSheetName);
        } else {
            excelSheet = workbook.createSheet(excelSheetName);
            // Style details for heading in new sheet
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.GREEN.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setColor(HSSFColor.WHITE.index);
            style.setFont(font);
            excelData.put("0", new Object[] { "RollNo", "Name", "Dept",
                    "Subj1", "Subj2", "Subj3", "Subj4", "Subj5", "Total",
                    "Avg", "Result" });
            addIntoCell(excelData, excelSheet, "0", style);
        }
    } else {
        workbook = new XSSFWorkbook();
        if (workbook.getSheet(excelSheetName) != null) {
            excelSheet = workbook.getSheet(excelSheetName);
        } else {
            excelSheet = workbook.createSheet(excelSheetName);
            // Style details for heading in new sheet
            style = workbook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.GREEN.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            font = workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setColor(HSSFColor.WHITE.index);
            style.setFont(font);
            excelData.put("0", new Object[] { "RollNo", "Name", "Dept",
                    "Subj1", "Subj2", "Subj3", "Subj4", "Subj5", "Total",
                    "Avg", "Pass/Fail" });
            addIntoCell(excelData, excelSheet, "0", style);
        }
    }
    // adding author name in excel sheet
    POIXMLProperties xmlProps = workbook.getProperties();
    POIXMLProperties.CoreProperties coreProps = xmlProps
            .getCoreProperties();
    coreProps.setCreator("Saminathan");
    // This data needs to be written (Object[])
    // Data calculation
    int dataForTotal = dataForSubj1 + dataForSubj2 + dataForSubj3
            + dataForSubj4 + dataForSubj5;
    double dataForAvg = dataForTotal / 5;
    String dataForResult = "Fail";
    if (dataForAvg > 55) {
        dataForResult = "Pass";
    }
    excelData.put(dataForRollNo,
            new Object[] { dataForRollNo, dataForName, dataForDept,
                    dataForSubj1, dataForSubj2, dataForSubj3, dataForSubj4,
                    dataForSubj5, dataForTotal,
                    Double.toString(dataForAvg), dataForResult });
    addIntoCell(excelData, excelSheet, dataForRollNo, style);
    try {
        // Write the workbook in file system
        out = new FileOutputStream(file);
        workbook.write(out);
        workbook.close();
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

// Inserting values into cell
public void addIntoCell(Map<String, Object[]> data, XSSFSheet sheet,
        String excelID, CellStyle style) {
    Set<String> keyset = data.keySet();
    Cell cell = null;
    Row row = null;
    Object[] objArr = null;
    int rownum = 0;
    if (Integer.parseInt(excelID) >= 1) {
        rownum = Integer.parseInt(excelID);
    }
    for (String key : keyset) {
        row = sheet.createRow(rownum);
        objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            cell = row.createCell(cellnum++);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Integer) {
                cell.setCellValue((Integer) obj);
            }
            // applying style only for heading
            if (Integer.parseInt(excelID) < 1) {
                cell.setCellStyle(style);
                }
            }
        }
    }
}