Java使用POI导出数据库表信息

poi简介

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

POI EXCEl文档类

• HSSFWorkbook excel文档对象
• HSSFSheet excel的sheet HSSFRow excel的行
• HSSFCell excel的单元格 HSSFFont excel字体
• HSSFName 名称 HSSFDataFormat 日期格式
• HSSFHeader sheet头
• HSSFFooter sheet尾
• HSSFCellStyle cell样式
• HSSFDateUtil 日期
• HSSFPrintSetup 打印
• HSSFErrorConstants 错误信息表

具体使用演示

• 首先在导入依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

• 编写工具栏

package com.xu.utils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

public class WriteUtils {
    //文档对象
    private static XSSFWorkbook workbook;
    //行
    private  static XSSFRow row;
    //excel的单元格
    private  static  XSSFCell cell;
    //excel的sheet
    private  static  XSSFSheet sheet;
    //样式
    private  static  XSSFCellStyle style;
    private static File file;
     //样式
    private static void initStyle(){
        style=workbook.createCellStyle();
        sheet.setDefaultColumnWidth(14);
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
    }
    //创建sheet页
    private static void setSheet(String sheetName){
        workbook=new XSSFWorkbook();
        sheet=workbook.createSheet(sheetName);
    }
    //创建表头
    private   static void  createTableHeader(List<Object> tableHeaderList){
        //在表格第一行创建
        row=sheet.createRow(0);
        //根据数组将表头数据写入excel
        for (int i=0;i<tableHeaderList.size();i++){
             cell = row.createCell(i);
              cell.setCellValue((String) tableHeaderList.get(i));
            initStyle();
        }
    }
    //获取excel表内容
    private static void createContext(List<List< Object>> contextList){
        for (int i = 0;i < contextList.size();i++){
            row=sheet.createRow(i+1);
            for (int j=0;j<contextList.get(i).size();j++){
                cell=row.createCell(j);
                cell.setCellValue((String) contextList.get(i).get(j));
                initStyle();
            }
        }
    }
    //写入文件
    private static void writeExcel(String path) throws IOException {
        file=new File(path);
        workbook.write(new FileOutputStream(file));
        System.out.println("文件写入成功");
        workbook.close();
    }
    public static void getContext(String sheetName,Map<String, List<List<Object>>> mapContext) throws IOException {
        List<Object> header=mapContext.get("tableHeader").get(0);
        List<List<Object>> context=mapContext.get("tableContext");
        setSheet(sheetName);//创建sheet
        createTableHeader(header);//表头
        createContext(context);//内容
        writeExcel("E://demo1.xls");
    }
}

使用：
调用getContext放法传入一个sheet的名字和一个Map,Map中的tableHeader为表头信息，类型是这样的样式[[‘用户名’，‘密码’]],tableContext对应的是表内容格式为[[‘admin’，‘123’],[‘admin1’，‘321’],[‘admin2’，‘132’]]
具体演示：
我数据库中的信息如下（以用户表为例）
编写测试类

public class MyTest {
    @Test
    public void test() throws IOException {
     ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) context.getBean("userDao");
        OrderDao orderDao= (OrderDao) context.getBean("orderDao");
        UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
        List<User> allUser = userServiceImpl.findAllUser();
        List<List<Object>> tableListContext=new ArrayList<List<Object>>();
        List<List<Object>> tableHeadList=new ArrayList<List<Object>>();
        List<Object> tableHead=new ArrayList<Object>();
        //表头
        tableHead.add("id");
        tableHead.add("用户名");
        tableHead.add("密码");
        tableHead.add("姓名");
        tableHead.add("年龄");
        tableHead.add("性别");
        tableHead.add("邮箱");
        tableHead.add("手机号");
        tableHead.add("角色");
        tableHead.add("问题");
        tableHead.add("答案");
        tableHead.add("删除状态");
        tableHead.add("禁用状态");
        tableHead.add("创建时间");
        tableHead.add("更新时间");
      //表内容
        for (User user : allUser) {
            List<Object> data=new ArrayList<Object>();
            data.add(user.getId()+"");
            data.add(user.getAccount());
            data.add(user.getPassword());
            data.add(user.getName());
            data.add(user.getAge()+"");
            data.add(user.getSex()+"");
            data.add(user.getEmail());
            data.add(user.getPhone());
            data.add(user.getRole()+"");
            data.add(user.getQuestion());
            data.add(user.getAsw());
            data.add(user.getDel()+"");
            data.add(user.getState()+"");
            data.add(user.getCreate_time()+"");
            data.add(user.getUpdate_time()+"");
            tableListContext.add(data);
        }
        Map<String, List<List<Object>>> maptable=new HashMap<String ,List<List<Object>>>();
       tableHeadList.add(tableHead);
        
        maptable.put("tableHeader",tableHeadList);
        maptable.put("tableContext",tableListContext);
        WriteUtils.getContext("demo",maptable);
    }
    @Test
    public void testDemo(){

    }
}

测试结果