excel怎样套用表格,怎么用excel制作表格

问题:excel如何套用表格?

导出excel表格,excel表格

前提:引入jar包

 1 <dependency>
 2     <groupId>org.apache.poi</groupId>
 3     <artifactId>poi-ooxml</artifactId>
 4     <version>3.10-FINAL</version>
 5 </dependency>
 6 <dependency>
 7     <groupId>org.apache.poi</groupId>
 8     <artifactId>poi</artifactId>
 9     <version>3.17</version>
10 </dependency>
11 <dependency>
12     <groupId>junit</groupId>
13     <artifactId>junit</artifactId>
14     <version>4.11</version>
15     <scope>test</scope>
16 </dependency>

将数据库表中的字段设置为表格表头,对应的笔录3回写入到excel中。

后台代码:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.HSSFPatternFormatting;
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;
import org.junit.Test;

/**
 * 导出excel
 * @author hewangtong
 *
 */
@SuppressWarnings("deprecation")
public class ExportExcel {

    /**
     * 导出excel
     * @param out 输出到哪里
     * @param students 学生信息
     * @param title excel中内容标题
     * @param headers 学生信息表中的头标签
     */
    public void exportExcel(OutputStream out, List<Map<String, Object>> students, String title, String[] headers){
        //1.创建一个工作簿。
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建一个sheet页
        HSSFSheet sheet = workbook.createSheet("student-info");
        //3.设置sheet页的列宽
        sheet.setDefaultColumnWidth(30);
        //4.解决标题
        //设置标题的样式
        HSSFCellStyle titleCellStyle = workbook.createCellStyle();//创建标题样式
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//使标题居中
        //设置标题的字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontHeight((short) 8);
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setFontName("宋体");
        titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        //将字体样式植入样式中
        titleCellStyle.setFont(titleFont);
        //设置标题位置
        HSSFRow titleRow = sheet.createRow(0);//第一行
        HSSFCell titleCell = titleRow.createCell(0);
        //将标题值和样式放入相应位置
        titleCell.setCellValue(title);
        titleCell.setCellStyle(titleCellStyle);
        //合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(cra);
        //5.解决表格表头
        //学生统计表的表头
        HSSFRow headerRow = sheet.createRow(1);//第二行
        HSSFCell headerCell = null;
        //设置表头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//设置边框
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //以下这两种必须搭配使用
        headerStyle.setFillForegroundColor(HSSFColor.ORANGE.index);//填充前景颜色
        headerStyle.setFillPattern(HSSFPatternFormatting.SOLID_FOREGROUND);//填充模式
        //设置表头字体
        HSSFFont headerFont = workbook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        headerFont.setColor(HSSFColor.GREEN.index);
        headerFont.setFontName("宋体");
        headerFont.setFontHeight((short) 6);
        headerFont.setFontHeightInPoints((short) 12);
        headerStyle.setFont(headerFont);
        for(int i=0; i<headers.length; i++){
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(headers[i]);
            headerCell.setCellStyle(headerStyle);
        }
        //6.解决表格内容
        HSSFRow contentRow = null;
        HSSFCell contentCell = null;
        HSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont contentFont = workbook.createFont();
        contentFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 10);
        contentStyle.setFont(contentFont);
        for(int i=0; i<students.size(); i++){
            contentRow = sheet.createRow(i+2);//第行及其之后行
            contentCell = contentRow.createCell(0);
            contentCell.setCellValue(students.get(i).get("id").toString());
            contentCell.setCellStyle(contentStyle);
            contentCell = contentRow.createCell(1);
            contentCell.setCellValue(students.get(i).get("name").toString());
            contentCell.setCellStyle(contentStyle);
            contentCell = contentRow.createCell(2);
            contentCell.setCellValue(students.get(i).get("sex").toString());
            contentCell.setCellStyle(contentStyle);
            contentCell = contentRow.createCell(3);
            contentCell.setCellValue(students.get(i).get("age").toString());
            contentCell.setCellStyle(contentStyle);
        }
        try {
            //7.将该工作簿输出到指定目录
            workbook.write(out);
            //8.关闭资源
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> getStudents(){
        List<Map<String, Object>> students = new ArrayList<Map<String, Object>>();
        Map<String, Object> map1 = new HashMap<String, Object>();
        Map<String, Object> map2 = new HashMap<String, Object>();
        Map<String, Object> map3 = new HashMap<String, Object>();
        Map<String, Object> map4 = new HashMap<String, Object>();
        Map<String, Object> map5 = new HashMap<String, Object>();
        Map<String, Object> map6 = new HashMap<String, Object>();
        map1.put("id", 1);
        map1.put("name", "张");
        map1.put("sex", "男");
        map1.put("age", 12);
        students.add(map1);
        map2.put("id", 2);
        map2.put("name", "王五");
        map2.put("sex", "男");
        map2.put("age", 21);
        students.add(map2);
        map3.put("id", 3);
        map3.put("name", "赵六");
        map3.put("sex", "男");
        map3.put("age", 15);
        students.add(map3);
        map4.put("id", 4);
        map4.put("name", "晓慧");
        map4.put("sex", "女");
        map4.put("age", 18);
        students.add(map4);
        map5.put("id", 5);
        map5.put("name", "欢欢");
        map5.put("sex", "女");
        map5.put("age", 35);
        students.add(map5);
        map6.put("id", 6);
        map6.put("name", "罗罗");
        map6.put("sex", "女");
        map6.put("age", 56);
        students.add(map6);
        return students;
    }

    @Test
    public void testExportExcel(){
        OutputStream out = null;
        try {
            File file = new File("D:/"+"student"+".xls");
            if(!file.isDirectory()){
                file.createNewFile();
            }
            out = new FileOutputStream(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String[] headers = new String[]{"编号", "姓名", "性别", "年龄"};
        exportExcel(out, getStudents(), "学生统计表", headers);

    }

excel怎样套用表格,怎么用excel制作表格。效果如下:

图片 1

参考:

前提:引入jar包 1
dependency 2 groupId org.apache.poi / groupId 3 artifactId poi-ooxml /
artifactId 4 version 3.10-FINAL / version 5 / depende…

前提:引入jar包

问题:怎么用excel制作表格?

回答:

 1 <dependency>
 2     <groupId>org.apache.poi</groupId>
 3     <artifactId>poi-ooxml</artifactId>
 4     <version>3.10-FINAL</version>
 5 </dependency>
 6 <dependency>
 7     <groupId>org.apache.poi</groupId>
 8     <artifactId>poi</artifactId>
 9     <version>3.17</version>
10 </dependency>
11 <dependency>
12     <groupId>junit</groupId>
13     <artifactId>junit</artifactId>
14     <version>4.11</version>
15     <scope>test</scope>
16 </dependency>

回答:图片工具的功用区

谢邀,小编是Excel大全,头条号原创作者,天天分享实用的Excel技巧。

将数据库表中的字段设置为表格表头,对应的笔录3次写入到excel中。

当您单击图表时,功效区上会引进蕴涵 DESIGN 和 FO奥德赛MAT 选项卡的 CHAPRADOT TOOLS
选项卡。

Excel中,套用表格样式,有多样艺术,都大致的操作:

后台代码:

第二步 – 单击图表。 带有 DESIGN 和 FOMuranoMAT 选项卡的 CHAKugaT TOOLS
将呈未来功能区上。

  • 套用单元格格式;

  • 套用表格样式;

  • 转为表格区域;

  • 透视表样式;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.HSSFPatternFormatting;
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;
import org.junit.Test;

/**
 * 导出excel
 * @author hewangtong
 *
 */
@SuppressWarnings("deprecation")
public class ExportExcel {

    /**
     * 导出excel
     * @param out 输出到哪里
     * @param students 学生信息
     * @param title excel中内容标题
     * @param headers 学生信息表中的头标签
     */
    public void exportExcel(OutputStream out, List<Map<String, Object>> students, String title, String[] headers){
        //1.创建一个工作簿。
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建一个sheet页
        HSSFSheet sheet = workbook.createSheet("student-info");
        //3.设置sheet页的列宽
        sheet.setDefaultColumnWidth(30);
        //4.解决标题
        //设置标题的样式
        HSSFCellStyle titleCellStyle = workbook.createCellStyle();//创建标题样式
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//使标题居中
        //设置标题的字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setFontHeight((short) 8);
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setFontName("宋体");
        titleFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        //将字体样式植入样式中
        titleCellStyle.setFont(titleFont);
        //设置标题位置
        HSSFRow titleRow = sheet.createRow(0);//第一行
        HSSFCell titleCell = titleRow.createCell(0);
        //将标题值和样式放入相应位置
        titleCell.setCellValue(title);
        titleCell.setCellStyle(titleCellStyle);
        //合并单元格
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(cra);
        //5.解决表格表头
        //学生统计表的表头
        HSSFRow headerRow = sheet.createRow(1);//第二行
        HSSFCell headerCell = null;
        //设置表头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//设置边框
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //以下这两种必须搭配使用
        headerStyle.setFillForegroundColor(HSSFColor.ORANGE.index);//填充前景颜色
        headerStyle.setFillPattern(HSSFPatternFormatting.SOLID_FOREGROUND);//填充模式
        //设置表头字体
        HSSFFont headerFont = workbook.createFont();
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        headerFont.setColor(HSSFColor.GREEN.index);
        headerFont.setFontName("宋体");
        headerFont.setFontHeight((short) 6);
        headerFont.setFontHeightInPoints((short) 12);
        headerStyle.setFont(headerFont);
        for(int i=0; i<headers.length; i++){
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(headers[i]);
            headerCell.setCellStyle(headerStyle);
        }
        //6.解决表格内容
        HSSFRow contentRow = null;
        HSSFCell contentCell = null;
        HSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont contentFont = workbook.createFont();
        contentFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 10);
        contentStyle.setFont(contentFont);
        for(int i=0; i<students.size(); i++){
            contentRow = sheet.createRow(i+2);//第行及其之后行
            contentCell = contentRow.createCell(0);
            contentCell.setCellValue(students.get(i).get("id").toString());
            contentCell.setCellStyle(contentStyle);
            contentCell = contentRow.createCell(1);
            contentCell.setCellValue(students.get(i).get("name").toString());
            contentCell.setCellStyle(contentStyle);
            contentCell = contentRow.createCell(2);
            contentCell.setCellValue(students.get(i).get("sex").toString());
            contentCell.setCellStyle(contentStyle);
            contentCell = contentRow.createCell(3);
            contentCell.setCellValue(students.get(i).get("age").toString());
            contentCell.setCellStyle(contentStyle);
        }
        try {
            //7.将该工作簿输出到指定目录
            workbook.write(out);
            //8.关闭资源
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public List<Map<String, Object>> getStudents(){
        List<Map<String, Object>> students = new ArrayList<Map<String, Object>>();
        Map<String, Object> map1 = new HashMap<String, Object>();
        Map<String, Object> map2 = new HashMap<String, Object>();
        Map<String, Object> map3 = new HashMap<String, Object>();
        Map<String, Object> map4 = new HashMap<String, Object>();
        Map<String, Object> map5 = new HashMap<String, Object>();
        Map<String, Object> map6 = new HashMap<String, Object>();
        map1.put("id", 1);
        map1.put("name", "张");
        map1.put("sex", "男");
        map1.put("age", 12);
        students.add(map1);
        map2.put("id", 2);
        map2.put("name", "王五");
        map2.put("sex", "男");
        map2.put("age", 21);
        students.add(map2);
        map3.put("id", 3);
        map3.put("name", "赵六");
        map3.put("sex", "男");
        map3.put("age", 15);
        students.add(map3);
        map4.put("id", 4);
        map4.put("name", "晓慧");
        map4.put("sex", "女");
        map4.put("age", 18);
        students.add(map4);
        map5.put("id", 5);
        map5.put("name", "欢欢");
        map5.put("sex", "女");
        map5.put("age", 35);
        students.add(map5);
        map6.put("id", 6);
        map6.put("name", "罗罗");
        map6.put("sex", "女");
        map6.put("age", 56);
        students.add(map6);
        return students;
    }

    @Test
    public void testExportExcel(){
        OutputStream out = null;
        try {
            File file = new File("D:/"+"student"+".xls");
            if(!file.isDirectory()){
                file.createNewFile();
            }
            out = new FileOutputStream(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String[] headers = new String[]{"编号", "姓名", "性别", "年龄"};
        exportExcel(out, getStudents(), "学生统计表", headers);

    }

图片 2

0一 单元格格式

这几个方法是指向具体某1类别的单元格也许区域的,能够按住<Ctrl>,用鼠标接纳单元格,当然,也足以<Ctrl>+A全选全数单元格,然后,菜单采纳起来,单元格样式,如下所示:

图片 3

成效如下:

让我们询问 DESIGN 选项卡的作用。

02 表格样式

以此是相比较常用的,针对任何办事表区域的体裁,直接套用表格样式,能够实现一键鼓吹表格的功效,每一个样式都很经典哦!

图片 4

这个暗中同意的体制还不够,别忘了,还足以自定义样式哦,如下所示:

图片 5

图片 6

第1步 – 单击图表。

03 表格样式

菜单插入,表格,飞快键是<Ctrl>+T,真正的一键美化表格,将工作表的经常区域转换为表格,这些表格可老好用了,建议大家能够试试,不仅可以套用丰裕的体裁,不用公式便可轻松生成汇总值,仍是能够动态扩张公式和数据源,非凡强劲!

图片 7

图片 8

图片 9

参考:

第三步 – 单击“ 设计” 选项卡。该 功效区 以往来得的保有选项 图设计 。

0四 透视表样式

透视表,当然也有体制,也是基本上,选拔透视表,菜单设计中,就能够一分区直属机关接大选择套用样式了

图片 10

图片 11