1、准备好Excel模板如图
2、代码实现参数替换
主要代码
private static void generatePaymentNoticeSheet( final XSSFWorkbook workbook, final Map< String, String > dataMap ) throws IOException
{
final String templatePath = "PN_Template_BS";
// 获取客户结算单的模板路径
final String path = JANUtil.basePath + "/template/" + ( JANUtil.filterEmpty( templatePath ) != null ? templatePath : "Payment_Notice_Template" ) + ".xlsx";
// 创建File
final File file = new File( path );
// 初始化工作薄
final XSSFWorkbook templateWorkbook = file.exists() ? new XSSFWorkbook( new FileInputStream( file ) ) : new XSSFWorkbook();
final Map< String, String > placeholderMap = new HashMap< String, String >();
placeholderMap.put( "entityName", "${entityName}" );
placeholderMap.put( "clientInvoiceType", "${clientInvoiceType}" );
placeholderMap.put( "clientName", "${clientName}" );
placeholderMap.put( "clientRegisterAddress", "${clientRegisterAddress}" );
placeholderMap.put( "clientTaxCode", "${clientTaxCode}" );
placeholderMap.put( "clientBankBranch", "${clientBankBranch}" );
placeholderMap.put( "settleMonth", "${settleMonth}" );
placeholderMap.put( "clientBankAccount", "${clientBankAccount}" );
placeholderMap.put( "lastPaymentDate", "${lastPaymentDate}" );
placeholderMap.put( "entityBankAccountName", "${entityBankAccountName}" );
placeholderMap.put( "entityBankBranch", "${entityBankBranch}" );
placeholderMap.put( "entityBankAccount", "${entityBankAccount}" );
placeholderMap.put( "serviceFee", "${serviceFee}" );
placeholderMap.put( "businessType", "${businessType}" );
placeholderMap.put( "saleBranch", "${saleBranch}" );
placeholderMap.put( "ownerName", "${ownerName}" );
placeholderMap.put( "sbAmountCompany", "${sbAmountCompany}" );
placeholderMap.put( "sbAmountPersonal", "${sbAmountPersonal}" );
placeholderMap.put( "fundAmountCompany", "${fundAmountCompany}" );
placeholderMap.put( "fundAmountPersonal", "${fundAmountPersonal}" );
placeholderMap.put( "disableAmount", "${disableAmount}" );
placeholderMap.put( "personalIncomeTax", "${personalIncomeTax}" );
placeholderMap.put( "actualPay", "${actualPay}" );
placeholderMap.put( "archiveAmount", "${archiveAmount}" );
placeholderMap.put( "otherCollectionAndPay", "${otherCollectionAndPay}" );
placeholderMap.put( "cbAmount", "${cbAmount}" );
placeholderMap.put( "taxRate", "${taxRate}" );
placeholderMap.put( "taxFee", "${taxFee}" );
placeholderMap.put( "serviceFee", "${serviceFee}" );
placeholderMap.put( "businessType", "${businessType}" );
placeholderMap.put( "saleBranch", "${saleBranch}" );
placeholderMap.put( "ownerName", "${ownerName}" );
final List< String > numericPlaceholders = new ArrayList< String >();
numericPlaceholders.add( "sbAmountCompany" );
numericPlaceholders.add( "sbAmountPersonal" );
numericPlaceholders.add( "fundAmountCompany" );
numericPlaceholders.add( "fundAmountPersonal" );
numericPlaceholders.add( "disableAmount" );
numericPlaceholders.add( "personalIncomeTax" );
numericPlaceholders.add( "actualPay" );
numericPlaceholders.add( "archiveAmount" );
numericPlaceholders.add( "otherCollectionAndPay" );
numericPlaceholders.add( "cbAmount" );
numericPlaceholders.add( "taxRate" );
numericPlaceholders.add( "taxFee" );
numericPlaceholders.add( "serviceFee" );
int pnIndex = 0;
if ( JANUtil.filterEmpty( dataMap.get( "businessTypeId" ) ) != null )
{
// 1 ：派遣；2 ：代理（单立户）； 3 ：代理（大户）； 5 ：代发工资；
if ( dataMap.get( "businessTypeId" ).equals( "1" ) || dataMap.get( "businessTypeId" ).equals( "2" ) || dataMap.get( "businessTypeId" ).equals( "3" )
|| dataMap.get( "businessTypeId" ).equals( "5" ) )
{
pnIndex = 1;
}
// 4 ：商业保险； 6 ：存档； 7 ：其他； 8 ：全风险外包； 9 ：薪酬管理； 10：基础性外包
else if ( dataMap.get( "businessTypeId" ).equals( "4" ) || dataMap.get( "businessTypeId" ).equals( "6" ) || dataMap.get( "businessTypeId" ).equals( "7" )
|| dataMap.get( "businessTypeId" ).equals( "8" ) || dataMap.get( "businessTypeId" ).equals( "9" ) || dataMap.get( "businessTypeId" ).equals( "10" ) )
{
pnIndex = 2;
}
}
// 获取工作表
final XSSFSheet templateSheet = templateWorkbook.getSheetAt( pnIndex );
templateSheet.setForceFormulaRecalculation( true );
if ( placeholderMap != null && placeholderMap.size() > 0 && dataMap != null && dataMap.size() > 0 )
{
// 遍历行
for ( int i = 1; i < templateSheet.getPhysicalNumberOfRows(); i++ )
{
// 读取左上端单元格
final Row row = templateSheet.getRow( i );
// 行不为空
if ( row != null )
{
// 获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
// 遍历列
for ( int j = 0; j < cells; j++ )
{
// 获取到列的值
Cell cell = row.getCell( j );
if ( cell != null )
{
String cellValue = "";
final CellType cellType = cell.getCellTypeEnum();
if ( cellType == CellType.NUMERIC )
{
cellValue = String.valueOf( cell.getNumericCellValue() );
}
else if ( cellType == CellType.STRING )
{
cellValue = cell.getStringCellValue();
}
if ( JANUtil.filterEmpty( cellValue ) != null && cellValue.contains( "$" ) )
{
final String field = JANUtil.getSubString( cellValue, "{", "}" );
if ( numericPlaceholders.contains( field ) )
{
cell.setCellValue( JANUtil.isDecimal( dataMap.get( field ) ) ? Double.valueOf( dataMap.get( field ) ) : 0 );
}
else
{
cell.setCellValue( cellValue.replace( "${" + field + "}", JANUtil.filterEmpty( dataMap.get( field ) ) != null ? dataMap.get( field ) : "" ) );
}
}
}
}
}
}
// 创建表格
final Sheet sheet = workbook.createSheet( dataMap.get( "sheetName" ) );
sheet.setForceFormulaRecalculation( true );
copySheet( workbook, templateSheet, sheet );
templateWorkbook.close();
}
}
private static void copySheet( Workbook workbook, Sheet fromSheet, Sheet toSheet )
{
mergeSheetAllRegion( fromSheet, toSheet );
// 设置列宽
int length = fromSheet.getRow( fromSheet.getFirstRowNum() ).getLastCellNum();
for ( int i = 0; i <= length; i++ )
{
toSheet.setColumnWidth( i, fromSheet.getColumnWidth( i ) );
}
for ( Iterator< ? > rowIt = fromSheet.rowIterator(); rowIt.hasNext(); )
{
Row oldRow = ( Row ) rowIt.next();
Row newRow = toSheet.createRow( oldRow.getRowNum() );
copyRow( workbook, oldRow, newRow );
}
}
private static void copyRow( Workbook workbook, Row oldRow, Row toRow )
{
toRow.setHeight( oldRow.getHeight() );
for ( Iterator< ? > cellIt = oldRow.cellIterator(); cellIt.hasNext(); )
{
Cell tmpCell = ( Cell ) cellIt.next();
Cell newCell = toRow.createCell( tmpCell.getColumnIndex() );
if ( tmpCell != null && tmpCell.toString() != "" )
{
copyCell( workbook, tmpCell, newCell );
}
}
}
private static void copyCell( Workbook workbook, Cell fromCell, Cell toCell )
{
//注意样式创建太多报错，容量大概4000
CellStyle newstyle = workbook.createCellStyle();
try
{
// 复制单元格样式
newstyle.cloneStyleFrom( fromCell.getCellStyle() );
}
catch ( Exception e )
{
System.err.println( e );
}
// 样式
toCell.setCellStyle( newstyle );
if ( fromCell.getCellComment() != null )
{
toCell.setCellComment( fromCell.getCellComment() );
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellTypeEnum();
toCell.setCellType( fromCellType );
if ( fromCellType == CellType.NUMERIC )
{
if ( DateUtil.isCellDateFormatted( fromCell ) )
{
toCell.setCellValue( fromCell.getDateCellValue() );
}
else
{
toCell.setCellValue( fromCell.getNumericCellValue() );
}
}
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
}
}
3、最终结果
