引入poi的jar包,大致如下:
读取代码如下,应该能看得明白吧
importjava.io.FileInputStream;
importjava.io.FileNotFoundException;
importjava.io.IOException;
importjava.io.InputStream;
importjava.math.BigDecimal;
importjava.text.DecimalFormat;
importjava.text.SimpleDateFormat;
importorg.apache.poi.xssf.usermodel.XSSFCell;
importorg.apache.poi.xssf.usermodel.XSSFRow;
importorg.apache.poi.xssf.usermodel.XSSFSheet;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
publicclassExcelUtil2007{
/**读取excel文件流的指定索引的sheet
*@paraminputStreamexcel文件流
*@paramsheetIndex要读取的sheet的索引
*@return
*@throwsFileNotFoundException
*@throwsIOException
*/
publicstaticXSSFSheetreadExcel(InputStreaminputStream,intsheetIndex)throwsFileNotFoundException,IOException
{
returnreadExcel(inputStream).getSheetAt(sheetIndex);
}
/**读取excel文件的指定索引的sheet
*@paramfilePathexcel文件路径
*@paramsheetIndex要读取的sheet的索引
*@return
*@throwsIOException
*@throwsFileNotFoundException
*/
publicstaticXSSFSheetreadExcel(StringfilePath,intsheetIndex)throwsFileNotFoundException,IOException
{
returnreadExcel(filePath).getSheetAt(sheetIndex);
}
/**读取excel文件的指定索引的sheet
*@paramfilePathexcel文件路径
*@paramsheetName要读取的sheet的名称
*@return
*@throwsIOException
*@throwsFileNotFoundException
*/
publicstaticXSSFSheetreadExcel(StringfilePath,StringsheetName)throwsFileNotFoundException,IOException
{
returnreadExcel(filePath).getSheet(sheetName);
}
/**读取excel文件,返回XSSFWorkbook对象
*@paramfilePathexcel文件路径
*@return
*@throwsFileNotFoundException
*@throwsIOException
*/
publicstaticXSSFWorkbookreadExcel(StringfilePath)throwsFileNotFoundException,IOException
{
XSSFWorkbookwb=newXSSFWorkbook(newFileInputStream(filePath));
returnwb;
}
/**读取excel文件流,返回XSSFWorkbook对象
*@paraminputStreamexcel文件流
*@return
*@throwsFileNotFoundException
*@throwsIOException
*/
publicstaticXSSFWorkbookreadExcel(InputStreaminputStream)throwsFileNotFoundException,IOException
{
XSSFWorkbookwb=newXSSFWorkbook(inputStream);
returnwb;
}
/***读取excel中指定的单元格,并返回字符串形式的值
*1.数字
*2.字符
*3.公式(返回的为公式内容,非单元格的值)
*4.空
*@paramst要读取的sheet对象
*@paramrowIndex行索引
*@paramcolIndex列索引
*@paramisDate是否要取的是日期(是则返回yyyy-MM-dd格式的字符串)
*@return
*/
publicstaticStringgetCellString(XSSFSheetst,introwIndex,intcolIndex,booleanisDate){
Strings="";
XSSFRowrow=st.getRow(rowIndex);
if(row==null)return"";
XSSFCellcell=row.getCell(colIndex);
if(cell==null)return"";
if(cell.getCellType()==0){//数字
if(isDate)s=newSimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
elses=trimPointO(String.valueOf(getStringValue(cell)).trim());
}elseif(cell.getCellType()==1){//字符(excel中的空格,不是全角,也不是半角,不知道是神马,反正就是""这个)
s=cell.getRichStringCellValue().getString().replaceAll("","").trim();
//s=cell.getStringCellValue();//07API新增,好像跟上一句一致
}
elseif(cell.getCellType()==2){//公式
s=cell.getCellFormula();
}
elseif(cell.getCellType()==3){//空
s="";
}
returns;
}
/**如果数字以.0结尾,则去掉.0
*@params
*@return
*/
publicstaticStringtrimPointO(Strings){
if(s.endsWith(".0"))
returns.substring(0,s.length()-2);
else
returns;
}
/**处理科学计数法和百分比模式的数字单元格
*@paramcell
*@return
*/
publicstaticStringgetStringValue(XSSFCellcell){
StringsValue=null;
shortdataFormat=cell.getCellStyle().getDataFormat();
doubled=cell.getNumericCellValue();
BigDecimalb=newBigDecimal(Double.toString(d));
//百分比样式的
if(dataFormat==0xa||dataFormat==9){
b=b.multiply(newBigDecimal(100));
//Stringtemp=b.toPlainString();
DecimalFormatdf=newDecimalFormat("0.00");//保留两位小数的百分比格式
sValue=df.format(b)+"%";
}else{
sValue=b.toPlainString();
}
returnsValue;
}
}