POI导出案例

1. 引用POI依赖

	<!-- 导出为Excel所需依赖 -->
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.9</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.9</version>
      </dependency>

　　2. 具体的实现

 

	/**
	 * POI方式导出
	 * @param list 数据
	 * @param exportFields 绑定列数组,0: 表头,1: 数据key
	 * @param response
	 */
	public static void exportExcel(List list,String[] exportFields,HttpServletResponse response) {


		Workbook wb = new XSSFWorkbook();
		//设置单元格式
		Font font = wb.createFont();
		font.setColor(Font.COLOR_NORMAL);
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setFont(font);
		Sheet sheet = wb.createSheet("测试数据");
		// 首行冻结
		sheet.createFreezePane( 0, 1, 0, 1 );

		String[]headers = exportFields[0].split(",");
		// 创建标题行
		Row titleRow = sheet.createRow(0);

		for (int i = 0; i < headers.length; i++) {
			Cell cell = titleRow.createCell(i);
			cell.setCellValue(headers[i]);
		}
		String[]keys = exportFields[1].split(",");
		for (int i = 0; i < list.size(); i++) {
			Row currentRow = sheet.createRow(i+1);
			// 获取当前行的数据
			Object o = list.get(i);
			for (int j = 0; j < keys.length; j++) {
				// 设置列宽
				// sheet.setColumnWidth(j, 4100);
				Cell cell = currentRow.createCell(j);
				cell.setCellStyle(cellStyle);
				//每个单元格的值目前做 String 处理
				// cell.setCellType(CellType.STRING);
				// cell.setCellValue(ReflectUtil.getFieldValue(o, keys[j]).toString());
				Object fieldValue = ReflectUtil.getFieldValue(o, keys[j]);
				if (ObjectUtil.isNotEmpty(fieldValue)){
					cell.setCellValue(fieldValue.toString());
				}

			}
		}
		// writeToExcel(response, wb, "导出数据列表");

		// 生成Excel文件
		try (FileOutputStream outputStream = new FileOutputStream("E:\\项目\\work\\宁波\\工作\\2024\\7\\导出\\data.xlsx")) {
			wb.write(outputStream);
			outputStream.flush();
		}catch (Exception e) {
			logger.error(e);
		}


	}

	public void writeToExcel(HttpServletResponse response, Workbook wb, String fileName) {
		OutputStream os = null;
		try {
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
			response.setHeader("Content-Disposition", String.format("attachment; filename=%s", new String(URLEncoder.encode(fileName + ".xlsx" , "UTF-8")
					.getBytes("UTF-8"), "UTF-8")));
			os = response.getOutputStream();
			wb.write(os);
			os.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void main(String[] args) {
		List<RegulatorySettlementReport> items = new ArrayList<>();
		// 表头+数据key对应,利用反射赋值
		String[] exportFields = {
				"结单状态,监管结算单号,结算发起时间,入库日期,出库日期,在库状态,仓库,货主,客户方,入库重量,入库金额,出库重量,出库金额,采销差价,铝拓服务费率,铝拓服务收益,采购付款日期,销售回款日期,入库码单号,出库单号,入库批号,品名,牌号,规格,厂家,重量,数量单位,重量单位,货物编号",
				"goodsAlledName,regulatorySettlementNo,settlementLaunchDate,inWhsDate,outWhsDate,stockStatusName,whsName,goodsOwnerName,customerName,inWhsAmount,inWhsMoney,outAmount,invyOutMoney,purchaseSalesPriceDiff,serviceRateName,serviceRevenue,purchasePaymentDate,salesCollectionDate,inWhsMdNo,outWhsNo,inWhsLotNo,brandName,texture,specification,producingName,invyOutAmount,quantityUnitName,weightUnitName,packageNo"
		};
		RegulatorySettlementReport report1 = new RegulatorySettlementReport();
		report1.setGoodsAlledName("未结单");
		report1.setRegulatorySettlementNo("001");
		items.add(report1);

		RegulatorySettlementReport report2 = new RegulatorySettlementReport();
		report2.setGoodsAlledName("已结单");
		report2.setRegulatorySettlementNo("002");
		items.add(report2);

		exportExcel(items,exportFields,null);
	}