POI==>
引入依赖:
<!--excel导出poi工具jar包-->
<!--poi(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--poi(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--时间格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
代码:
package gcw.demo.exportFile;
import gcw.demo.text.Text01;
import javafx.animation.FadeTransitionBuilder;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.junit.Test;
import java.io.*;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
/**
* @Author GCW
* @Date 2021/6/4 16:16
*/
public class poi {
/**
* poi导出Excel的demo(03版)
*
* @throws IOException
* @throws BiffException
*/
public static void TextWrite03() throws IOException{
// 1、创建一个工作簿
Workbook wb = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = wb.createSheet("问题表");
// 3、创建一行
Row row1 = sheet.createRow(0);
Row row2 = sheet.createRow(1);
// 4、创建一个单元格
Cell cell1 = row1.createCell(0);
// 5、在第一个单元格内添加内容
cell1.setCellValue("姓名");
Cell cell2 = row1.createCell(1);
cell2.setCellValue("时间");
Cell cell3 = row2.createCell(0);
cell3.setCellValue("张三");
Cell cell4 = row2.createCell(1);
String time = new DateTime().toString("YYYY-MM-dd HH:mm:ss");
cell4.setCellValue(time);
FileOutputStream fileOutputStream = null;
try {
// 6、创建一个EXCEL的文件
fileOutputStream = new FileOutputStream("成伟的EXCEL.xls");
// 7、进行输出
wb.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
// 8、关闭流
fileOutputStream.close();
}
System.out.println("运行成功");
}
/**
* poi导出Excel的demo(07版)
*
* @throws IOException
*/
@Test
public void TextWrite07() throws IOException{
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
for (int rowNum = 0; rowNum < 10 ; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream("textWrite07.xlsx");
wb.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
}finally {
if (fileOutputStream !=null){
fileOutputStream.close();
}
}
}
/**
* poi读取Excel的工具类(03版)
*
* @throws IOException
* @throws BiffException
*/
@Test
public void excelRead() throws IOException {
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream("D:\\y\\成伟的EXCEL.xls");
// 1、读取一个工作簿
Workbook wb = new HSSFWorkbook(fileInputStream);
// 2、读取一个工作表
Sheet sheet = wb.getSheetAt(0);
// 3、读取一个单元格
// Row rowTitle = sheet.getRow(0);
// if (rowTitle != null) {
// /*获取一行多少位*/
// int cleeCount = rowTitle.getPhysicalNumberOfCells();
// for (int cellNum = 0; cellNum < cleeCount; cellNum++) {
// Cell cell = rowTitle.getCell(cellNum);
// if (cell != null) {
// //读取值一定要注意读取的类型
// //cell.getNumericCellValue() 读取的为数字类型
// System.out.println(cell.getStringCellValue());
// }
// }
// }
/*获取每列的数据*/
//获取到这一行多少个有值的单元格
int rowCount = sheet.getPhysicalNumberOfRows();
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)wb);
for (int rowNum = 1; rowNum <rowCount ; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData!=null){
/*获取一行多少位*/
int cleeCount = rowData.getPhysicalNumberOfCells();
//从第二行第一个开始
for (int cellNum = 0; cellNum < cleeCount; cellNum++) {
System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
Cell cell = rowData.getCell(cellNum);
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_NUMERIC: // 数字(日期 和 普通数字)
System.out.print("【NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
System.out.print("【日期】");
// 转换为时间类型
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("YYYY-MM-dd");
}else {//数字
// 不是日期时 防止数字过长
System.out.print("【转换为字符串输出】");
cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_FORMULA: // 格式为公式时
System.out.print("【FORMULA】");
String formula = cell.getCellFormula();
System.out.println(formula);
// 计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
cellValue = evaluate.formatAsString();
break;
case HSSFCell.CELL_TYPE_BLANK: //为null时
System.out.print("【BLANK】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
cell.getBooleanCellValue();
System.out.print("【布尔类型】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR: // 数据类型错误
System.out.print("【数据类型错误】");
break;
}
//读取值一定要注意读取的类型
//cell.getNumericCellValue() 读取的为数字类型
// System.out.println(cell.getStringCellValue());
System.out.print(cellValue+"|");
}
}
System.out.println();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileInputStream!=null){
fileInputStream.close();
}
}
}
/**
* poi读取Excel的工具类(03版)
*
* @throws IOException
* @throws BiffException
*/
@Test
public void excelRead07() throws IOException {
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream("D:\\y\\成伟的EXCEL.xls");
// 1、读取一个工作簿
Workbook wb = new HSSFWorkbook(fileInputStream);
// 2、读取一个工作表
Sheet sheet = wb.getSheetAt(0);
/*获取每列的数据*/
//获取到这一行多少个有值的单元格
int rowCount = sheet.getPhysicalNumberOfRows();
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)wb);
for (int rowNum = 1; rowNum <rowCount ; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData!=null){
/*获取一行多少位*/
int cleeCount = rowData.getPhysicalNumberOfCells();
//从第二行第一个开始
for (int cellNum = 0; cellNum < cleeCount; cellNum++) {
System.out.print("["+(rowNum+1)+"-"+(cellNum+1)+"]");
Cell cell = rowData.getCell(cellNum);
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_NUMERIC: // 数字(日期 和 普通数字)
System.out.print("【NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
System.out.print("【日期】");
// 转换为时间类型
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("YYYY-MM-dd");
}else {//数字
// 不是日期时 防止数字过长
System.out.print("【转换为字符串输出】");
cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_FORMULA: // 格式为公式时
System.out.print("【FORMULA】");
String formula = cell.getCellFormula();
System.out.println(formula);
// 计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
cellValue = evaluate.formatAsString();
break;
case HSSFCell.CELL_TYPE_BLANK: //为null时
System.out.print("【BLANK】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
cell.getBooleanCellValue();
System.out.print("【布尔类型】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR: // 数据类型错误
System.out.print("【数据类型错误】");
break;
}
//读取值一定要注意读取的类型
//cell.getNumericCellValue() 读取的为数字类型
// System.out.println(cell.getStringCellValue());
System.out.print(cellValue+"|");
}
}
System.out.println();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileInputStream!=null){
fileInputStream.close();
}
}
}
}
easyexcel=>
<!--引入easyExcel的依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
写入
entity
@Excelproperty("字符串标题")
private String string
@Excelproperty("日期标题")
private String string
@Excelproperty("数字标题")
private String string
service
public void simpleWrite(){
//创建一个集合
List<DemoData> list = new ArrayList<DemoData>()
//new所对应的实体类对象
DemoData demo = new DemoData;
/*在这下面把数据添加进对象中再添加到集合中*/
//写法一
String filename = PATH+"Execl.xlsx";
//这里需要指定写用那个class去写,然后写第一个sheet,名字为模板 然后文件流会自动关闭
//write (fileName, 格式类)
//sheet(表名)
//doWrite
Easyexcel.Write(fileName,DemoData.class).sheet("模板").doWrite(list)
}