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(""))

 else 

value = cell.getNumericCellValue() + "";

  }

 case HSSFCell.CELL_TYPE_BLANK:

 case HSSFCell.CELL_TYPE_ERROR:

 case HSSFCell.CELL_TYPE_BOOLEAN:

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

     default:

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)

 length--;

 return str.substring(0,length);

結果：

43057

1

第一個字元串

Y

N

本文轉自 知止内明 51CTO部落格，原文連結：http://blog.51cto.com/357712148/1982902，如需轉載請自行聯系原作者