Excel是我们平时工作中比较常用的用于存储二维表数据的,JAVA也可以直接对Excel进行操作,在这篇博客中将为大家介绍两种操作Excel的方式,分别为:jxl和poi。
(一)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();
}
}
}
}
依赖包
net.sourceforge.jexcelapi
jxl
2.6.10
原创来源:滴一盘技术