<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
XSSFWorkbook workbook = new XSSFWorkbook("D:\\hello.xlsx");
XSSFSheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
String value = cell.getStringCellValue();
System.out.println(value);
}
}
workbook.close();
XSSFWorkbook workbook = new XSSFWorkbook("D:\\hello.xlsx");
XSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
for(int i=0;i<=lastRowNum;i++){
XSSFRow row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
for(short j=0;j<lastCellNum;j++){
String value = row.getCell(j).getStringCellValue();
System.out.println(value);
}
}
workbook.close();
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("sheet");
XSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("名称");
row.createCell(2).setCellValue("年龄");
XSSFRow row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("1");
row1.createCell(1).setCellValue("小明");
row1.createCell(2).setCellValue("10");
XSSFRow row2 = sheet.createRow(2);
row2.createCell(0).setCellValue("2");
row2.createCell(1).setCellValue("小王");
row2.createCell(2).setCellValue("20");
FileOutputStream out = new FileOutputStream("D:\\itcast.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();
@RequestMapping("/exportBusinessReport")
public void exportBusinessReport(HttpServletRequest request, HttpServletResponse response){
try {
List<TbGoods> goodsList = goodsService.findAll();
String temlateRealPath = request.getSession().getServletContext().getRealPath("template") +
File.separator + "goods_template.xlsx";
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(temlateRealPath)));
XSSFSheet sheet = workbook.getSheetAt(0);
int rowNum=2;
for (TbGoods goods : goodsList) {
XSSFRow row = sheet.getRow(rowNum);
row.getCell(0).setCellValue(goods.getId());
row.getCell(1).setCellValue(goods.getSellerId());
row.getCell(2).setCellValue(goods.getGoodsName());
Long defaultItemId = goods.getDefaultItemId();
String defaultItemIdStr="";
if (defaultItemId != null) {
defaultItemIdStr=defaultItemId+"";
}
row.getCell(3).setCellValue(defaultItemIdStr);
row.getCell(4).setCellValue(goods.getAuditStatus());
row.getCell(5).setCellValue(goods.getIsMarketable());
Long brandId = goods.getBrandId();
TbBrand brand = brandService.getById(brandId);
if (brand != null) {
row.getCell(6).setCellValue(brand.getName());
}
row.getCell(7).setCellValue(goods.getCaption());
Long category1Id = goods.getCategory1Id();
TbItemCat itemCat1 = itemCatService.getById(category1Id);
if (itemCat1 != null) {
row.getCell(8).setCellValue(itemCat1.getName());
}
Long category2Id = goods.getCategory2Id();
TbItemCat itemCat2 = itemCatService.getById(category2Id);
if (itemCat2 != null) {
row.getCell(9).setCellValue(itemCat2.getName());
}
Long category3Id = goods.getCategory3Id();
TbItemCat itemCat3 = itemCatService.getById(category3Id);
if (itemCat3 != null) {
row.getCell(10).setCellValue(itemCat3.getName());
}
row.getCell(11).setCellValue(goods.getSmallPic());
BigDecimal price = goods.getPrice();
if (price != null) {
row.getCell(12).setCellValue(price.doubleValue());
}
Long typeTemplateId = goods.getTypeTemplateId();
TbTypeTemplate template = typeTemplateService.getById(typeTemplateId);
if (template != null) {
row.getCell(13).setCellValue(template.getName());
}
row.getCell(14).setCellValue(goods.getIsEnableSpec());
row.getCell(15).setCellValue(goods.getIsDelete());
rowNum++;
}
ServletOutputStream out = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=goods_report.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@RequestMapping("/upload")
public Result upload(MultipartFile excelFile, Long parentId) {
try {
List<String[]> list = readExcel(excelFile);
List<TbItemCat> itemCatList = new ArrayList<>();
if (list != null && list.size() > 0) {
for (String[] row : list) {
String name = row[0];
if (name == null) {
continue;
}
TbItemCat itemCat = new TbItemCat();
itemCat.setName(name);
String typeName = row[1];
TbTypeTemplate typeTemplate = typeTemplateService.findByName(typeName);
itemCat.setTypeId(typeTemplate.getId());
itemCat.setParentId(parentId);
itemCatList.add(itemCat);
}
}
itemCatService.addList(itemCatList);
return new Result(true, "上传成功");
} catch (Exception e) {
e.printStackTrace();
return new Result(false, "上传失败");
}
}
private List<String[]> readExcel(MultipartFile file) throws IOException {
POIUtils.checkFile(file);
Workbook workbook = POIUtils.getWorkBook(file);
List<String[]> list = new ArrayList<String[]>();
if (workbook != null) {
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = firstRowNum + 3; rowNum <= lastRowNum; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
cells[cellNum] = POIUtils.getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
<el-upload :action="'/itemCat/upload.do?parentId='+parentId"
name="excelFile"
:show-file-list="false"
:on-success="handleSuccess"
:before-upload="beforeUpload">
<el-button size="small">导入规格数据</el-button>
</el-upload>
handleSuccess(response, file) {
if(response.success){
this.$message({
message: response.message,
type: 'success'
});
}else{
this.$message.error(response.message);
}
this.findByParentId2(this.parentId);
},
beforeUpload(file){
var FilenameExtension=file.name.substr(file.name.lastIndexOf("."));
const isXLS=FilenameExtension==='.xls';
if(isXLS){
return true;
}
const isXLSX=FilenameExtension==='.xlsx';
if(isXLSX){
return true;
}
this.$message.error('上传文件只能是xls或者xlsx格式!');
return false;
}