目录
POI操作
poi操作excel分为03版和07版,03版的excel是有长度限制的
最多可以写入65535条数据
03版excel操作
1,导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具,这里用Java原生的也可以-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
2,最简单的写入
@Test
//03版excel写操作
public void test01() throws Exception {
//创建一个新的工作簿
Workbook workbook = new HSSFWorkbook();
//新建一个工作表
Sheet sheet = workbook.createSheet("导出excel");
//创建行
Row row = sheet.createRow(0);
//创建单元格(列和行交错)
Cell cell = row.createCell(0);
//给第一个单元格设置值
cell.setCellValue("天下第一");
//创建第二个单元格
Cell cell1 = row.createCell(1);
//给第二个单元格设置值
cell1.setCellValue("1");
//创建第二行的两列数据
Row row1 = sheet.createRow(1);
Cell cell2 = row1.createCell(0);
cell2.setCellValue("天下第二");
Cell cell3 = row1.createCell(1);
cell3.setCellValue("2");
//创建输出流
FileOutputStream fs = new FileOutputStream(path+"03版排名.xls");
//将相应的excel工作簿存盘
workbook.write(fs);
fs.close();
System.out.println("文件生成成功");
}
3,大数据写入
@Test
//03版写操作大数据
public void test03() throws Exception{
long start = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("03版操作大数据");
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);
}
}
FileOutputStream fs = new FileOutputStream(path+"03版大数据操作.xls");
workbook.write(fs);
System.out.println("文件导出成功");
long end = System.currentTimeMillis();
System.out.println((double) (end-start) / 1000 +"秒");
}
07版操作
导入依赖
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
1,最简单的写
@Test
//07版excel写操作
public void test02() throws Exception {
//与03版实现类不同
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("07版excel");
//第一行数据
Row row1 = sheet.createRow(0);
Cell cell1 = row1.createCell(0);
cell1.setCellValue("字段一");
Cell cell2 = row1.createCell(1);
cell2.setCellValue("1");
//第二行数据
Row row2 = sheet.createRow(1);
Cell cell3 = row2.createCell(0);
cell