package exportexcel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
public class ImportExcelUtil {
/**
*
* @param is 上传的文件,传入进来的的流
* @param ExcelName 文件的名称,拿到后缀 xls创建HSSFWorkbook,xlsx创建 XSSFWorkbook
* @throws Exception
*/
public void importExcel(InputStream is,String ExcelName) throws Exception {
Workbook wb = null;//工作簿
Sheet sheet = null;//工作表
Row row = null;//行
//1、创建一个工作簿对象,(面向对象思想,把读取的excel封装成对象,那么我们就可以进行操作它了)
String Exceln=ExcelName.substring(ExcelName.lastIndexOf("."));//获取上传文件的后缀
System.out.println("exceln:"+Exceln);
if(Exceln.equals(".xls")){
wb = new HSSFWorkbook(is);
}else if(Exceln.equals(".xlsx")){
wb = new XSSFWorkbook(is);
}
//2、获取sheet页
sheet = wb.getSheetAt(0);//获取第0个sheet页
//3、判断是否为空
if(sheet==null){
return;
}
//4、遍历行 sheet.getLastRowNum() 获取最后一行
for(int rowNum=2;rowNum<=sheet.getLastRowNum();rowNum++){
//5、获取行
row = sheet.getRow(rowNum);
if(row==null){//如果行为空,结束本次循环,继续下一次循环
continue;
}
/**
* 这里根据你单元格的多少来创建多少个cell对象
*/
Cell cell0 = row.getCell(0, Row.RETURN_BLANK_AS_NULL);//当Excel单元格为空的时候,getCell是为空的,是不对创建cell对象的,会出现空指针异常 Row.RETURN_BLANK_AS_NULL
Cell cell1 = row.getCell(1, Row.RETURN_BLANK_AS_NULL);
Cell cell2 = row.getCell(2, Row.RETURN_BLANK_AS_NULL);
Cell cell3 = row.getCell(3, Row.RETURN_BLANK_AS_NULL);
Cell cell4 = row.getCell(4, Row.RETURN_BLANK_AS_NULL);
Cell cell5 = row.getCell(5, Row.RETURN_BLANK_AS_NULL);
Cell cell6 = row.getCell(6, Row.RETURN_BLANK_AS_NULL);
Cell cell7 = row.getCell(7, Row.RETURN_BLANK_AS_NULL);
Cell cell8 = row.getCell(8, Row.RETURN_BLANK_AS_NULL);
Cell cell9 = row.getCell(9, Row.RETURN_BLANK_AS_NULL);
Cell cell10 = row.getCell(10, Row.RETURN_BLANK_AS_NULL);
Cell cell11 = row.getCell(11, Row.RETURN_BLANK_AS_NULL);
Cell cell12 = row.getCell(12, Row.RETURN_BLANK_AS_NULL);
Cell cell13 = row.getCell(13, Row.RETURN_BLANK_AS_NULL);
Cell cell14 = row.getCell(14, Row.RETURN_BLANK_AS_NULL);
Cell cell15 = row.getCell(15, Row.RETURN_BLANK_AS_NULL);
Cell cell16 = row.getCell(16, Row.RETURN_BLANK_AS_NULL);
Cell cell17 = row.getCell(17, Row.RETURN_BLANK_AS_NULL);
Cell cell18 = row.getCell(18, Row.RETURN_BLANK_AS_NULL);
System.out.print("序号:"+getValue(cell0)+" ");
System.out.print("姓名:"+getValue(cell1)+" ");
System.out.print("参加工作时间:"+getValue(cell2)+" ");
System.out.print("XXX:"+getValue(cell3)+" ");//提供模板的人,为了计算工龄而有的一行,数据没意义,因此不插入。
System.out.print("工龄:"+getValue(cell4)+" ");
System.out.print("公休假:"+getValue(cell5)+" ");
System.out.print("实休:"+getValue(cell6)+" ");
System.out.print("1次:"+getValue(cell7)+" ");
System.out.print("2次:"+getValue(cell8)+" ");
System.out.print("3次:"+getValue(cell9)+" ");
System.out.print("4次:"+getValue(cell10)+" ");
System.out.print("5次:"+getValue(cell11)+" ");
System.out.print("6次:"+getValue(cell12)+" ");
System.out.print("7次:"+getValue(cell13)+" ");
System.out.print("8次:"+getValue(cell14)+" ");
System.out.print("9次:"+getValue(cell15)+" ");
System.out.print("10次:"+getValue(cell16)+" ");
System.out.print("11次:"+getValue(cell17)+" ");
System.out.print("12次:"+getValue(cell18)+" ");
}
}
/**
* 根据单元格不同属性返回字符串数值
* @param cell
* @return
*/
public String getValue(Cell cell) {
String value = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA://公式处理
try {
value = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
value = String.valueOf(cell.getRichStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC://数值处理
if(DateUtil.isCellDateFormatted(cell)){
value = String.valueOf(cell.getDateCellValue());
}else{
cell.setCellType(cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
/*
* 判断是否包含小数点,如果不含小数点,则以字符串读取,
* 如果含小数点,则转换为Double类型的字符串
*/
if (temp.indexOf(".") > -1) {
value = String.valueOf(new Double(temp)).trim();
} else {
value = temp.trim();
}
}
break;
case Cell.CELL_TYPE_STRING://字符处理
value = String.valueOf(cell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空值处理
value="";
break;
case Cell.CELL_TYPE_BOOLEAN://boolean值处理
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR://错误处理
break;
}
}
return value;
}
@Test
public void fun() throws Exception{
InputStream input = new FileInputStream("C:\\Users\\Administrator\\Desktop\\章贡区OA需求\\章贡区OA需求20190816\\章贡区OA需求\\区工信局休假汇总表.xls");
String excelName="区工信局休假汇总表.xls";
importExcel(input, excelName);
}
}
效果图: