大家好，我是Java Spring Boot的初学者。我想从excel中读取数据。数据中包含文本和图像都包含。我已经从excelt 和中读取了文本并将文本成功插入db 中，但是我无法读取该图像，我必须将图像插入db。我在下面附加了错误，任何人都可以给我解决方案吗？任何帮助将不胜感激。谢谢。
图片 :
@RequestMapping("uploadQuestion")
public String uploadQuestion(@RequestParam("file") MultipartFile file, @RequestParam Long examId, Model model,
HttpSession session) throws IOException, InvalidFormatException {
int flag = 0;
String id = (String) session.getAttribute("userId");
model.addAttribute("examList", onlineExamMasterRepository.findAll());
DataFormatter formatter = new DataFormatter();
List<OnlineExamQuestionMaster> quetions = new ArrayList<OnlineExamQuestionMaster>();
List<OnlineExamOptionMaster> options = new ArrayList<OnlineExamOptionMaster>();
if (file.isEmpty()) {
model.addAttribute("info", "Please select a file to upload");
return "onlinexam/questionUpload :: section";
}
InputStream in = file.getInputStream();
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheetAt(0);
Row row;
System.out.println(sheet.getLastRowNum());
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
OnlineExamQuestionMaster qm = new OnlineExamQuestionMaster();
OnlineExamQuestionMasterPK qmp = new OnlineExamQuestionMasterPK();
OnlineExamOptionMasterPK omp[] = new OnlineExamOptionMasterPK[4];
OnlineExamOptionMaster om[] = new OnlineExamOptionMaster[4];
qmp.setExamId(examId);
qm.setLogTimestamp(new Date());
qm.setLogUserid(id);
flag++;
row = (Row) sheet.getRow(i);
System.out.println(row.getCell(0).toString());
if (row.getCell(0).toString().equals(null)) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
qmp.setQuestionId(Long.parseLong(formatter.formatCellValue(row.getCell(0))));
if (onlineExamQuestionMasterRepository.exists(qmp)) {
model.addAttribute("message", "Already QuestionId with " + formatter.formatCellValue(row.getCell(0))
+ " Exist for ExamId " + examId);
return "onlinexam/questionUpload :: section";
}
}
if (row.getCell(1).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else
{
row = (Row) sheet.getRow(i);
Iterator<Cell> iterator2 = row.cellIterator();
/*XSSFWorkbook workbook2 = sheet.getWorkbook();
List<XSSFPictureData> pictures = workbook2.getAllPictures();
Iterator<XSSFPictureData> iterator = pictures.iterator();*/
while(iterator2.hasNext())
{
PictureData pictureData = (PictureData)iterator2.next();
String fileextension = pictureData.suggestFileExtension();
byte[] data = pictureData.getData();
if(fileextension.equals("jpeg"))
{
qm.setImage(data);;
}
else
qm.setQidDescription(row.getCell(1).toString().trim());
}
}
if (row.getCell(2).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
omp[0] = new OnlineExamOptionMasterPK();
om[0] = new OnlineExamOptionMaster();
omp[0].setQid(Long.parseLong(formatter.formatCellValue(row.getCell(0))));
omp[0].setOptionId("A");
omp[0].setExamId(examId);
om[0].setLogTimestamp(new Date());
om[0].setLogUserid(id);
om[0].setOptionDesc(row.getCell(2).toString().trim());
om[0].setId(omp[0]);
}
if (row.getCell(3).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
omp[1] = new OnlineExamOptionMasterPK();
om[1] = new OnlineExamOptionMaster();
omp[1].setExamId(examId);
omp[1].setQid(Long.parseLong(formatter.formatCellValue(row.getCell(0))));
omp[1].setOptionId("B");
om[0].setLogTimestamp(new Date());
om[0].setLogUserid(id);
om[1].setOptionDesc(row.getCell(3).toString().trim());
om[1].setId(omp[1]);
}
if (row.getCell(4).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
omp[2] = new OnlineExamOptionMasterPK();
om[2] = new OnlineExamOptionMaster();
omp[2].setExamId(examId);
omp[2].setQid(Long.parseLong(formatter.formatCellValue(row.getCell(0))));
omp[2].setOptionId("C");
om[0].setLogTimestamp(new Date());
om[0].setLogUserid(id);
om[2].setOptionDesc(row.getCell(4).toString().trim());
om[2].setId(omp[2]);
}
if (row.getCell(5).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
omp[3] = new OnlineExamOptionMasterPK();
om[3] = new OnlineExamOptionMaster();
omp[3].setExamId(examId);
omp[3].setQid(Long.parseLong(formatter.formatCellValue(row.getCell(0))));
omp[3].setOptionId("D");
om[0].setLogTimestamp(new Date());
om[0].setLogUserid(id);
om[3].setOptionDesc(row.getCell(5).toString().trim());
om[3].setId(omp[3]);
}
if (row.getCell(6).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
qm.setAnswer(row.getCell(6).toString().toUpperCase().trim());
}
if (row.getCell(7).toString().equals("")) {
model.addAttribute("info", "Some columns are null please check and try");
return "onlinexam/questionUpload :: section";
} else {
qm.setMarks(Long.parseLong(formatter.formatCellValue(row.getCell(7))));
}
qm.setId(qmp);
quetions.add(qm);
options.addAll(Arrays.asList(om));
}
for (OnlineExamQuestionMaster h : quetions) {
onlineExamQuestionMasterRepository.save(h);
}
System.out.println(options.size());
for (OnlineExamOptionMaster h : options) {
System.out.println(h.toString());
onlineExamOptionMasterRepository.save(h);
}
model.addAttribute("info", flag + "Questions Uploaded Sucessfully");
return "onlinexam/questionUpload :: section";
}
] 3] 3
最佳答案
图片不是Excel中的单元格内容。它们将鼠标悬停在图纸上的单独的绘图层中(在XSSF情况下为XSSFDrawing)，并固定到单元格上。
因此，如果需要根据图片锚定的位置获取图片，那么我们需要
以下示例将执行此操作，并生成一个
Map，它将XSSFPicture映射到其位置。我得到了XSSFPicture，因为它们比单独的XSSFPictureData提供了更多的信息。而且XSSFPictureData可以很容易地从XSSFPicture中获得。import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.util.Map;
import java.util.HashMap;
public class ExcelGetPicturesWithPosition {
static Map<String, XSSFPicture> getPicturesWithPosition(XSSFSheet sheet) {
Map<String, XSSFPicture> pictures = new HashMap<String, XSSFPicture>();
XSSFDrawing drawing = sheet.getDrawingPatriarch();
for (XSSFShape shape : drawing.getShapes()) {
if (shape instanceof XSSFPicture) {
XSSFPicture picture = (XSSFPicture)shape;
XSSFClientAnchor anchor = picture.getClientAnchor();
String cellAddr = "R" + anchor.getRow1() + "C" + anchor.getCol1();
pictures.put(cellAddr, picture);
}
}
return pictures;
}
public static void main(String[] args) throws Exception {
XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("ExcelWithPictures.xlsx"));
XSSFSheet sheet = workbook.getSheetAt(0);
Map<String, XSSFPicture> pictures = getPicturesWithPosition(sheet);
System.out.println(pictures);
workbook.close();
}
}
一旦有了该地图，就很容易在迭代工作表的同时获取图片。例如，如果我们在
int row = 2; int column = 1;上:XSSFPicture picture = pictures.get("R"+row+"C"+column);