//实现类:FluctuationBillDataImportUI
//实现项目: 中山佳能项目 异动申请单数据导入UI
导入Excel详解
1.Bos创建导入UI
2.画好界面后进行按钮代码逻辑控制
(1)初始化数据出错处理下拉框(onload 调用)
/**
* @author: longcw_moya
* @date: 2017-5-23 上午12:49:18
* @Description: 初始化数据出错处理下拉框
*/
private void initComboError()
{
this.comboError.removeAllItems();
this.comboError.addItem(ErrorHandleModeEnum.ReturnRightnow); //出错立即返回
this.comboError.addItem(ErrorHandleModeEnum.ReturnWhenFinish); //执行完返回
this.comboError.setSelectedIndex(0);
}
(2)引出模板按钮代码:
/**
* @author: longcw_moya
* @date: 2017-5-19 下午04:54:52
* @Description: 引出模板按钮
* @param e
* @throws Exception
*/
@Override
protected void btnExportTemplate_actionPerformed(ActionEvent e)
throws Exception {
// super.btnExportTemplate_actionPerformed(e);
FileSelector fselector = new FileSelector(this, FileChooserPromptBox.CHOOSERTYPE_SAVE); // 创建一个文件选择器 参数1 父类 参数2 选择器类型
fselector.setDefFileName("FluctuationData.xls"); //设置文件选择器名称
fselector.show();
//校验文件选择器是否被取消
if(fselector.isCanceled()) {
return;
}
String expFilePath = fselector.getData().toString(); //获取Exp文件输出路径
FileOutputStream out = null; //创建文件输出流
try {
out = new FileOutputStream(expFilePath); //将加载输出路径
} catch (FileNotFoundException ex) { //防止覆盖一打开文件
MsgBox.showError(this, "该文件已打开,不能覆盖");
SysUtil.abort();
}
HSSFWorkbook wb = new HSSFWorkbook(); //创建一个Excel文件
//1.初始化单元格样式
HSSFCellStyle nameCellStyle = createNameCellStyle(wb); //创建名称单元格样式(Head中文标识行)
HSSFCellStyle codeCellStyle = createCodeCellStyle(wb); //创建编码单元格样式(Head英文标识行)
HSSFCellStyle titleStyle = createTitleCellStyle(wb); //创建标题单元格样式
HSSFCellStyle cellValueStyle = createValueCellStyle(wb); //创建值单元格样式
HSSFSheet sheet = wb.createSheet(); //创建一个excel的sheet(工作表,也就是新增一个新页签)
//2.创建标题行
HSSFRow titleRow1 = sheet.createRow(0);
HSSFRow titleRow2 = sheet.createRow(1);
//3.创建Head行
HSSFRow headRow = sheet.createRow(2);
HSSFRow headRow2 = sheet.createRow(3);
/*
* 4.初始化导出模板字段
*/
//初始化标题单元格
createHeadCell(titleRow1, codeCellStyle, 0, "EAS"); //初始化单元格 参数(head行, 单元格样式, 单元格下标, 单元格值)
createHeadCell(titleRow2, codeCellStyle, 0, "fluctuationbill");
createHeadCell(titleRow1, codeCellStyle, 1, "5.4");
createHeadCell(titleRow2, codeCellStyle, 1, "内部异动名单明细");
createHeadCell(titleRow1, titleStyle, 2, "内部异动名单明细");
createHeadCell(titleRow2, titleStyle, 2, "内部异动名单明细");
//初始化名称单元格
createHeadCell(headRow, codeCellStyle, 0, "person");
createHeadCell(headRow2, nameCellStyle, 0, "姓名");
//初始化人员编码单元格
createHeadCell(headRow, codeCellStyle, 1, "number");
createHeadCell(headRow2, nameCellStyle, 1, "人员编码");
//初始化异动类型单元格
createHeadCell(headRow, codeCellStyle, 2, "type");
createHeadCell(headRow2, nameCellStyle, 2, "异动类型");
//初始化异动生效日期单元格
createHeadCell(headRow, codeCellStyle, 3, "effectDate");
createHeadCell(headRow2, nameCellStyle, 3, "异动生效日期");
//初始化当前行政组织单元格
createHeadCell(headRow, codeCellStyle, 4, "oldDept");
createHeadCell(headRow2, nameCellStyle, 4, "当前行政组织");
//初始化当前职位单元格
createHeadCell(headRow, codeCellStyle, 5, "oldPosition");
createHeadCell(headRow2, nameCellStyle, 5, "当前职位");
//初始化当前职务单元格
createHeadCell(headRow, codeCellStyle, 6, "oldJob");
createHeadCell(headRow2, nameCellStyle, 6, "当前职务");
//初始化目标职位单元格
createHeadCell(headRow, codeCellStyle, 7, "newPosition");
createHeadCell(headRow2, nameCellStyle, 7, "目标职位");
//初始化目标行政组织单元格
createHeadCell(headRow, codeCellStyle, 8, "newDept");
createHeadCell(headRow2, nameCellStyle, 8, "目标行政组织");
//初始化目标所属代码单元格
createHeadCell(headRow, codeCellStyle, 9, "adminorgBase");
createHeadCell(headRow2, nameCellStyle, 9, "目标所属代码");
//初始化目标考勤代码单元格
createHeadCell(headRow, codeCellStyle, 10, "attendanceCode");
createHeadCell(headRow2, nameCellStyle, 10, "目标考勤代码");
//初始化异动原因单元格
createHeadCell(headRow, codeCellStyle, 11, "reason");
createHeadCell(headRow2, nameCellStyle, 11, "异动原因");
//初始化备注单元格
createHeadCell(headRow, codeCellStyle, 12, "remark");
createHeadCell(headRow2, nameCellStyle, 12, "备注");
//初始化融合行
sheet.addMergedRegion(new Region(0, (short) 2, 1, (short) 12));
//设置默认列样式
for (int i = 0; i < 12; i++)
{
sheet.setDefaultColumnStyle((short)i, cellValueStyle);
}
//设置默认列宽
sheet.setDefaultColumnWidth((short) 60);
wb.setSheetName(0, "内部异动名单明细"); //设置Excel的Sheet页签名称
wb.write(out); //文件输出流写入Excel文件
out.flush(); //清空文件输出流
out.close(); //关闭文件输出流
MsgBox.showInfo("模板导出成功");
}
/**
* @author: longcw_moya
* @date: 2017-5-19 下午07:37:03
* @Description: 初始化名称单元格样式
* @param workbook
* @return
*/
private HSSFCellStyle createNameCellStyle(HSSFWorkbook workbook) {
HSSFCellStyle titleStyle = null;
if (titleStyle == null)
{
titleStyle = workbook.createCellStyle();
titleStyle.setFillPattern((short) 1);
titleStyle.setFillForegroundColor((short) 26);
titleStyle.setAlignment((short) 2);
titleStyle.setHidden(false);
titleStyle.setLocked(false);
short borderstyle = 7;
titleStyle.setBorderLeft(borderstyle);
titleStyle.setBorderRight(borderstyle);
titleStyle.setBorderTop(borderstyle);
titleStyle.setBorderBottom(borderstyle);
short backColorIndex = 8;
titleStyle.setBottomBorderColor(backColorIndex);
titleStyle.setTopBorderColor(backColorIndex);
titleStyle.setLeftBorderColor(backColorIndex);
titleStyle.setRightBorderColor(backColorIndex);
titleStyle.setVerticalAlignment((short) 1);
HSSFFont defaultFont = workbook.createFont();
defaultFont.setFontName("宋体");
defaultFont.setColor((short) 8);
short size = 12;
defaultFont.setFontHeightInPoints(size);
titleStyle.setFont(defaultFont);
}
return titleStyle;
}
/**
* @author: longcw_moya
* @date: 2017-5-19 下午07:37:03
* @Description: 初始化编码单元格样式
* @param workbook
* @return
*/
private HSSFCellStyle createCodeCellStyle(HSSFWorkbook workbook)
{
HSSFCellStyle orgStructStyle = null;
if (orgStructStyle == null)
{
orgStructStyle = workbook.createCellStyle();
orgStructStyle.setFillPattern((short) 1);
orgStructStyle.setFillForegroundColor((short) 31);
orgStructStyle.setAlignment((short) 1);
orgStructStyle.setHidden(false);
orgStructStyle.setLocked(false);
short borderstyle = 7;
orgStructStyle.setBorderLeft(borderstyle);
orgStructStyle.setBorderRight(borderstyle);
orgStructStyle.setBorderTop(borderstyle);
orgStructStyle.setBorderBottom(borderstyle);
short backColorIndex = 8;
orgStructStyle.setBottomBorderColor(backColorIndex);
orgStructStyle.setTopBorderColor(backColorIndex);
orgStructStyle.setLeftBorderColor(backColorIndex);
orgStructStyle.setRightBorderColor(backColorIndex);
orgStructStyle.setVerticalAlignment((short) 1);
HSSFFont defaultFont = workbook.createFont();
defaultFont.setFontName("宋体");
short size = 12;
defaultFont.setFontHeightInPoints(size);
orgStructStyle.setFont(defaultFont);
}
return orgStructStyle;
}
/**
* @author: longcw_moya
* @date: 2017-5-19 下午07:37:03
* @Description: 初始化标题单元格样式
* @param workbook
* @return
*/
private HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook)
{
HSSFCellStyle titleStyle = null;
if (titleStyle == null)
{
titleStyle = workbook.createCellStyle();
titleStyle.setFillPattern((short) 1);
titleStyle.setFillForegroundColor((short) 26);
titleStyle.setAlignment((short) 2);
titleStyle.setHidden(false);
titleStyle.setLocked(false);
titleStyle.setBorderLeft((short) 0);
titleStyle.setBorderRight((short) 0);
titleStyle.setBorderTop((short) 0);
titleStyle.setBorderBottom((short) 0);
short backColorIndex = 8;
titleStyle.setBottomBorderColor(backColorIndex);
titleStyle.setTopBorderColor(backColorIndex);
titleStyle.setLeftBorderColor(backColorIndex);
titleStyle.setRightBorderColor(backColorIndex);
titleStyle.setVerticalAlignment((short) 1);
HSSFFont defaultFont = workbook.createFont();
defaultFont.setFontName("宋体");
defaultFont.setColor((short) 17);
short size = 18;
defaultFont.setFontHeight(size);
defaultFont.setFontHeightInPoints(size);
titleStyle.setFont(defaultFont);
}
return titleStyle;
}
/**
* @author: longcw_moya
* @date: 2017-5-19 下午07:37:03
* @Description:初始化值单元格样式
* @param workbook
* @return
*/
private HSSFCellStyle createValueCellStyle(HSSFWorkbook workbook)
{
HSSFCellStyle valueStyle = workbook.createCellStyle();
valueStyle.setAlignment((short) 1);
valueStyle.setHidden(true);
short borderstyle = 7;
valueStyle.setBorderLeft(borderstyle);
valueStyle.setBorderRight(borderstyle);
valueStyle.setBorderTop(borderstyle);
valueStyle.setBorderBottom(borderstyle);
short backColorIndex = 8;
valueStyle.setBottomBorderColor(backColorIndex);
valueStyle.setTopBorderColor(backColorIndex);
valueStyle.setLeftBorderColor(backColorIndex);
valueStyle.setRightBorderColor(backColorIndex);
valueStyle.setVerticalAlignment((short) 1);
HSSFFont defaultFont = workbook.createFont();
defaultFont.setFontName("宋体");
defaultFont.setColor((short) 8);
short size = 12;
defaultFont.setFontHeightInPoints(size);
valueStyle.setFont(defaultFont);
valueStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
return valueStyle;
}
/**
* @author: longcw_moya
* @date: 2017-5-19 下午07:37:03
* @Description: 初始化Head单元格
* @param workbook
* @return
*/
private void createHeadCell(HSSFRow headRow, HSSFCellStyle cellStyle, int index, String cellValue)
{
HSSFCell cell6 = headRow.createCell((short)index);
cell6.setCellType(1);
cell6.setCellValue(cellValue);
cell6.setCellStyle(cellStyle);
}