public void putHeaderDefault( WritableCellFormat cellFormat, WritableSheet sheet, Object value, int row, int column) throws RowsExceededException, WriteException { sheet.setColumnView(column, 20); Label label = new Label(column, row, "" + value, cellFormat); sheet.addCell(label); }
public static void addRowTop(WritableSheet ws) throws Exception { WritableFont Bwf = new WritableFont( WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体:Arial,大小为18号,粗体,非斜体 Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色 // 创建单元格格式: jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf); CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐 CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐 CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色--也可以设置其他颜色) CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线 CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线 List<Label> labelList = new ArrayList<Label>(); labelList.add(new Label(0, 0, "商品名称", CwcfF)); // labelList.add(new Label(1, 0, "单位", CwcfF)); // labelList.add(new Label(2, 0, "销售数量", CwcfF)); // labelList.add(new Label(3, 0, "销售成本", CwcfF)); labelList.add(new Label(4, 0, "销售金额", CwcfF)); labelList.add(new Label(5, 0, "利润", CwcfF)); labelList.add(new Label(6, 0, "利润率", CwcfF)); for (int j = 0; j < labelList.size(); j++) { ws.addCell(labelList.get(j)); } for (int i = 0; i < ws.getColumns(); i++) { Cell cell = ws.getCell(i, 0); ws.setColumnView(i, cell.getContents().length() * 4); } // ws.setRowView(0, 80*4); }
public String exportReaderBorrowedExcel(String rootDir, ReaderView readerView) { List<Reader> readers = new ArrayList<Reader>(); WritableWorkbook ww; String fileName = "upload" + File.separator + "readerBorrowedsStatics.xls"; File file = new File(rootDir + fileName); try { readers = readerDao.selectReaders(readerView); ww = Workbook.createWorkbook(file); WritableSheet ws = ww.createSheet("读者借阅信息统计", 0); ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "读者借阅信息统计", ExcelStyle.getHeaderStyle()); ExcelOperate.addLabelToSheet(ws, 0, 1, "借阅证号", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 1, 1, "条形码", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 2, 1, "读者姓名", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 3, 1, "姓别", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 4, 1, "当前借阅数量", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 5, 1, "累计借阅数量", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 6, 1, "读者单位", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 7, 1, "读者类别", ExcelStyle.getTitleStyle()); int count = 2; for (Reader reader : readers) { ExcelOperate.addLabelToSheet( ws, 0, count, reader.getCardNo(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 1, count, reader.getBarCode(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 2, count, reader.getReaderName(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet(ws, 3, count, reader.getSex(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 4, count, reader.getBorrowedQuantiy(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 5, count, reader.getTotalBQuantity(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 6, count, reader.getReaderUnits().getUnitName(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 7, count, reader.getReaderType().getReaderCateName(), ExcelStyle.getContentStyle()); count++; } for (int i = 0; i < 8; i++) { ws.setColumnView(i, 16); } ws.setRowView(0, 20); ww.write(); ww.close(); System.out.println("写入excel成功!"); } catch (Exception e) { System.out.println("写入excel失败!"); e.printStackTrace(); } return fileName; }
/** @param ws @MethodName : setColumnAutoSize @Description : 设置工作表自动列宽和首行加粗 */ private static void setColumnAutoSize(WritableSheet ws, int extraWith) { // 获取本列的最宽单元格的宽度 for (int i = 0; i < ws.getColumns(); i++) { int colWith = 0; for (int j = 0; j < ws.getRows(); j++) { String content = ws.getCell(i, j).getContents().toString(); int cellWith = content.length(); if (colWith < cellWith) { colWith = cellWith; } } // 设置单元格的宽度为最宽宽度+额外宽度 ws.setColumnView(i, colWith + extraWith); } }
public void executeLast(Hashtable _tagLibrary, Hashtable _beanLibrary) { try { bean _sysPdfCC = (bean) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_CurrentCELL); bean _sysPdfCR = (bean) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_CurrentROW); int X = 0; try { X = ((Integer) _sysPdfCC.getContent()).intValue(); } catch (Exception e) { } int Y = 0; try { Y = ((Integer) _sysPdfCR.getContent()).intValue(); } catch (Exception e) { } WritableSheet document = (WritableSheet) (((report_element_base) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_Document)) .getContent()); ((java.util.Vector) (((report_element_base) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_Canvas)) .getContent())) .add(this.getCellC(document.getWritableCell(X, Y), X, Y)); try { if (!internal_style.getWIDTH().equals("")) document.setColumnView(X, new Integer(internal_style.getWIDTH()).intValue()); } catch (Exception e) { } if (!internal_style.getHEIGHT().equals("") && parent != null && parent instanceof table_row) ((table_row) parent).setHEIGHT(internal_style.getHEIGHT()); X++; _sysPdfCC.setContent(new Integer(X)); _beanLibrary.put(_sysPdfCC.getName() + ":" + _sysPdfCC.getID(), _sysPdfCC); if (_tagLibrary.get(getName() + ":" + getID()) == null) _tagLibrary.remove(getName() + ":" + getID() + "_ids_" + this.motore.hashCode()); else _tagLibrary.remove(getName() + ":" + getID()); } catch (Exception e) { setError(e, iStub.log_WARN); } }
public String exportExcel(String rootDir, SysLogView sysLogView) { List<SysLog> sysLogs = new ArrayList<SysLog>(); WritableWorkbook ww; String fileName = "upload" + File.separator + "sysLogs.xls"; File file = new File(rootDir + fileName); try { sysLogs = sysLogDao.selectSysLogs(sysLogView); ww = Workbook.createWorkbook(file); WritableSheet ws = ww.createSheet("日志信息", 0); ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "日志信息", ExcelStyle.getHeaderStyle()); ExcelOperate.addLabelToSheet(ws, 0, 1, "操作类型", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 1, 1, "事件", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 2, 1, "操作人", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 3, 1, "日期", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 4, 1, "ip地址", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 5, 1, "业务描述", ExcelStyle.getTitleStyle()); int count = 2; for (SysLog sysLog : sysLogs) { ExcelOperate.addLabelToSheet( ws, 0, count, sysLog.getOperateType(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 1, count, sysLog.getFuncName(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 2, count, sysLog.getOperator(), ExcelStyle.getContentStyle()); String operateDate = DateFormatUtil.format(sysLog.getOperateDate(), "yyyy-MM-dd"); ExcelOperate.addLabelToSheet( ws, 3, count, DateFormatUtil.convertToDate(operateDate), ExcelStyle.getDateStyle()); ExcelOperate.addLabelToSheet( ws, 4, count, sysLog.getOperateIPAddress(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 5, count, sysLog.getOperateDescription(), ExcelStyle.getContentStyle()); count++; } for (int i = 0; i < 6; i++) { ws.setColumnView(i, 16); } ws.setRowView(0, 20); ww.write(); ww.close(); System.out.println("写入excel成功!"); } catch (Exception e) { System.out.println("写入excel失败!"); e.printStackTrace(); } return fileName; }
/** * * * <DL> * <DT>メソッド記述: * <DD><br> * </DL> * * @param MCtlListVo , String * @return int * @throws BizException */ public boolean exportEXCEL(String strFilePath) throws BizException { try { List<MCtlVo> lstMCtlVo = null; /** データある場合、データ取得 */ lstMCtlVo = dao.exportEXCEL(); if (lstMCtlVo == null || lstMCtlVo.size() == 0) { return false; } WritableWorkbook workbook = Workbook.createWorkbook(new File(strFilePath)); WritableSheet s1 = workbook.createSheet("M_CTL", 0); FontName fontName = WritableFont.createFont("MS ゴシック"); WritableFont fontFormat = new WritableFont(fontName, 12); // header format (color, padding, border) WritableCellFormat headFormat = new WritableCellFormat(fontFormat); headFormat.setAlignment(Alignment.CENTRE); headFormat.setVerticalAlignment(VerticalAlignment.CENTRE); headFormat.setBorder(Border.ALL, BorderLineStyle.THIN); headFormat.setBackground(Colour.VERY_LIGHT_YELLOW); // left format WritableCellFormat leftCellFormat = new WritableCellFormat(fontFormat); leftCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); leftCellFormat.setAlignment(Alignment.LEFT); // center format WritableCellFormat centerCellFormat = new WritableCellFormat(fontFormat); centerCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); centerCellFormat.setAlignment(Alignment.CENTRE); // right format WritableCellFormat rightCellFormat = new WritableCellFormat(fontFormat); rightCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN); rightCellFormat.setAlignment(Alignment.RIGHT); final String[] excelHeader = { "USERID", "KEY", "NAME", "DATA", "HELP", "入力桁数", "入力桁数(小数桁)", "入力属性地", "メンテフラグ", "コントロールフラグ", "変更可否フラグ", "登録ユーザー名", "登録PC名", "登録日付", "登録時刻", "最終更新ユーザー名", "最終更新PC名", "最終更新日付", "最終更新時刻" }; int columnCount = excelHeader.length; for (int i = 0; i < columnCount; i++) { Label lbHeader = new Label(i, 0, excelHeader[i], headFormat); s1.addCell(lbHeader); if (i == 2 || i == 3) s1.setColumnView(i, 60); else if (i == 4) s1.setColumnView(i, 150); else s1.setColumnView(i, 20); } for (int i = 0; i < lstMCtlVo.size(); i++) { int column = 0; MCtlVo mCtlVo = (MCtlVo) lstMCtlVo.get(i); s1.addCell(new Label(column++, i + 1, mCtlVo.getUserid(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCKey(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCName(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCData(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCHelp(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCBm(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCDecbm(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCAttr(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getMtnFlg(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getCntFlg(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getUpdFlg(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getAddUserView(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getAddPc(), leftCellFormat)); s1.addCell( new Label( column++, i + 1, DateUtils.getDateWithSplitYobi(mCtlVo.getAddDate()), centerCellFormat)); s1.addCell( new Label( column++, i + 1, DateUtils.getTimeWithSplit(mCtlVo.getAddTime()), centerCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupUserView(), leftCellFormat)); s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupPc(), leftCellFormat)); s1.addCell( new Label( column++, i + 1, DateUtils.getDateWithSplitYobi(mCtlVo.getLastupDate()), centerCellFormat)); s1.addCell( new Label( column++, i + 1, DateUtils.getTimeWithSplit(mCtlVo.getLastupTime()), centerCellFormat)); } workbook.write(); workbook.close(); } catch (Exception e) { e.printStackTrace(); } return true; }
/** * 生成一张盘点表格 * * @return * @throws Exception */ public String createCheckingExcel() throws Exception { if (date == null || date.trim().equals("")) { date = DateTool.getInstance().DateToPattern1(new Date()); } warehouses = this.warehouseService.findByKeyword(""); if (warehouse != null && warehouse.getId() != 0) { warehouse = this.warehouseService.get(Warehouse.class, warehouse.getId()); stockCheckingList = this.stockCheckingService.findCheckingListByDateAndWarehouse(date, warehouse.getId()); } else { stockCheckingList = this.stockCheckingService.findCheckingListByDate(date); } ByteOutputStream outputStream = new ByteOutputStream(); WritableWorkbook workbook = Workbook.createWorkbook(outputStream); WritableSheet sheet = workbook.createSheet("盘点记录表", 0); int row = 0; { Label label01 = new Label(0, row, "仓库"); sheet.addCell(label01); if (warehouse.getId() != 0) { Label label02 = new Label(1, row, warehouse.getWnickname()); sheet.addCell(label02); } else { Label label02 = new Label(1, row, "全部"); sheet.addCell(label02); } } row++; { Label label01 = new Label(0, row, "行"); Label label02 = new Label(1, row, "产品编码*"); Label label03 = new Label(2, row, "产品型号*"); Label label04 = new Label(3, row, "盘点前数量"); Label label05 = new Label(4, row, "盘点数量"); Label label06 = new Label(5, row, "差额"); Label label07 = new Label(6, row, "盘点人"); Label label08 = new Label(7, row, "时间"); sheet.addCell(label01); sheet.addCell(label02); sheet.addCell(label03); sheet.addCell(label04); sheet.addCell(label05); sheet.addCell(label06); sheet.addCell(label07); sheet.addCell(label08); } row++; for (int i = 0; i < stockCheckingList.size(); i++) { StockChecking checking = stockCheckingList.get(i); Label label01 = new Label(0, row, "" + (i + 1)); Label label02 = new Label(1, row, checking.getProductInfo().getBarcode()); Label label03 = new Label(2, row, checking.getProductInfo().getPdesc()); jxl.write.Number label04 = new jxl.write.Number(3, row, checking.getQuantity_before()); jxl.write.Number label05 = new jxl.write.Number(4, row, checking.getQuantity_after()); jxl.write.Number label06 = new jxl.write.Number( 5, row, checking.getQuantity_after() - checking.getQuantity_before()); Label label07 = new Label(6, row, checking.getOperator()); Label label08 = new Label(7, row, checking.getUpdatetime()); sheet.addCell(label01); sheet.addCell(label02); sheet.addCell(label03); sheet.addCell(label04); sheet.addCell(label05); sheet.addCell(label06); sheet.addCell(label07); sheet.addCell(label08); row++; } for (int i = 0; i < sheet.getColumns(); i++) { sheet.setColumnView(i, 30); } for (int i = 0; i < sheet.getRows(); i++) { sheet.setRowView(i, 300); } workbook.write(); workbook.close(); response.reset(); response.setContentType("application/vn.ms-xls"); response.setCharacterEncoding("utf-8"); this.inputStream = new ByteArrayInputStream(outputStream.getBytes()); // 关键,以 inputstream 输出 return SUCCESS; }
public void ExportExcel(HttpServletResponse response, String filename, List<MemberLevel> items) { boolean b = false; OutputStream os = null; WritableWorkbook wbook = null; try { os = response.getOutputStream(); // 取得输出流 response.reset(); // 清空输出流 response.setHeader( "Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode(filename, "UTF-8") + ".xls"); // 设定输出文件头 response.setContentType("application/msexcel"); // 定义输出类型 wbook = Workbook.createWorkbook(os); // 建立excel文件 String tmptitle = "会员级别"; // 标题 WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称 // 设置excel标题 WritableFont wfont = new WritableFont( WritableFont.ARIAL, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat wcfFC = new WritableCellFormat(wfont); wcfFC.setBackground(Colour.AQUA); wsheet.addCell(new Label(1, 0, tmptitle, wcfFC)); wfont = new jxl.write.WritableFont( WritableFont.ARIAL, 15, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); wcfFC = new WritableCellFormat(wfont); int r = 0; wsheet.setColumnView(r, 10); // 设置列的宽度 wsheet.setColumnView(r + 1, 13); // 设置列的宽度 wsheet.setColumnView(r + 2, 12); // 设置列的宽度 wsheet.setColumnView(r + 3, 12); // 设置列的宽度 wsheet.setColumnView(r + 4, 15); // 设置列的宽度 wsheet.setColumnView(r + 5, 15); // 设置列的宽度 wsheet.setColumnView(r + 6, 12); // 设置列的宽度 wsheet.setColumnView(r + 7, 12); // 设置列的宽度 wsheet.setColumnView(r + 8, 12); // 设置列的宽度 wsheet.setColumnView(r + 9, 12); // 设置列的宽度 wsheet.setColumnView(r + 10, 12); // 设置列的宽度 wsheet.setColumnView(r + 11, 12); // 设置列的宽度 wsheet.setColumnView(r + 12, 12); // 设置列的宽度 int f = 1; // from this row write data r = 0; // 开始生成主体内容 会员号 姓名 联系电话 会员等级 降级期? 总积分 产品积分 变更时间 前一 后一 后二 后三 后四 后五 wsheet.addCell(new Label(r, f, "姓名")); wsheet.addCell(new Label(r + 1, f, "会员号")); // wsheet.addCell(new Label(r+2, f, "性别")); wsheet.addCell(new Label(r + 2, f, "联系电话")); wsheet.addCell(new Label(r + 3, f, "会员等级")); wsheet.addCell(new Label(r + 4, f, "总积分")); wsheet.addCell(new Label(r + 5, f, "产品积分")); wsheet.addCell(new Label(r + 6, f, "变更时间")); wsheet.addCell(new Label(r + 7, f, "前一")); wsheet.addCell(new Label(r + 8, f, "后一")); wsheet.addCell(new Label(r + 9, f, "后二")); wsheet.addCell(new Label(r + 10, f, "后三")); wsheet.addCell(new Label(r + 11, f, "后四")); wsheet.addCell(new Label(r + 12, f, "后五")); f++; r = 0; int row = 0; for (MemberLevel item : items) { wsheet.addCell(new Label(r, row + f, item.getRealName())); // 数据库的城市名字段 wsheet.addCell(new Label(r + 1, row + f, item.getMemberId())); // 数据库的城市名字段 wsheet.addCell(new Label(r + 2, row + f, item.getPhoneNo())); // 数据库的城市名字段 wsheet.addCell(new Label(r + 3, row + f, item.getLevelName())); wsheet.addCell(new Label(r + 4, row + f, item.getTotalScore() + "")); wsheet.addCell(new Label(r + 5, row + f, item.getSnScore() + "")); wsheet.addCell(new Label(r + 6, row + f, item.getUpdateTime())); wsheet.addCell(new Label(r + 7, row + f, item.getScore1() + "")); wsheet.addCell(new Label(r + 8, row + f, item.getScore2() + "")); wsheet.addCell(new Label(r + 9, row + f, item.getScore3() + "")); wsheet.addCell(new Label(r + 10, row + f, item.getScore4() + "")); wsheet.addCell(new Label(r + 11, row + f, item.getScore5() + "")); wsheet.addCell(new Label(r + 12, row + f, item.getScore6() + "")); row++; } // 主体内容生成结束 wbook.write(); // 写入文件 wbook.close(); os.close(); // 关闭流 b = true; } catch (java.net.SocketException exc) { exc.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (wbook != null) wbook.close(); if (os != null) os.close(); // 关闭流 } catch (Exception ex) { } } }
public static void export( String field, String value, String start, String end, TaodianApi api, OutputStream os) { try { WritableWorkbook wbook = Workbook.createWorkbook(os); WritableSheet wsheet = wbook.createSheet("访问详情", 0); WritableFont wfont = new WritableFont( WritableFont.ARIAL, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat wcfFC = new WritableCellFormat(wfont); wcfFC.setBackground(Colour.AQUA); // wsheet.addCell(new Label(1, 0, tmptitle, wcfFC)); wfont = new jxl.write.WritableFont( WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); wcfFC = new WritableCellFormat(wfont); int row = 0; wsheet.addCell(new Label(0, row, "短网址")); wsheet.setColumnView(0, 10); wsheet.addCell(new Label(1, row, "商家")); wsheet.setColumnView(1, 12); wsheet.addCell(new Label(2, row, "商品")); wsheet.setColumnView(2, 12); wsheet.addCell(new Label(3, row, "推广者")); wsheet.setColumnView(3, 6); wsheet.addCell(new Label(4, row, "点击单价")); wsheet.setColumnView(4, 7); wsheet.addCell(new Label(5, row, "访问IP")); wsheet.setColumnView(5, 14); wsheet.addCell(new Label(6, row, "访问设备")); wsheet.setColumnView(6, 8); wsheet.addCell(new Label(7, row, "浏览器")); wsheet.setColumnView(7, 8); wsheet.addCell(new Label(8, row, "访问时间")); wsheet.setColumnView(8, 20); wsheet.addCell(new Label(9, row, "访问者ID")); wsheet.setColumnView(9, 12); wsheet.addCell(new Label(10, row, "访问来源")); wsheet.setColumnView(10, 80); wsheet.addCell(new Label(11, row, "Agent")); wsheet.setColumnView(11, 80); DataIterator data = new DataIterator(field, value, start, end, api); row++; while (data.hasNext()) { writeRow(wsheet, data.next(), row++); } wbook.write(); // 写入文件 wbook.close(); } catch (Exception e) { log.error(e, e); } }
public void createEmployeesFile(List<Employee> list, OutputStream os) throws Exception { WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件 WritableSheet wsheet = wbook.createSheet("Sheet1", 0); // 工作表名称 // 设置公司名 WritableFont companyfont = new WritableFont( WritableFont.createFont("宋体"), 18, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat companyFormat = new WritableCellFormat(companyfont); companyFormat.setAlignment(jxl.format.Alignment.CENTRE); companyFormat.setVerticalAlignment(VerticalAlignment.CENTRE); Label excelCompany = new Label(0, 0, "桐庐富伟针织有限公司员工花名册", companyFormat); wsheet.addCell(excelCompany); wsheet.mergeCells(0, 0, 13, 0); wsheet.setRowView(0, 800); // 设置Excel字体 WritableFont wfont = new WritableFont( WritableFont.createFont("宋体"), 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat titleFormat = new WritableCellFormat(wfont); titleFormat.setAlignment(jxl.format.Alignment.CENTRE); titleFormat.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // BorderLineStyle边框 WritableFont wfont2 = new WritableFont( WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat titleFormat2 = new WritableCellFormat(wfont2); titleFormat2.setAlignment(jxl.format.Alignment.CENTRE); titleFormat2.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // BorderLineStyle边框 String[] title = { "编号", "姓名", "性别", "入厂日期", "身份证号码", "联系方式", "岗位", "部门", "家庭住址", "现居住地", "合同期限", "用工形式", "离职时间", "时薪" }; // 设置Excel表头 int col = 0; int merge_col = 0; int columnBestWidth[] = new int[title.length + 1]; // 保存最佳列宽数据的数组 for (int i = 0; i < title.length; i++, col++) { columnBestWidth[col] = title[i].getBytes().length; Label excelTitle = new Label(col, 1, title[i], titleFormat); if (title[i].equals("合同期限")) { merge_col = col; col++; columnBestWidth[col] = title[i].getBytes().length; } wsheet.addCell(excelTitle); } wsheet.setRowView(1, 400); wsheet.mergeCells(merge_col, 1, merge_col + 1, 1); int c = 2; // 用于循环时Excel的行号 for (Employee employee : list) { wsheet.setRowView(c, 400); Label content1 = new Label(0, c, employee.getNumber(), titleFormat2); Label content2 = new Label(1, c, employee.getName(), titleFormat2); Label content3 = new Label(2, c, employee.getSex(), titleFormat2); Label content4 = new Label(3, c, DateTool.formatDateYMD(employee.getEnter_at()), titleFormat2); Label content5 = new Label(4, c, employee.getId_card(), titleFormat2); Label content6 = new Label(5, c, employee.getTel(), titleFormat2); Label content7 = new Label(6, c, employee.getJob(), titleFormat2); Label content8 = new Label(7, c, SystemCache.getDepartmentName(employee.getDepartmentId()), titleFormat2); Label content9 = new Label(8, c, employee.getAddress_home(), titleFormat2); Label content10 = new Label(9, c, employee.getAddress(), titleFormat2); Label content11 = new Label(10, c, DateTool.formatDateYMD(employee.getAgreement_at()), titleFormat2); Label content12 = new Label(11, c, DateTool.formatDateYMD(employee.getAgreement_end()), titleFormat2); Label content13 = new Label(12, c, employee.getEmployee_type(), titleFormat2); Label content14 = new Label(13, c, DateTool.formatDateYMD(employee.getLeave_at(), "/"), titleFormat2); Label content15 = new Label( 14, c, employee.getHour_salary() == null ? "" : String.valueOf(employee.getHour_salary()), titleFormat2); wsheet.addCell(content1); wsheet.addCell(content2); wsheet.addCell(content3); wsheet.addCell(content4); wsheet.addCell(content5); wsheet.addCell(content6); wsheet.addCell(content7); wsheet.addCell(content8); wsheet.addCell(content9); wsheet.addCell(content10); wsheet.addCell(content11); wsheet.addCell(content12); wsheet.addCell(content13); wsheet.addCell(content14); wsheet.addCell(content15); int width1 = content1.getContents().getBytes().length; int width2 = content2.getContents().getBytes().length; int width3 = content3.getContents().getBytes().length; int width4 = content4.getContents().getBytes().length; int width5 = content5.getContents().getBytes().length; int width6 = content6.getContents().getBytes().length; int width7 = content7.getContents().getBytes().length; int width8 = content8.getContents().getBytes().length; int width9 = content9.getContents().getBytes().length; int width10 = content10.getContents().getBytes().length; int width11 = content11.getContents().getBytes().length; int width12 = content12.getContents().getBytes().length; int width13 = content13.getContents().getBytes().length; int width14 = content14.getContents().getBytes().length; int width15 = content15.getContents().getBytes().length; if (columnBestWidth[0] < width1) { columnBestWidth[0] = width1; } if (columnBestWidth[1] < width2) { columnBestWidth[1] = width2; } if (columnBestWidth[2] < width3) { columnBestWidth[2] = width3; } if (columnBestWidth[3] < width4) { columnBestWidth[3] = width4; } if (columnBestWidth[4] < width5) { columnBestWidth[4] = width5; } if (columnBestWidth[5] < width6) { columnBestWidth[5] = width6; } if (columnBestWidth[6] < width7) { columnBestWidth[6] = width7; } if (columnBestWidth[7] < width8) { columnBestWidth[7] = width8; } if (columnBestWidth[8] < width9) { columnBestWidth[8] = width9; } if (columnBestWidth[9] < width10) { columnBestWidth[9] = width10; } if (columnBestWidth[10] < width11) { columnBestWidth[10] = width11; } if (columnBestWidth[11] < width12) { columnBestWidth[11] = width12; } if (columnBestWidth[12] < width13) { columnBestWidth[12] = width13; } if (columnBestWidth[13] < width14) { columnBestWidth[13] = width14; } if (columnBestWidth[14] < width15) { columnBestWidth[14] = width15; } c++; } for (int p = 0; p < columnBestWidth.length; ++p) { wsheet.setColumnView(p, columnBestWidth[p] + 1); } wbook.write(); // 写入文件 wbook.close(); os.close(); }
/** * Создание шапки с датами и марками топлива * * @param column - номер первой колонки * @param row - номер первой строки * @param sheet - лист, на котором будет записываться * @throws RowsExceededException * @throws WriteException */ private int cape(int column, int row, WritableSheet sheet) throws RowsExceededException, WriteException { /** * формат записи даты<br> * Пример 26.04.2013 г. */ SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy"); /** Список надписей под результатом */ String[] result = {"Изменение цен %", "Изменение цен руб."}; /* * Поле "Дата" */ sheet.addCell(new Label(column, row, "Дата", font.tahoma9ptBoldMedion)); sheet.mergeCells(column, row, column, row + 1); sheet.setColumnView(column, 15); /** Поле "Марка бензина" */ sheet.addCell(new Label(column + 1, row, "Марка бензина", font.tahoma9ptBoldMedion)); sheet.mergeCells(column + 1, row, column + 1, row + 1); sheet.setColumnView(column + 1, 10); /** первая строчка под наименование поставщика */ sheet.setRowView(row, 900); /** вторая строчка под пояснение выбранного значения */ sheet.setRowView(row + 1, 2000); /** Запись даты и марки топлива */ for (int i = 0; i < time.length; i++) { /** дата */ sheet.addCell( new Label( column, row + 2 + i * 7, formatter.format(new Date(time[i])) + " г.", font.tahoma9ptBoldMedion)); /** склеивание со строчками марки топлива */ sheet.mergeCells(column, row + 2 + i * 7, column, row + 2 + i * 7 + 6); /** запись марки топлива */ for (int p = 0; p < label_toplivo.length; p++) { sheet.addCell( new Label(column + 1, row + 2 + i * 7 + p, label_toplivo[p], font.tahomaLabelToplivo)); } } // для удобства int nextRow = row + 2 + 7 * time.length; /** Запись результата */ for (String res : result) { /** Титульник */ sheet.addCell(new Label(column, nextRow, res, font.tahoma9ptBoldMedion)); sheet.mergeCells(column, nextRow, column + 1, nextRow); /** марка топлива */ for (int p = 0; p < label_toplivo.length; p++) { sheet.addCell( new Label(column, nextRow + 1 + p, label_toplivo[p], font.tahoma9ptBoldMedion)); sheet.mergeCells(column, nextRow + 1 + p, column + 1, nextRow + 1 + p); } /** смещение к след пункту, без потери номера строки */ nextRow += label_toplivo.length + 1; } return column + 2; }
public void doFinish(IProgressMonitor monitor) { monitor.beginTask("Exporting ", rowCount); try { switch (ComboFile) { case 0: WritableWorkbook myWorkbook = null; String str = null; try { str = command.Command_Int("SELECT COUNT(*) FROM " + sCollection); } catch (Exception e) { e.printStackTrace(); } if (Integer.parseInt(str) > 50000) { int j = 0; int k = 0; int m = 0; while (k * 50000 < Integer.parseInt(str)) { if (Integer.parseInt(str) > 100000) myWorkbook = Workbook.createWorkbook(new File(sFullPath + "(" + m + ")" + ".xls")); else { myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls")); } int l = 0; for (; k * 50000 < Integer.parseInt(str); k++) { myWorkbook.createSheet("Sheet" + k, k); WritableSheet mySheet = myWorkbook.getSheet(l); String sImsi = ""; for (int i = 0; i < oColumns.size(); i++) { sImsi = oColumns.get(i); WritableCellFormat ColumnFormat = new WritableCellFormat(); ColumnFormat.setAlignment(Alignment.CENTRE); ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE); ColumnFormat.setBackground(Colour.GRAY_25); mySheet.setColumnView(i, (sImsi.trim().length() * 2)); jxl.write.Label oColumnLabel = null; oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat); mySheet.addCell(oColumnLabel); } sQuery = "selectattr " + sAttr + "'limit " + 50000 + "offset " + (j) * 50000 + "'"; ArrayList<String> oExcel = command.Command_Excel(sQuery); int nTotal = oExcel.size() / oColumns.size(); int nColumn = oColumns.size(); int nCount = 0; for (int nRow = 1; nRow <= nTotal; nRow++) { for (int nCol = 0; nCol < nColumn; nCol++) { jxl.write.Label numberLabels = null; numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++)); mySheet.addCell(numberLabels); } monitor.worked(1); } j++; l++; if (l == 2) { k++; break; } } m++; myWorkbook.write(); myWorkbook.close(); } } else if (Integer.parseInt(str) < 50000) { myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls")); WritableSheet mySheet = myWorkbook.createSheet(sCollection.replaceAll("/", "_"), 0); String sImsi = ""; for (int i = 0; i < oColumns.size(); i++) { sImsi = oColumns.get(i); WritableCellFormat ColumnFormat = new WritableCellFormat(); ColumnFormat.setAlignment(Alignment.CENTRE); ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE); ColumnFormat.setBackground(Colour.GRAY_25); mySheet.setColumnView(i, (sImsi.trim().length() * 2)); jxl.write.Label oColumnLabel = null; oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat); mySheet.addCell(oColumnLabel); } ArrayList<String> oExcel = command.Command_Excel(sQuery); int nTotal = oExcel.size() / oColumns.size(); int nColumn = oColumns.size(); int nCount = 0; for (int nRow = 1; nRow <= nTotal; nRow++) { for (int nCol = 0; nCol < nColumn; nCol++) { jxl.write.Label numberLabels = null; numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++)); mySheet.addCell(numberLabels); } monitor.worked(1); } myWorkbook.write(); myWorkbook.close(); } final int sheet = lblsheet; final int File; if ((lblsheet / 2) == 0) { File = 1; } else { File = (lblsheet / 2); } if (istrue == true) { getShell() .getDisplay() .syncExec( new Runnable() { public void run() { MessageDialog.openInformation( getShell(), "AMGA_Mangaer", "Success : Files have been saved[" + sFullPath + (page2.getComboFile() == 0 ? ".xls" : ".txt") + "]" + "\r\n" + "File : " + File + " Sheet : " + sheet); } }); } break; case 1: // Txt String sDelimited = ""; switch (ComboString) { case 0: sDelimited = "\t"; break; case 1: sDelimited = " "; break; case 2: sDelimited = ","; break; default: break; } String sTitle = ""; if (p1btnselection == true) { sQuery = "selectattr " + sAttr + " ' '"; for (int i = 0; i < oColumns.size(); i++) { sTitle = sTitle + oColumns.get(i) + sDelimited; } } else { sQuery = p1_1text; String Query = sQuery; ArrayList<String> ooColumn = new ArrayList<String>(); int nQuery = 0; // selectattr for (int i = 0; i < Query.length(); i++) { if (Query.charAt(i) == '\'') { nQuery = i; break; } } StringTokenizer st1 = new StringTokenizer(Query.substring(0, nQuery), " "); while (st1.hasMoreElements()) { String sTemp = (String) st1.nextElement(); if (sTemp.trim().charAt(0) == '/') { ooColumn.add(sTemp); } } for (int i = 0; i < ooColumn.size(); i++) { sTitle = sTitle + ooColumn.get(i).substring(page1.str.length() + 1) + sDelimited; } nColumnCount = ooColumn.size(); } ArrayList<String> oTxt = null; try { oTxt = command.Command_Txt(sQuery, sDelimited, nColumnCount); } catch (Exception e) { final String str1 = e.getLocalizedMessage(); getShell() .getDisplay() .syncExec( new Runnable() { public void run() { MessageDialog.openError(getShell(), "AMGA_Mangaer", str1); } }); istrue = false; } oTxt.add(0, sTitle); BufferedWriter out = new BufferedWriter(new FileWriter(sFullPath + ".txt")); for (int i = 0; i < oTxt.size(); i++) { out.write(oTxt.get(i)); out.newLine(); monitor.worked(1); } out.close(); if (istrue == true) { getShell() .getDisplay() .syncExec( new Runnable() { public void run() { MessageDialog.openInformation( getShell(), "AMGA_Mangaer", "Success : Files have been saved[" + sFullPath + (page2.getComboFile() == 0 ? ".xls" : ".txt") + "]"); } }); } break; default: break; } } catch (Exception e) { final String str = e.getLocalizedMessage(); getShell() .getDisplay() .syncExec( new Runnable() { public void run() { MessageDialog.openError(getShell(), "AMGA_Mangaer", str); } }); } }
public String exportExcel(String rootDir, ReaderView readerView) { List<Reader> readers = new ArrayList<Reader>(); WritableWorkbook ww; String fileName = "upload" + File.separator + "readers.xls"; File file = new File(rootDir + fileName); try { readers = readerDao.selectReaders(readerView); ww = Workbook.createWorkbook(file); WritableSheet ws = ww.createSheet("读者信息", 0); ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "读者信息", ExcelStyle.getHeaderStyle()); ExcelOperate.addLabelToSheet(ws, 0, 1, "借阅证号", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 1, 1, "密码", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 2, 1, "条形码", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 3, 1, "读者姓名", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 4, 1, "出生日期", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 5, 1, "姓别", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 6, 1, "邮箱", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 7, 1, "联系电话", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 8, 1, "余额", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 9, 1, "拼音", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 10, 1, "办证日期", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 11, 1, "有效日期", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 12, 1, "当前借阅数量", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 13, 1, "累计借阅数量", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 14, 1, "读者描述", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 15, 1, "读者单位", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 16, 1, "读者类别", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 17, 1, "证件类别", ExcelStyle.getTitleStyle()); ExcelOperate.addLabelToSheet(ws, 18, 1, "借阅证状态", ExcelStyle.getTitleStyle()); int count = 2; for (Reader reader : readers) { ExcelOperate.addLabelToSheet( ws, 0, count, reader.getCardNo(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 1, count, reader.getPassword(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 2, count, reader.getBarCode(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 3, count, reader.getReaderName(), ExcelStyle.getContentStyle()); String birthday = DateFormatUtil.format(reader.getBirthday(), "yyyy-MM-dd"); ExcelOperate.addLabelToSheet( ws, 4, count, DateFormatUtil.convertToDate(birthday), ExcelStyle.getDateStyle()); // ExcelOperate.addLabelToSheet(ws, 4, count,reader.getBirthday(), // ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet(ws, 5, count, reader.getSex(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet(ws, 6, count, reader.getEmail(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 7, count, reader.getContactTel(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 8, count, reader.getLeftMoney(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet(ws, 9, count, reader.getSpell(), ExcelStyle.getContentStyle()); String entyDate = DateFormatUtil.format(reader.getEntyDate(), "yyyy-MM-dd"); ExcelOperate.addLabelToSheet( ws, 10, count, DateFormatUtil.convertToDate(entyDate), ExcelStyle.getDateStyle()); // ExcelOperate.addLabelToSheet(ws, 10, count,reader.getEntyDate(), // ExcelStyle.getContentStyle()); String effectiveDate = DateFormatUtil.format(reader.getEffectiveDate(), "yyyy-MM-dd"); ExcelOperate.addLabelToSheet( ws, 11, count, DateFormatUtil.convertToDate(effectiveDate), ExcelStyle.getDateStyle()); // ExcelOperate.addLabelToSheet(ws, 11, // count,reader.getEffectiveDate(),ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 12, count, reader.getBorrowedQuantiy(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 13, count, reader.getTotalBQuantity(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 14, count, reader.getReaderDesc(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 15, count, reader.getReaderUnits().getUnitName(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 16, count, reader.getReaderType().getReaderCateName(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 17, count, reader.getCertificate().getItemName(), ExcelStyle.getContentStyle()); ExcelOperate.addLabelToSheet( ws, 18, count, reader.getCardState().getItemName(), ExcelStyle.getContentStyle()); count++; } for (int i = 0; i < 20; i++) { ws.setColumnView(i, 16); } ws.setRowView(0, 20); ww.write(); ww.close(); System.out.println("写入excel成功!"); } catch (Exception e) { System.out.println("写入excel失败!"); e.printStackTrace(); } return fileName; }
public static void main(String args[]) { try { // 打开文件 WritableWorkbook book = Workbook.createWorkbook(new File("/home/uc/local/sandy/测试.xls")); // 生成名为“第一页”的工作表,参数0表示这是第一页 WritableSheet sheetOne = book.createSheet("第一页", 0); /** 定义单元格样式 */ WritableFont wf_title = new WritableFont( WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_head = new WritableFont( WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableFont wf_table = new WritableFont( WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色 WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义 wcf_title.setBackground(jxl.format.Colour.WHITE); // 设置单元格的背景颜色 wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 wcf_title.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框 WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义 wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色 wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 wcf_title1.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框 WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); // 单元格定义 wcf_title2.setBackground(jxl.format.Colour.YELLOW2); // 设置单元格的背景颜色 wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 wcf_title2.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框 WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head); wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN); wcf_head1.setAlignment(jxl.format.Alignment.CENTRE); wcf_head1.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); WritableCellFormat wcf_head2 = new WritableCellFormat(wf_head); wcf_head2.setBackground(jxl.format.Colour.YELLOW2); wcf_head2.setAlignment(jxl.format.Alignment.CENTRE); wcf_head2.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table); wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN); wcf_table1.setAlignment(jxl.format.Alignment.CENTRE); wcf_table1.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); WritableCellFormat wcf_table2 = new WritableCellFormat(wf_table); wcf_table2.setBackground(jxl.format.Colour.YELLOW2); wcf_table2.setAlignment(jxl.format.Alignment.CENTRE); wcf_table2.setBorder( jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); sheetOne.setColumnView(0, 15); // 设置列的宽度 sheetOne.setColumnView(1, 15); // 设置列的宽度 sheetOne.setColumnView(2, 15); // 设置列的宽度 sheetOne.setColumnView(3, 15); // 设置列的宽度 sheetOne.setColumnView(4, 15); // 设置列的宽度 sheetOne.setColumnView(5, 15); // 设置列的宽度 sheetOne.setColumnView(6, 15); // 设置列的宽度 sheetOne.setColumnView(7, 15); // 设置列的宽度 sheetOne.setColumnView(8, 15); // 设置列的宽度 sheetOne.setColumnView(9, 15); // 设置列的宽度 sheetOne.setColumnView(10, 15); // 设置列的宽度 sheetOne.setColumnView(11, 15); // 设置列的宽度 sheetOne.setColumnView(12, 15); // 设置列的宽度 sheetOne.setColumnView(13, 15); // 设置列的宽度 // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0) // 以及单元格内容为test Label title = new Label(0, 0, "统计", wcf_title); Label titleOne = new Label(0, 1, "统计1", wcf_title1); Label titleTwo = new Label(2, 1, "统计2", wcf_title2); Label column1 = new Label(0, 2, "姓名", wcf_head1); Label column2 = new Label(1, 2, "所选课程", wcf_head1); Label column3 = new Label(2, 2, "姓名", wcf_head2); Label column4 = new Label(3, 2, "所选课程", wcf_head2); // 或者WritableCell cell = new jxl.write.Number(column, row, value, wcf) // 将定义好的单元格添加到工作表中 sheetOne.addCell(title); sheetOne.addCell(titleOne); sheetOne.addCell(titleTwo); sheetOne.addCell(column1); sheetOne.addCell(column2); sheetOne.addCell(column3); sheetOne.addCell(column4); // 合: 第1列第1行 到 第13列第1行 sheetOne.mergeCells(0, 0, 3, 0); sheetOne.mergeCells(0, 1, 1, 1); sheetOne.mergeCells(2, 1, 3, 1); /*动态数据 */ Label content1 = new Label(0, 3, "张三", wcf_table1); Label content2 = new Label(0, 4, "张三", wcf_table1); Label content3 = new Label(0, 5, "张三", wcf_table1); Label kecheg1 = new Label(1, 3, "语文", wcf_table1); Label kecheg2 = new Label(1, 4, "数学", wcf_table1); Label kecheg3 = new Label(1, 5, "英语", wcf_table1); sheetOne.addCell(content1); sheetOne.addCell(content2); sheetOne.addCell(content3); sheetOne.addCell(kecheg1); sheetOne.addCell(kecheg2); sheetOne.addCell(kecheg3); sheetOne.mergeCells(0, 3, 0, 2 + 3); Label content11 = new Label(2, 3, "李四", wcf_table2); Label content22 = new Label(2, 4, "李四", wcf_table2); Label content33 = new Label(2, 5, "李四", wcf_table2); Label kecheg11 = new Label(3, 3, "语文", wcf_table2); Label kecheg22 = new Label(3, 4, "数学", wcf_table2); Label kecheg33 = new Label(3, 5, "英语", wcf_table2); sheetOne.addCell(content11); sheetOne.addCell(content22); sheetOne.addCell(content33); sheetOne.addCell(kecheg11); sheetOne.addCell(kecheg22); sheetOne.addCell(kecheg33); sheetOne.mergeCells(2, 3, 2, 2 + 3); // 写入数据并关闭文件 book.write(); book.close(); } catch (Exception e) { System.out.println(e); } }
public static Map<String, Object> exportCommunicationEvent( DispatchContext dctx, Map<String, ?> context) { Delegator delegator = dctx.getDelegator(); String outpath = (String) context.get("outpath"); // mandatory List<String> results = FastList.newInstance(); String commEventId = null; if (UtilValidate.isNotEmpty(outpath)) { File outdir = new File(outpath); if (!outdir.exists()) { outdir.mkdir(); } if (outdir.isDirectory() && outdir.canWrite()) { String entityName = "CommunicationEvent"; long numberWritten = 0; EntityListIterator values = null; WritableWorkbook workbook = null; List conditionExprs = FastList.newInstance(); conditionExprs.add( EntityCondition.makeCondition( "communicationEventTypeId", EntityOperator.EQUALS, "EMAIL_COMMUNICATION")); conditionExprs.add( EntityCondition.makeCondition( "contactMechTypeId", EntityOperator.EQUALS, "EMAIL_ADDRESS")); List orExprs = FastList.newInstance(); String cntctUsSbjStr = "%Contact us%".toUpperCase(); String reqCatSbjStr = "%Request Catalog%".toUpperCase(); orExprs.add( EntityCondition.makeCondition( EntityFunction.UPPER_FIELD("subject"), EntityOperator.LIKE, cntctUsSbjStr)); orExprs.add( EntityCondition.makeCondition( EntityFunction.UPPER_FIELD("subject"), EntityOperator.LIKE, reqCatSbjStr)); conditionExprs.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR)); EntityConditionList whereEntityCondition = EntityCondition.makeCondition(conditionExprs, EntityOperator.AND); try { boolean beganTx = TransactionUtil.begin(); try { values = delegator.find( entityName, whereEntityCondition, null, null, UtilMisc.toList("communicationEventId"), null); } catch (Exception entityEx) { results.add("error while process retrieving from db"); } GenericValue value = (GenericValue) values.next(); if (value != null) { PrintWriter writer = new PrintWriter( new BufferedWriter( new OutputStreamWriter( new FileOutputStream(new File(outdir, entityName + ".xml")), "UTF-8"))); writer.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"); writer.println("<entity-engine-xml>"); do { value.writeXmlText(writer, ""); numberWritten++; if (numberWritten % 500 == 0) { TransactionUtil.commit(beganTx); beganTx = TransactionUtil.begin(); } } while ((value = (GenericValue) values.next()) != null); writer.println("</entity-engine-xml>"); writer.close(); } else { results.add("No record found"); } values.close(); TransactionUtil.commit(beganTx); File file = new File(outdir, entityName + ".xls"); WorkbookSettings wbSettings = new WorkbookSettings(); wbSettings.setLocale(new Locale("en", "EN")); workbook = Workbook.createWorkbook(file, wbSettings); workbook.createSheet(entityName, 0); WritableSheet excelSheet = workbook.getSheet(0); int row = 0; WritableFont headerFont = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD); WritableCellFormat headerFormat = new WritableCellFormat(headerFont); CellView cv = new CellView(); cv.setAutosize(true); Label label = new Label(0, row, "ID", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(0, cv); label = new Label(1, row, "Date", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(1, cv); label = new Label(2, row, "Subject", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(2, cv); label = new Label(3, row, "Email Address", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(3, cv); label = new Label(4, row, "First Name", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(4, cv); label = new Label(5, row, "Last Name", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(5, cv); label = new Label(6, row, "Contact Email Address", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(6, cv); label = new Label(7, row, "Reason For Contact ", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(7, cv); label = new Label(8, row, "Address1", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(8, cv); label = new Label(9, row, "Address2", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(9, cv); label = new Label(10, row, "City State Zip", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(10, cv); label = new Label(11, row, "Phone", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(11, cv); label = new Label(12, row, "Order #", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(12, cv); label = new Label(13, row, "Comment", headerFormat); excelSheet.addCell(label); excelSheet.setColumnView(13, cv); // label = new Label(12, row, "Email Content Body", headerFormat); // excelSheet.addCell(label); // excelSheet.setColumnView(12, cv); Debug.logInfo("wrote header", module); row++; WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10); WritableCellFormat times = new WritableCellFormat(times10pt); String XmlFilePath = new File(outdir, entityName + ".xml").getAbsolutePath(); List<Map<Object, Object>> listMaps = getListMapsFromXmlFile(XmlFilePath); for (Map entityDataRow : listMaps) { String fname = "", lname = "", contactEmail = "", reasonOfContact = "", emailContentAddress = "", address1 = "", address2 = "", cityStateZip = "", phone = "", comment = "", orderNum = ""; commEventId = entityDataRow.get("communicationEventId").toString(); contactEmail = entityDataRow.get("fromString").toString(); // Debug.logInfo("Extracting column value for commEventId:" + commEventId, module); label = new Label(0, row, entityDataRow.get("communicationEventId").toString(), times); excelSheet.addCell(label); label = new Label(1, row, entityDataRow.get("createdTxStamp").toString(), times); excelSheet.addCell(label); label = new Label(2, row, entityDataRow.get("subject").toString(), times); excelSheet.addCell(label); label = new Label(3, row, entityDataRow.get("toString").toString(), times); excelSheet.addCell(label); String emailContent = entityDataRow.get("content").toString(); String emailContentBody = emailContent.substring( emailContent.indexOf("<body>") + "<body>".length(), emailContent.indexOf("</body>")); emailContentBody = emailContentBody .replaceAll(":()", "") .replaceAll(" ", " ") .replaceAll("@()", "@") .replaceAll(",", " ") .replaceAll(";", " "); try { if (emailContentBody.indexOf("First Name:") > -1) { int idxStart = emailContentBody.indexOf("First Name:") + "First Name:</td>".length(); fname = emailContentBody.substring( idxStart, emailContentBody.indexOf("</td>", idxStart)); fname = fname.substring(fname.lastIndexOf(">") + 1); fname.trim(); } else { if (emailContentBody.indexOf(">First Name") > -1) { int idxStart = emailContentBody.indexOf(">First Name") + ">First Name".length(); int idxEnd = emailContentBody.indexOf("</p>", emailContentBody.indexOf(">First Name")); fname = emailContentBody.substring(idxStart, idxEnd); if (fname.indexOf("<span class=\"data\">") > -1) { fname = fname.substring(fname.indexOf("\">") + 2, fname.indexOf("</span>")); } else { if (fname.indexOf("\"infoValue\">") > -1) { idxStart = fname.indexOf("\"infoValue\">") + "\"infoValue\">".length(); fname = fname.substring(idxStart, fname.indexOf("</div>", idxStart)); } else if (fname.indexOf(">") > -1) { fname = fname.substring(fname.lastIndexOf(">") + 1); } } fname.trim(); } } if (emailContentBody.indexOf("Last Name:") > -1) { int idxStart = emailContentBody.indexOf("Last Name:") + "Last Name:</td>".length(); lname = emailContentBody.substring( idxStart, emailContentBody.indexOf("</td>", idxStart)); lname = lname.substring(lname.lastIndexOf(">") + 1); lname.trim(); } else { if (emailContentBody.indexOf(">Last Name") > -1) { int idxStart = emailContentBody.indexOf(">Last Name") + ">Last Name".length(); int idxEnd = emailContentBody.indexOf("</p>", emailContentBody.indexOf(">Last Name")); lname = emailContentBody.substring(idxStart, idxEnd); if (lname.indexOf("<span class=\"data\">") > -1) { lname = lname.substring(lname.indexOf("\">") + 2, lname.indexOf("</span>")); } else { if (lname.indexOf("\"infoValue\">") > -1) { idxStart = lname.indexOf("\"infoValue\">") + "\"infoValue\">".length(); lname = lname.substring(idxStart, lname.indexOf("</div>", idxStart)); } else if (lname.indexOf(">") > -1) { lname = lname.substring(lname.lastIndexOf(">") + 1); } } lname.trim(); } } if (UtilValidate.isEmpty(contactEmail)) { if (emailContentBody.indexOf(">Email Address") > -1) { contactEmail = emailContentBody.substring( emailContentBody.indexOf(">Email Address") + ">Email Address".length(), emailContentBody.indexOf( "</p>", emailContentBody.indexOf(">Email Address"))); if (contactEmail.indexOf(">") > -1) { contactEmail = contactEmail.substring(contactEmail.lastIndexOf(">") + 1); } } } if (emailContentBody.indexOf("Reason For Contact:") > -1) { int idxStart = emailContentBody.indexOf("Reason For Contact:") + "Reason For Contact:</td>".length(); reasonOfContact = emailContentBody.substring( idxStart, emailContentBody.indexOf("</td>", idxStart)); reasonOfContact = reasonOfContact.substring(reasonOfContact.lastIndexOf(">") + 1); reasonOfContact.trim(); } else { if (emailContentBody.indexOf(">Reason For Contact") > -1) { int idxStart = emailContentBody.indexOf(">Reason For Contact") + ">Reason For Contact".length(); int idxEnd = emailContentBody.indexOf( "</p>", emailContentBody.indexOf(">Reason For Contact")); reasonOfContact = emailContentBody.substring(idxStart, idxEnd); if (reasonOfContact.indexOf("<span class=\"data\">") > -1) { reasonOfContact = reasonOfContact.substring( reasonOfContact.indexOf("\">") + 2, reasonOfContact.indexOf("</span>")); } else { if (reasonOfContact.indexOf("\"infoValue\">") > -1) { idxStart = reasonOfContact.indexOf("\"infoValue\">") + "\"infoValue\">".length(); reasonOfContact = reasonOfContact.substring( idxStart, reasonOfContact.indexOf("</div>", idxStart)); } else if (reasonOfContact.indexOf(">") > -1) { reasonOfContact = reasonOfContact.substring(reasonOfContact.lastIndexOf(">") + 1); } } reasonOfContact.trim(); } } if (emailContentBody.indexOf(">Address") > -1) { emailContentAddress = emailContentBody.substring( emailContentBody.indexOf(">Address") + ">Address".length(), emailContentBody.indexOf(">Contact Phone")); } if (emailContentAddress.indexOf("<p>") > -1) { address1 = emailContentAddress.substring( emailContentAddress.indexOf("<p>") + "<p>".length(), emailContentAddress.indexOf("</p>", emailContentAddress.indexOf("<p>"))); emailContentAddress = emailContentAddress.replaceFirst( emailContentAddress.substring( emailContentAddress.indexOf("<p>"), emailContentAddress.indexOf( "</p>", emailContentAddress.indexOf("<p>"))), ""); if (address1.indexOf(">") > -1) { address1 = address1.substring(address1.lastIndexOf(">") + 1); } } if (emailContentAddress.indexOf("<p>") > -1) { address2 = emailContentAddress.substring( emailContentAddress.indexOf("<p>") + "<p>".length(), emailContentAddress.indexOf("</p>", emailContentAddress.indexOf("<p>"))); emailContentAddress = emailContentAddress.replaceFirst( emailContentAddress.substring( emailContentAddress.indexOf("<p>"), emailContentAddress.indexOf( "</p>", emailContentAddress.indexOf("<p>"))), ""); if (address2.indexOf(">") > -1) { address2 = address2.substring(address2.lastIndexOf(">") + 1); } } if (emailContentAddress.indexOf("<p>") > -1) { cityStateZip = emailContentAddress.substring( emailContentAddress.indexOf("<p>") + "<p>".length(), emailContentAddress.indexOf("</p>", emailContentAddress.indexOf("<p>"))); if (cityStateZip.indexOf(">") > -1) { cityStateZip = cityStateZip.substring(cityStateZip.lastIndexOf(">") + 1); } } if (emailContentBody.indexOf("Contact Phone:") > -1) { int idxStart = emailContentBody.indexOf("Contact Phone:") + "Contact Phone:</td>".length(); phone = emailContentBody.substring( idxStart, emailContentBody.indexOf("</td>", idxStart)); phone = phone.substring(phone.lastIndexOf(">") + 1); phone.trim(); } else { if (emailContentBody.indexOf(">Contact Phone") > -1) { int idxStart = emailContentBody.indexOf(">Contact Phone") + ">Contact Phone".length(); int idxEnd = emailContentBody.indexOf("</p>", emailContentBody.indexOf(">Contact Phone")); phone = emailContentBody.substring(idxStart, idxEnd); if (phone.indexOf("<span class=\"data\">") > -1) { phone = phone.substring(phone.indexOf("\">") + 2, phone.indexOf("</span>")); } else { if (phone.indexOf("\"infoValue\">") > -1) { idxStart = phone.indexOf("\"infoValue\">") + "\"infoValue\">".length(); phone = phone.substring(idxStart, phone.indexOf("</div>", idxStart)); } else if (phone.indexOf(">") > -1) { phone = phone.substring(phone.lastIndexOf(">") + 1); } } phone.trim(); } } if (emailContentBody.indexOf("Order #:") > -1) { int idxStart = emailContentBody.indexOf("Order #:") + "Order #:</td>".length(); orderNum = emailContentBody.substring( idxStart, emailContentBody.indexOf("</td>", idxStart)); orderNum = orderNum.substring(orderNum.lastIndexOf(">") + 1); orderNum.trim(); } else { if (emailContentBody.indexOf(">Order #") > -1) { int idxStart = emailContentBody.indexOf(">Order #") + ">Order #".length(); int idxEnd = emailContentBody.indexOf("</p>", emailContentBody.indexOf(">Order #")); orderNum = emailContentBody.substring(idxStart, idxEnd); if (orderNum.indexOf("<span class=\"data\">") > -1) { orderNum = orderNum.substring( orderNum.indexOf("\">") + 2, orderNum.indexOf("</span>")); } else { if (orderNum.indexOf("\"infoValue\">") > -1) { idxStart = orderNum.indexOf("\"infoValue\">") + "\"infoValue\">".length(); orderNum = orderNum.substring(idxStart, orderNum.indexOf("</div>", idxStart)); } else if (orderNum.indexOf(">") > -1) { orderNum = orderNum.substring(orderNum.lastIndexOf(">") + 1); } } orderNum.trim(); } } if (emailContentBody.indexOf("Comment:") > -1) { int idxStart = emailContentBody.indexOf("Comment:") + "Comment:</td>".length(); comment = emailContentBody.substring( idxStart, emailContentBody.indexOf("</td>", idxStart)); comment = comment.substring(comment.lastIndexOf(">") + 1); comment.trim(); } else { if (emailContentBody.indexOf(">Comment") > -1) { int idxStart = emailContentBody.indexOf(">Comment") + ">Comment".length(); int idxEnd = emailContentBody.indexOf("</p>", idxStart); comment = emailContentBody.substring(idxStart, idxEnd); if (comment.indexOf("<span class=\"data\">") > -1) { comment = comment.substring(comment.indexOf("\">") + 2, comment.indexOf("</span>")); } else { if (comment.indexOf("\"infoValue\">") > -1) { idxStart = comment.indexOf("\"infoValue\">") + "\"infoValue\">".length(); comment = comment.substring(idxStart, comment.indexOf("</div>", idxStart)); } else if (comment.indexOf(">") > -1) { comment = comment.substring(comment.lastIndexOf(">") + 1); } } } } } catch (IndexOutOfBoundsException iobex) { Debug.logError( iobex, "Error extracting column value for commEventId:" + commEventId, module); return ServiceUtil.returnError( "Error extracting column value for commEventId:" + commEventId); } catch (Exception ex) { Debug.logError(ex, "Error commEventId:" + commEventId, module); return ServiceUtil.returnError( "Error extracting column value for commEventId:" + commEventId); } label = new Label(4, row, fname.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(5, row, lname.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(6, row, contactEmail.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(7, row, reasonOfContact.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(8, row, address1.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(9, row, address2.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(10, row, cityStateZip.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(11, row, phone.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(12, row, orderNum.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); label = new Label(13, row, comment.replaceAll("\n", "").trim(), times); excelSheet.addCell(label); // label = new Label(12, row, emailContentBody, times); // excelSheet.addCell(label); row++; } Debug.logInfo("Finished loop", module); workbook.write(); workbook.close(); new File(outdir, entityName + ".xml").delete(); } catch (Exception ex) { if (values != null) { try { values.close(); } catch (Exception exc) { // Debug.warning(); } } if (workbook != null) { try { workbook.close(); } catch (Exception exc) { // Debug.warning(); } } } } else { results.add("Path not found or no write access."); return ServiceUtil.returnError("Path not found or no write access."); } } else { results.add("No path specified, doing nothing."); return ServiceUtil.returnError("No path specified, doing nothing."); } // send the notification Debug.logInfo("Finished job", module); Map<String, Object> resp = UtilMisc.<String, Object>toMap("results", results); return resp; }