1.Java中使用POI操作ExceL的读与写
直接给代码
1.1导入依赖
org.apache.poi
poi
3.10-FINAL
org.apache.poi
poi-ooxml
3.10-FINAL
1.2 使用工具类
package cn.Poi;
import cn.domain.Customer;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
/**
* 、创建单元格
* @param excelDir 路径
* @param excelFileName 文件名
* @throws Exception
*/
public static void createExcel(File excelDir , String excelFileName) throws Exception{
// 1. 内存中创建一个excel工作薄
Workbook wb = new HSSFWorkbook(); //创建了一个之前格式的excel文件
// 创建页
createSheet(wb,10);
// 创建行和列
fillData(wb.getSheet("第0页"),10,10);
// 2. 保存这个excel 磁盘上
File excelFile = new File(excelDir,excelFileName);
final FileOutputStream out = new FileOutputStream(excelFile);
wb.write(out);
out.close();
}
/**
* 同上
* @param excelDir
* @param excelFileName
* @throws Exception
*/
public static void createExcel(File excelDir , String excelFileName) throws Exception{
// 1. 内存中创建一个excel工作薄
Workbook wb = new XSSFWorkbook();//创建了一个之前格式的excel文件
// 2. 保存这个excel 磁盘上
File excelFile = new File(excelDir,excelFileName);
final FileOutputStream out = new FileOutputStream(excelFile);
wb.write(out);
out.close();
}
/**
* 创建文档页码
* @param wb
* @param sheetNum
*/
public static void createSheet(Workbook wb , int sheetNum){
for (int i = 0; i < sheetNum; i++) {
wb.createSheet("第"+i+"页");
}
}
/**
* 创建单元格,并且在里面填充数字进行测试,写入测试
* @param sheet
* @param row
* @param col
*/
public static void fillData(Sheet sheet , int row , int col){
//创建行
for (int i = 0; i < row; i++) {
Row currentRow = sheet.createRow(i);
//每行多少个单元格
for (int i1 = 0; i1 < col; i1++) {
//获取单元格,并且设置单元格里面的值
currentRow.createCell(i1).setCellValue(i*col+i1);
}
}
}
/**、
* 测试读取数字,从第0页开始
* @throws Exception
*/
public static void readExcelint() throws Exception{
FileInputStream excelFile = new FileInputStream("C:/Users\\10596\\Desktop\\firstExcel.xls");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(excelFile);
final HSSFSheet sheet = hssfWorkbook.getSheet("第0页");
//分别获取 文档中最后一行的行标,单元格
for (int i = 0 ; i
final HSSFRow row = sheet.getRow(i);
for (int j = 0 ; j
double value = row.getCell(j).getNumericCellValue();
System.out.println(value);
}
}
}
/**、
* 测试读取文字,从第0页开始
* @throws Exception
*/
public static void readExcelStr() throws Exception{
FileInputStream excelFile = new FileInputStream("C:/Users\\10596\\Desktop\\customerList.xls");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(excelFile);
final HSSFSheet sheet = hssfWorkbook.getSheet("第0页");
//分别获取 文档中最后一行的行标,单元格
for (int i = 0 ; i
final HSSFRow row = sheet.getRow(i);
for (int j = 0 ; j
String stringCellValue = row.getCell(j).getStringCellValue();
System.out.println(stringCellValue);
}
}
}
/**
* 写入数据
* @throws Exception
*/
public static void db2excel() throws Exception{
List list = new ArrayList();
for(int i = 0; i < 100; i++){
Customer customer = new Customer();
customer.setId(i+"jack");
customer.setName("jack"+i);
customer.setAddress("测试"+i);
list.add(customer);
}
// 1. 内存中创建一个excel工作薄
Workbook wb = new HSSFWorkbook(); //创建了一个之前格式的excel文件
// 创建页
createSheet(wb,10);
// 创建行和列
Field[] declaredFields = Customer.class.getDeclaredFields();
//在第0页填充数据
Sheet sheet = wb.getSheet("第0页");
//获取集合的大小,间接性获取有多少个对象需要存入到文档
for (int i = 0; i < list.size() ; i++){
Customer customer = list.get(i);
//有多少对象就有多少行,创建单元行
Row row = sheet.createRow(i);
int col = 0;
for (Field field : declaredFields) {
//反射的 实体类有 私有属性,进行暴力反射
field.setAccessible(true);
//获取对象里面的值,填充到单元格中
String value = (String) field.get(customer);
row.createCell(col++).setCellValue(value);
}
}
// 2. 保存这个excel 磁盘上
File excelFile = new File("C:\\\\Users\\\\10596\\\\Desktop","customerList.xls");
final FileOutputStream out = new FileOutputStream(excelFile);
wb.write(out);
out.close();
}
public static void main(String[] args) throws Exception {
// createExcel(new File("C:\\Users\\10596\\Desktop"),"firstExcel.xls");
// readExcelint(); //读取填充的int数
//填充数据
db2excel();
//读取填充的字符串
readExcelStr();
}
}
1.3 使用写入时创建的实体类
package cn.domain;
public class Customer {
private String id;
private String name;
private String address;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
1.4 包结构,以及效果
包结构
写入的效果
读取文档打印的效果
标签:Java,String,poi,ExceL,new,POI,import,public,wb
来源: /Violet_03027/article/details/100177857