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);
}
/**
* 添加单元格&内容.
*
* @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("ＭＳ ゴシック");
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",
"入力桁数",
"入力桁数(小数桁)",
"入力属性地",
"メンテフラグ",
"コントロールフラグ",
"変更可否フラグ",
"登録ユーザー名",
"登録ＰＣ名",
"登録日付",
"登録時刻",
"最終更新ユーザー名",
"最終更新ＰＣ名",
"最終更新日付",
"最終更新時刻"
};
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();
}
