XSSFWorkbook Excel导入导出

package com.lcsoft.practice.controller;


import java.io.IOException;
import java.io.InputStream;
import java.util.List;


import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import javax.ws.rs.Consumes;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;


import com.github.pagehelper.PageInfo;
import com.lcsoft.practice.common.PoiHelper;
import com.lcsoft.practice.common.GlobalExceptionHandler.Result;
import com.lcsoft.practice.common.GlobalExceptionHandler.ResultExcel;
import com.lcsoft.practice.model.Building;
import com.lcsoft.practice.service.IBuildingService;
import com.lcsoft.practice.vo.BuildingVo;
import com.lcsoft.practice.vo.ExcelOutPut;


import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;




@Api(description="宿舍楼")
@RestController
@RequestMapping("practice/building")
public class BuildingController {

@Autowired
private IBuildingService buildingService;

@ApiOperation("导入Excel-宿舍楼信息")
@PostMapping("/excelBuildingImport")
@Consumes("multipart/form-data")
public ResultExcel excelStudentImport(@RequestParam String access_token,
@RequestParam("file") MultipartFile file) throws ServletException, IOException {

try {

if (file.isEmpty()) {
ResultExcel resultExcel = new ResultExcel(100);
resultExcel.setMessage("请上传Excel文件");
return resultExcel;
}
// 文件名称
String fileName = file.getOriginalFilename();
if (fileName == null || file.isEmpty()) {
ResultExcel resultExcel = new ResultExcel(100);
resultExcel.setMessage("请上传Excel文件");
return resultExcel;
}


// 文件类型
String fileType = "";
try {
fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
String allowImgType = "xlsx,xls";
if (allowImgType.toString().indexOf(fileType.toLowerCase()) < 0) {
ResultExcel resultExcel = new ResultExcel(100);
resultExcel.setMessage("请上传Excel文件");
return resultExcel;
}
} catch (Exception e) {
ResultExcel resultExcel = new ResultExcel(100);
resultExcel.setMessage("异常：文件类型截取失败，请上传正确文件");
return resultExcel;
}


InputStream excelFileIo = file.getInputStream();
ExcelOutPut excelOutPut = null;
if (fileType.equals("xlsx")) {
excelOutPut = PoiHelper.readXlsx(excelFileIo);
} else if (fileType.equals("xls")) {
excelOutPut = PoiHelper.readXls(excelFileIo);
}


if (excelOutPut.getDataList().isEmpty()) {
ResultExcel resultExcel = new ResultExcel(100);
resultExcel.setMessage("请将Excel模版填写完整，至少需要一条数据");
return resultExcel;
}


// 宿舍楼-基本信息
return buildingService.excelBuildingImport( excelOutPut);


} catch (Exception e) {
ResultExcel resultExcel = new ResultExcel(100);
resultExcel.setMessage(e.getMessage());
return resultExcel;
}
}


@ApiOperation("导出Excel-宿舍楼信息")
@GetMapping("/excelBuildingExport")
public Result excelBuildingExport(HttpServletRequest request, HttpServletResponse response,
@RequestParam String access_token) {
try {
return buildingService.excelBuildingExport(request, response);


} catch (Exception e) {
return Result.Fail();
}
}

}

//--------------------------------------------------------接口省略 --- serviceImpl --------------------------

package com.lcsoft.practice.service.impl;


import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.lcsoft.practice.common.CheckExcelData;
import com.lcsoft.practice.common.PoiHelper;
import com.lcsoft.practice.common.GlobalExceptionHandler.Result;
import com.lcsoft.practice.common.GlobalExceptionHandler.ResultExcel;
import com.lcsoft.practice.dao.BuildingDao;
import com.lcsoft.practice.model.Building;
import com.lcsoft.practice.service.IBuildingService;
import com.lcsoft.practice.vo.BuildingVo;
import com.lcsoft.practice.vo.ExcelOutError;
import com.lcsoft.practice.vo.ExcelOutPut;


@Service
public class BuildingServiceImpl implements IBuildingService {

@Autowired
private BuildingDao buildingDao;

@Override
public ResultExcel excelBuildingImport(ExcelOutPut excelOutPut) {
ResultExcel resultExcel = new ResultExcel(0);
List<String> excelTitleName = new ArrayList<String>();
excelTitleName.add("宿舍楼号");
excelTitleName.add("宿舍楼名称");
excelTitleName.add("备注");


List<ExcelOutError> excelOutErrors = new ArrayList<ExcelOutError>();
// 数据行数
int excelRowNum = 0;
resultExcel = CheckExcelData.checkExcelData(excelTitleName, excelOutPut, excelOutErrors, excelRowNum,
resultExcel);
if (resultExcel.getCode() == 44) {
return resultExcel;
}


// Excel填充信息
List<Building> buildings = new ArrayList<>();


for (int i = 0; i < excelOutPut.getDataList().size(); i++) {
List<String> rowDate = excelOutPut.getDataList().get(i);
Building building = new Building();
building.setBuildingNo(Integer.parseInt(rowDate.get(0)));
building.setBuildingName(rowDate.get(1));
building.setRemark(rowDate.get(2));
buildings.add(building);
}


// 数据库宿舍楼信息
List<Building> regBuildings = buildingDao.getBuildings();
// 数据行数
excelRowNum = 0;
// 数据校验
for (int i = 0; i < buildings.size(); i++) {
excelRowNum++;
ExcelOutError error = new ExcelOutError();
error.setRowNum("第" + excelRowNum + "行数据：");


Building rowData = buildings.get(i);


String rowErrors = "";


// 宿舍楼号
if (regBuildings.stream().filter(c -> c.getBuildingNo()==(rowData.getBuildingNo())).count() > 0) {
rowErrors += String.format("【宿舍楼号】:" + rowData.getBuildingNo() + "已经存在多条，请核实数据;");
}
if (buildings.stream().filter(c -> c.getBuildingNo()==(rowData.getBuildingNo())).count() > 1) {
rowErrors += String.format("【宿舍楼号】:" + rowData.getBuildingNo() + "在Excel数据中存在多条，请保证数据唯一性;");
}
//宿舍楼名称
if (regBuildings.stream().filter(c -> c.getBuildingName().equals(rowData.getBuildingName())).count() > 0) {
rowErrors += String.format("【宿舍楼名称】:" + rowData.getBuildingName() + "已经存在多条，请核实数据;");
}
if (buildings.stream().filter(c -> c.getBuildingName().equals(rowData.getBuildingName())).count() > 1) {
rowErrors += String.format("【宿舍楼名称】:" + rowData.getBuildingName() + "在Excel数据中存在多条，请保证数据唯一性;");
}
// 备注
if (rowData.getRemark().isEmpty()) {
rowData.setRemark(null);
}
if ( ! rowErrors.isEmpty()) {
error.setErrorMsg(rowErrors);
excelOutErrors.add(error);
}
}
// 返回错误
if (! excelOutErrors.isEmpty()) {
resultExcel.setCode(44);
resultExcel.setExcelOutErrors(excelOutErrors);
return resultExcel;
}


try {
for (int i = 0; i < buildings.size(); i++) {


Building building = buildings.get(i);

int n = buildingDao.insertBuilding(building);

if( n > 0 ){
resultExcel.setCode(0);
}else{
resultExcel.setCode(100);
resultExcel.setMessage("异常：新增【宿舍楼】信息失败，执行结果返回0");
}
if (resultExcel.getCode() == 100) {
break;
}
}
} catch (Exception e) {
// 手动回滚
resultExcel.setCode(100);
resultExcel.setMessage(e.getMessage());
}


if (resultExcel.getCode() == 100) {
// 手动回滚
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
return resultExcel;
}


@Override
public Result excelBuildingExport(HttpServletRequest request, HttpServletResponse response) throws Exception {

List<Building> regBuildings = buildingDao.getBuildings();
// 标题数据
ArrayList<String> titleList = new ArrayList<String>();


titleList.add("宿舍楼号");
titleList.add("宿舍楼名称");
titleList.add("备注");
// 实际数据
ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();


for (Building building : regBuildings) {
ArrayList<String> data = new ArrayList<>();
data.add(building.getBuildingNo()+"");
data.add(building.getBuildingName());
data.add(building.getRemark());
dataList.add(data);
}


ExcelOutPut excelOutPut = new ExcelOutPut(titleList, dataList);
PoiHelper.createExcel(response, excelOutPut);


return Result.Success();
}


}

package com.lcsoft.practice.common;


import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


import javax.servlet.http.HttpServletResponse;


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.util.NumberToTextConverter;
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 com.lcsoft.practice.vo.ExcelOutPut;


/**
 * @author 
 *
 */
public class PoiHelper {
/**
* ExcelOutPut
* 
* @param filePath
* @return
* @throws InvalidFormatException
* @throws IOException
*/
public static ExcelOutPut readXlsx(InputStream io) throws InvalidFormatException, IOException {


ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
ArrayList<String> titleList = new ArrayList<String>();
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(io);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);


int rowstart = xssfSheet.getFirstRowNum();
int rowEnd = xssfSheet.getLastRowNum();


// 分离excel第一行
XSSFRow row1 = xssfSheet.getRow(rowstart);
if (null == row1) {
xssfWorkbook.close();
return null;
}
int cellStart1 = row1.getFirstCellNum();
int cellEnd1 = row1.getLastCellNum();
for (int k = cellStart1; k <= cellEnd1; k++) {
XSSFCell cell = row1.getCell(k);
if (null == cell) {
continue;
} else {
if (cell.toString().isEmpty() == false) {
titleList.add(cell.toString());
} else {
continue;
}
}
}


for (int i = rowstart + 1; i <= rowEnd; i++) {


XSSFRow row = xssfSheet.getRow(i);
if (null == row)
continue;
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
if (cellStart < 0 || cellEnd < 0) {
continue;
}
ArrayList<String> arrayList = new ArrayList<String>();
for (int k = cellStart; k < titleList.size(); k++) {
XSSFCell cell = row.getCell(k);
if (null == cell) {
arrayList.add("");
} else {
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
arrayList.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case STRING: // 字符串
arrayList.add(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
arrayList.add(String.valueOf(cell.getBooleanCellValue()));
break;
case FORMULA: // 公式
arrayList.add(cell.getCellFormula());
break;
case BLANK: // 空值
arrayList.add("");
break;
case ERROR: // 故障
arrayList.add("");
break;
default:
arrayList.add("");
break;
}
}
}


dataList.add(arrayList);
}
ExcelOutPut excelOutPut = new ExcelOutPut(titleList, dataList);
xssfWorkbook.close();
io.close();
return excelOutPut;
}


/**
* ExcelOutPut
* 
* @param filePath
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static ExcelOutPut readXls(InputStream io) throws FileNotFoundException, IOException {
ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
ArrayList<String> titleList = new ArrayList<String>();


HSSFWorkbook hssfWorkbook = new HSSFWorkbook(io);
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);


int rowstart = hssfSheet.getFirstRowNum();
int rowEnd = hssfSheet.getLastRowNum();


// 分离excel第一行
HSSFRow row1 = hssfSheet.getRow(rowstart);
if (null == row1) {
hssfWorkbook.close();
return null;
}
int cellStart1 = row1.getFirstCellNum();
int cellEnd1 = row1.getLastCellNum();
for (int k = cellStart1; k <= cellEnd1; k++) {
HSSFCell cell = row1.getCell(k);
if (null == cell) {
continue;
} else {
if (cell.toString().isEmpty() == false) {
titleList.add(cell.toString());
} else {
continue;
}
}
}


for (int i = rowstart + 1; i <= rowEnd; i++) {
HSSFRow row = hssfSheet.getRow(i);
if (null == row)
continue;
int cellStart = row.getFirstCellNum();
int cellEnd = row.getLastCellNum();
if (cellStart < 0 || cellEnd < 0) {
continue;
}
ArrayList<String> arrayList = new ArrayList<String>();
for (int k = cellStart; k < titleList.size(); k++) {
HSSFCell cell = row.getCell(k);
if (null == cell) {
arrayList.add("");
} else {
switch (cell.getCellTypeEnum()) {
case NUMERIC: // 数字
arrayList.add(NumberToTextConverter.toText(cell.getNumericCellValue()));
break;
case STRING: // 字符串
arrayList.add(cell.getStringCellValue());
break;
case BOOLEAN: // Boolean
arrayList.add(String.valueOf(cell.getBooleanCellValue()));
break;
case FORMULA: // 公式
arrayList.add(cell.getCellFormula());
break;
case BLANK: // 空值
arrayList.add("");
break;
case ERROR: // 故障
arrayList.add("");
break;
default:
arrayList.add("");
break;
}
}
}
dataList.add(arrayList);
}
ExcelOutPut excelOutPut = new ExcelOutPut(titleList, dataList);
hssfWorkbook.close();
return excelOutPut;
}


/**
* CreateExcel
* 
* @param response
* @param excelOutPut
* @return
* @throws IOException
*/
public static Boolean createExcel(HttpServletResponse response, ExcelOutPut excelOutPut) throws IOException {


ArrayList<String> titleList = excelOutPut.getTitleList();
ArrayList<ArrayList<String>> dataList = excelOutPut.getDataList();
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFRow row = sheet.createRow((short) 0);
for (int i = 0; i < titleList.size(); i++) {
row.createCell(i).setCellValue(titleList.get(i));
}


for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow((short) i + 1);
for (int j = 0; j < dataList.get(i).size(); j++) {
row.createCell(j).setCellValue(dataList.get(i).get(j));
}
}


for (int columnNum = 0; columnNum < dataList.get(0).size(); columnNum++) {
int columnWidth = sheet.getColumnWidth(columnNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
XSSFRow currentRow;
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}


if (currentRow.getCell(columnNum) != null) {
XSSFCell currentCell = currentRow.getCell(columnNum);
int length = currentCell.toString().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
sheet.setColumnWidth(columnNum, columnWidth * 256 + 256 * 1);
}


Date currentTime = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String fileName = formatter.format(currentTime);
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.setContentType("application/octet-stream;charset=utf-8");
OutputStream outStream = response.getOutputStream();
wb.write(outStream);
wb.close();


return true;
}
}

public static class ResultExcel {
public static final Integer OK = 0;
public static final Integer ERROR = 100;


private Integer code;
private String message;
private String detail;


private List<ExcelOutError> excelOutErrors;


public List<ExcelOutError> getExcelOutErrors() {
return excelOutErrors;
}


public void setExcelOutErrors(List<ExcelOutError> excelOutErrors) {
this.excelOutErrors = excelOutErrors;
}


public Integer getCode() {
return code;
}


public void setCode(Integer code) {
this.code = code;
}


public String getMessage() {
return message;
}


public void setMessage(String message) {
this.message = message;
}


public String getDetail() {
return detail;
}


public void setDetail(String detail) {
this.detail = detail;
}


public ResultExcel() {
}


public ResultExcel(int errorCode) {


this.code = errorCode;
}


public static ResultExcel Success() {


return new ResultExcel(ResultExcel.OK);
}


public static ResultExcel Fail() {


return new ResultExcel(ResultExcel.ERROR);
}
}

package com.lcsoft.practice.vo;


import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;


@ApiModel("导入Excel错误信息")
public class ExcelOutError {


@ApiModelProperty("错误行数")
private String rowNum;
@ApiModelProperty("错误信息")
private String errorMsg;


public String getRowNum() {
return rowNum;
}


public void setRowNum(String rowNum) {
this.rowNum = rowNum;
}


public String getErrorMsg() {
return errorMsg;
}


public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
}