首页 > 学院 > 开发设计 > 正文

java poi 导入excel

2019-11-15 00:30:50
字体:
来源:转载
供稿:网友
java poi 导入Excel

最近项目需要导入excel,网上有很多例子,自己整合记录下,兼容2003和2007,暂时没有添加图片处理功能。

所需jar包 http://pan.baidu.com/s/1sjPuWDR

package example.poi;import java.io.*;import java.text.DecimalFormat;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.*;public class ImportExcel {   PRivate Workbook wb = null; private Sheet sheet = null; private Row row = null; private int sheetNum = 0; private int rowNum = 0; private FileInputStream fis = null; private File file = null; private DecimalFormat df = new DecimalFormat("0"); public ImportExcel() {  super(); } public void setSheetNum(int sheetNum) {  this.sheetNum = sheetNum; } public void setRowNum(int rowNum) {  this.rowNum = rowNum; } public void setFile(File file) {  this.file = file; } /**  * 读取excel文件获得HSSFWorkbook对象  * @throws IOException  */ public void open(String filePath) throws IOException {  if(validateExcel(filePath)) {   file = new File(filePath);   fis = new FileInputStream(file);   if(isExcel2003(filePath)) {    wb = new HSSFWorkbook(fis);   } else {    wb = new XSSFWorkbook(fis);   }   fis.close();  } }   /**  * 获取sheet表数目  * @return sheet表数目  */ public int getSheetCount() {  int sheetCount = -1;  sheetCount = wb.getNumberOfSheets();  return sheetCount; }   /**  * 获取sheetNum下的记录行数  * @return 记录行数  */ public int getRowCount() {  if(wb == null) {   System.err.println("----------->WorkBook为空");  }  Sheet sheet = wb.getSheetAt(this.sheetNum);  int rowCount = -1;  rowCount = sheet.getLastRowNum();  return rowCount; }   /**  * 获取指定sheetNum的记录行数  * @param sheetNum 表编号  * @return 记录行数  */ public int getRowCount(int sheetNum) {  Sheet sheet = wb.getSheetAt(sheetNum);  int rowCount = -1;  rowCount = sheet.getLastRowNum();  return rowCount; }   /**  * 得到指定行的内容  * @param lineNum 行数  * @return 内容  */ public String[] readExcelLine(int lineNum) {  return readExcelLine(this.sheetNum, lineNum); }   /**  * 获取指定工作表和行数的内容  * @param sheetNum 表编号  * @param lineNum 行数  * @return 内容  */ public String[] readExcelLine(int sheetNum, int lineNum) {  if(sheetNum < 0 || lineNum < 0) {   return null;  }  String[] strExcelLine = null;  try {   sheet = wb.getSheetAt(sheetNum);   row = sheet.getRow(lineNum);       int cellCount = row.getLastCellNum();   strExcelLine = new String[cellCount + 1];   for(int i = 0; i <= cellCount; i++) {    strExcelLine[i] = readStringExcelCell(lineNum, i);   }  } catch (Exception e) {   e.printStackTrace();  }  return strExcelLine; }   /**  * 获取指定列的内容  * @param cellNum 列编号  * @return 内容  */ public String readStringExcelCell(int cellNum) {  return readStringExcelCell(this.rowNum, cellNum); }   /**  * 获取指定行和列编号的内容  * @param rowNum 行编号  * @param cellNum 列编号  * @return 内容  */ public String readStringExcelCell(int rowNum, int cellNum) {  return readStringExcelCell(this.sheetNum, rowNum, cellNum); }   /**  * 获取指定工作表、行、列的内容  * @param sheetNum 表编号  * @param rowNum 行编号  * @param cellNum 列编号  * @return 内容  */ public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {  if(sheetNum < 0 || rowNum < 0) {   return "";  }  String strExcelCell = "";  try {   sheet = wb.getSheetAt(sheetNum);   row = sheet.getRow(rowNum);       if(row.getCell(cellNum) != null) {    switch(row.getCell(cellNum).getCellType()) {    case HSSFCell.CELL_TYPE_FORMULA:     strExcelCell = "FORMULA";     break;    case HSSFCell.CELL_TYPE_NUMERIC:     //strExcelCell = String.valueOf(row.getCell(cellNum).getNumericCellValue());     //防止科学计数,不需要的话可以用上一行     strExcelCell = decimalFormat(row.getCell(cellNum).getNumericCellValue());     break;    case HSSFCell.CELL_TYPE_STRING:     strExcelCell = row.getCell(cellNum).getStringCellValue();     break;    case HSSFCell.CELL_TYPE_BLANK:     strExcelCell = "";     break;    default:     strExcelCell = "";     break;    }   }  } catch (Exception e) {   e.printStackTrace();  }  return strExcelCell; }   /**  * 测试  * @param args  */ public static void main(String[] args) {  String filePath = "C://Users//Administrator//Desktop//工作簿1.xlsx";  ImportExcel ie = new ImportExcel();  try {   ie.open(filePath);  } catch (IOException e) {   e.printStackTrace();  }  ie.setSheetNum(0);  int count = ie.getRowCount();  for (int i = 0; i <= count; i++) {   String[] rows = ie.readExcelLine(i);   for (int j = 0; j < rows.length; j++) {    System.out.print(rows[j] + " ");   }   System.out.print("/n");  } }   /**  * 验证文件是否存在以及是否是excel文件  * @param filePath  * @return 结果  */ public boolean validateExcel(String filePath) {  if(filePath == null || ! (isExcel2003(filePath) || isExcel2007(filePath))) {   System.err.println("文件不是excel文件!");   return false;  }  File file = new File(filePath);  if(file == null || ! file.exists()) {   System.err.println("文件不存在!");   return false;  }  return true; }   /**  * 判断是否是2003版excel  * @param filePath 文件路径  * @return 结果  */ public boolean isExcel2003(String filePath) {  return filePath.matches("^.+//.(?i)(xls)$"); }   /**  * 判断是否是2007版excel  * @param filePath 文件路径  * @return 结果  */ public boolean isExcel2007(String filePath) {  return filePath.matches("^.+//.(?i)(xlsx)$"); }   /**  * 将获取到的数据类型转换成String防止科学计数法  * @param decimal 数据  * @return 结果  */ public String decimalFormat(Double decimal) {  return df.format(decimal); }}

内容借鉴:

http://download.csdn.net/detail/wangquanzheng/3526296

http://blog.csdn.net/mmm333zzz/article/details/7962377


发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表