引包:
<!--excel表格处理-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtil {
/**
* 1.先用InputStream获取excel文件的io流
* 2.然后穿件一个内存中的excel文件HSSFWorkbook类型对象,这个对象表示了整个excel文件。
* 3.对这个excel文件的每页做循环处理
* 4.对每页中每行做循环处理
* 5.对每行中的每个单元格做处理,获取这个单元格的值
* 6.把这行的结果添加到一个List数组中
* 7.把每行的结果添加到最后的总结果中
* 8.解析完以后就获取了一个List<List<String>>类型的对象了
* @Title: readXls
* @Description: 处理xls文件
*/
public static List<List<String>> Exceljiexii(InputStream inputStream) throws IOException, InvalidFormatException {
XSSFWorkbook xss = new XSSFWorkbook(inputStream);
List<List<String>> result = new ArrayList<List<String>>();
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
int size = hssfWorkbook.getNumberOfSheets();
// 循环每一页,并处理当前循环页
for (int numSheet = 0; numSheet < size; numSheet++) {
// HSSFSheet 标识某一页
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 处理当前页,循环读取每一行
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
// HSSFRow表示行
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
int minColIx = hssfRow.getFirstCellNum();
int maxColIx = hssfRow.getLastCellNum();
List<String> rowList = new ArrayList<String>();
// 遍历改行,获取处理每个cell元素
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
// HSSFCell 表示单元格
HSSFCell cell = hssfRow.getCell(colIx);
if (cell == null) {
continue;
}
rowList.add(getStringVal(cell));
}
result.add(rowList);
}
}
return result;
}
/**
* @param @param path
* @param @return
* @param @throws Exception 设定文件
* @return List<List<String>> 返回类型
* @Title: readXlsx
* @Description: 处理Xlsx文件
*/
public static List<List<String>> readXlsx(InputStream inputStream) throws Exception {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
List<List<String>> result = new ArrayList<List<String>>();
// 循环每一页,并处理当前循环页
for (Sheet xssfSheet : xssfWorkbook) {
if (xssfSheet == null) {
continue;
}
// 处理当前页,循环读取每一行
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
Row xssfRow = xssfSheet.getRow(rowNum);
int minColIx = xssfRow.getFirstCellNum();
int maxColIx = xssfRow.getLastCellNum();
List<String> rowList = new ArrayList<String>();
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
Cell cell = xssfRow.getCell(colIx);
if (cell == null) {
continue;
}
rowList.add(cell.toString());
}
result.add(rowList);
}
}
return result;
}
/**
* 改造poi默认的toString()方法如下
* @param @param cell
* @param @return 设定文件
* @return String 返回类型
* @throws
* @Title: getStringVal
* @Description: 1.对于不熟悉的类型,或者为空则返回""控制串
* 2.如果是数字,则修改单元格类型为String,然后返回String,这样就保证数字不被格式化了
*/
public static String getStringVal(HSSFCell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_NUMERIC:
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return "";
}
}
/**
* excel生成
* @param excel
* @return
*/
public static XSSFWorkbook excel(List<List<String>> excel) {
//创建excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表sheet
XSSFSheet sheet = workbook.createSheet();
//写入数据
for (int i = 0; i < excel.size(); i++) {
//列
XSSFRow nrow = sheet.createRow(i);
//行
for (int u=0;u<excel.get(i).size();u++){
XSSFCell ncell = nrow.createCell(u);
ncell.setCellValue(excel.get(i).get(u));
}
}
return workbook;
}
}
excel表格的工具类,直接上,不说话。