本次使用的是ApachePOI技术，挺简单的就不多说了，做下记录
1、使用ExcelUtils工具类进行操作
工具类：
package com.youming.shuiku.system.utils.excel;
import cn.hutool.json.JSON;
import com.alibaba.fastjson.JSONObject;
import com.youming.shuiku.commons.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Hyperlink;
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.usermodel.WorkbookFactory;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Excel工具类
*/
@Slf4j
public class ExcelUtils {
public static String DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
public static List<List<String>> getExcelContent(InputStream inputStream, int beginRow, ExcelFilesVaildate[] validates)throws Exception {
DataFormatter dataFormatter = new DataFormatter();
List<List<String>> fileContent = new ArrayList();
Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum() + 1;
if (rows >= 1000 + beginRow) {
throw new BusinessException("excel文件的行数超过系统允许导入最大行数：" + 1000);
} else {
if (rows >= beginRow) {
List rowList = null;
Row row = null;
for (int i = beginRow - 1; i < rows; ++i) {
row = sheet.getRow(i);
rowList = new ArrayList();
fileContent.add(rowList);
if (row != null) {
int cells = row.getLastCellNum();
if (cells > 200) {
throw new BusinessException("文件列数超过200列，请检查文件！");
}
for (int j = 0; j < cells; ++j) {
Cell cell = row.getCell(j);
String cellValue = "";
Hyperlink hyperlink = cell.getHyperlink();
if(hyperlink != null){
HashMap<String,Object> map=new HashMap<>();
map.put("key",cell.getStringCellValue());
map.put("value",hyperlink.getAddress());
Object json = JSONObject.toJSON(map);
cellValue =json.toString();
}
if (cell != null && hyperlink == null) {
log.debug("Reading Excel File row:" + i + ", col:" + j + " cellType:" + cell.getCellType());
switch (cell.getCellType()) {
case 0:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
cellValue = formatDateByFormat(cell.getDateCellValue(), DATETIME_FORMAT);
} else {
cellValue = dataFormatter.formatCellValue(cell);
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
}
}
if (validates != null && validates.length > j) {
if (cellValue == null) {
throw new BusinessException("第" + (i + beginRow - 1) + "行,第" + (j + 1) + "列数据校验出错：" + validates[j].getErrorMsg());
}
Pattern p = Pattern.compile(validates[j].getPattern());
Matcher m = p.matcher(cellValue);
if (!m.matches()) {
throw new BusinessException("第" + (i + beginRow - 1) + "行,第" + (j + 1) + "列数据校验出错：" + validates[j].getErrorMsg());
}
}
rowList.add(cellValue);
}
}
}
}
return fileContent;
}
}
public static String formatDateByFormat(Date date, String format) {
String result = "";
if (date != null) {
try {
SimpleDateFormat sdf = new SimpleDateFormat(format);
result = sdf.format(date);
} catch (Exception var4) {
var4.printStackTrace();
}
}
return result;
}
public static String getExcelValue(List<String> list, int index) {
if (list == null || list.isEmpty() || list.size() < index + 1) {
return null;
}
return StringUtils.trim(list.get(index));
}
}
使用：
LambdaQueryWrapper<ArchiveDisplacementMeter> lambda = Wrappers.lambdaQuery();
lambda.orderByAsc(ArchiveDisplacementMeter::getNumber);
List<ArchiveDisplacementMeter> list = this.list(lambda);
try {
String[] showName = null;
String[] resourceFild = null;
if (list != null && list.size() > 0) {
for (ArchiveDisplacementMeter archiveDisplacementMeter: list) {
archiveDisplacementMeter.setMeasurePointExport("多点位移计"+archiveDisplacementMeter.getNumber());
double v = Double.valueOf(archiveDisplacementMeter.getElevation()) -
Double.valueOf(archiveDisplacementMeter.getHead());
archiveDisplacementMeter.setBeginEndElevation(String.valueOf(v));
archiveDisplacementMeter.setWorkState("正常");
}
}
showName = new String[]{"水工建筑物编号","测点编号","测点别名","考证信息日期","型式",
"桩号","轴距","起始高程","安装日期","测定日期","仪器出厂编号","工作状态","备注"};
resourceFild = new String[]{"getToponym","getMeasurePointExport","getMeasurePointName","getPreparerTime",
"getSensorType","getStakeMark","getAxisDistance","getBeginEndElevation","getPreparerTime","getPreparerTime",
"getFactoryNumber","getWorkState","getRemark"};
XSSFWorkbook workbook = ExportExcel.getWorkbookXlsx(list, showName, resourceFild, ArchiveDisplacementMeter.class,
null);
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
byte[] b = os.toByteArray();
MinioUploadDto minioUploadDto = minioService.upload("多点位移计安装埋设考证表.xlsx", b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
return minioUploadDto.getUrl();
2、原始poi
int a = waterHeightReport.get(0).getPrv().size()+2;
XSSFWorkbook sheets = new XSSFWorkbook();
XSSFSheet water1 = sheets.createSheet("water1");
XSSFRow row = water1.createRow(0);
XSSFCell cell1 = row.createCell(0);
XSSFCellStyle cellStyle = sheets.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
XSSFFont font = sheets.createFont(); //创建字体
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
cellStyle.setFont(font);
cell1.setCellStyle(cellStyle);
cell1.setCellValue("水位报表");
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,a-1);//起始行,结束行,起始列,结束列
water1.addMergedRegion(callRangeAddress);//合并
XSSFRow row1 = water1.createRow(1);
XSSFCell cell2 = row1.createCell(0);
cell2.setCellValue("站点");
cell2.setCellStyle(cellStyle);
XSSFCell cell3 = row1.createCell(1);
cell3.setCellValue("实时水位");
cell3.setCellStyle(cellStyle);
for (int i = 0; i < waterHeightReport.get(0).getPrv().size() ; i++) {
XSSFCell cell = row1.createCell(i+2);
cell.setCellValue(waterHeightReport.get(0).getPrv().get(i).getPeriod()+"日(米)");
cell.setCellStyle(cellStyle);
}
for (int i = 0; i < waterHeightReport.size(); i++) {
XSSFRow rows = water1.createRow(i + 2);
rows.createCell(0).setCellValue(waterHeightReport.get(i).getStNm());
rows.createCell(1).setCellValue(waterHeightReport.get(i).getPrv().get(a-3).getWaterHeight());
for (int j = 0; j < waterHeightReport.get(i).getPrv().size(); j++) {
rows.createCell(j+2).setCellValue(waterHeightReport.get(i).getPrv().get(j).getWaterHeight());
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
sheets.write(os);
byte[] b = os.toByteArray();
MinioUploadDto minioUploadDto = minioService.upload("水位报表.xlsx", b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
return minioUploadDto.getUrl();
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/75412.html
