excel學習筆記之二

excel學習筆記之二

import java.io.BufferedInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Date;

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;

public class ExcelOperate {

public static void main(String[] args) throws Exception {

// TODO Auto-generated method stub

File file = new File("E:\\data.xls");

String[][] result = getData(file, 0);

int rowLength = result.length;

for(int i = 0;i < rowLength; i++)

{

for(int j = 0; j < result[i].length; j++)

{

System.out.println(result[i][j] + "\t");

}

//System.out.println();

}

}

/**

    * 讀取Excel的內容，第一維數組存儲的是一行中格列的值，二維數組存儲的是多少個行

    * @param file 讀取數據的源Excel

    * @param ignoreRows 讀取數據忽略的行數，比喻行頭不需要讀入 忽略的行數為1

    * @return 讀出的Excel中數據的內容

    * @throws FileNotFoundException

    * @throws IOException

    */

@SuppressWarnings("deprecation")

private static String[][] getData(File file, int ignoreRows) throws FileNotFoundException, IOException 

{

List<String[]> result = new ArrayList<String[]>();

int rowSize = 0;

BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));

// 打開HSSFWorkbook

POIFSFileSystem fs = new POIFSFileSystem(in);

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFCell cell = null;

for(int sheetIndex = 0;sheetIndex < wb.getNumberOfSheets(); sheetIndex++)

{

HSSFSheet st = wb.getSheetAt(sheetIndex);

// 第一行為標題，不取

for(int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum();rowIndex++)

{

HSSFRow row = st.getRow(rowIndex);

if(row == null)

{

continue;

}

int tempRowSize = row.getLastCellNum() + 1;

if (tempRowSize > rowSize)

{

rowSize = tempRowSize;

}

String[] values = new String[rowSize];

Arrays.fill(values, "");

boolean hasValue = false;

for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++)

{

String value = " ";

cell = row.getCell(columnIndex);

if(cell != null)

{

// 注意：一定要設成這個，否則可能會出現亂碼

//www.nabble.com論壇上找的一段話，3.2已經自動Unicode處理了。

//cell.setEncoding(HSSFCell.ENCODING_UTF_16);

switch (cell.getCellType()) 

{

case HSSFCell.CELL_TYPE_STRING:

value = cell.getStringCellValue();

break;

case HSSFCell.CELL_TYPE_NUMERIC:

if (HSSFDateUtil.isCellDateFormatted(cell))

{

Date date = cell.getDateCellValue();

if(date != null)

{

value = new SimpleDateFormat("YYY-MM-dd").format(date);

}

else

{

value = "";

}

}

else {

value = new DecimalFormat("0").format(cell.getNumericCellValue());

}

break;

case HSSFCell.CELL_TYPE_FORMULA:

// 導入時如果為公式生成的數據則無值

if (!cell.getStringCellValue().equals(""))

{

value = cell.getStringCellValue();

}

else 

{

value = cell.getNumericCellValue() + "";

 }

break;

case HSSFCell.CELL_TYPE_BLANK:

break;

case HSSFCell.CELL_TYPE_ERROR:

value = "";

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

value = (cell.getBooleanCellValue() == true ? "Y" : "N");

break;

    default:

value = "";

}

}

if (columnIndex == 0 && value.trim().equals(""))

{

break;

}

values[columnIndex] = rightTrim(value);

hasValue = true;

}

if(hasValue)

{

result.add(values);

}

}

}

in.close();

String[][] returnArray = new String[result.size()][rowSize];

for(int i = 0; i < returnArray.length; i++)

{

returnArray[i] = (String[])result.get(i);

}

return returnArray;

}

/**

    * 去掉字符串右邊的空格

    * @param str 要處理的字符串

    * @return 處理后的字符串

    */

public static String rightTrim(String str) 

{

if (str == null ) {

return "";

}

int length = str.length();

for(int i = length - 1; i >= 0;i--)

{

if(str.charAt(i) != 0x20)

{

break;

}

length--;

}

return str.substring(0,length);

}

}

結果：

43057

1

第一個字符串

Y

0

N