本站使用了 Disqus 评论框,如需留言请扶墙

java 导出数据为office xls文件

引入包(Maven/gradle) office操作使用apache poi开源组件

maven

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->  
<dependency>  
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency>  
<dependency>  
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>

gradle

compile(  
     'org.apache.poi:poi:3.8',
     'org.apache.poi:poi-ooxml:3.8'
)

编写一个工具类来简化操作

(代码很臭,用了3个循环,各位看看就好。如果你有更优的写法,也可以分享给我,让我学习学习~)

import org.apache.poi.hssf.usermodel.*;

import java.io.ByteArrayInputStream;  
import java.io.ByteArrayOutputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.util.List;

public class ExportExcel {

   public InputStream exportExcel(String[] headers, List<Object[]> li, Object[] totalHeaders, Object[] totalData) throws Exception {
        final String sheetname = "sheet";
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 粗体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        // 生成一个表格(若结果集大于2000行,会分成多个页签)
        HSSFSheet sheet;

        int pageSize = 2000;
        int sheetCount = li.size() % pageSize == 0 ? li.size() / pageSize : li.size() / pageSize + 1;

        for (int currPage =1; currPage<=sheetCount; currPage++) {

            // 遍历集合数据,产生数据行
            sheet = workbook.createSheet(sheetname + currPage);
            int index = 0; // 计数器
            sheet.setDefaultColumnWidth(25);

            if (totalHeaders != null && totalData != null) {
                HSSFRow row = sheet.createRow(0);
                addTotalRow(row, style, totalHeaders);
                row = sheet.createRow(1);
                addTotalDataRow(row, totalData);
                index = 2;
            }
            HSSFRow row = sheet.createRow(index);
            // 产生表格标题行
            addHeader(row, style, headers);

            int begin = (currPage - 1) * pageSize;
            int end = (begin + pageSize) > li.size() ? li.size() : (begin + pageSize);

            System.out.println("begin:" + begin + ",end=" + end);

            int rowCount = 1;
            for (int n = begin; n < end; n++) {
                row = sheet.createRow(rowCount);
                rowCount++;
                Object[] lo = li.get(n);
                for (int i = 0; i < headers.length; i++) {
                    HSSFCell cell = row.createCell(i);
                    // 设置成文本格式
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    try {
                        // 获取值
                        String value;
                        if (null == lo[i]) {
                            value = "";
                        } else {
                            value = lo[i].toString();
                        }

                        // 判断值的类型后进行强制类型转换
                        if (null == value) {
                            cell.setCellValue(new HSSFRichTextString(""));
                        } else {

                            cell.setCellValue(new HSSFRichTextString(value));
                        }

                    } catch (Exception e) {
                        // 捕获异常
                        e.printStackTrace();
                        throw new Exception("导出时,转换JavaBean出错");
                    }

                }
            }
        }

        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            throw new Exception("导出失败");
        }
        byte[] ba = bos.toByteArray();
        bos.close();
        ByteArrayInputStream bis = new ByteArrayInputStream(ba);
        return bis;
   }

    /**
     * 设置表格标题行
     * @param row
     * @param style
     * @param headers
     */
    protected static void addHeader(HSSFRow row, HSSFCellStyle style, String[] headers) {
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            //设置成文本格式
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
    }

    private void addTotalRow(HSSFRow row, HSSFCellStyle style, Object[] totalHeaders) {
        for (int i = 0; i < totalHeaders.length; i++) {
            HSSFCell cell = row.createCell(i);
            //设置成文本格式
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(totalHeaders[i]==null?"":totalHeaders[i].toString());
            cell.setCellValue(text);
        }
    }

    private void addTotalDataRow(HSSFRow row, Object[] totalData) {
        for (int i = 0; i < totalData.length; i++) {
            HSSFCell cell = row.createCell(i);
            //设置成文本格式
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString text = new HSSFRichTextString(totalData[i]==null?"":totalData[i].toString());
            cell.setCellValue(text);
        }
    }
}

调用方法例示

        ExportExcel ex = new ExportExcel();
        String[] headers = {"ID", "姓名", "账户", "注册时间", "状态"};
        String querySQL = "SELECT `id`, `name`, `username`, `reg_time`, `status` FROM `users` ORDER BY `id`";
        List<Map<String, Object>> items = jdbcTemplate.queryForList(querySQL);

        List<Object[]> excelData = Lists.newArrayList();

        InputStream in = null;
        OutputStream out = null;
        int totalUser = 0;

        try {
            // j2ee 将请求头设置为 excel文件(浏览器会自动弹出下载xls)
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode("文件名称", "UTF-8") + ".xls");
            for (int i = 0; i < items.size(); i++) {
                Map<String, Object> item = items.get(i);
                Object[] data = new Object[headers.length];
                data[0] = item.get("id");
                data[1] = item.get("name");
                data[3] = item.get("username");
                data[4] = item.get("reg_time");
                data[5] = item.get("status");
                excelData.add(data);
                totalUser++;
            }
            Object[] data = new Object[headers.length];
            data[0] = "";
            data[1] = "";
            data[2] = "合计";
            data[3] = totalUser + " 个用户";
            data[4] = "";
            data[5] = "";
            excelData.add(data);
            in = ex.exportExcel(headers, excelData);
            HSSFWorkbook xwb = (HSSFWorkbook) WorkbookFactory.create(in);
            out = response.getOutputStream();
            xwb.write(out);
            out.flush();

        } catch (Exception e) {
            e.printStackTrace();
        }

Sendya

又穷又屌丝又不会英语 写什么 Bio

Amoy, china.