/** * 添加单元格&内容. * * @param ws * @param cel * @param row * @param valObject */ private void addCell(WritableSheet ws, int cel, int row, Object valObj) throws WriteException { if (valObj == null) { Label cr = new Label(cel, row, ""); ws.addCell(cr); return; } @SuppressWarnings("rawtypes") Class clazz2 = valObj.getClass(); if (clazz2 == String.class) { Label cr = new Label(cel, row, (String) valObj); ws.addCell(cr); return; } else if (clazz2 == int.class || clazz2 == Integer.class) { Number cr = new Number(cel, row, Double.parseDouble(((Integer) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == long.class || clazz2 == Long.class) { Number cr = new Number(cel, row, Double.parseDouble(((Long) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == float.class || clazz2 == Float.class) { Number cr = new Number(cel, row, Double.parseDouble(((Float) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == double.class || clazz2 == Double.class) { Number cr = new Number(cel, row, Double.parseDouble(((Double) valObj).toString())); ws.addCell(cr); return; } else if (clazz2 == Date.class) { DateTime cr = new DateTime(cel, row, (Date) valObj); ws.addCell(cr); return; } else if (clazz2 == boolean.class || clazz2 == Boolean.class) { jxl.write.Boolean cr = new jxl.write.Boolean(cel, row, (Boolean) valObj); ws.addCell(cr); return; } else if (clazz2 == BigDecimal.class) { NumberFormat nf = new NumberFormat("0.00"); WritableCellFormat wcf = new WritableCellFormat(nf); wcf.setFont(new WritableFont(WritableFont.createFont("宋体"), PS)); Number cr = new Number(cel, row, ((BigDecimal) valObj).doubleValue(), wcf); ws.addCell(cr); return; } else if (clazz2 == Short.class) { Number cr = new Number(cel, row, Double.parseDouble(((Short) valObj).toString())); ws.addCell(cr); return; } }
/** * * * <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; }
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(); }