2.导入依赖pache POI [1] 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案(适用于Excel97-2008)
为义安等地区用户提供了全套网页设计制作服务,及义安网站建设行业解决方案。主营业务为成都网站设计、成都网站制作、外贸网站建设、义安网站设计,以传统方式定制建设网站,并提供域名空间备案等一条龙服务,秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求,就会得到认可,从而选择与我们长期合作。这样,我们也可以走得更远!
03版本xml结尾的只能有65535行,07版以xlsx结尾,没有限制
org.apache.poi poi3.9 org.apache.poi poi-ooxml3.9 joda-time joda-time2.10.1 junit junit4.12
3.写入Excel03版本用的是HSSF,07版本用的是XSSF
3-1.03测试代码 xlspackage com.example.list.controller;
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 org.junit.Test;
import java.io.FileOutputStream;
//写测试
public class ExcelWriteTest {
String PATH = "E:\\java\\list\\";
@Test
public void test01() throws Exception {
//1.创建工作簿
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
//第一行
//3.创建一个行
Row row1 = sheet.createRow(0);
//4.创建一个单元格//(1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增观众");
//4.创建一个单元格//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(6666);
//第二行(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);
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成完毕");
}
}
3-2.07版本xlsxpackage com.example.list.controller;
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 org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileOutputStream;
//写测试
public class ExcelWriteTest {
String PATH = "E:\\java\\list\\";
@Test
public void test01() throws Exception {
//1.创建工作簿
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
//第一行
//3.创建一个行
Row row1 = sheet.createRow(0);
//4.创建一个单元格//(1,1)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增观众");
//4.创建一个单元格//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(6666);
//第二行(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);
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成完毕");
}
}
4.大数据导入
4-1 03版本03版本耗时2.83秒(每个人电脑性能不一样) 03版本只能处理65536行,过程中写入缓存,不操作磁盘,写入速度快
package com.example.list.controller;
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.junit.Test;
import java.io.FileOutputStream;
//写测试
public class ExcelWriteTest {
String PATH = "E:\\java\\list\\";
@Test
public void testBigData() throws Exception {
//开始时间
long begin = System.currentTimeMillis();
//创建一个薄
HSSFWorkbook 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("完成");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "大数据03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double)( end-begin)/1000);
}
}
4-2 07版本07版本速度慢,写入耗内存,也会内存溢出,我电脑是 4.28秒,处理65536行。
package com.example.list.controller;
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 org.junit.Test;
import java.io.FileOutputStream;
//写测试
public class ExcelWriteTest {
String PATH = "E:\\java\\list\\";
@Test
public void testBigData() throws Exception {
//开始时间
long begin = System.currentTimeMillis();
//创建一个薄
Workbook workbook = new XSSFWorkbook();
//创建一个表
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("完成");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "大数据07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);
}
}
对于写入慢的问题,大文件可以写SCSSF,在写入过程中会生成临时文件,内存只有100条,可以自己修改
package com.example.list.controller;
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.streaming.SXSSFWorkbook;
import org.junit.Test;
import java.io.FileOutputStream;
//写测试
public class ExcelWriteTest {
String PATH = "E:\\java\\list\\";
@Test
public void testBigData() throws Exception {
//开始时间
long begin = System.currentTimeMillis();
//创建一个薄
Workbook workbook = new SXSSFWorkbook();
//创建一个表
Sheet sheet = workbook.createSheet();
//写入数据
//行
for (int rowNum = 0; rowNum< 1000000; rowNum++) {
Row row = sheet.createRow(rowNum);
//列
for (int cellNum = 0; cellNum< 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("完成");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "大数据07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//清楚临时文件
((SXSSFWorkbook)workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);
}
}
5.excel读
5-1 03版本package com.example.list.controller;
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.junit.Test;
import java.io.FileInputStream;
public class ExcelReadTest {
public static final String PATH = "E:\\java\\list\\";
@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH+"统计表03.xls");
//创建一个工作薄,
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//得到表
Sheet sheet = workbook.getSheetAt(0);
//得到行
Row row = sheet.getRow(0);
//得到列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
inputStream.close();
}
}
5-2 07版本package com.example.list.controller;
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 org.junit.Test;
import java.io.FileInputStream;
public class ExcelReadTest {
public static final String PATH = "E:\\java\\list\\";
@Test
public void testRead07() throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH+"统计表07.xlsx");
//创建一个工作薄,
Workbook workbook = new XSSFWorkbook(inputStream);
//得到表
Sheet sheet = workbook.getSheetAt(0);
//得到行
Row row = sheet.getRow(0);
//得到列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
inputStream.close();
}
}
你是否还在寻找稳定的海外服务器提供商?创新互联www.cdcxhl.cn海外机房具备T级流量清洗系统配攻击溯源,准确流量调度确保服务器高可用性,企业级服务器适合批量采购,新人活动首月15元起,快前往官网查看详情吧