1. 介绍
提供poi的excel操作：如excel读取和excel写入，支持数据上传或数据下载的场景操作
2. 使用
gradle中
implementation('org.yunchen.gb:gb-plugin-poi:1.4.0.1')3. 功能描述
两个辅助类，分别是ExcelReadBuilder和ExcelWriteBuilder ExcelReadBuilder使用示例：
new ExcelBuilder(2003,file.bytes).eachLine {
println "First column on row ${row.rowNum} = "+row.getCell(0) +row.getCell(1)
println cell(1)
}ExcelWriteBuilder使用示例：
ExcelWriteBuilder workbook = new ExcelWriteBuilder(new File('c://b.xls')).workbook {
sheet("Data") { // sheet1
row(["Invoice Number", "Invoice Date", "Amount"])
row(["100", Date.parse("yyyy-MM-dd", "2010-10-18"), 123.45])
row(["600", Date.parse("yyyy-MM-dd", "2010-11-17"), 132.54])
skipRow(7)
row(['页脚'])
}
sheet("Summary") { // sheet2
row(["Sheet: Summary"])
skipRow(10)
row(["Total", 123.45 + 132.54])
}
sheet("sheet1"){
skipRow(10)
row(["Total", 123.45 + 132.54])
}
}
workbook.dwonload();4. 使用word模板
4.1. word模板使用
WordOperateBuilder是用来进行word模板的操作的辅助类，支持对word中文字、表格、图表进行直观操作。
| jdk1.8 使用 版本1.3.1.6, jdk11以上需要使用版本1.4.0.1 |
4.2. 使用示例：
以下示例演示了加载远程json数据转化为Map对象，对word模板中的文字替换，表格中的公式计算，及图表的操作 针对word中使用${} 进行标记的地方进行变量替换或计算 如： ${year}
//使用Object进行json和对象的转换
ObjectMapper objectMapper=new ObjectMapper()
Map needMap=objectMapper.readValue(dataJson,Map)
//此处使用本地Map演示
Map valueMap=[year:2025,num1:999,worker4:'工作者',ending:'多谢！再见！',value1:0.66,tHeader:'标题','row1':'row1',row2:'row2',row3:'row3',row4:'row4',row5:'row5',row6:'row6',row7:'row7',row8:'row8',row9:'row9',row10:'row10']
Binding binding = new Binding()
valueMap.each{k,v->
binding.setVariable(k,v)
}
GroovyShell groovyShell = new GroovyShell(binding)
binding.setVariable("valueMap",valueMap)
//获取resources下的word模板文件
File tempFile=File.createTempFile("_tmp",".docx")
File tempOutFile=File.createTempFile("_tmp",".docx")
//开发模式或jar部署模式获取模板文件
URL templateFileResource=ClassLoader.getResource("templates/tools/template1.docx")
if(templateFileResource){
tempFile.bytes=new File(templateFileResource.toURI()).bytes
}else{
//jar包模式下获取模板文件
tempFile.bytes=this.class.classLoader.getResourceAsStream("templates/tools/template1.docx")?.bytes
}
//操作word
WordOperateBuilder wordOperateBuilder=new WordOperateBuilder(tempFile)
wordOperateBuilder.with {
//替换段落中的变量
document {XWPFDocument document->
//改文字
eachParagraph { XWPFParagraph paragraph->
replaceParagraphText(valueMap)
}
}
//修改表格中的变量
eachTable { XWPFTable table->
eachTableRow{ XWPFTableRow row->
eachTableCell{ XWPFTableCell cell->
if(cell.text.contains('${') && cell.text.contains('}')){
String evalStr=cell.text.replaceAll('\\$','').replaceAll('\\{','').replaceAll('\\}','')
cell.text = groovyShell.evaluate(evalStr)?.toString()
}
}
}
}
//写入chart中的数据表，以第一个chart的修改示例
editChart(0){ XWPFChart chart->
chart.setTitleText('new title3')
workbook{
sheet('Sheet1'){ org.apache.poi.ss.usermodel.Sheet sheet->
//修改excel数据源数据
// 示例计算表格中的一个公式
Cell cell=sheet.getRow(2).getCell(1)
if(cell.toString().contains('${') && cell.toString().contains('}')){
String evalStr=cell.toString().replaceAll('\\$','').replaceAll('\\{','').replaceAll('\\}','')
cell.setCellValue(groovyShell.evaluate(evalStr))
}
//另一种修改方式
skipRow(1)
row(['河北省', 0.99])
//修改chart 中的cache数据 （判断表格区域）
XDDFCategoryDataSource category = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1,10,0,1))
XDDFChartData.Series series=chart.getChartSeries().get(0).getSeries(0)
series.replaceData(category, XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1,10,1,1)))
series.plot()
}
}
}
//两种输出文件的方式
tempOutFile.bytes=getBytes()
saveAs(tempOutFile.path)
}4.3. 新建word内容
WordOperateBuilder wordOperateBuilder=new WordOperateBuilder()
wordOperateBuilder.with {
document {XWPFDocument document->
createParagraph{XWPFParagraph paragraph->
createRun { XWPFRun run->
run.text='新建word文档'
run.fontFamily='微软雅黑'
run.fontSize=12
run.color='FF0000'
run.bold=false
}
}
createTable(3,3){ XWPFTable table->
int rowNum=0;
eachTableRow{ XWPFTableRow row->
rowNum++
int columnNum=0
eachTableCell{ XWPFTableCell cell->
columnNum++
cell.text = rowNum*10+columnNum
}
}
}
createChart(15* Units.EMU_PER_CENTIMETER,5*Units.EMU_PER_CENTIMETER){ XWPFChart chart->
chart.titleText='aaa'
List categories=['2023-01-01','2023-01-02','2023-01-03','2023-01-04','2023-01-05','2023-01-06']
List HistogramvaluesA=[1.74,2.31,0.65,1.42,2.00,1.73]
List LinevaluesA=[1.74,2.31,0.65,1.42,2.00,1.73]
String categoryDataRange=chart.formatRange(new CellRangeAddress(1,categories.size(),0,0))
String valuesDataRangeA=chart.formatRange(new CellRangeAddress(1,categories.size(),1,1))
XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromArray(categories.toArray() as String[], categoryDataRange, 1);
XDDFNumericalDataSource<Double> valuesDataA = XDDFDataSourcesFactory.fromArray(HistogramvaluesA.toArray() as Number[], valuesDataRangeA, 1);
//创建X轴
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.TOP);
// 左Y轴
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
// 左Y轴和X轴交叉点在X轴0点位置，在这里我直接注释掉了。
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 构建坐标轴
leftAxis.crossAxis(bottomAxis);
bottomAxis.crossAxis(leftAxis);
//设置柱状图Y轴名称，方位和坐标轴大小
leftAxis.setTitle("降雨量/mm");
leftAxis.setCrosses(AxisCrosses.MAX);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// create series
bottomAxis.setMajorTickMark(AxisTickMark.NONE);//取消X轴的标刻度
//获取X轴 图表的基本配置都在这个对象里面里面
CTCatAx catAx = chart.getCTChart().getPlotArea().getCatAxArray(0);
CTSkip ctSkip = CTSkip.Factory.newInstance();
//设置显示间隔
ctSkip.setVal((int) Math.ceil(1));
catAx.setTickLblSkip(ctSkip);
//设置标签位置为最下
CTTickLblPos ctTickLblPos = CTTickLblPos.Factory.newInstance();
ctTickLblPos.setVal(STTickLblPos.LOW);
catAx.setTickLblPos(ctTickLblPos);
//获取Y轴 图表的基本配置都在这个对象里面里面
CTValAx catAy = chart.getCTChart().getPlotArea().getValAxArray(0);
CTScaling ctScaling ;
ctScaling = catAy.addNewScaling();
//设置柱状图Y轴坐标最大值
ctScaling.addNewMax().setVal(8);
ctScaling.addNewOrientation().setVal(STOrientation.MAX_MIN);
catAy.setScaling(ctScaling);
// 设置图表背后的网格线
CTLineProperties ctLine = catAy.addNewMajorGridlines().addNewSpPr().addNewLn();
ctLine.addNewPrstDash().setVal(STPresetLineDashVal.DASH);
//创建柱状图数据对象
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
((XDDFBarChartData) data).setBarDirection(BarDirection.COL);
//柱状图图例标题
XDDFChartData.Series series = data.addSeries(categoriesData, valuesDataA);
int column=0
XSSFSheet sheet=chart.getWorkbook().createSheet()
sheet.createRow(0).createCell(column).cellValue='new title'
CellReference cellReference=new CellReference(sheet.getSheetName(), 0, column, true, true);
series.setTitle("下雨量", cellReference);
chart.plot(data);
//-----------------------------------------折线图-------------------------------------------------
// 右Y轴
XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
// 右Y轴和X轴交叉点在X轴最大值位置
rightAxis.setCrosses(AxisCrosses.MIN);
rightAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
// 构建坐标轴
rightAxis.crossAxis(bottomAxis);
bottomAxis.crossAxis(rightAxis);
//设置折线图Y轴名称
rightAxis.setTitle("水位/m");
XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(categories.toArray() as String[]);
//设置折线图Y轴坐标最大值
rightAxis.setMaximum(8);
//LINE：折线图，
data = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
//加载折线图数据
XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromArray(LinevaluesA.toArray() as Number[]);
//图表加载数据，折线1
XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
//折线图例标题
series1.setTitle("水位", null);
//直线
series1.setSmooth(true);
//设置标记大小
series1.setMarkerSize((short) 2);
//设置空数据显示间隙
CTDispBlanksAs disp = CTDispBlanksAs.Factory.newInstance();
disp.setVal(STDispBlanksAs.GAP);
chart.getCTChart().setDispBlanksAs(disp);
data.setVaryColors(false);
//绘制
chart.plot(data);
//设置图表图例
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP);
}
//document.write(new FileOutputStream(new File("C:\\Users\\xiaopeng\\Desktop\\new.docx")))
saveAs("C:\\Users\\xiaopeng\\Desktop\\new.docx")
//toHtml('C:\\Users\\xiaopeng\\Desktop\\new.html')
//toPdf("C:\\Users\\xiaopeng\\Desktop\\new.pdf")
close()
}
}
4.4. 使用样例word文件内容更改
WordOperateBuilder使用示例：模板wordTemplate.docx
new WordOperateBuilder("C:\\Users\\Administrator\\Desktop\\wordTemplate.docx").document { XWPFDocument document->
//操作段落
eachParagraph {XWPFParagraph paragraph->
println paragraph.getText()
//操作文字组合
//替换 设置样式 包括字体大小、颜色和粗体属性
eachRun{XWPFRun replaceRun->
if(replaceRun.text()=='大学'){
replaceRun.setText('n',0)
replaceRun.setFontSize(36)
replaceRun.setColor('FF0000')
replaceRun.setBold(true)
}
}
// 插入设置
eachRun{
XWPFRun insertRun ->
if (insertRun.text() == 'auto'){
insertRun.setText("在此处插入一段文字")
}
}
}
//操作表格
eachTable{XWPFTable table->
eachTableRow{XWPFTableRow row->
eachTableCell{XWPFTableCell cell->
println cell.getText()
}
}
}
//修改表格
eachTable { XWPFTable table ->
table.
table.addNewCol()//加新行,包含原样式
//替换行对象，使用操作文字方法
eachTableRow { XWPFTableRow row ->
row.addNewTableCell()//加新列,包含原样式
//替换列对象，使用操作文字方法
row.getCell(0).setText("al")//追加值,包含原样式
row.getCell(3).setText("tx")//追加值,包含原样式
}
table.addNewCol()//加新行,包含原样式
}
//读取chart中的数据表
eachChart{XWPFChart chart->
eachLineInSheet([sheet:'Sheet1',labels:true]){Row row->
println "First column on row ${row.rowNum} = "+row.getCell(0) +row.getCell(1)
println cell(1)
}
}
//写入chart中的数据表
eachChart{XWPFChart chart->
chart.setTitleText('new title3')
workbook{
sheet('Sheet1'){ org.apache.poi.ss.usermodel.Sheet sheet->
//修改excel数据源数据
skipRow(1)
row([null, 400d])
row([null, 400d])
row([null, 400d])
row([null, 400d])
//修改chart 中的cache数据
XDDFCategoryDataSource category = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1,4,0,0))
getChartSeries(0){XDDFChartData chartSeries->
getSeries(0){XDDFChartData.Series series->
series.setTitle(sheet.getRow(0).getCell(1).richStringCellValue.toString(), new CellReference(sheet.sheetName, 0, 1, true, true));
series.replaceData(category, XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1,4,1,1)));
series.plot();
//修改fill颜色
changeSeriesFillColor(PresetColor.CHARTREUSE)
}
}
}
}
}
saveAs("C:\\Users\\xiaopeng\\Desktop\\new.docx")
//toHtml('C:\\Users\\xiaopeng\\Desktop\\new.html')
//toPdf("C:\\Users\\xiaopeng\\Desktop\\new.pdf")
}