方式一:HSSF创建Excel
package com.excel.demo;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class PoiCreateExcel {
/**
* POI创建Excel文件
* HSSF生成
*/
@SuppressWarnings("resource")
public static void main(String[] args) {
String[] title = { "id", "name", "age" };
// 创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表sheet
HSSFSheet sheet = workbook.createSheet();
// 创建第一行
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
// 插入第一行数据 id,name,age
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 追加数据
for (int i = 1; i < 10; i++) {
// 从第一行开始(索引)
row = sheet.createRow(i);
// 创建列索引
cell = row.createCell(0);
cell.setCellValue("0" + i);
cell = row.createCell(1);
cell.setCellValue("张" + i);
cell = row.createCell(2);
cell.setCellValue("1" + i);
// 创建一个文件
File file = new File("D:/poi_test.xls");
try {
file.createNewFile();
// 将excel内容存盘
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
}
方式二:XSSF创建Excel
package com.excel.demo;
import java.io.File;
import java.io.FileOutputStream;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class PoiCreateExcel2 {
/**
* POI创建Excel文件
* XSSF生成,将所有HSSF改为XSSF即可
*/
@SuppressWarnings("resource")
public static void main(String[] args) {
String[] title = { "id", "name", "age" };
// 创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建一个工作表sheet
XSSFSheet sheet = workbook.createSheet();
// 创建第一行
XSSFRow row = sheet.createRow(0);
XSSFCell cell = null;
// 插入第一行数据 id,name,age
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
}
// 追加数据
for (int i = 1; i < 10; i++) {
// 从第一行开始(索引)
row = sheet.createRow(i);
// 创建列索引
cell = row.createCell(0);
cell.setCellValue("0" + i);
cell = row.createCell(1);
cell.setCellValue("张" + i);
cell = row.createCell(2);
cell.setCellValue("1" + i);
// 创建一个文件
File file = new File("D:/poi_test.xlsx");
try {
file.createNewFile();
// 将excel内容存盘
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
}
POI解析Excel
package com.excel.demo;
import java.io.File;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class PoiAnalysisExcel {
/**
* POI解析Excel
*/
public static void main(String[] args) {
// 获取需要解析的文件
File file = new File("D:/poi_test.xls");
try {
// 创建Excel,读取文件内容
HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
// 读取第一个工作表 (这个有两种获取,知道Sheet名字,直接get就行,不知道读取默认)
// HSSFSheet sheet = workbook.getSheet("Sheet0");
// 获取默认第一个工作表Sheet
HSSFSheet sheetAt = workbook.getSheetAt(0);
// 获取sheet第一行
int firstRowNum = 0;
// 获取sheet中的最后一行
int lastRowNum = sheetAt.getLastRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) {
// 获取当前行
HSSFRow row = sheetAt.getRow(i);
// 获取当前行的最后一列
short lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
// 获取当前列
HSSFCell cell = row.getCell(j);
// 给当前列赋值
String cellValue = cell.getStringCellValue();
// 要去掉ln,防止列换行
System.out.print(cellValue + " ");
}
// 需要换行
System.out.println();
}
} catch (Exception e) {
}
}
}