读写excel技术
package com.yms;
import java.io.File;
import java.io.IOException;
/**
* @Author 杨明书
* @PackageName: com.yms
* @ClassName: JXLCreateExcel
* @Description:
* @Date: 2022/1/4 12:59
*/
public class JXLCreateExcel {
public static void main(String[] args) throws IOException {
//创建文件file
File file = new File("C:\\Users\\Administrator\\Desktop\\test.xls");
file.createNewFile();
// 2.创建工作簿
// WritableWorkbook workbook = Workbook.createWorkbook(file);
// // 3:创建sheet页,也可以设置多个
// WritableSheet sheet = workbook.createSheet("第一页",0);
// // 4.设置titles(列名)
// String [] titles={"第一列","第二列","第三列"};
// // 5. 单元格
// Label label = null;
// //6:给第一行设置列名
// for(int i=0;i<titles.length;i++){
// //x,y,第一行的列名
// label=new Label(i,0,titles[i]);
// // 7：添加单元格
// sheet.addCell(label);
// }
// //8：模拟数据库导入数据
// for (int i = 1; i < 10; i++) {
// //添加编号，第二行第一列
// label=new Label(0,i,i+"");
// sheet.addCell(label);
// //添加第二列
// label=new Label(1,i,"10010"+i);
// sheet.addCell(label);
// //添加第三列
// label=new Label(2,i,"123456");
// sheet.addCell(label);
// }
// // 写入数据（这一步不要忘了，要不Excel是空的）
// workbook.write();
// // 最后一步 关闭工作簿
// workbook.close();
}
}
package com.yms;
import java.io.IOException;
/**
* @Author 杨明书
* @PackageName: com.yms
* @ClassName: JXLReadExcel
* @Description:
* @Date: 2022/1/4 13:08
*/
public class JXLReadExcel {
public static void main(String[] args) throws IOException {
// 1:创建workbook
// Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\hpadmin\\Desktop\\test.xls"));
// // 2:获取第一个工作表sheet
// Sheet sheet=workbook.getSheet(0);
// // 3.获取数据
// System.out.println("行："+sheet.getRows());// 获取行数
// System.out.println("列："+sheet.getColumns());// 获取列数
// System.out.println();
// for(int i=0;i<sheet.getRows();i++){
// for(int j=0;j<sheet.getColumns();j++){
// Cell cell=sheet.getCell(j,i);
// System.out.print(cell.getContents()+" ");
// }
// System.out.println();
// }
// //最后一步：关闭资源
// workbook.close();
}
}
package com.yms;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* @Author 杨明书
* @PackageName: com.yms
* @ClassName: ExcelWriteTest
* @Description:
* @Date: 2022/1/4 11:44
* 向Excel写入数据
*/
public class ExcelWriteTest {
public static void main(String[] args) throws IOException {
File PATH = new File("C:\\Users\\Administrator\\Desktop");
boolean newFile = PATH.createNewFile();
//1.创建一个工作簿
Workbook workbook=new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("yms工作表");
//3.创建行
Row row = sheet.createRow(0);
//4.创建一个单元格，也就是列
Cell cell = row.createCell(0);
cell.setCellValue("第一列");
Cell cell1 = row.createCell(1);
cell1.setCellValue(111);
// 第二行 (2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("时间");
// (2,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 第三行 (2,1)
Row row3 = sheet.createRow(2);
Cell cell3 = row3.createCell(0);
cell3.setCellValue("姓名");
// (3,3)
Cell cell2 = row3.createCell(1);
cell2.setCellValue("xiaoming");
// 生成一张表（IO 流） 03 版本就是使用 xls结尾！
try {
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "test03.xls");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("狂神观众统计表03 生成完毕！");
}catch (Exception e){
e.printStackTrace();
}
}
}
package com.yms;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
/**
* @Author 杨明书
* @PackageName: com.yms
* @ClassName: testWrite07
* @Description:
* @Date: 2022/1/4 13:35
*/
public class testWrite07 {
public static void main(String[] args) {
File file=new File("C:\\Users\\Administrator\\Desktop");
// 1、创建一个工作簿 07
Workbook workbook=new XSSFWorkbook();
// 2、创建一个工作表
Sheet sheet = workbook.createSheet("yms");
// 3、创建一个行 （1,1）
Row row = sheet.createRow(0);
// 4、创建一个单元格
Cell cell = row.createCell(0);
cell.setCellValue("第一列");
// (1,2)
Cell cell1 = row.createCell(1);
cell.setCellValue("ss");
// 第二行 (2,1)
Row row1 = sheet.createRow(1);
Cell cell2 = row1.createCell(0);
cell2.setCellValue("x");
// (2,2)
// 生成一张表（IO 流） 07 版本就是使用 xlsx结尾！
try {
FileOutputStream fileOutputStream = new FileOutputStream(file + "tt.xlsx");
// 输出
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
}catch (Exception e){
e.printStackTrace();
}
System.out.println("文档生成完毕！");
}
}
package com.yms;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileOutputStream;
/**
* @Author 杨明书
* @PackageName: com.yms
* @ClassName: testWrite03BigData
* @Description:
* @Date: 2022/1/4 14:02
*/
public class testWrite03BigData {
public static void main(String[] args) {
// 时间
long begin = System.currentTimeMillis();
File file = new File("C:\\Users\\Administrator\\Desktop");
// 创建一个book
Workbook workbook=new HSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
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);
}
}
System.out.println("over");
try {
FileOutputStream fileOutputStream = new FileOutputStream(file + "testWrite03BigData.xls");
workbook.write(fileOutputStream);
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
fileOutputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
注音：如果超出他的最大范围将会报错：
Exception in thread "main" java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
at org.apache.poi.hssf.usermodel.HSSFRow.setRowNum(HSSFRow.java:239)
at org.apache.poi.hssf.usermodel.HSSFRow.<init>(HSSFRow.java:87)
at org.apache.poi.hssf.usermodel.HSSFRow.<init>(HSSFRow.java:71)
at org.apache.poi.hssf.usermodel.HSSFSheet.createRow(HSSFSheet.java:232)
at org.apache.poi.hssf.usermodel.HSSFSheet.createRow(HSSFSheet.java:68)
at com.yms.testWrite03BigData.main(testWrite03BigData.java:36)
与目标 VM 断开连接, 地址为: ''127.0.0.1:61266'，传输: '套接字''
package com.yms;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
/**
* @Author 杨明书
* @PackageName: com.yms
* @ClassName: testWrite07BigData
* @Description:
* @Date: 2022/1/4 14:20
*/
public class testWrite07BigData {
public static void main(String[] args) {
//开始时间
long begin = System.currentTimeMillis();
//创建文件路径
File file = new File("C:\\Users\\Administrator\\Desktop");
//创建工作薄
Workbook workbook=new XSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 100; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
try {
FileOutputStream fileOutputStream = new FileOutputStream(file + "testWrite07BigData.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}catch (Exception e){
e.printStackTrace();
}
}
}
java.io.FileNotFoundException: C:\Users\Administrator\DesktoptestWrite07BigData.xlsx (另一个程序正在使用此文件，进程无法访问。)
at java.io.FileOutputStream.open0(Native Method)
at java.io.FileOutputStream.open(FileOutputStream.java:270)
at java.io.FileOutputStream.<init>(FileOutputStream.java:213)
at java.io.FileOutputStream.<init>(FileOutputStream.java:101)
at com.yms.testWrite07BigData.main(testWrite07BigData.java:42)
与目标 VM 断开连接, 地址为: ''127.0.0.1:61531'，传输: '套接字''
可以写入大量数据，写入的时间很慢
