Gavin's Blog

package name.laigw.utils.excel;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 
/**
* Handle excel(read and write) class by POI
*
* @author Gavin
*/
public class ExcelHandleByPOI {
 
public static void main(String[] args) {
 
String filePath = "input/test_from.xls";
String filePathTo = "input/test_to.xls";
 
List listList = ExcelHandleByPOI.readFromExcel(filePath, true);
System.out.println("Data write to: " + ExcelHandleByPOI.writeToExcel(listList, filePathTo));
 
//~ display the table content for demo
Iterator iterList = listList.iterator();
for(int h=0; iterList.hasNext(); ++h) { // get each 'list', indicating each table
 
List listStr = (ArrayList)iterList.next();
Iterator iterStrArray = listStr.iterator();
 
System.out.println("Table[" + h + "]: ");
System.out.print("\t");
 
int r = 0;
for(; iterStrArray.hasNext(); ++r) { // get each String array, indicating each record of a table
 
final String[] strArray = (String[])iterStrArray.next();
for(int i = 0; i < strArray.length; ++i) {
if (i == 0) {
System.out.print(strArray[i]);
} else {
System.out.print(", " + strArray[i]);
}
}
System.out.println();
}
}
}
 
/**
* write data storing in list to excel corresponding to <i>filePath</i>
*
* @param list: structure: <i>list</i> has some sub list,
* <br>each sub list corresponding to a sheet of excel.
* <br>And each sub list has some String Array data,
* <br>each String Array corresponding to a record of a sheet.
* @param filePath
* @return filePath
*/
public static String writeToExcel(final List list, String filePath) {
 
FileOutputStream fileOut = null;
 
// create a new work book
HSSFWorkbook wb = new HSSFWorkbook();
 
HSSFSheet sheet = null;
HSSFRow row = null;
 
Iterator iterList = list.iterator();
for(int h=0; iterList.hasNext(); ++h) { // get each 'list', indicating each table
 
sheet = wb.createSheet("Sheet " + (h+1));
List listStr = (ArrayList)iterList.next();
Iterator iterStrArray = listStr.iterator();
 
for(int r = 0; iterStrArray.hasNext(); ++r) { // get each String array, indicating each record of a table
 
row = sheet.createRow(r);
String[] strArr = (String[])iterStrArray.next();
 
for(int i=0; i < strArr.length; ++i) {
 
row.createCell((short)i).setCellValue(strArr[i]);
}
}
}
 
try
{
SupportUtil.mkdir(SupportUtil.extractFileDir(filePath));
fileOut = new FileOutputStream(filePath);
 
// write data to the new create file
wb.write(fileOut);
} catch (FileNotFoundException ex) {
System.out.println("----------Error: " + ex);
ex.printStackTrace();
} catch (IOException ex) {
System.out.println("----------Error: " + ex);
ex.printStackTrace();
} finally {
try {
//close the file
fileOut.close();
} catch(IOException ex) {
System.out.println("----------Error: " + ex);
ex.printStackTrace();
}
}
 
return filePath;
}
 
/**
* Read data into list from excel corresponding to filePath.
*
* @param filePath, absolute path(As "E:/input/abc.xls"),
* <br>or relative path(As "input/abc.xls"), ralating to project root.
* @param hasHead, tag for excel sheet format, indicating whether having table head.
* @return List, storing data of the excel.
* <br>list structure:
* <br><i>list</i> has some sub list,
* <br>each sub list corresponding to a sheet of excel.
* <br>And each sub list has some String Array data,
* <br>each String Array corresponding to a record of a sheet.
*/
public static List readFromExcel(String filePath, boolean hasHead) {
 
List retList = new ArrayList();
FileInputStream filein = null;
 
try
{
filein = new FileInputStream(filePath); // read data from file appointed by filePath
POIFSFileSystem fs = new POIFSFileSystem(filein); // get POIFS file stream
HSSFWorkbook wb = new HSSFWorkbook(fs); // workbook
 
for(short h=0; h < wb.getNumberOfSheets(); ++h)
{
List list = new ArrayList();
 
final HSSFSheet sheet = wb.getSheetAt(h);
int rowcount = sheet.getLastRowNum();
rowcount++;
System.out.print("-----sheet[" + h + "]: row count = " + rowcount);
 
int colcount = 0;
for(int i=0; i < rowcount; ++i)
{
final HSSFRow row = sheet.getRow(i); // i=0 indicate the first row
if (row == null) continue; // without the row, break and continue;
 
if (colcount==0) { // colunm count set to column of the first effective row
colcount = row.getLastCellNum();
System.out.println(", column count = " + colcount);
}
 
if (hasHead && i==0) { // if the sheet has "head", then ingore the head
continue;
}
 
String[] fieldValue = new String[colcount];
for(short j=0; j < colcount; ++j) { // column data in the current
 
final HSSFCell cell = row.getCell(j);
fieldValue[j] = getCellStringValue(cell);
}
 
list.add(fieldValue);
}
 
retList.add(list);
}
 
} catch(Exception ex){
System.out.println("----------Error: " + ex.toString());
ex.printStackTrace();
} finally {
try{
if(filein != null){
filein.close();
filein = null;
}
}catch(IOException ioe){
System.out.println("----------Error: " + ioe.toString());
}
}
 
return retList;
}
 
/**
* get a <i>cell</i>'s value as String.
*
* @param cell
* @return
*/
public static String getCellStringValue(HSSFCell cell) {
 
String cellValue = "";
if(cell == null) return cellValue;
 
switch(cell.getCellType()) {
 
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue().trim();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
 
if ( HSSFDateUtil.isCellDateFormatted(cell) ) {
cellValue = SupportUtil.datetimeFormat(cell.getDateCellValue());
} else if (SupportUtil.isInteger(cell.getNumericCellValue())) {
cellValue = String.valueOf((int)cell.getNumericCellValue());
} else {
cellValue = String.valueOf(cell.getNumericCellValue());
}
 
break;
case HSSFCell.CELL_TYPE_FORMULA:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
default:
cellValue = "";
}
 
return cellValue;
}
 
}

package name.laigw.utils.excel;
 
import java.io.File;
import java.text.SimpleDateFormat;
 
public class SupportUtil {
/**
* <b>set to world datetime(yyyy-MM-dd HH:mm:ss.SSS)</b>
* @param date
* @return
*/
public static String datetimeFormat(java.util.Date date) {
 
SimpleDateFormat pdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
String dateStr = "";
try {
if (date != null) {
dateStr = pdf.format(date);
}
}catch (Exception e){
e.printStackTrace();
}
return dateStr;
}
 
/**
* extract file' directory,
* <br>filepath="input/test_from.xls"
* <br>return="input"
* @param filepath
* @return file directory
*/
public static String extractFileDir(String filepath) {
 
return new File(filepath).getParent();
}
 
/**
* mkdir
* @param pathname
* @return:<br>true: mkdir successful; <br>false: mkdir fail
*/
public static boolean mkdir(String filedir) {
return new File(filedir).mkdirs();
}
 
/**
* judge the input val whether can be a integer val without value changing.
* <br>like 15.00 -> 15: true; 15.35 -> 15: false;
* @param val
* @return
*/
public static boolean isInteger(double val){
double exp = 0.0000000000000001;
return (Math.abs(val - (int)val)) < exp;
}
}