读取xls以及xlsx表格的方法
在项目中，有时候会遇到处理文件的需求，比如上传表格保存数据到数据库，以表格的形式下载内容，或者读取表格的内容做其他用途。总之就是操作表格，那么有哪些方式呢？这里可以提供两种。
一、利用WorkBook读取xls格式的表格
用到的jar包的话，这里是Maven工程就直接加依赖：
<dependency>
<groupId>poi</groupId>
<artifactId>poi</artifactId>
<version>3.1-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi.jxl</groupId>
<artifactId>jxl</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>org.lucee</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15.0</version>
</dependency>
然后直接贴代码：
// 业务逻辑 //根据不同日期获取不同表格文件
Date now=new Date();
Date date=DateUtils.addDays(now, -30);
SimpleDateFormat sFormat0=new SimpleDateFormat("yyyy-MM-dd");
String currentDate0=sFormat0.format(date);
String[] strDate=currentDate0.split("-");
String filepath = "C:/Users/Administrator/Desktop/abcd/"+strDate[1]+strDate[2]+".xls";
StringBuilder sbBuilder = new StringBuilder();
try {
Workbook workbook = Workbook.getWorkbook(new File(filepath));
Sheet sheet = workbook.getSheet(0);
// j为行数，getCell("列号","行号")
int j = sheet.getRows();
int y = sheet.getColumns();
for (int i = 0; i < j; i++) {
for (int x = 0; x < y; x++) {
// 获取单元格内容
Cell c = sheet.getCell(x, i);
String s = c.getContents();
sbBuilder.append(",").append(s);
}
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// ,分割
String[] strings = phones.split(",");
StringBuilder sBuilder1 = new StringBuilder();
StringBuilder sBuilder2 = new StringBuilder();
StringBuilder sBuilder3 = new StringBuilder();
if (strings.length % 3 == 0) {
for (int i = 0; i < strings.length; i += 3) {
sBuilder1.append(",").append(strings[i]);
sBuilder2.append(",").append(strings[i + 1]);
sBuilder3.append(",").append(strings[i + 2]);
}
}else{
logger.info("读取的文件中有空值");
throw new IOException("文件错误");
}
logger.info("从表格中读取的第一个字段：" + sBuilder1.toString());
// 保存到数组
String[] str = sBuilder1.toString().substring(1).split(","); // 去“，”有效
String[] str2 = sBuilder2.toString().substring(1).split(",");
String[] str3 = sBuilder3.toString().substring(1).split(",");
这个是有三个字段的表格读取，根据需求变动。
二、利用XSSFWorkbook读取xlsx
依赖更简洁，都是poi的包：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.12</version>
</dependency>
直接贴代码：
long start = System.currentTimeMillis();String filepath = "C:/Users/Administrator/Desktop/****/totalusers.xlsx";
StringBuilder sBuilder1 = new StringBuilder();
StringBuilder sBuilder2 = new StringBuilder();
StringBuilder sBuilder3 = new StringBuilder();
XSSFWorkbook xssfWorkbook;
try {
xssfWorkbook = new XSSFWorkbook(new File(filepath));
// 获取每一个工作薄
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 获取当前工作薄的每一行
for (int rowNum = 1; rowNum <=1 /*xssfSheet.getLastRowNum()*/; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
//读取第一列数据
XSSFCell one = xssfRow.getCell(0);
//这里根据字段实际类型获取内容，或者设置类型在获取内容
//one.setCellType(Cell.CELL_TYPE_STRING);
sBuilder1.append(",").append(one.getStringCellValue());
//读取第二列数据
XSSFCell two = xssfRow.getCell(1);
sBuilder2.append(",").append(two.getStringCellValue());
//读取第三列数据
XSSFCell three = xssfRow.getCell(2);
sBuilder3.append(",").append(three.getStringCellValue());
}
}
}
} catch (InvalidFormatException | IOException e) {
logger.error("表格读取错误！",e);
throw new IOException();
}
xssfWorkbook.close();
long end = System.currentTimeMillis();
logger.info("从表格中读取的第一个字段：" + sBuilder1.toString());
好了，这里是两种读取表格的方法，至于保存到表格，时间不够，思想应该差不多，有机会再说。
以上是 读取xls以及xlsx表格的方法 的全部内容， 来源链接： utcz.com/z/509160.html
