execl合并表格的导入和自定义导出execl合并表格的导入和自定义导出
java 获取函数，与获取值
poi读取excel的时候判断空行-腾讯云开发者社区-腾讯云 (tencent.com)
poi小姿势：如何判断excel单元格Cell是否为空 - ForrestGump01 - 博客园 (cnblogs.com)
Java读取Excel数值内容带.0或变科学计数法的解决办法 - Yaaaaa - 博客园 (cnblogs.com)
Java读取Excel表格中的图片_java读取excel中的图片-CSDN博客
java使用POI读取Excel表格中的图片（包含嵌入单元格图片，浮动图片，xls-2003,xlsx-2007）_workbook解析excel中的图片-CSDN博客
java导入带图片的excel表格（工具HSSFWorkbook-HSSFSheet）（支持一条数据（单元格）可以导入多个图片，）_java导入excel带图片-CSDN博客
(72条消息) java导出Excel合并单元格_datangxiajun的博客-CSDN博客
(72条消息) JAVA对excle创建、读取、设置单元格颜色、背景色、跨行跨列_java设置excel背景色_谷同学的博客-CSDN博客
(72条消息) POI 设置Excel单元格背景色（setFillForegroundColor 与 setFillPattern 的使用）_行思坐忆，志凌云的博客-CSDN博客
(35条消息) Java使用POI获取Excel公式_ITarmi的博客-CSDN博客_poi读取excel公式
(72条消息) java 实现excel样式设置（居中、字体、大小、换行、合并行，列宽、指定特定字符串样式等）_cellstyle设置字体大小_时间 流逝的博客-CSDN博客
(35条消息) java.lang.ClassNotFoundException: org.apache.poi.hssf.usermodel.HSSFWorkbook 错误的原因和解决方法_wh445306的博客-CSDN博客
(35条消息) Java使用POI获取Excel公式并计算公式得到值-20220530_qq_40711092的博客-CSDN博客_java读取excel公式计算结果
(35条消息) 【Apache POI】Excel操作（六）：Excel计算公式的读取和使用_古阙月的博客-CSDN博客
Java Pattern类和Matcher类的使用 (biancheng.net)
(43条消息) Java Pattern类的用法详解(正则表达式)_demon7552003的博客-CSDN博客_pattern类
(46条消息) 关于poi的NUMERIC读取excel单元格数值类型数字的问题_龙池小生的博客-CSDN博客_poi读取excel数字类型
Java实现Excel导入和导出，看这一篇就够了(珍藏版)（一） - 知乎 (zhihu.com)
(72条消息) Java 在Excel中添加筛选器并执行筛选_Eiceblue的博客-CSDN博客
通过 Maven 仓库安装 Spire 系列 Java 产品 (e-iceblue.cn)
[(47条消息) Java实现Excel模板的复制，并利用Java反射往新的Excel文件中的指定行开始写入数据_风难追的博客-CSDN博客](https://blog.csdn.net/weixin_42023666/article/details/88681870?ops_request_misc=%7B%22request%5Fid%22%3A%22167098318316782388030582%22%2C%22scm%22%3A%2220140713.130102334..%22%7D&request_id=167098318316782388030582&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-2-88681870-null-null.142^v68^control,201^v4^add_ask,213^v2^t3_esquery_v1&utm_term=java 复制excel&spm=1018.2226.3001.4187)
https://blog.csdn.net/qq_32003379/article/details/123095339
[JAVA导出EXCEL表格 - 顾东城 - 博客园 (cnblogs.com)](https://www.cnblogs.com/gudongcheng/p/8268909.html#:~:text=JAVA导出EXCEL表格 1 1. 下载jar包： 官方下载： http%3A%2F%2Fpoi.apache.org%2Fdownload.html 这里可以下载到它的最新版本和文档，目前最新版本是3.7，这里使用比较稳定的3.6版。 …,首先，我们应该要知道的是，一个Excel文件对应一个workbook，一个workbook中有多个sheet组成，一个sheet是由多个行 (row)和列 (cell)组成。 … 5 5. 导出Excel应用实例： )
Java实现读取Excel多个Sheet数据_java读取excel多个sheet-CSDN博客
(91条消息) 关于poi的NUMERIC读取excel单元格数值类型数字的问题_poi numeric_龙池小生的博客-CSDN博客
NumberToTextConverter.toText() 方法会返回单元格的初始格式 然后转化为String类型；
Excel的后缀名不对，你java项目中的poi jar包是对2003版也就是后缀名为xls的excel进行操作的代码，如果这时候找到的excel是2007版的也就是后缀名为xlsx的excel时，代码就会报错，所以在操作前你需要判断一下操作的excel的后缀，如果是xls的就让他执行，要不然就告诉报错，告诉操作者把excel另存为后缀为xls格式的excel，另一种办法就是两种都写，先判断后缀名为什么，之后就进入对应的操作。
java通过poi读取excel中的日期类型数据或自定义类型日期 - 张亮java - 博客园 (cnblogs.com)
CellType类型以及值的对应关系
| CellType |
类型 |
值 |
| CELL_TYPE_NUMERIC |
数值 |
0 |
| CELL_TYPE_STRING |
字符串型 |
1 |
| CELL_TYPE_FORMULA |
公式型 |
2 |
| CELL_TYPE_BLANK |
空值 |
3 |
| CELL_TYPE_BOOLEAN |
布尔型 |
4 |
| CELL_TYPE_ERROR |
错误 |
5 |
FormulaEvaluator formulaEvaluator;
String fileType = Objects.requireNonNull(file.getOriginalFilename(), "文件名不能为空").
substring(file.getOriginalFilename().lastIndexOf(".") + 1);
if ("xls".equalsIgnoreCase(fileType)){
book=new HSSFWorkbook(file.getInputStream());//2003版本office
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) book);
}else{
book=new XSSFWorkbook(file.getInputStream());//判断是否为2007版本office
formulaEvaluator =new XSSFFormulaEvaluator((XSSFWorkbook) book);
}
//获取到有多少个sheet页
int numberOfSheets = book.getNumberOfSheets();
//对多少页进行for循环，处理各个的业务层
//读取各个Sheet
for (int i = 0; i < numberOfSheets; i++) {
String sheetName = book.getSheetName(i);
Sheet sheet = book.getSheetAt(i);
int lastRowNum = sheet.getLastRowNum();
for (int j = 0; j <= lastRowNum; j++) {
Row row = sheet.getRow(j);
if (null == row) {
continue;
}
String data = "";
int lastCellNum = row.getLastCellNum();
for (int k = 0; k < lastCellNum; k++) {
Cell cell = row.getCell(k);
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
result = cell.getStringCellValue();
break;
case NUMERIC:
result = cell.getNumericCellValue();
break;
case BOOLEAN:
result = cell.getBooleanCellValue();
break;
case FORMULA:
result = cell.getCellFormula();
break;
case ERROR:
result = cell.getErrorCellValue();
break;
case BLANK:
break;
default:
break;
}
}
if (result != "") {
data += result + "|";
}
}
if (data.length() > 0) {
System.out.println("sheet" + sheetName + "第" + (j + 1) + "行：" + data);
}
}
}
|
//获取单元格各类型值，返回字符串类型
public static String getCellValueByCell(Cell cell) {
//判断是否为null或空串
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
CellType cellType = cell.getCellType();
switch (cellType) {
case NUMERIC: // 数字
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
cellValue = sdf.format(date);
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
try {
cellValue = sdf.format(cell.getDateCellValue());// 日期
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
cellValue = cell.getCellFormula();
break;
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
|
https://blog.csdn.net/qq_38025219/article/details/82760471
// 创建一个 workbook 对象
Workbook workbook = new XSSFWorkbook();
// 创建一个 sheet
Sheet sheet = workbook.createSheet();
//创建一行
Row row = sheet.createRow((short) 1);
ellStyle style = workbook.createCellStyle();
//关键点 IndexedColors.AQUA.getIndex() 对应颜色
style.setFillForegroundColor(***IndexedColors.AQUA.getIndex()***);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X1");
cell.setCellStyle(style);
颜色
IndexedColors.AQUA.getIndex()
IndexedColors.AUTOMATIC.getIndex()
IndexedColors.BLUE.getIndex()
IndexedColors.BLUE_GREY.getIndex()
IndexedColors.BRIGHT_GREEN.getIndex()
IndexedColors.BROWN.getIndex()
IndexedColors.CORAL.getIndex()
IndexedColors.CORNFLOWER_BLUE.getIndex()
IndexedColors.DARK_BLUE.getIndex()
IndexedColors.DARK_GREEN.getIndex()
IndexedColors.DARK_RED.getIndex()
IndexedColors.DARK_TEAL.getIndex()
IndexedColors.DARK_YELLOW.getIndex()
IndexedColors.GOLD.getIndex()
IndexedColors.GREEN.getIndex()
IndexedColors.GREY_25_PERCENT.getIndex()
IndexedColors.GREY_40_PERCENT.getIndex()
IndexedColors.GREY_50_PERCENT.getIndex()
IndexedColors.GREY_80_PERCENT.getIndex()
IndexedColors.INDIGO.getIndex()
IndexedColors.LAVENDER.getIndex()
IndexedColors.LEMON_CHIFFON.getIndex()
IndexedColors.LIGHT_BLUE.getIndex()
IndexedColors.LEMON_CHIFFON.getIndex()
IndexedColors.LIGHT_BLUE.getIndex()
IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()
IndexedColors.LIGHT_GREEN.getIndex()
IndexedColors.LIGHT_ORANGE.getIndex()
IndexedColors.LIGHT_TURQUOISE.getIndex()
IndexedColors.LIGHT_YELLOW.getIndex()
IndexedColors.LIME.getIndex()
IndexedColors.MAROON.getIndex()
IndexedColors.OLIVE_GREEN.getIndex()
IndexedColors.ORANGE.getIndex()
IndexedColors.ORCHID.getIndex()
IndexedColors.PALE_BLUE.getIndex()
IndexedColors.PINK.getIndex()
IndexedColors.PLUM.getIndex()
IndexedColors.RED.getIndex()
IndexedColors.ROSE.getIndex()
IndexedColors.ROYAL_BLUE.getIndex()
IndexedColors.SEA_GREEN.getIndex()
IndexedColors.SKY_BLUE.getIndex()
IndexedColors.TAN.getIndex()
IndexedColors.TEAL.getIndex()
IndexedColors.TURQUOISE.getIndex()
IndexedColors.VIOLET.getIndex()
IndexedColors.WHITE.getIndex()
IndexedColors.YELLOW.getIndex()
|
合并表格导入参考
/**
* @author hjx
* TODO判断是否为合并单元格
* @method isMergedRegion
* @param sheet
* @param row
* @param column
* @return
* @return Boolean
* @date 2022年11月16日 下午2:18:44
*/
private Boolean isMergedRegion(Sheet sheet, int row, int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row>=firstRow&&row<=lastRow) {
if (column>=firstColumn&&column<=lastColumn) {
return true;
}
}
}
return false;
}
/**
* @author hjx
* TODO获取合并单元格的值
* @method getMergedRegionValue
* @param sheet
* @param row
* @param column
* @return
* @return String
* @date 2022年11月16日 下午2:18:44
*/
public String getMergedRegionValue(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row xRow = sheet.getRow(firstRow);
Cell xCell = xRow.getCell(firstColumn);
return xCell.getStringCellValue();
}
}
}
return null ;
}
代码参考
夏新bom 维护导入
@Override
@Transactional
public ResultData uploadBackup(MultipartFile file, BomMaster entity) {
if (null==file){
return ResultData.fail("文件不能为空");
}
try{
Workbook book;
//使用它来执行计算公式
FormulaEvaluator formulaEvaluator;
String fileType = Objects.requireNonNull(file.getOriginalFilename(), "文件名不能为空").
substring(file.getOriginalFilename().lastIndexOf(".") + 1);
if ("xls".equalsIgnoreCase(fileType)){
book=new HSSFWorkbook(file.getInputStream());//2003版本office
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) book);
}else{
book=new XSSFWorkbook(file.getInputStream());//判断是否为2007版本office
formulaEvaluator =new XSSFFormulaEvaluator((XSSFWorkbook) book);
}
ExcelUtil eu = new ExcelUtil();
List<Row> rows = eu.readExcel(book);
String[] cloums={"序号","工序","类别","新编码","名称","规格描述","用量","损耗率"};
for (int i =0; i < cloums.length ; i++){
if (!cloums[i].equals(rows.get(0).getCell(i).getStringCellValue())){
return ResultData.fail("表头的第"+(i+1)+"列与模板不一致，请核对");
}
}
StringBuilder message =new StringBuilder();
ArrayList<BomDetail> bomDetailList = new ArrayList<>();
ArrayList<BomReplace> bomReplaceList =new ArrayList<>();
//给BomMaster赋值
BomMaster bomMaster = new BomMaster();
bomMaster.setBomCode(entity.getBomCode());
bomMaster.setModelCode(entity.getModelCode());
bomMaster.setCreateUser(UserUtils.getUsername());
bomMaster.setEdition(entity.getEdition());
bomMaster.setCustomerCode(entity.getCustomerCode());
bomMaster.setEditionDate(entity.getTime());
String materialCode ="";
for (int i =1 ; i < rows.size() ; i++){
short lastCellNum = rows.get(i).getLastCellNum();
if (lastCellNum>8){
return ResultData.fail("表格的格式有误，列数超过8列，请核对表格，并删除多出的列");
}
if (isMergedRegion(book.getSheetAt(0),i,0)){
rows.get(i).getCell(0).setCellType(CellType.STRING);
rows.get(i).getCell(0).setCellValue(getMergedRegionValue(book.getSheetAt(0), i, 0));
}
if (isMergedRegion(book.getSheetAt(0),i,4)){
String value = getMergedRegionValue(book.getSheetAt(0), i, 4);
rows.get(i).getCell(4).setCellValue(value);
}
String s = rows.get(i).getCell(0).toString();
rows.get(i).getCell(0).setCellType(CellType.STRING);
rows.get(i).getCell(0).setCellValue(s);
if ("A".equals(ExcelUtil.getCellValue(rows.get(i).getCell(2)))) {
BomDetail bomDetail = new BomDetail();
bomDetail.setBomCode(entity.getBomCode());
bomDetail.setCreateUser(UserUtils.getUsername());
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(0)))) {
message.append("第" + (i + 1) + "行的序号不能为空\n");
} else {
int sno;
try{
sno = new BigDecimal(formulaEvaluator.evaluate(rows.get(i).getCell(0)).getStringValue()).intValue();
if (sno<=0){
message.append("第"+(i+1)+"行序号的数不能小于等于0\n");
}else{
bomDetail.setSno(sno);
}
}catch (Exception exception){
exception.printStackTrace();
message.append("第"+(i+1)+"行序号的数值格式有误\n");
}
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(1)))) {
message.append("第" + (i + 1) + "行的工序不能为空\n");
} else {
bomDetail.setParentProcess(ExcelUtil.getCellValue(rows.get(i).getCell(1)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(2)))) {
message.append("第" + (i + 1) + "行的类别不能为空\n");
} else {
bomDetail.setTypeclass(ExcelUtil.getCellValue(rows.get(i).getCell(2)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(3)))) {
message.append("第" + (i + 1) + "行的新编码不能为空\n");
} else {
if (rows.get(i).getCell(3).getCellType().equals(CellType.NUMERIC)){
bomDetail.setMaterialCode(NumberToTextConverter.toText(rows.get(i).getCell(3).getNumericCellValue()));
}else {
bomDetail.setMaterialCode(ExcelUtil.getCellValue(rows.get(i).getCell(3)));
}
bomDetail.setMaterialCode(bomDetail.getMaterialCode().trim());
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(4)))) {
message.append("第" + (i + 1) + "行的名称不能为空\n");
} else {
bomDetail.setMaterialName(ExcelUtil.getCellValue(rows.get(i).getCell(4)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(5)))) {
message.append("第" + (i + 1) + "行的规格描述不能为空\n");
} else {
bomDetail.setSpecifications(ExcelUtil.getCellValue(rows.get(i).getCell(5)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(6)))) {
message.append("第" + (i + 1) + "行的用量不能为空\n");
} else {
double useQty;
try{
useQty = formulaEvaluator.evaluate(rows.get(i).getCell(6)).getNumberValue();
if (useQty <=0){
message.append("第" + (i + 1) + "行的用量不能小于等于0\n");
}else{
bomDetail.setUseQty(useQty);
}
}catch (Exception exception){
exception.printStackTrace();
message.append("第" + (i + 1) + "行的用量的值格式有误\n");
}
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(7)))) {
message.append("第" + (i + 1) + "行的损耗率不能为空\n");
} else {
double loss;
try {
loss = formulaEvaluator.evaluate(rows.get(i).getCell(7)).getNumberValue();
if (loss<0){
message.append("第" + (i + 1) + "行的损耗率的值不能小于0\n");
}else {
bomDetail.setLossRate(loss);
}
}catch (Exception ex){
ex.printStackTrace();
message.append("第" + (i + 1) + "行的损耗率的值格式有误\n");
}
}
bomDetailList.add(bomDetail);
materialCode=ExcelUtil.getCellValue(rows.get(i).getCell(3));
}else{
BomReplace bomReplace = new BomReplace();
bomReplace.setBomCode(entity.getBomCode());
bomReplace.setMaterialCode(materialCode.trim());
bomReplace.setCreateUser(UserUtils.getUsername());
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(0)))) {
message.append("第" + (i + 1) + "行的序号不能为空\n");
} else {
int sno;
try{
sno = new BigDecimal(formulaEvaluator.evaluate(rows.get(i).getCell(0)).getStringValue()).intValue();
if (sno<=0){
message.append("第"+(i+1)+"行序号的数值不能小于等于0\n");
}else{
bomReplace.setSno(sno);
}
}catch (Exception exception){
exception.printStackTrace();
message.append("第"+(i+1)+"行序号的数值格式有误\n");
}
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(1)))) {
message.append("第" + (i + 1) + "行的工序不能为空\n");
} else {
bomReplace.setParentProcess(ExcelUtil.getCellValue(rows.get(i).getCell(1)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(2)))) {
message.append("第" + (i + 1) + "行的类别不能为空\n");
} else {
bomReplace.setTypeclass(ExcelUtil.getCellValue(rows.get(i).getCell(2)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(3)))) {
message.append("第" + (i + 1) + "行的新编码不能为空\n");
} else {
if (rows.get(i).getCell(3).getCellType().equals(CellType.NUMERIC)){
bomReplace.setMaterialReplace(NumberToTextConverter.toText(rows.get(i).getCell(3).getNumericCellValue()));
}else {
bomReplace.setMaterialReplace(ExcelUtil.getCellValue(rows.get(i).getCell(3)));
}
bomReplace.setMaterialReplace(bomReplace.getMaterialReplace().trim());
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(4)))) {
message.append("第" + (i + 1) + "行的名称不能为空\n");
} else {
bomReplace.setMaterialName(ExcelUtil.getCellValue(rows.get(i).getCell(4)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(5)))) {
message.append("第" + (i + 1) + "行的规格描述不能为空\n");
} else {
bomReplace.setSpecifications(ExcelUtil.getCellValue(rows.get(i).getCell(5)));
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(6)))) {
message.append("第" + (i + 1) + "行的用量不能为空\n");
} else {
double useQty;
try{
useQty = formulaEvaluator.evaluate(rows.get(i).getCell(6)).getNumberValue();
if (useQty <=0){
message.append("第" + (i + 1) + "行的用量的值不能小于等于0\n");
}else{
bomReplace.setUseQty(useQty);
}
}catch (Exception exception){
exception.printStackTrace();
message.append("第" + (i + 1) + "行的用量数值格式有误\n");
}
}
if ("".equals(ExcelUtil.getCellValue(rows.get(i).getCell(7)))) {
message.append("第" + (i + 1) + "行的损耗率不能为空\n");
} else {
double loss;
try {
loss = formulaEvaluator.evaluate(rows.get(i).getCell(7)).getNumberValue();
if (loss<0){
message.append("第" + (i + 1) + "行的损耗率为的值不能小于0\n");
}else {
bomReplace.setLossRate(loss);
}
}catch (Exception ex){
ex.printStackTrace();
message.append("第" + (i + 1) + "行的损耗率为的值格式有误\n");
}
}
bomReplaceList.add(bomReplace);
}
}
if (message.length()>0){
return ResultData.fail(message.toString());
}
BomMaster rowData = bomManagementMapper.getRowData(bomMaster);
if(rowData!=null){
bomManagementMapper.deleteRowBom(rowData);
BomDetail temp1=new BomDetail();
temp1.setBomCode(entity.getBomCode());
bomManagementMapper.deleteBomDetail(temp1);
BomReplace temp2=new BomReplace();
temp2.setBomCode(entity.getBomCode());
bomManagementMapper.deleteBomReplace(temp2);
bomManagementMapper.insertBomMaster(bomMaster);
if(bomDetailList.size() >0) {
bomManagementMapper.insertBomDetailBackup(bomDetailList);
}
if(bomReplaceList.size()>0) {
bomManagementMapper.insertBomReplaceBackup(bomReplaceList);
}
}else {
bomManagementMapper.insertBomMaster(bomMaster);
if(bomDetailList.size()>0) {
bomManagementMapper.insertBomDetailBackup(bomDetailList);
}
if(bomReplaceList.size()>0) {
bomManagementMapper.insertBomReplaceBackup(bomReplaceList);
}
}
}catch (Exception exception){
exception.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return ResultData.fail("在导入过程中出现了问题，请核对表格");
}
return ResultData.succeed("导入成功");
}
|
自定义导出excel参考
@Override
public ResponseEntity<byte[]> derive(ScheduleMaster scheduleMaster) {
List<Map<String, String>> dateList = new ArrayList<>();
List<Map<String, Object>> tableDataList = new ArrayList<>();
List<Map<String, Object>> dateDataList = new ArrayList<>();
List<Map<String, Object>> beforeDateDataList = new ArrayList<>();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String[] weekDays = {"星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六"};
String sqlString = ",";
//选择的时间
String selectTime = scheduleMaster.getSelectTime();
Date parse = null;
try {
parse = simpleDateFormat.parse(selectTime);
} catch (ParseException e) {
e.printStackTrace();
}
Calendar instance = Calendar.getInstance();
instance.setTime(parse);
//选择的时间的后7天
instance.add(Calendar.DATE, 7);
Date endSelectTime = instance.getTime();
scheduleMaster.setEndSelectTime(simpleDateFormat.format(endSelectTime));
//选择的时间的后7天
instance.add(Calendar.DATE, -14);
Date startSelectTime = instance.getTime();
scheduleMaster.setStartSelectTime(simpleDateFormat.format(startSelectTime));
while (instance.getTime().compareTo(endSelectTime) <= 0) {
instance.add(Calendar.DATE, 1);
HashMap<String, String> dateMap = new HashMap<>();
dateMap.put("dateTime", simpleDateFormat.format(instance.getTime()));
sqlString = sqlString + "'' as '" + simpleDateFormat.format(instance.getTime()) + "',";
dateMap.put("weekDate", weekDays[instance.get(Calendar.DAY_OF_WEEK) - 1]);
dateList.add(dateMap);
}
String sqlDateString = "";
if (sqlString.length() > 1) {
sqlDateString = sqlString.substring(0, sqlString.length() - 1);
}
scheduleMaster.setSqlDateString(sqlDateString);
dateDataList = scheduleMasterMapper.getPlannedShipmentDetailsBySelectTimeProductionPLanCapacity(scheduleMaster);
tableDataList = scheduleMasterMapper.getPlannedShipmentDetails(scheduleMaster);
beforeDateDataList = scheduleMasterMapper.getPlannedShipmentDetailsBySelectTimeSmtProductTransaction(scheduleMaster);
if (!tableDataList.isEmpty()) {
for (int i = 0; i < tableDataList.size(); i++) {
Map<String, Object> map1 = tableDataList.get(i);
for (Map<String, String> date : dateList) {
String dateTime = date.get("dateTime");
List<Map<String, Object>> collect = dateDataList.stream().filter(h -> h.get("schedule_code").equals(map1.get("schedule_code"))
&& h.get("work_date").equals(dateTime)).collect(Collectors.toList());
if (null != collect && collect.size() > 0) {
tableDataList.get(i).remove(dateTime);
tableDataList.get(i).put(dateTime, String.valueOf(collect.get(0).get("plan_output_qty")));
} else {
List<Map<String, Object>> collect1 = beforeDateDataList.stream().filter(h -> h.get("schedule_code").equals(map1.get("schedule_code"))
&& h.get("create_time").equals(dateTime)).collect(Collectors.toList());
if (null != collect1 && collect1.size() > 0) {
tableDataList.get(i).remove(dateTime);
tableDataList.get(i).put(dateTime, String.valueOf(collect1.get(0).get("t_qty")));
}
}
}
}
}
try {
String fileName = "计划出货报表";
//1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("计划出货报表");
//设置列宽
sheet.setDefaultColumnWidth(10);
//固定9列 + 时间的列数
String[] columnNames = {"状态", "产品型号", "批号", "产品编号", "设计号", "客户", "工单量", "出货数量", "未出货数量"};
//处理列的条数和数据
List<String> dateTimeList = dateList.stream().map(h -> h.get("dateTime")).collect(Collectors.toList());
//String[] dateList = {"2023-04-16", "2023-04-17", "2023-04-18", "2023-04-19", "2023-04-20", "2023-04-21", "2023-04-22", "2023-04-23", "2023-04-24", "2023-04-25", "2023-04-26", "2023-04-27", "2023-04-28", "2023-04-29", "2023-04-30"};
//String[] weekList = {"星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期日"};
List<String> weekList = dateList.stream().map(h -> h.get("weekDate")).collect(Collectors.toList());
List<String> columnList = new ArrayList<>();
columnList.addAll(Arrays.asList(columnNames));
columnList.addAll(dateTimeList);
//设置列长（原来固定的加上动态时间的）
//已开始固定的字段9个（状态 产品型号 批号 产品编号 设计号 客户 工单量 出货数量 未出货数量）
//假设为22
int dataSize = columnList.size();
//1.1创建合并单元格对象
CellRangeAddress cellAddressesHead = new CellRangeAddress(0, 2, 0, dataSize - 1);
//合并表格
sheet.addMergedRegion(cellAddressesHead);
//创建标题样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
//居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
//背景颜色
titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
//填充方式，默认为NO_FILL，所以就算上面set了背景颜色，下面没设置填充色也没效果
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置字体样式
HSSFFont titleFont = workbook.createFont();
//加粗
titleFont.setBold(true);
//字体大小
titleFont.setFontHeight((short) 500);
//将字体赋值给样式
titleStyle.setFont(titleFont);
//设置表列头的样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 230);
font.setBold(true);
cellStyle.setFont(font);
//编写第一行
HSSFRow titleRow = sheet.createRow(0);
HSSFCell cell = titleRow.createCell(0);
cell.setCellStyle(titleStyle);
cell.setCellValue("计划出货报表");
HSSFRow columnRowZero = sheet.createRow(3);
HSSFCellStyle cellStyleZero = workbook.createCellStyle();
cellStyleZero.setAlignment(HorizontalAlignment.CENTER);
cellStyleZero.setVerticalAlignment(VerticalAlignment.CENTER);
CellRangeAddress cellAddressesZero = new CellRangeAddress(3, 3, 6, 8);
sheet.addMergedRegion(cellAddressesZero);
columnRowZero.createCell(6).setCellValue("AMOI-S-PM-003B V1.0");
columnRowZero.getCell(6).setCellStyle(cellStyleZero);
HSSFRow columnRow = sheet.createRow(4);
columnRow.setHeight((short) 400);
HSSFRow columnRowSecond = sheet.createRow(5);
columnRowSecond.setHeight((short) 400);
for (int i = 0; i < columnList.size(); i++) {
if (i < 9) {
CellRangeAddress cellAddresses = new CellRangeAddress(4, 5, i, i);
sheet.addMergedRegion(cellAddresses);
columnRow.createCell(i).setCellValue(columnNames[i]);
columnRow.getCell(i).setCellStyle(cellStyle);
} else {
columnRow.createCell(i).setCellValue(dateTimeList.get(i-9));
columnRowSecond.createCell(i).setCellValue(weekList.get(i-9));
}
}
for (int i = 6; i < tableDataList.size() + 6; i++) {
HSSFRow row = sheet.createRow(i);
row.createCell(0).setCellValue(tableDataList.get(i - 6).get("status").toString());
row.createCell(1).setCellValue(tableDataList.get(i - 6).get("model_code").toString());
row.createCell(2).setCellValue(tableDataList.get(i - 6).get("schedule_code").toString());
row.createCell(3).setCellValue(tableDataList.get(i - 6).get("material_code").toString());
row.createCell(4).setCellValue(tableDataList.get(i - 6).get("design_code")==null?"":tableDataList.get(i - 6).get("design_code").toString());
row.createCell(5).setCellValue(tableDataList.get(i - 6).get("to_customer").toString());
row.createCell(6).setCellValue(String.valueOf(tableDataList.get(i - 6).get("schedule_qty")));
row.createCell(7).setCellValue(String.valueOf(tableDataList.get(i - 6).get("shipment_qty")));
row.createCell(8).setCellValue(String.valueOf(tableDataList.get(i - 6).get("not_shipment_qty")));
for(int s=0 ; s<dateTimeList.size();s++){
row.createCell(s+9).setCellValue(String.valueOf(tableDataList.get(i - 6).get(dateTimeList.get(s))));
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
// 设置请求头内容,告诉浏览器代开下载窗口
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
ResponseEntity<byte[]> responseEntity = new ResponseEntity<byte[]>(os.toByteArray(), headers, HttpStatus.OK);
return responseEntity;
} catch (Exception exception) {
exception.printStackTrace();
}
return null;
}
|
Java读取Excel表格中的图片_java读取excel中的图片-CSDN博客
读取图片
不同版本的excel有不同的拓展名（.[xls](https://so.csdn.net/so/search?q=xls&spm=1001.2101.3001.7020) .xlsx），对应不同的方法。
所有的图片在excel中保存在一个集合中，以行列号为key，图片为value，将结果封装到map集合中，图片的格式为“HSSFPictureData”“XSSFPictureData”。
|
/**
* 获取图片和位置 (xls)
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, HSSFPictureData> getPictures (HSSFSheet sheet) throws IOException {
Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = picture.getClientAnchor();
HSSFPictureData pdata = picture.getPictureData();
String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
map.put(key, pdata);
}
}
return map;
}
/**
* 获取图片和位置 (xlsx)
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, XSSFPictureData> getPictures (XSSFSheet sheet) throws IOException {
Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
map.put(key, picture.getPictureData());
}
}
}
return map;
}
|
从map拿到图片使用输出流将图片保存在本地
PictureData picData = map.get(hang-1+":"+20);
String ext = picData.suggestFileExtension();//获取图片的后缀名
byte[] data = picData.getData();
String fileName = UUID.randomUUID().toString();
//文件夹路径：temporarypath
String temporarypath = FtpPropertiesLoader.get("star");
FileOutputStream out = new FileOutputStream(star + fileName + ext);
out.write(data);
out.close();
|
