java它们的定义jar套餐读Excel(这包括2003和2007)数据,和实例-阿里云开发者社区

开发者社区> 开发与运维> 正文
登录阅读全文

java它们的定义jar套餐读Excel(这包括2003和2007)数据,和实例

简介:

使用java它们的定义jar套餐读excel数据支持excel2007和excel2003

在http://download.csdn.net/detail/u010792467/8079355下载所须要的包

假设须要excel2003和excel2007文件能够去

http://download.csdn.net/detail/u010792467/8072009下载

在http://download.csdn.net/detail/u010792467/8079345下载project

自己定义jar包运用举例

<span style="font-size:18px;">package com.readExcel;

import java.util.List;

import Excel.ImportExeclTool;

public class exceltest {
	public static void main(String[] args) {
//		String filePath = "D:\\excel\\EXCEL2007測试.xlsx";
		 String filePath="D:\\excel\\EXCEL2003測试.xls";
		ImportExeclTool impexcel = new ImportExeclTool();
		// 所有sheet页内容
		// List<List<String>> list= impexcel.readExcel(filePath);
		// filePath excel文件路径
		// 1 代表第一个sheet页 2 代表第一个sheet页...
		List<List<String>> list = impexcel.readExcel(filePath, 1);
		if (list != null) {

			for (int i = 0; i < list.size(); i++) {

				System.out.print("第" + (i) + "行");

				List<String> listCell = list.get(i);

				for (String s : listCell) {
					System.out.print(" " + s);
				}
				System.out.println();

			}

		}
		System.out.println("执行完毕");
	}
}
</span>


自己定义jar包源代码

<span style="font-size:18px;">package Excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
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 ImportExeclTool {
	private String errorInfo;
	private static int readSheet = 0;
	private static boolean readSheetNum = false;
	private static boolean isGtSheetNum = false;

	public boolean validateExcel(String filePath) {

		if (filePath == null
				|| !(is2003Excel(filePath) || is2007Excel(filePath))) {

			errorInfo = "文件名称不是excel格式";

			return false;
		}

		File file = new File(filePath);

		if (file == null || !file.exists()) {

			errorInfo = "excel文件不存在";

			return false;

		}

		return true;

	}

	public List<List<String>> readExcel(String filePath) {

		List<List<String>> dataList = new ArrayList<List<String>>();

		InputStream is = null;

		try {

			if (!validateExcel(filePath)) {

				System.out.println();
				List<String> list3 = new ArrayList<String>();
				list3.add(errorInfo);
				dataList.add(list3);
				return dataList;

			}

			boolean is2003Excel = true;

			if (is2007Excel(filePath)) {

				is2003Excel = false;
			}

			File file = new File(filePath);

			is = new FileInputStream(file);

			dataList = readFile(is, is2003Excel);

			is.close();

		} catch (Exception ex) {

			ex.printStackTrace();

		} finally {

			if (is != null) {

				try {

					is.close();

				} catch (IOException e) {

					is = null;

					e.printStackTrace();

				}

			}

		}

		return dataList;

	}

	public List<List<String>> readFile(InputStream inputStream,
			boolean is2003Excel) {

		List<List<String>> dataLists = null;

		try {

			Workbook wb = null;

			if (is2003Excel) {
				wb = new HSSFWorkbook(inputStream);
			} else {
				wb = new XSSFWorkbook(inputStream);
			}
			int sheetNum = sheetCirculation(wb);
			if (readSheet >= sheetNum) {
				List<String> list2 = new ArrayList<String>();
				list2.add("输入页数大于实际sheet页数!!!");
				List list4 = new ArrayList();
				list4.add(list2);
				this.isGtSheetNum = true;
				return list4;
			}
			List<List<String>> dataList = new ArrayList<List<String>>();
			if (readSheetNum) {
				dataLists = read(dataList, wb, readSheet);
			} else {
				for (int i = 0; i < sheetNum; i++) {
					dataLists = read(dataList, wb, i);
				}
			}

		} catch (IOException e) {

			e.printStackTrace();

		}

		return dataLists;

	}

	private List<List<String>> read(List<List<String>> dataList, Workbook wb,
			int sheets) {
		int totalRows = 0;
		int totalCells = 0;

		Sheet sheet = wb.getSheetAt(sheets);

		totalRows = sheet.getPhysicalNumberOfRows();

		if (totalRows >= 1 && sheet.getRow(0) != null) {

			totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

		}
		for (int r = 0; r < totalRows; r++) {

			Row row = sheet.getRow(r);

			if (row == null) {

				continue;

			}

			List<String> rowDataList = new ArrayList<String>();

			for (int c = 0; c < totalCells; c++) {

				Cell cell = row.getCell(c);

				String cellValue = "";

				if (null != cell) {
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_NUMERIC: // 数字
						cellValue = cell.getNumericCellValue() + "";
						break;

					case HSSFCell.CELL_TYPE_STRING: // 字符串
						cellValue = cell.getStringCellValue();
						break;

					case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
						cellValue = cell.getBooleanCellValue() + "";
						break;

					case HSSFCell.CELL_TYPE_FORMULA: // 公式
						cellValue = cell.getCellFormula() + "";
						break;

					case HSSFCell.CELL_TYPE_BLANK: // 空值
						cellValue = "";
						break;

					case HSSFCell.CELL_TYPE_ERROR: // 故障
						cellValue = "非法字符";
						break;

					default:
						cellValue = "未知类型";
						break;
					}
				}

				rowDataList.add(cellValue);

			}
			dataList.add(rowDataList);

		}
		return dataList;

	}

	private int sheetCirculation(Workbook wb) {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	public static boolean is2003Excel(String filePath) {

		return filePath.matches("^.+\\.(?

i)(xls)$"); } public static boolean is2007Excel(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } public ImportExeclTool() { } public String getErrorInfo() { return errorInfo; } public List<List<String>> readExcel(String filePath, int num) { this.readSheet = num - 1; this.readSheetNum = true; if (num <= 0) { List list0 = new ArrayList(); List list5 = new ArrayList(); list0.add("excel必须大于0页!!!"); list5.add(list0); return list5; } List<List<String>> list = readExcel(filePath); if (!this.isGtSheetNum && list.size() == 0) { List<String> list1 = new ArrayList<String>(); list1.add("sheet页中数据为空!!!"); list.add(list1); } return list; } } </span>



作者:儱剑阿攵
转载请注明链接:http://blog.csdn.net/awenluck/article/details/
40442207

版权声明:本文博客原创文章,博客,未经同意,不得转载。





本文转自mfrbuaa博客园博客,原文链接:http://www.cnblogs.com/mfrbuaa/p/4678193.html,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

其他文章
最新文章
相关文章