一,Excel上传解析读取
POI依赖
<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>
HSSFWorkbook对应 .xls后缀文件
XSSFWorkbook对应.xlsx后缀文件
从文件服务器读取。
/**
* @author left
*/
public List<Object> readENExcel(String path) {
// 输入流对象
InputStream is = null;
// 单元格,最终按字符串处理
String cellStr = null;
//进行数据筛选
List<Object> resList = new ArrayList<>();
Object enRes = null;
// 服务器返回的状态
int HttpResult;
try {
// 创建URL
URL url = new URL(path);
// 试图连接并取得返回状态码
URLConnection urlconn = url.openConnection();
urlconn.connect();
HttpURLConnection httpconn = (HttpURLConnection) urlconn;
HttpResult = httpconn.getResponseCode();
if (HttpResult != HttpURLConnection.HTTP_OK) {
log.info("无法连接!");
} else {
is = urlconn.getInputStream();
// 创建Excel2007文件对象
XSSFWorkbook workbook2007 = new XSSFWorkbook(is);
// 开始循环遍历行,表头不处理,从1开始
for (int s = 0; s < workbook2007.getNumberOfSheets(); s++) {
//读取第二个单元格
if(s == 0){
continue;
}
//初始数据存储
List<Object> list = new ArrayList<>();
// 取出工作表,索引0开始
XSSFSheet sheet = workbook2007.getSheetAt(s);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
enRes = new Object();
// 获取行对象
XSSFRow row = sheet.getRow(i);
if (row == null) {
break;
}
// row如果不为空,循环遍历单元格
for (int j = 0; j < row.getLastCellNum(); j++) {
XSSFCell cell = row.getCell(j);
if (cell == null) {
cellStr = "";
} else if (CellType.BOOLEAN.equals(cell.getCellTypeEnum())) {
cellStr = String.valueOf(cell.getBooleanCellValue());
} else if (CellType.NUMERIC.equals(cell.getCellTypeEnum())) {
cellStr = cell.getNumericCellValue() + "";
} else {// 其余按照字符串处理
cellStr = cell.getStringCellValue();
}
// 下面按照数据出现位置封装到对象中
if (j == 0) {
//TODO
continue;
}
if (j == 1) {
//TODO
continue;
}
if (j == 2) {
//TODO
continue;
}
if (j == 3) {
//TODO
continue;
}
...
}
resList.add(enRes);
}
}
}
} catch (Exception e) {
Log.error(e.toString());
e.printStackTrace();
} finally {// 关闭文件流
try {
if (is != null) {
is.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
return resList;
}
二,文件的导出
/**
* @author left
*/
@Component
public class ExportExcelUtil {
//创建excel
public XSSFWorkbook createExcel(String sheetName,
List<String> title,
List data) throws Exception {
//创建HSSFWorkbook对象 .xls
//创建XSSFWorkbook对象 .xlsx
XSSFWorkbook wb = new XSSFWorkbook();
//创建sheet对象
XSSFSheet sheet = wb.createSheet(sheetName);
//在sheet里创建第一行,这里即是表头
XSSFRow rowTitle = sheet.createRow(0);
//写入表头的每一个列
for (int i = 0; i < titleList.size(); i++) {
//创建单元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
//写入每一行的记录
for (int i = 0; i < dataList.size(); i++) {
//创建新的一行,递增
XSSFRow rowData = sheet.createRow(i + 1);
//通过反射,获取POJO对象
Class cl = dataList.get(i).getClass();
//获取类的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
//设置字段可见,否则会报错,禁止访问
fields[j].setAccessible(true);
//创建单元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
return wb;
}
}
实例:
/**
* @author left
*/
@RestController
public class ExcelController {
@Autowired
private ExportExcelUtil exportExcelUtil;
@GetMapping(value = "/out")
public String outExcel(HttpServletResponse response) throws Exception {
String fileName = "文件名称";
String sheetName = "sheet名称";
//表头集合
List<String> title = new ArrayList<>();
title.add("xxx");
title.add("xxx");
title.add("xxx");
...
//数据对象 Object含有啊a b c三个属性
Object o = new Object ();
Object .setA("xxx");
Object .setB("xxx");
Object .setC("xxx");
...
//将两个对象加入到集合中
List<Object > dataList = new ArrayList<>();
dataList .add(o);
//创建文件
XSSFWorkbook workbook = exportExcelUtil.createExcel(sheetName, title, dataList);
//输出
OutputStream output = response.getOutputStream();
response.reset();
//中文名称要进行编码处理
response.setCharacterEncoding("UTF-8");
fileName = URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition", "attachment; filename="+ fileName +".xlsx");
response.setContentType("application/vnd.ms-excel");
workbook.write(output);
output.close();
return null;
}
}