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);
}
}