java 写Excel （Excel表格风格设置，Excel合并单元格，Excel设置背景色，Excel添加边框，Excel插入图片）

注意：此示例采用poi-3.17版本，其他版本api可能略有不同

poi.jar 地址：https://download.csdn.net/download/zheng_chang_wei/10620928

 
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
 
import javax.imageio.ImageIO;
 
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ExcelWriter {
 
    private static List<CellRangeAddress> cellRangeAddressList = new ArrayList<CellRangeAddress>();
 
    /**
     * 将elements中的数据保存到excel文件中
     * 
     * @param excelPath
     * @param userList
     * @param elements
     * @throws Exception
     */
    public static void writer(String excelPath, List<User> userList) throws Exception {
        Workbook wb = null;
        if (excelPath.endsWith("xls")) {
            wb = new HSSFWorkbook();
        } else if (excelPath.endsWith("xlsx")) {
            wb = new XSSFWorkbook();
        }
        Sheet sheet = wb.createSheet("sheetname");
        int stratRow = 0;
        int startLine = 0;
        Row row = sheet.createRow(stratRow);
        Cell cell = row.createCell(startLine);
        cell.setCellValue("标题");
 
        CellStyle headStyle = createHeadStyle(wb);
        cell.setCellStyle(headStyle);
        // 合并单元格 三行两列
        mergeCells(sheet, stratRow, 2, 0, 1);
        stratRow = stratRow + 3;
        // 水平垂直居中风格
        CellStyle vHCenterStyle = createVHCenterStyle(wb);
        for (User user : userList) {
            row = sheet.createRow(stratRow);
            cell = row.createCell(startLine);
 
            cell.setCellValue(user.getName());
            cell.setCellStyle(vHCenterStyle);
            startLine++;
            cell = row.createCell(startLine);
            cell.setCellValue(user.getAge());
            cell.setCellStyle(vHCenterStyle);
            startLine = 0;
            stratRow++;
        }
        String imagePath = "要插入的图片的路径.png";
        addImage(wb, sheet, imagePath, 0, 0, 0, 0, 0, stratRow, 12);
        // 添加边框
        addBorder(sheet);
        // 创建文件流
        final OutputStream stream = new FileOutputStream(excelPath);
        // 写入数据
        wb.write(stream);
        // 关闭文件流
        stream.close();
    }
 
    /**
     * 合并单元格的边框添加
     * 
     * @param wb
     * @param sheet
     */
    private static void addBorder(final Sheet sheet) {
        for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
        }
        cellRangeAddressList.clear();
    }
 
    /**
     * 插入图片
     * 
     * @param wb
     * @param sheet
     * @param imagePath
     * @param dx1
     *            起始单元格的x偏移量
     * @param dy1
     *            起始单元格的y偏移量
     * @param dx2
     *            终止单元格的x偏移量
     * @param dy2
     *            终止单元格的y偏移量
     * @param col1
     *            起始单元格列序号
     * @param row1
     *            起始单元格列序号
     * @param col2
     *            终止单元格列序号
     * @return 写到了哪一行
     */
    private static int addImage(final Workbook wb, final Sheet sheet, final String imagePath, final int dx1, final int dy1, final int dx2, final int dy2, int col1, final int row1, final int col2) {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try {
            File imageFile = new File(imagePath);
            if (imageFile.exists()) {
                BufferedImage bufferImg = ImageIO.read(imageFile);
                int width = bufferImg.getWidth();
                int height = bufferImg.getHeight();
                // 每个像素占多少行，大约
                double rateR = 40.5 / 768;
                // 每个像素占多少列，大约
                double rateC = 14.3 / 1024;
                int col = (int) (rateC * width) + 1;
                int row = (int) (rateR * height) + 1;
                ClientAnchor anchor = null;
                if (sheet instanceof HSSFSheet) {
                    // 当图片宽度超过指定列数时，结束列按指定列数，当图片宽度小于指定列数时，居中显示
                    if (col > col2) {
                        anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, (short) col1, row1, (short) col2, row1 + row);
                    } else {
                        col1 = (col2 - col) / 2;
                        anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, (short) col1, row1, (short) (col2 - col1), row1 + row);
                    }
                } else if (sheet instanceof XSSFSheet) {
                    if (col > col2) {
                        anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row1 + row);
                    } else {
                        col1 = (col2 - col) / 2;
                        anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2 - col1, row1 + row);
                    }
                }
                ImageIO.write(bufferImg, "png", byteArrayOutputStream);
                Drawing<?> patriarch = sheet.createDrawingPatriarch();
                patriarch.createPicture(anchor, wb.addPicture(byteArrayOutputStream.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
                mergeCells(sheet, row1, row1 + row - 1, 0, 12);
                return row1 + row;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (byteArrayOutputStream != null) {
                try {
                    byteArrayOutputStream.close();
                    byteArrayOutputStream = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return row1;
    }
 
    /**
     * 合并单元格
     * 
     * @param sheet
     * @param firstRow
     *            起始行
     * @param lastRow
     *            结束行
     * @param firstCol
     *            起始列
     * @param lastCol
     *            结束列
     */
    private static void mergeCells(final Sheet sheet, final int firstRow, final int lastRow, final int firstCol, final int lastCol) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellRangeAddress);
        cellRangeAddressList.add(cellRangeAddress);
    }
 
    /**
     * 创建浅蓝绿色背景风格
     * 
     * @param wb
     */
    private static CellStyle createHeadStyle(final Workbook wb) {
        CellStyle style = createVHCenterStyle(wb);
        final Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short) 300);
        font.setBold(true);
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }
 
    /**
     * 垂直水平居中风格
     * 
     * @param wb
     * @return
     */
    private static CellStyle createVHCenterStyle(final Workbook wb) {
        final CellStyle style = wb.createCellStyle(); // 样式对象
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
        style.setAlignment(HorizontalAlignment.CENTER);// 水平
        style.setWrapText(true);// 指定当单元格内容显示不下时自动换行
        // 添加边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        return style;
    }
 
    public static void main(String[] args) {
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            userList.add(new User("张三" + i, i + 10));
        }
        String excelPath = "user.xls";
        try {
            ExcelWriter.writer(excelPath, userList);
        } catch (Exception e) {
            e.printStackTrace();
        }
 
    }
}
 
class User {
    private String name;
    private int age;
 
    public User(String name, int age) {
        super();
        this.name = name;
        this.age = age;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public int getAge() {
        return age;
    }
 
    public void setAge(int age) {
        this.age = age;
    }
 
}

 

背景色颜色类型：https://blog.csdn.net/hantiannan/article/details/5312133

需将上述链接中的HSSFColor换成IndexedColors

生成的Excel如下图所示：

 

 

转载至：https://blog.csdn.net/zheng_chang_wei/article/details/81948495