yu9851910
yu9851910
采纳率77.4%
2018-02-05 01:59 阅读 3.0k
已采纳

java导出excel多重表头

20

后台代码对多重表头要怎么排列?比如星期一下面还有早上,中午晚上,再下面才是动态数据!
图片说明

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

7条回答 默认 最新

  • 已采纳
    qq_25127109 qq_25127109 2018-02-05 02:06

    package com.you.excel;

    import java.io.FileOutputStream;

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

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

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

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

    /**

    • 请用一句话概括功能

    • @ClassName:ExcelExport
    • @Description:
    • @Author:YouHaiDong
    • @Date:2015年11月4日 下午2:23:49
    • */

      public class ExcelExport

      {

      /**

      • @Title:ExcelExport
      • @Description:
      • @param args
      • @Date:2015年11月4日 下午2:23:49
      • @return: void
      • @throws Exception
        */

        @SuppressWarnings("resource")

        public static void main(String[] args) throws Exception

        {

        // 创建一个Workbook

        HSSFWorkbook workbook = new HSSFWorkbook();

        // 创建一个sheet页

        HSSFSheet sheet = workbook.createSheet("学生表");

        // 创建第一行

        HSSFRow row = sheet.createRow(0);

        // 创建单元格

        HSSFCell cell1 = row.createCell(0);

        HSSFCell cell2 = row.createCell(1);

        HSSFCell cell3 = row.createCell(2);

        HSSFCell cell4 = row.createCell(2);

        // 设置表头

        cell1.setCellValue("学号");

        cell2.setCellValue("姓名");

        cell3.setCellValue("性别");

        cell4.setCellValue("年龄");

        FileOutputStream stream = new FileOutputStream("d:/student.xls");

        workbook.write(stream);

        }

    }

    点赞 评论 复制链接分享
  • qq_40574584 六月下起了大雪 2018-02-05 02:11

    开始列,开始行,结束列,结束行
    和 excelUtil.mergeCellsAndInsertData(sheet, 14, 1, 14, 2, "", titleWcf);
    中间的4个数字对应填写即可。

    点赞 评论 复制链接分享
  • qq_40574584 六月下起了大雪 2018-02-05 02:12
        //创建标题(合并单元格) 开始列,开始行,结束列,结束行
        excelUtil.mergeCellsAndInsertData(sheet, 0, 0, 14, 0, yearMonth+"表", themWcf);
        //创建表头
        excelUtil.mergeCellsAndInsertData(sheet, 0, 1, 0, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 1, 1, 1, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 2, 1, 2, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 3, 1, 3, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 4, 1, 4, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 5, 1, 5, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 6, 1, 6, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 7, 1, 8, 1, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 7, 2, 7, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 8, 2, 8, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 9, 1, 9, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 10, 1, 10, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 11, 1, 11, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 12, 1, 12, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 13, 1, 13, 2, "", titleWcf);
        excelUtil.mergeCellsAndInsertData(sheet, 14, 1, 14, 2, "", titleWcf);
    
    
        你可以根据几行几列这样填写,注意:都是0开始
    
    点赞 评论 复制链接分享
  • liu13816947142 lyter1214 2018-02-05 02:14

    只要表头固定跟普通excel解析一样的,有总共有多少列全部建出来,对应填充数据就可以

    点赞 评论 复制链接分享
  • u012958215 一兜兜 2018-02-05 02:17

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet(DateConverter.currentDate(new Date()));

    sheet.setDefaultColumnWidth(15);

    HSSFRow titleRow = sheet.createRow(0);
    HSSFCell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("填报工时");
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //合并单元格

    注意最后一行 合并单元格

    点赞 评论 复制链接分享
  • qq_23126581 郭老师的小迷弟雅思莫了 2018-02-05 02:34

    建议使用excel模板做,表头自己定义就行,然后就是对应的填充数据了。

    点赞 评论 复制链接分享
  • htao201 htao201 2018-02-05 02:47

    import java.sql.SQLException;
    import java.util.List;

    import javax.servlet.http.HttpServletRequest;

    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.HSSFPrintSetup;
    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;

    public class Excel {

         private HSSFWorkbook wb = null;
    
         private HSSFSheet sheet = null;
    
         private HSSFRow row = null;
    
         private HSSFCell cell = null;
    
         private HSSFCellStyle titleStyle = null;
    
         private HSSFCellStyle headStyle = null;
    
         private HSSFCellStyle bodyStyle = null;
    
         private int num = 0;
    
         private int headLength = 0;
    
         public Excel() {
                   wb = new HSSFWorkbook();
                   sheet = wb.createSheet();
                   sheet.setDefaultColumnWidth(14);
                   sheet.setDefaultRowHeight((short)20);
                   //打印设置
                   HSSFPrintSetup hps = sheet.getPrintSetup(); 
                   hps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 设置A4纸
                   hps.setLandscape(true); // 将页面设置为横向打印模式
    

    // sheet.setHorizontallyCenter(true); // 设置打印页面为水平居中
    // sheet.setVerticallyCenter(true); // 设置打印页面为垂直居中
    //冻结第一行和第二行
    sheet.createFreezePane( 0, 2, 0, 2 );

                   init();
         }
    
         /**
          * 初始化样式
          */
         private void init() {
                   titleFont();
                   headFont();
                   bodyFont();
         }
    
         /**
          * 设置标题样式
          * 
          */
         private void titleFont() {
                   HSSFFont titleFont = wb.createFont();
                   titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                   titleFont.setFontName("宋体");
                   titleFont.setFontHeightInPoints((short) 18);
                   titleStyle = wb.createCellStyle();
                   titleStyle.setFont(titleFont);
    

    // titleStyle.setBorderTop((short)1);
    // titleStyle.setBorderRight((short)1);
    titleStyle.setBorderBottom((short)1);
    // titleStyle.setBorderLeft((short)1);

                   titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
         }
    
         /**
          * 设置head样式
          * 
          */
         private void headFont() {
    
                   HSSFFont headFont = wb.createFont();
                   headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                   headFont.setFontName("宋体");
                   headFont.setFontHeightInPoints((short) 11);
                   headStyle = wb.createCellStyle();
                   headStyle.setFont(headFont);
                   headStyle.setBorderTop((short)1);
                   headStyle.setBorderRight((short)1);
                   headStyle.setBorderBottom((short)1);
                   headStyle.setBorderLeft((short)1);
                   headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         }
    
         /**
          * 设置body样式
          * 
          */
         private void bodyFont() {
                   HSSFFont bodyFont = wb.createFont();
                   bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
                   bodyFont.setFontName("宋体");
                   bodyFont.setFontHeightInPoints((short) 9);
                   bodyStyle = wb.createCellStyle();
                   bodyStyle.setFont(bodyFont);
                   bodyStyle.setBorderTop((short)1);
                   bodyStyle.setBorderRight((short)1);
                   bodyStyle.setBorderBottom((short)1);
                   bodyStyle.setBorderLeft((short)1);
                   bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                   bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
         }
    
         /**
          * 生成Excel主方法
          * @param xml :包括title和head的内容和设置
          * @param list :内容
          * @param fit :是否需要宽度自适应
          * @return HSSFWorkbook
          * @throws SQLException
          */
         public HSSFWorkbook createWorkBook(final ExcelHeadXML xml, final List<Object[]> list, boolean fit) throws SQLException {
                   createTop(xml);
                   /*
                   List<Object[]> list1 = new LinkedList<Object[]>();
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   list1.add(new Object[]{"000003","武汉市XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX公司","XXXXXX","VVVVVVVV","dddd","23","2009-04-04"});
                   createBody(list1,true);
                   */
                   createBody(list,true);
                   //宽度自适应
                   if(fit){
                            for(int i=0; i<headLength; i++){
                                     sheet.autoSizeColumn(i); 
                            }
                   }
                   return wb;
         }
    
         /**
          * 生成Excel主方法
          * @param xml :包括title和head的内容和设置
          * @param list :内容
          * @param fit :是否需要宽度自适应
          * @return HSSFWorkbook
          * @throws SQLException
          */
         public HSSFWorkbook createWorkBook(final ExcelHeadXML xml,HttpServletRequest request, final List<Object[]> list, boolean fit) throws SQLException {
                   createTop(xml,request);
                   createBody(list,true);
                   //宽度自适应
                   if(fit){
                            for(int i=0; i<headLength; i++){
                                     sheet.autoSizeColumn(i); 
                            }
                   }
                   return wb;
         }
    
         /**
          * 创建Excel的Head和title部分
          * @param xml ExcelHeadXML
          */
         @SuppressWarnings("deprecation")
         private void createTop(final ExcelHeadXML xml,HttpServletRequest request) {
                   //创建Title部分
                   if (null != xml.getTitle() && !"".equals(xml.getTitle())) {
                            row = sheet.createRow(num++);
                            cell = row.createCell(0);
                            cell.setCellStyle(titleStyle);
                            sheet.addMergedRegion(new CellRangeAddress(xml.getUniteRowStart(),
                                               xml.getUniteRowEnd(), xml.getUniteCellStart(), xml
                                                                 .getUniteRowCount()));
    
                   }
                   //创建Head部分
                   List<String> head = xml.getHead();
                   if(null != head && head.size() > 0){
                            headLength = xml.getHeadLength();
                            row = sheet.createRow(num++);
                            for(int i=0; i<head.size(); i++){
                                     cell = row.createCell(i);
                                     cell.setCellStyle(headStyle);
                                     cell.setCellValue(new HSSFRichTextString(head.get(i)));
    
                            }
                   }
         }
    
    
         /**
          * 创建Excel的Head和title部分
          * @param xml ExcelHeadXML
          */
         @SuppressWarnings("deprecation")
         private void createTop(final ExcelHeadXML xml) {
                   //创建Title部分
                   if (null != xml.getTitle() && !"".equals(xml.getTitle())) {
                            row = sheet.createRow(num++);
                            cell = row.createCell(0);
                            cell.setCellStyle(titleStyle);
                            cell.setCellValue(new HSSFRichTextString(xml.getTitle().trim()));
                            sheet.addMergedRegion(new CellRangeAddress(xml.getUniteRowStart(),
                                               xml.getUniteRowEnd(), xml.getUniteCellStart(), xml
                                                                 .getUniteRowCount()));
    
                   }
                   //创建Head部分
                   List<String> head = xml.getHead();
                   if(null != head && head.size() > 0){
                            headLength = xml.getHeadLength();
                            row = sheet.createRow(num++);
                            for(int i=0; i<head.size(); i++){
                                     cell = row.createCell(i);
                                     cell.setCellStyle(headStyle);
                                     cell.setCellValue(new HSSFRichTextString(head.get(i)));
    
                            }
                   }
         }
         /**
          * 创建Excel的body部分
          * @param list : body部分的数据
          * @param useId : body部分是否要序号
          * @throws SQLException 
          */
         private void createBody(final List<Object[]> list, boolean useId) throws SQLException {
                   if(null != list && list.size() > 0){
                            Object[] o;
    
                            for(int i=0; i<list.size(); i++){
                                     row = sheet.createRow(num++);
                                     row.setHeightInPoints(30);
                                     o = list.get(i);
                                     if(useId){
                                               //序号
                                               cell = row.createCell(0);
                                               cell.setCellStyle(bodyStyle);
                                               cell.setCellValue(new HSSFRichTextString((i+1)+""));
    
                                     }
                                     for(int j=0; j<o.length; j++){
                                               if(useId){
                                                        cell = row.createCell(j+1);
                                               }else{
                                                        cell = row.createCell(j);
                                               }
                                               cell.setCellStyle(bodyStyle);
                                               cell.setCellValue(new HSSFRichTextString(emptyToString(o[j])));
    
                                     }
                            }
                   }
         } 
    
    点赞 评论 复制链接分享

相关推荐