本文目录一览：
如何提高poi读写excel文件（同一文件）的效率
我的代码
// 导入普通客户信息
public String importExcle() {
TEmployee employee = (TEmployee) request.getSession().getAttribute(
"employee");
TCustomerInfo c = new TCustomerInfo();
// 声明数据流
InputStream is = null;
if (employee != null) {
int hang = 0;
int lie = 0;
if (excel != null) {
try {
// 解析excel 2007 版本文件
is = new FileInputStream(excel);
XSSFWorkbook work = new XSSFWorkbook(is);
XSSFSheet sheet = work.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
// 循环表的行，从第二行开始
for (int i = 1; i rows; i++) {
hang = i + 1;
c = new TCustomerInfo();
XSSFRow row = sheet.getRow(i);
if (row == null) {// 判断是否为空
continue;
}
// 读取该行的每一列
for (int j = 0; j row.getPhysicalNumberOfCells(); j++) {
XSSFCell cell = row.getCell(j);
if (cell == null) {
continue;
}
if (cell.toString() == null
"".equals(cell.toString())) {
continue;
}
String v = Util.getXssfCellValue(cell);
switch (j) {// 通过列数来判断对应插如的字段
case 0:
c.setCompanyName(v);
break;
case 1:
c.setLinkName(v);
break;
case 2:
c.setTell(v);
break;
case 3:
c.setTelephone(v);
break;
case 4:
c.setPosition(v);
break;
case 5:
c.setEmail(v);
break;
case 6:
c.setCompanyType(v);
break;
case 7:
c.setCustomerType(v);
break;
case 8:
c.setCompanyDetail(v);
break;
}
c.setCreateMan(employee.getName());
c.setCreateDate(new Date());
}
customerInfoBiz.addCustomer(c);
}
request.setAttribute("message", "导入成功(=^_^=)");
} catch (Exception e) {
System.out.println("进入 ---解析excel 2003 版本文件--错误抛出");
try {
// 解析excel 2003 版本文件
is = new FileInputStream(excel);
HSSFWorkbook work = new HSSFWorkbook(is);
HSSFSheet sheet = work.getSheetAt(0);
if (sheet != null) {
int rows = sheet.getPhysicalNumberOfRows();
// 循环表的行，从第二行开始
for (int i = 1; i rows; i++) {
hang = i + 1;
c = new TCustomerInfo();
HSSFRow row = sheet.getRow(i);
if (row == null) {// 判断是否为空
continue;
}
// 循环表格的列
for (short j = 0; j row
.getPhysicalNumberOfCells(); j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
continue;
}
if (cell.toString() == null|| "".equals(cell.toString())) {
continue;
}
String v = Util.getHssfCellValue(cell);
switch (j) {// 通过列数来判断对应插如的字段
case 0:
c.setCompanyName(v);
break;
case 1:
c.setLinkName(v);
break;
case 2:
c.setTell(v);
break;
case 3:
c.setTelephone(v);
break;
case 4:
c.setPosition(v);
break;
case 5:
c.setEmail(v);
break;
case 6:
c.setCompanyType(v);
break;
case 7:
c.setCustomerType(v);
break;
case 8:
c.setCompanyDetail(v);
break;
}
c.setCreateMan(v);
c.setCreateDate(new Date());
}
customerInfoBiz.addCustomer(c);
}
request.setAttribute("message", "导入成功(=^_^=)");
}
} catch (Exception e1) {
request.setAttribute("message", "第" + hang + "行,第"
+ lie + "列开始导入失败，请注意导入格式！！");
e1.printStackTrace();
}
}
}
} else {
request.setAttribute("message", "登录超时 ，请重新登录！！");
}
// 查询时对象中含有值对查询有误
if (customer != null) {
customer = null;
}
return customerList();
}
POI 读取excel
直接上源码：
package edu.sjtu.erplab.poi;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
/**
* 操作Excel表格的功能类
*/
public class ExcelReader {
private POIFSFileSystem fs;
private HSSFWorkbook wb;
private HSSFSheet sheet;
private HSSFRow row;
/**
* 读取Excel表格表头的内容
* @param InputStream
* @return String 表头内容的数组
*/
public String[] readExcelTitle(InputStream is) {
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
System.out.println("colNum:" + colNum);
String[] title = new String[colNum];
for (int i = 0; i colNum; i++) {
//title[i] = getStringCellValue(row.getCell((short) i));
title[i] = getCellFormatValue(row.getCell((short) i));
}
return title;
}
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public MapInteger, String readExcelContent(InputStream is) {
MapInteger, String content = new HashMapInteger, String();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i = rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j colNum) {
// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
// str += getStringCellValue(row.getCell((short) j)).trim() +
// "-";
str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 获取单元格数据内容为字符串类型的数据
*
* @param cell Excel单元格
* @return String 单元格数据内容
*/
private String getStringCellValue(HSSFCell cell) {
String strCell = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
strCell = "";
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
if (cell == null) {
return "";
}
return strCell;
}
/**
* 获取单元格数据内容为日期类型的数据
*
* @param cell
* Excel单元格
* @return String 单元格数据内容
*/
private String getDateCellValue(HSSFCell cell) {
String result = "";
try {
int cellType = cell.getCellType();
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
Date date = cell.getDateCellValue();
result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
+ "-" + date.getDate();
} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
String date = getStringCellValue(cell);
result = date.replaceAll("[年月]", "-").replace("日", "").trim();
} else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
result = "";
}
} catch (Exception e) {
System.out.println("日期格式不正确!");
e.printStackTrace();
}
return result;
}
/**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
//方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//cellvalue = cell.getDateCellValue().toLocaleString();
//方法2：这样子的data格式是不带带时分秒的：2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
public static void main(String[] args) {
try {
// 对读取Excel表格标题测试
InputStream is = new FileInputStream("d://test2.xls");
ExcelReader excelReader = new ExcelReader();
String[] title = excelReader.readExcelTitle(is);
System.out.println("获得Excel表格的标题:");
for (String s : title) {
System.out.print(s + " ");
}
// 对读取Excel表格内容测试
InputStream is2 = new FileInputStream("d://test2.xls");
MapInteger, String map = excelReader.readExcelContent(is2);
System.out.println("获得Excel表格的内容:");
for (int i = 1; i = map.size(); i++) {
System.out.println(map.get(i));
}
} catch (FileNotFoundException e) {
System.out.println("未找到指定路径的文件!");
e.printStackTrace();
}
}
}
使用poi包读写excel需要导入哪几个jar包
需要导入哪个jar包主要是你用到了哪些功能，poi-3.8-**这个是最基本的，其他的你可以根据具体需要来导入。因为不知道你具体要实现的功能，教你使用jar报的方法吧！1.最简单的方法-全部导入。2、首先看官方文档或者实例，找到代码，如果代码报错就引入相应的jar包，代码不报错了，就运行，如果报classNotFound，就根据class找相应的jar包了。3.看官方介绍，看看每个包是干嘛的，就可以根据你的功能取需要的了。