- 之所以用到poi,是因为本次需求用easyExcel不好解决,最后无奈之下还是用到了poi,现把本次的poi的常用操作记录如下:
- poi分为2003版(.xls文件)和2007版(.xlsx文件),本次只介绍07版Excel的基础操作。
话不多说,先贴jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
创建工作簿
Workbook wb = new XSSFWorkbook();
创建sheet表单
Sheet sheet = workbook.createSheet("test1");
创建行对象
Row row = sheet.createRow(1);
创建单元格
Cell cell = row.createCell(1);
设置单元格内容
cell.setCellValue("上面的例子是第二行第二个单元格");
写入文件
FileOutputStream fos = new FileOutputStream("/Users/test1.xlsx");
workbook.write(fos);
fos.close();
创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
byte[] rgb = {(byte) 217, (byte) 216, (byte) 216};
cellStyle.setFillForegroundColor(new XSSFColor(rgb, new DefaultIndexedColorMap()));
cellStyle.setBorderTop(BorderStyle.DOUBLE);
cellStyle.setBorderBottom(BorderStyle.DOTTED);
cellStyle.setBorderLeft(BorderStyle.DASH_DOT);
cellStyle.setBorderRight(BorderStyle.DASHED);
cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
设置字体字号以及颜色
Font font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 28);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(IndexedColors.WHITE.index);
cellStyle.setFont(font);
cellStyle.setWrapText(true);
合并单元格
CellRangeAddress region = new CellRangeAddress(0, 2, 0, 2);
sheet.addMergedRegion(region);
绘制图形
FileInputStream fis = new FileInputStream("/Users/Downloads/黄昏.jpg");
int pictureIndex = ((XSSFWorkbook) workbook).addPicture(fis, Workbook.PICTURE_TYPE_JPEG);
CreationHelper creationHelper = workbook.getCreationHelper();
Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
ClientAnchor clientAnchor = creationHelper.createClientAnchor();
clientAnchor.setRow1(0);
clientAnchor.setCol1(0);
Picture picture = drawingPatriarch.createPicture(clientAnchor,pictureIndex);
picture.resize();
- 我的电脑是Mac,所以存储路径和Windows稍有区别。
- 合电脑,打道回府