数据导出excel简单案例

  1. 需要的依赖坐标

    1. <!--POI-->
    2.      <dependency>
    3.          <groupId>org.apache.poi</groupId>
    4.          <artifactId>poi</artifactId>
    5.          <version>4.0.1</version>
    6.      </dependency>
    7.      <dependency>
    8.          <groupId>org.apache.poi</groupId>
    9.          <artifactId>poi-ooxml</artifactId>
    10.          <version>4.0.1</version>
    11.      </dependency>
    12.      <dependency>
    13.          <groupId>org.apache.poi</groupId>
    14.          <artifactId>poi-ooxml-schemas</artifactId>
    15.          <version>4.0.1</version>
    16.      </dependency>

  2. Java代码演示 ```java package com.xiaoha.excel.excel_eximport;

import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

/**

  • 导出excel文件 *
  • @author HausenLee

  • @date 2021/04/09 */ public class ExceExport { public static void main(String[] args) {

    1.  //1.获取到对应的Excel文件，工作簿文件
    2.  Workbook wb = new XSSFWorkbook();
    3.  //2.创建工作表
    4.  Sheet sheet = wb.createSheet();
    5.  wb.createSheet("这是啥呀");
    7.  //3.创建工作表中的行对象
    8.  Row row = sheet.createRow(1);
    9.  //4.创建工作表中行中的列对象
    10.  Cell cell = row.createCell(1);
    11.  //5.在列中写数据(设置数据)
    12.  cell.setCellValue("测试一下单元格");
    14.  //创建一个文件对象，作为excel文件内容的输出文件
    15.  File f = new File("test.xlsx");//文件在当前项目下
    16.  //输出时通过流的形式对外输出，包装对应的目标文件
    17.  OutputStream os = null;
    18.  try {
    19.      os = new FileOutputStream(f);
    20.      wb.write(os);
    21.  } catch (Exception e) {
    22.      e.printStackTrace();
    23.  } finally {
    24.      //将内存中的workbook数据写入到流中
    25.      try {
    26.          wb.close();
    27.          if(os!=null){
    28.              os.close();
    29.          }
    30.      } catch (IOException e) {
    31.          e.printStackTrace();
    32.      }
    33.  }

    } } ```

excel导入数据简单案例

  1. Java代码演示 ```java package com.xiaoha.excel.excelImport;

import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;

/**

  • 导入excel文件 *
  • @author HausenLee

  • @date 2021/04/09 */ public class ExcelImport { public static void main(String[] args) {

    1.  Workbook wb = null;
    2.  try {
    3.      //1.获取要读取的文件工作簿对象  
    4.      wb = new XSSFWorkbook("test.xlsx");
    5.      //2.获取工作表  
    6.      Sheet s = wb.getSheetAt(0);
    7.      //3.获取行  
    8.      Row row = s.getRow(1);
    9.      //4.获取列  
    10.      Cell cell = row.getCell(1);
    11.      //5.根据数据的类型获取数据  
    12.      String data = cell.getStringCellValue();
    13.      //double data = cell.getNumericCellValue(); 获取数值类型  
    14.      //boolean data = cell.getBooleanCellValue(); 获取布尔类型的
    15.      System.out.println(data);
    17.  } catch (IOException e) {
    18.      e.printStackTrace();
    19.  } finally {
    20.      try {
    21.          if(wb!=null){
    22.              wb.close();
    23.          }
    24.      } catch (IOException e) {
    25.          e.printStackTrace();
    26.      }
    27.  }

    } } ```

数据导出excel复杂案例

  1. Java代码演示 ```java package com.xiaoha.excel.export_header;

import com.xiaoha.excel.pojo.Question; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test;

import java.io.*; import java.util.ArrayList; import java.util.List;

/**

  • 导出表头 *
  • @author HausenLee

  • @date 2021/04/09 */ public class ExportHeader { public static void main(String[] args) {

    1.  //1.获取到对应的Excel文件，工作簿文件  
    2.  Workbook wb = new XSSFWorkbook();
    3.  //2.创建工作表  
    4.  Sheet s = wb.createSheet("题目数据文件");
    5.  //设置通用配置  
    6.  //s.setColumnWidth(4,100);  
    7.  //制作标题  addMergedRegion方法是合并单元格的方法
    8.  //参数为指定行列的对象CellRangeAddress
    9.  //它的构造方法参数为第一行，最后一行，第一列，最后一列
    10.  s.addMergedRegion(new CellRangeAddress(1,1,1,12));
    11.  //写入标题
    12.  Row row_1 = s.createRow(1);
    13.  Cell cell_1_1 = row_1.createCell(1);
    14.  cell_1_1.setCellValue("在线试题导出信息");
    15.  //创建一个样式  
    16.  CellStyle cs_title = wb.createCellStyle();
    17.  //水平对齐
    18.  cs_title.setAlignment(HorizontalAlignment.CENTER);
    19.  //垂直对齐
    20.  cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
    21.  cell_1_1.setCellStyle(cs_title);
    22.  //制作表头  
    23.  String[] fields = {"题目ID","所属公司ID","所属目录ID","题目简介","题干描述","题干配图","题目分析","题目类型","题目难度","是否经典题","题目状态","审核状态"};
    24.  Row row_2 = s.createRow(2);
    25.  for (int i = 0; i < fields.length; i++) {
    26.      Cell cell_2_temp = row_2.createCell(1 + i);
    27.      //++      
    28.      cell_2_temp.setCellValue(fields[i]);
    29.      //++
    30.      CellStyle cs_field = wb.createCellStyle();
    31.      cs_field.setAlignment(HorizontalAlignment.CENTER);
    32.      cell_2_temp.setCellStyle(cs_field);
    33.  }
    34.  //制作数据区  
    35.  // 创建一个文件对象，作为excel文件内容的输出文件  
    36.  File f = new File("test.xlsx");
    37.  //输出时通过流的形式对外输出，包装对应的目标文件  
    38.  OutputStream os = null;
    39.  try {
    40.      os = new FileOutputStream(f);
    41.      //将内存中的workbook数据写入到流中  
    42.      wb.write(os);
    43.  } catch (Exception e) {
    44.      e.printStackTrace();
    45.  } finally {
    46.      try {
    47.          wb.close();
    48.          if(os!=null){
    49.              os.close();
    50.          }
    52.      } catch (IOException e) {
    53.          e.printStackTrace();
    54.      }
    55.  }
  1. }
  2. @Test
  3. public void testProjectPoi() throws IOException {
  4.     //1.获取到对应的Excel文件，工作簿文件  
  5.     Workbook wb = new XSSFWorkbook();
  6.     //2.创建工作表  
  7.     Sheet s = wb.createSheet("题目数据文件");
  9.     //制作数据区  
  10.     List<Question> questionList = new ArrayList<>();
  11.     Question qq = new Question();
  12.     qq.setId("1");
  13.     qq.setPicture("12");
  14.     qq.setReviewStatus("13");
  15.     qq.setAnalysis("14");
  16.     qq.setCatalogId("15");
  17.     qq.setCompanyId("16");
  18.     qq.setDifficulty("17");
  19.     qq.setIsClassic("18");
  20.     qq.setRemark("19");
  21.     qq.setState("21");
  22.     qq.setSubject("31");
  23.     qq.setType("41");
  24.     questionList.add(qq);
  25.     Question qqq = new Question();
  26.     qqq.setId("1");
  27.     qqq.setPicture("12");
  28.     qqq.setReviewStatus("13");
  29.     qqq.setAnalysis("14");
  30.     qqq.setCatalogId("15");
  31.     qqq.setCompanyId("16");
  32.     qqq.setDifficulty("17");
  33.     qqq.setIsClassic("18");
  34.     qqq.setRemark("19");
  35.     qqq.setState("21");
  36.     qqq.setSubject("31");
  37.     qqq.setType("41");
  38.     questionList.add(qqq);
  40.     //遍历集合
  41.     /*for (int i = 0; i < questionList.size(); i++) {
  42.         Row row = s.createRow(i + 3);
  43.         for (int j = 0; j < 12; j++) {
  44.             Cell cell = row.createCell(j + 1);
  46.         }
  47.     }*/
  49.     int rowIndex= 0;
  51.     for (Question question : questionList) {
  52.         int cellIndex = 0;
  53.         Row row = s.createRow( 3+rowIndex++);
  54.         Cell cell_1 = row.createCell(1+cellIndex++);
  55.         cell_1.setCellValue(question.getId());
  57.         Cell cell_2 = row.createCell(2+cellIndex++);
  58.         cell_2.setCellValue(question.getPicture());
  60.         Cell cell_3 = row.createCell(3+cellIndex++);
  61.         cell_3.setCellValue(question.getReviewStatus());
  63.         Cell cell_4 = row.createCell(4+cellIndex++);
  64.         cell_4.setCellValue(question.getAnalysis());
  66.         Cell cell_5 = row.createCell(5+cellIndex++);
  67.         cell_5.setCellValue(question.getCatalogId());
  69.         Cell cell_6 = row.createCell(6+cellIndex++);
  70.         cell_6.setCellValue(question.getCompanyId());
  72.         Cell cell_7 = row.createCell(7+cellIndex++);
  73.         cell_7.setCellValue(question.getDifficulty());
  75.         Cell cell_8 = row.createCell(8+cellIndex++);
  76.         cell_8.setCellValue(question.getIsClassic());
  78.         Cell cell_9 = row.createCell(9+cellIndex++);
  79.         cell_9.setCellValue(question.getRemark());
  81.         Cell cell_10 = row.createCell(10+cellIndex++);
  82.         cell_10.setCellValue(question.getState());
  84.         Cell cell_11 = row.createCell(11+cellIndex++);
  85.         cell_11.setCellValue(question.getSubject());
  87.         Cell cell_12 = row.createCell(12+cellIndex++);
  88.         cell_12.setCellValue(question.getType());
  89.     }
  90.     //创建一个文件对象，作为excel文件内容的输出文件
  91.     File f = new File("test.xlsx");//文件在当前项目下
  92.     //输出时通过流的形式对外输出，包装对应的目标文件
  93.     OutputStream os = null;
  94.     try {
  95.         os = new FileOutputStream(f);
  96.         wb.write(os);
  97.     } catch (Exception e) {
  98.         e.printStackTrace();
  99.     } finally {
  100.         //将内存中的workbook数据写入到流中
  101.         try {
  102.             wb.close();
  103.             if(os!=null){
  104.                 os.close();
  105.             }
  106.         } catch (IOException e) {
  107.             e.printStackTrace();
  108.         }
  109.     }
  110. }

} ```