jxl操作excel
/** * 分隔符 */ private final static String SEPARATOR = "|"; /** * 由List导出至指定的Sheet,带total行(最后一行) * @param wb 模板的workbook * @param sheetNum 第几个表单 * @param targetFilePath 生成文件夹路径 * @param l 内容list集合,以|分割的对象string集合 * @param headInfoRows 头信息的行数 * @param columnsLength 列数 * @param remarkRowNumber 备注所在行 * @param remark 备注 * @return * @throws WriteException * @throws IOException * int */ public static int exportExcelFromList(jxl.Workbook wb, int sheetNum, String targetFilePath, List<String> l, int headInfoRows, int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException { // 创建可写入的Excel工作薄对象 WritableWorkbook wwb = null; int writeCount = 0; // 单元格样式 // WritableFont bold = new // WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示 WritableCellFormat normalFormat = new WritableCellFormat( NumberFormats.TEXT); normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK); //设置字体; WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED); WritableCellFormat normalFormat_total = new WritableCellFormat( font); normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK); try { // 创建可写入的Excel工作薄对象 wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb); WritableSheet ws = wwb.getSheet(0); Label cellRemark = new Label(0, remarkRowNumber, remark, normalFormat); ws.addCell(cellRemark); int row = l.size(); int columns = columnsLength; String[] ary = new String[120]; for (int i = 0; i < row; i++) { ary = l.get(i).split("\" + SEPARATOR); for (int j = 0; j < columns; j++) { if(i==row-1) { Label cell = new Label(j, i + headInfoRows, ary[j], normalFormat_total); ws.addCell(cell); }else { Label cell = new Label(j, i + headInfoRows, ary[j], normalFormat); ws.addCell(cell); } } writeCount++; } wwb.write(); } catch (Exception ex) { ex.printStackTrace(); } finally { if (wwb != null) { wwb.close(); } } return writeCount; } /** * 导出不需要合计行 * @param wb * @param sheetNum * @param targetFilePath * @param l * @param headInfoRows * @param columnsLength * @param remarkRowNumber * @param remark * @return * @throws WriteException * @throws IOException */ public static int exportExcelFromListNoTotal(jxl.Workbook wb, int sheetNum, String targetFilePath, List<String> l, int headInfoRows, int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException { // 创建可写入的Excel工作薄对象 WritableWorkbook wwb = null; int writeCount = 0; // 单元格样式 // WritableFont bold = new // WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示 WritableCellFormat normalFormat = new WritableCellFormat( NumberFormats.TEXT); normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK); //设置字体; WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK); WritableCellFormat normalFormat_total = new WritableCellFormat( font); normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK); try { // 创建可写入的Excel工作薄对象 wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb); WritableSheet ws = wwb.getSheet(0); Label cellRemark = new Label(0, remarkRowNumber, remark, normalFormat); ws.addCell(cellRemark); int row = l.size(); int columns = columnsLength; String[] ary = new String[120]; for (int i = 0; i < row; i++) { ary = l.get(i).split("\" + SEPARATOR); for (int j = 0; j < columns; j++) { Label cell = new Label(j, i + headInfoRows, ary[j], normalFormat); ws.addCell(cell); } writeCount++; } wwb.write(); } catch (Exception ex) { ex.printStackTrace(); } finally { if (wwb != null) { wwb.close(); } } return writeCount; }
exportList为List<String>，生成方式为遍历每个对象并将所有属性以|串起来
List<DetectorHistory> dfList = service.getList(); //获取对象集合 List<String> exportList = new ArrayList<String>(); StringBuffer sbList = new StringBuffer(); if (dfList!=null&&dfList.size()>0) { for (DetectorHistory ele:dfList) { sbList.delete(0, sbList.length());
//加入|
//加入属性
... exportList.add(sbList.toString()); } }
controller层
//模板所在文件夹路径 String tempPath = req.getSession().getServletContext() .getRealPath(CommonValue.FileTemplatePath); //生成文件所在文件夹路径 String exportFilePath = req.getSession().getServletContext() .getRealPath(CommonValue.ExportFilePath); //导出文件名 String exportFileName = ""; //模板文件名,事先生成好以及头文件情况 String targetFileName = "ReportTmp_detectorHistory.xls"; //生成的行数 int operatorCount = 0; JSONObject jsonObject = new JSONObject(); try { // 文件导出 if (exportList.size() > 0) { //生成文件名 exportFileName = "detectorHistory" + CommonTool.getNowDateStr2() + "." + targetFileName.split("\.")[1]; //利用模板生成Workbook Workbook rw = jxl.Workbook.getWorkbook(new File(tempPath + File.separator + targetFileName)); // 写入备注文件 String remarkInfo = "统计时间:" + CommonTool.getNowDateStr2() + " 金额单位:元"; //rw为模板workbook，0为sheetnum，其次为导出文件路径，exportList为|分割属性的string对象集合,4为头的行数,14为列数,1为备注所在行(从0开始),remarkinfo为备注所在行的信息 operatorCount = ExcelHelper_ChargeSituation.exportExcelFromListNoTotal(rw, 0, exportFilePath + File.separator + exportFileName, exportList, 4, 14, 1, remarkInfo); } jsonObject.put("operatorCount", operatorCount); //返回操作条数 jsonObject.put("exportFilePath", CommonValue.ExportFilePath + File.separator + exportFileName); //返回生成的文件路径 if(jsonObject.get("operatorCount")!=null&&Integer.valueOf(jsonObject.get("operatorCount").toString())<=0) {//当生成内容条数为0时 jsonObject.put("rtnCode", "404"); }else { jsonObject.put("rtnCode", "0"); } } catch (BiffException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); }finally{ } res.resetBuffer(); res.setContentType("text/html;charset=UTF-8"); res.getOutputStream().write(jsonObject.toString().getBytes("utf-8")); res.getOutputStream().flush(); return null;