先看代码,挨句解释:
一般遍历使用两种方式,1:得到总的行数和每行的列数,然后循环。2:使用迭代
先看第一种:
package com.golden.test;
import java.io.File;
import java.io.FileInputStream;
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;
/**
*
* @author 崔素强
*
*/
public class PoiReadXls2 {
public static void main(String[] args) {
File f = new File("c:\\a.xls");
try {
FileInputStream is = new FileInputStream(f);
HSSFWorkbook wbs = new HSSFWorkbook(is);
HSSFSheet childSheet = wbs.getSheetAt(0);
// System.out.println(childSheet.getPhysicalNumberOfRows());
System.out.println("有行数" + childSheet.getLastRowNum());
for (int j = 0; j < childSheet.getLastRowNum(); j++) {
HSSFRow row = childSheet.getRow(j);
// System.out.println(row.getPhysicalNumberOfCells());
// System.out.println("有列数" + row.getLastCellNum());
if (null != row) {
for (int k = 0; k < row.getLastCellNum(); k++) {
HSSFCell cell = row.getCell(k);
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue()
+ " ");
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue()
+ " ");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue()
+ " ");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + " ");
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println(" ");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println(" ");
break;
default:
System.out.print("未知类型 ");
break;
}
} else {
System.out.print("- ");
}
}
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
得到Excel的文件然后读取,这个很简单。关键有两个地方,也许在网上会看到有的这样使用有的那样使用。
System.out.println("有行数" + childSheet.getLastRowNum());
System.out.println(childSheet.getPhysicalNumberOfRows());
System.out.println("有列数" + row.getLastCellNum());
System.out.println(row.getPhysicalNumberOfCells());
如果人都拷贝代码进行使用了,不知道有什么区别。太多的区别不知道,但是有一点我发现了,那就是如果中间各行或者隔列的话getPhysicalNumberOfRows和getPhysicalNumberOfCells就不能读取到所有的行和列了。
再者,一定要对单元格的格式进行判断switch (cell.getCellType()),不同的单元格格式使用不同的方法。最后加上为止类型,以防万一。
而且在数字类型里,又分为了纯数字和时间格式:
case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是date类型则 ,获取该cell的date值
value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else { // 纯数字
value = String.valueOf(cell.getNumericCellValue());
}
还有一种迭代的方法:
package com.golden.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
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 org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
*
* @author 崔素强
*
*/
public class PoiReadXls {
@SuppressWarnings( { "unchecked", "deprecation" })
public static void main(String[] args) {
File f = new File("c:\\a.xls");
try {
InputStream input = new FileInputStream(f);
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
// System.out.print("行:" + row.getRowNum() + " ");
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
// System.out.println("列:" + cell.getCellNum());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue() + " ");
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue() + " ");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue() + " ");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + " ");
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println(" ");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println(" ");
break;
default:
System.out.print("未知类型 ");
break;
}
}
System.out.println();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;
import java.io.*;
class excel {
public excel() {
}
/**
* 读取excel
*
* @param filepath
*/
public static void readexcel(String filepath) {
try {
InputStream is = new FileInputStream(filepath);
Workbook rwb = Workbook.getWorkbook(is);
// sheet st = rwb.getsheet("0")这里有两种方法获取sheet表,1为名字,2为下标,从0开始
Sheet st = rwb.getSheet(0);
Cell c00 = st.getCell(1, 0);
// 通用的获取cell值的方式,返回字符串
String strc00 = c00.getContents();
// 获得cell具体类型值的方式
if (c00.getType() == CellType.LABEL) {
LabelCell labelc00 = (LabelCell) c00;
strc00 = labelc00.getString();
}
// 输出
System.out.println(strc00);
// 关闭
rwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 输出excel
*
* @param os
*/
public static void writeexcel(OutputStream os) {
try {
/**
* 只能通过api提供的工厂方法来创建workbook,而不能使用WritableWorkbook的构造函数,
* 因为类WritableWorkbook的构造函数为protected类型
* method(1)直接从目标文件中读取WritableWorkbook wwb =
* workbook.createWorkbook(new file(targetfile)); method(2)如下实例所示
* 将WritableWorkbook直接写入到输出流
*
*/
WritableWorkbook wwb = Workbook.createWorkbook(os);
// 创建excel工作表 指定名称和位置
WritableSheet ws = wwb.createSheet("test sheet 1", 0);
WritableSheet ws2 = wwb.createSheet("test sheet 2", 1);
// **************往工作表中添加数据*****************
// 1.添加label对象
Label label = new Label(0, 0, "this is a label test");
ws.addCell(label);
Label label2 = new Label(0, 0, "this is a label test2");
ws2.addCell(label2);
// 添加带有字型formatting对象
WritableFont wf = new WritableFont(WritableFont.TIMES, 18,
WritableFont.BOLD, true);
WritableCellFormat wcf = new WritableCellFormat(wf);
Label labelcf = new Label(1, 0, "this is a label test", wcf);
ws.addCell(labelcf);
// 添加带有字体颜色的formatting对象
WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
WritableCellFormat wcffc = new WritableCellFormat(wfc);
Label Labelcf = new Label(1, 0, "this is a Label cell", wcffc);
ws.addCell(Labelcf);
// 2.添加number对象
Number Labeln = new Number(0, 1, 3.1415926);
ws.addCell(Labeln);
// 添加带有formatting的number对象
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfn = new WritableCellFormat(nf);
Number Labelnf = new jxl.write.Number(1, 1, 3.1415926, wcfn);
ws.addCell(Labelnf);
// 3.添加boolean对象
Boolean Labelb = new jxl.write.Boolean(0, 2, false);
ws.addCell(Labelb);
// 4.添加datetime对象
jxl.write.DateTime Labeldt = new jxl.write.DateTime(0, 3,
new java.util.Date());
ws.addCell(Labeldt);
// 添加带有formatting的dateformat对象
DateFormat df = new DateFormat("dd mm yyyy hh:mm:ss");
WritableCellFormat wcfdf = new WritableCellFormat(df);
DateTime Labeldtf = new DateTime(1, 3, new java.util.Date(), wcfdf);
ws.addCell(Labeldtf);
// 添加图片对象,jxl只支持png格式图片
// File image = new File("f:\\2.png");
// WritableImage wimage = new WritableImage(0,1,2,2,image);
// ws.addImage(wimage);
// 写入工作表
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 拷贝后,进行修改,其中File1为被copy对象,File2为修改后创建的对象
* 尽单元格原有的格式化修饰是不能去掉的,我们还是可以将新的单元格修饰加上去, 以使单元格的内容以不同的形式表现
*
* @param File1
* @param File2
*/
public static void modifyexcel(File File1, File File2) {
try {
Workbook rwb = Workbook.getWorkbook(File1);
WritableWorkbook wwb = Workbook.createWorkbook(File2, rwb);// copy
WritableSheet ws = wwb.getSheet(0);
WritableCell wc = ws.getWritableCell(0, 0);
// 判断单元格的类型,做出相应的转换
if (wc.getType() == CellType.LABEL) {
Label Label = (Label) wc;
Label.setString("the value has been modified");
}
wwb.write();
wwb.close();
rwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试
public static void main(String[] args) {
try {
// 读excel
excel.readexcel("f:/testread.xls");
// 输出excel
File Filewrite = new File("f:/testwrite.xls");
Filewrite.createNewFile();
OutputStream os = new FileOutputStream(Filewrite);
excel.writeexcel(os);
// 修改excel
// excel.modifyexcel(new File(""),new File(""));
} catch (Exception e) {
e.printStackTrace();
}
}
}
/*
* 2.在jsp中做相关测试,创建一个writeexcel.jsp <% response.reset();//清除buffer
* response.setcontenttype("application/vnd.ms-excel"); File Filewrite = new
* File("f:/testwrite.xls"); Filewrite.createNewFile(); new
* FileOutputStream(Filewrite); excelhandle.writeexcel(new
* FileOutputStream(Filewrite)); %>
* 在ie中浏览writeexcel.jsp就可以动态生成excel文档了,其中response.setcontenttype
* ("application/vnd.ms-excel");语句必须要,才能确保不乱码, 在jsp中输入<%@page contenttype
一般遍历使用两种方式,1:得到总的行数和每行的列数,然后循环。2:使用迭代
先看第一种:
package com.golden.test;
import java.io.File;
import java.io.FileInputStream;
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;
/**
*
* @author 崔素强
*
*/
public class PoiReadXls2 {
public static void main(String[] args) {
File f = new File("c:\\a.xls");
try {
FileInputStream is = new FileInputStream(f);
HSSFWorkbook wbs = new HSSFWorkbook(is);
HSSFSheet childSheet = wbs.getSheetAt(0);
// System.out.println(childSheet.getPhysicalNumberOfRows());
System.out.println("有行数" + childSheet.getLastRowNum());
for (int j = 0; j < childSheet.getLastRowNum(); j++) {
HSSFRow row = childSheet.getRow(j);
// System.out.println(row.getPhysicalNumberOfCells());
// System.out.println("有列数" + row.getLastCellNum());
if (null != row) {
for (int k = 0; k < row.getLastCellNum(); k++) {
HSSFCell cell = row.getCell(k);
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue()
+ " ");
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue()
+ " ");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue()
+ " ");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + " ");
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println(" ");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println(" ");
break;
default:
System.out.print("未知类型 ");
break;
}
} else {
System.out.print("- ");
}
}
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
得到Excel的文件然后读取,这个很简单。关键有两个地方,也许在网上会看到有的这样使用有的那样使用。
System.out.println("有行数" + childSheet.getLastRowNum());
System.out.println(childSheet.getPhysicalNumberOfRows());
System.out.println("有列数" + row.getLastCellNum());
System.out.println(row.getPhysicalNumberOfCells());
如果人都拷贝代码进行使用了,不知道有什么区别。太多的区别不知道,但是有一点我发现了,那就是如果中间各行或者隔列的话getPhysicalNumberOfRows和getPhysicalNumberOfCells就不能读取到所有的行和列了。
再者,一定要对单元格的格式进行判断switch (cell.getCellType()),不同的单元格格式使用不同的方法。最后加上为止类型,以防万一。
而且在数字类型里,又分为了纯数字和时间格式:
case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是date类型则 ,获取该cell的date值
value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else { // 纯数字
value = String.valueOf(cell.getNumericCellValue());
}
还有一种迭代的方法:
package com.golden.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
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 org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
*
* @author 崔素强
*
*/
public class PoiReadXls {
@SuppressWarnings( { "unchecked", "deprecation" })
public static void main(String[] args) {
File f = new File("c:\\a.xls");
try {
InputStream input = new FileInputStream(f);
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
// System.out.print("行:" + row.getRowNum() + " ");
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
// System.out.println("列:" + cell.getCellNum());
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue() + " ");
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue() + " ");
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue() + " ");
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + " ");
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println(" ");
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println(" ");
break;
default:
System.out.print("未知类型 ");
break;
}
}
System.out.println();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
这种方法,如果数据的紧凑的,使用还是方便的,但是我发现,如果是空行或者是空列,他就会隔过去。具体的自己试试就知道了。
另外,也能看到这里得到Excel文件的方式是通过File,如果要引用到Struts2里,这是很简单的,因为Struts2上传时Action里定义的就是File或者File数组。声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;
import java.io.*;
class excel {
public excel() {
}
/**
* 读取excel
*
* @param filepath
*/
public static void readexcel(String filepath) {
try {
InputStream is = new FileInputStream(filepath);
Workbook rwb = Workbook.getWorkbook(is);
// sheet st = rwb.getsheet("0")这里有两种方法获取sheet表,1为名字,2为下标,从0开始
Sheet st = rwb.getSheet(0);
Cell c00 = st.getCell(1, 0);
// 通用的获取cell值的方式,返回字符串
String strc00 = c00.getContents();
// 获得cell具体类型值的方式
if (c00.getType() == CellType.LABEL) {
LabelCell labelc00 = (LabelCell) c00;
strc00 = labelc00.getString();
}
// 输出
System.out.println(strc00);
// 关闭
rwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 输出excel
*
* @param os
*/
public static void writeexcel(OutputStream os) {
try {
/**
* 只能通过api提供的工厂方法来创建workbook,而不能使用WritableWorkbook的构造函数,
* 因为类WritableWorkbook的构造函数为protected类型
* method(1)直接从目标文件中读取WritableWorkbook wwb =
* workbook.createWorkbook(new file(targetfile)); method(2)如下实例所示
* 将WritableWorkbook直接写入到输出流
*
*/
WritableWorkbook wwb = Workbook.createWorkbook(os);
// 创建excel工作表 指定名称和位置
WritableSheet ws = wwb.createSheet("test sheet 1", 0);
WritableSheet ws2 = wwb.createSheet("test sheet 2", 1);
// **************往工作表中添加数据*****************
// 1.添加label对象
Label label = new Label(0, 0, "this is a label test");
ws.addCell(label);
Label label2 = new Label(0, 0, "this is a label test2");
ws2.addCell(label2);
// 添加带有字型formatting对象
WritableFont wf = new WritableFont(WritableFont.TIMES, 18,
WritableFont.BOLD, true);
WritableCellFormat wcf = new WritableCellFormat(wf);
Label labelcf = new Label(1, 0, "this is a label test", wcf);
ws.addCell(labelcf);
// 添加带有字体颜色的formatting对象
WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.RED);
WritableCellFormat wcffc = new WritableCellFormat(wfc);
Label Labelcf = new Label(1, 0, "this is a Label cell", wcffc);
ws.addCell(Labelcf);
// 2.添加number对象
Number Labeln = new Number(0, 1, 3.1415926);
ws.addCell(Labeln);
// 添加带有formatting的number对象
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcfn = new WritableCellFormat(nf);
Number Labelnf = new jxl.write.Number(1, 1, 3.1415926, wcfn);
ws.addCell(Labelnf);
// 3.添加boolean对象
Boolean Labelb = new jxl.write.Boolean(0, 2, false);
ws.addCell(Labelb);
// 4.添加datetime对象
jxl.write.DateTime Labeldt = new jxl.write.DateTime(0, 3,
new java.util.Date());
ws.addCell(Labeldt);
// 添加带有formatting的dateformat对象
DateFormat df = new DateFormat("dd mm yyyy hh:mm:ss");
WritableCellFormat wcfdf = new WritableCellFormat(df);
DateTime Labeldtf = new DateTime(1, 3, new java.util.Date(), wcfdf);
ws.addCell(Labeldtf);
// 添加图片对象,jxl只支持png格式图片
// File image = new File("f:\\2.png");
// WritableImage wimage = new WritableImage(0,1,2,2,image);
// ws.addImage(wimage);
// 写入工作表
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 拷贝后,进行修改,其中File1为被copy对象,File2为修改后创建的对象
* 尽单元格原有的格式化修饰是不能去掉的,我们还是可以将新的单元格修饰加上去, 以使单元格的内容以不同的形式表现
*
* @param File1
* @param File2
*/
public static void modifyexcel(File File1, File File2) {
try {
Workbook rwb = Workbook.getWorkbook(File1);
WritableWorkbook wwb = Workbook.createWorkbook(File2, rwb);// copy
WritableSheet ws = wwb.getSheet(0);
WritableCell wc = ws.getWritableCell(0, 0);
// 判断单元格的类型,做出相应的转换
if (wc.getType() == CellType.LABEL) {
Label Label = (Label) wc;
Label.setString("the value has been modified");
}
wwb.write();
wwb.close();
rwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试
public static void main(String[] args) {
try {
// 读excel
excel.readexcel("f:/testread.xls");
// 输出excel
File Filewrite = new File("f:/testwrite.xls");
Filewrite.createNewFile();
OutputStream os = new FileOutputStream(Filewrite);
excel.writeexcel(os);
// 修改excel
// excel.modifyexcel(new File(""),new File(""));
} catch (Exception e) {
e.printStackTrace();
}
}
}
/*
* 2.在jsp中做相关测试,创建一个writeexcel.jsp <% response.reset();//清除buffer
* response.setcontenttype("application/vnd.ms-excel"); File Filewrite = new
* File("f:/testwrite.xls"); Filewrite.createNewFile(); new
* FileOutputStream(Filewrite); excelhandle.writeexcel(new
* FileOutputStream(Filewrite)); %>
* 在ie中浏览writeexcel.jsp就可以动态生成excel文档了,其中response.setcontenttype
* ("application/vnd.ms-excel");语句必须要,才能确保不乱码, 在jsp中输入<%@page contenttype
* ="application/vnd.ms-excel;charset=gbk"%>不行。
原文 http://cuisuqiang.iteye.com/blog/759437