Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
其实就是可以当做接口来用的;
1. 首先是要进行创建文件
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
public class Work {
public static void main(String[] args) {
// create a new workbook
Workbook workbook = new HSSFWorkbook();
try {
FileOutputStream fileOutputStream = new FileOutputStream("F:\\workbook.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
- 有的时候如果是一个文件多了的话是要进行sheet分页的
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
public class Sheet {
public static void main(String[] args) {
// create a new workbook
Workbook workbook = new HSSFWorkbook();
workbook.createSheet("first sheet");
workbook.createSheet("second sheet");
try {
FileOutputStream fileOutputStream = new FileOutputStream("F:\\workbook1.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
- 时间格式的单元格
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
* 时间格式的单元格
*/
public class DateSheet {
public static void main(String[] args) throws Exception {
Workbook workbook=new HSSFWorkbook();
Sheet sheet=workbook.createSheet("first Sheet");
Row row=sheet.createRow(0);
Cell cell=row.createCell(0);
cell.setCellValue(new Date());
CreationHelper createHelper=workbook.getCreationHelper();
CellStyle cellStyle=workbook.createCellStyle(); //单元格样式类
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy-mm-dd hh:mm:ss"));
cell=row.createCell(1); // 第二列
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
cell=row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
FileOutputStream fileOut=new FileOutputStream("F:\\workbook1.xls");
workbook.write(fileOut);
fileOut.close();
}
}
- 不同内容格式的单元格
import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
/**
* 不同内容格式的单元格
*/
public class DiffSheet {
public static void main(String[] args) throws Exception{
Workbook wb=new HSSFWorkbook();
Sheet sheet=wb.createSheet(" fiest Sheet");
Row row=sheet.createRow(0); // 创建一个行
Cell cell=row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(new Date().toString());
row.createCell(1).setCellValue(1);
row.createCell(2).setCellValue("str");
row.createCell(3).setCellValue(true);
row.createCell(4).setCellValue(HSSFCell.CELL_TYPE_NUMERIC);
row.createCell(5).setCellValue(false);
FileOutputStream fileOut=new FileOutputStream("F:\\workbook.xls");
wb.write(fileOut);
fileOut.close();
}
}
- 设置值
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
public class CellDemo {
public static void main(String[] args) throws Exception{
Workbook workbook = new HSSFWorkbook();
Sheet sheet=workbook.createSheet("first sheet");
// create row
Row row=sheet.createRow(0);
Cell cell=row.createCell(0); // 创建一个单元格 第1列
cell.setCellValue(1); // 给单元格设置值
row.createCell(1).setCellValue(666); // 第2列 值是666
row.createCell(2).setCellValue("str"); // 第3列 值为一个str
row.createCell(3).setCellValue(false); // 第4列 值为布尔类型
FileOutputStream fileOut=new FileOutputStream("F:\\workbook1.xls");
workbook.write(fileOut);
fileOut.close();
}
}
- 文本提取
import java.io.FileInputStream;
import java.io.InputStream;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* 文本提取
*/
public class FileSheet {
public static void main(String[] args) throws Exception{
InputStream is=new FileInputStream("f:\\11.xls");
POIFSFileSystem fs=new POIFSFileSystem(is);
HSSFWorkbook wb=new HSSFWorkbook(fs);
ExcelExtractor excelExtractor=new ExcelExtractor(wb);
excelExtractor.setIncludeSheetNames(false);// 我们不需要Sheet页的名字
System.out.println(excelExtractor.getText());
}
}