文件对比:
从后缀名来看：
03.xls
07.xlsx
Excel 中的对象：
1-工作簿 2-工作表 3-行 4-列
按照面向对象的思想：显示成成一个工作簿，由工作簿创建工作表，由工作表创建行，由行指定列，从而定位到具体的某个单元格。
写文件：
03版本写：
/**
* 操作Microsoft 03版本的excel
*/
@org.junit.Test
public void testWrite03() {
//创建一个新的excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//在excel工作簿中创建一个工作表，其中缺省名字为sheet0
HSSFSheet sheet = workbook.createSheet("丫丫丫统计表");
//在这个sheet中创建第一行 row1
HSSFRow row1 = sheet.createRow(0);//注意：第一行的下标是从0开始的
//在这个sheet中创建第一个列，也就是第一个单元格 col 1-1
Cell cell_1_1 = row1.createCell(0);//注意：下标也是从0开始的
cell_1_1.setCellValue("今日新增关注");
//创建单元格 col 1-2
Cell cell_1_2 = row1.createCell(1);
cell_1_2.setCellValue(999);
//创建第二行 row 2
HSSFRow row2 = sheet.createRow(1);
Cell cell2_1 = row2.createCell(0);
cell2_1.setCellValue("统计时间");
Cell cell2_2 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell2_2.setCellValue(time);
String path = "D:\ideaOldProject\poi-excel\";
//新建输出流（注意：要先创建文件夹）
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(path + "丫丫丫统计表03版本.xls");
//把相应的excel 工作簿存盘
workbook.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
System.out.println("文件生成完毕~~");
} catch (IOException e) {
e.printStackTrace();
}
}
}
07版本写：
/**
* 操作Microsoft 07版本
*/
@org.junit.Test
public void testWrite07(){
//创建一个新的excel工作簿 基本上什么都没有改变，只是生成工作簿的对象变了
XSSFWorkbook workbook = new XSSFWorkbook();
//在excel工作簿中创建一个工作表，其中缺省名字为sheet0
XSSFSheet sheet = workbook.createSheet("服装统计表");
//在这个sheet中创建第一行 row1
XSSFRow row1 = sheet.createRow(0);//注意：第一行的下标是从0开始的
//在这个sheet中创建第一个列，也就是第一个单元格 col 1-1
Cell cell_1_1 = row1.createCell(0);//注意：下标也是从0开始的
cell_1_1.setCellValue("服装品牌");
//创建单元格 col 1-2
Cell cell_1_2 = row1.createCell(1);
cell_1_2.setCellValue("波司登");
//创建第二行 row 2
XSSFRow row2 = sheet.createRow(1);
Cell cell2_1 = row2.createCell(0);
cell2_1.setCellValue("销量");
Cell cell2_2 = row2.createCell(1);
// String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell2_2.setCellValue(999);
String path = "D:\ideaOldProject\poi-excel\";
//新建输出流（注意：要先创建文件夹）
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(path + "服装销售统计.xlsx");
//把相应的excel 工作簿存盘
workbook.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
System.out.println("文件生成完毕~~");
} catch (IOException e) {
e.printStackTrace();
}
}
}
数据批量导入！
大文件写HSSF
缺点：最多只能处理65536行，否则会抛出异常
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
大文件写XSSF
缺点：写数据时速度非常慢，非常消耗内存，也会发生内存溢出，如100万条
优点：可以写较大的数据量，如20万条
大文件写SXSSF
优点：可以写非常大的数据量，如100万条甚至更多条数据，写数据速度快，占用更少的内存。
注意：
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果超过这个数量，则最前面的数据被写入临时文件。
如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook(数量)
SXSSFWorkbook-来自官方的解释：实现BigGridDemo策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。
请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释。。。仍然只能存储在内存中，因此如果广泛使用，可能需要大量内存。
03版本批量写
/**
* 操作Microsoft 03版本 写如大数据
*/
@org.junit.Test
public void testWrite03BigData(){
//记录开始的时间:
long start = System.currentTimeMillis();
//创建一个新的excel工作簿 基本上什么都没有改变，只是生成工作簿的对象变了
HSSFWorkbook workbook = new HSSFWorkbook();
//在excel工作簿中创建一个工作表，其中缺省名字为sheet0
Sheet sheet = workbook.createSheet("服装统计表");
//xls文件最大支持65536行
for (int rowNum = 0; rowNum <65536 ; rowNum++) {
//创建一个行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum <10 ; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum+1);
}
}
String path = "D:\ideaOldProject\poi-excel\";
//新建输出流（注意：要先创建文件夹）
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(path + "大数据量写入03版本.xls");
//把相应的excel 工作簿存盘
workbook.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
System.out.println("文件生成完毕~~");
} catch (IOException e) {
e.printStackTrace();
}
}
//记录结束的时间:
long end = System.currentTimeMillis();
System.out.println("============耗时："+(double)(end-start)/1000);//除以1000，最后得出来的就是秒
}
文件生成完毕~~
============耗时：1.179
如果我们想生成65537行：
报错：java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
07版本批量写
/**
* 操作Microsoft 07版本 写如大数据 对写入的数据量没有限制，只和电脑的配置有关系了。
*/
@org.junit.Test
public void testWrite07BigData() {
//记录开始的时间:
long start = System.currentTimeMillis();
//创建一个新的excel工作簿 基本上什么都没有改变，只是生成工作簿的对象变了
XSSFWorkbook workbook = new XSSFWorkbook();
//在excel工作簿中创建一个工作表，其中缺省名字为sheet0
Sheet sheet = workbook.createSheet("服装统计表");
//xlsx文件，理论上没有限制
for (int rowNum = 0; rowNum < 100000; rowNum++) {
//创建一个行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum + 1);
}
}
String path = "D:\ideaOldProject\poi-excel\";
//新建输出流（注意：要先创建文件夹）
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(path + "大数据量写入07版本.xlsx");
//把相应的excel 工作簿存盘
workbook.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
System.out.println("文件生成完毕~~");
} catch (IOException e) {
e.printStackTrace();
}
}
//记录结束的时间:
long end = System.currentTimeMillis();
System.out.println("============耗时：" + (double) (end - start) / 1000);//除以1000，最后得出来的就是秒
}
文件生成完毕~~
============耗时：9.705
07升级版批量写
/**
* 操作Microsoft 07版本 写如大数据 对写入的数据量没有限制，只和电脑的配置有关系了。但是速度方面和03版本的相比慢多了
* 但是我们可以使用它的升级版本，借助临时文件，就比07版本的快多了
*/
@org.junit.Test
public void testWrite07BigDataSuper() {
//记录开始的时间:
long start = System.currentTimeMillis();
//创建一个新的excel工作簿 基本上什么都没有改变，只是生成工作簿的对象变了
SXSSFWorkbook workbook = new SXSSFWorkbook();
//在excel工作簿中创建一个工作表，其中缺省名字为sheet0
Sheet sheet = workbook.createSheet("服装统计表");
//xlsx文件，理论上没有限制
for (int rowNum = 0; rowNum < 100000; rowNum++) {
//创建一个行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum + 1);
}
}
String path = "D:\ideaOldProject\poi-excel\";
//新建输出流（注意：要先创建文件夹）
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(path + "大数据量写入07升级版本.xlsx");
//把相应的excel 工作簿存盘
workbook.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
System.out.println("文件生成完毕~~");
} catch (IOException e) {
e.printStackTrace();
}
}
//文件生成完毕的时候，将产生的临时文件删除掉
((SXSSFWorkbook)workbook).dispose();
//记录结束的时间:
long end = System.currentTimeMillis();
System.out.println("============耗时：" + (double) (end - start) / 1000);//除以1000，最后得出来的就是秒
}
文件生成完毕~~
============耗时：2.428
读操作：
03版本
/**
* Microsoft 03版本的数据读操作
*/
@org.junit.Test
public void testRead03() throws IOException {
String path = "D:\ideaOldProject\poi-excel\";
InputStream inputStream=null;
try {
inputStream= new FileInputStream(path+ "丫丫丫统计表03版本.xls");
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
HSSFSheet sheet = workbook.getSheetAt(0);//这是一种使用下标的方式，当然也可以通过名字来进行获取
//读取第一行第一列
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String cellValue = cell.getStringCellValue();
System.out.println("=========单元格的内容："+cellValue);
} catch (FileNotFoundException e) {
e.printStackTrace();
}finally {
if(inputStream!=null){
inputStream.close();
}
}
}
=========单元格的内容：今日新增关注
07版本
/**
* Microsoft 07版本的数据读操作
*/
@org.junit.Test
public void testRead07() throws IOException {
String path = "D:\ideaOldProject\poi-excel\";
InputStream inputStream=null;
try {
inputStream= new FileInputStream(path+ "服装销售统计.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);//这是一种使用下标的方式，当然也可以通过名字来进行获取
//读取第一行第一列
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String cellValue = cell.getStringCellValue();
System.out.println("=========单元格的内容："+cellValue);
} catch (FileNotFoundException e) {
e.printStackTrace();
}finally {
if(inputStream!=null){
inputStream.close();
}
}
}
=========单元格的内容：服装品牌
读取excel文件中的不同类型：
这里以03版本的excel为例
/**
* 批量的读取excel文件中的数据
* Microsoft 03版本
*/
@org.junit.Test
public void testReadBigData03(){
String path = "D:\ideaOldProject\poi-excel\";
InputStream inputStream=null;
try {
inputStream=new FileInputStream(path+"会员消费商品明细表.xls");
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//获取第一个sheet页中的内容
HSSFSheet sheet = workbook.getSheetAt(0);
//首先，读取标题的内容
Row rowTitle = sheet.getRow(0);
//在实际的项目开发中一定要判断为空
if(rowTitle!=null){
//求得这个行中一共有多少个非空的列
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int i = 0; i <cellCount ; i++) {
Cell cell = rowTitle.getCell(i);
if(cell!=null){
int type = cell.getCellType();//获取该单元格的类型
String value = cell.getStringCellValue();//获取单元格的值，一般情况下，标题单元格的值都是String类型的，这里是不用进行类型判断的额
System.out.print(value+"|");
}
}
}
System.out.println("");//加上一个换行
//读取商品列表中的数据
int rowCount = sheet.getPhysicalNumberOfRows();//求得这个sheet页中一共有多少个非空的行
//循环是从1开始的，因为0表示的是标题行
for (int i = 1; i <rowCount ; i++) {
Row rowData = sheet.getRow(i);
if(rowData!=null){
//读取cell格子中的内容
int cellCount = rowData.getPhysicalNumberOfCells();//得到这个行中一共有多少个格子，也就是列
System.out.print("["+(i+1)+"-"+(cellCount)+"]");
for (int j = 0; j <cellCount ; j++) {
Cell cell = rowData.getCell(j);
if(cell!=null){
//判断单元格中的内容的数据类型
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING://字符串
System.out.print("[STRING]");
cellValue= cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔类型
System.out.print("[BOOLEAN]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
System.out.print("[BLANK]");
// cellValue= cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC://数值类型
//数值类型，可能是日期类型，当然也可能是正常的数值类型，这里需要进行判断
if (HSSFDateUtil.isCellDateFormatted(cell)){//如果是日期类型
System.out.print("[DATE]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
break;
}else{//正常的数值类型
System.out.print("[Number]");
cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
double numericCellValue = cell.getNumericCellValue();
cellValue = String.valueOf(numericCellValue);
break;
}
case HSSFCell.CELL_TYPE_ERROR://如果是一个错误
System.out.print("[ERROR]");
// cellValue= cell.getStringCellValue();
break;
}
System.out.print(cellValue+" ");
}
}
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if(inputStream!=null){
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
运行结果：
序号|卡号|持卡人|手机号|消费日期|小票号|商品编号|商品条码|商品名称|商品单位|原价|销售价|销售数量|销售金额|优惠金额|是否上架|
[2-16][STRING]1 [STRING]100088 [STRING]爱丽丝 [STRING]12333333333 [DATE]2020-04-21 [STRING]0000201510200146 [STRING]PV700012 [STRING]PV700012 [STRING]蒙牛 [STRING]瓶 [Number]1.0 [Number]1.0 [STRING]1.0 [Number]1.0 [Number]1.0 [BOOLEAN]true
[3-16][STRING]2 [Number]1.0 [STRING]丫丫丫 [STRING]12333333333 [DATE]2020-04-21 [STRING]0000201510200146 [STRING]PV700006 [STRING]PV700006 [STRING]老白金 [STRING]盒 [Number]1.0 [Number]1.0 [STRING]1.0 [Number]1.0 [BLANK] [BOOLEAN]false
读取excel中的公式：
@org.junit.Test
public void testFormula(){
//首先，读取文件
String path = "D:\ideaOldProject\poi-excel\";
InputStream inputStream=null;
try {
inputStream=new FileInputStream(path+"计算公式.xls");
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//获取第一个sheet页中的内容
HSSFSheet sheet = workbook.getSheetAt(0);
//首先，读取标题的内容
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
//公式计算器
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);
//输出单元格内容
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA:
//得到公式
String formula = cell.getCellFormula();
System.out.println("======公式："+formula);
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println("======单元格的值："+cellValue);
break;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if(inputStream!=null){
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
运行结果：
======公式：SUM(A2:A4)
======单元格的值：600.0