package com.eastcom_sw.inas.common.utils.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
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 org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
/**
* 用于读取excel
*/
public class ExcelReader1 {
private HSSFWorkbook wb = null;//Excel文档对象
private HSSFSheet sheet = null;//Excel表单对象
private HSSFRow row = null;//Excel行
private int sheetNum = 0;//第1个工作表
private int rowNum = 0;
private InputStream fis = null;
private CommonsMultipartFile file = null;
public ExcelReader1() {
}
public ExcelReader1(CommonsMultipartFile file) {
this.file = file;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}
public void setFile(CommonsMultipartFile file) {
this.file = file;
}
/**
* 读取excel文件获得HSSFWorkbook对象
*/
public void open() throws IOException {
// fis = new FileInputStream(file);
fis = file.getInputStream();
wb = new HSSFWorkbook(new POIFSFileSystem(fis));
fis.close();
}
/**
* 返回sheet表数目
*
* @return int
*/
public int getSheetCount() {
int sheetCount = -1;
sheetCount = wb.getNumberOfSheets();
return sheetCount;
}
/**
* sheetNum下的记录行数
*
* @return int
*/
public int getRowCount() {
if (wb == null)
System.out.println("=============>WorkBook为空");
HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 读取指定sheetNum的rowCount
*
* @param sheetNum
* @return int
*/
public int getRowCount(int sheetNum) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 得到指定行的内容
*
* @param lineNum
* @return String[]
*/
public String[] readExcelLine(int lineNum) {
return readExcelLine(this.sheetNum, lineNum);
}
/**
* 指定工作表和行数的内容
*
* @param sheetNum
* @param lineNum
* @return String[]
*/
public String[] readExcelLine(int sheetNum, int lineNum) {
if (sheetNum < 0 || lineNum < 0)
return null;
String[] strExcelLine = null;
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(lineNum);
int cellCount=0;
if(!isBlankRow(row)){
cellCount = row.getLastCellNum();//获得最后一个单元格的索引
}
strExcelLine = new String[cellCount + 1];
for (int i = 0; i < cellCount; i++) {
strExcelLine[i] = readStringExcelCell(lineNum, i);
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelLine;
}
/**
* 读取指定列的内容
*
* @param cellNum
* @return String
*/
public String readStringExcelCell(int cellNum) {
return readStringExcelCell(this.rowNum, cellNum);
}
/**
* 指定行和列编号的内容
*
* @param rowNum
* @param cellNum
* @return String
*/
public String readStringExcelCell(int rowNum, int cellNum) {
return readStringExcelCell(this.sheetNum, rowNum, cellNum);
}
/**
* 指定工作表、行、列下的内容
*
* @param sheetNum
* @param rowNum
* @param cellNum
* @return String
*/
public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
if (sheetNum < 0 || rowNum < 0)
return "";
String strExcelCell = "";
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(rowNum);
if(row.getCell((short)cellNum) != null){
switch (row.getCell((short) cellNum).getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA://如果是公式型
strExcelCell = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: //如果是数字型
strExcelCell = String.valueOf(row.getCell((short) cellNum).getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING://如果是字符串型
strExcelCell = row.getCell((short) cellNum).getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://如果是布尔型
strExcelCell=String.valueOf(row.getCell((short) cellNum).getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://如果是空串
strExcelCell = "";
break;
case HSSFCell.CELL_TYPE_ERROR://如果是错误型
strExcelCell = String.valueOf(row.getCell((short) cellNum).getErrorCellValue());
break;
default:
strExcelCell = "";
break;
}
}
else{
strExcelCell = "";
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelCell;
}
//判断指定行单元格是否为空
public static boolean isBlankRow(HSSFRow row){
if(row == null) return true;
boolean result = true;
for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
HSSFCell cell = row.getCell(i, HSSFRow.RETURN_BLANK_AS_NULL);
String value = "";
if(cell != null){
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING://如果是字符串型
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC://如果是数字型
value = String.valueOf((int) cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN://如果是布尔型
value = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA://如果是公式型
value = String.valueOf(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK://如果是空串
value="";
break;
case HSSFCell.CELL_TYPE_ERROR://如果是错误型
value = String.valueOf(cell.getErrorCellValue());
default://其他类型
value="";
break;
}
}
if(!value.trim().equals("")){
result = false;
break;
}
}
return result;
}
// 主函数用于测试
public static void main(String args[]) {
// File file = new File("E:\\text.xls");
// CommonsMultipartFile file1 = new CommonsMultipartFile("E:\\text.xls");
// ExcelReader1 readExcel = new ExcelReader1(file1);
// try {
// readExcel.open();
// } catch (IOException e) {
// e.printStackTrace();
// }
// readExcel.setSheetNum(0); //设置读取索引为0的工作表
// //总行数
// int count = readExcel.getRowCount();
// for (int i = 0; i <= count; i++) {
// String[] rows = readExcel.readExcelLine(i);
// if(rows.length>0){
// for (int j = 0; j < rows.length; j++) {
// if(!(rows[j]==null)){
// System.out.print(rows[j] + " ");
// }
// }
// }
// System.out.print("\n");
// }
}
}
excel合并含合并同类项的导出
最新推荐文章于 2024-08-14 23:41:11 发布