POI读取excelITeye - 众发娱乐

POI读取excelITeye

2019-01-14 05:07:36 | 作者: 天风 | 标签: 读取,内容,单元格 | 浏览: 2988

项目中要求读取excel文件内容,并将其转化为xml格局。常见读取excel文档一般运用POI和JExcelAPI这两个东西。这儿咱们介绍运用POI完成读取excel文档。

2.代码实例
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 Map Integer, String readExcelContent(InputStream is) {
 Map Integer, String content = new HashMap Integer, 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");
 Map Integer, 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();

 

由于excel单元格中的内容往往都有必定的格局,比方日期型,数字型,字符串型,因此在读取的时分要进行格局判别,否则会呈现过错。常见的就是不能正常读取日期。在代码实例中有一个办法:

getCellFormatValue(HSSFCell cell)

往这个办法中传入excel单元格就能辨认单元格格局,并转化为正确的格局。

ps:2012-2-23

代码实例中有一段代码:

int colNum = row.getPhysicalNumberOfCells();

 

其间的HSSFRow.getPhysicalNumberOfCells();这个办法是用于获取一行中存在的单元格数,POI的官方API中有给出getPhysicalNumberOfCells办法的解说

getPhysicalNumberOfCells
public int getPhysicalNumberOfCells()
gets the number of defined cells (NOT number of cells in the actual row!). That is to say if only columns 0,4,5 have values then there would be 3.
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表众发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章