xls&xlsx 导入、导出、解析(笔记)

/**
 * excel文件导入、导出、下载工具类
 */
public class ExcelTools extends ExcelUtils {

	private static final String CONTENT_TYPE = "application/vnd.ms-excel";

	private static final String HEADER_DIS = "Content-Disposition";

	private static final String HEADER_ATT = "attachment;filename=";

	private static final String FILE_EX = ".xls";

	POIFSFileSystem fs = null;
	
	HSSFWorkbook wb = null;
	
	private int readWhichSheet; // 读取哪一个sheet
	
	private int startReadRowNum;// 从第几行开始读取
	
	private int endReadRowNum;  // 读到第几行结束

	/**
	 * excel文件生成并下载
	 * 
	 * @param servletActionContext 上下文
	 * @param config 模板文件
	 * @param fname 导出时的文件名
	 * @throws ExcelException
	 * @throws IOException
	 */
	@SuppressWarnings("static-access")
	public static void export2Web(HttpServletResponse response, String config,
			String fname) throws ExcelException, IOException {
		response.reset();
		response.setContentType(CONTENT_TYPE);
		response.setHeader(HEADER_DIS, HEADER_ATT + fname + FILE_EX);
		export(config, response.getOutputStream());
	 
	}

	/**
	 * 将excel生成至本地
	 * 
	 * @param config  模板文件
	 * @param dir 输出的文件目录
	 * @param fname  输出的文件名
	 * @throws ExcelException
	 * @throws IOException
	 */
	@SuppressWarnings("static-access")
	public static void export2Local(String config, String dir, String fname)
			throws ExcelException, IOException {

		export(config, new FileOutputStream(dir + "/" + fname));

	}

	/**加载EXCEL文件
	 * @param excelfile  excel文件
	 * @param readWhichSheet 需要解析哪个sheet表，座标1开始
	 * @param startReadRowNum 需要从哪一行开始解析,座标1开始
	 * @param endReadRowNum   结束到哪一行，输入0则解析到末尾
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public void loadExcelFile(File excelfile, int readWhichSheet,
			int startReadRowNum, int endReadRowNum)
			throws FileNotFoundException, IOException {
		fs = new POIFSFileSystem(new FileInputStream(excelfile));
		wb = new HSSFWorkbook(fs);
		this.readWhichSheet = readWhichSheet - 1;
		this.startReadRowNum = startReadRowNum - 1;
		this.endReadRowNum = endReadRowNum - 1;
	}

	/**加载EXCEL文件
	 * @param inputStream  excel文件流
	 * @param readWhichSheet 需要解析哪个sheet表，座标1开始
	 * @param startReadRowNum 需要从哪一行开始解析,座标1开始
	 * @param endReadRowNum   结束到哪一行，输入0则解析到末尾
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public void loadExcelFile(InputStream inputStream, int readWhichSheet,
			int startReadRowNum, int endReadRowNum)
			throws FileNotFoundException, IOException {
		fs = new POIFSFileSystem(inputStream);
		wb = new HSSFWorkbook(fs);
		this.readWhichSheet = readWhichSheet;
		this.startReadRowNum = startReadRowNum;
		this.endReadRowNum = endReadRowNum;
	}

	/**
	 * 读取excel的值，以List<String[]>返回
	 * @return
	 */
	public List<String[]> readExcelData() {
		int allsheet = wb.getNumberOfSheets();
		if (readWhichSheet >= allsheet) {
			return new ArrayList<String[]>(0);
		}
		HSSFSheet sheet = wb.getSheetAt(readWhichSheet);
		if (sheet == null) {
			return new ArrayList<String[]>(0);
		}

		int rowNum = sheet.getLastRowNum() + 1; // 这里比较特殊，cell个数计算正确，row个数需要加1才对
		System.out.println("rownum:" + rowNum);

		if (endReadRowNum == -1)
			endReadRowNum = rowNum - 1;// 如果结束行等0，那么就取到末尾

		List<String[]> resultList = new ArrayList<String[]>(rowNum);
		for (int i = startReadRowNum; i <= endReadRowNum && i < rowNum; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row == null) {
				break;
			}
			short cellsnum = row.getLastCellNum();
			String[] cells = new String[cellsnum];
			for (int k = 0; k < cellsnum; k++) {
				HSSFCell cell = row.getCell((short) k);
				Object val = getCellValue(cell);
				cells[k] = val == null || "".equals(val.toString()) ? "无" : val.toString();
			}
			resultList.add(cells);
		}
		return resultList;
	}

	/**
	 * 根据Cell类型返回正确的值
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(HSSFCell cell) {

		if ((cell == null) || (HSSFCell.CELL_TYPE_BLANK == cell.getCellType())) {
			return "";
		} else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
			return cell.getBooleanCellValue();
		} else if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) {
			return cell.getCellFormula();
		} else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				return DateUtil.getDate2Str(cell.getDateCellValue(), null);
			} else {
				return cell.getNumericCellValue();
			}
		} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
			return cell.getRichStringCellValue().getString();
		} else {
			return cell.getRichStringCellValue().getString();
		}

	}

	/**
	 * 根据Cell类型返回正确的值
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellValueAsString(HSSFCell cell) {

		if (cell == null) {
			return null;
		} else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) {
			return "";
		} else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				return DateUtil.getDate2Str(cell.getDateCellValue(), null);
			} else {
				DecimalFormat df = new DecimalFormat("#00");
				return df.format(cell.getNumericCellValue());
			}
		} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
			return cell.getRichStringCellValue().getString();
		} else {
			return cell.getRichStringCellValue().getString();
		}

	}
	
	

}

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class XlsMain {

	public static void main(String[] args) throws IOException {
		XlsMain xlsMain = new XlsMain();

		xlsMain.readXls("f:\\demo.xls");
	}

	private void readXls(String filename) throws IOException {
		InputStream is = new FileInputStream(filename);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			
			if (hssfSheet == null) {
				continue;
			}

			// 循环行Row
			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				
				if (hssfRow == null) {
					continue;
				}

				// 循环列Cell
				for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
					HSSFCell hssfCell = hssfRow.getCell(cellNum);
					
					if (hssfCell == null) {
						System.out.print("    " + "null");
 						continue;
					}

					System.out.print("    " + getValue(hssfCell));
				}
				System.out.println();
			}
		}
	}

	@SuppressWarnings("static-access")
	private String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

}

import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxMain {

	public static void main(String[] args) throws IOException {
		XlsxMain xlsxMain = new XlsxMain();

		xlsxMain.readXlsx("e:/a.xlsx");
	}

	private void readXlsx(String filename) throws IOException {
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(filename);

		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet); 
			
			if (xssfSheet == null) {
				continue;
			}

			// 循环行Row
			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				
				if (xssfRow == null) {
					continue;
				}

				// 循环列Cell
				for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
					XSSFCell xssfCell = xssfRow.getCell(cellNum);
					
					if (xssfCell == null) {
						continue;
					}
					System.out.print("   " + getValue(xssfCell));
				}
				System.out.println();
			}
		}
	}

	@SuppressWarnings("static-access")
	private String getValue(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfCell.getNumericCellValue());
		} else {
			try {
				return String.valueOf(xssfCell.getStringCellValue());
			} catch (Exception e) {
				return "null";
			}

		}
	}

}

评论

2 楼 samgogo 2016-02-15  

密码是多少呀?

1 楼 ls2005nba 2015-10-08  

密码是多少呀?