/**
* 写入公式
*
* @param colNum 列号
* @param rowNum 行号
* @param formula 写入公式内容
*/
public void setFormula(int colNum, int rowNum, String formula) {
try {
wrCurrentSheet.addCell(new Formula(6, 3, formula));
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
/**
* 写入数值
*
* @param colNum 列号
* @param rowNum 行号
* @param num 数值
*/
public void setNumber(int colNum, int rowNum, double num) {
jxl.write.Number number = new jxl.write.Number(colNum, rowNum, num);
try {
// 写入数值
wrCurrentSheet.addCell(number);
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
/**
* 写入文本值
*
* @param colNum 列号
* @param rowNum 行号
* @param sText 文本内容
*/
public void setText(int colNum, int rowNum, String sText) {
Label labelc = new Label(colNum, rowNum, sText);
try {
// 写入文本值
wrCurrentSheet.addCell(labelc);
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
/**
* 写入日期型
*
* @param colNum 列号
* @param rowNum 行号
* @param dt 日期或时间
* @param dateformat “YYY-MM-dd” "YYYY-MM-dd hh:mm：ss" 日期或时间格式
*/
public void setDate(int colNum, int rowNum, Date dt, String dateformat) {
try {
// 定义日期格式
DateFormat df = new DateFormat(dateformat);
// 定义cell的日期格式
WritableCellFormat wcfDF = new WritableCellFormat(df);
DateTime labelDTF = new DateTime(colNum, rowNum, dt, wcfDF);
// 写入日期
wrCurrentSheet.addCell(labelDTF);
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
/** {@inheritDoc} */
@Override
public String exportData(String baseUri, List<SecrecyStatisticsDto> list, District district) {
boolean flag = false;
int currentRow = 2;
JExcelUtils excelUtils = new JExcelUtils();
String path = baseUri + "exportExcel/staticDatas_" + System.currentTimeMillis() + ".xls";
File file = new File(path);
String sheetName = district.getDistrictName() + "级机关单位数据录入情况一览表";
try {
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file));
WritableWorkbook workbook = Workbook.createWorkbook(bos);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
excelUtils.initialSheetSetting(sheet);
// 第一行 组装
Label labelIndex = new Label(0, 0, "单位名称", excelUtils.getTitleCellFormat());
sheet.addCell(labelIndex);
for (int i = 1; i < 5; i++) {
Label label = new Label(i, 0, "保密工作机构", excelUtils.getTitleCellFormat());
sheet.mergeCells(1, 0, 4, 0);
sheet.addCell(label);
}
for (int i = 5; i < 11; i++) {
Label label = new Label(i, 0, "保密业务", excelUtils.getTitleCellFormat());
sheet.mergeCells(5, 0, 10, 0);
sheet.addCell(label);
}
// 第二行 组装
Label label0 = new Label(0, 1, "单位名称", excelUtils.getTitleCellFormat());
sheet.addCell(label0);
for (int i = 1; i < 3; i++) {
Label label = new Label(i, 1, "机构信息", excelUtils.getTitleCellFormat());
sheet.mergeCells(1, 1, 2, 1);
sheet.addCell(label);
}
Label label3 = new Label(3, 1, "机构成员", excelUtils.getTitleCellFormat());
sheet.addCell(label3);
Label label4 = new Label(4, 1, "保密办成员", excelUtils.getTitleCellFormat());
sheet.addCell(label4);
for (int i = 5; i < 7; i++) {
Label label = new Label(i, 1, "要害部门", excelUtils.getTitleCellFormat());
sheet.mergeCells(5, 1, 6, 1);
sheet.addCell(label);
}
for (int i = 7; i < 9; i++) {
Label label = new Label(i, 1, "要害部位", excelUtils.getTitleCellFormat());
sheet.mergeCells(7, 1, 8, 1);
sheet.addCell(label);
}
for (int i = 9; i < 11; i++) {
Label label = new Label(i, 1, "涉密人员", excelUtils.getTitleCellFormat());
sheet.mergeCells(9, 1, 10, 1);
sheet.addCell(label);
}
// 第三行标题
for (int i = 0; i < dataTitles.length; i++) {
Label label = new Label(i, 2, dataTitles[i], excelUtils.getTitleCellFormat());
sheet.addCell(label);
}
// 统计数字
Integer num1 = 0;
Integer num2 = 0;
Integer num3 = 0;
Integer num4 = 0;
Integer num5 = 0;
Integer num6 = 0;
Integer num7 = 0;
Integer num8 = 0;
Integer num9 = 0;
Integer num10 = 0;
// 数据行
for (SecrecyStatisticsDto ob : list) {
currentRow += 1;
String[] data =
new String[] {
ob.getOrganName(),
ob.getNumGroupEntering() + "",
ob.getNumGroupReprot() + "",
ob.getNumGroupMember() + "",
ob.getNumSecrecyWorkOrganMember() + "",
ob.getNumKeysectionEntering() + "",
ob.getNumKeyPartReport() + "",
ob.getNumKeyPartEntering() + "",
ob.getNumKeyPartReport() + "",
ob.getNumSecrecyPersonEntering() + "",
ob.getNumSecrecyPersonReport() + ""
};
num1 += ob.getNumGroupEntering();
num2 += ob.getNumGroupReprot();
num3 += ob.getNumGroupMember();
num4 += ob.getNumSecrecyWorkOrganMember();
num5 += ob.getNumKeysectionEntering();
num6 += ob.getNumKeyPartReport();
num7 += ob.getNumKeyPartEntering();
num8 += ob.getNumKeyPartReport();
num9 += ob.getNumSecrecyPersonEntering();
num10 += ob.getNumSecrecyPersonReport();
excelUtils.insertRowData(
sheet, currentRow, data, excelUtils.getDataCellFormat(CellType.LABEL));
}
// 最后一行 组装
currentRow = currentRow + 1;
String[] dataLast =
new String[] {
"统计结果", num1 + "", num2 + "", num3 + "", num4 + "", num5 + "", num6 + "", num7 + "",
num8 + "", num9 + "", num10 + ""
};
excelUtils.insertRowData(
sheet, currentRow, dataLast, excelUtils.getDataCellFormat(CellType.LABEL));
workbook.write();
workbook.close();
flag = true;
} catch (FileNotFoundException e) {
LOGGER.error(e.getMessage(), e);
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
} catch (RowsExceededException e) {
LOGGER.error(e.getMessage(), e);
} catch (WriteException e) {
LOGGER.error(e.getMessage(), e);
}
return (flag ? path : null);
}