欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

jxl读excel模板

程序员文章站 2022-04-09 09:22:18
...
	os = response.getOutputStream();
response.reset();// 清空输出流
response.setContentType("request/vnd.ms-excel");
response.addHeader("Content-Disposition", new String(
"attachment; filename=invoiceExcel.xls".getBytes("GBK"),
"ISO-8859-1"));


public class InvoiceExcel {
@SuppressWarnings({ "unchecked", "rawtypes" })
public static void export(String excelPath, List<Test2> invoiceList,OutputStream os){
//选择模板文件:
try {
InputStream is = new FileInputStream(excelPath);
Workbook wb = Workbook.getWorkbook(is);
//通过模板得到一个可写的Workbook:
WritableCell wc = null;

WritableWorkbook wwb = Workbook.createWorkbook(os, wb);
//选择模板中名称为StateResult的Sheet:

WritableSheet ws = wwb.getSheet("commercial invoice");

WritableCellFormat wcf = ExcelFormatter.getWritableCellFormatCellFormat();
WritableCellFormat noWCF = new WritableCellFormat();
noWCF.setBorder(Border.ALL, BorderLineStyle.NONE);
//选择单元格,写入动态值,根据单元格的不同类型转换成相应类型的单元格:
Label lable;
wc = ws.getWritableCell(7, 9);
wc = ExcelFormatter.cloneCellWithValue(7, 9, "789456",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(7, 11);
wc = ExcelFormatter.cloneCellWithValue(7, 11, "1100019/MAG11041",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(7, 14);
wc = ExcelFormatter.cloneCellWithValue(7, 14, "BY SEA",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(0, 16);
wc = ExcelFormatter.cloneCellWithValue(0, 16, "# 2011 - shanghai dd 03/01/2011",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(5, 16);
wc = ExcelFormatter.cloneCellWithValue(5, 16, "1,22",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(6, 16);
wc = ExcelFormatter.cloneCellWithValue(6, 16, "Moscow",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(7, 16);
wc = ExcelFormatter.cloneCellWithValue(7, 16, "worE",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(0, 10);
wc = ExcelFormatter.cloneCellWithValue(0, 10, "OOO CUMMINS Moscow",noWCF);
ws.addCell(wc);

wc = ws.getWritableCell(0, 11);
wc = ExcelFormatter.cloneCellWithValue(0, 11, "Russia, Khimki Area, Klyazma, 1G, Moscow region, Russia, 141402",noWCF);
ws.addCell(wc);



//表格主体循环打入数据

for(int i=0;i<invoiceList.size();i++){
wc = ws.getWritableCell(0, 18+i);
wc = ExcelFormatter.cloneCellWithValue(0, 18+i, invoiceList.get(i).getBoxno(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(1, 18+i);
wc = ExcelFormatter.cloneCellWithValue(1, 18+i, invoiceList.get(i).getOrderno(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(2, 18+i);
wc = ExcelFormatter.cloneCellWithValue(2, 18+i, invoiceList.get(i).getCo(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(3, 18+i);
wc = ExcelFormatter.cloneCellWithValue(3, 18+i, invoiceList.get(i).getPartno(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(4, 18+i);
wc = ExcelFormatter.cloneCellWithValue(4, 18+i, invoiceList.get(i).getSsrelationship(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(5, 18+i);
wc = ExcelFormatter.cloneCellWithValue(5, 18+i, invoiceList.get(i).getDsction(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(6, 18+i);
wc = ExcelFormatter.cloneCellWithValue(6, 18+i, invoiceList.get(i).getQty(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(7, 18+i);
wc = ExcelFormatter.cloneCellWithValue(7, 18+i, invoiceList.get(i).getUnitprice(),wcf);
ws.addCell(wc);
wc = ws.getWritableCell(8, 18+i);
wc = ExcelFormatter.cloneCellWithValue(8, 18+i, invoiceList.get(i).getAmout(),wcf);
ws.addCell(wc);


}
wwb.write();
// 关闭文件
wwb.close();
System.out.println("导出成功");
} catch (Exception e) {
System.out.println("导出失败");
e.printStackTrace();
}

}

}


/**
* 验证输入的数据格式转换
* @param col
* @param row
* @param value
* @param wcFormat
* @return
*/
public static WritableCell cloneCellWithValue(int col, int row, Object value,WritableCellFormat wcFormat) {
WritableCell wc = null;
// 判断数据是否为STRING类型,是用LABLE形式插入,否则用NUMBER形式插入
if (value == null) {
wc = new jxl.write.Blank(col, row,wcFormat);
} else if (value instanceof String) {
jxl.write.Label label = new jxl.write.Label(col, row,
value.toString(),wcFormat);
wc = label;
} else {
wc = new jxl.write.Number(col, row,
new Double(value.toString()).doubleValue(),wcFormat);
}
return wc;
}


	public static WritableCellFormat getWritableCellFormatCellFormat(){

WritableCellFormat wcf = new WritableCellFormat();

try {
// 设置居中
wcf.setAlignment(Alignment.CENTRE);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


return wcf;
}
}



PS:项目中用到的一个关于jxl 读取excel模板的代码 公大家学习
相关标签: Excel WCF OS