JAVA 使用poi导出并自定义表头，在浏览器下载excel表格

最新推荐文章于 2024-07-29 14:22:26 发布

新啊～～～

最新推荐文章于 2024-07-29 14:22:26 发布

阅读量912

点赞数 3

文章标签： poi java java-ee maven

本文链接：https://blog.csdn.net/Tudou_aa/article/details/119324078

版权

@JAVA 导出Excel表格，并自定义表头

JAVA导出Excel表格，并自定义表头

1，MAVEN依赖

	<!-- springboot通过poi导出excel -->
		<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>

2，导入的包

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

3，完整的代码

	@GetMapping("/excel")
    public void excel(){
        try{
            HSSFWorkbook  wb  =  new HSSFWorkbook();
            HSSFSheet sheet  =  wb.createSheet("测试表格");
            //设置表格的样式，可设置多个
            HSSFCellStyle style1 = wb.createCellStyle();
            style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直  
            style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
            Font ztFont = wb.createFont();
            ztFont.setColor(Font.COLOR_NORMAL);
            ztFont.setFontHeightInPoints((short)16);
            ztFont.setFontName("宋体");
            ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style1.setFont(ztFont);

            HSSFCellStyle styleWork = wb.createCellStyle();
            styleWork.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直  
            styleWork.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
            styleWork.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            styleWork.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            styleWork.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            styleWork.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            Font fontWork = wb.createFont();
            fontWork.setFontName("宋体");
            fontWork.setFontHeightInPoints((short)12);
            styleWork.setFont(fontWork);

            HSSFCellStyle styleValue = wb.createCellStyle();
            styleValue.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直  
            styleValue.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平
            Font fontValue = wb.createFont();
            fontValue.setFontName("宋体");
            fontValue.setFontHeightInPoints((short)12);
            styleValue.setFont(fontValue);

			//设置行
            HSSFRow row  =  sheet.createRow((short)   0);
            HSSFRow  row2  =  sheet.createRow((short)   2);
            HSSFRow  row3  =  sheet.createRow((short)   3);
            HSSFRow  row4  =  sheet.createRow((short)   4);
            HSSFRow  row5  =  sheet.createRow((short)   5);
            
            HSSFCell ce=row.createCell((short)0);
            //这个是设置合并单元格的方法，四个参数分别是：起始行，结束行，起始列，结束列
            CellRangeAddress cra1 = new CellRangeAddress(0, 1, 0, 8);
            sheet.addMergedRegion(cra1);
            //给表格设置值
            ce.setCellValue("test");
            ce.setCellStyle(style1);
            
            HSSFCell cellW = row2.createCell((short) (0));
            HSSFCell cellWV = row2.createCell((short) (1));
            cellW.setCellValue("人物1");
            sheet.addMergedRegion(new  CellRangeAddress(2,2,1,8));
            cellWV.setCellValue("张三");
            cellW.setCellStyle(styleWork);
            cellWV.setCellStyle(styleValue);

            HSSFCell cellPro = row3.createCell((short) (0));
            HSSFCell cellProVal = row3.createCell((short) (1));
            cellPro.setCellValue("人物2");
            sheet.addMergedRegion(new  CellRangeAddress(3,3,1,8));
            cellProVal.setCellValue("李四");
            cellPro.setCellStyle(styleWork);
            cellProVal.setCellStyle(styleValue);

            HSSFCell cellWorkFlow = row4.createCell((short) (0));
            HSSFCell cellWorkFlowVal = row4.createCell((short) (1));
            cellWorkFlow.setCellValue("人物3");
            sheet.addMergedRegion(new  CellRangeAddress(4,4,1,8));
            cellWorkFlowVal.setCellValue("王五");
            cellWorkFlow.setCellStyle(styleWork);
            cellWorkFlowVal.setCellStyle(styleValue);
            
			//给合并的单元格设置边框，一定要统一放在下面，排查了好久的问题
            CellRangeAddress cellRange = new CellRangeAddress(0,1,0,8);
            sheet.addMergedRegion(cellRange);//为合并单元格添加边框
            RegionUtil.setBorderTop(1, cellRange, sheet, wb);
            RegionUtil.setBorderBottom(1, cellRange, sheet, wb);
            RegionUtil.setBorderLeft(1, cellRange, sheet, wb);
            RegionUtil.setBorderRight(1, cellRange, sheet, wb);
			
            CellRangeAddress cellRange1 = new CellRangeAddress(2,2,1,8);
            sheet.addMergedRegion(cellRange1);//为合并单元格添加边框
            RegionUtil.setBorderTop(1, cellRange1, sheet, wb);
            RegionUtil.setBorderBottom(1, cellRange1, sheet, wb);
            RegionUtil.setBorderLeft(1, cellRange1, sheet, wb);
            RegionUtil.setBorderRight(1, cellRange1, sheet, wb);

            CellRangeAddress cellRange2 = new CellRangeAddress(3,3,1,8);
            sheet.addMergedRegion(cellRange2);//为合并单元格添加边框
            RegionUtil.setBorderTop(1, cellRange2, sheet, wb);
            RegionUtil.setBorderBottom(1, cellRange2, sheet, wb);
            RegionUtil.setBorderLeft(1, cellRange2, sheet, wb);
            RegionUtil.setBorderRight(1, cellRange2, sheet, wb);

            CellRangeAddress cellRange3 = new CellRangeAddress(4,4,1,8);
            sheet.addMergedRegion(cellRange3);//为合并单元格添加边框
            RegionUtil.setBorderTop(1, cellRange3, sheet, wb);
            RegionUtil.setBorderBottom(1, cellRange3, sheet, wb);
            RegionUtil.setBorderLeft(1, cellRange3, sheet, wb);
            RegionUtil.setBorderRight(1, cellRange3, sheet, wb);
            
            //循环设置表格宽度
            for (int i = 0; i < 9; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 35 / 15);
            }
            //在第五行的基础上，创建单元格
            HSSFCell  cell1  =   row5.createCell((short) (0) );
            HSSFCell  cell2  =   row5.createCell((short) (1) );
            HSSFCell  cell3  =   row5.createCell((short) (2) );
            HSSFCell  cell4  =   row5.createCell((short) (3) );
            HSSFCell  cellb1  =   row5.createCell((short) (4) );
            HSSFCell  cellb2  =   row5.createCell((short) (5) );
            HSSFCell  cellb3  =   row5.createCell((short) (6) );
            HSSFCell  cellb4  =   row5.createCell((short) (7) );
            HSSFCell  cellb5  =   row5.createCell((short) (8) );
            cell1.setCellValue("test1");
            cell2.setCellValue("test2");
            cell3.setCellValue("test3");
            cell4.setCellValue("test4");
            cellb1.setCellValue("test5");
            cellb2.setCellValue("test6"); 
            cellb3.setCellValue("test7");
            cellb4.setCellValue("test8");
            cellb5.setCellValue("test9");
            
            cell1.setCellStyle(styleWork);
            cell2.setCellStyle(styleWork);
            cell3.setCellStyle(styleWork);
            cell4.setCellStyle(styleWork);
            cellb1.setCellStyle(styleWork);
            cellb2.setCellStyle(styleWork);
            cellb3.setCellStyle(styleWork);
            cellb4.setCellStyle(styleWork);
            cellb5.setCellStyle(styleWork);
            FileOutputStream fileOut  =  new  FileOutputStream("D:测试表格.xls");
            wb.write(fileOut);   
            fileOut.close();  
        }catch(Exception ex){
            ex.printStackTrace(); 
        }
    }

4，代码运行效果图

在这里插入图片描述

5，表体加值

//dao.getData();是获取数据的方法，返回值如果用map的话，泛型必须是Map<String,String>
List<Map<String,String>> result = dao.getData();
for(int i=0;i<result.size();i++){
	                row = (HSSFRow) sheet.createRow(i + 6);
	                Map<String, String> data = result.get(i);
	                Cell cellNew0 = row.createCell(0);
	                Cell cellNew1 = row.createCell(1);
	                Cell cellNew2 = row.createCell(2);
	                Cell cellNew3 = row.createCell(3);
	                Cell cellNew4 = row.createCell(4);
	                Cell cellNew5 = row.createCell(5);
	                Cell cellNew6 = row.createCell(6);
	                Cell cellNew7 = row.createCell(7);
	                Cell cellNew8 = row.createCell(8);
	                cellNew0.setCellStyle(styleWork);
	                cellNew0.setCellValue(data.get("TEST1"));
	                cellNew1.setCellStyle(styleWork);
	                cellNew1.setCellValue(data.get("TEST2"));
	                cellNew2.setCellStyle(styleWork);
	                cellNew2.setCellValue(data.get("TEST3"));
	                cellNew3.setCellStyle(styleWork);
	                cellNew3.setCellValue(data.get("TEST4"));
	                cellNew4.setCellStyle(styleWork);
	                cellNew4.setCellValue(data.get("TEST5"));
	                cellNew5.setCellStyle(styleWork);
	                cellNew5.setCellValue(data.get("TEST6"));
	                cellNew6.setCellStyle(styleWork);
	                cellNew6.setCellValue(data.get("TEST7"));
	                cellNew7.setCellStyle(styleWork);
	                cellNew7.setCellValue(data.get("TEST8"));
	                cellNew8.setCellStyle(styleWork);
	                cellNew8.setCellValue(data.get("TEST9"));
	            }

5，在浏览器下载表格

//方法的参数列表需要新加一个参数HttpServletResponse response
String filename = "测试表格" + ".xls";
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    .concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
            OutputStream out = response.getOutputStream();
            try {
                wb.write(out);// 将数据写出去
                String str = "导出测试表格成功！";
                System.out.println(str);
            } catch (Exception e) {
                e.printStackTrace();
                String str1 = "导出测试表格失败！";
                System.out.println(str1);
            } finally {
                out.close();
            }

新啊～～～

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
JAVA 使用poi导出并自定义表头，在浏览器下载excel表格

@JAVA 导出Excel表格，并自定义表头JAVA导出Excel表格，并自定义表头1，MAVEN依赖  <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> </dep
复制链接

扫一扫