java对excel/word/ppt读写POI的使用
2021-09-21 本文已影响0人
开心的小哈
依赖POI
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.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @outhor chenglong
* @create 2021-09-20 18:14
* @name 小哈
*/
public class Maintest {
String PATH="C:\\Users\\小哈\\poitest";
public static void main(String[] args) throws IOException {
Maintest maintest = new Maintest();
maintest.op003();
}
public void op007() throws IOException{
{
//1.创建一个工作簿07的对象XSSFWorkbook
Workbook workbook=new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet=workbook.createSheet("嘻嘻嘻");
//3.创建一行
Row row1 =sheet.createRow(0);
//4.创建一个单元格
Cell cell = row1.createCell(0);
//5.写入数据
cell.setCellValue("to day user");
Cell cell2 = row1.createCell(1);
cell2.setCellValue(9999999999999l);
Cell cell1 = row1.createCell(1);
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String format = simpleDateFormat.format(date);
cell1.setCellValue(format);
//生成文件 03版本就是使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\xixii.xlsx");
workbook.write(fileOutputStream);
//关闭流
workbook.close();
fileOutputStream.close();
}
}
public void op003() throws IOException {
//1.创建一个工作簿03的对象HSSFWorkbook
Workbook workbook=new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet=workbook.createSheet("嘻嘻嘻");
//3.创建一行
Row row1 =sheet.createRow(0);
//4.创建一个单元格
Cell cell = row1.createCell(0);
//5.写入数据
cell.setCellValue("to day user");
Cell cell2 = row1.createCell(1);
cell2.setCellValue(""+999999999999999L);//如果以数字输出的excel会自动将其转换成科学计数法,所以可以使用字符串输出
Cell cell1 = row1.createCell(2);
Date date = new Date();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String format = simpleDateFormat.format(date);
cell1.setCellValue(format);
//生成文件 03版本就是使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\xixii22.xls");
workbook.write(fileOutputStream);
//关闭流
workbook.close();
fileOutputStream.close();
}
//大数据测试007版本 耗时较长
// over
//13.623本次耗时
public void op007Data() throws IOException{
long startTime=System.currentTimeMillis();
Workbook sheets = new XSSFWorkbook();
Sheet sheet = sheets.createSheet("123");
//写入数据
for(int rowNum=0;rowNum<75536;rowNum++){//耗时较长
Row row = sheet.createRow(rowNum);
for (int chllnum=0;chllnum<10;chllnum++){
Cell cell = row.createCell(chllnum);
cell.setCellValue(chllnum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\testData.xlsx");
sheets.write(fileOutputStream);
fileOutputStream.close();
sheets.close();
long endTiem=System.currentTimeMillis();
System.out.println(((double)(endTiem-startTime)/1000)+"本次耗时");
}
//优化op007Data速度 //SXSSFWorkbook虽然速度快,但是生成临时文件,要在关闭的时候清除临时文件哦
public void sOp007Data() throws IOException{
long startTime=System.currentTimeMillis();
Workbook sheets = new SXSSFWorkbook();
Sheet sheet = sheets.createSheet("123");
//写入数据
for(int rowNum=0;rowNum<75536;rowNum++){//耗时较长
Row row = sheet.createRow(rowNum);
for (int chllnum=0;chllnum<10;chllnum++){
Cell cell = row.createCell(chllnum);
cell.setCellValue(chllnum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\testDataSoup.xlsx");
sheets.write(fileOutputStream);
fileOutputStream.close();
sheets.close();
//清除临时文件
((SXSSFWorkbook)sheets).dispose();
long endTiem=System.currentTimeMillis();
System.out.println(((double)(endTiem-startTime)/1000)+"本次耗时");
}
//大数据测试003版本
public void op003Data() throws IOException{
long startTime=System.currentTimeMillis();
Workbook sheets = new HSSFWorkbook();
Sheet sheet = sheets.createSheet("123");
//写入数据
for(int rowNum=0;rowNum<65536;rowNum++){//不能超过65536行!否则报错Invalid row number (65536) outside allowable range (0..65535)
Row row = sheet.createRow(rowNum);
for (int chllnum=0;chllnum<10;chllnum++){
Cell cell = row.createCell(chllnum);
cell.setCellValue(chllnum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\testData.xls");
sheets.write(fileOutputStream);
fileOutputStream.close();
sheets.close();
long endTiem=System.currentTimeMillis();
System.out.println(((double)(endTiem-startTime)/1000)+"本次耗时");
}
}
import com.sun.org.apache.bcel.internal.generic.NEW;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
/**
* @outhor chenglong
* @create 2021-09-21 11:59
* @name 小哈
*/
public class ExcelRead {
String PATH="C:\\Users\\小哈\\poitest";
public static void main(String[] args) {
ExcelRead excelRead = new ExcelRead();
try {
excelRead.testReadCellTyoe007();
} catch (IOException e) {
e.printStackTrace();
}
}
public void testRead003() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "\\xixii22.xls");
//创建一个工作簿,
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheetAt = workbook.getSheetAt(0);
Row row = sheetAt.getRow(1);
Cell cell = row.getCell(0);
//获取字符串getStringCellValue 字符串类型
System.out.println(cell.getDateCellValue());
fileInputStream.close();
workbook.close();
}
public void testRead007() throws IOException {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "\\xixii22.xlsx");
//创建一个工作簿,
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheetAt = workbook.getSheetAt(0);
Row row = sheetAt.getRow(1);
Cell cell = row.getCell(0);
//获取字符串getStringCellValue 字符串类型
System.out.println(cell.getDateCellValue());
fileInputStream.close();
workbook.close();
}
//读取不同类型的数据
public void testReadCellTyoe007() throws IOException{
//
//获取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "\\zengq.xlsx");
//创建一个工作簿,
Workbook workbook = new XSSFWorkbook(fileInputStream);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.PINK.getIndex());
Sheet sheetAt = workbook.getSheetAt(0);
Row row = sheetAt.getRow(0);
if(row!=null){
int physicalNumberOfCells = row.getPhysicalNumberOfCells();//获取该行的列数总 如果列中存在断值,则列总数到null结束
System.out.println(physicalNumberOfCells);
for (int cellNum=0;cellNum<physicalNumberOfCells;cellNum++){
Cell cell = row.getCell(cellNum);
if(cell!=null){
CellType cellType = cell.getCellType();
switch (cellType){
case STRING:
String stringCellValue = cell.getStringCellValue();
System.out.print(cellType+stringCellValue+"|\t");
break;
case NUMERIC:
double numericCellValue = cell.getNumericCellValue();
System.out.print(cellType+""+numericCellValue+"|\t");
break;
}
}
}
System.out.println();
int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();//获取行总数
System.out.println(physicalNumberOfRows);
for(int rowNum=0;rowNum<physicalNumberOfRows;rowNum++){
Row row1 = sheetAt.getRow(rowNum);
System.out.println();
if(row1!=null){
int physicalNumberOfCells1 = row.getPhysicalNumberOfCells();
for(int cellNum=0;cellNum<physicalNumberOfCells1;cellNum++){
// System.out.print(row1.getCell(cellNum)+"|\t");//会存在科学计数法,这时候可以时候,转换
System.out.println("转换字符串进行输出");
Cell cell = row1.getCell(cellNum);
// cell.setCellType(CellType.STRING);
//This method is deprecated and will be removed in POI 5.0. Use explicit setCellFormula(String), setCellValue(...) or setBlank() to get the desired result.
//可以使用该方法进行代替
// String s1 = new BigDecimal(String.valueOf(cellNum)).stripTrailingZeros().toPlainString();//将科学计数法转换成字符串输出
String s = cell.toString();
System.out.println(s);
}
}
}
}
}
}
公式
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
/**
* @outhor chenglong
* @create 2021-09-21 13:36
* @name 小哈
*/
public class ExcelGongshi {
static String PATH="C:\\Users\\小哈\\poitest";
public static void main(String[] args) {
try {
getdata();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void getdata() throws IOException {
FileInputStream fileInputStream = new FileInputStream(PATH + "\\zengq.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);//XSSFWorkbook xlsx,可以换成HSSFWorkbook读取xls
Sheet sheetAt = workbook.getSheetAt(0);
Row row = sheetAt.getRow(11);
Cell cell = row.getCell(3);
//拿到公式eval
FormulaEvaluator hssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
CellType cellType = cell.getCellType();
switch (cellType){
case FORMULA:
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);//获取公司
//那么如果使用字符串进行输出呢?
CellValue evaluate = hssfFormulaEvaluator.evaluate(cell);//执行计算
//可以使用该方法进行代替
String s = evaluate.formatAsString();
String s1 = new BigDecimal(String.valueOf(s)).stripTrailingZeros().toPlainString();//将科学
System.out.println(s1);//将科学计数法转换字符串进行输出
break;
}
}
}
