Poi简介
1、maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2、Poi包结构
1、HSSF:读写07年之前的Excel版本
2、XSSF:读写07年之后的Excel版本
3、HWPF:读写Word Doc文档
4、HSLF:读写PowerPoint
3、Poi与Jxl的对比
Jxl:功耗小,图片、图形支持有限
Poi:功能更加完善
使用Poi
1、从Excel中读取数据
//获取工作簿
XSSFWorkbook workbook = new XSSFWorkbook("D:\\test.xlsx");
//获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//增强for循环
//遍历表格的行
for (Row row : sheet) {
//遍历行中的单元格
for (Cell cell : row) {
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
}
}
//使用普通for循环
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
short lastCellNum = row.getLastCellNum();
for (int i1 = 0; i1 <= lastCellNum; i1++) {
XSSFCell cell = row.getCell(i1);
if (cell != null) {
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
}
}
}
}
workbook.close();
2、向Excel中写入数据
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = workbook.createSheet("工作簿一");
//创建行
XSSFRow row = sheet.createRow(0);
//创建单元格
row.createCell(0).setCellValue("开科");
row.createCell(1).setCellValue("java");
row.createCell(2).setCellValue("中科");
XSSFRow row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("开科");
row1.createCell(1).setCellValue("java");
row1.createCell(2).setCellValue("中科");
//创建输出流
FileOutputStream outputStream = new FileOutputStream("D:\\java.xlsx");
workbook.write(outputStream);
//关闭资源
outputStream.flush();
outputStream.close();
workbook.close();
3、设置格式
//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = workbook.createSheet("工作簿一");
//创建行
XSSFRow row = sheet.createRow(0);
//获取单元格格式对象
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.PINK.getIndex());
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//获取字体对象
XSSFFont font = workbook.createFont();
font.setColor(IndexedColors.YELLOW.getIndex());
//将字体设置到单元格格式对象中
cellStyle.setFont(font);//后面创建单元格之后将单元格格式对象设置到单元格中
//创建单元格
XSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("开科");
XSSFCell cell1 = row.createCell(1);
cell1.setCellStyle(cellStyle);
cell1.setCellValue("java");
XSSFCell cell2 = row.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue("中科");
//创建输出流
FileOutputStream outputStream = new FileOutputStream("D:\\java.xlsx");
workbook.write(outputStream);
//关闭资源
outputStream.flush();
outputStream.close();
workbook.close();