由于公司业务要求使用PIO进行Excel的操作，今日份将学习笔记与代码上传CSDN，与诸君共赏，如有不足，请随时指正
本篇文章涉及点：
目录
二、POI Workbook接口和HSSFWorkbook对象和XSSFWorkbook对象
一、POI Workbook接口需要的Jar包
如果只使用HSSFWorkbook，那么就可以只导入一个pio包就行，但是如果需要使用XSSFWorkbook包，就要导入两个包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>PIO包中东西很多，具体见表：
| Maven artifactId | Prerequisites | JAR |
|---|---|---|
| poi | commons-logging, commons-codec, log4j | poi-version-yyyymmdd.jar |
| poi-scratchpad | poi | poi-scratchpad-version-yyyymmdd.jar |
| poi-ooxml | poi, poi-ooxml-schemas | poi-ooxml-version-yyyymmdd.jar |
| poi-ooxml-schemas | xmlbeans | poi-ooxml-schemas-version-yyyymmdd.jar |
| poi-examples | poi, poi-scratchpad, poi-ooxml | poi-examples-version-yyyymmdd.jar |
| ooxml-schemas | xmlbeans | ooxml-schemas-1.1.jar |
当我们只要使用xls格式时、只要导入poi-version-yyyymmdd.jar就可以了。
当我们还要使用xlsx格式、还要导入poi-ooxml-version-yyyymmdd.jar。
至于poi-ooxml-schemas-version-yyyymmdd.jar这个jar基本不太会用到的。
当我们需要操作word、ppt、viso、outlook等时需要用到poi-scratchpad-version-yyyymmdd.jar
二、POI Workbook接口和HSSFWorkbook对象和XSSFWorkbook对象
HSSFWorkbook只能操作excel2003一下版本
XSSFWorkbook只能操作excel2007以上版本
两者在使用原理中没有特别大的区别，在具体的业务中，为了兼容两个，可以在Java中可以利用Workbook接口，创建对应的对象操作excel来处理兼容性。
具体代码见：
@Test
public void HSSFTest() throws Exception{
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream( new File("D://students.xls")));
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row =sheet.getRow(0);
HSSFCell cell= row.getCell(0);
}
@Test
public void XSSFTest() throws Exception{
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream( new File("D://students.xls")));
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row =sheet.getRow(0);
XSSFCell cell= row.getCell(0);
}
@Test //利用Workbook接口和判断excel版本创建相应版本HSSFWorkbook/XSSFWorkbook对象
public void test() throws Exception{
String file = "D://students.xls";
boolean isExcel2003 = file.toLowerCase().endsWith("xls")?true:false;
Workbook workbook = null;
if(isExcel2003){
workbook = new HSSFWorkbook(new FileInputStream(new File(file)));
}else{
workbook = new XSSFWorkbook(new FileInputStream(new File(file)));
}
//后面与正常做法相同
Sheet sheet = workbook.getSheetAt(0);
Row row =sheet.getRow(0);
Cell cell= row.getCell(0);
}三、XSSFWorkbook对象的增删改查
由于本次业务是在Excel2007环境下进行的，所以本次练习主要以XSSF为主，HSSF原理相同，可以类推。
（一）创建一个sheet
public static void CreateExcel(String file,String sheetName) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName);
int last = sheet.getLastRowNum();
XSSFRow row = sheet.createRow(last);
row.createCell(0).setCellValue("姓名");
row.createCell(1).setCellValue("年龄");
row.createCell(2).setCellValue("性别");
// XSSFRow row1 = sheet.createRow(last + 1);
// row1.createCell(0).setCellValue("李静静");
// row1.createCell(1).setCellValue("8");
// row1.createCell(2).setCellValue("女孩子");
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}我这里设计，用户直接指定要创建的文件名以及sheet名称；
如果为一个新表，sheet.getLastRowNum是没有必要出现的，肯定是0，这个方法主要是获取现在表格中是否含有数据，新建立的表行是在现有数据后面连接的；
在创建好表头之后，可以直接写入数据进行测试，这里面有很多种类型可以设置，可以说很人性化了。
注意这些代码行：
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);之前的代码已经将XSSFWorkBook内容编辑好了，但是如果没有IO流的写入，也没有办法直接将数据写入本地，
那么首先借助FileOutputStream字节流，有些人肯定就有疑问了，那字符流可以操作这个吗？
咱们要使用XSSFWorkbook的write写方法，就要遵守人家的规则，他们需要的是一个字节输入流。
到这里，就已经完全创建了一个sheet名指定好的Excel了
具体效果如下：
（二）sheet中插入数据
依旧在上面表格中继续插入数据
//增加新表数据
public static void insertExcel(String file, String sheetName) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
int last = sheet.getLastRowNum();
for (int i = 1; i < 6; i++) {
XSSFRow row1 = sheet.createRow(last +i);
row1.createCell(0).setCellValue("静静");
row1.createCell(1).setCellValue("18");
row1.createCell(2).setCellValue("女孩子");
}
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}这里使用工厂模式构建了一个Workbook对象，利用字节流进行指定文件的读出
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();记得关闭流，避免资源浪费。
具体效果如下：
（三）指定单元格的修改
//修改指定位置单元格数据
public static void updateSheet(String file,String sheetName,int row,int cell,String value) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
sheet.getRow(row).getCell(cell).setCellValue(value);
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}哈哈，代码很简单，
参数介绍：
- file:指定修改的文件名
- sheetName：要保证文件里有这个sheet名称，不然会报错
- row | cell ;用来唯一确定一个单元格
- value： 要修改的新数据
最有意思的一句是：
sheet.getRow(row).getCell(cell).setCellValue(value);先获取行，在行中找列，之后直接set新值。
具体效果如下：
这个单元格数据改变了，因为我传递参数是将女孩子变成男孩子。
（四）删除某行
//删除某行数据
public static void deleteRow(XSSFSheet sheet, int startRow,int rowIndex) {
int lastRowNum = sheet.getLastRowNum();
if(startRow <lastRowNum){
//上移
sheet.shiftRows(startRow, lastRowNum, -rowIndex,true,false);
//下移
sheet.shiftRows(startRow, lastRowNum, rowIndex,true,false);
}
}参数介绍 :
- startRow: 开始行
- rowIndex：一共删除的行数
- n:要移动的行数
- copyRowHeight：是否在换班时复制行高
- resetOriginalRowHeight：是否将原始行的高度设置为默认值
主要作用： 同过将选中的行上移（n 为负数）或下移（n 为 正数） 来覆盖原来的行来实现删行操作
有心会发现，如果没有写入的字节流操作，本地的数据是不会变化的，我在写程序的时候，忘记写这个了，还将重心放在后面两个boolean参数上了，
完成的代码应该是：
//删除某行数据
public static void deleteRow(String file,String sheetName, int startRow,int rowIndex) throws IOException, InvalidFormatException{
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
System.out.println(sheet.getSheetName());
int lastRowNum = sheet.getLastRowNum();
System.out.println(lastRowNum);
if(startRow <lastRowNum){
//上移
sheet.shiftRows(startRow, lastRowNum, -rowIndex,true,false);
//下移
// sheet.shiftRows(startRow, lastRowNum, rowIndex,true,false);
}
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}是的，看起来前面和后面是一摸一样的，所以如果在真实业务中，所有的操作都有，前面和后面的代码块可以提炼出来。
展示效果如下：
切记，这里面的startRow开始标志，是整个表格的开始，所以如果有表头的话，可以看到删除的数据是：静静1。
（五）删除某列
//删除某列数据
public static void deleteCell(String file,String sheetName, int startCell) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
int lastCellNum = sheet.getRow(0).getLastCellNum();
for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext(); ) {
XSSFRow row = (XSSFRow) rowIterator.next();
XSSFCell cell = row.getCell(startCell);
if (cell == null) {
continue;
}
row.removeCell(cell);
}
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}这里面使用了迭代器，因为数据在表格中是以行进行展示的，所以执行原理就是遍历每一行，讲指定数据列删除。
这个方法在数据量很大的情况下实现会比较慢，所以如果有什么更好的想法，欢迎留言交流，给我一个好好学习的机会。
展示效果如下：
（六）查找数据：
//获取文件数据
public static void getExcelAsFile(String file) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
//3.得到Excel工作表对象
Sheet sheet = wb.getSheetAt(0);
//总行数
int trLength = sheet.getLastRowNum();
//4.得到Excel工作表的行
Row row = sheet.getRow(0);
//总列数
int tdLength = row.getLastCellNum();
//5.得到Excel工作表指定行的单元格
Cell cell = row.getCell((short) 1);
//6.得到单元格样式
CellStyle cellStyle = cell.getCellStyle();
//下面可以设置样式
for (int i = 0; i < trLength; i++) {
//得到Excel工作表的行
Row row1 = sheet.getRow(i);
for (int j = 0; j < tdLength; j++) {
//得到Excel工作表指定行的单元格
Cell cell1 = row1.getCell(j);
//获得每一列中的值
System.out.print(cell1 + " ");
}
System.out.println();
}
//将修改样式后的数据保存
OutputStream out = new FileOutputStream(file);
wb.write(out);
}我是直接在打印台输出的：
如果有需求。可以使用OutputStream重新写在另一个文件里面也行，反正数据已经拿到了，怎么处理，还不是一句话的事情嘛
四、测试类展示
别忘记咱们还有的异常时抛出来的，还没解决呢，自己的事情自己做，来吧
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import java.io.IOException;
public class Text {
public static void main(String[] args) {
try {
// PIOText.CreateExcel("D:\\students.xlsx","表格一");
// PIOText.insertExcel("D:\\students.xlsx", "表格一");
// PIOText.updateSheet("D:\\students.xlsx","表格一",2,2,"男孩子");
// PIOText.deleteRow("D:\\students.xlsx","表格一",2,1);
// PIOText.deleteCell("D:\\students.xlsx","表格一",2);
PIOText.getExcelAsFile("D:\\students.xlsx");
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
}想进行那个操作就将那个操作注释放开。
五、码汉全席
拿来即用。
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.util.Iterator;
public class PIOText {
//获取文件数据
public static void getExcelAsFile(String file) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
//3.得到Excel工作表对象
Sheet sheet = wb.getSheetAt(0);
//总行数
int trLength = sheet.getLastRowNum();
//4.得到Excel工作表的行
Row row = sheet.getRow(0);
//总列数
int tdLength = row.getLastCellNum();
//5.得到Excel工作表指定行的单元格
Cell cell = row.getCell((short) 1);
//6.得到单元格样式
CellStyle cellStyle = cell.getCellStyle();
//下面可以设置样式
for (int i = 0; i < trLength; i++) {
//得到Excel工作表的行
Row row1 = sheet.getRow(i);
for (int j = 0; j < tdLength; j++) {
//得到Excel工作表指定行的单元格
Cell cell1 = row1.getCell(j);
System.out.print(cell1 + " ");
}
System.out.println();
}
//将修改样式后的数据保存
OutputStream out = new FileOutputStream(file);
wb.write(out);
}
//创建一个新表
public static void CreateExcel(String file,String sheetName) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName);
int last = sheet.getLastRowNum();
XSSFRow row = sheet.createRow(last);
row.createCell(0).setCellValue("姓名");
row.createCell(1).setCellValue("年龄");
row.createCell(2).setCellValue("性别");
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//增加新表数据
public static void insertExcel(String file, String sheetName) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
int last = sheet.getLastRowNum();
for (int i = 1; i < 6; i++) {
XSSFRow row1 = sheet.createRow(last +i);
row1.createCell(0).setCellValue("静静");
row1.createCell(1).setCellValue("18");
row1.createCell(2).setCellValue("女孩子");
}
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//修改指定位置单元格数据
public static void updateSheet(String file,String sheetName,int row,int cell,String value) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
sheet.getRow(row).getCell(cell).setCellValue(value);
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//删除某行数据
public static void deleteRow(String file,String sheetName, int startRow,int rowIndex) throws IOException, InvalidFormatException{
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
System.out.println(sheet.getSheetName());
int lastRowNum = sheet.getLastRowNum();
System.out.println(lastRowNum);
if(startRow <lastRowNum){
//上移
sheet.shiftRows(startRow, lastRowNum, -rowIndex,true,false);
//下移
// sheet.shiftRows(startRow, lastRowNum, rowIndex,true,false);
}
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//删除某列数据
public static void deleteCell(String file,String sheetName, int startCell) throws IOException, InvalidFormatException {
InputStream ins = new FileInputStream(new File(file));
Workbook wb = WorkbookFactory.create(ins);
ins.close();
XSSFSheet sheet = (XSSFSheet) wb.getSheet(sheetName);
int lastCellNum = sheet.getRow(0).getLastCellNum();
for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext(); ) {
XSSFRow row = (XSSFRow) rowIterator.next();
XSSFCell cell = row.getCell(startCell);
if (cell == null) {
continue;
}
row.removeCell(cell);
}
try {
FileOutputStream fout = new FileOutputStream(file);
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
THE END
GOOD LUCK
扫一扫
2085
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
