POI是APACHE出品的一个OFFICE文档操作类库,下面对读取HSSF的一个基本试用.
package busi.imp.thread;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.ss.usermodel.Cell;
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;
public class ReadWriteExcelUtil{
/**
* @param args
*/
public static void main(String[] args) {
String fileName = "d:" + File.separator + "test.xls";
System.out.println(ReadWriteExcelUtil.readExcel(fileName));
String fileName1 = "d:" + File.separator + "abc.xlsx";
ReadWriteExcelUtil.writeExcel(fileName1);
}
/**
* 從excel文件中读取所有的內容
*
* @param file
* excel文件
* @return excel文件的內容
*/
private static String readExcel(String fileName1) {
StringBuffer sb = new StringBuffer();
File f = new File(fileName1);
Workbook wb = null;
//兼容2003和2007
//根据版本创建Workbook的方式
if (f.exists()) {
try {
wb = new XSSFWorkbook(new FileInputStream(f));
} catch (Exception e) {
try {
wb = new HSSFWorkbook(new FileInputStream(f));
} catch (Exception e1) {
System.out.println("上传文件格式错误!");
e1.printStackTrace();
}
}
} else {
System.out.println("导入文件不存在,请重新上传文件!");
}
// 获得了Workbook对象之后,就可以通过它得到Sheet(工作表)的数目
int sheetCount = wb.getNumberOfSheets();
// 对每个工作表进行循环
for (int i = 0; i < sheetCount; i++) {
//得到当前工作表
Sheet sheet = wb.getSheetAt(i);
// 得到当前工作表的行数
int rowNum = sheet.getLastRowNum();
// 得到当前工作表第一行的列数
int totalCells = sheet.getRow(0).getLastCellNum();
//循环行
for (int r = 0; r < rowNum; r++) {
//得到当前行
Row row = sheet.getRow(r);
//循环列
for (int c = 0; c < totalCells; c++){
//得到当前单元格
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell){
cellValue = getCellContent(cell);
sb.append(cellValue + "\t");
}
}
sb.append("\r\n");
}
sb.append("\r\n");
}
// 最后关闭资源,释放内存
return sb.toString();
}
private static String getCellContent(Cell cell) {
String value = "";
if ((cell.getCellType() != HSSFCell.CELL_TYPE_STRING)
&& HSSFDateUtil.isCellDateFormatted(cell)) {//时间类型
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
value = simpleDateFormat.format(cell.getDateCellValue());
} else {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN://boolean
value = new Boolean(cell.getBooleanCellValue()).toString();
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字
value = new Double(cell.getNumericCellValue()).toString();
break;
case HSSFCell.CELL_TYPE_FORMULA://公式
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_STRING://字符串
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK://空值
value = "";
default:
value = "未知类型";
break;
}
}
return value;
}
/**
* 把內容寫入excel文件中
*
* @param fileName
* 要寫入的文件的名稱
*/
public static void writeExcel(String fileName) {
//工作簿
HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
//创建sheet页
HSSFSheet sheet = wb.createSheet("new sheet");//建立新的sheet对象
HSSFRow row = sheet.createRow((short)0);//建立新行
HSSFCell cell = row.createCell((short)0);//建立新cell
cell.setCellValue(1);//设置cell的整数类型的值
row.createCell((short)1).setCellValue(1.2);//设置cell浮点类型的值
row.createCell((short)2).setCellValue("test");//设置cell字符类型的值
row.createCell((short)3).setCellValue(true);//设置cell布尔类型的值
HSSFCellStyle cellStyle = wb.createCellStyle();//建立新的cell样式
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));;//设置cell样式为定制的日期格式
HSSFCell dCell =row.createCell((short)4);
dCell.setCellValue(new Date());//设置cell为日期类型的值
dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式
HSSFCell csCell =row.createCell((short)5);
csCell.setCellType(HSSFCell.ENCODING_UTF_16);//设置cell编码解决中文高位字节截断
csCell.setCellValue("中文测试_Chinese Words Test");//设置中西文结合字符串
row.createCell((short)6).setCellType(HSSFCell.CELL_TYPE_ERROR);//建立错误cell
//输出
FileOutputStream fileoutputstream;
try {
fileoutputstream = new FileOutputStream(fileName);
wb.write(fileoutputstream);
fileoutputstream.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}