需求:
需要自动操控生成一个excel,固定模板,包含了多个sheet,以下是一个例子,如有帮助请打赏谢谢
使用maven管理
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
使用方法
package com.ld.ldmall.controller;
import com.ld.ldmall.bo.ExcelParam;
import com.ld.ldmall.common.api.CommonResult;
import com.ld.ldmall.utils.ExcelOperateSelfUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
@Controller
@Api(tags = "ExcelOperateController", description = "excel管理")
@RequestMapping("/excel")
@Slf4j
/**
* excel操作管理
*/
public class ExcelOperateController {
@RequestMapping("/importExcel")
@ApiOperation("将数据导入到excel中")
@ResponseBody
/**
* 将数据按照规则循环到excel中
*/
public void importExcel(HttpServletResponse response) throws Exception{
String fname = "D:\\ddj\\java\\ldmall\\admin\\src\\main\\resources\\doc\\test_template.xls";
Workbook ExcelBook = ExcelOperateSelfUtil.ExcelCommon(fname);
CommonResult week = week("");
Map<String, List<Map<CellAddress, Object>>> data = (Map) week.getData();
ExcelOperateSelfUtil.dataResult(data,ExcelBook);
String fileName = "周表数据";//表格名称
OutputStream os = null;
os = response.getOutputStream();
fileName = new String(fileName.getBytes("GB2312"), "ISO_8859_1");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xls");
ExcelBook.write(os);
os.flush();
os.close();
log.info("下载成功");
}
@RequestMapping("/week")
@ApiOperation("读取数据指定一个文件获取里面的内容")
@ResponseBody
public CommonResult week(@RequestParam(value="type",defaultValue="") String type) throws Exception{
try{
String fname = "D:\\ddj\\java\\ldmall\\admin\\src\\main\\resources\\doc\\test.xls";
Workbook ExcelBook = ExcelOperateSelfUtil.ExcelCommon(fname);
ExcelParam excelParam = new ExcelParam();
if(null != type){
excelParam.setType(type);
switch(type){
case "sheetName":
excelParam.setSheetName("statistics");
break;
case "row":
excelParam.setSheetName("statistics");
excelParam.setRow(5);
break;
case "coordinate":
excelParam.setSheetName("statistics");
excelParam.setCoordinate("A6");
break;
}
}
CommonResult excelContent = ExcelOperateSelfUtil.getExcelContent(ExcelBook, excelParam);
return excelContent;
} catch (Exception e) {
log.error("读取excel数据失败:"+e.getMessage());
e.printStackTrace();
}
return CommonResult.failed("excel读取失败");
}
}
以下是公共类
ExcelOperateSelfUtil
package com.ld.ldmall.utils;
import com.ld.ldmall.bo.ExcelParam;
import com.ld.ldmall.common.api.CommonResult;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* excel自定义的操作工具类
数据格式:
{
"code": 200,
"message": "操作成功",
"data": {
"sheet名称": [
//每一列是一个对象
{
"A1": "新闻ID(数据库)",
"B1": "部门",
}
]
}
}
*excelParam 使用需注意
*/
@Slf4j
public class ExcelOperateSelfUtil {
/**
* 初始化excel中公共的一些变量等
* @param path
* @throws Exception
*/
public static Workbook ExcelCommon(String path) throws Exception{
FileInputStream ExcelFile;
Workbook ExcelBook = null;
try{
//实例化excel 文件的FileInputStream 对象
ExcelFile = new FileInputStream(path);
//实例化excel 文件的XSSFWorkbook 对象
if (path.toLowerCase().endsWith("xlsx")) {
ExcelBook = new XSSFWorkbook(ExcelFile);
} else if (path.toLowerCase().endsWith("xls")) {
ExcelBook = new HSSFWorkbook(new POIFSFileSystem(ExcelFile));
}
}catch (Exception e){
log.error("excel初始化 有误!!!"+e.getMessage());
throw (e);
}
return ExcelBook;
}
/**
* 获取excel的内容 不同的类型获取不同的数据
* @param ExcelBook
* @param excelParam
* @return
* @throws Exception
*/
public static CommonResult getExcelContent(Workbook ExcelBook, ExcelParam excelParam) throws Exception{
Map mapSheet = new HashMap();
Map<CellAddress,Object> map = new HashMap();
List listSheet = new ArrayList();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
String sheetName = excelParam.getSheetName();
String type = excelParam.getType();
Integer row = excelParam.getRow();
String coordinate = excelParam.getCoordinate();
try{
if("".equals(type) || null == type){
int numberOfSheets = ExcelBook.getNumberOfSheets();
for(int i=0;i<numberOfSheets;i++) {
Sheet sheet = ExcelBook.getSheetAt(i);
//获取每一行
sheetDataLoop(map, listSheet, sheet);
mapSheet.put(sheet.getSheetName(), listSheet);
listSheet = new ArrayList();
list.add(mapSheet);
mapSheet = new HashMap();
}
return CommonResult.success(list);
}else if(type.equals("sheetName") && !"".equals(sheetName)){
Sheet sheet = ExcelBook.getSheet(sheetName);
sheetDataLoop(map, listSheet, sheet);
mapSheet.put(sheetName, listSheet);
listSheet = new ArrayList();
list.add(mapSheet);
return CommonResult.success(list);
}else if(type.equals("row") && !"".equals(sheetName) && row > 0){
Sheet sheet = ExcelBook.getSheet(sheetName);
Row currentRow = sheet.getRow(row);
rowDataLoop(map, listSheet, currentRow);
mapSheet.put(sheetName, listSheet);
listSheet = new ArrayList();
list.add(mapSheet);
return CommonResult.success(list);
}else if(type.equals("coordinate") && !"".equals(sheetName) && !"".equals(coordinate)){
Sheet sheet = ExcelBook.getSheet(sheetName);
CellAddress address = new CellAddress(coordinate);
Row rowCoordinate = sheet.getRow(address.getRow());
// 获取列
Cell cell = rowCoordinate.getCell(address.getColumn());
cellTypeJudge(map, cell);
listSheet.add(map);
mapSheet.put(sheetName, listSheet);
list.add(mapSheet);
return CommonResult.success(list);
}
return CommonResult.failed("参数传递有误");
} catch (Exception e) {
log.error("读取excel数据失败:"+e.getMessage());
e.printStackTrace();
}
return CommonResult.failed("excel读取失败");
}
/**
* 单元格类型判断
* @param map
* @param cell
*/
private static void cellTypeJudge(Map<CellAddress, Object> map, Cell cell) {
switch (cell.getCellType()) {
case NUMERIC:
map.put(cell.getAddress(), String.valueOf(cell.getNumericCellValue()));
break;
case STRING:
map.put(cell.getAddress(), cell.getStringCellValue());
break;
case BOOLEAN:
break;
case FORMULA:
break;
default:
break;
}
}
/**
* 处理数据,将所有数据插入到指定的单元格
* @param data
* @param ExcelBook
*/
public static void dataResult(Map<String, List<Map<CellAddress, Object>>> data,Workbook ExcelBook){
for(Map.Entry<String, List<Map<CellAddress, Object>>> entry : data.entrySet()){
String mapKey = entry.getKey();
//选择sheet
Sheet sheet = ExcelBook.getSheet(mapKey);
List<Map<CellAddress, Object>> list = entry.getValue();
for(int i=0;i<list.size();i++){
//得到一行的数据 {"A1":"xx","B1":"aaa"}
Map<CellAddress, Object> map = list.get(i);
for(Map.Entry<CellAddress, Object> son : map.entrySet()){
CellAddress address = son.getKey();
// 获取行
Row row = sheet.getRow(address.getRow());
// 获取列
Cell cell = row.getCell(address.getColumn());
//设置单元格的值 需要做类型判断
cell.setCellValue((String) son.getValue());
}
}
}
}
/**
* 循环每一个sheet
* @param map
* @param listSheet
* @param sheet
*/
private static void sheetDataLoop(Map<CellAddress, Object> map, List listSheet, Sheet sheet) {
for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
Row currentRow = sheet.getRow(j);
map = new HashMap<>();
rowDataLoop(map, listSheet, currentRow);
}
}
/**
* @param map
* @param listSheet
* @param currentRow
* @return
* 循环每一行
*/
private static void rowDataLoop(Map<CellAddress, Object> map, List listSheet, Row currentRow) {
if (currentRow != null) {
//获取每一个单元格
for (int k = 0; k < currentRow.getPhysicalNumberOfCells(); k++) {
Cell cell = currentRow.getCell(k);
if (cell == null || "".equals(cell)) {
continue;
}
cellTypeJudge(map, cell);
}
listSheet.add(map);
}
}
}
ExcelParam
package com.ld.ldmall.bo;
import lombok.Data;
@Data
/**
* excel操作处理的实体
* type 的值 sheetName、row、coordinate
* sheetName>row>coordinate
* row 和 coordinate 在使用的时候 必须确保sheetName有值
*/
public class ExcelParam {
private String sheetName;
private Integer row;
private String coordinate;
private String type;
public ExcelParam() {
}
public ExcelParam(String sheetName, Integer row, String coordinate, String type) {
this.sheetName = sheetName;
this.row = row;
this.coordinate = coordinate;
this.type = type;
}
}