HSSFWorkbook导出数据到excel文件

数据

/**
 * @describe: 数据提供
 * Create By Zheng
 */
public class DataProvider {

    public static String title = "手机报价表";

    public static List<String> headerList = new ArrayList<>();
    static {
        headerList.add("ID");
        headerList.add("品牌");
        headerList.add("型号");
        headerList.add("价格");
        headerList.add("更新日期");
        headerList.add("数量");
    }

    public static List<List<String>> dataList = new ArrayList<>();
    static {
        dataList.add(Arrays.asList(new String[]{"1","苹果","x","300","2020/10/10","5"}));
        dataList.add(Arrays.asList(new String[]{"2","苹果","xs","2000","2020/10/10","4"}));
        dataList.add(Arrays.asList(new String[]{"3","苹果","xs max","2000","2020/10/10","5"}));
        dataList.add(Arrays.asList(new String[]{"4","苹果","xr","2000","2020/10/10","53"}));
        dataList.add(Arrays.asList(new String[]{"5","苹果","11","3000","2020/10/10","51"}));
        dataList.add(Arrays.asList(new String[]{"6","苹果","11pro","3000","2020/10/10","51"}));
    }
}

代码

    @Test
    public void createExcelFile() throws IOException {
        HSSFWorkbook workbook = createWorkbook();
        // 创建本地文件
        FileOutputStream file = new FileOutputStream("C:\\Users\\huishe010\\Desktop\\excel导出.xlsx");
        BufferedOutputStream fileOutputStream = new BufferedOutputStream(file);
        workbook.write(fileOutputStream);
        if (fileOutputStream != null) {
         	fileOutputStream.close();
        }
    }


    /**
     * 创建工作簿，并在工作簿中添加内容
     * @return workbook 工作簿
     */
    public HSSFWorkbook createWorkbook() {
        // 1-声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();//工作簿
        HSSFSheet sheet = workbook.createSheet("手机报表");//excel表名
        sheet.setDefaultColumnWidth(20);//设置表格列宽度为20个字节
        // 2-数据准备
        String title = DataProvider.title;
        List<String> headerList = DataProvider.headerList;
        List<List<String>> dataList = DataProvider.dataList;
        int width = headerList.size();

        // 3-标题准备
        int row = 0;
        updateCellText(sheet, row ++, 0, title);
        CellRangeAddress region = new CellRangeAddress(0 , 0, 0, width - 1);// 合并标题单元格
        sheet.addMergedRegion(region);

        // 4-表头准备
        for (int i = 0; i < width; i++) {
            updateCellText(sheet, row, i, headerList.get(i));
        }
        row ++;

        // 6-表数据写入
        for (int i = 0; i < dataList.size(); i++) {
            for (int j = 0; j < dataList.get(i).size(); j++) {
                updateCellText(sheet, row + i, j, dataList.get(i).get(j));
            }
        }

        return workbook;
    }

    /**
     * 对工作簿对象-Excel-表格中进行内容的填充-仅展示文本Text内容
     * @param sheet 工作簿中的表格
     * @param rowIndex 行数 Starting from 0
     * @param cellIndex 列数 Starting from 0
     * @param content 单元格内容
     * Create By Zheng
     */
    private void updateCellText(HSSFSheet sheet, int rowIndex, int cellIndex, String content) {
        // 1-获取行对象
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        // 2-创建单元格对象
        HSSFCell cell = row.createCell(cellIndex);
        // 3-单元格文本数据填充
        cell.setCellValue(new HSSFRichTextString(content));
    }

通过接口返回excel文件

	@GetMapping(value="/export")
    public void exportExcel(HttpServletResponse response) throws IOException, ServerSqlErrorException {

        HSSFWorkbook workbook = HSSFWorkbookDemo.createWorkbook(id);

        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //这后面可以设置导出Excel的名称，此例中名为report.xls
        response.setHeader("Content-disposition", "attachment;filename=report.xlsx");

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载
        workbook.write(response.getOutputStream());
    }

导出效果图：