精华内容
下载资源
问答
  • java导出excel复杂表头
    2021-04-28 14:33:02

    //声明一个工作薄
    HSSFWorkbook workbook =new HSSFWorkbook();
    //创建sheet
    HSSFSheet sheet =workbook.createSheet(“统计表”);
    //设置根据列名,列宽自适应 ,columnIndex 列索引
    sheet.setColumnWidth(columnIndex,“列名”.getBytes().length2256);
    //创建style
    HSSFCellStyle style = workbook.createCellStyle();
    //设置单元格边框
    style.setBorderTop(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN);
    //设置单元格水平、垂直居中
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    //创建行
    HSSFRow row =sheet.createRow(0);
    //设置单元格合并,四个参数分别为:起始行、结束行、起始列、结束列
    CellRangeAddress rangeAddress = new CellRangeAddress(0,1,0,0);
    //添加到sheet中
    sheet.addMergedRegion(rangeAddress);
    //创建列,赋值、并添加样式
    HSSFCell cell1 = row.createCell(0);
    cell1.setCellStyle(style);
    cell1.setCellValue(“序号”);

    更多相关内容
  • java导出excel复杂表头

    2019-02-20 16:09:50
    java工程,源代码,可直接运行,需自己改造成web下载方式。
  • Java导出Excel 复杂表头

    2021-03-12 17:42:08
    一种是直接使用模板直接添加数据就可以=== 复杂表头 依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</...

    文章标题

    导出表格

    导出表格的方式在我的理解有两种
    一种是直接用代码控制表头==== 简单的表头
    一种是直接使用模板直接添加数据就可以=== 复杂的表头

    依赖

    <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>3.9</version>
    </dependency>
    
    <!--下面是我直接测试表格,因为要在浏览器导出,直接创建Springboot 简单访问-->
     <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
                <version>2.3.4.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>4.0.1</version>
            </dependency>
    
    package com.example.demo.test;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @program: Ecology1
     * @description: this is a class
     * @author: Mr.zeng
     * @create: 2021-03-12 10:02
     **/
    
    public class TestTemplateExcel {
        /*
         *
         */
        private static final long serialVersionUID = 1L;
    
        /**
         * 生成excel并下载
         */
        public void exportExcel(HttpServletResponse response, HttpServletRequest request) {
            String userName = request.getParameter("userName");
            File newFile = createNewFile();
            // File newFile = new File("d:/ss.xls");
            // 新文件写入数据,并下载*****************************************************
            InputStream is = null;
            Workbook workbook = null;
            Sheet sheet = null;
            try {
                is = new FileInputStream(newFile);// 将excel文件转为输入流
    
                workbook=XlsImpUtil.create(is);
    //            workbook = new XSSFWorkbook(is);// 创建个workbook,
                // 获取第一个sheet
                sheet = workbook.getSheetAt(0);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
    
            if (sheet != null) {
                try {
                    // 写数据
                    FileOutputStream fos = new FileOutputStream(newFile);
                    Row row = sheet.getRow(3);
                    if (row == null) {
                        row = sheet.createRow(3);
                    }
                    Cell cell = row.getCell(0);
                    if (cell == null) {
                        cell = row.createCell(0);
                    }
    
                    // TODO 定义一个list集合假数据
                    List<Map<String, Object>> lst = new ArrayList();
                    Map<String, Object> map1 = new HashMap<String, Object>();
                    // 只能添加11个
                    for (int i = 0; i < 11; i++) {
                        map1.put("id" + i, i);
                        lst.add(map1);
                    }
                    row=sheet.getRow(1);
                    String sj="填报日期:2020年03月24日 15:52 星期四";
                    cell=row.getCell(0);
                    cell.setCellValue(sj);
                    for (int m = 0; m < lst.size(); m++) {
                        Map<String, Object> map = lst.get(m);
                        row=sheet.getRow(m+3);
    //                    row = sheet.createRow((int) m + 3);
                        for (int i = 0; i < 5; i++) {
                            String value = map.get("id" + m) + "";
                            if (value.equals("null")) {
                                value = "0";
                            }
                            if(row.getRowNum()>= 7 && row.getRowNum()<=12){
                                cell=row.getCell(i+2);
                            }else {
                                cell=row.getCell(i+2);
                            }
    
                        /*    cell = row.createCell(i);*/
                            cell.setCellValue(value);
                        }
    
                    }
                    // 填报人
                    row=sheet.getRow(14);
                    String tbr="\t\t填报人:"+userName;
                    cell=row.getCell(0);
                    cell.setCellValue(tbr);
    
                    workbook.write(fos);
                    fos.flush();
                    fos.close();
                    // 下载
                    InputStream fis = new BufferedInputStream(new FileInputStream(
                            newFile));
    //                    HttpServletResponse response = /*ServletActionContext.getResponse();*/null;
                    byte[] buffer = new byte[fis.available()];
                    fis.read(buffer);
                    fis.close();
                    response.reset();
                    response.setContentType("text/html;charset=UTF-8");
                    OutputStream toClient = new BufferedOutputStream(
                            response.getOutputStream());
                    response.setContentType("application/x-msdownload");
                    String newName = URLEncoder.encode(
                            "活动报表" + System.currentTimeMillis() + ".xlsx",
                            "UTF-8");
                    response.addHeader("Content-Disposition",
                            "attachment;filename=\"" + newName + "\"");
                    response.addHeader("Content-Length", "" + newFile.length());
                    toClient.write(buffer);
                    toClient.flush();
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (null != is) {
                            is.close();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            // 删除创建的新文件
             this.deleteFile(newFile);
        }
        /**
         * 复制文件
         *
         * @param s
         *            源文件
         * @param t
         *            复制到的新文件
         */
        public void fileChannelCopy(File s, File t) {
            try {
                InputStream in = null;
                OutputStream out = null;
                try {
                    in = new BufferedInputStream(new FileInputStream(s), 1024);
                    out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                    byte[] buffer = new byte[1024];
                    int len;
                    while ((len = in.read(buffer)) != -1) {
                        out.write(buffer, 0, len);
                    }
                } finally {
                    if (null != in) {
                        in.close();
                    }
                    if (null != out) {
                        out.close();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        private String getSispPath() {
            String classPaths = "C:\\Users\\zp\\IdeaProjects\\Ecology1\\src\\main\\resources";
    //        String classPaths="/usr/weaver/ecology/classbean/com/api/zp";
            String[] aa = classPaths.split("/");
            String sispPath = "";
            for (int i = 1; i < aa.length - 2; i++) {
                sispPath += aa[i] + "/";
            }
            return sispPath;
        }
        /**
         * 读取excel模板,并复制到新文件中供写入和下载
         *
         * @return
         */
        public File createNewFile() {
            // 读取模板,并赋值到新文件************************************************************
           final String name="C:\\Users\\zp\\IdeaProjects\\demo\\活动报表";
    //       String name="/usr/weaver/ecology/classbean/com/api/zp/活动报表";
            // 文件模板路径
            String path =  name+".xls";
            File file = new File(path);
            // 保存文件的路径
            String realPath =  "";
            // 新的文件名
            String newFileName = name + System.currentTimeMillis() + ".xls";
            // 判断路径是否存在
            File dir = new File(realPath);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            // 写入到新的excel
            File newFile = new File(newFileName);
            try {
                newFile.createNewFile();
                // 复制模板到新文件
                fileChannelCopy(file, newFile);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return newFile;
        }
        /**
         * 下载成功后删除
         *
         * @param files
         */
        private void deleteFile(File... files) {
            for (File file : files) {
                if (file.exists()) {
                    file.delete();
                }
            }
        }
        }
    
    
    import com.fasterxml.jackson.databind.exc.InvalidFormatException;
    import org.apache.poi.POIXMLDocument;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.PushbackInputStream;
    
    /**
     * @program: Ecology
     * @description: this is a class
     * @author: Mr.zeng
     * @create: 2021-03-12 11:34
     **/
    
    public class XlsImpUtil {
        public static Workbook create(InputStream inp) throws IOException, InvalidFormatException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {
            if (!inp.markSupported()) {
                inp = new PushbackInputStream(inp, 8);
            }
            if (POIFSFileSystem.hasPOIFSHeader(inp)) {
                return new HSSFWorkbook(inp);
            }
            if (POIXMLDocument.hasOOXMLHeader(inp)) {
                return new XSSFWorkbook(OPCPackage.open(inp));
            }
            throw new IllegalArgumentException("你的excel版本目前poi解析不了");
        }
    }
    

    我的Controller

    import com.example.demo.test.TestTemplateExcel;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * @program: Ecology1
     * @description: this is a class
     * @author: Mr.zeng
     * @create: 2021-03-12 10:52
     **/
    @Controller
    @RequestMapping("/excel")
    public class ExcelMainController {
    
        @RequestMapping("/port")
        public String excelport(HttpServletResponse response, HttpServletRequest request){
             new TestTemplateExcel().exportExcel(response,request);
             return "";
        }
    }
    
    展开全文
  • java实现导出Excel多行表头复杂模板 一般我们都会选择poi来导出,选择一个比较好的ExcelUtils 但是对于初学者而言不了解poi的,还需从poi API文档去学习,如果是自学的话更好,如果是公司安排的任务,没有接触过有...
  • Java实现POI导出excel复杂表头

    千次阅读 2022-02-16 14:51:28
    主方法加数据和excel表头布局 /** * 导出 * @param request * @param response * @throws IOException */ @RequestMapping("/exportMultipleItemStatisticsList.do") public void export(HttpServletRequest...
    1. 主方法加数据和excel表头布局
    /**
         * 导出
         * @param request
         * @param response
         * @throws IOException
         */
        @RequestMapping("/exportMultipleItemStatisticsList.do")
        public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
            String startTime = request.getParameter("startTime")==null ?"":request.getParameter("startTime").toString();//开始时间
            String endTime=request.getParameter("endTime")==null ?"":request.getParameter("endTime").toString();//结束时间
            String category = request.getParameter("category")==null ?"":request.getParameter("category").toString();//场点类型
            String reportcode=request.getParameter("reportcode")==null ?"":request.getParameter("reportcode").toString();//受检编号
            String entername=request.getParameter("entername")==null ?"":request.getParameter("entername").toString();//受检单位
            String immName = request.getParameter("immName")==null ?"":request.getParameter("immName").toString();//是否免疫
            String areaName = request.getParameter("areaName")==null ?"":request.getParameter("areaName").toString();//地市
            Map entityMap=new HashMap();
            entityMap.put("startTime",startTime);
            entityMap.put("endTime",endTime);
            entityMap.put("category",category);
            entityMap.put("reportcode",reportcode);
            entityMap.put("entername",entername);
            entityMap.put("immName",immName);
            entityMap.put("areaName",areaName);
            List<Map> list=multipleItemStatisticsService.getMultipleItemStatisticsList(entityMap);
            //1.创建一个workbook,对应一个excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
    
            //2.在workbook中添加一个sheet,对应Excel中的sheet
            HSSFSheet sheet = wb.createSheet("多重检测项目明细");
    
            //设置每一列的列宽
            sheet.setColumnWidth(0,256*15);
            sheet.setColumnWidth(1,256*15);
            sheet.setColumnWidth(2,256*15);
            sheet.setColumnWidth(3,256*15);
            sheet.setColumnWidth(4,256*15);
            sheet.setColumnWidth(5,256*15);
            sheet.setColumnWidth(6,256*15);
            sheet.setColumnWidth(7,256*15);
            sheet.setColumnWidth(8,256*15);
            sheet.setColumnWidth(9,256*15);
            sheet.setColumnWidth(10,256*15);
            sheet.setColumnWidth(11,256*15);
            sheet.setColumnWidth(12,256*15);
            sheet.setColumnWidth(13,256*15);
            sheet.setColumnWidth(14,256*15);
    //        sheet.setColumnWidth(15,256*15);
    
            //3.设置样式以及字体样式
            HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
            HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
            HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);
    
            //4.创建标题,合并标题单元格
            //行号
            int rowNum = 0;
    
            //创建第一行,索引从0开始(标题行)
            HSSFRow row0 = sheet.createRow(rowNum++);
            row0.setHeight((short) 800);// 设置行高
            String title = "多重检测项目明细表";
            HSSFCell c00 = row0.createCell(0);
            c00.setCellValue(title);
            c00.setCellStyle(titleStyle);
            // 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));//标题合并单元格操作,总列数为16
    
            //第二行
            HSSFRow row1 = sheet.createRow(rowNum++);
            row1.setHeight((short)500);
            String[] row_first = {"受检编号","样品编号","受检单位","地市","监测动物种类","年龄阶段","监测场点类型","饲养量","样品总量",list.get(0).get("name").toString(),"","","","",""};
            for (int i = 0; i < row_first.length; i++) {
                HSSFCell tempCell = row1.createCell(i);
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(headerStyle);
            }
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));//受检编号
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));//样品编号
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));//受检单位
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 3, 3));//地市
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 4, 4));//监测动物种类
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 5, 5));//年龄阶段
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 6, 6));//年龄阶段
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 7, 7));//年龄阶段
            sheet.addMergedRegion(new CellRangeAddress(1, 3, 8, 8));//年龄阶段
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 14));//截止2019年12月27日业务明细
    
            //第三行
            HSSFRow row2 = sheet.createRow(rowNum++);
            row2.setHeight((short)500);
            String[] row_second = {"","","","","","","","","",list.get(0).get("standard_name0").toString(),"","",list.get(0).get("standard_name1").toString(),"",""};
            for (int i = 0; i < row_second.length; i++) {
                HSSFCell tempCell = row2.createCell(i);
                tempCell.setCellValue(row_second[i]);
                tempCell.setCellStyle(headerStyle);
            }
    
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 14));
    
            //第四行
            HSSFRow row3 = sheet.createRow(rowNum++);
            row3.setHeight((short)500);
            String[] row_third = {"","","","","","","","","","检测样品数","阳性数","阳性率(%)","检测样品数","阳性数","阳性率(%)"};
            for (int i = 0; i < row_third.length; i++) {
                HSSFCell tempCell = row3.createCell(i);
                tempCell.setCellValue(row_third[i]);
                tempCell.setCellStyle(headerStyle);
            }
    
            //查询月度明细列表
    //        List<BusinessDetail> list = businessService.selectMonthBusinessList(businessDetail);
            for(int i = 0;i<list.size();i++){
                HSSFRow tempRow = sheet.createRow(rowNum++);
                tempRow.setHeight((short)500);
                //循环单元格填入数据
                for(int j=0;j<15;j++){
                    HSSFCell tempCell = tempRow.createCell(j);
                    tempCell.setCellStyle(contentStyle);
                    String cellValue = "";
                    if(j ==0){
                        //受检编号
                        cellValue = list.get(i).get("reportcode").toString();
                    }else if(j == 1){
                        //样品编号
                        cellValue = list.get(i).get("roundsamplecode").toString();
                    }else if(j ==2){
                        //受检单位
                        if(list.get(i).get("takeinspectionunit") ==null){
                            cellValue = "";
                        }else{
                            cellValue = list.get(i).get("takeinspectionunit").toString();
                        }
                    }else if(j == 3){
                        //地市
                        if(list.get(i).get("areaName") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("areaName").toString();
                        }
                    }else if(j ==4){
                        //监测动物种类
                        if(list.get(i).get("animal") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("animal").toString();
                        }
                    }else if(j == 5){
                        //年龄阶段
                        if(list.get(i).get("sampleAnimalName") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("sampleAnimalName").toString();
                        }
                    }else if(j == 6){
                        //监测场点类型
                        if(list.get(i).get("category") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("category").toString();
                        }
                    }else if(j ==7){
                        //饲养量
                        if(list.get(i).get("sampleSumNum") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("sampleSumNum").toString();
                        }
                    }else if(j == 8){
                        //样品总量
                        if(list.get(i).get("allNum") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("allNum").toString();
                        }
                    }else if(j == 9){
                        //a检测样品数
                        if(list.get(i).get("sampleNum0") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("sampleNum0").toString();
                        }
                    }else if(j == 10){
                        //阳性数
                        if(list.get(i).get("positiveNum0") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("positiveNum0").toString();
                        }
                    }else if(j == 11){
                        //阳性率(%)
                        if(list.get(i).get("sun0") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("sun0").toString();
                        }
                    }else if( j == 12){
                        //a检测样品数
                        if(list.get(i).get("sampleNum1") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("sampleNum1").toString();
                        }
                    }else if(j == 13){
                        //阳性数
                        if(list.get(i).get("positiveNum1") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("positiveNum1").toString();
                        }
                    }else if(j ==14){
                        //阳性率(%)
                        if(list.get(i).get("sun1") ==null){
                            cellValue = "0";
                        }else{
                            cellValue = list.get(i).get("sun1").toString();
                        }
                    }
                    tempCell.setCellValue(cellValue);
                }
            }
    
            //导出excel
    //        HttpServletResponse response = this.getResponse();
            String fileName = "月度业务明细.xls";
            try {
                fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
                response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
                OutputStream stream = response.getOutputStream();
                if(null != wb && null != stream){
                    wb.write(stream);
                    wb.close();
                    stream.close();
                }
    //            FileOutputStream outputStream = new FileOutputStream(new File("d:\\月度业务明细.xls"));
    //            wb.write(outputStream);
    //            outputStream.close();
            }catch (Exception e){
                e.printStackTrace();
            }
    //        return null;
        }
    

    2.对应样式单独加到一个工具类中

    /**
     * 导出excel设置的样式
     */
    public class ExcelUtils {
        /**
         * 创建标题样式
         * @param wb
         * @return
         */
        public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
            HSSFCellStyle cellStyle = wb.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直对齐
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    //        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            //背景颜色
    //        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    
            HSSFFont headerFont1 = (HSSFFont) wb.createFont();
            //字体加粗
            headerFont1.setBold(true);
            //字体类型
            headerFont1.setFontName("黑体");
            //字体大小
            headerFont1.setFontHeightInPoints((short)15);
            cellStyle.setFont(headerFont1);
            return cellStyle;
        }
    
        /**
         * 创建表头样式
         * @param wb
         * @return
         */
        public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
            HSSFCellStyle cellStyle = wb.createCellStyle();
            //设置自动换行
            cellStyle.setWrapText(true);
            //设置背景颜色
    //        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            //水平居中
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直对齐
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    //        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    //        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
            //下边框
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //左边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            //右边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //上边框
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    
            //创建字体样式
            HSSFFont headerFont = (HSSFFont)wb.createFont();
            //字体加粗
            headerFont.setBold(true);
            //字体类型
            headerFont.setFontName("黑体");
            //字体大小
            headerFont.setFontHeightInPoints((short)12);
            //为标题样式添加字体样式
            cellStyle.setFont(headerFont);
    
            return cellStyle;
        }
    
        /**
         *  设置表格内容样式
         * @param wb
         * @return
         */
        public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
            HSSFCellStyle cellStyle = wb.createCellStyle();
            //水平居中
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //设置自动换行
            cellStyle.setWrapText(true);
            //上边框
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //下边框
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //左边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            //右边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    
            //设置字体
            HSSFFont font = (HSSFFont)wb.createFont();
    //        font.setColor((short)8);
            font.setFontHeightInPoints((short)12);
    
            return cellStyle;
        }
    
    }
    

    3.只需要以上配置即可,保存路径在页面请求自定义路径。

    window.location.href = excel_url;
    
    展开全文
  • java excel复杂表头导出

    2021-03-10 10:43:10
    这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的...,丰富你的文章UML 图表FLowchart流程图导出与导入导出导入...
    public void exportExcel(ManagerProjectBean bean,HttpServletResponse response, UserInfoBean userInfoBean){
        bean.setIsStatistics(1);
        String projectName = bean.getProjectName();
        if(StringUtils.isNotEmpty(projectName)){
            projectName = projectName.substring(0,projectName.length()-1);
            bean.setProjectName(projectName);
        }
        //1.创建一个workbook,对应一个excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //2.在workbook中添加一个sheet,对应Excel中的sheet
        HSSFSheet sheet = wb.createSheet("年度新增工程统计");
        for(int i = 0; i < 30; i++){
            //设置每一列的列宽
            sheet.setColumnWidth(i,256*16);
        }
        //3.设置样式以及字体样式
        HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
        HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
        HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);
        //行号
        int rowNum = 0;
        //第1行
        HSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short)600);
        String[] row_first = {"施工许可号","监督编号","工程名称","工程情况","","","","","","","","","","","","","","","","","形象进度","","","","","","","竣工验收日期","监督小组成员","备注"};
        for (int i = 0; i < row_first.length; i++) {
            HSSFCell tempCell1 = row0.createCell(i);
            tempCell1.setCellValue(row_first[i]);
            tempCell1.setCellStyle(headerStyle);
        }
        //第2行
        HSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short)600);
        String[] row_second = {"","","","建筑类别","建筑子类别","监督面积(㎡)","工程造价(万元)","地上层次","地下层次","高度","工程报监日期","实际开工日期","建设单位","施工单位","监理单位","设计单位",
                "勘察单位","图审机构","检测机构","工程地址","桩基","深基坑","基础","主体","装饰","节能","幕墙","","",""};
        for (int i = 0; i < row_second.length; i++) {
            HSSFCell tempCell2 = row1.createCell(i);
            tempCell2.setCellValue(row_second[i]);
            tempCell2.setCellStyle(headerStyle);
        }
    
        // 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));//工程名称
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 18));//工程情况
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 25));//形象进度
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 26, 26));//竣工验收日期
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 27, 27));//监督小组成员
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 28, 28));//备注
        List<ManagerProjectBean> managerProjectList = managerProjectService.findManagerProjectList(bean, userInfoBean);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if(null != managerProjectList){
            for(int i = 0; i < managerProjectList.size();i++){
                HSSFRow tempRow = sheet.createRow(i+2);
                tempRow.setHeight((short)600);
                //循环单元格填入数据
                for(int j=0;j<30;j++){
                    HSSFCell tempCell = tempRow.createCell(j);
                    tempCell.setCellStyle(contentStyle);
                    String cellValue = "";
                    if(j ==0){
                        cellValue = managerProjectList.get(i).getBuilderLicense();
                    }else if(j == 1){
                        cellValue = managerProjectList.get(i).getProjectCode();
                    }else if(j == 2){
                        cellValue = managerProjectList.get(i).getProjectName();
                    }else if(j ==3){
                        cellValue = StringUtils.isNotEmpty(managerProjectList.get(i).getBuildType()) ?
                                DictUtils.getDictValue(managerProjectList.get(i).getBuildType(), "build_type", "") : "";
                    }else if(j == 4){
                        cellValue = StringUtils.isNotEmpty(managerProjectList.get(i).getBuildChildType()) ?
                                DictUtils.getDictValue(managerProjectList.get(i).getBuildChildType(), "build_child_type", "") : "";
                    }else if(j ==5){
                        cellValue = null != managerProjectList.get(i).getAllArea() ?  managerProjectList.get(i).getAllArea().toString() : "";
                    }else if(j == 6){
                        cellValue = null != managerProjectList.get(i).getProjectCost() ? managerProjectList.get(i).getProjectCost().toString() : "";
                    }else if(j == 7){
                        cellValue = managerProjectList.get(i).getFloorUp();
                    }else if(j ==8){
                        cellValue = managerProjectList.get(i).getFloorDown();
                    }else if(j == 9){
                        cellValue = StringUtils.isNotEmpty(managerProjectList.get(i).getBuildHeightStr()) ? managerProjectList.get(i).getBuildHeightStr() : "";
                    }else if(j == 10){
                        cellValue = null != managerProjectList.get(i).getReportDate() ? sdf.format(managerProjectList.get(i).getReportDate()) : "";
                    }else if(j == 11){
                        cellValue = null != managerProjectList.get(i).getBeginDate() ? sdf.format(managerProjectList.get(i).getBeginDate()) : "";
                    }else if(j == 12){
                        cellValue = managerProjectList.get(i).getJsUnit();
                    }else if( j == 13){
                        cellValue = managerProjectList.get(i).getSgUnit();
                    }else if(j == 14){
                        cellValue = managerProjectList.get(i).getJlUnit();
                    }else if(j ==15){
                        cellValue = managerProjectList.get(i).getSjUnit();
                    }else if(j == 16){
                        cellValue = managerProjectList.get(i).getKcUnit();
                    }else if(j == 17){
                        cellValue = managerProjectList.get(i).getTsUnit();
                    }else if(j == 18){
                        cellValue = managerProjectList.get(i).getJcUnit();
                    }else if(j == 19){
                        cellValue = managerProjectList.get(i).getAddress();
                    }else if(j == 20){
                        cellValue = managerProjectList.get(i).getZj();
                    }else if(j == 21){
                        cellValue = managerProjectList.get(i).getSjk();
                    }else if(j == 22){
                        cellValue = managerProjectList.get(i).getJc();
                    }else if(j == 23){
                        cellValue = managerProjectList.get(i).getZt();
                    }else if(j == 24){
                        cellValue = managerProjectList.get(i).getZs();
                    }else if(j == 25){
                        cellValue = managerProjectList.get(i).getJn();
                    }else if(j == 26){
                        cellValue = managerProjectList.get(i).getMq();
                    }else if(j == 27){
                        cellValue = null != managerProjectList.get(i).getEndDate() ? sdf.format(managerProjectList.get(i).getEndDate()) : "";
                    }else if(j == 28){
                        cellValue = managerProjectList.get(i).getMonitorZl();
                    }else if(j == 29){
                        cellValue = managerProjectList.get(i).getRemark();
                    }
                    tempCell.setCellValue(cellValue);
                }
            }
        }
    
        String fileName = "年度新增工程统计.xls";
        try {
            fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
            response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
            OutputStream stream = response.getOutputStream();
            if(null != wb && null != stream){
                wb.write(stream);
                wb.close();
                stream.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    
    展开全文
  • 支持从数据库中查询到数据,以多级表头形式导入到Excel文件中。比如:一级表头:学号、姓名、各科成绩。二级表头:语文、数学、英语(二级表头在各科成绩下面)。
  • java导出excel复合表头简单实例

    热门讨论 2014-05-10 13:11:32
    本资源实现了Excel的简单导出实例,同时也实现了导出Excel复合表头的简单实例,适合一些需要用到导出Excel技术的初学者。
  • import java.io.FileOutputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Comparator; import java.util.List; import java.util.UUID; ...
  • easyexcel 动态导出复杂表头 例子 带cellRange
  • java 导出Excel表头

    2021-06-18 13:45:42
    1、表头数据从前端传入 //导出模板 export function exportExcel() { let lists = [ "姓名", "身份证", "性别", "年龄" ] let param = { title:"数据导入模板", colNames:lists } return request({ ...
  • 设置Java导出Excel表头

    千次阅读 2021-02-28 07:43:38
    1、问题背景有一个学生表,需要导出Excel,有学号、姓名、性别和年龄四个字段2、实现源码/**** @Project:Report* @Title:ExcelExport.java* @Package:com.you.excel* @Description:* @Author:YouHaiDong* @Date:...
  • 今天这篇文章就是分享导出Excel单表头或多表头的实现,目前实现方案仅支持2行表头场景。如有更复杂的3行表头、4行表头复杂需求可以自行实现。二、实现思路1.借助POI包实现表头的写入。每个表头其实就是一行,如果是...
  • 主要为大家详细介绍了java生成可变表头excel的方法,传入一个表头和数据,将数据导入到excel中,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
  • Java导出Excel表头

    2021-03-13 00:54:09
    1、问题背景Java导出Excel表格时,表头出现了三个,即多表头Excel2、实现源码/**** @Project:Report* @Title:ThreeHead.java* @Package:com.you.excel* @Description:* @Author:YouHaiDong* @Date:2015年11月4日 ...
  • 使用POI导出Excel复杂表头(超详细)

    千次阅读 2022-05-13 14:58:51
    相信很多小伙伴不管是在项目开发中还是在学习过程中,会碰到这样的需求:导出一些复杂表头的报表。今天看我这篇保证你不在为此头痛,Begin 引入依赖: <dependency> <groupId>org.apache.poi<...
  • 注解反射导出Excel自定义中文表头,数据库查出数据,亲测可用
  • java导出excel多重表头

    2018-02-05 01:59:01
    后台代码对多重表头要怎么排列?比如星期一下面还有早上,中午晚上,再下面才是动态数据! ![图片说明](https://img-ask.csdn.net/upload/201802/05/1517795905_167671.png)
  • 多级表头复杂表头导出功能都可以仿照这个例子去编写提示以下是本篇文章正文内容,下面案例可供参考
  • Java实现Excel表头动态数据导出

    千次阅读 2020-07-23 13:05:12
    好久没时间写帖子了,由于工作需要,写一个基于JAVA实现的Excel表头动态导出功能,首先可能这个叫法比较啰嗦,下面我们先看看什么是Excel表头动态导出(效果图): 它包含两部分:1、是表头,就像大家看到的...
  • 导出excel表头-java

    2021-06-24 14:20:30
    首先在导出excel 是通过poi 实现的,但是这里我采用的是阿里巴巴提供的 easyExcel 实现的多表头导出,当然还需要数据列对应类的属性,用属性获取数据并实现数据导出。 第一步:首先需要导入maven 坐标: <...
  • 利用Hutool——ExcelWriter 导出Excel 多级表头。操作思路,对单个单元格进行操作,并赋值。
  • import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.use...
  • 导出excel的时候,需要自己设计复杂表头,如下: 首先理解下表格的位置,二维的,从0开始,依次增加。 表格1:理解表格的位置 红色表示单个的位置 合并语法: new CellRangeAddress(int firstRow, int last...
  • //表头1 要合并的格表头描述字段 已@分割 注意 这里的表头是至第一行表头 var gauge_head2="日期@交易量汇总@设备占比@WEB占比@APP占比@互生币支付@互商订单支付@代兑互生币@兑换互生币@货币转银行";// 表头描述...
  • 导出Excel文件是业务中经常遇到的需求,以下是经常遇到的一些问题:1,导出中文文件名乱码String filename = "sheet1";response.setCharacterEncoding("UTF-8");response.setContentType("application/octet-stream...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 11,090
精华内容 4,436
关键字:

java导出excel复杂表头