poi写入excel基本操作

poi写入excel基本操作

参考

【Java】使用Apache POI生成和解析Excel文件

POI导出excel--设置字体,颜色,行高自适应,列宽自适应,锁住单元格,合并单元格

依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.14</version>
</dependency>

操作类

package excel;

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

import java.io.FileOutputStream;
import java.io.IOException;

/*
合并单元格的边框需要先创建单元格，再设置边框。先设置边框后创建单元格会影响边框。
参考
POI导出excel--设置字体,颜色,行高自适应,列宽自适应,锁住单元格,合并单元格
https://blog.csdn.net/zzzgd_666/article/details/80627175
 */
public class Excel_01 {
    public static void main(String[] args) throws IOException {
//        创建空文件();
//        设置行高();
//        单元格文本字体颜色边框对齐();
        合并单元格();
    }

    public static void 创建空文件() throws IOException {
        String file = "d:\00\01\a.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.write(new FileOutputStream(file));
    }

    public static void 设置行高() throws IOException {
        String file = "d:\00\01\a.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("我的sheet");

        //sheet.setDefaultRowHeight((short) 20);//错误
        sheet.setDefaultRowHeightInPoints((short) 20);
        workbook.write(new FileOutputStream(file));
    }

    public static void 合并单元格() throws IOException {
        String file = "d:\00\01\a.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("我的sheet");
        HSSFRow row = sheet.createRow(1);
        HSSFCell cell = row.createCell(1);

        //索引是基于0的，也就是从0开始。包含起始和结束。[起始，结束]，具体可以查看源码注释。
        CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 2, 1, 12);
        sheet.addMergedRegion(cellRangeAddress);

        //设置合并单元格的边框
        RegionUtil.setBorderTop(CellStyle.BORDER_DASH_DOT_DOT, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderBottom(CellStyle.BORDER_HAIR, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderLeft(CellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderRight(CellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook);

        RegionUtil.setTopBorderColor(HSSFColor.BROWN.index, cellRangeAddress, sheet, workbook);
        RegionUtil.setBottomBorderColor(HSSFColor.BROWN.index, cellRangeAddress, sheet, workbook);
        RegionUtil.setLeftBorderColor(HSSFColor.BROWN.index, cellRangeAddress, sheet, workbook);
        RegionUtil.setRightBorderColor(HSSFColor.BROWN.index, cellRangeAddress, sheet, workbook);

        //设置合并单元格行高
        row.setHeightInPoints(40);

        //设置合并单元格文本内容，为合并单元格的第1个单元格设置内容即为合并单元格设置内容。
        cell.setCellValue("味遇日结账单信息表");

        //设置合并单元格样式
        HSSFCellStyle hssfCellStyle = cellStyle(workbook);
        cell.setCellStyle(hssfCellStyle);

        workbook.write(new FileOutputStream(file));
    }

    public static HSSFCellStyle cellStyle(HSSFWorkbook workbook){
        /*边框*/
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(CellStyle.BORDER_DOUBLE);
        cellStyle.setBorderLeft(CellStyle.BORDER_DASH_DOT);
        cellStyle.setBorderRight(CellStyle.BORDER_DASH_DOT_DOT);

        /*对齐*/
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        /*字体*/
        HSSFFont font = workbook.createFont();
        //加粗
        font.setBold(true);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        //大小
        font.setFontHeightInPoints((short) 15);
        //字体
        font.setFontName("微软雅黑");
        //颜色
        font.setColor(HSSFColor.BRIGHT_GREEN.index);

        //自定义颜色
        HSSFPalette customPalette = workbook.getCustomPalette();
        customPalette.setColorAtIndex(HSSFColor.BLACK.index, (byte)0x8d, (byte)0x3c, (byte)0xc4);
        customPalette.setColorAtIndex(HSSFColor.BLACK.index, (byte)254, (byte)155, (byte)28);
        font.setColor(HSSFColor.BLACK.index);

        cellStyle.setFont(font);
        return cellStyle;
    }

    public static void 单元格文本字体颜色边框对齐() throws IOException {
        String file = "d:\00\01\a.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("我的sheet");
        HSSFRow row = sheet.createRow(1);
        HSSFCell cell = row.createCell(1);
        HSSFCellStyle hssfCellStyle = cellStyle(workbook);
        cell.setCellStyle(hssfCellStyle);
        workbook.write(new FileOutputStream(file));
    }
}