/**
*
*/
package com.bn.car.common.report.excel; import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.bn.car.biz.supply.dto.PartsInfoDTO; /**
* @author huangjing
* @date 2014-1-24
*/
public class XlsMain {
public static void main(String[] args) throws IOException {
try {
XlsMain xlsMain = new XlsMain();
PartsInfoDTO dto = null;
List<PartsInfoDTO> list = xlsMain.readXls("d://parts2.xls");
// System.out.println(list.size());
for (int i = 0; i < list.size(); i++) {
dto = list.get(i);
System.out.println(dto.getPartsCode() +"--"+dto.getPartsName() +"--"+dto.getStoreNum() +"--"+dto.getCostPrice() +"--"+dto.getMarketPrice() +"--"+dto.getRetailPrice() +"--"+dto.getWeight() +"--"+dto.getUpdown());
}
System.out.println("OK!!");
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} /**
* 读取xls文件内容
*
* @return List<XlsDto>对象
* @throws IOException
* 输入/输出(i/o)异常
*/
public static List<PartsInfoDTO> readXls(String xlspath) throws IOException {
InputStream is = new FileInputStream(xlspath);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
PartsInfoDTO dto = null;
List<PartsInfoDTO> list = new ArrayList<PartsInfoDTO>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
// System.out.println("hssfSheet:" + hssfSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row,从第一行开始。
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
// System.out.println("hssfRow:"+hssfRow);
if (hssfRow == null) {
continue;
}
dto = new PartsInfoDTO();
// 循环列Cell
// 0学号 1姓名 2学院 3课程名 4 成绩
// for (int cellNum = 0; cellNum <=4; cellNum++) {
HSSFCell cell = hssfRow.getCell(0);
// System.out.println("cell:" + cell);
if (cell == null) {
continue;
}
dto.setPartsCode(getValue(cell)); cell = hssfRow.getCell(1);
// System.out.println("cell1:" + cell);
if (cell == null) {
continue;
}
dto.setPartsName(getValue(cell)); cell = hssfRow.getCell(2);
// System.out.println("cell2:" + cell);
if (cell == null) {
continue;
}
dto.setStoreNum(Float.valueOf(getValue(cell)).intValue()); cell = hssfRow.getCell(3);
// System.out.println("cell3:" + cell);
if (cell == null) {
continue;
}
dto.setCostPrice(Float.valueOf(getValue(cell))); cell = hssfRow.getCell(4);
// System.out.println("cell4:" + cell);
if (cell == null) {
continue;
}
dto.setMarketPrice(Float.parseFloat(getValue(cell))); cell = hssfRow.getCell(5);
// System.out.println("cell5:" + cell);
if (cell == null) {
continue;
}
dto.setRetailPrice(Float.parseFloat(getValue(cell))); cell = hssfRow.getCell(6);
// System.out.println("cell6:" + cell);
if (cell == null) {
continue;
}
dto.setWeight(Float.parseFloat(getValue(cell))); cell = hssfRow.getCell(7); //写成8了,取不到,所以不执行下面的代码~!!
// System.out.println("cell7:" + cell);
if (cell == null) {
continue;
}
if(getValue(cell).equals("是")){
dto.setUpdown(1);
}else{
dto.setUpdown(0);
} // System.out.println("上下架:"+dto.getUpdown());
list.add(dto);
}
}
// System.out.println("SIZE:" + list.size());
return list;
} /**
* 得到Excel表中的值
*
* @param hssfCell
* Excel中的每一个格子
* @return Excel中每一个格子中的值
*/
@SuppressWarnings("static-access")
public static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
} }
parts2.xls (导入模板文件~!)