使用Apache POI读/写到同一个excel文件 [英] Read/write to same excel file using Apache POI
本文介绍了使用Apache POI读/写到同一个excel文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想根据一些条件读取excel并写入相同的excel。
例如
Empno名称工资
1 jonh 2000
2 Sam 3000
3 Dean 7000
现在我的求职者是
1）想要根据列名称Name读取数据，并以相同的方式基于列获取所有数据在该列
2）名称说'名字'我想添加一行数据
即将数据添加到同一个excel文件后，数据应该以下列方式
Empno姓名工资
1 jonh 2000
2 Sam 3000
3 Dean 7000
4 Smith 8000
如何使用Apache POI。
可以为我提供一个例子。我想读/读同样的excel不想创建一个新的excel。
提前感谢。
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;
/ **
* Java中使用Apache POI读取和写入Excel文件的示例Java程序
*
* /
public class FinalFormattingExcelWriting {
@SuppressWarnings（resource）
public static void main（String [] args）throws IOException，
InvalidFormatException {
FinalFormattingExcelWriting exl = new FinalFormattingExcelWriting（）;
//将学生信息添加到表
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（学生，4，学生4，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（学生，9，学生9，yz，45，78，49，77，59）;
exl.print（学生，10，学生10，abc1，77，65，77，95，46）;
System.out.println（Task Completed ........................）;
}
/ *
*现有表格和新表格添加流程
* /
@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工作簿= null;
文件文件=新文件（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 []>（）;
//验证文件是否存在
if（file.exists（））{
FileInputStream inputStream = new FileInputStream（file）;
workbook = new XSSFWorkbook（inputStream）;
//验证工作表是否可用
if（workbook.getSheet（excelSheetName）！= null）{
excelSheet = workbook.getSheet（excelSheetName）;
} else {
excelSheet = workbook.createSheet（excelSheetName）;
//新表格中的标题风格细节
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 = 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）;
}
}
//在excel表中添加作者名称
POIXMLProperties xmlProps = workbook.getProperties（）;
POIXMLProperties.CoreProperties coreProps = xmlProps
.getCoreProperties（）;
coreProps.setCreator（Saminathan）;
//这个数据需要写（Object []）
//数据计算
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）;
尝试{
//在文件系统中写入工作簿
out = new FileOutputStream（file）;
workbook.write（out）;
workbook.close（）;
out.close（）;
} catch（Exception e）{
e.printStackTrace（）;
}
}
//将值插入到单元格
中public void addIntoCell（Map< String，Object []> data，XSSFSheet sheet，
String excelID，CellStyle style）{
Set< String> keyset = data.keySet（）;
单元格= 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）;
}
//仅为标题
应用样式if（Integer.parseInt（excelID）< 1）{
cell.setCellStyle（style）;
}
}
}
}
}
输出将如
祝你好运！ ：）
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
Now my requrient is
1) want to read the data based on column name say 'Name' and get all the data under that column
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
how to do this using Apache POI.
can anybody provide me an example . i want to read/read to the same excel donot want to create a new excel.
thanks in advance.
解决方案
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屋！
查看全文
