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模板的代码 公大家学习
下一篇: jxl读excel