excel學(xué)習(xí)筆記之二

excel學(xué)習(xí)筆記之二

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的內(nèi)容，第一維數(shù)組存儲的是一行中格列的值，二維數(shù)組存儲的是多少個行

    * @param file 讀取數(shù)據(jù)的源Excel

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

    * @return 讀出的Excel中數(shù)據(jù)的內(nèi)容

    * @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);

// 第一行為標(biāo)題，不取

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)

{

// 注意：一定要設(shè)成這個，否則可能會出現(xiàn)亂碼

//www.nabble.com論壇上找的一段話，3.2已經(jīng)自動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:

// 導(dǎo)入時如果為公式生成的數(shù)據(jù)則無值

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

}

}

結(jié)果：

43057

1

第一個字符串

Y

0

N