前言
Apache下的POI是很好的工具,在需要定制化开发Excel,Word,PDF时很友好。 记录下利用POI生成Excel心得。
EasyExcel也是很不错的工具,可以支持百万级别数据导入导出。EasyExcel连接
一、 简单的demo
@SneakyThrows
public static void main(String[] args) {
String fileName = URLEncoder.encode("poi生成Excel".concat(".xlsx"), GlobalConstant.Sys.UTF8);
// 获取当前项目下的文件
// InputStream siTemplate = this.getClass().getClassLoader().getResourceAsStream("poi生成Excel.xlsx");
// 读模板,填充数据
// XSSFWorkbook wb = new XSSFWorkbook(siTemplate);
XSSFWorkbook wb = new XSSFWorkbook();
// 样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
// 单元格内容上下居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 自动换行
cellStyle.setWrapText(true);
// 字体样式
XSSFFont font = wb.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
// 创建sheet
XSSFSheet sheet = wb.createSheet("你好,sheet!");
// 创建列宽 252*列宽+323
sheet.setColumnWidth(0, 5081);
// 创建单元格
XSSFRow row = sheet.createRow(0);
row.setHeight(new Short("500")); // 设置高度后,自动换行失效
XSSFCell cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue("利用poi生成简单Excel, 很高兴。 \n 只有更加努力,才能看起毫不费力");
FileOutputStream out = new FileOutputStream("D:\\"+fileName);
wb.write(out);
out.close();
wb.close();
}
生成效果图:
二、样式
2、单元格
2.1 合并单元格
方案一:
//指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress = new CellRangeAddress(rowNum1, rowNum2, firstCol, lastCol);
//添加要合并地址到表格
sheet.addMergedRegion(rangeAddress);
方案二:
## 必须要两个及两个以上的单元格才能合并
String region1 = "B1:C2";
CellRangeAddress region = CellRangeAddress.valueOf(region1);
sheet.addMergedRegion(region);
2.2 单元格内容水平、居中
## poi提供了CellUtil
// 设置单元格内容水平垂直居中
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
2.3 单元格 边框
具体可查看RegionUtil
作用:可以使合并单元格的线条补齐; BorderStyle线条粗细(类型)可选
## 同一列,合并不同行时只能显示第一行的边框样式,其余不能显示。
## 可以参考下样式丢失问题 https://www.cnblogs.com/mr-wuxiansheng/p/7911521.html
// 合并单元格左边框样式
RegionUtil.setBorderLeft(BorderStyle.MEDIUM, region, sheet);
// 合并单元格上边框样式
RegionUtil.setBorderTop(BorderStyle.MEDIUM, region, sheet);
// 合并单元格右边框样式
RegionUtil.setBorderRight(BorderStyle.MEDIUM, region, sheet);
// 合并单元格下边框样式
RegionUtil.setBorderBottom(BorderStyle.MEDIUM, region, sheet);
2.4 克隆样式
方法1:
wb.cloneSheet();
方法2:
public static void copySheet(XSSFWorkbook wb,XSSFSheet fromSheet, XSSFSheet toSheet, int readRow) {
mergeSheetAllRegion(fromSheet, toSheet);
//设置列宽
for(int i=0;i<=fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();i++){
toSheet.setColumnWidth(i,fromSheet.getColumnWidth(i));
}
// for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
// XSSFRow oldRow = (XSSFRow) rowIt.next();
// XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
// copyRow(wb,oldRow,newRow);
// }
// 注意模板是否有很多行,复制时间会增大。 可以自己设定行,列
for (int i=0; i<readRow; i++){
XSSFRow oldRow = (XSSFRow) fromSheet.getRow(i);
XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
copyRow(wb,oldRow,newRow);
}
}
public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
toStyle.cloneStyleFrom(fromStyle);//此一行代码搞定
}
public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {//合并单元格
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++) {
cellR = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
XSSFCellStyle newstyle=wb.createCellStyle();
copyCellStyle(fromCell.getCellStyle(), newstyle);
//toCell.setEncoding(fromCell.getEncoding());
//样式
toCell.setCellStyle(newstyle);
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellType();
toCell.setCellType(fromCellType);
if (fromCellType == CellType.NUMERIC) {
toCell.setCellValue(fromCell.getDateCellValue());
} else if (fromCellType == CellType.STRING) {
toCell.setCellValue(fromCell.getRichStringCellValue());
} else if (fromCellType == CellType.BLANK) {
// nothing21
} else if (fromCellType == CellType.BOOLEAN) {
toCell.setCellValue(fromCell.getBooleanCellValue());
} else if (fromCellType == CellType.ERROR) {
toCell.setCellErrorValue(fromCell.getErrorCellValue());
} else if (fromCellType == CellType.FORMULA) {
toCell.setCellFormula(fromCell.getCellFormula());
} else { // nothing29
}
}
public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow){
toRow.setHeight(oldRow.getHeight());
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
XSSFCell tmpCell = (XSSFCell) cellIt.next();
XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb,tmpCell, newCell);
}
}
3. 字体
3.1 字体样式
具体样式可参考源码, 这里只提供案列
## 具体样式可参考源码, 这里只提供案列
XSSFFont font2 = wb.createFont();
//加粗
font2.setBold(ft);
font2.setFontName("Arial");
font2.setFontHeightInPoints((short) 10);
5. 其他格式
5.1 复选框
依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.4</version>
</dependency>
示例代码
/**
*
* @param col1,dx1 表示左上角的单元格的坐标
* @param dx1,dy1 表示左上角的单元格的偏移量
* col2,row2 表示右下角的单元格的坐标
* dx2,dy2 表示右下角的单元格的偏移量
* label 单元格里的描述
* @return
**/
public static void addCheckbox(XSSFVMLDrawing drawing,
int col1, int dx1, int row1, int dy1, int col2, int dx2, int row2, int dy2,
String label, boolean checked) throws Exception {
String shapeTypeId = "_x0000_t201";
Field _shapeId = XSSFVMLDrawing.class.getDeclaredField("_shapeId");
_shapeId.setAccessible(true);
int shapeId = (int)_shapeId.get(drawing);
_shapeId.set(drawing, shapeId + 1);
CTShape shape = CTShape.Factory.newInstance();
shape.setId("_x0000_s" + shapeId);
shape.setType("#" + shapeTypeId);
shape.setFilled(com.microsoft.schemas.vml.STTrueFalse.F);
shape.setStroked(com.microsoft.schemas.vml.STTrueFalse.F);
String textboxHTML = "<div style='text-align:left'>"
+"<font face=\"Tahoma\" size=\"160\" color=\"auto\">" + label + "</font> </div>";
CTTextbox[] textboxArray = new CTTextbox[1];
textboxArray[0] = CTTextbox.Factory.parse(textboxHTML);
textboxArray[0].setStyle("mso-direction-alt:auto");
textboxArray[0].setSingleclick(com.microsoft.schemas.office.office.STTrueFalse.F);
shape.setTextboxArray(textboxArray);
CTClientData cldata = shape.addNewClientData();
cldata.setObjectType(com.microsoft.schemas.office.excel.STObjectType.CHECKBOX);
cldata.addNewMoveWithCells();
cldata.addNewSizeWithCells();
cldata.addNewAnchor().setStringValue("" + col1 + ", " + dx1 + ", " + row1 + ", " +dy1 + ", " + col2 + ", " + dx2 + ", " + row2 + ", " + dy2);
cldata.addAutoFill(com.microsoft.schemas.office.excel.STTrueFalseBlank.FALSE);
cldata.addAutoLine(com.microsoft.schemas.office.excel.STTrueFalseBlank.FALSE);
cldata.addTextVAlign("Center");
cldata.addNoThreeD(com.microsoft.schemas.office.excel.STTrueFalseBlank.TRUE);
cldata.addChecked((checked)?java.math.BigInteger.valueOf(1):java.math.BigInteger.valueOf(0));
Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
_items.setAccessible(true);
@SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
List<XmlObject> items = (List<XmlObject>)_items.get(drawing);
Field _qnames = XSSFVMLDrawing.class.getDeclaredField("_qnames");
_qnames.setAccessible(true);
@SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
List<QName> qnames = (List<QName>)_qnames.get(drawing);
items.add(shape);
qnames.add(new QName("urn:schemas-microsoft-com:vml", "shape"));
}
public static XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet) {
XSSFVMLDrawing drawing = null;
if (sheet.getCTWorksheet().getLegacyDrawing() != null) {
String legacyDrawingId = sheet.getCTWorksheet().getLegacyDrawing().getId();
drawing = (XSSFVMLDrawing)sheet.getRelationById(legacyDrawingId);
}else {
int drawingNumber = sheet.getPackagePart().getPackage().getPartsByContentType(XSSFRelation.VML_DRAWINGS.getContentType()).size() + 1;
POIXMLDocumentPart.RelationPart rp = sheet.createRelationship(XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber, false);
drawing = rp.getDocumentPart();
String rId = rp.getRelationship().getId();
sheet.getCTWorksheet().addNewLegacyDrawing().setId(rId);
}
return drawing;
}
public static void addCheckboxShapeType(XSSFVMLDrawing drawing) throws Exception {
String shapeTypeId = "_x0000_t201";
CTShapetype shapetype = CTShapetype.Factory.newInstance();
shapetype.setId(shapeTypeId);
shapetype.setCoordsize("21600,21600");
shapetype.setSpt(201);
shapetype.setPath2("m,l,21600r21600,l21600,xe");
Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
_items.setAccessible(true);
@SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
List<XmlObject> items = (List<XmlObject>)_items.get(drawing);
Field _qnames = XSSFVMLDrawing.class.getDeclaredField("_qnames");
_qnames.setAccessible(true);
@SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
List<QName> qnames = (List<QName>)_qnames.get(drawing);
items.add(shapetype);
qnames.add(new QName("urn:schemas-microsoft-com:vml", "shapetype"));
}
5.2 上传图片
public static void imgExcel(XSSFWorkbook wb, XSSFSheet sheet, InputStream inputStream, int row1, int col1, int v, int v1){
// InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(path);
byte[] bytes = new byte[0];
try {
bytes = IOUtils.toByteArray(inputStream);
int picture = wb.addPicture(bytes, wb.PICTURE_TYPE_PNG);
XSSFCreationHelper helper = wb.getCreationHelper();
XSSFClientAnchor clientAnchor = helper.createClientAnchor();
clientAnchor.setRow1(row1); // 图片左上角单元格的行
clientAnchor.setCol1(col1); // 图片左上角单元格的列
Drawing drawingPatriarch = sheet.createDrawingPatriarch();
Picture picture1 = drawingPatriarch.createPicture(clientAnchor, picture);
// v 图片占Excel的列数
// v1 图片占Excel的行数
picture1.resize(v,v1);
inputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}