apache poi 读写xlsx文件简单示例

北风留影

package com.javaexcel;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Sample {
public static void main(String[] args) throws IOException {
//testRead();
//testWrite();
testChange();
}
/**
* 删除一个文件
*/
public static void delete(){
File file = new File("f:/log/poi_wirte.xlsx");
System.out.println(file.delete());
}
public static void testWrite(){
Workbook workbook = null;//一个excel文件
Sheet sheet = null;//excel一个sheet
workbook = new XSSFWorkbook();
sheet = workbook.createSheet("xssf sheet");
Row row1 = sheet.createRow(3);
Cell cell = row1.createCell(2);
cell.setCellValue("new xssf cell");
try {
workbook.write(new FileOutputStream(new File("f:/log/poi_wirte.xlsx")));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 没有发现可以修改excel文件的方法，直接覆盖后文件被损坏。只能建新文件，删除源文件，然后重命名。
* 删除源文件是需要关闭输入输出流，需要用OPCPackage的close方法。
*/
public static void testChange(){
OPCPackage opc = null;
Workbook workbook = null;
Sheet sheet = null;
try {
opc = OPCPackage.open("f:/log/poi_change.xlsx");
workbook = new XSSFWorkbook(opc);
//File f = new File("f:/log/poi_change.xlsx");
sheet = workbook.getSheetAt(0);
Row row = sheet.createRow(1);
row.createCell(0).setCellValue("a2");
row.createCell(1).setCellValue("b2");
row.createCell(2).setCellValue("c2");
workbook.write(new FileOutputStream("f:/log/poi_change2.xlsx"));
opc.close();
System.out.println(new File("f:/log/poi_change.xlsx").delete());
new File("f:/log/poi_change2.xlsx").renameTo(new File("f:/log/poi_change3.xlsx"));
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void testRead(){
Workbook workBook = null;//一个excel文件
Sheet sheet = null;//excel一个sheet
try {
workBook = WorkbookFactory.create(new File("f:/log/poi_read.xlsx"));
sheet = workBook.getSheetAt(0);
for(Row row:sheet){
System.out.print(row.getRowNum()+":");
for(Cell c:row){
System.out.print("("+c.getColumnIndex()+":"+c+")");
}
System.out.print("\n");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
}