java jxl模板 list_java(jxl)根据excle模板填值生成新excle返回

importjxl.Workbook;importjxl.WorkbookSettings;importjxl.format.CellFormat;importjxl.format.PaperSize;import jxl.write.*;import java.io.*;importjava.util.Date;importjava.util.Map;importjava.util.UUID;/*** Jxl工具类*/

public classJxlUtils {/*** 生成新文件临时路径

*

*@return

*/

private staticString getNewFilePath() {return new Date().getTime() + UUID.randomUUID().toString().replaceAll("-", "");

}/*** 过程管理*/

static classJxlModel {staticWorkbook wb;staticWritableWorkbook wwb;staticFile f;/*** 初始化打印

*

*@paramfilePath

*@return*@throwsException*/

static WritableSheet openSheet(String filePath) throwsException {//为了降低CPU性能影响 禁用回收机制

WorkbookSettings wbs = newWorkbookSettings();

wbs.setGCDisabled(true);

//不设置会报错access超出长度

wbs.setWriteAccess(null);//获取模板内容

wb =getModelWorkbook(filePath);//利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄

String newFilePath = "/tmp/" + getNewFilePath() + ".xls";

f= newFile(newFilePath);

FileOutputStream output= newFileOutputStream(f);

wwb=Workbook.createWorkbook(output, wb, wbs);//读取第一张工作表

WritableSheet ws = wwb.getSheet(0);//设置打印属性

ws.getSettings().setPaperSize(PaperSize.A4);

ws.getSettings().setTopMargin(0.1);

ws.getSettings().setBottomMargin(0.1);

ws.getSettings().setLeftMargin(0.2);

ws.getSettings().setRightMargin(0.2);returnws;

}/*** 写入文件，关闭打印

*

*@return*@throwsIOException

*@throwsWriteException*/

static FileInputStream end() throwsException {

wwb.write();

wwb.close();

wb.close();return newFileInputStream(f);

}

}/*** 获取模板

*

*@paramfilePath

*@return*@throwsException*/

private static Workbook getModelWorkbook(String filePath) throwsException {//构建Workbook对象, 只读Workbook对象//直接从本地文件创建Workbook

InputStream instream = newFileInputStream(filePath);

Workbook wb=Workbook.getWorkbook(instream);returnwb;

}/*** 表格列格式化

*

*@paramwritableSheet

*@paramformat

*@paramcolumn 列数

*@paramcolumns 表格列

*@paramstart 开始行

*@paramsize 列表一共多少行*/

private static void format(WritableSheet writableSheet, Map format, int column, int[] columns, int start, int size) throwsException {//算出列表结尾行

size +=start;//表格开始行

final int rowStart =start;while (start

WritableCell writableCell=writableSheet.getWritableCell(j, start);//全部用blank格式化

Blank blank = newBlank(j, start);if (format.get(j) == null) {

format.put(j, writableCell.getCellFormat());

}else{

blank.setCellFormat(format.get(j));

writableSheet.addCell(blank);

}

}

start++;if (start > rowStart && start

writableSheet.insertRow(start);for (int c = 0; c < columns.length; c++) {int cStart =columns[c];int cEnd =cStart;//最后一个

if (c == columns.length - 1) {

cEnd= column - 1;

}else{

cEnd= columns[c + 1] - 1;

}if (cStart !=cEnd) {

writableSheet.mergeCells(cStart, start, cEnd, start);

}

}

}

}

}/*** 添加lable单元格

*

*@paramwritableSheet

*@paramcontent

*@paramcloum

*@paramrow

*@throwsException*/

private static void insertCell(WritableSheet writableSheet, String content, int cloum, int row) throwsException {//获取单元格

WritableCell writableCell =writableSheet.getWritableCell(cloum, row);//统一label处理

Label lable = newLabel(cloum, row, content);//继承格式

lable.setCellFormat(writableCell.getCellFormat());//写入单元格

writableSheet.addCell(lable);

}/*** 带格式添加lable单元格

*

*@paramwritableSheet

*@paramcontent

*@paramcloum

*@paramrow

*@paramwrap

*@throwsException*/

private static void insertCell(WritableSheet writableSheet, String content, int cloum, int row, boolean wrap) throwsException {//获取单元格

WritableCell writableCell =writableSheet.getWritableCell(cloum, row);//格式化

WritableCellFormat cellFormat = newWritableCellFormat(writableCell.getCellFormat());//换行

cellFormat.setWrap(wrap);//统一label处理

Label lable = newLabel(cloum, row, content);//继承格式

lable.setCellFormat(cellFormat);//写入单元格

writableSheet.addCell(lable);

}

}