Java中常见的用来操作 Excel 的方式有2种:JXL和POI。JXL只能对 Excel进行操作,且只支持到 Excel 95-2000的版本。而POI是Apache 的开源项目,有Java编写的跨平台 Java API,可操作性比较强,简单方便。借助POI,可以方便的生成数据报表,数据批量上传,数据备份等工作。无论你是创建maven项目还是创建普通的项目,都需要拿到使用的jar包。
以下是一些简单使用:
1.创建Maven工程,导入依赖的jar包
<!-- poi 相关 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
2.查看API,根据需求选择需要的类型
-
HSSF : 读写 Microsoft Excel XLS 格式文档
-
XSSF : 读写 Microsoft Excel OOXML XLSX 格式文档
-
SXSSF : 读写 Microsoft Excel OOXML XLSX 格式文档
-
HWPF : 读写 Microsoft Word DOC 格式文档
-
HSLF : 读写 Microsoft PowerPoint 格式文档
-
HDGF : 读 Microsoft Visio 格式文档
-
HPBF : 读 Microsoft Publisher 格式文档
-
HSMF : 读 Microsoft Outlook 格式文档
3.操作示例,将excel中的数据导入数据库。
(1)main方法
package com.hcy.main;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.hcy.dao.OracleDao;
import com.hcy.entity.OptCblBc;
public class ImportDataMain {
private static Logger logger = Logger.getLogger(OracleDao.class);
private static ResourceBundle bundle=ResourceBundle.getBundle("path");
public static void main(String[] args) {
OracleDao OracleDao=new OracleDao();
String filePath=bundle.getString("file_path");
OracleDao.deleteData();
List<OptCblBc> datas=readExcel(filePath);
if(datas!=null && datas.size()>0) {
if(!OracleDao.saveData(datas)) {
System.out.println("保存失败,请检查!");
}
}
}
public static List<OptCblBc> readExcel(String filePath){
List<OptCblBc> list=new ArrayList<OptCblBc>();
List<OptCblBc> tempList=new ArrayList<OptCblBc>();
try {
FileInputStream inputStream = new FileInputStream(new File(filePath));//打开需要读取的文件
HSSFWorkbook wordBook = new HSSFWorkbook(inputStream); //读取工作簿
int sheetNum=wordBook.getNumberOfSheets();//获取该表格的页数
for(int n=0;n<sheetNum;n++) {
HSSFSheet sheet = wordBook.getSheetAt(n);//读取工作表,从0开始
int rowNum=sheet.getLastRowNum();
int ColumnNum=sheet.getRow(0).getPhysicalNumberOfCells();
if(ColumnNum!=13) {//此处根据个人列数修改
logger.info("excel表格列数不等于13!!!");
return null;
}
for(int i=1;i<=rowNum;i++) {//将读取到的每个字段set进实体类
HSSFRow row = sheet.getRow(i);//读取第i行
OptCblBc ocb=new OptCblBc();
ocb.setOpt_seg_name(String.valueOf(row.getCell(0).getRichStringCellValue()));
ocb.setOpt_seg_capacity_name(String.valueOf(row.getCell(1).getRichStringCellValue()));
ocb.setOpt_state(String.valueOf(row.getCell(2).getRichStringCellValue()));
ocb.setCapacity(String.valueOf(row.getCell(3).getRichStringCellValue()));
ocb.setFirst_floor_channel_name(String.valueOf(row.getCell(4).getRichStringCellValue()));
ocb.setSecond_floor_channel_name(String.valueOf(row.getCell(5).getRichStringCellValue()));
ocb.setOtn_channel(String.valueOf(row.getCell(6).getRichStringCellValue()));
ocb.setCircuit_name(String.valueOf(row.getCell(7).getRichStringCellValue()));
ocb.setBusiness_type(String.valueOf(row.getCell(8).getRichStringCellValue()));
ocb.setChannel_state(String.valueOf(row.getCell(9).getRichStringCellValue()));
ocb.setProperty_right(String.valueOf(row.getCell(10).getRichStringCellValue()));
ocb.setCustomer_id(String.valueOf(row.getCell(11).getRichStringCellValue()));
ocb.setPassing_submarine_cable(String.valueOf(row.getCell(12).getRichStringCellValue()));
tempList.add(ocb);
}
}
list.addAll(tempList);
inputStream.close(); //关闭输入流
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
(2)dao层
package com.hcy.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.log4j.Logger;
import com.hcy.entity.OptCblBc;
import com.hcy.utils.JdbcUtil;
public class OracleDao {
private Logger logger = Logger.getLogger(OracleDao.class);
private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource(), true);
public boolean saveData(List<OptCblBc> datas) {
if (datas == null || datas.size() == 0) {
return false;
}
try {
String sql = "insert into v_get_cbl_bc_test (opt_seg_name,opt_seg_capacity_name,opt_state,capacity,first_floor_channel_name,second_floor_channel_name,otn_channel,"
+ "circuit_name,business_type,channel_state,property_right,customer_id,passing_submarine_cable,insert_time) values(?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)";
System.out.println("sql-------->>>" + sql);
Object[][] params = this.dataObject(datas);
qr.batch(sql, params);
logger.info("保存数据至表v_get_cbl_bc_test");
return true;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public void deleteData() {
try {
String sql = "delete from v_get_cbl_bc_test";
qr.update(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Object[][] dataObject(List<OptCblBc> datas) {
Object[][] params = new Object[datas.size()][];
for (int i = 0; i < datas.size(); i++) {
OptCblBc ocb = datas.get(i);
Object[] obj = new Object[13];
obj[0] = ocb.getOpt_seg_name();
obj[1] = ocb.getOpt_seg_capacity_name();
obj[2] = ocb.getOpt_state();
obj[3] = ocb.getCapacity();
obj[4] = ocb.getFirst_floor_channel_name();
obj[5] = ocb.getSecond_floor_channel_name();
obj[6] = ocb.getOtn_channel();
obj[7] = ocb.getCircuit_name();
obj[8] = ocb.getBusiness_type();
obj[9] = ocb.getChannel_state();
obj[10] = ocb.getProperty_right();
obj[11] = ocb.getCustomer_id();
obj[12] = ocb.getPassing_submarine_cable();
params[i] = obj;
}
return params;
}
}
其他根据需要另写,以上就是个最简单也比较实用的例子。