POI+Maven实现数据导出到Excel

来源：互联网发布：光学字符识别软件编辑：程序博客网时间：2024/05/19 11:44

关于poi的使用将数据库中的数据导出到Excel中详解

1.首先要导入poi的maven坐标

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

2.使用工具类进行下载封装，并且完成下载后单元格的样式设置，宽度随着单元格内容长度自适应，以及Excel标题的设定。

import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletResponse;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.hssf.util.CellRangeAddress;import org.apache.poi.hssf.util.HSSFColor;public class ExportExcel{    // 显示的导出表的标题    private String title;    // 导出表的列名    private String[] rowName;    private List<Object[]> dataList = new ArrayList<Object[]>();    HttpServletResponse response;    // 构造方法，传入要导出的数据    public ExportExcel(String title, String[] rowName,            List<Object[]> dataList) {        this.dataList = dataList;        this.rowName = rowName;        this.title = title;    }    /*     * 导出数据     */    public void export(OutputStream out) throws Exception {        try {            HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象            HSSFSheet sheet = workbook.createSheet(title); // 创建工作表            // 产生表格标题行            HSSFRow rowm = sheet.createRow(0);            HSSFCell cellTiltle = rowm.createCell(0);            // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象            HSSFCellStyle style = this.getStyle(workbook); // 单元格样式对象            /*             * sheet.addMergedRegion(new             * CellRangeAddress(0,dataList.get(0).length-1 , 0,             * (rowName.length-1)));             */// 合并单元格            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,                    dataList.get(0).length - 1));// 列行            cellTiltle.setCellStyle(style);            cellTiltle.setCellValue(title);            // 定义所需列数            int columnNum = rowName.length;            HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)            // 将列头设置到sheet的单元格中            for (int n = 0; n < columnNum; n++) {                HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);                cellRowName.setCellValue(text); // 设置列头单元格的值                cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式            }            // 将查询出的数据设置到sheet对应的单元格中            for (int i = 0; i < dataList.size(); i++) {                Object[] obj = dataList.get(i);// 遍历每个对象                HSSFRow row = sheet.createRow(i + 2);// 创建所需的行数（从第三行开始写数据）                for (int j = 0; j < obj.length; j++) {                    HSSFCell cell = null; // 设置单元格的数据类型                    if (j == 0) {                        cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);                        cell.setCellValue(i);                    } else {                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);                        if (obj[j] != null) {                            cell.setCellValue(obj[j].toString());                        }                    }                    cell.setCellStyle(style); // 设置单元格样式                }            }            // 让列宽随着导出的列长自动适应            for (int colNum = 0; colNum < dataList.get(1).length; colNum++) {                int columnWidth = sheet.getColumnWidth(colNum) / 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(colNum) != null) { HSSFCell                     * currentCell = currentRow.getCell(colNum); if                     * (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING)                     * { int length =                     * currentCell.getStringCellValue().getBytes().length; if                     * (columnWidth < length) { columnWidth = length; } } }                     */                    if (currentRow.getCell(colNum) != null) {                        HSSFCell currentCell = currentRow.getCell(colNum);                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {                            int length = 0;                            try {                                length = currentCell.getStringCellValue()                                        .getBytes().length;                            } catch (Exception e) {                                e.printStackTrace();                            }                            if (columnWidth < length) {                                columnWidth = length;                            }                        }                    }                }                if (colNum == 0) {                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);                } else {                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);                }            }            if (workbook != null) {                try {                    workbook.write(out);                } catch (IOException e) {                    e.printStackTrace();                }            }        } catch (Exception e) {            e.printStackTrace();        } finally {            out.close();        }    }    /*     * 列头单元格样式     */    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {        // 设置字体        HSSFFont font = workbook.createFont();        // 设置字体大小        font.setFontHeightInPoints((short) 11);        // 字体加粗        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        // 设置字体名字        font.setFontName("Courier New");        // 设置样式;        HSSFCellStyle style = workbook.createCellStyle();        // 设置底边框;        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);        // 设置底边框颜色;        style.setBottomBorderColor(HSSFColor.BLACK.index);        // 设置左边框;        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);        // 设置左边框颜色;        style.setLeftBorderColor(HSSFColor.BLACK.index);        // 设置右边框;        style.setBorderRight(HSSFCellStyle.BORDER_THIN);        // 设置右边框颜色;        style.setRightBorderColor(HSSFColor.BLACK.index);        // 设置顶边框;        style.setBorderTop(HSSFCellStyle.BORDER_THIN);        // 设置顶边框颜色;        style.setTopBorderColor(HSSFColor.BLACK.index);        // 在样式用应用设置的字体;        style.setFont(font);        // 设置自动换行;        style.setWrapText(false);        // 设置水平对齐的样式为居中对齐;        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        // 设置垂直对齐的样式为居中对齐;        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        return style;    }    /*     * 列数据信息单元格样式     */    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {        // 设置字体        HSSFFont font = workbook.createFont();        // 设置字体大小        // font.setFontHeightInPoints((short)10);        // 字体加粗        // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        // 设置字体名字        font.setFontName("Courier New");        // 设置样式;        HSSFCellStyle style = workbook.createCellStyle();        // 设置底边框;        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);        // 设置底边框颜色;        style.setBottomBorderColor(HSSFColor.BLACK.index);        // 设置左边框;        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);        // 设置左边框颜色;        style.setLeftBorderColor(HSSFColor.BLACK.index);        // 设置右边框;        style.setBorderRight(HSSFCellStyle.BORDER_THIN);        // 设置右边框颜色;        style.setRightBorderColor(HSSFColor.BLACK.index);        // 设置顶边框;        style.setBorderTop(HSSFCellStyle.BORDER_THIN);        // 设置顶边框颜色;        style.setTopBorderColor(HSSFColor.BLACK.index);        // 在样式用应用设置的字体;        style.setFont(font);        // 设置自动换行;        style.setWrapText(false);        // 设置水平对齐的样式为居中对齐;        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        // 设置垂直对齐的样式为居中对齐;        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        return style;    }}

3.使用方式

List<Map<String, Object>> list = registrationInformationDao                .findRegistrationInformation1(map);//获取数据        List<Object[]> dataList = new ArrayList<Object[]>();        Object[] objects0 = new Object[list.get(0).size()+1];        dataList.add(objects0);        Map<String, Object> map2 = list.get(0);        Set<String> keySet = map2.keySet();        int m = 1;        for (String key : keySet) {            dataList.get(0)[m++] = key;        }        for (int i = 0; i < list.size(); i++) {            Object[] objects = new Object[list.get(i).size()+1];            dataList.add(objects);            int j = 1;            Map<String, Object> map3 = list.get(i);            Set<String> keySet1 = map3.keySet();            for (String key : keySet1) {                dataList.get(i+1)[j++] = map3.get(key);            }        }// 使用流将数据导出OutputStream out = null;try {// 防止中文乱码Parament findByName = paramentDao.findByName("export_name");String headStr = "attachment; filename=\""        + new String((findByName.getParament_value()+/*下载后的文件名*/".xls").getBytes("gb2312"), "ISO8859-1") + "\"";response.setContentType("octets/stream");response.setContentType("APPLICATION/OCTET-STREAM");response.setHeader("Content-Disposition", headStr);out = response.getOutputStream();ExportExcelSeedBack ex = new ExportExcelSeedBack(findByName.getParament_value(),/*表的标题*/ new String[]{findByName.getParament_value()/*内容标题*/},        dataList/*数据准备  ---数据类型为List<Object[]>*/);ex.export(out);} catch (Exception e) {    e.printStackTrace();}

0 0