POI操作Excel（批量导出数据/下载excel）

目录

    1、第一个demo：创建工作簿，创建sheet页，创建单元格
    2、创建一个时间格式的单元格
    3、遍历工作簿的行和列并获取单元格内容
    4、文本提取
    5、单元格对齐方式
    6、单元格边框处理
    7、单元格填充色和颜色操作
    8、单元格合并
    9、字体处理
   10、读取和重写工作簿
   11、单元格中使用换行
   12、创建用户自定义数据格式
   13、批量导出数据
   14、demo: 利用 poi 实现数据的批量导出
   15、demo: 利用 poi 技术实现使用模版批量导出数据

 

1、第一个demo：创建工作簿，创建sheet页，创建单元格  <--返回目录

　　导包：poi-3.9-20121203.jar

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(0); // 创建一个行
    Cell cell = row.createCell(0); // 创建一个单元格 第1列
    cell.setCellValue(1); // 给单元格设置值

    row.createCell(1).setCellValue(1.2); // 创建一个单元格 第2列 值是1.2

    row.createCell(2).setCellValue("这是一个字符串类型"); // 创建一个单元格 第3列 值为一个字符串

    row.createCell(3).setCellValue(false); // 创建一个单元格 第4列 值为布尔类型

    FileOutputStream fileOut = new FileOutputStream("d:\\Poi生成的excel.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

2、创建一个时间格式的单元格    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(0); // 创建一个行
    Cell cell = row.createCell(0); // 创建一个单元格 第1列
    cell.setCellValue(new Date()); // 给单元格设置值

    // 单元格样式类
    CellStyle cellStyle = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy-mm-dd hh:mm:ss"));

    cell = row.createCell(1); // 第二列
    cell.setCellValue(new Date());
    cell.setCellStyle(cellStyle);

    cell = row.createCell(2); // 第三列
    cell.setCellValue(Calendar.getInstance());
    cell.setCellStyle(cellStyle);

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

3、遍历工作簿的行和列并获取单元格内容    <--返回目录

public static void main(String[] args) throws Exception {
    InputStream is = new FileInputStream("d:\\工作簿1.xls");
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
    if (hssfSheet == null) return;
        
    // 遍历行Row
    for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
        HSSFRow hssfRow = hssfSheet.getRow(rowNum);
        if (hssfRow == null) {
            continue;
        }
        // 遍历列Cell
        for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
            HSSFCell hssfCell = hssfRow.getCell(cellNum);
            if (hssfCell == null) {
                continue;
            }
            System.out.print(" " + getValue(hssfCell));
        }
        System.out.println();
    }
}

private static String getValue(HSSFCell hssfCell) {
    if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
        return String.valueOf(hssfCell.getBooleanCellValue());
    } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        return String.valueOf(hssfCell.getNumericCellValue());
    } else {
        return String.valueOf(hssfCell.getStringCellValue());
    }
}

 

4、文本提取    <--返回目录

public static void main(String[] args) throws Exception {
    InputStream is = new FileInputStream("d:\\工作簿1.xls");
    POIFSFileSystem fs = new POIFSFileSystem(is);
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    ExcelExtractor excelExtractor = new ExcelExtractor(wb);
    // false:不打印Sheet页的名字
    excelExtractor.setIncludeSheetNames(true);
    System.out.println(excelExtractor.getText());
}

 

5、单元格对齐方式    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(2); // 创建一个行
    row.setHeightInPoints(30); // 设置行高

    createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_FILL, HSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER);

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

/**
 * 创建一个单元格并为其设定指定的对齐方式
 * 
 * @param wb     工作簿
 * @param row    行
 * @param column 列
 * @param halign 水平方向对齐方式
 * @param valign 垂直方向对齐方式
 */
private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
    CellStyle cellStyle = wb.createCellStyle(); // 创建单元格样式
    cellStyle.setAlignment(halign); // 设置单元格水平方向对齐方式
    cellStyle.setVerticalAlignment(valign); // 设置单元格垂直方向对齐方式
    
    Cell cell = row.createCell(column); // 创建单元格
    cell.setCellValue(new HSSFRichTextString("Align It")); // 设置值
    cell.setCellStyle(cellStyle); // 设置单元格样式
}

 

6、单元格边框处理    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(1); // 创建一个行

    Cell cell = row.createCell(1); // 创建一个单元格
    cell.setCellValue(4);

    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
    cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex()); // 底部边框颜色

    cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
    cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色

    cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
    cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex()); // 右边边框颜色

    cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); // 上边边框
    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色

    cell.setCellStyle(cellStyle);
    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

7、单元格填充色和颜色操作    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(1); // 创建一个行

    Cell cell = row.createCell(1);
    cell.setCellValue("abc");
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); // 背景色
    cellStyle.setFillPattern(CellStyle.BIG_SPOTS); // 填充方式
    cell.setCellStyle(cellStyle);

    Cell cell2 = row.createCell(2);
    cell2.setCellValue("ABC");
    CellStyle cellStyle2 = wb.createCellStyle();
    cellStyle2.setFillForegroundColor(IndexedColors.RED.getIndex()); // 前景色
    cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充方式
    cell2.setCellStyle(cellStyle2);

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

8、单元格合并    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(1); // 创建一个行

    Cell cell = row.createCell(1);
    cell.setCellValue("单元格合并测试");

    sheet.addMergedRegion(new CellRangeAddress(1, // 起始行
            2, // 结束行
            1, // 其实列
            2 // 结束列
    ));

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

　　結果：

　　

 

 9、字体处理    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(1); // 创建一个行

    // 创建一个字体处理类
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 24);
    font.setFontName("Courier New");
    font.setItalic(true);
    font.setStrikeout(true);

    CellStyle style = wb.createCellStyle();
    style.setFont(font);

    Cell cell = row.createCell((short) 1);
    cell.setCellValue("This is test of fonts");
    cell.setCellStyle(style);

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

10、读取和重写工作簿    <--返回目录

public static void main(String[] args) throws Exception {
    InputStream inp = new FileInputStream("d:\\工作簿.xls");
    POIFSFileSystem fs = new POIFSFileSystem(inp);
    Workbook wb = new HSSFWorkbook(fs);
    Sheet sheet = wb.getSheetAt(0); // 获取第一个Sheet页
    Row row = sheet.getRow(0); // 获取第一行
    Cell cell = row.getCell(0); // 获取第一个单元格
    if (cell == null) {
        cell = row.createCell(3);
    }
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue("测试单元格");

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

 11、单元格中使用换行    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    Row row = sheet.createRow(2); // 创建一个行
    Cell cell = row.createCell(2);
    cell.setCellValue("我要换行 \n 成功了吗？");

    CellStyle cs = wb.createCellStyle();
    // 设置可以换行
    cs.setWrapText(true);
    cell.setCellStyle(cs);

    // 调整下行的高度
    row.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints());
    // 调整单元格宽度
    sheet.autoSizeColumn(2);

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

12、创建用户自定义数据格式    <--返回目录

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
    Sheet sheet = wb.createSheet("第一个Sheet页"); // 创建第一个Sheet页
    CellStyle style;
    DataFormat format = wb.createDataFormat();
    Row row;
    Cell cell;
    short rowNum = 0;
    short colNum = 0;

    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(111111.25);

    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("0.0")); // 设置数据格式
    cell.setCellStyle(style);

    row = sheet.createRow(rowNum++);
    cell = row.createCell(colNum);
    cell.setCellValue(1111111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,##0.000"));
    cell.setCellStyle(style);

    FileOutputStream fileOut = new FileOutputStream("d:\\工作簿.xls");
    wb.write(fileOut);
    fileOut.close();
}

 

13、批量导出数据    <--返回目录

public String export()throws Exception{
    Connection con=null;
    try {
        con=dbUtil.getCon();
        Workbook wb=new HSSFWorkbook();
        String headers[]={"编号","姓名","电话","Email","QQ"};
        ResultSet rs=userDao.userList(con, null);
        ExcelUtil.fillExcelData(rs, wb, headers);
        ResponseUtil.export(ServletActionContext.getResponse(), wb, "导出excel.xls");
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        try {
            dbUtil.closeCon(con);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    return null;
}

　　

　　ExcelUtil

package com.oy;

import java.sql.ResultSet;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

    public static void fillExcelData(ResultSet rs, Workbook wb, String[] headers) throws Exception {
        int rowIndex = 0;
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(rowIndex++);
        for (int i = 0; i < headers.length; i++) {
            row.createCell(i).setCellValue(headers[i]);
        }
        while (rs.next()) {
            row = sheet.createRow(rowIndex++);
            for (int i = 0; i < headers.length; i++) {
                row.createCell(i).setCellValue(rs.getObject(i + 1).toString());
            }
        }
    }
}

 

　　ResponseUtil

package com.oy;

import java.io.OutputStream;
import java.io.PrintWriter;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;

public class ResponseUtil {

    public static void write(HttpServletResponse response, Object o) throws Exception {
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        out.print(o.toString());
        out.flush();
        out.close();
    }

    public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception {
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }
}

 

14、demo: 利用 poi 实现数据的批量导出    <--返回目录

　　依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

　　PoiController

@Controller
public class PoiController {
    @RequestMapping("/poi/export")
    public void get(HttpServletResponse response) throws Exception {
        Workbook wb = new HSSFWorkbook();
        String headers[] = { "id", "name", "mobileNum", "email" };
        List<User> userList = new ArrayList<>();
        User user1 = new User(1, "张三", "13512345678", "123@163.com");
        User user2 = new User(1, "张三", "13512345678", "123@163.com");
        User user3 = new User(1, "张三", "13512345678", "123@163.com");
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);

        ExcelUtil.fillExcelData(userList, wb, headers);
        ResponseUtil.export(response, wb, "导出excel.xls");

    }
}

　　User

public class User {
    private Integer id;
    private String name;
    private String mobileNum;
    private String email;
    public User() {
    }
    public User(Integer id, String name, String mobileNum, String email) {
        this.id = id;
        this.name = name;
        this.mobileNum = mobileNum;
        this.email = email;
    }
    // getter和setter方法省略
    public Integer getId() {
        return id;
    }
}

　　ExcelUtil

public class ExcelUtil {

    public static void fillExcelData(List<User> userList, Workbook wb, String[] headers) throws Exception {
        int rowIndex = 0;
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(rowIndex++);
        for (int i = 0; i < headers.length; i++) {
            row.createCell(i).setCellValue(headers[i]);
        }

        if (userList != null && userList.size() > 0) {
            for (User user : userList) {
                row = sheet.createRow(rowIndex++);
                for (int i = 0; i < headers.length; i++) {
                    String field = headers[i];
                    field = field.substring(0, 1).toUpperCase() + field.substring(1);
                    Object val = user.getClass().getMethod("get" + field).invoke(user);
                    row.createCell(i).setCellValue(val.toString());
                }
            }
        }

    }
}

　　ResponseUtil

public class ResponseUtil {

    public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception {
        response.setHeader("Content-Disposition","attachment;filename=" 
                + new String(fileName.getBytes("utf-8"), "iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }
}

 

15、demo: 利用 poi 技术实现使用模版批量导出数据    <--返回目录

　　demo结构：

　　

 　　userExporTemplate.xls

 

 　　PoiController

@Controller
public class PoiController {
    
    @RequestMapping("/poi/export2")
    public void get2(HttpServletResponse response) throws Exception {
        List<User> userList = new ArrayList<>();
        User user1 = new User(1, "张三", "13512345678", "123@163.com");
        User user2 = new User(1, "张三", "13512345678", "123@163.com");
        User user3 = new User(1, "张三", "13512345678", "123@163.com");
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);
        
        String headers[] = { "id", "name", "mobileNum", "email" };
        Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userList, headers, "userExporTemplate.xls");
        ResponseUtil.export(response, wb, "利用模版导出excel.xls");
    }
}

　　ExcelUtil

public class ExcelUtil {

    public static Workbook fillExcelDataWithTemplate(List<User> userList, String[] headers, String templateFileName) throws Exception {
        InputStream in = ExcelUtil.class.getResourceAsStream("/static/template/" + templateFileName);
        POIFSFileSystem fs = new POIFSFileSystem(in);
        Workbook wb = new HSSFWorkbook(fs);
        Sheet sheet = wb.getSheetAt(0);
        // 获取列数
        int cellNums = sheet.getRow(0).getLastCellNum();
        int rowIndex = 1;
        Row row = null;
        if (userList != null && userList.size() > 0) {
            for (User user : userList) {
                row = sheet.createRow(rowIndex++);
                for (int i = 0; i < cellNums; i++) {
                    String field = headers[i];
                    field = field.substring(0, 1).toUpperCase() + field.substring(1);
                    Object val = user.getClass().getMethod("get" + field).invoke(user);
                    row.createCell(i).setCellValue(val.toString());
                }
            }
        }
        return wb;
    }
}

　　User类和ResponseUtil类与上一个demo相同。