/**
* <br>Created on 2013-3-9
* <br>本类对POI API中对Excel（2003）的操作做了一些简单包装
* @author Y.Huang - 黄勇
*/
package office.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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;
/**
* <br>Created on 2013-3-9
* @author Y.Huang
*/
public class POIExcel implements Excel {
private File outFile;
private HSSFWorkbook wb;
private int defaultKeyNum = 0;
private Map<String, Integer> keyNumMap = new HashMap<String, Integer>(); ;
public static Excel createEmpytExcel(File file) {
POIExcel excel = new POIExcel();
excel.wb = new HSSFWorkbook();
excel.outFile = file;
excel.createSheet();
return excel;
}
private POIExcel() {}
public POIExcel(File f) throws Exception {
this(f, f);
}
public POIExcel(File templateFile, File destFile) throws Exception {
this(new FileInputStream(templateFile), destFile);
}
public POIExcel(InputStream is, File destFile) throws Exception {
this.wb = new HSSFWorkbook(is);
this.outFile = destFile;
}
/**
* @return
*/
public String createSheet() {
HSSFSheet sheet = this.wb.createSheet();
return sheet.getSheetName();
}
/**
* @param name
* @return
* @throws Exception
*/
public String createSheet(String name) {
HSSFSheet sheet = wb.createSheet(name);
return sheet.getSheetName();
}
/**
* @param sheetIndex
* @return
*/
public String copySheet(int sheetIndex) {
return createSheet(wb.getSheetName(sheetIndex));
}
/**
* @param sheetName
* @return
*/
public String copySheet(String sheetName) {
int sheetIndex = wb.getSheetIndex(sheetName);
wb.cloneSheet(sheetIndex);
return sheetName;
}
/**
* @param sheetIndex
* @param newSheetName
* @return
*/
public String copySheet(int sheetIndex, String newSheetName) {
return copySheet(wb.getSheetName(sheetIndex), newSheetName);
}
/**
* @param sheetName
* @param newSheetName
* @return
*/
public String copySheet(String sheetName, String newSheetName) {
int sheetIndex = wb.getSheetIndex(sheetName);
HSSFSheet newSheet = wb.cloneSheet(sheetIndex);
int newSheetIndex = wb.getSheetIndex(newSheet);
setSheetName(newSheetIndex, newSheetName);
return newSheetName;
}
/**
* @param sheetIndex
* @return
*/
public String getSheetName(int sheetIndex) {
return wb.getSheetName(sheetIndex);
}
/**
* @param sheetName
* @return
*/
public int getSheetIndex(String sheetName) {
return wb.getSheetIndex(sheetName);
}
/**
* @return
*/
public String[] getSheetNames() {
int sheetCount = getSheetCount();
String[] names = new String[sheetCount];
for (int i = 0; i < sheetCount; i++) {
names[i] = wb.getSheetName(i);
}
return names;
}
/**
* @param sheetName
* @param newName
*/
public void setSheetName(String sheetName, String newName) {
int sheetIndex = wb.getSheetIndex(sheetName);
setSheetName(sheetIndex, newName);
}
/**
* @param sheetIndex
* @param newName
*/
public void setSheetName(int sheetIndex, String newName) {
wb.setSheetName(sheetIndex, newName);
}
/**
* @return
*/
public int getSheetCount() {
return wb.getNumberOfSheets();
}
/**
* @param sheetName
*/
public void deleteSheet(String sheetName) {
int sheetIndex = wb.getSheetIndex(sheetName);
deleteSheet(sheetIndex);
}
/**
* @param sheetIndex
*/
public void deleteSheet(int sheetIndex) {
wb.removeSheetAt(sheetIndex);
}
/**
* @return
* @throws Exception
*/
public void save() throws Exception {
OutputStream out = new FileOutputStream(this.outFile);
wb.write(out);
out.close();
}
/**
* @param sheetIndex
* @return
*/
public List<String[]> getAllValuesOf(int sheetIndex) {
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
return getAllValuesOf(sheet);
}
private List<String[]> getAllValuesOf(HSSFSheet sheet) {
List<String[]> ret = new ArrayList<String[]>();
int rowCount = getRowCount(sheet);
int startRow = getStartRow(wb.getSheetIndex(sheet));
for (int i = startRow; i < rowCount; i++) {
int cellCount = getCellCount(sheet, i);
HSSFRow row = sheet.getRow(i);
ret.add(getAllValuesOf(row, cellCount));
}
return ret;
}
private String[] getAllValuesOf(HSSFRow row, int cellCount) {
String[] values = new String[cellCount];
for (int j = 0; j < cellCount; j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
values[j] = null;
continue;
}
switch (cell.getCellType()) {
case 2: // '\002'
values[j] = cell.getCellFormula().toString();
break;
case 0: // '\0'
values[j] = String.valueOf(cell.getNumericCellValue());
break;
case 1: // '\001'
values[j] = cell.getStringCellValue();
break;
}
}
return values;
}
private int getRowCount(HSSFSheet sheet) {
if (sheet == null) {
return 0;
}
int lastRowNum = sheet.getLastRowNum();
return lastRowNum + 1;
}
private int getCellCount(HSSFSheet sheet, int rowIndex) {
int sheetIndex = wb.getSheetIndex(sheet);
int keyNum = getKeyNum(sheetIndex);
HSSFRow row = sheet.getRow(rowIndex);
if (keyNum >= 0) {
HSSFRow tmpRow = sheet.getRow(keyNum);
if (tmpRow != null) {
row = tmpRow;
}
}
if (row == null) {
return 0;
}
int lastCellNum = row.getLastCellNum();
return lastCellNum;
}
/**
* @param sheetName
* @return
*/
public List<String[]> getAllValuesOf(String sheetName) {
HSSFSheet sheet = wb.getSheet(sheetName);
return getAllValuesOf(sheet);
}
/**
* @param sheetName
* @return
*/
public String[] getFirstRowValuesOf(String sheetName) {
HSSFSheet sheet = wb.getSheet(sheetName);
HSSFRow row = sheet.getRow(0);
int cellCount = getCellCount(sheet, 0);
return getAllValuesOf(row, cellCount);
}
/**
* @param sheetIndex
* @return
*/
public String[] getFirstRowValuesOf(int sheetIndex) {
String sheetName = wb.getSheetName(sheetIndex);
return getFirstRowValuesOf(sheetName);
}
/**
* @param sheetName
* @param rowIndex
* @return
*/
public String[] getRowValuesOf(String sheetName, int rowIndex) {
HSSFSheet sheet = wb.getSheet(sheetName);
HSSFRow row = sheet.getRow(rowIndex);
int cellCount = getCellCount(sheet, rowIndex);
return getAllValuesOf(row, cellCount);
}
/**
* @param sheetIndex
* @param rowIndex
* @return
*/
public String[] getRowValuesOf(int sheetIndex, int rowIndex) {
return getRowValuesOf(wb.getSheetName(sheetIndex), rowIndex);
}
/**
* @param sheetIndex
* @param column
* @ret
评论0
最新资源