jxl读excel模板
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 blocke.printStackTrace();} return wcf;}}
PS:项目中用到的一个关于jxl 读取excel模板的代码 公大家学习
页:
[1]