JAVA最新版本poi 4.0.0 实现Excel导入、
Excel导出、PDF导出
本文章写的Excel导入、Excel导出、PDF导出,
写的工具类都是适用于很多都能用的，但是我封装了数据,不过只是封装了一层而已，很好解析出来先导包
<!-- 最新版 4.0.0 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<!-- 解析PDF用的 -->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.5.1</version>
</dependency>1.Excel 导入
先建立相关要的实体类
column 实体类 ：相当于页面上或者Excel一行的一列的数据
// 每列对象
public class Column {
// 列名
private String name;
// 列值
private Object value;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
}Line实体类 ：相当于页面上或者Excel一行的数据
// 一行
public class Line {
// 一行每列集合
private List<Column> columns;
public List<Column> getColumns() {
return columns;
}
public void setColumns(List<Column> columns) {
this.columns = columns;
}
}导入工具类
/**
* 导入工具类
* file Excel :上传过来的文件
* columnNames :为Excel 实体类属性集合一一对应
* 比如我的实体类有 id,name,age,address
* 那么这个集合就是List<String> list =new ArrayList<>(){id,name,age,address}
*/
public static Map<String, Object> uploadExcel(MultipartFile file, List<String> columnNames) {
List<Line> lines = new LinkedList<>();
Map<String, Object> resultMap = new HashMap<String, Object>();
int state = 1;
String msg = "读取成功！";
if (file.isEmpty()) {
state = 0;
msg = "文件为空！";
}
try {
//根据路径获取这个操作excel的实例
Workbook wb = WorkbookFactory.create(file.getInputStream());
//根据页面index 获取sheet页
Sheet sheet = wb.getSheetAt(0);
Row row = null;
//循环sheet页中数据从第二行开始，第一行是标题
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
Line line = new Line();
List<Column> columns = new ArrayList<Column>();
row = sheet.getRow(i);
for (int j = 0; j < columnNames.size(); j++) {
Column clbo = new Column();
clbo.setName(columnNames.get(j));
if (row.getCell(j) != null) {
CellType cellType = row.getCell(j).getCellType();
String value = null;
if (CellType.NUMERIC.equals(cellType)) {
if ("General".equals(row.getCell(j).getCellStyle().getDataFormatString())) {
value = String.valueOf(row.getCell(j).getNumericCellValue());
} else if ("m/d/yy".equals(row.getCell(j).getCellStyle().getDataFormatString())) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
value = sdf.format(row.getCell(j).getDateCellValue());
} else {
value = String.valueOf(row.getCell(j).getNumericCellValue());
}
clbo.setValue(value);
} else {
row.getCell(j).setCellType(CellType.STRING);//设置成String
clbo.setValue(row.getCell(j).getStringCellValue());
}
} else {
clbo.setValue("");
}
columns.add(clbo);
}
line.setColumns(columns);
lines.add(line);
}
} catch (Exception e) {
//log.error(e.getMessage());
e.printStackTrace();
state = 0;
msg = "读取失败，请稍后再试！";
}
resultMap.put("state", state);
resultMap.put("msg", msg);
resultMap.put("lines", lines);
return resultMap;
}2.Excel 导出
/**
* 导出工具类
* @param response
* @param lines 跟导入进来,返回出来的对象一致,行集合
* @param titles 为Excel 实体类属性集合一一对应的意思
* 比如我的实体类有 id,name,age,address
* 那么这个集合就是List<String> list =new ArrayList<>(){"id","名称","年龄","地址"}
* @param title 为标题
* @throws Exception
*/
public static void excelExport(HttpServletResponse response,
List<Line> lines, List<String> titles, String title) throws Exception {
response.setHeader("content-Type", "application/vnd.ms-excel");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(title, "utf-8"));
exportExcel(titles, lines, response, title);
}
public static void exportExcel(List<String> titles, List<Line> lines,
HttpServletResponse response, String title) {
// 创建Excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表sheet 默认是表名是sheet0
HSSFSheet sheet = workbook.createSheet();
// 创建表的第一行
HSSFRow row = sheet.createRow(0);
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeightInPoints(20);
// 创建第0行 也就是标题
row.setHeightInPoints(30);// 设备标题的高度
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.size()));
// 创建单元格
HSSFCell cell = null;
cell = row.createCell(0);
cell.setCellStyle(titleStyle(workbook));
cell.setCellValue(title);
HSSFRow row1 = sheet.createRow(1);
// 循环为第一行插入标题
for (int i = 0; i < titles.size(); i++) {
cell = row1.createCell(i);
cell.setCellStyle(style(workbook));
cell.setCellValue(titles.get(i));
}
// 追加数据 1是第二行
for (int i = 2; i <= lines.size(); i++) {
Line line = lines.get(i - 1);
List<Column> columns = line.getColumns();
HSSFRow nextrow = sheet.createRow(i);
for (int j = 0; j < columns.size(); j++) {
HSSFCell cell2 = nextrow.createCell(j);
cell2.setCellStyle(style1(workbook));
if (columns.get(j).getValue() == null) {
cell2.setCellValue("");
} else {
cell2.setCellValue(String.valueOf(columns.get(j).getValue()));
}
}
}
ServletOutputStream out;
try {
//.xls 是2003版本，excel2003、2007、2010都可以打开，兼容性最好
response.setHeader("Content-Disposition", "attachment;fileName=" + title + ".xls");
response.setContentType("APPLICATION/OCTET-STREAM");
out = response.getOutputStream();
workbook.write(out);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// 标题第一行样式
public static HSSFCellStyle titleStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBottomBorderColor((short) 8);
style.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 15); // 设置字体大小
style.setFont(headerFont); // 为标题样式设置字体样式
return style;
}
// 第二行样式
public static HSSFCellStyle style(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBottomBorderColor((short) 8);
style.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
style.setFont(headerFont); // 为标题样式设置字体样式
return style;
}
// 数据行样式
public static HSSFCellStyle style1(HSSFWorkbook wb) {
HSSFCellStyle style1 = wb.createCellStyle();
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HorizontalAlignment.CENTER); //HSSFCellStyle.ALIGN_CENTER
style1.setVerticalAlignment(VerticalAlignment.CENTER); // HSSFCellStyle.VERTICAL_CENTER // 创建一个居中格式
style1.setBottomBorderColor((short) 8);
style1.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
style1.setBorderLeft(BorderStyle.THIN);
style1.setBorderRight(BorderStyle.THIN);
style1.setBorderTop(BorderStyle.THIN);
HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
style1.setFont(headerFont1); // 为标题样式设置字体样式
return style1;3.PDF 导出
先将Excel转换成HSSFWorkbook对象
/**
* Excel 转HSSFWorkbook 转解析Excel对象
* @param lines 跟导入进来,返回出来的对象一致,行集合
* @param titles 为Excel 实体类属性集合一一对应的意思
* 比如我的实体类有 id,name,age,address
* 那么这个集合就是List<String> list =new ArrayList<>(){"id","名称","年龄","地址"}
* @param title 为标题
* @return
*/
public static HSSFWorkbook excelToHSSFWorkbook(List<String> titles, List<Line> lines,String title) {
// 创建Excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表sheet 默认是表名是sheet0
HSSFSheet sheet = workbook.createSheet();
// 创建表的第一行
HSSFRow row = sheet.createRow(0);
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeightInPoints(20);
// 创建第0行 也就是标题
row.setHeightInPoints(30);// 设备标题的高度
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.size()));
// 创建单元格
HSSFCell cell = null;
cell = row.createCell(0);
cell.setCellStyle(titleStyle(workbook));
cell.setCellValue(title);
HSSFRow row1 = sheet.createRow(1);
// 循环为第一行插入标题
for (int i = 0; i < titles.size(); i++) {
cell = row1.createCell(i);
cell.setCellStyle(style(workbook));
cell.setCellValue(titles.get(i));
}
// 追加数据 1是第二行
for (int i = 2; i <= lines.size(); i++) {
Line line = lines.get(i - 1);
List<Column> columns = line.getColumns();
HSSFRow nextrow = sheet.createRow(i);
for (int j = 0; j < columns.size(); j++) {
HSSFCell cell2 = nextrow.createCell(j);
cell2.setCellStyle(style1(workbook));
if (columns.get(j).getValue() == null) {
cell2.setCellValue("");
} else {
cell2.setCellValue(String.valueOf(columns.get(j).getValue()));
}
}
}
return workbook;
}
// 标题第一行样式
public static HSSFCellStyle titleStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBottomBorderColor((short) 8);
style.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 15); // 设置字体大小
style.setFont(headerFont); // 为标题样式设置字体样式
return style;
}
// 第二行样式
public static HSSFCellStyle style(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBottomBorderColor((short) 8);
style.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
style.setFont(headerFont); // 为标题样式设置字体样式
return style;
}
// 数据行样式
public static HSSFCellStyle style1(HSSFWorkbook wb) {
HSSFCellStyle style1 = wb.createCellStyle();
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HorizontalAlignment.CENTER); //HSSFCellStyle.ALIGN_CENTER
style1.setVerticalAlignment(VerticalAlignment.CENTER); // HSSFCellStyle.VERTICAL_CENTER // 创建一个居中格式
style1.setBottomBorderColor((short) 8);
style1.setBorderBottom(BorderStyle.THIN); // HSSFCellStyle.BORDER_THIN
style1.setBorderLeft(BorderStyle.THIN);
style1.setBorderRight(BorderStyle.THIN);
style1.setBorderTop(BorderStyle.THIN);
HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 10); // 设置字体大小
style1.setFont(headerFont1); // 为标题样式设置字体样式
return style1;
}开始PDF导出
/**
* 导出PDF文件(Excel转PDF)工具类
* @param response
* @param lines 跟导入进来,返回出来的对象一致,行集合
* @param titles 为Excel 实体类属性集合一一对应的意思
* 比如我的实体类有 id,name,age,address
* 那么这个集合就是List<String> list =new ArrayList<>(){"id","名称","年龄","地址"}
* @param pdfName 为标题
* @throws Exception
*/
public void PdfExport(HttpServletResponse response, List<String> titles, List<Line> lines, String pdfName) throws Exception {
HSSFWorkbook workBook = ExcelUtil.excelToHSSFWorkbook(titles,lines,pdfName);
response.setHeader("content-Type", "application/pdf");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "inline;filename=" + URLEncoder.encode(pdfName, "utf-8")+".pdf");
excelToPDF(response,workBook,pdfName);
}public static void excelToPDF(HttpServletResponse response, HSSFWorkbook workBook, String pdfName) throws Exception {
// 也可以改成直接读取Excel文件目录就可以直接Excel转PDF
// InputStream inputStream = new FileInputStream(Excel文件绝对路径);
// HSSFWorkbook workBook = new HSSFWorkbook(inputStream);
HSSFSheet sheet = workBook.getSheetAt(0);
ByteArrayOutputStream stream = new ByteArrayOutputStream();
Document document = new Document(PageSize.A4);//设置pdf纸张大小
PdfWriter writer = PdfWriter.getInstance(document, stream);
document.setMargins(0, 0, 15, 15);//设置页边距
document.open();
float[] widths = getColWidth(sheet);//获取excel每列宽度占比
PdfPTable table = new PdfPTable(widths);//初始化pdf中每列的宽度
table.setWidthPercentage(88);
int colCount = widths.length;
BaseFont baseFont = BaseFont.createFont("C:\\Windows\\Fonts\\simsun.ttf", BaseFont.IDENTITY_H,
BaseFont.NOT_EMBEDDED);//设置基本字体
//开始遍历excel内容并绘制pdf
for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) {
HSSFRow row = sheet.getRow(r);
if (row != null) {
for (int c = row.getFirstCellNum(); (c < row.getLastCellNum() || c < colCount) && c > -1; c++) {
if (c >= row.getPhysicalNumberOfCells()) {
PdfPCell pCell = new PdfPCell(new Phrase(""));
pCell.setBorder(0);
table.addCell(pCell);
continue;
}
HSSFCell excelCell = row.getCell(c);
String value = "";
if (excelCell != null) {
value = excelCell.toString().trim();
if (value != null && value.length() != 0) {
String dataFormat = excelCell.getCellStyle().getDataFormatString();//获取excel单元格数据显示样式
if (dataFormat != "General" && dataFormat != "@") {
try {
String numStyle = getNumStyle(dataFormat);
value = numFormat(numStyle, excelCell.getNumericCellValue());
} catch (Exception e) {
}
}
}
}
HSSFFont excelFont = excelCell.getCellStyle().getFont(workBook);
// Font.BOLD : 正体大 NORMAL 正体小 ITALIC :斜体小 BOLDITALIC :合并BOLD和ITALIC
Font pdFont = new Font(baseFont, excelFont.getFontHeightInPoints(),
excelFont.getBold() == true ? Font.BOLD : Font.NORMAL, BaseColor.BLACK);//设置单元格字体
PdfPCell pCell = new PdfPCell(new Phrase(value, pdFont));
boolean hasBorder = hasBorder(excelCell);
if (!hasBorder) {
pCell.setBorder(0);
}
pCell.setHorizontalAlignment(getHorAglin(excelCell.getCellStyle().getAlignment().getCode()));
pCell.setVerticalAlignment(getVerAglin(excelCell.getCellStyle().getVerticalAlignment().getCode()));
pCell.setMinimumHeight(row.getHeightInPoints());
if (isMergedRegion(sheet, r, c)) {
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;//合并过的列直接跳过
}
table.addCell(pCell);
}
} else {
PdfPCell pCell = new PdfPCell(new Phrase(""));
pCell.setBorder(0);
pCell.setMinimumHeight(13);
table.addCell(pCell);
}
}
document.add(table);
document.close();
byte[] pdfByte = stream.toByteArray();
stream.close();
ServletOutputStream out;
try {
response.setHeader("Content-Disposition", "inline;fileName=" + pdfName + ".pdf");
response.setContentType("application/pdf");
out = response.getOutputStream();
out.write(pdfByte);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* * 获取excel中每列宽度的占比
* * @param sheet
* * @return
*
*/
private static float[] getColWidth(HSSFSheet sheet) {
int rowNum = getMaxColRowNum(sheet);
HSSFRow row = sheet.getRow(rowNum);
int cellCount = row.getPhysicalNumberOfCells();
int[] colWidths = new int[cellCount];
int sum = 0;
for (int i = row.getFirstCellNum(); i < cellCount; i++) {
HSSFCell cell = row.getCell(i);
if (cell != null) {
colWidths[i] = sheet.getColumnWidth(i);
sum += sheet.getColumnWidth(i);
}
}
float[] colWidthPer = new float[cellCount];
for (int i = row.getFirstCellNum(); i < cellCount; i++) {
colWidthPer[i] = (float) colWidths[i] / sum * 100;
}
return colWidthPer;
}
/**
* * 获取excel中列数最多的行号
* * @param sheet
* * @return
*
*/
private static int getMaxColRowNum(HSSFSheet sheet) {
int rowNum = 0;
int maxCol = 0;
for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) {
HSSFRow row = sheet.getRow(r);
if (row != null && maxCol < row.getPhysicalNumberOfCells()) {
maxCol = row.getPhysicalNumberOfCells();
rowNum = r;
}
}
return rowNum;
}
/**
* * 获取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(HSSFSheet 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;
}
/**
* * 计算合并单元格合并的跨行跨列数
* * @param sheet
* * @param row
* * @param column
* * @return
*
*/
private static int[] getMergedSpan(HSSFSheet 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 excelCell
* * @return
*
*/
private static boolean hasBorder(HSSFCell 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;
}
/**
* * 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;
}
}
/**
* * 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 3:
return Element.ALIGN_TOP;
default:
return Element.ALIGN_MIDDLE;
}
}
/**
* * 格式化数字
* * @param pattern
* * @param num
* * @return
*
*/
private static String numFormat(String pattern, double num) {
DecimalFormat format = new DecimalFormat(pattern);
return format.format(num);
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
