Java导出excel文件的两种方法

最新推荐文章于 2025-07-17 15:50:36 发布

一线搬砖工

最新推荐文章于 2025-07-17 15:50:36 发布

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

文章标签： Java excel导出

本文链接：https://blog.youkuaiyun.com/weixin_41069590/article/details/80079591

本文介绍了一种通过Java实现的导出入库单详情的方法。该方法可以生成包含ICCID、MSISDN等信息的Excel表格，并支持两种方式：一种是在项目中生成临时文件后再提供下载，另一种是直接通过输出流输出文件。

在项目中生成临时文件的方法

Config.inReceiptFilePath.getValue()//文件路径

/**
 * 导出入库单详情
 * @param inReceiptId   入库单id
 * @param response
 * @param request
 * @throws Exception
 */
@RequestMapping("exportInReceipt")
@ResponseBody
public void exportPriceList(Integer inReceiptId, HttpServletResponse response, HttpServletRequest request) throws Exception {
   List<IotCard> iotCards = cardService.selectByInReceiptId(inReceiptId);
   IotInReceipt iotInReceipt = inReceiptService.selectById(inReceiptId);
   FileOutputStream outputStream = null;
   String tableName = "入库单明细";
   String filePath = Config.inReceiptFilePath.getValue() + File.separator/*+adminUser.getAdminName()*/ + "-"+iotInReceipt.getSerialNum()+ "-" + DateUtil.Date2String(new Date(), "yyyy-MM-dd") + tableName + ".xls";
   String showFileName = iotInReceipt.getSerialNum()+ "-" + DateUtil.Date2String(new Date(), "yyyy-MM-dd") + tableName + ".xls";
   try {
      //创建一个Excel工作簿对象
      outputStream = new FileOutputStream(filePath);
      //创建一个Excel工作簿对象
      HSSFWorkbook workbook = new HSSFWorkbook();
      //创建一个合并单元格做为表头根据下标合并单元格
      CellRangeAddress c1 = new CellRangeAddress(0, 0, 0, 9);
      //得到Excel工作表对象(sheet的名字为title的值)
      HSSFSheet sheet = workbook.createSheet(tableName);
      //创建单元格样式
      HSSFCellStyle style1 = workbook.createCellStyle();
      style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      //创建字体样式
      HSSFFont font1 = workbook.createFont();
      font1.setFontHeightInPoints((short) 18);
      //把字体赋予样式
      style1.setFont(font1);
      //创建单元格样式
      HSSFCellStyle style2 = workbook.createCellStyle();
      style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      //创建字体样式
      HSSFFont font2 = workbook.createFont();
      font2.setFontHeightInPoints((short) 12);
      //把字体赋予样式
      style2.setFont(font2);
      //将创建好的单元格赋予表1
      sheet.addMergedRegion(c1);
      //获取第一行
      HSSFRow row1 = sheet.createRow(0);
      //获取第一行中的第一个单元格
      HSSFCell cell = row1.createCell(0);
      //把样式赋予单元格
      cell.setCellStyle(style1);
      cell.setCellValue(iotInReceipt.getSerialNum() + "入库单明细");
      HSSFRow row2 = sheet.createRow(1);
      String[] titles = {"ICCID", "MSISDN", "运营商", "流量套餐", "供应商", "入库时间"};
      for (int i = 0; i < titles.length; i++) {
         String title = titles[i];
         HSSFCell cell2 = row2.createCell(i);
         cell2.setCellStyle(style2);
         cell2.setCellValue(title);

      }
      for (int j = 0; j < iotCards.size(); j++) {
         //输出list
         IotCard  card = iotCards.get(j);
         HSSFRow row = sheet.createRow(j + 2);
         HSSFCell cc1 = row.createCell(0);
         cc1.setCellValue(card.getIccid());
         HSSFCell cc2 = row.createCell(1);
         cc2.setCellValue(card.getMsisdn());
         HSSFCell cc3 = row.createCell(2);
         cc3.setCellValue(card.getOperatorLiteral());
         HSSFCell cc4 = row.createCell(3);
         cc4.setCellValue(card.getProductName());
         HSSFCell cc5 = row.createCell(4);
         cc5.setCellValue(card.getSupplyName());
         HSSFCell cc6 = row.createCell(5);
         cc6.setCellValue(card.getCreateTime().substring(0,19));
      }
      workbook.write(outputStream);

      FileUtil.downloadFile(response,filePath,showFileName);
   } catch (Exception e) {
      logger.error(e.getMessage(), e);
   } finally {
      try {
         if(outputStream != null) {
            outputStream.close();
         }
      } catch (Exception e) {
         logger.error(e.getMessage(), e);
      }
   }
}

直接输出不在项目中生成临时文件的方法

/**
 * 导出入库单详情
 * @param inReceiptId   入库单id
 * @param response
 * @param request
 * @throws Exception
 */
@RequestMapping("exportInReceipt")
@ResponseBody
public void exportPriceList(Integer inReceiptId, HttpServletResponse response, HttpServletRequest request) throws Exception {
   List<IotCard> iotCards = cardService.selectByInReceiptId(inReceiptId);
   IotInReceipt iotInReceipt = inReceiptService.selectById(inReceiptId);
   OutputStream os = response.getOutputStream();// 取得输出流
   String tableName = "入库单明细";
   String showFileName = iotInReceipt.getSerialNum()+ "-" + DateUtil.Date2String(new Date(), "yyyy-MM-dd") + tableName + ".xls";
   try {
      //创建一个Excel工作簿对象
      HSSFWorkbook workbook = new HSSFWorkbook();
      //创建一个合并单元格做为表头根据下标合并单元格
      CellRangeAddress c1 = new CellRangeAddress(0, 0, 0, 9);
      //得到Excel工作表对象(sheet的名字为title的值)
      HSSFSheet sheet = workbook.createSheet(tableName);
      //创建单元格样式
      HSSFCellStyle style1 = workbook.createCellStyle();
      style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      //创建字体样式
      HSSFFont font1 = workbook.createFont();
      font1.setFontHeightInPoints((short) 18);
      //把字体赋予样式
      style1.setFont(font1);
      //创建单元格样式
      HSSFCellStyle style2 = workbook.createCellStyle();
      style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      //创建字体样式
      HSSFFont font2 = workbook.createFont();
      font2.setFontHeightInPoints((short) 12);
      //把字体赋予样式
      style2.setFont(font2);
      //将创建好的单元格赋予表1
      sheet.addMergedRegion(c1);
      //获取第一行
      HSSFRow row1 = sheet.createRow(0);
      //获取第一行中的第一个单元格
      HSSFCell cell = row1.createCell(0);
      //把样式赋予单元格
      cell.setCellStyle(style1);
      cell.setCellValue(iotInReceipt.getSerialNum() + "入库单明细");
      HSSFRow row2 = sheet.createRow(1);
      String[] titles = {"ICCID", "MSISDN", "运营商", "流量套餐", "供应商", "入库时间"};
      for (int i = 0; i < titles.length; i++) {
         String title = titles[i];
         HSSFCell cell2 = row2.createCell(i);
         cell2.setCellStyle(style2);
         cell2.setCellValue(title);

      }
      for (int j = 0; j < iotCards.size(); j++) {
         //输出list
         IotCard  card = iotCards.get(j);
         HSSFRow row = sheet.createRow(j + 2);
         HSSFCell cc1 = row.createCell(0);
         cc1.setCellValue(card.getIccid());
         HSSFCell cc2 = row.createCell(1);
         cc2.setCellValue(card.getMsisdn());
         HSSFCell cc3 = row.createCell(2);
         cc3.setCellValue(card.getOperatorLiteral());
         HSSFCell cc4 = row.createCell(3);
         cc4.setCellValue(card.getProductName());
         HSSFCell cc5 = row.createCell(4);
         cc5.setCellValue(card.getSupplyName());
         HSSFCell cc6 = row.createCell(5);
         cc6.setCellValue(card.getCreateTime().substring(0,19));
      }
      response.reset();// 清空输出流
      response.setHeader("Content-disposition", "attachment; filename="
            + showFileName + ".xls"); // 设定输出文件头,该方法有两个参数，分别表示应答头的名字和值。
      response.setContentType("application/msexcel");
      workbook.write(os);
   } catch (Exception e) {
      logger.error(e.getMessage(), e);
   } finally {
      try {
         if(os != null) {
            os.close();
         }
      } catch (Exception e) {
         logger.error(e.getMessage(), e);
      }
   }
}