读/写相同的使用Apache POI excel文件 [英] Read/write to same excel file using Apache POI

问题描述

我想读一个Excel和写入同一个Excel根据一定的条件。
例如：

i want to read an excel and write to the same excel based on some conditions. for example

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

现在我requrient是

Now my requrient is

1）要根据列名说'名称'读取数据和列下得到的所有数据

1) want to read the data based on column name say 'Name' and get all the data under that column

2）的基础上，列名以同样的方式说'名称'我想添加数据的一个多行
   即数据应以下面的方式增加新的行相同excel文件之后

2) in the same way based on the column name say 'Name' i want to add one more row of data ie data should be in the following way after adding new row to same excel file

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

如何使用Apache POI做到这一点。

how to do this using Apache POI.

有谁能够给我提供一个例子。我想读/写的同一个Excel DONOT要创建一个新的Excel。

can anybody provide me an example . i want to read/read to the same excel donot want to create a new excel.

先谢谢了。

推荐答案

您可以参考我下面的示例程序，它可以帮助你。

You can refer below my sample program it may help you.

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

输出会像

祝你好运...！！！ ：）

The output will be like Good Luck...!!! :)

这篇关于读/写相同的使用Apache POI excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！