Java语言读取excel(一)
public LinkedHashMap<String, String> getExceldata(String tableTitle) {
LinkedHashMap<String, String> excelData= null;
FileInputStream fileIs = null;
try {
fileIs = new FileInputStream("src/test/resource/exceldata.xlsx");
XSSFWorkbook excel = new XSSFWorkbook(fileIs);
XSSFSheet sheet = excel.getSheetAt(0);
XSSFRow row1 = sheet.getRow(0);
String s1_1 = row1.getCell(0).getStringCellValue();
for (int rowN = 0; rowN <= sheet.getLastRowNum(); rowN++) {
XSSFCell sn_1_c = sheet.getRow(rowN) != null ? sheet.getRow(rowN).getCell(0) : null;
String sn_1 = sn_1_c != null ? sn_1_c.getStringCellValue() : "";指定行中第一列的值
if (sn_1.equals(tableTitle)) {
excelData= new LinkedHashMap<>();
XSSFRow rown = sheet.getRow(rowN);
for (int cellN = 0; cellN <= row1.getLastCellNum() - 1; cellN++) {
XSSFCell value = rown.getCell(cellN);
excelData.put(row1.getCell(cellN).getStringCellValue(), value != null ? value.getStringCellValue() : "");
}
break;
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return excelData;
}
Java语言读取excel(二)
public class ReadExcle {
public static Object[][] proessExcel(String filePath,int sheetId) throws IOException {
File file = new File(System.getProperty("user.dir")+filePath);
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(sheetId);
int numberOfRow = sheet.getPhysicalNumberOfRows();
int numberOfCell = sheet.getRow(0).getLastCellNum();
Object[][] dttData = new Object[numberOfRow][numberOfCell];
for(int i=0;i<numberOfRow;i++){
if(null==sheet.getRow(i)||"".equals(sheet.getRow(i))){
continue;
}
for(int j=0;j<numberOfCell;j++) {
if(null==sheet.getRow(i).getCell(j)||"".equals(sheet.getRow(i).getCell(j))){
continue;
}
HSSFCell cell = sheet.getRow(i).getCell(j);
cell.setCellType(CellType.STRING);
dttData[i][j] = cell.getStringCellValue();
}
}
return dttData;
}
}
Python语言读取excel
class ReadExcel():
"""读取excel文件数据"""
def __init__(self,fileName, SheetName="Sheet1"):
self.data = xlrd.open_workbook(fileName)
self.table = self.data.sheet_by_name(SheetName)
# 获取总行数、总列数
self.nrows = self.table.nrows
self.ncols = self.table.ncols
def read_data(self):
if self.nrows > 1:
# 获取第一行的内容,列表格式
keys = self.table.row_values(0)
listApiData = []
# 获取每一行的内容,列表格式
for col in range(1, self.nrows): #获取第几行的数据 从1到总行数
values = self.table.row_values(col) #获取第几行的数据传给values
# keys,values组合转换为字典
api_dict = dict(zip(keys, values)) #利用zip函数将两个列表组成字典
listApiData.append(api_dict) #封装到 key,表头,对应封装的表的内容
return listApiData
else:
print("表格是空数据!")
return None