Java使用POI创建excel,并加入条形码转PDF面单,发票。
- 欢迎使用Markdown编辑器
- 新的改变
- 功能快捷键
- 合理的创建标题,有助于目录的生成
- 如何改变文本的样式
- 插入链接与图片
- 如何插入一段漂亮的代码片
- 生成一个适合你的列表
- 创建一个表格
- 设定内容居中、居左、居右
- SmartyPants
- 创建一个自定义列表
- 如何创建一个注脚
- 注释也是必不可少的
- KaTeX数学公式
- 新的甘特图功能,丰富你的文章
- UML 图表
- FLowchart流程图
- 导出与导入
- 导出
- 导入
创建excel,设置sheet的名称,多页的话以索引0开始。
HSSFCellStyle style = workbook.createCellStyle();
创建样式,可设置背景色,excel边框,合并单元格,字体旋转。
此篇文章未用背景色。
更多可参考:https://poi.apache.org/ 文档
先上图
public class WaybillExcelToPDFUtils {
private static void excelLink() {
//1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建工作表
HSSFSheet sheet = workbook.createSheet("text");
// 样式
HSSFCellStyle excelBorrFont = excelBorrFont(workbook);
HSSFCellStyle hssfCellStyles = excelBorr(workbook);
// 左对齐样式
HSSFCellStyle leftCellStyle = leftexcelBors(workbook);
//1.1创建合并单元格对象
CellRangeAddress callRangeAddres = new CellRangeAddress(0, 4, 0, 15);//起始行,结束行,起始列,结束列
// 第一侧边 第一行
CellRangeAddress callRangeAddres1 = new CellRangeAddress(5, 16, 0, 0);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddres2 = new CellRangeAddress(5, 7, 1, 4);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddres3 = new CellRangeAddress(5, 7, 5, 11);//起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddres4 = new CellRangeAddress(5, 7, 12, 15);
// 第一侧边 第二行
CellRangeAddress callRangeAddres5 = new CellRangeAddress(8, 12, 1, 5);
CellRangeAddress callRangeAddres6 = new CellRangeAddress(8, 12, 6, 13);
CellRangeAddress callRangeAddres7 = new CellRangeAddress(8, 12, 14, 15);
// 第一侧边 第三行
CellRangeAddress callRangeAddres8 = new CellRangeAddress(13, 16, 1, 5);
CellRangeAddress callRangeAddres9 = new CellRangeAddress(13, 16, 6, 11);
CellRangeAddress callRangeAddres10 = new CellRangeAddress(13, 16, 12, 13);
CellRangeAddress callRangeAddres11 = new CellRangeAddress(13, 16, 14, 15);
// 第二侧边
CellRangeAddress callRangeAddres12 = new CellRangeAddress(17, 25, 0, 0);
CellRangeAddress callRangeAddres13 = new CellRangeAddress(17, 25, 1, 11);
// 第三侧边
CellRangeAddress callRangeAddres14 = new CellRangeAddress(26, 36, 0, 0);
CellRangeAddress callRangeAddres15 = new CellRangeAddress(26, 36, 1, 11);
//竖行条形码区域
CellRangeAddress callRangeAddres16 = new CellRangeAddress(17, 36, 12, 15);
// 尾部
CellRangeAddress callRangeAddres17 = new CellRangeAddress(37, 40, 0, 8);
CellRangeAddress callRangeAddres18 = new CellRangeAddress(37, 38, 9, 15);
CellRangeAddress callRangeAddres19 = new CellRangeAddress(39, 40, 9, 15);
sheet.addMergedRegion(callRangeAddres);
sheet.addMergedRegion(callRangeAddres1);
sheet.addMergedRegion(callRangeAddres2);
sheet.addMergedRegion(callRangeAddres3);
sheet.addMergedRegion(callRangeAddres4);
sheet.addMergedRegion(callRangeAddres5);
sheet.addMergedRegion(callRangeAddres6);
sheet.addMergedRegion(callRangeAddres7);
sheet.addMergedRegion(callRangeAddres8);
sheet.addMergedRegion(callRangeAddres9);
sheet.addMergedRegion(callRangeAddres10);
sheet.addMergedRegion(callRangeAddres11);
sheet.addMergedRegion(callRangeAddres12);
sheet.addMergedRegion(callRangeAddres13);
sheet.addMergedRegion(callRangeAddres14);
sheet.addMergedRegion(callRangeAddres15);
sheet.addMergedRegion(callRangeAddres16);
sheet.addMergedRegion(callRangeAddres17);
sheet.addMergedRegion(callRangeAddres18);
sheet.addMergedRegion(callRangeAddres19);
for (int rows = 5; rows <= 40; rows++) {
forExcel(rows, 15, workbook, sheet);
}
forExceles(4, 15, workbook, sheet);
//设置默认列宽
sheet.setDefaultColumnWidth(5);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
// 运单号
cell.setCellValue("SGFGH0000132079YQ");
cell.setCellStyle(excelBorrFont);
HSSFRow row2 = sheet.createRow(5);
HSSFCell cells1 = row2.createCell(0);
forExcels(5, 15, workbook, sheet);
cells1.setCellValue("shipment details");
cells1.setCellStyle(hssfCellStyles);
HSSFCell cell2 = row2.createCell(1);
cell2.setCellValue("ORGIN:CAN");
cell2.setCellStyle(leftCellStyle);
HSSFCell cell3 = row2.createCell(5);
cell3.setCellStyle(leftCellStyle);
cell3.setCellValue("Destination:IL");
HSSFCell cell4 = row2.createCell(12);
cell4.setCellStyle(leftCellStyle);
cell4.setCellValue("Product:G-Home");
HSSFRow row6 = sheet.createRow(8);
HSSFCell cell6 = row6.createCell(1);
cell6.setCellStyle(leftCellStyle);
forExcels(8, 15, workbook, sheet);
cell6.setCellValue("Weight:0.536KG");
HSSFCell cell7 = row6.createCell(6);
cell7.setCellStyle(leftCellStyle);
cell7.setCellValue("Description of goods:Blouse");
HSSFCell cell8 = row6.createCell(14);
//加载单元格样式
cell8.setCellStyle(leftCellStyle);
cell8.setCellValue("Prepaid:(P)");
HSSFRow row13 = sheet.createRow(13);
HSSFCell cell13 = row13.createCell(1);
cell13.setCellStyle(leftCellStyle);
forExcels(13, 15, workbook, sheet);
cell13.setCellValue("Customs Valus:USD43.27");
HSSFCell cell14 = row13.createCell(6);
cell14.setCellStyle(leftCellStyle);
cell14.setCellValue("Goods Origin:CN");
HSSFCell cell15 = row13.createCell(12);
cell15.setCellStyle(leftCellStyle);
cell15.setCellValue("PCS:1");
HSSFCell cell16 = row13.createCell(14);
cell16.setCellStyle(leftCellStyle);
cell16.setCellValue("Services:DDP");
HSSFRow row17 = sheet.createRow(17);
HSSFCell cell17 = row17.createCell(0);
cell17.setCellStyle(hssfCellStyles);
forExcels(17, 15, workbook, sheet);
cell17.setCellValue("shipper Details");
HSSFCell cell18 = row17.createCell(1);
cell18.setCellStyle(leftCellStyle);
cell18.setCellValue("Account:SHEIN" + "\n\n" +
"无法预知数据" + "\n\n" +
"2134216534" + "\n\n" +
"CHINA 23213");
HSSFRow row28 = sheet.createRow(26);
HSSFCell cell28 = row28.createCell(0);
cell28.setCellStyle(hssfCellStyles);
forExcels(26, 15, workbook, sheet);
cell28.setCellValue("Consignee Details");
HSSFCell cell29 = row28.createCell(1);
cell29.setCellStyle(leftCellStyle);
cell29.setCellValue("tatiana:leikin" + "\n" +
"无法预知数据" + "\n\n\n" +
"2134216534" + "\n" +
"CHINA23213" + "\n" +
"fdsdfsdfsdf" + "\n" +
"IL 23213123");
HSSFRow row40 = sheet.createRow(37);
HSSFCell cell40 = row40.createCell(0);
cell40.setCellStyle(leftCellStyle);
forExcels(37, 15, workbook, sheet);
cell40.setCellValue("Remarks:0.536KG");
HSSFCell cell49 = row40.createCell(9);
cell49.setCellStyle(leftCellStyle);
cell49.setCellValue("Order Number:" + "\n" + "BG87467326472364");
HSSFRow row42 = sheet.createRow(39);
HSSFCell cell42 = row42.createCell(9);
cell42.setCellStyle(leftCellStyle);
forExcels(39, 15, workbook, sheet);
cell42.setCellValue("Print Date:" + "\n" + "08:43");
try {
// 输出Excel文件
Date date = new Date();
SimpleDateFormat yyyyMMddHHmmss = new SimpleDateFormat("yyyyMMddHHmmss");
String format = yyyyMMddHHmmss.format(date);
String paths = "D:\\202103\\" + "Waybill_" + format + ".xls";
FileOutputStream fos = new FileOutputStream(new File(paths));
workbook.write(fos);
workbook.close();
fos.close();
// 生成条形码
String imgPath = "D:\\202103\\";
String imgPaths = InsExcel.makeBarcode("SGFGH0000132079YQ", imgPath);
// 条形码路径,xls路径,新生成的xls路径
InsExcel.insBarcodeInExcel(imgPaths, paths, paths,
0, 7, 8, 4.2);
// 把图片旋转90°
BufferedImage src = ImageIO.read(new File(imgPaths));
// 旋转90
BufferedImage rotate = RotateImage.Rotate(src, 90);
ImageIO.write(rotate, "png", new File(imgPaths));
InsExcel.insBarcodeInExcel(imgPaths, paths, paths,
18, 13, 2.6, 17);
//加载Excel文档
String pathPDF = "D:\\202103\\" + "Waybill_" + format + ".pdf";
com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
wb.loadFromFile(paths);
//调用方法保存为PDF格式 发票模板
wb.saveToFile(pathPDF, FileFormat.PDF);
File file = new File(paths);
file.delete();
File imgPathsfile = new File(imgPaths);
imgPathsfile.delete();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void forExcel(int row, int cells, HSSFWorkbook workbook, HSSFSheet sheet) {
//加载单元格样式
HSSFRow row1 = sheet.createRow(row);
for (int i = 0; i <= cells; i++) {
Cell cell = row1.createCell(i);
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cell.setCellStyle(style);
}
}
private static void forExcels(int row, int cells, HSSFWorkbook workbook, HSSFSheet sheet) {
//加载单元格样式
HSSFRow row1 = sheet.createRow(row);
for (int i = 0; i <= cells; i++) {
Cell cell = row1.createCell(i);
HSSFCellStyle style = workbook.createCellStyle();
if (row == 5) {
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cell.setCellStyle(style);
} else {
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cell.setCellStyle(style);
}
}
}
private static void forExceles(int row, int cells, HSSFWorkbook workbook, HSSFSheet sheet) {
//加载单元格样式
HSSFRow row1 = sheet.createRow(row);
for (int i = 0; i <= cells; i++) {
Cell cell = row1.createCell(i);
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cell.setCellStyle(style);
}
}
private static HSSFCellStyle excelBor(HSSFWorkbook workbook) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
//水平居中
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
font.setFontHeightInPoints((short) 7);
//加载字体
style.setFont(font);
return style;
}
private static HSSFCellStyle excelBorr(HSSFWorkbook workbook) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
//水平居中
style.setWrapText(true);
style.setRotation((short) 90);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
font.setFontHeightInPoints((short) 10);
//加载字体
style.setFont(font);
return style;
}
private static HSSFCellStyle excelBorrFont(HSSFWorkbook workbook) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
//水平居中
style.setWrapText(true);
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//加载字体
return style;
}
private static HSSFCellStyle excelBors(HSSFWorkbook workbook) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
//水平居中
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
font.setFontHeightInPoints((short) 15);
//加载字体
style.setFont(font);
return style;
}
private static HSSFCellStyle leftexcelBors(HSSFWorkbook workbook) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
//水平居中
style.setWrapText(true);
//创建字体
HSSFFont font = workbook.createFont();
//是否加粗字体
font.setFontHeightInPoints((short) 10);
//加载字体
style.setFont(font);
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.TOP);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
return style;
}
public static void main(String[] args) {
excelLink();
}
public class InsExcel {
public static void main(String[] args) throws Exception {
//生成条码
String bcVal = "SGFGH0000132079YQ";
//生成条码图片
String bcPath = InsExcel.makeBarcode(bcVal,"D:\\202103\\");
//插入excel
String newExcelPath = "D:\\202103\\base.xls";
//InsExcel.insBarcodeInExcel("D:\\202103\\xiao.png","D:\\202103\\sdsadasdasdsa.xls",newExcelPath);
}
//生成条码值
public static String makeBarcodeValue() {
return "A" + System.currentTimeMillis();
}
//生成条码文件至临时目录,并返回生成图片的路径信息
public static String makeBarcode(final String barcodeValue,String barcodePicPath) {
//存放条码图片的路径
try {
//Create the barcode bean
Code39Bean bean = new Code39Bean();
final int dpi = 250;
bean.setModuleWidth(UnitConv.in2mm(1.0f / dpi));
bean.setWideFactor(5);
bean.doQuietZone(false);
File outputFile = new File(barcodePicPath + barcodeValue + ".png");
OutputStream out = new FileOutputStream(outputFile);
try {
BitmapCanvasProvider canvas = new BitmapCanvasProvider(
out, "image/png", dpi, BufferedImage.TYPE_BYTE_BINARY, false, 0);
bean.generateBarcode(canvas, barcodeValue);
canvas.finish();
} finally {
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return barcodePicPath + barcodeValue + ".png";
}
public static void insBarcodeInExcel(String barcodePic,String path,String newExcelPath,
int row,int coll, double a, double b) {
FileInputStream input = null;
try {
input = new FileInputStream(new File(path));// excelPath,Excel
// 文件 的绝对路径
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
InputStream inputStream = new FileInputStream(barcodePic);
byte[] bytes = IOUtils.toByteArray(inputStream);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
inputStream.close();
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setRow1(row);
anchor.setCol1(coll);
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize(a,b);
FileOutputStream fileOut = new FileOutputStream(newExcelPath);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (input != null) {
input.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
还可使用html来做pdf的转换,此文章自己安装pdf转换的依赖,博主首页下载。
自己原创有效,欢迎指点。
欢迎使用Markdown编辑器
你好! 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章,了解一下Markdown的基本语法知识。
新的改变
我们对Markdown编辑器进行了一些功能拓展与语法支持,除了标准的Markdown编辑器功能,我们增加了如下几点新功能,帮助你用它写博客:
- 全新的界面设计 ,将会带来全新的写作体验;
- 在创作中心设置你喜爱的代码高亮样式,Markdown 将代码片显示选择的高亮样式 进行展示;
- 增加了 图片拖拽 功能,你可以将本地的图片直接拖拽到编辑区域直接展示;
- 全新的 KaTeX数学公式 语法;
- 增加了支持甘特图的mermaid语法1
- 增加了 多屏幕编辑 Markdown文章功能;
- 增加了 焦点写作模式、预览模式、简洁写作模式、左右区域同步滚轮设置 等功能,功能按钮位于编辑区域与预览区域中间;
- 增加了 检查列表 功能。
功能快捷键
撤销:Ctrl/Command + Z
重做:Ctrl/Command + Y
加粗:Ctrl/Command + B
斜体:Ctrl/Command + I
标题:Ctrl/Command + Shift + H
无序列表:Ctrl/Command + Shift + U
有序列表:Ctrl/Command + Shift + O
检查列表:Ctrl/Command + Shift + C
插入代码:Ctrl/Command + Shift + K
插入链接:Ctrl/Command + Shift + L
插入图片:Ctrl/Command + Shift + G
查找:Ctrl/Command + F
替换:Ctrl/Command + G
合理的创建标题,有助于目录的生成
直接输入1次#,并按下space后,将生成1级标题。
输入2次#,并按下space后,将生成2级标题。
以此类推,我们支持6级标题。有助于使用TOC
语法后生成一个完美的目录。
如何改变文本的样式
强调文本 强调文本
加粗文本 加粗文本
标记文本
删除文本
引用文本
H2O is是液体。
210 运算结果是 1024.
插入链接与图片
图片:
带尺寸的图片:
居中的图片:
居中并且带尺寸的图片:
当然,我们为了让用户更加便捷,我们增加了图片拖拽功能。
如何插入一段漂亮的代码片
去博客设置页面,选择一款你喜欢的代码片高亮样式,下面展示同样高亮的 代码片
.
// An highlighted block
var foo = 'bar';
生成一个适合你的列表
- 项目
- 项目
- 项目
- 项目1
- 项目2
- 项目3
创建一个表格
一个简单的表格是这么创建的:
项目 | Value |
电脑 | $1600 |
手机 | $12 |
导管 | $1 |
设定内容居中、居左、居右
使用:---------:
居中
使用:----------
居左
使用----------:
居右
第一列 | 第二列 | 第三列 |
第一列文本居中 | 第二列文本居右 | 第三列文本居左 |
SmartyPants
SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如:
TYPE | ASCII | HTML |
Single backticks |
| ‘Isn’t this fun?’ |
Quotes |
| “Isn’t this fun?” |
Dashes |
| – is en-dash, — is em-dash |
创建一个自定义列表
HTML
Authors
John
Luke
如何创建一个注脚
一个具有注脚的文本。2
注释也是必不可少的
Markdown将文本转换为 HTML。
KaTeX数学公式
您可以使用渲染LaTeX数学表达式 KaTeX:
Gamma公式展示
你可以找到更多关于的信息 LaTeX 数学表达式here.
新的甘特图功能,丰富你的文章
2014-01-07 2014-01-09 2014-01-11 2014-01-13 2014-01-15 2014-01-17 2014-01-19 2014-01-21 已完成 进行中 计划一 计划二 现有任务 Adding GANTT diagram functionality to mermaid
- 关于 甘特图 语法,参考 这儿,
UML 图表
可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图:
张三 李四 王五 你好!李四, 最近怎么样? 你最近怎么样,王五? 我很好,谢谢! 我很好,谢谢! 李四想了很长时间, 文字太长了 不适合放在一行. 打量着王五... 很好... 王五, 你怎么样? 张三 李四 王五
这将产生一个流程图。:
链接
长方形
圆
圆角长方形
菱形
- 关于 Mermaid 语法,参考 这儿,
FLowchart流程图
我们依旧会支持flowchart的流程图:
Created with Raphaël 2.3.0 开始 我的操作 确认? 结束 yes no
导出
如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ,生成一个.md文件或者.html文件进行本地保存。
导入
如果你想加载一篇你写过的.md文件,在上方工具栏可以选择导入功能进行对应扩展名的文件导入,
继续你的创作。