POI读取excel

package com.excel;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
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.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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;

import com.dao.ExcelDAO;

public class ReadExcel {

public static void main(String[] args) {

String path = "D://B2013.10.30欠费名单.xls";// 文件路径
try {
File files = new File(path);
List<Map<String,Object>> information= new ArrayList<Map<String, Object>>();

List<String[]> list = getDataList(files); // 读取
if (list != null && list.size() > 0) {
for (String[] str : list) {

Map<String,Object> record=new HashMap<String,Object>();

for (int i = 0; i < str.length; i++) {
switch(i)
{
case 0: record.put("grade", str[i]);
case 1: record.put("name", str[i]);
case 2: record.put("number", str[i]);
case 3: record.put("year", str[i]);
}
}

information.add(record);
}
int flag = new ExcelDAO().add(information);
}

} catch (Exception e) {
e.printStackTrace();
}
}

/**
* 读取EXCEL
*
* @param firstrow
* 从第几行开始读取
* @return 读取后返回数组
*/
@SuppressWarnings("deprecation")
public static String[][] getData(File file, int firstrow)
throws FileNotFoundException, IOException {
List<String[]> result = new ArrayList<String[]>();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
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 = firstrow; 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 (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
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("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0.00").format(cell
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
value = "";
break;
// 导入时如果为空
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
// 导入时如果为BOOLEAN型 自定义格式输出
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}

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;
}
return null;

}

public static List<String[]> getDataList(File file)
throws FileNotFoundException, IOException {
List<String[]> result = new ArrayList<String[]>();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
// 建立excell的文档对象

HSSFCell cell = null; // HSSFCell excell的格子单元
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet st = wb.getSheetAt(sheetIndex);
// HSSFSheet excell的表单
for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {
HSSFRow row = st.getRow(rowIndex);
// HSSFRow excell的行

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;
//columnIndex为行的每个单元格
for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);//获得指定单元格的数据
if (cell != null) {
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("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
// 如果格式为数值，自定义格式输出
value = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
value = "";
break;
// 导入时如果为空
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
// 导入时如果为BOOLEAN型 自定义格式输出
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}

values[columnIndex] = rightTrim(value);
hasValue = true;
}

if (hasValue) {
result.add(values);
}
}

in.close();
return result;
}
return null;

}

/**
* 去掉字符串右边的空格
*
* @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);
}

/**
* @作者：
* @时间：
* @说明：poi导出Excel
* @使用地方：
* @param list
* @throws IOException
*/
public static void outExcel(List<String[]> list) throws IOException {

// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("表一");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制int
HSSFRow row = sheet.createRow((int) 0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

HSSFCell cell = row.createCell((int) 0);
cell.setCellValue("一");
cell.setCellStyle(style);
cell = row.createCell((int) 1);
cell.setCellValue("二");
cell.setCellStyle(style);
cell = row.createCell((int) 2);
cell.setCellValue("三");
cell.setCellStyle(style);
cell = row.createCell((int) 3);
cell.setCellValue("四");
cell.setCellStyle(style);

// 第五步，写入实体数据 实际应用中这些数据从数据库得到，

int i = 0;
for (String[] str : list) {
row = sheet.createRow((int) i + 1);
System.out.println("str=" + i);
for (int j = 0; j < str.length; j++) {
// 第四步，创建单元格，并设置值
System.out.println("str=" + str[j]);
row.createCell((int) j).setCellValue(str[j]);
j++;
}
i++;

}
// 第六步，将文件存到指定位置
try {
FileOutputStream fout = new FileOutputStream("E:/students.xls");
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
}

}

}