一、导入需要的maven jar包
<!-- excel转pdf -->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.5.13.2</version>
</dependency>
<!-- 操作excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<!-- hutool工具类 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.7</version>
</dependency>
二、操作excel数据以及excel转pdf的测试类以及方法
package com.pj.utils;
import com.itextpdf.text.Document;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.RectangleReadOnly;
import com.itextpdf.text.pdf.PdfWriter;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class getExcelData {
public static void main(String[] args) {
getExcelData getExcelData = new getExcelData();
getExcelData.OperateExcelData();
}
/**
* 操作EXCEL数据并生成新的EXCEL导出
*/
public void OperateExcelData (){
try {
Workbook workbook = new XSSFWorkbook(new FileInputStream("templates/Sales Orders.xlsx"));
Sheet sheet = workbook.getSheetAt(0);
// 动态插入数据-增加行
List<Map<String, Object>> datas = new ArrayList<>();
for (int i = 1; i <= 5; i++) {
Map data = new HashMap<>();
data.put("No", i);
data.put("Model", "Model" + i);
data.put("Serial Number", "Serial Number" + i);
data.put("Description", "Description" + i);
data.put("MOY", "MOY" + i);
data.put("QTY", "QTY" + i);
data.put("Unit Price", "Unit Price" + i);
data.put("Amount", "Amount" + i);
datas.add(data);
}
// 第1个参数是指要开始插入的行，第2个参数是结*行尾**数,第三个参数表示动态添加的行数
// sheet.getLastRowNum() 可以自动的根据新增行数代码进行后移，maven poi jar包版本可能会影响，本项目版本为4.1.1
// 如果第二个参数结*行尾**数填的行数大小小于内容行数，位新增行后面的内容可能会被覆盖
sheet.shiftRows(15, sheet.getLastRowNum() , datas.size() -3, true, false);
//设置参数样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
for (int i = 0; i < datas.size(); i++) {
Row creRow = sheet.createRow(14 + i);
Cell cell0 = creRow.createCell(0);
Cell cell1 = creRow.createCell(1);
Cell cell2 = creRow.createCell(2);
Cell cell3 = creRow.createCell(3);
Cell cell4 = creRow.createCell(4);
Cell cell5 = creRow.createCell(5);
Cell cell6 = creRow.createCell(6);
Cell cell7 = creRow.createCell(7);
cell0.setCellValue(datas.get(i).get("No").toString());
cell1.setCellValue(datas.get(i).get("Model").toString());
cell2.setCellValue(datas.get(i).get("Serial Number").toString());
cell3.setCellValue(datas.get(i).get("Description").toString());
cell4.setCellValue(datas.get(i).get("MOY").toString());
cell5.setCellValue(datas.get(i).get("QTY").toString());
cell6.setCellValue(datas.get(i).get("Unit Price").toString());
cell7.setCellValue(datas.get(i).get("Amount").toString());
cell0.setCellStyle(cellStyle);
cell1.setCellStyle(cellStyle);
cell2.setCellStyle(cellStyle);
cell3.setCellStyle(cellStyle);
cell4.setCellStyle(cellStyle);
cell5.setCellStyle(cellStyle);
cell6.setCellStyle(cellStyle);
cell7.setCellStyle(cellStyle);
}
// 遍历行和列，查找并替换占位符
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == CellType.STRING) {
String cellValue = cell.getStringCellValue();
if (cellValue.contains("${placeholder}")) {
cellValue = cellValue.replace("${placeholder}", "测试编号");
cell.setCellValue(cellValue);
}
}
}
}
// 将修改后的数据写入新的xlsx文件
try (FileOutputStream fileOut = new FileOutputStream("templates/Sales Orders Test.xlsx")) {
workbook.write(fileOut);
}
//加载转换方法
excelToPdf();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public void excelToPdf(){
// 1.获取excel文件
XSSFWorkbook temp = null;
Document document =null;
ByteArrayOutputStream stream = null;
byte[] pdfByte = null;
try {
temp = new XSSFWorkbook("templates/Sales Orders Test.xlsx");
// 2.新建一个stream,用于绑定document
stream = new ByteArrayOutputStream();
// 3.A4大小
RectangleReadOnly shuban = new RectangleReadOnly(PageSize.A4);
document = new Document(shuban);
// 4.获取一个pdfwriter实例
PdfWriter writer = PdfWriter.getInstance(document, stream);
// 5.打开document
document.open();
// 6.设置页边距
document.setMargins(20, 20, 30, 30);
// 7.新增页
document.newPage();
// 8.excel转pdf，并将处理后的内容添加到document中
document.add(ExcelToPdfUtil.excelToPdf(temp, "templates/Sales Orders Test1.xlsx", PageSize.A4.getWidth() - 150, 0));
} catch (Exception e) {
throw new RuntimeException(e);
}
// 9.关闭workbook document
try {
temp.close();
document.close();
// 10.以byte[]方式获取pdf
pdfByte = stream.toByteArray();
stream.flush();
stream.reset();
stream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
// 11.保存pdf
String fileName = "pdfdemo.pdf";
String filePath = "templates/";
(new File(filePath)).mkdirs();
File file = new File(filePath + fileName);
if (file.exists()) {
file.delete();
}
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(filePath + fileName);
outputStream.write(pdfByte);
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
try {
outputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
}
FontGenerater 字体工具类 FONT_URL这个根据自己电脑安装字体所在的盘符填写
package com.pj.utils;
import com.itextpdf.text.pdf.BaseFont;
import java.util.HashMap;
import java.util.Map;
/**
* 读取字体
*/
public class FontGenerater {
// 符号
public final static String EMPTY = "";
public final static String FXG = "\\";
public final static String DIAN = ".";
//for report
public final static String HG = "-";
public final static String REPORT_UNDERLINE = "<r_u>";
public final static String REPORT_UNDERLINE_SUFFIX = "</r_u>";
public final static String NORMAL_CHAR_SET = "UTF-8";
// font
public static final short FONT_SMALL_SIZE = 8;
public static final int FONT_SMALL_VALUELENGTH = 12;
public static String FONT_URL = EMPTY;
public static Map FONTMAP = null;
public static final String FONT_HWXK_NAME = "华文行楷";
public static final String FONT_HWXK = "/STXINGKA.TTF";
public static final String FONT_FS_NAME = "仿宋";
public static final String FONT_FS_NAME_PINYIN = "FangSong";
public static final String FONT_FS = "\\simfang.ttf";
public static final String FONT_CALIBRI_NAME = "Calibri";
public static final String FONT_CALIBRI = "\\calibri.ttf";
public static final String FONT_CONSOLAS_NAME = "Consolas";
public static final String FONT_CONSOLAS = "\\consola.ttf";
public static final String FONT_DENGXIAN_NAME = "等线";
public static final String FONT_DENGXIAN = "\\Deng.ttf";
public static final String FONT_SONTI_NAME = "宋体";
public static final String FONT_SONTI = "\\simsun.ttc,0";
public static BaseFont getFont(String name) {
if (FONTMAP == null) {
FONT_URL = "C:\\Windows\\Fonts";
FONTMAP = new HashMap<String, BaseFont>() {{
try {
put(FONT_SONTI_NAME, BaseFont.createFont(FONT_URL + FONT_SONTI, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_HWXK_NAME, BaseFont.createFont(FONT_URL + FONT_HWXK, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_FS_NAME, BaseFont.createFont(FONT_URL + FONT_FS, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_FS_NAME_PINYIN, BaseFont.createFont(FONT_URL + FONT_FS, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_CALIBRI_NAME, BaseFont.createFont(FONT_URL + FONT_CALIBRI, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_CONSOLAS_NAME, BaseFont.createFont(FONT_URL + FONT_CONSOLAS, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_DENGXIAN_NAME, BaseFont.createFont(FONT_URL + FONT_DENGXIAN, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
} catch (Exception e) {
}
}};
}
return (BaseFont) FONTMAP.get(name);
}
}
PicturesInfo 图片处理需要字段的实体类
package com.pj.utils;
public class PicturesInfo {
private int minRow;
private int maxRow;
private int minCol;
private int maxCol;
private String ext;
private byte[] pictureData;
public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, byte[] pictureData, String ext) {
this.minRow = minRow;
this.maxRow = maxRow;
this.minCol = minCol;
this.maxCol = maxCol;
this.ext = ext;
this.pictureData = pictureData;
}
public byte[] getPictureData() {
return pictureData;
}
public void setPictureData(byte[] pictureData) {
this.pictureData = pictureData;
}
public int getMinRow() {
return minRow;
}
public void setMinRow(int minRow) {
this.minRow = minRow;
}
public int getMaxRow() {
return maxRow;
}
public void setMaxRow(int maxRow) {
this.maxRow = maxRow;
}
public int getMinCol() {
return minCol;
}
public void setMinCol(int minCol) {
this.minCol = minCol;
}
public int getMaxCol() {
return maxCol;
}
public void setMaxCol(int maxCol) {
this.maxCol = maxCol;
}
public String getExt() {
return ext;
}
public void setExt(String ext) {
this.ext = ext;
}
}
POIExtend 关于poi处理excel图片的一些工具方法
package com.pj.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;
import java.util.ArrayList;
import java.util.List;
public class POIExtend {
public static List<PicturesInfo> getAllPictureInfos(Sheet sheet, boolean onlyInternal) throws Exception {
return getAllPictureInfos(sheet, null, null, null, null, onlyInternal);
}
public static List<PicturesInfo> getAllPictureInfos(Sheet sheet, Integer minRow, Integer maxRow, Integer minCol,
Integer maxCol, boolean onlyInternal) throws Exception {
if (sheet instanceof HSSFSheet) {
return getXLSAllPictureInfos((HSSFSheet) sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
} else if (sheet instanceof XSSFSheet) {
return getXLSXAllPictureInfos((XSSFSheet) sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
} else {
throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
}
}
private static List<PicturesInfo> getXLSAllPictureInfos(HSSFSheet sheet, Integer minRow, Integer maxRow,
Integer minCol, Integer maxCol, Boolean onlyInternal) {
List<PicturesInfo> picturesInfoList = new ArrayList<>();
HSSFShapeContainer shapeContainer = sheet.getDrawingPatriarch();
if (null != shapeContainer) {
List<HSSFShape> shapeList = shapeContainer.getChildren();
for (HSSFShape shape : shapeList) {
if (shape instanceof HSSFPicture && shape.getAnchor() instanceof HSSFClientAnchor) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(),
anchor.getCol1(), anchor.getCol2(), onlyInternal)) {
picturesInfoList.add(
new PicturesInfo(anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(),
picture.getPictureData().getData(), picture.getPictureData().getMimeType()));
}
}
}
}
return picturesInfoList;
}
private static List<PicturesInfo> getXLSXAllPictureInfos(XSSFSheet sheet, Integer minRow, Integer maxRow,
Integer minCol, Integer maxCol, Boolean onlyInternal) {
List<PicturesInfo> picturesInfoList = new ArrayList<>();
List<POIXMLDocumentPart> documentPartList = sheet.getRelations();
for (POIXMLDocumentPart documentPart : documentPartList) {
if (documentPart instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) documentPart;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
if (shape instanceof XSSFPicture) {
XSSFPicture picture = (XSSFPicture) shape;
// XSSFClientAnchor anchor = picture.getPreferredSize();
XSSFClientAnchor anchor = picture.getClientAnchor();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(),
anchor.getCol1(), anchor.getCol2(), onlyInternal)) {
picturesInfoList.add(new PicturesInfo(anchor.getRow1(), anchor.getRow2(), anchor.getCol1(),
anchor.getCol2(), picture.getPictureData().getData(),
picture.getPictureData().getMimeType()));
}
}
}
}
}
return picturesInfoList;
}
private static boolean isInternalOrIntersect(Integer rangeMinRow, Integer rangeMaxRow, Integer rangeMinCol,
Integer rangeMaxCol, int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol,
Boolean onlyInternal) {
int _rangeMinRow = rangeMinRow == null ? pictureMinRow : rangeMinRow;
int _rangeMaxRow = rangeMaxRow == null ? pictureMaxRow : rangeMaxRow;
int _rangeMinCol = rangeMinCol == null ? pictureMinCol : rangeMinCol;
int _rangeMaxCol = rangeMaxCol == null ? pictureMaxCol : rangeMaxCol;
if (onlyInternal) {
return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow && _rangeMinCol <= pictureMinCol
&& _rangeMaxCol >= pictureMaxCol);
} else {
return ((Math.abs(_rangeMaxRow - _rangeMinRow) + Math.abs(pictureMaxRow - pictureMinRow) >= Math
.abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow))
&& (Math.abs(_rangeMaxCol - _rangeMinCol) + Math.abs(pictureMaxCol - pictureMinCol) >= Math
.abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
}
}
}
excel转pdf的工具类
package com.pj.utils;
import cn.hutool.core.util.StrUtil;
import com.itextpdf.text.*;
import com.itextpdf.text.Font;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;
public class ExcelToPdfUtil {
public static PdfPTable excelToPdf(Workbook workbook, String filename, float pagewidth, int sheetindex) throws Exception {
Sheet sheet = workbook.getSheetAt(sheetindex);
// BaseFont bfChinese = BaseFont.createFont("C:\\Windows\\Fonts\\msyh.ttc,0", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
// Font font =new Font(bfChinese,50f,Font.BOLD,BaseColor.BLACK);
float[] widths = getColWidth(sheet);
PdfPTable table = new PdfPTable(widths);
table.setWidthPercentage(90);
table.setLockedWidth(true);
table.setTotalWidth(pagewidth);
int colCount = widths.length;
for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = 0; c < colCount; c++) {
Cell excelCell = row.getCell(c);
if (excelCell == null) {
excelCell = row.createCell(c);
}
String value = "";
if (excelCell != null) {
excelCell.setCellType(CellType.STRING);
value = excelCell.getStringCellValue();
//value = excelCell.getStringCellValue() == null ? "" : excelCell.getStringCellValue();
}
org.apache.poi.ss.usermodel.Font excelFont = getExcelFont(workbook, excelCell, filename);
short fontsize = excelFont.getFontHeightInPoints();
if (!StrUtil.isEmpty(value) && value.length() > FontGenerater.FONT_SMALL_VALUELENGTH && value.split(FontGenerater.HG).length >= 3 && r <= 1) {
fontsize = FontGenerater.FONT_SMALL_SIZE;
}
Font pdFont = getFont(excelFont.getFontName(), fontsize, excelFont.getBold());
PdfPCell pCell = null;
if (value.indexOf(FontGenerater.REPORT_UNDERLINE) >= 0) {
pCell = new PdfPCell(new Phrase(FontGenerater.EMPTY, pdFont));
Paragraph para = new Paragraph();
String[] values = value.split(FontGenerater.REPORT_UNDERLINE_SUFFIX);
for (String v : values) {
if (v.indexOf(FontGenerater.REPORT_UNDERLINE) >= 0) {
v = v.replace(FontGenerater.REPORT_UNDERLINE, FontGenerater.EMPTY);
Chunk dateUnderline = new Chunk(v);
dateUnderline.setUnderline(0.1f, -2f);
para.add(dateUnderline);
} else {
para.add(new Chunk(v));
}
}
pCell.getPhrase().add(para);
} else {
pCell = new PdfPCell(new Phrase(value, pdFont));
}
List<PicturesInfo> infos = POIExtend.getAllPictureInfos(sheet, r, r, c, c, false);
if (!infos.isEmpty()) {
PicturesInfo info = infos.get(0);
Image img = Image.getInstance(infos.get(0).getPictureData());
img.scaleToFit(120, 120);
pCell = new PdfPCell(img);
pCell.setUseAscender(true);
pCell.setHorizontalAlignment(Element.ALIGN_CENTER);
pCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
}
pCell.setBorder(1);
pCell.setHorizontalAlignment(getHorAglin(excelCell.getCellStyle().getAlignment().getCode()));
pCell.setVerticalAlignment(getVerAglin(excelCell.getCellStyle().getVerticalAlignment().getCode()));
//String FontChPath = PropertyUitls.getProperties("config.properties").getProperty("fontSourceSong");
//Font font =new Font(bfChinese,20f,Font.BOLD,BaseColor.BLACK);
pCell.setMinimumHeight(row.getHeightInPoints());
if (isMergedRegion(sheet, r, c)) {
int[] line = getMerged(sheet, r, c);
if (r == line[0]) {
pCell.setBorderWidthLeft(excelCell.getCellStyle().getBorderLeft().getCode());
pCell.setBorderWidthTop(excelCell.getCellStyle().getBorderTop().getCode());
}
if (line[1] == sheet.getPhysicalNumberOfRows() - 1) {
pCell.setBorderWidthBottom(sheet.getRow(line[1]).getCell(line[2]).getCellStyle().getBorderBottom().getCode());
}
int[] span = getMergedSpan(sheet, r, c);
if (span[0] == 1 && span[1] == 1) {//忽略合并过的单元格
continue;
}
pCell.setRowspan(span[0]);
pCell.setColspan(span[1]);
c = c + span[1] - 1;//合并过的列直接跳过
} else {
pCell.setBorderWidthTop(excelCell.getCellStyle().getBorderTop().getCode());
pCell.setBorderWidthLeft(excelCell.getCellStyle().getBorderLeft().getCode());
}
if (r == sheet.getPhysicalNumberOfRows() - 1) {
pCell.setBorderWidthBottom(excelCell.getCellStyle().getBorderBottom().getCode());
}
if (c == row.getPhysicalNumberOfCells() - 1) {
pCell.setBorderWidthRight(excelCell.getCellStyle().getBorderRight().getCode());
}
table.addCell(pCell);
}
} else {
PdfPCell pCell = new PdfPCell(new Phrase(FontGenerater.EMPTY));
pCell.setBorder(0);
pCell.setMinimumHeight(13);
table.addCell(pCell);
}
}
return table;
}
//获取字体
private static org.apache.poi.ss.usermodel.Font getExcelFont(Workbook workbook, Cell cell, String excelName) {
if (excelName.endsWith(".xls")) {
return ((HSSFCell) cell).getCellStyle().getFont(workbook);
}
return ((XSSFCell) cell).getCellStyle().getFont();
}
/**
* 判断excel单元格是否有边框
*
* @param excelCell
* @return
*/
private static boolean hasBorder(Cell excelCell) {
short top = excelCell.getCellStyle().getBorderTop().getCode();
short bottom = excelCell.getCellStyle().getBorderBottom().getCode();
short left = excelCell.getCellStyle().getBorderLeft().getCode();
short right = excelCell.getCellStyle().getBorderRight().getCode();
return top + bottom + left + right > 2;
}
private static void setBorder(Cell excelCell, PdfPCell pCell) {
pCell.setBorderWidthTop(excelCell.getCellStyle().getBorderTop().getCode());
pCell.setBorderWidthBottom(excelCell.getCellStyle().getBorderBottom().getCode());
pCell.setBorderWidthLeft(excelCell.getCellStyle().getBorderLeft().getCode());
pCell.setBorderWidthRight(excelCell.getCellStyle().getBorderRight().getCode());
}
/**
* 获取excel单元格数据显示格式
*
* @param dataFormat
* @return
* @throws Exception
*/
private static String getNumStyle(String dataFormat) throws Exception {
if (dataFormat == null || dataFormat.length() == 0) {
throw new Exception("");
}
if (dataFormat.indexOf("%") > -1) {
return dataFormat;
} else {
return dataFormat.substring(0, dataFormat.length() - 2);
}
}
/**
* 判断单元格是否是合并单元格
*
* @param sheet
* @param row
* @param column
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
private static int[] getMerged(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
int[] span = {0, 0, 0, 0};
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
span[0] = firstRow;
span[1] = lastRow;
span[2] = firstColumn;
span[3] = lastColumn;
break;
}
}
}
return span;
}
/**
* 计算合并单元格合并的跨行跨列数
*
* @param sheet
* @param row
* @param column
* @return
*/
private static int[] getMergedSpan(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
int[] span = {1, 1};
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (firstColumn == column && firstRow == row) {
span[0] = lastRow - firstRow + 1;
span[1] = lastColumn - firstColumn + 1;
break;
}
}
return span;
}
/**
* 获取excel中每列宽度的占比
*
* @param sheet
* @return
*/
private static float[] getColWidth(Sheet sheet) {
int rowNum = getMaxColRowNum(sheet);
Row row = sheet.getRow(rowNum);
int cellCount = row.getPhysicalNumberOfCells();
int[] colWidths = new int[cellCount];
int sum = 0;
for (int i = 0; i < cellCount; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
colWidths[i] = sheet.getColumnWidth(i);
sum += sheet.getColumnWidth(i);
}
}
float[] colWidthPer = new float[cellCount];
for (int i = 0; i < cellCount; i++) {
colWidthPer[i] = (float) colWidths[i] / sum * 100;
}
return colWidthPer;
}
/**
* 获取excel中列数最多的行号
*
* @param sheet
* @return
*/
private static int getMaxColRowNum(Sheet sheet) {
int rowNum = 0;
int maxCol = 0;
for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
Row row = sheet.getRow(r);
if (row != null && maxCol < row.getPhysicalNumberOfCells()) {
maxCol = row.getPhysicalNumberOfCells();
rowNum = r;
}
}
return rowNum;
}
/**
* excel垂直对齐方式映射到pdf对齐方式
*
* @param aglin
* @return
*/
private static int getVerAglin(int aglin) {
switch (aglin) {
case 1:
return Element.ALIGN_MIDDLE;
case 2:
return Element.ALIGN_BOTTOM;
case 0:
return Element.ALIGN_TOP;
default:
return Element.ALIGN_MIDDLE;
}
}
/**
* excel水平对齐方式映射到pdf水平对齐方式
*
* @param aglin
* @return
*/
private static int getHorAglin(int aglin) {
switch (aglin) {
case 2:
return Element.ALIGN_CENTER;
case 3:
return Element.ALIGN_RIGHT;
case 1:
return Element.ALIGN_LEFT;
default:
return Element.ALIGN_CENTER;
}
}
/**
* 格式化数字
*
* @param pattern
* @param num
* @return
*/
private static String numFormat(String pattern, double num) {
DecimalFormat format = new DecimalFormat(pattern);
return format.format(num);
}
private static int[] getImgPostion(String imgKey) {
String[] arr = imgKey.split("_");
int[] position = new int[arr.length];
for (int i = 0; i < arr.length; i++) {
position[i] = Integer.parseInt(arr[i]);
}
return position;
}
public static Map<String, HSSFPictureData> getPictrues(HSSFWorkbook wb) {
Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
// getAllPictures方法只能获取不同的图片，如果Excel中存在相同的图片，只能得到一张图片
List<HSSFPictureData> pics = wb.getAllPictures();
if (pics.size() == 0) {
return map;
}
for (Integer sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
if (patriarch == null) {
continue;
}
for (HSSFShape shape : patriarch.getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
HSSFPicture pic = (HSSFPicture) shape;
int picIndex = pic.getPictureIndex() - 1;
HSSFPictureData picData = pics.get(picIndex);
// 键格式：sheet索引_行号_列号_单元格内的上边距_单元格内的左边距_uuid
String key = sheetIndex + "_" + anchor.getRow1() + "_" + anchor.getCol1() + "_" + anchor.getRow2() + "_" + anchor.getCol2();
key += "_" + anchor.getDx1() + "_" + anchor.getDy1() + "_" + anchor.getDx2() + "_" + anchor.getDy2();
key += "_" + UUID.randomUUID();
map.put(key, picData);
}
}
}
return map;
}
public static Map<String, PictureData> getPictrues(Workbook wb, int type) {
Map<String, PictureData> map = new HashMap<String, PictureData>();
// getAllPictures方法只能获取不同的图片，如果Excel中存在相同的图片，只能得到一张图片
List<? extends PictureData> pics = wb.getAllPictures();
if (pics.size() == 0) {
return map;
}
for (Integer sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Drawing<?> patriarch = sheet.getDrawingPatriarch();
if (patriarch == null) {
continue;
}
if (type == 2) {
HSSFPatriarch hssfpatriarch = (HSSFPatriarch) patriarch;
for (HSSFShape shape : hssfpatriarch.getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
HSSFPicture pic = (HSSFPicture) shape;
int picIndex = pic.getPictureIndex() - 1;
HSSFPictureData picData = (HSSFPictureData) pics.get(picIndex);
// 键格式：sheet索引_行号_列号_单元格内的上边距_单元格内的左边距_uuid
String key = sheetIndex + "_" + anchor.getRow1() + "_" + anchor.getCol1() + "_" + anchor.getRow2() + "_" + anchor.getCol2();
key += "_" + anchor.getDx1() + "_" + anchor.getDy1() + "_" + anchor.getDx2() + "_" + anchor.getDy2();
key += "_" + UUID.randomUUID();
map.put(key, picData);
}
}
} else if (type == 1) {
XSSFSheet xssfsheet = (XSSFSheet) sheet;
for (POIXMLDocumentPart dr : xssfsheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFPictureData data = pic.getPictureData();
XSSFClientAnchor aaa = pic.getClientAnchor();
CTMarker ccc = aaa.getFrom();
int ddd = ccc.getRow();
int eee = ccc.getCol();
// XSSFClientAnchor anchor = pic.getPreferredSize();
// CTMarker ctMarker = anchor.getFrom();
// String picIndex = String.valueOf(0) + "_" + ctMarker.getRow() + "_" + ctMarker.getCol();
String picIndex = String.valueOf(0) + "_" + ddd + "_" + eee;
map.put(picIndex, pic.getPictureData());
}
}
}
}
}
return map;
}
/**
* 单元格是否是图片的起始位置
*
* @return 单元格是否是图片的起始位置
*/
public static BaseColor parseBackgroundColor(Color hssfColor) {
if (hssfColor == null) {
// 白色
return new BaseColor(255, 255, 255);
}
short[] rgb = new short[]{255, 255, 255};
if (rgb[0] == 0 && rgb[1] == 0 && rgb[2] == 0) {
rgb = new short[]{255, 255, 255};
}
return new BaseColor(rgb[0], rgb[1], rgb[2]);
}
public static PdfPCell parseImgPCell(Cell cell) {
PdfPCell pdfpCell = new PdfPCell();
CellStyle cellStyle = cell.getCellStyle();
pdfpCell.setUseAscender(true);
// 水平对齐方式
int halign_itext = cellStyle.getAlignment().getCode();
//int halign_itext = parseHorizontalAlignmen(halign);
pdfpCell.setHorizontalAlignment(halign_itext);
// 垂直对齐方式
int valign_itext = cellStyle.getVerticalAlignment().getCode();
// int valign_itext = parseVerticalAlignment(valign);
pdfpCell.setVerticalAlignment(valign_itext);
// 填充色（背景色）
// HSSFColor backgroundColor = cellStyle.getFillBackgroundColorColor();
Color backgroundColor = cellStyle.getFillForegroundColorColor();
BaseColor backgroundColor_itext = parseBackgroundColor(backgroundColor);
pdfpCell.setBackgroundColor(backgroundColor_itext);
// 自动换行
boolean noWrap = !cellStyle.getWrapText();
pdfpCell.setNoWrap(noWrap);
// 边框样式
// 下边框
float borderWidth = cellStyle.getBorderBottom().getCode() / 32.00f;
//float borderWidth = borderStyle ;
pdfpCell.setBorderWidthBottom(borderWidth);
// 上框线
// borderStyle = cellStyle.getBorderTop();
pdfpCell.setBorderWidthTop(cellStyle.getBorderTop().getCode() / 32.00f);
// 左框线
// borderStyle = cellStyle.getBorderLeft();
pdfpCell.setBorderWidthLeft(cellStyle.getBorderLeft().getCode() / 32.00f);
// 右框线
//borderStyle = cellStyle.getBorderRight();
pdfpCell.setBorderWidthRight(cellStyle.getBorderRight().getCode() / 32.00f);
pdfpCell.normalize();
// pdfpCell.disableBorderSide(9);
return pdfpCell;
}
public static BaseColor parseColor(HSSFColor hssfColor) {
if (hssfColor == null) {
return new BaseColor(255, 255, 255);
}
short[] rgb = hssfColor.getTriplet();
return new BaseColor(rgb[0], rgb[1], rgb[2]);
}
public static Font getFont(String fontname, int heigth, boolean bold) throws Exception {
BaseFont font = FontGenerater.getFont(fontname);
if (font == null) {
font = (BaseFont) FontGenerater.FONTMAP.get(FontGenerater.FONT_SONTI_NAME);
}
return new Font(font, heigth, bold ? Font.BOLD : Font.NORMAL, BaseColor.BLACK);
}
//判断数据的类型
public static String getExcelCellValue(Cell excelCell) {
if (excelCell == null) {
return "";
}
// 判断数据的类型
CellType cellType = excelCell.getCellType();
if (cellType == CellType.STRING) {
return excelCell.getStringCellValue();
}
if (cellType == CellType.BOOLEAN) {
return String.valueOf(excelCell.getBooleanCellValue());
}
if (cellType == CellType.FORMULA) {
return excelCell.getCellFormula();
}
if (cellType == CellType.NUMERIC) {
//short s = excelCell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(excelCell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf;
// 验证short值
if (excelCell.getCellStyle().getDataFormat() == 14) {
sdf = new SimpleDateFormat("yyyy/MM/dd");
} else if (excelCell.getCellStyle().getDataFormat() == 21) {
sdf = new SimpleDateFormat("HH:mm:ss");
} else if (excelCell.getCellStyle().getDataFormat() == 22) {
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
} else {
throw new RuntimeException("日期格式错误!!!");
}
Date date = excelCell.getDateCellValue();
return sdf.format(date);
} else if (excelCell.getCellStyle().getDataFormat() == 0) {
//处理数值格式
DataFormatter formatter = new DataFormatter();
return formatter.formatCellValue(excelCell);
}
}
if (cellType == CellType.ERROR) {
return "非法字符";
}
return "";
}
}