poi读写操作
admin
2024-03-17 13:28:51
0次

1.poi介绍

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)

2.导入依赖

03版本xml结尾的只能有65535行,07版以xlsx结尾,没有限制

  org.apache.poipoi3.9org.apache.poipoi-ooxml3.9joda-timejoda-time2.10.1junitjunit4.12

 3.写入Excel

03版本用的是HSSF,07版本用的是XSSF

3-1.03测试代码 xls

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.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\\";@Testpublic 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版本xlsx

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.joda.time.DateTime;
import org.junit.Test;import java.io.FileOutputStream;//写测试
public class ExcelWriteTest {String PATH = "E:\\java\\list\\";@Testpublic 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\\";@Testpublic 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\\";@Testpublic 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\\";@Testpublic 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\\";@Testpublic 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\\";@Testpublic 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();}
}

 

相关内容

热门资讯

蜻蜓订购APP贵金属订购非法期...   蜻蜓订购APP以"投资平台"名义对外宣传,主打贵金属期货(订购)交易。该平台一方面强调自身"正规...
悟空订购APP非法期货交易欺骗...   近期,有投资者反映,“悟空订购APP”等平台以白银、贵金属订购为名,宣传所谓“高收益”“低门槛”...
小赢淘金APP期货投资骗局,平...   “小赢淘金”之流,最擅长的就是用“注册送现金”、“充值返券”的糖衣炮弹来引流。别天真地以为那是福...
八方淘金APP非法期货交易欺骗...   警惕!八方生财、每日淘金、小赢淘金、悟空订购、蜻蜓订购、斗金订购等APP均涉嫌非法期货交易!  ...
智易购APP贵金属订购交易骗局...   很多人看到“3元起投”“充值送福利”“操作简单”“随时提现”这类宣传,很容易把智易购APP当成普...
喜玛银庄APP商品订购投资骗局...   喜玛银庄APP白银、铂金“现货订购”骗局警示:无资质平台涉嫌非法期货交易,投资者爆仓后如何维权?...
吉鑫乐享APP炒贵金属订购交易...   近期,多名投资者反映在“吉鑫乐享APP”进行贵金属投资时遭遇严重亏损,甚至血本无归。看似正规的“...
易掘金APP虚假期货投资骗局,...   “易掘金”的APP它打着“高收益、灵活投资”的旗号,宣称通过“商品订购”就能轻松赚钱,吸引了不少...
在大吉订购APP订购交易亏损被...   近期,不少投资者反映在“大吉订购App”上投资白银、铂金等商品遭遇严重亏损。该平台宣称提供现货订...
铂银壹号APP商品订购交易亏损...   许多中小投资者误以为网上的“商品现货订购”是合规合法的理财渠道,殊不知这背后隐藏着巨大的合规风险...
兴鑫淘金APP贵金属订购交易亏...   白银、贵金属或其他商品类投资,除国家批准的正规期货交易场所及持牌期货公司外,市面上大量第三方交易...
铂银掘金APP无资质的违规平台...   铂银掘金等平台的投资者,往往是被“注册送现金”“充值返现券”“首单免亏”等广告话术吸引,进而下载...
八方淘金APP订购交易不合规,...   “八方淘金APP”“铂银掘金APP”“铂银壹号APP”等软件被指以“现货订购”为名,实际采用高杠...
在指尖订购APP订购商品亏损被...   指尖订购APP这类平台,表面是“订购交易”,实则是披着电商外衣的非法期货陷阱。绝大多数投资者被蒙...
在犀贝易购APP做商品订购交易...   市面上打着“现货投资”“贵金属交易”旗号的APP并不少,但真正合规、受监管的平台并不多。犀贝易购...
易达淘金APP虚假商品订购交易...   易达淘金APP存在严重的虚假宣传与违规交易行为。该平台打着“商品订购”的旗号,实则通过不合规的交...
大浪淘金APP白银订购亏损被骗...   大浪淘金APP最隐蔽的套路,在于其完全封闭的内盘交易系统。表面上,它宣称与国际贵金属市场接轨;实...
一品好购APP上订购白银出现亏...   一品好购APP原名“一品订购”,运营方为上海宝智未来信息技术有限公司。近期不少投资者反映在该平台...
九州订购APP贵金属订购投资骗...   九州订购APP正通过抖.音、快手、今日头条等平台大量投放投资广告,用“5元起投”“充值返现”“0...
坚锅淘金APP交易软件并不正规...   当前,市面上大量所谓的“现货商城订购”交易软件,早已偏离了正常的实物商品买卖,演变为高杠杆、高手...