/**
* get the specified excel sheet and put its value string to list.
*
* @param sheetName excel sheet name
* @param ignoreRows first several rows not to read.
* @param ignoreCols first several cols not to read.
* @param readRows specified row count to read.
* @param readColumns specified column count to read.
* @throws RuntimeException
*/
public List<String> excelToList(
String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
FileInputStream fso = null;
List<String> paraList = new ArrayList<String>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
}
readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
xlRow = xlSheet.getRow(i);
readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
if (xlRow != null) {
for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
paraList.add("");
} else {
paraList.add(xlCell.toString());
}
}
}
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
/**
* put dataList to excel sheets.
*
* @param sheetName excel sheet name.
* @param dataList data list to be parsed and put into excel sheets.
* @param rowNum row count of the sheet to be modified.
* @param ignoreRows rows to skip when put value.
* @param ignoreColumns columns to skip when put value.
* @throws RuntimeException
* @throws IllegalArgumentException
*/
public void putListToExcelWithFullIgnore(
String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
if (dataList.size() % rowNum != 0) {
LOG.error("dataList has wrong element count for excel!");
throw new IllegalArgumentException("dataList has wrong element count for excel!");
}
String value = null;
int index = 0;
final int colCount = dataList.size() / rowNum;
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
xlRow = xlSheet.getRow(j);
if (xlRow == null) {
xlRow = xlSheet.createRow(j);
}
for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
value = dataList.get(index);
xlCell = xlRow.getCell(i);
if (xlCell == null) {
xlCell = xlRow.createCell(i);
}
xlCell.setCellType(1);
if (value != null) {
xlCell.setCellValue(value);
}
index++;
}
}
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
/**
* read excel Xls and add the result into arraylist.
*
* @param sheetName excel sheet name
* @throws RuntimeException
*/
public List<Map<String, String>> excelToList(String sheetName) {
Row firstxlRow = null;
FileInputStream fso = null;
List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
return null;
}
firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
int firstCell = firstxlRow.getFirstCellNum();
int lastCell = firstxlRow.getLastCellNum();
List<String> keyList = new ArrayList<String>();
for (int cNum = firstCell; cNum < lastCell; cNum++) {
if (firstxlRow.getCell(cNum).toString() == null) {
break;
}
keyList.add(firstxlRow.getCell(cNum).toString());
}
for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
xlRow = xlSheet.getRow(i);
List<String> valueList = new ArrayList<String>();
if (xlRow == null) {
break;
}
for (int j = firstCell; j < lastCell; j++) {
xlCell = xlRow.getCell(j);
if (xlCell == null) {
valueList.add(null);
continue;
} else {
valueList.add(xlCell.toString());
}
}
paraList.add(creatMap(keyList, valueList));
}
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return paraList;
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
/**
* put data into map.
*
* @param keys map key names
* @param parms map key values
* @throws RuntimeException
*/
public Map<String, String> creatMap(List<String> keys, List<String> parms) {
if (keys.size() != parms.size()) {
LOG.error("incorrect parameters, the size of list not equals!");
throw new RuntimeException("incorrect parameters, the size of list not equals!");
}
Map<String, String> paraMap = new HashMap<String, String>();
for (int i = 0; i < keys.size(); i++) {
paraMap.put(keys.get(i), parms.get(i));
}
return paraMap;
}
/**
* get excel cell value of specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @return excel cell value string
* @throws RuntimeException
*/
public String getExcelValue(String sheetName, int row, int col) {
String text = null;
FileInputStream fso = null;
try {
fso = new FileInputStream(fileName);
Workbook workBook = getWorkBook(fso, true);
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
LOG.error("sheet [" + sheetName + "] does not exist!");
throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
}
xlRow = xlSheet.getRow(row - 1);
xlCell = (xlRow == null) ? null : xlRow.getCell(col - 1);
text = (xlCell == null) ? "" : xlCell.toString();
fso.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("read excel failed:" + e.getMessage());
}
return text;
}
private Workbook getWorkBook(FileInputStream fso, Boolean readOnly) {
String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1);
try {
if (new File(fileName).exists()) {
return getWorkBookStyle(fso);
} else {
readFileOnNotExist(readOnly);
return getWorkBookStyle(fileExt);
}
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException(e);
}
}
/**
* Description: get the txt file content.
*
* @return the txt file content list.
*/
public List<String> readTXTToList() {
String line = null;
BufferedReader br = null;
List<String> List = new ArrayList<String>();
try {
br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charSet));
while (br.ready() && (line = br.readLine()) != null) {
List.add(line);
}
br.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException(e);
} finally {
System.gc();
}
return List;
}
/**
* Description: change value of the specified position.
*
* @param row the row position.
* @param col the column position.
* @param input the text value to input.
*/
public void inputTxtValue(int row, int col, String input) {
List<String> original = readTXTToList();
BufferedWriter bw = null;
StringBuffer sb = new StringBuffer();
String[] text = null;
String temp = null;
String txtMark = original.get(0).contains("\t") ? "\t" : _mark;
try {
bw =
new BufferedWriter(
new OutputStreamWriter(new FileOutputStream(fileName, false), charSet));
for (int irow = 0; irow < original.size(); irow++) {
if (irow != row - 1) {
bw.write(original.get(irow));
} else {
temp = original.get(irow).replaceAll("\\t", _mark);
text = temp.split(_mark);
for (int icol = 0; icol < text.length; icol++) {
if (icol != col - 1) {
sb.append(text[icol]);
sb.append(txtMark);
} else {
sb.append(input);
sb.append(txtMark);
}
}
bw.write(sb.toString());
}
if (irow != original.size() - 1) {
bw.newLine();
}
}
bw.flush();
bw.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException(e);
} finally {
System.gc();
}
}
/**
* add map to arraylist.
*
* @throws RuntimeException
*/
@SuppressWarnings("resource")
public List<Map<String, String>> configList() {
List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();
try {
BufferedReader br =
new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charSet));
String strKey = br.readLine();
String[] keys = strKey.replaceAll("\\t", _mark).split(_mark);
String strParm = null;
while ((strParm = br.readLine()) != null && strParm.length() > 0) {
String[] parms = strParm.replaceAll("\\t", _mark).split(_mark);
paraList.add(creatParaMap(keys, parms));
}
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("execute extern file failed:" + e.getMessage());
} finally {
System.gc();
}
return paraList;
}
/**
* Description: get the specified row and column value of the txt file.
*
* @param row the row position.
* @param col the column position.
* @return text value of the file specified position.
*/
public String getTxtValue(int row, int col) {
BufferedReader br = null;
int irow = 0;
String[][] value = new String[1024][1024];
try {
br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charSet));
String temp;
while ((temp = br.readLine()) != null) {
temp = temp.replaceAll("\\t", _mark);
value[irow] = temp.split(_mark);
irow++;
}
br.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException(e);
} finally {
System.gc();
}
return value[row - 1][col - 1];
}
private void readFileOnNotExist(Boolean readOnly) {
if (readOnly) {
LOG.error("file [" + fileName + "] does not exist!");
throw new RuntimeException("file [" + fileName + "] does not exist!");
}
}