poi工具类

Java poi 导出

一、依赖包

<dependency>

        <groupId>org.apache.poi</groupId>

            <artifactId>poi</artifactId>

            <version>3.17</version>

        </dependency>

二、工具类

package com.gdfxit.foundwater.util;

import java.util.List;

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

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

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

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

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

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

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

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

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

import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelUtils {

// 标题

private String[] headers;

// 数据

private List<Object[]> data;

public ExcelUtils(String[] headers,List<Object[]> data) {

this.headers = headers;

this.data = data;

}

public  HSSFWorkbook Export() {

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet();

// 新增数据行

int rowNum = 1;

// 设置单元格样式

HSSFCellStyle headStyle = this.setHeadStyle(workbook);

HSSFCellStyle cellStyle = this.setCellStyle(workbook);

HSSFRow row = sheet.createRow(0);

row.setHeight((short) 800);

for(int i=0;i<this.headers.length;i++){

            HSSFCell cell = row.createCell(i);            

            HSSFRichTextString text = new HSSFRichTextString(headers[i]);

            cell.setCellValue(text);

            cell.setCellStyle(headStyle);

        }

//在表中存放查询到的数据放入对应的列

for(Object[] os:data) {

HSSFRow row1 = sheet.createRow(rowNum);

row1.setHeight((short) 400);

for(int j = 0;j<os.length;j++) {

HSSFCell cell = row1.createCell(j);

cell.setCellValue(os[j].toString());

cell.setCellStyle(cellStyle);

 //设置自动列宽(必须在单元格设值以后进行)

            sheet.autoSizeColumn(j);

            sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 17 / 10);

}

rowNum++;

}

for(int k = 0; k<data.size();k++) {

sheet.autoSizeColumn(k);

}

setSizeColumn(sheet, data.size());

return workbook;

}

/**

 * 设置标题样式

 * @param wb

 * @return

 */

private HSSFCellStyle setHeadStyle(HSSFWorkbook wb) {

HSSFCellStyle headStyle = wb.createCellStyle();

// 设置水平居中

headStyle.setAlignment(HorizontalAlignment.CENTER);

// 设置字体

HSSFFont headFont = wb.createFont();

// 设置字体大小

headFont.setFontHeightInPoints((short) 14);

// 设置字体

headFont.setFontName("实体");

// 设置粗体

headFont.setBold(true);

headStyle.setFont(headFont);

return headStyle;

}

/**

 * 单元格样式

 * @param wb

 * @return

 */

private HSSFCellStyle setCellStyle(HSSFWorkbook wb) {

HSSFCellStyle cellStyle = wb.createCellStyle();

// 设置对齐方式

cellStyle.setAlignment(HorizontalAlignment.CENTER);

// 设置字体

HSSFFont cellFont = wb.createFont();

// 设置字体大小

cellFont.setFontHeightInPoints((short) 11);

cellStyle.setFont(cellFont);

return cellStyle;

}

 /**

     * 自适应宽度(中文支持)

     * @param sheet

     * @param size

     */

    private  void setSizeColumn(HSSFSheet sheet, int size) {

        for (int columnNum = 0; columnNum < size; columnNum++) {

            int columnWidth = sheet.getColumnWidth(columnNum) / 256;

            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {

             HSSFRow currentRow;

                //当前行未被使用过

                if (sheet.getRow(rowNum) == null) {

                    currentRow = sheet.createRow(rowNum);

                } else {

                    currentRow = sheet.getRow(rowNum);

                }

                if (currentRow.getCell(columnNum) != null) {

                 HSSFCell currentCell = currentRow.getCell(columnNum);

                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {

                        int length = currentCell.getStringCellValue().getBytes().length;

                        if (columnWidth < length) {

                            columnWidth = length;

                        }

                    }

                }

            }

            sheet.setColumnWidth(columnNum, columnWidth * 256);

        }

    }

}

三、导出的效果

 

注意：如果要导出1万以上的数据，建议使用 SXSSFworkbook。