如何将数据库数据导出至excel

我就是我 2023-10-04 19:11 139阅读 0赞

1.List<Map> all = ywGytdcrsrInfoService.gytdExportExcel(pageMap, user);

将数据从数据库取出,最好放到list集合中,方便循环设置值

2.创建表格

Workbook workbook = new SXSSFWorkbook();                        // 创建工作簿对象
    SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title);    // 创建工作表

注意:如果使用SSH则表格至多可以放65535条数据,这里使用的是SXSSF

3.创建表头,里面存放的是数据的名称.然后将数据循环取出加序号得到一个list

String[] rowsName = {"列名 | 字段在数据库的名称"}

for (int i = 1; i <= rowsName.length; i++) {
        temp[i] = rowsName[i - 1].split("\\|")[0];
    }循环切割取左侧可以得到一个存放列名的集合

遍历从数据库查询到的list集合

HashMap<String, Object> data = listMap.get(i);

objs[0] = i;

for (int j = 1; j <= rowsName.length; j++) {
        Object o = data.get(rowsName[j - 1].split("\\|")[1]);
        objs[j] = (o == null || o.toString().length() <= 0) ? " " : o;
    }循环切割取右侧,通过得到的key查询可以得到一个个字段的值存入数据然后放入集合

4.设置样式

// 产生表格标题行
    Row rowm = sheet.createRow(0);
    Cell cellTiltle = rowm.createCell(0);

rowm.setHeight((short) (25 * 35)); //设置高度

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length-1)));
    cellTiltle.setCellStyle(columnTopStyle);
    cellTiltle.setCellValue(title);

/ 定义所需列数
    int columnNum = rowName.length;
    Row rowRowName = sheet.createRow(1);                // 在索引2的位置创建行(最顶端的行开始的第二行)
    rowRowName.setHeight((short) (25 * 25)); //设置高度

getColumnTopStyle:

// 设置字体
    Font font = workbook.createFont();
    //设置字体大小
    font.setFontHeightInPoints((short)11);
    //字体加粗
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //设置字体名字
    font.setFontName("Courier New");
    //设置样式;
    CellStyle 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);
    
    //设置单元格背景颜色
    style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

getStyle:

// 设置字体
    Font font = workbook.createFont();
    //设置字体名字
    font.setFontName("Courier New");
    //设置样式;
    CellStyle 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);

返回值为CellStyle

5.将列头和里面的数据插入单元格中

// 将列头设置到sheet的单元格中
    for(int n=0;n<columnNum;n++){
        Cell  cellRowName = rowRowName.createCell(n);                //创建列头对应个数的单元格
        cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
        RichTextString text = new XSSFRichTextString(rowName[n]);
        cellRowName.setCellValue(text);                                    //设置列头单元格的值
        cellRowName.setCellStyle(columnTopStyle);                        //设置列头单元格样式
    }

//将查询出的数据设置到sheet对应的单元格中
    for(int i=0;i<dataList.size();i++){
    
        Object[] obj = dataList.get(i);//遍历每个对象
        Row row = sheet.createRow(i+2);//创建所需的行数
    
        row.setHeight((short) (25 * 20)); //设置高度
    
        for(int j=0; j<obj.length; j++){
            Cell  cell = null;   //设置单元格的数据类型
            if(j == 0){
                cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(i+1);
            }else{
                cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                if(!"".equals(obj[j]) && obj[j] != null){
                    cell.setCellValue(obj[j].toString());                        //设置单元格的值
                }
            }
            cell.setCellStyle(style);                                    //设置单元格样式
        }
    }
    
    6.限制最大的列宽,如果不限制长度的话,长度超过限制就会报错
    sheet.trackAllColumnsForAutoSizing();

for (int colNum = 0; colNum < columnNum; colNum++) {
        int orgWidth = sheet.getColumnWidth(colNum)+1400;
        sheet.autoSizeColumn(colNum, true);
        int newWidth = (int) (sheet.getColumnWidth(colNum) + 1400);
        int maxWith = 256*255;
        //限制下最大宽度
        if(newWidth > maxWith) {
            sheet.setColumnWidth(colNum, maxWith);
        }else if (newWidth > orgWidth) {
            sheet.setColumnWidth(colNum, newWidth);
        } else {
            sheet.setColumnWidth(colNum, orgWidth);
        }
    }

7.使用response将文件通过io传递到前台

String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xlsx";
                        String headStr = "attachment; filename=\"" + fileName + "\"";
                        response.setContentType("APPLICATION/OCTET-STREAM");
                        response.setHeader("Content-Disposition", headStr);
                        OutputStream out = response.getOutputStream();
                        workbook.write(out);
    SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
                        out.close();