当前位置 博文首页 > m0_46212244的博客:java使用Jxl和Poi读写Excel
使用Jxl和Poi需要使用到的jar包(由于我所在电脑的权限问题,不能直接给),可自行前往下载:jar下载
要用到的jar包(最好是全下了):
1.commons-io-2.2.jar
2.dom4j-1.6.1.jar
3.poi-3.11.jar
4.poi-examples-3.11.jar
5.poi-excelant-3.11.jar
6.poi-ooxml-3.11.jar
7.poi-ooxml-schemas-3.11.jar
8.poi-scratchpad-3.11.jar
9.xmlbeans-2.6.0.jar
**
**
1.使用JXl写Excel文件
package com.company.jxl_one;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import java.io.File;
import java.io.IOException;
public class Jxl_WriteExcel {
public static void main(String[] args) {
// 先定义一个表头
String[] strings = {"id", "userName", "sex"};
// 创建Excel文件
File file = new File("E:/Java_Test/jxl_test1.xls");
try {
file.createNewFile();
// 创建工作簿
WritableWorkbook workbook = Workbook.createWorkbook(file);
// 创建sheet页
WritableSheet sheet = workbook.createSheet("sheet1", 0);
// 创建Label对象 给excel表头填入数据
Label label = null;
for (int i = 0; i < strings.length; i++){
label = new Label(i, 0, strings[i]);
sheet.addCell(label);
}
// 追加数据 给相应的表头添加相应的数据
for (int i = 1; i < 10; i++){
label = new Label(0, i, i + "");
sheet.addCell(label);
label = new Label(1, i, "userName" + i);
sheet.addCell(label);
label = new Label(2, i, "男");
sheet.addCell(label);
}
// 写如数据
workbook.write();
// 关闭流
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
2.使用Jxl读取Excel文件
package com.company.jxl_one;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.File;
import java.io.IOException;
// Jxl读取excel
public class Jxl_ReadExcel {
public static void main(String[] args) {
try {
// 创建workbook
Workbook workbook = Workbook.getWorkbook(new File("E:\\Java_Test\\jxl_test1.xls"));
// 获取第一个sheet表
Sheet sheet = workbook.getSheet(0);
// 通过遍历获取数据
for (int i = 0; i < sheet.getRows(); i++){
for (int j = 0; j < sheet.getColumns(); j++){
Cell cell = sheet.getCell(j, i);
System.out.print(cell.getContents() + " ");
}
System.out.println();
}
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
}
3.使用Poi写Excel文件(低版本的Excel即xls)
package com.company.poi;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
// 写excel
public class Poi_WriteExcel {
public static void main(String[] args) {
String[] strings = {"id", "userName", "sex"};
// 创建excel
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
// 创建sheet表
HSSFSheet sheet = hssfWorkbook.createSheet();
// 创建第一行
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
// 插入第一行 即表头
for (int i = 0; i < strings.length; i++){
cell = row.createCell(i);
cell.setCellValue(strings[i]);
}
// 追加数据
for (int i = 1; i < 10; i++){
HSSFRow row1 = sheet.createRow(i);
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(i); // 第0列第i行
cell1 = row1.createCell(1);
cell1.setCellValue("userName" + i); // 第1列第i行
cell1 = row1.createCell(2);
cell1.setCellValue("男"); // 第2列第i行
}
// 创建一个excel文件
File file = new File("E:\\Java_Test\\Poi_test1.xls");
try {
file.createNewFile();
// 将excel存入流中
FileOutputStream stream = FileUtils.openOutputStream(file);
// 将内容写入
hssfWorkbook.write(stream);
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
4.使用Poi读取Excel(低版本的Excel)
package com.company.poi;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.