要被打死的，一个Excel的导出我写了一百多行代码

最新推荐文章于 2024-05-10 17:51:50 发布

abc_ABC123A

最新推荐文章于 2024-05-10 17:51:50 发布

阅读量375

点赞数

分类专栏： poi 文章标签： poi Excel导出

本文链接：https://blog.csdn.net/abc_ABC123A/article/details/82787645

版权

poi 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

反正都要被打死，所以把这个例子记下来吧，一个学习的辛苦历程!

/**
	 * 导出Excel
	 * @param request 请求对象
	 * @param response 相应对象
	 * @return
	 * @throws Exception 导出异常
	 */
	@GetMapping("/download")
	public RestResult downloadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
		//初始整个Excel
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		//创建一个表格
		HSSFSheet createSheet = hssfWorkbook.createSheet("药品信息数据表");
		//设置单元格风格
		 PoiUtil poiUtil = new PoiUtil();
		//设置标题风格
        HSSFCellStyle cs = hssfWorkbook.createCellStyle();
        //设置单元格
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        //设置字体
        HSSFFont createFont = hssfWorkbook.createFont();
        createFont.setFontHeight((short)300);
        createFont.setFontName("黑体");
        createFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        createFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
        cs.setFont(createFont);
       //设置一级单元格风格
        HSSFCellStyle cs1 = hssfWorkbook.createCellStyle();
        cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cs1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cs1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        HSSFFont createFont1 = hssfWorkbook.createFont();
        createFont1.setFontHeight((short)260);
        createFont1.setFontName("宋体");
        cs1.setFont(createFont1);
        //设置二级单元格风格
        HSSFCellStyle cs2 = hssfWorkbook.createCellStyle();
        cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        HSSFFont createFont2 = hssfWorkbook.createFont();
        createFont2.setFontHeight((short)220);
        createFont2.setFontName("宋体");
        cs2.setFont(createFont2);
		//获取数据库的数据确定需要多少行
		List<DrugInfo> findAll = drugInfoService.findAll();
		HSSFRow createRowTitle = createSheet.createRow(1);
		//设置高宽40px
		createRowTitle.setHeight((short)(15.625*40));
		createRowTitle.setHeightInPoints(30f);
		for (int i = 2; i <7; i++) {
			HSSFCell createCell1 = createRowTitle.createCell(i);
			createCell1.setCellStyle(cs);
			createCell1.setCellValue("药品信息数据表");
			createSheet.autoSizeColumn(i);
			createSheet.addMergedRegion(new Region(1, (short)2, 1, (short)6));
			//createSheet.addMergedRegion(new CellRangeAddress(0,0,2,7));
		}
		
		HSSFRow createRowTop = createSheet.createRow(2);
		createRowTop.setHeight((short)(15.625*25));
		createRowTop.setHeightInPoints(20f);
		HSSFCell createCell2 = createRowTop.createCell(2);
		createCell2.setCellValue("药品状态");
		createCell2.setCellStyle(cs1);
		createSheet.autoSizeColumn(2);
		HSSFCell createCell3 = createRowTop.createCell(3);
		createCell3.setCellValue("批准文号");
		createCell3.setCellStyle(cs1);
		createSheet.autoSizeColumn(3);
		HSSFCell createCell4 = createRowTop.createCell(4);
		createCell4.setCellValue("商品名称(中)");
		createCell4.setCellStyle(cs1);
		createSheet.autoSizeColumn(4);
		HSSFCell createCell5 = createRowTop.createCell(5);
		createCell5.setCellValue("商品名称(中)");
		createCell5.setCellStyle(cs1);
		createSheet.autoSizeColumn(5);
		HSSFCell createCell6 = createRowTop.createCell(6);
		createCell6.setCellValue("规格");
		createCell6.setCellStyle(cs1);
		createSheet.autoSizeColumn(6);
		//创建行
		for (int rowIndex = 0; rowIndex < findAll.size(); rowIndex++) {
			HSSFRow createRow = createSheet.createRow(rowIndex+3);
			DrugInfo drugInfo = findAll.get(rowIndex);
			createRow.setHeight((short)(15.625*30));
			createRow.setHeightInPoints(20f);
			//创建列
			for (int columnIndex = 0; columnIndex < 7; columnIndex++) {
				HSSFCell createCell = createRow.createCell(columnIndex);
				//设置风格
				switch (columnIndex) {
				case 2:
					createCell.setCellValue(drugInfo.getProductionStatus());
					createCell.setCellStyle(cs2);
					createSheet.autoSizeColumn(2);
					break;
				case 3:
					createCell.setCellValue(drugInfo.getLicence());
					createCell.setCellStyle(cs2);
					createSheet.autoSizeColumn(3);
					break;
				case 4:
					createCell.setCellValue(drugInfo.getDrugName());
					createCell.setCellStyle(cs2);
					createSheet.autoSizeColumn(4);
					break;
				case 5:
					createCell.setCellValue(drugInfo.getDrugProductName());
					createCell.setCellStyle(cs2);
					createSheet.autoSizeColumn(5);
					break;
				case 6:
					createCell.setCellValue(drugInfo.getDrugSpec());
					createCell.setCellStyle(cs2);
					createSheet.autoSizeColumn(6);
					break;
				}
				
			}
		}
		// 告诉浏览器用什么软件可以打开此文件
		response.setHeader("content-Type", "application/vnd.ms-excel");
		// 下载文件的默认名称
		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("药品信息数据表","UTF-8") + ".xls");
		//编码
		response.setCharacterEncoding("UTF-8");
		hssfWorkbook.write(response.getOutputStream());

			/*ExportParams params = new ExportParams() ;  
    	 params.setSheetName("第一页"); ;  
         params.setTitle("药品信息数据");
         params.setSecondTitle("导出日期："+new Date());
    	// 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("药品信息数据表","UTF-8") + ".xls");
        //编码
        response.setCharacterEncoding("UTF-8");

        List<DrugInfo> findAll = drugInfoService.findAll();
        Workbook workbook = ExcelExportUtil.exportExcel(params, DrugInfo.class, findAll);
        CellStyle createCellStyle = workbook.createCellStyle();
        createCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);

        workbook.write(response.getOutputStream());*/
			return ResultGenerator.genSuccessResult().setMessage("导出成功");
		}

代码还未做优化，下次要做poi导出就简单多了！