Java Excel Read

1.pom

  		<!-- POI -(2003~2007)Excel-Support -->
  		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.10-FINAL</version>
		</dependency>
		<!-- POI -(2007+)Excel-Support -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.10-FINAL</version>
		</dependency>

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">2.colNo <--> char</span>

/**
	 * excel列名： 取数字对应的字母
	 * 
	 * @param index
	 * @return
	 */
	public static String getColCharByNo(int index) {
		String rs = "";
		index = index + 1 ;
		do {
			index--;
			rs = ((char) (index % 26 + (int) 'A')) + rs;
			index = (int) ((index - index % 26) / 26);
		} while (index > 0);
		return rs;
	}

	/**
	 * excel列名： 取字母对应的colNo
	 * @param col
	 * @return
	 */
	public static int getColNoByChar(String col) {
		col = col.toUpperCase();
		// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
		int count = -1;
		char[] cs = col.toCharArray();
		for (int i = 0; i < cs.length; i++) {
			count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
		}
		return count;
	}

3. create txt

package com.baidu.wanba.job.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.math.BigDecimal;

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 ExcelUtils {
	/**
	 * 过滤指定列并输出到目标文件中
	 * @param srcFileName
	 * @param destFileName
	 * @param sheetNo
	 * @param ignoreHeader
	 * @param destColChars
	 */
	public static void createTxtByExcelFilter(String srcFileName, String destFileName,	int sheetNo, boolean ignoreHeader, String... destColChars) {
		if(destColChars == null){
			return ;
		}
		int[] destColNos = new int[destColChars.length] ;
		int pos = 0 ;
		for(String destColChar : destColChars){
			destColNos[pos] = getColNoByChar(destColChar) ;
			pos++ ;
		}
		createTxtByExcelFilter(srcFileName,destFileName,sheetNo,ignoreHeader,destColNos);
	}
	
	/**
	 * 过滤指定列并输出到目标文件中
	 * @param srcFileName
	 * @param destFileName
	 * @param sheetNo
	 * @param ignoreHeader
	 * @param destColNos
	 */
	public static void createTxtByExcelFilter(String srcFileName, String destFileName,	int sheetNo, boolean ignoreHeader, int... destColNos) {
		Workbook srcFile = null;
		File destFile = null ;
		FileWriter writer = null;
		try {
			srcFile = new XSSFWorkbook(new FileInputStream(srcFileName));
			//sheet
			Sheet sheet = srcFile.getSheetAt(sheetNo);
			int rowNo = 0 ;
			if(ignoreHeader){
				rowNo = 1 ;
			}
			//rm-exist
			destFile = new File(destFileName) ;
			if(null!=destFile && destFile.exists()){
				destFile.delete();
			}
			writer = new FileWriter(destFile);
			//write row
			for ( ; rowNo < sheet.getLastRowNum(); rowNo++) {
				String  rowContent = createRowContent(sheet,rowNo,destColNos);
				if(null == rowContent){
					continue ;
				}
				writer.write(rowContent.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
			//rm dest-file
			if(null!=destFile){
				if(destFile.exists()){
					destFile.delete() ;
				}
			}
		} finally {
			if (null != writer) {
				try {
					writer.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	private static String createRowContent(Sheet sheet,int rowNo,int... destColNos){
		Row row = sheet.getRow(rowNo) ;
		if(null  == row ){
			return null ;
		}
		StringBuffer rowContent = new StringBuffer();
		//target-column
		for (int destColNo : destColNos) {
			Cell cell = row.getCell(destColNo) ;
			if(null!=cell){
				String cellValue = null ;
				switch(cell.getCellType()){
				case Cell.CELL_TYPE_NUMERIC: cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString() ; break;  
				case Cell.CELL_TYPE_STRING:  cellValue = cell.getStringCellValue();        break;  
				case Cell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue() + "";  break;  
				case Cell.CELL_TYPE_FORMULA: cellValue = cell.getCellFormula() + "";  break;  
				case Cell.CELL_TYPE_BLANK:  cellValue = "";   break;  
				case Cell.CELL_TYPE_ERROR: throw new RuntimeException(String.format("cell-content ERROR at[%s,%s]",rowNo,destColNo));  
				default:  cellValue = "未知类型"; break;  
				}
				rowContent.append(cellValue);
				rowContent.append("\t");
			}else{
				throw new RuntimeException(String.format("target-colum not exist at[%s]",rowNo));
			}
		}
		rowContent.append("\r\n");
		return rowContent.toString() ;
	}
	
	/**
	 * excel列名： 取数字对应的字母
	 * 
	 * @param index
	 * @return
	 */
	public static String getColCharByNo(int index) {
		String rs = "";
		index = index + 1 ;
		do {
			index--;
			rs = ((char) (index % 26 + (int) 'A')) + rs;
			index = (int) ((index - index % 26) / 26);
		} while (index > 0);
		return rs;
	}

	/**
	 * excel列名： 取字母对应的colNo
	 * @param col
	 * @return
	 */
	public static int getColNoByChar(String col) {
		col = col.toUpperCase();
		// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
		int count = -1;
		char[] cs = col.toCharArray();
		for (int i = 0; i < cs.length; i++) {
			count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
		}
		return count;
	}
	
	public static void main(String[] args){
		/* create-file */
		String srcFileName = "D:\\datas\\sms-datas\\history\\短信召回发送2014-5-30.xlsx" ;
		String destFileName = "D:\\datas\\sms-datas\\send.txt";
		boolean ignoreHeader = true ;
		int sheetNo = 0 ;
//		int[] destColNos = {6,12} ;
//		createTxtByExcelFilter(srcFileName, destFileName, sheetNo , ignoreHeader, destColNos);
		String[] destColChars = {"G","M"};
		createTxtByExcelFilter(srcFileName, destFileName, sheetNo, ignoreHeader, destColChars);
		
		/* colNo <--> char */
//		int colNo = 26;
//		String letters = getColCharByNo(colNo) ;
//		System.out.println(letters);
//		int tColNo = getColNoByChar(letters) ;
//		System.out.println(tColNo);
	}
}