Java·Excel导出百万级数据

- POI依赖介绍
- CSV格式介绍
- JXL依赖介绍
- 使用依赖
- 上下文配置
- Excel2003格式导出
- Excel2007格式
- CSV文本格式
- 使用JXL依赖导出Excel

POI依赖介绍

1. Excel2003格式（java对象使用HSSFWorkbook）
Excel2003支持每个工作表中最多有 65536 行和 256列。对于工作表能够支持的最大个数，受CPU反应速度和内存大小影响。
采用常用导出方式导出数据时，需要注意的是Excel 2003行数和列数的限制。常用导出方式中的POI支持该格式的只有HSSF包
，当导出数据量大于一页的最大行数（65536）时，可采取分页的形式进行存储。

2. Excel2007格式(java对象使用SXSSFWorkbook)
Excel 2007是Excel 2003的升级版，Excel 2007支持每个工作表中最多有 1,048,576 行和 16,384 列。采用常用导出方式导出数据时，需要注意的是Excel 2007行数和列数的限制，常用导出方式支持该格式的只有XSSF包，包含SXSSF扩展包，并且仅有SXSSF支持大数据。

CSV格式介绍

CSV是一种通用的、相对简单的文件格式，广泛的应用在程序之间转移表格数据。
它通常具有以下特征：
1）纯文本，可以使用Excel和文本编辑器打开；
2）每条记录被分隔符分隔为字段（典型分隔符有逗号、分号或制表符；有时分隔符可以包括可选的空格）；
3）常用导出方式不会引起内存溢出问题。
常用导出方式推荐：1

JXL依赖介绍

1. JXL介绍
JXL是一个开源的Java Excel API项目。它能作为Java Excel API的一个共同的支持库，是因为它的基本功能是可创建，读取和写入电子表格。基本特征如下：
1）生成Excel文件
2）从工作簿和电子表格导入数据
3）获得行和列的总数
此方式对中文支持很好，不会出现乱码情况，支持单元格的常用操作，满足一般需求，该方式在写入效率上优于POI方式。
需要注意：JXL只支持xls档案格式，并且处理的数据量非常有限。

使用依赖


  
      org.springframework.boot
      spring-boot-starter-web
  
  
      mysql
      mysql-connector-java
      8.0.29
  
  
  
      org.mybatis.spring.boot
      mybatis-spring-boot-starter
      2.2.2
  
  
  
      org.apache.poi
      poi
      3.17
  
  
      org.apache.poi
      poi-ooxml
      3.17
  
  
      org.xmlunit
      xmlunit-core
  
  
  
      net.sourceforge.jexcelapi
      jxl
      2.6.12

上下文配置

#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mm?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456

# 开启驼峰命名，不然数据库序列化时查询的数据会为null值
mybatis.configuration.map-underscore-to-camel-case=true

Excel2003格式导出

定义接口类

@Mapper
public interface QyjsdetaMapper {
    @Select("select id, account_id ,external_id  from table_name")
    public List qyjs();
}

实体类对象：用来序列化接受数据库中的数据

public  class QyjsdetaMode{
        public String id ;
        public String accountId ;
        public String externalId ;
        省略了get、set方法

导出Excel实现

@Controller
@RequestMapping("/")
public class QyjsExceldown {

    @Resource
    public QyjsdetaMapper qyjsdetaMpper;

    @RequestMapping(value = "/dd", method = RequestMethod.GET)
    public void downloadAllClassmate(HttpServletResponse response) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建HSSFWorkbook对象,  excel的文档对象
        HSSFSheet sheet = workbook.createSheet("信息表"); //excel的表单
        HSSFSheet sheet2 = workbook.createSheet("信息表2"); //excel的表单
        List classmateList = qyjsdetaMpper.qyjs();

        System.out.println("方法1");
        String fileName = "userinf"  + ".csv";//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据
        String[] headers = { "id", "姓名", "号码" };
        //headers表示excel表中第一行的表头
        HSSFRow row1 = sheet.createRow(0);
        //在excel表中添加表头
        for(int i=0;i
            HSSFCell cell = row1.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        HSSFRow row2 = sheet.createRow(1);

        int rowNum = 1;
        //在表中存放查询到的数据放入对应的列
        for (QyjsdetaMode qyjsdetaMode : classmateList) {
            if (rowNum < 65535) {
                row2 = sheet.createRow(rowNum);
            }
            // 注意：日期类型需要转换成string类型再输出，同时需要在配置文件中开启驼峰命名，不然会报空指针
            DateFormat str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String id = String.valueOf( qyjsdetaMode.getId() );
            String accountId = String.valueOf( qyjsdetaMode.getAccountId() );
            String externalId = String.valueOf(qyjsdetaMode.getExternalId() );
            //System.out.println("accountId："+accountId +"externalId："+ externalId);

            row2.createCell(0).setCellValue(qyjsdetaMode.getId());
            row2.createCell(1).setCellValue(qyjsdetaMode.getAccountId());
            row2.createCell(2).setCellValue(qyjsdetaMode.getExternalId());

            rowNum++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        System.out.println("方法 33333333");
    }
}

Excel2007格式

@RestController
public class Sxssf {

    @RequestMapping("/ss")
    public void exportExcle() throws SQLException, IOException, ClassNotFoundException {

        
        Class.forName("com.mysql.cj.jdbc.Driver"); //加载MYSQL JDBC驱动程序
        String url = "jdbc:mysql://localhost:3306/mm";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        Statement stat = conn.createStatement();
       // String sql = "select u.user_id ,u.account,u.name from " + "sys_user u";
        String sql = "select id , account_id acc,external_id ext  from "+ "table_name limit 10 " ;
        // 格式: String sql = "select * from " + TableName where 1=1;
        ResultSet rs = stat.executeQuery(sql);

        
        long t1 = System.currentTimeMillis();
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        workbook.createSheet("aaa");
        SXSSFSheet aaa = workbook.getSheetAt(0);
        // 测试导出100万行数据
        for (int i=0;i<1000000;i++){
            aaa.createRow(i);
            aaa.getRow(i).createCell(0).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
            aaa.getRow(i).createCell(1).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
            aaa.getRow(i).createCell(2).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
            aaa.getRow(i).createCell(3).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
            aaa.getRow(i).createCell(4).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
        }
        OutputStream outputStream = null;
        // 打开目的输入流，不存在则会创建
        outputStream = new FileOutputStream("out.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long t2 = System.currentTimeMillis();
        System.out.println("SXSSFWorkbook : 100w条数据写入Excel 消耗时间："+ (t2-t1));

        //======================================================================
        //设置存储在内存的行数，多余的存储在硬盘
        int cacheItems = 100;
        SXSSFWorkbook wb = new SXSSFWorkbook(cacheItems);
        wb.createSheet("测试");
        SXSSFSheet sh = wb.getSheetAt(0);
		// 导出数据库表数据
        int rownum = 0;
        while(rs.next()){
            sh.createRow(rownum);
            sh.getRow(rownum).createCell(0).setCellValue(rs.getString("id"));
            sh.getRow(rownum).createCell(1).setCellValue(rs.getString("acc"));
            sh.getRow(rownum).createCell(2).setCellValue(rs.getString("ext"));
            rownum ++;
            //每当行数达到设置的值就刷新数据到硬盘,以清理内存
            if(rownum % cacheItems == 0){
                ((SXSSFSheet)sh).flushRows();
            }
        }
        OutputStream out = new FileOutputStream("excel_data.xlsx");
        wb.write(out);
        out.close();
    }
}

CSV文本格式

@RestController
public class Csvexcel {
    @RequestMapping("/csv")
    public void exportExcle() throws IOException, SQLException, ClassNotFoundException {
        // 数据库连接配置
        Class.forName("com.mysql.cj.jdbc.Driver"); //加载MYSQL JDBC驱动程序
        String url = "jdbc:mysql://localhost:3306/mm";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        Statement stat = conn.createStatement();
        // String sql = "select u.user_id ,u.account,u.name from " + "sys_user u";
        String sql = "select id , account_id acc,external_id ext  from "+ " table_name limit 10 " ;
        
        File csvFile  = new File("excel_data.csv");
        File parent = csvFile.getParentFile();
        if (parent != null && !parent.exists()) {
            parent.mkdirs();
        }
        csvFile.createNewFile();

        // UTF-8使正确读取分隔符","
        BufferedWriter csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"), 1024);
        ResultSet rs = stat.executeQuery(sql);//查询数据
        while(rs.next()){
            StringBuffer sb = new StringBuffer();
            sb.append(""").append(rs.getString("id")).append("",");
            sb.append(""").append(rs.getString("acc")).append("",");
            sb.append(""").append(rs.getString("ext")).append(""");
            csvWriter.write(sb.toString());
            csvWriter.newLine();
        }
        csvWriter.close();
    }
}

使用JXL依赖导出Excel

@RestController
public class Hssfexcel {

      @RequestMapping("/ceshi")
      //创建XLS文档，并写入数据
      public  void createXLS()throws Exception{
          //create new XLS document
          jxl.write.WritableWorkbook book = jxl.Workbook.createWorkbook(new File("测试.xls"));
          //生成名为“第一页”的工作表，参数0表示是第一页
          jxl.write.WritableSheet sheet = book.createSheet("第一页",0);
          //在Label对象的构造出单元格第一列第一行(0,0,)的值为test
          jxl.write.Label label = new jxl.write.Label(0,0,"test");
          //将定义好的Label对象利用sheet对象添加到工作表中
          sheet.addCell(label);
          //生产数据的单元格
          jxl.write.Number number = new jxl.write.Number(1,0,123.21);
          sheet.addCell(number);
          //写入数据并进行关闭
          book.write();
          book.close();
      }

    @Resource
    public QyjsdetaMapper qyjsdetaMpper;

    @RequestMapping(value = "/shuru", method = RequestMethod.GET)
    public void downloadAllClassmate(HttpServletResponse response) throws IOException, WriteException {

        //create new XLS document
        jxl.write.WritableWorkbook book = jxl.Workbook.createWorkbook(new File("测试.xls"));
        //生成名为“第一页”的工作表，参数0表示是第一页
        jxl.write.WritableSheet sheet = book.createSheet("第一页", 0);

        //在Label对象的构造出单元格第一列第一行(0,0,)的值为test
        jxl.write.Number label01 = new jxl.write.Number(0, 0, 001);
        sheet.addCell(label01);//将定义好的Label对象利用sheet对象添加到工作表中
        //生产数据的单元格
        jxl.write.Label label02 = new jxl.write.Label(1, 0, "交易id");
        sheet.addCell(label02);
        jxl.write.Label label03 = new jxl.write.Label(2, 0, "账户id");
        sheet.addCell(label03);

        List classmateList = qyjsdetaMpper.qyjs();
        int rowNum = 1;
        // 对数据进行序列化
        for (QyjsdetaMode qyjsdetaMode : classmateList) {
            DateFormat str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String id = String.valueOf(qyjsdetaMode.getId());
            String accountId = String.valueOf(qyjsdetaMode.getAccountId());
            String externalId = String.valueOf(qyjsdetaMode.getExternalId());
            System.out.println("accountId：" + accountId + "externalId：" + externalId);

            jxl.write.Label label1 = new jxl.write.Label(0, rowNum, id);
            jxl.write.Label label2 = new jxl.write.Label(1, rowNum, accountId);
            jxl.write.Label label3 = new jxl.write.Label(2, rowNum, externalId);

            sheet.addCell(label1);
            sheet.addCell(label2);
            sheet.addCell(label3);
            if (rowNum < 60000) {
                rowNum++;
            }
        }
        //写入数据并进行关闭
        book.write();
        book.close();
    }


    @RequestMapping("/duqu")
      //一次型读取XLS文档
      public  void readXLS()throws Exception{
          //获取XLS对象
          jxl.Workbook book = jxl.Workbook.getWorkbook(new File("测试.xls"));
          //获取XLS第一页的工作表
          jxl.Sheet sheet = book.getSheet(0);

          //进行读取，可根据自己的实际情况去读取
          int i=0;
          do{
              int j=0;
              String str1;

              do{
                  jxl.Cell cell;
                  try{
                      //通过j,i的坐标找到单元格
                      cell = sheet.getCell(j,i);
                  }catch(ArrayIndexOutOfBoundsException ex){
                      break;
                  }
                  //用String接收单元格的值
                  str1= cell.getContents();
                  System.out.println(str1);
                  j++;
              }while(true);
              i++;
          }while(true);
      }
}

Java·Excel导出百万级数据

Java相关栏目本月热门文章