java操作excel常用的两种方式

时间:2023-02-01 01:34:45

Excel是我们平时工作中比较常用的用于存储二维表数据的,JAVA也可以直接对Excel进行操作,在这篇博客中将为大家介绍两种操作Excel的方式,分别为:jxl和poi。

对于两者的区别网上有测试如下:

测试结果

类型   数据量(行)   执行时间(ms)   执行时间(ms)   执行时间(ms)   平均时间(ms) 
POI   1000       579       562       532       558 
JXL   1000       500       469       484       484 
POI   5000       984       984       969       979 
JXL   5000       922       860       890       891 
POI   10000      1609      1594      1641       1615 
JXL   10000        1437      1453      1406       1432 
POI   30000      3782      3765      3828       3792 
JXL   30000      3922      3906      3922       3917 
POI   50000      5953      6484      5859       6099 
JXL   50000      6765      7421      6984       7057

 在小数据量时jxl快于poi,在大数据量时poi要快于jxl。但差距都不明显。

(一)jxl

写Excel

import java.io.File;
import java.io.IOException; import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException; /**
* jxl写Excel
*
* @author jianggujin
*
*/
public class JxlWriteDemo
{
public static void main(String[] args) throws IOException, WriteException
{
File xlsFile = new File("jxl.xls");
// 创建一个工作簿
WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);
// 创建一个工作表
WritableSheet sheet = workbook.createSheet("sheet1", 0);
for (int row = 0; row < 10; row++)
{
for (int col = 0; col < 10; col++)
{
// 向工作表中添加数据
sheet.addCell(new Label(col, row, "data" + row + col));
}
}
workbook.write();
workbook.close();
}
}

读Excel

import java.io.File;
import java.io.IOException; import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException; /**
* jxl读excel
*
* @author jianggujin
*
*/
public class JxlReadDemo
{
public static void main(String[] args) throws BiffException, IOException
{
File xlsFile = new File("jxl.xls");
// 获得工作簿对象
Workbook workbook = Workbook.getWorkbook(xlsFile);
// 获得所有工作表
Sheet[] sheets = workbook.getSheets();
// 遍历工作表
if (sheets != null)
{
for (Sheet sheet : sheets)
{
// 获得行数
int rows = sheet.getRows();
// 获得列数
int cols = sheet.getColumns();
// 读取数据
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
System.out.printf("%10s", sheet.getCell(col, row)
.getContents());
}
System.out.println();
}
}
}
workbook.close();
}
}

(二)poi

写Excel

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook; /**
* Poi写Excel
*
* @author jianggujin
*
*/
public class PoiWriteDemo
{
public static void main(String[] args) throws IOException
{
// 创建工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作表
HSSFSheet sheet = workbook.createSheet("sheet1"); for (int row = 0; row < 10; row++)
{
HSSFRow rows = sheet.createRow(row);
for (int col = 0; col < 10; col++)
{
// 向工作表中添加数据
rows.createCell(col).setCellValue("data" + row + col);
}
} File xlsFile = new File("poi.xls");
FileOutputStream xlsStream = new FileOutputStream(xlsFile);
workbook.write(xlsStream);
}
}

读Excel

import java.io.File;
import java.io.IOException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory; /**
* Poi写Excel
*
* @author jianggujin
*
*/
public class PoiReadDemo
{
public static void main(String[] args) throws IOException,
InvalidFormatException
{
File xlsFile = new File("poi.xls");
// 获得工作簿
Workbook workbook = WorkbookFactory.create(xlsFile);
// 获得工作表个数
int sheetCount = workbook.getNumberOfSheets();
// 遍历工作表
for (int i = 0; i < sheetCount; i++)
{
Sheet sheet = workbook.getSheetAt(i);
// 获得行数
int rows = sheet.getLastRowNum() + 1;
// 获得列数,先获得一行,在得到改行列数
Row tmp = sheet.getRow(0);
if (tmp == null)
{
continue;
}
int cols = tmp.getPhysicalNumberOfCells();
// 读取数据
for (int row = 0; row < rows; row++)
{
Row r = sheet.getRow(row);
for (int col = 0; col < cols; col++)
{
System.out.printf("%10s", r.getCell(col).getStringCellValue());
}
System.out.println();
}
}
}
}