POI读写操作excel
参考链接：
-
poi的使用以及详解,poi的maven依赖poi依赖J36524的博客-CSDN博客
-
技巧总结_哔哩哔哩_bilibili
1. 引入pom依赖
<dependencies><!--xls(03)--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><!--xlsx(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><!--test--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version></dependency></dependencies>
2. 写入excel
步骤：工作薄---工作表---行---列
2.1 HSSFWorkbook写入2003版xls格式excel(最多65535条)
package excel;import cn.hutool.core.date.DateTime;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.FileOutputStream;import java.io.IOException;import java.util.Date;/* @ClassName HssfWrite* @Description TODO* @Author Jiangnan Cui* @Date 2023/3/5 10:24* @Version 1.0*/public class HssfWrite {public static void main(String[] args) {String path = "D:\\\\excel\\\\poi\\\\";// 1. 创建一个工作薄Workbook workbook = new HSSFWorkbook();// 2. 在工作簿中创建一个工作表Sheet sheet = workbook.createSheet("xls-test");// 3.在工作表中创建第1行Row row1 = sheet.createRow(0);// 在第1行创建第11单元格Cell cell11 = row1.createCell(0);// 在11单元格写入数据cell11.setCellValue("小崔");// 在第1行创建第12单元格Cell cell12 = row1.createCell(1);// 在12单元格写入数据cell12.setCellValue("加油");// 4.在工作表中创建第2行Row row2 = sheet.createRow(1);// 在第2行创建第21单元格Cell cell21 = row2.createCell(0);// 在21单元格写入数据cell21.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));// 在第2行创建第22单元格Cell cell22 = row2.createCell(1);// 在22单元格写入数据cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));try {// 5.创建文件输出流FileOutputStream fos = new FileOutputStream(path + "03.xls");// 6.将工作薄写入文件workbook.write(fos);// 7.关闭流fos.close();System.out.println("03版excel写入完毕！");} catch (IOException e) {throw new RuntimeException(e);}}}
2.1.1 大数据写入(最多65535条)
package excel;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.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;/* @ClassName HssfWriteBigData* @Description TODO* @Author Jiangnan Cui* @Date 2023/3/5 10:59* @Version 1.0*/public class HssfWriteBigData {public static void main(String[] args) throws IOException {String path = "D:\\\\excel\\\\poi\\\\";// 时间long begin = System.currentTimeMillis();// 创建一个工作簿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");// 创建文件输出流FileOutputStream fos = new FileOutputStream(path +"big-03.xls");workbook.write(fos);// 关闭流fos.close();System.out.println("03版excel写入成功");long end=System.currentTimeMillis();System.out.println("总耗时："+(double)(end-begin)/1000);}}
2.2 XSSFWorkbook写入2007版xlsx格式excel(最多20万条)
package excel;import cn.hutool.core.date.DateTime;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.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;/* @ClassName HssfWrite* @Description TODO* @Author Jiangnan Cui* @Date 2023/3/5 10:24* @Version 1.0*/public class XssfWrite {public static void main(String[] args) {String path = "D:\\\\excel\\\\poi\\\\";// 1. 创建一个工作薄Workbook workbook = new XSSFWorkbook();// 2. 在工作簿中创建一个工作表Sheet sheet = workbook.createSheet("xlsx-test");// 3.在工作表中创建第1行Row row1 = sheet.createRow(0);// 在第1行创建第11单元格Cell cell11 = row1.createCell(0);// 在11单元格写入数据cell11.setCellValue("小崔");// 在第1行创建第12单元格Cell cell12 = row1.createCell(1);// 在12单元格写入数据cell12.setCellValue("加油");// 4.在工作表中创建第2行Row row2 = sheet.createRow(1);// 在第2行创建第21单元格Cell cell21 = row2.createCell(0);// 在21单元格写入数据cell21.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));// 在第2行创建第22单元格Cell cell22 = row2.createCell(1);// 在22单元格写入数据cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));try {// 5.创建文件输出流FileOutputStream fos = new FileOutputStream(path + "07.xlsx");// 6.将工作薄写入文件workbook.write(fos);// 7.关闭流fos.close();System.out.println("07版excel写入完毕！");} catch (IOException e) {throw new RuntimeException(e);}}}
2.2.1 大数据写入(最多20万条)
package excel;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.FileOutputStream;import java.io.IOException;/* @ClassName HssfWriteBigData* @Description TODO* @Author Jiangnan Cui* @Date 2023/3/5 10:59* @Version 1.0*/public class XssfWriteBigData {public static void main(String[] args) throws IOException {String path = "D:\\\\excel\\\\poi\\\\";// 时间long begin = System.currentTimeMillis();// 创建一个工作簿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 < 10; cellNum++) {// 创建单元格Cell cell = row.createCell(cellNum);cell.setCellValue(cellNum);}}System.out.println("over");// 创建文件输出流FileOutputStream fos = new FileOutputStream(path +"big-07.xlsx");workbook.write(fos);// 关闭流fos.close();System.out.println("07版excel写入成功");long end=System.currentTimeMillis();System.out.println("总耗时："+(double)(end-begin)/1000);}}
