1、VM页面的显示，显示一个单选条件和一个“导出列表”按钮
<form id="entryYearsDistributionExportFrame" action="${contextPath}/excel/excel_entryYearsDistribution.$webType"
method="post" target="entryYearsDistributionExportFrame">
入职分布年限导出:  是否包含已离职员工
<input type="radio" name="exportType" value="1" checked />是
<input type="radio" name="exportType" value="0" />否
<input name="" type="button" value="导出列表" onclick="Excel.entryYearsDistribution();"
class="add-btn" style="font-size: 12px;padding-left: 17px;"/>
</form>
<iframe id="entryYearsDistributionExportFrame" name="entryYearsDistributionExportFrame" width="0" height="0" style="display:none"></iframe>
2.1、Excel.entryYearsDistribution()的实现(js)。
entryYearsDistribution: function () {
var submit = function (v, h, f) {
if (v == 'ok') {
try {
exportTimer = setInterval("Excel.isExportComplated()", 5000);
$.jBox.close(true);
$.jBox.tip("正在导出，请稍候...", 'loading');
$("#entryYearsDistributionExportFrame").submit();
} catch (e) {
$.jBox.closeTip();
$.jBox.info(e.description, "系统温馨提示");
}
return false;
}
return true;//close
};
$.jBox.confirm("导出所选信息？", "系统温馨提示", submit);
}
2.2Excel.isExportComplated()的实现(js)。
isExportComplated: function () {
$.post(contextPath + "/inwork/inwork_isExportSuc" + webtype, function (result) {
if (result.length != 0 && result.msg != "exporting") {
window.jBox.closeTip();
clearInterval(exportTimer);
if (result.msg == "succ") {
$.jBox.info("导出完成", "系统温馨提示");
}
if (result.msg == "fail") {
$.jBox.info("导出失败", "系统温馨提示");
}
}
}, "json");
},
3.1《contextPath + “/inwork/inwork_isExportSuc” + webtype》实现类（java）。
public void isExportSuc() throws Exception {
this.setIsOutPut(Constants.VELOCITY_OUTPUT_SWITCH);
synchronized (this.getServletResponse()) {
HttpSession session = this.getServletRequest().getSession();
String isExport = (String) session.getAttribute("isExport");
if (isExport == null) {
this.outPutAjax("{\"msg\":\"exporting\"}");
} else {
log.info("isExport:" + isExport);
this.outPutAjax("{\"msg\":\"" + isExport + "\"}");
this.getServletRequest().getSession().removeAttribute("isExport");
}
}
}
3.2
《 c o n t e x t P a t h / e x c e l / e x c e l e n t r y Y e a r s D i s t r i b u t i o n . {contextPath}/excel/excel_entryYearsDistribution. contextPath/excel/excelentryYearsDistribution.webType》实现类（java）。
public void entryYearsDistribution() throws Exception {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String exportType = this.getServletRequest().getParameter("exportType");
ByteArrayOutputStream os = new ByteArrayOutputStream();
InputStream inStream = null;
OutputStream outputStream = null;
String isExport = "succ";
HttpSession session = this.getServletRequest().getSession();
try {
employeeInfoService.entryYearsDistribution(os,exportType);
String fileName = URLEncoder.encode("入职分布年限" + dateFormat.format(new Date()) + "列表");
os.flush();
byte[] buf = os.toByteArray();
log.info("生成excel长度：" + buf.length + "字节");
inStream = new ByteArrayInputStream(buf);
this.getServletResponse().reset();
this.getServletResponse().setContentType("application/vnd.ms-excel");
this.getServletResponse().setCharacterEncoding("UTF-8");
this.getServletResponse().addHeader("Content-Disposition",
"attachment; filename=\"" + fileName + ".xlsx\"");
byte[] b = new byte[1024];
int len;
outputStream = this.getServletResponse().getOutputStream();
while ((len = inStream.read(b)) > 0) {
outputStream.write(b, 0, len);
}
} catch (Exception e) {
log.error("{}", e);
isExport = "fail";
} finally {
try {
if (os != null) {
os.close();
}
if (inStream != null) {
inStream.close();
}
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
logger.error("os close exception!!", e);
}
session.setAttribute("isExport", isExport);
}
}
3.3employeeInfoService.entryYearsDistribution(os,exportType);实现类（java）。
public void entryYearsDistribution(ByteArrayOutputStream os,String exportType) {
List<DepartmentInfo> entryYearsDistributionList = null;
if("1".equals(exportType)) {
entryYearsDistributionList = departmentInfoDAO.getentryYearsDistribution();
}else {
entryYearsDistributionList = departmentInfoDAO.getentryYearsDistribution2();
}
ArrayList<Map<String, String>> data = new ArrayList<>();
for (DepartmentInfo entryYearsDistribution : entryYearsDistributionList) {
Map<String, String> map1 = ObjectUtil.objectToMap(entryYearsDistribution, "");
data.add(map1);
}
int[] headWidth = new int[]{15,15,15,15,15,15,15,15,15,15};
LinkedHashMap<String, String> relation = new LinkedHashMap<>();
relation.put("行标签", "departmentName");
relation.put("2013年及以前", "thirteenYearsAgo");
relation.put("2014年", "fourteenYear");
relation.put("2015年", "fifteenYear");
relation.put("2016年", "sixteenYear");
relation.put("2017年", "seventeenYear");
relation.put("2018年", "eighteenYear");
relation.put("2019年", "nineteenYear");
relation.put("总计", "total");
log.info("入职分布年限报表完成");
BaseExcelExportUtils baseExcelExportUtils = new BaseExcelExportUtils(headWidth) {
@Override
public String setCellStyle(Cell cell, String attrStr) {
return null;
}
};
baseExcelExportUtils.exportInwork(data, os, relation, "报工报表");
}
3.4 BaseExcelExportUtils工具类
@Slf4j
@NoArgsConstructor
public abstract class BaseExcelExportUtils {
private int[] headWidth;
private Map<String, CellStyle> styles;
public BaseExcelExportUtils(int[] headWidth) {
this.headWidth = headWidth;
}
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
DataFormat df = wb.createDataFormat();
CellStyle style;
Font headerFont = wb.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE
.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
style.setDataFormat(df.getFormat("text"));
styles.put("header", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setWrapText(true);
style.setDataFormat(df.getFormat("text"));
style.setLocked(true);
styles.put("cell_normal", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setWrapText(true);
style.setDataFormat(df.getFormat("text"));
style.setLocked(true);
// style.setLeftBorderColor((short) 1);
// style.setBorderLeft((short) 1);
styles.put("cell_decimal", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setWrapText(true);
style.setDataFormat(df.getFormat("text"));
style.setLocked(false);
styles.put("cell_normal_writable", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setWrapText(true);
style.setDataFormat(df.getFormat("text"));
style.setLocked(false);
styles.put("cell_decimal_writable", style);
//有问题的帐号样式
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.RED
.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(df.getFormat("text"));
styles.put("cell_red", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.YELLOW
.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(df.getFormat("text"));
styles.put("cell_yellow", style);
style = createBorderedStyle(wb);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.SKY_BLUE
.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(df.getFormat("text"));
styles.put("cell_skyblue", style);
return styles;
}
private static CellStyle createBorderedStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
return style;
}
public abstract String setCellStyle(Cell cell, String attrStr);
public void exportInwork(List<Map<String, String>> data, ByteArrayOutputStream os, Map<String, String> relation, String title) {
try {
log.info("创建excel");
XSSFWorkbook wb = new XSSFWorkbook();//创建excel
styles = createStyles(wb);//设置样式
if (data != null && data.size() > 0) {
//init excle
log.info("init excle");
Cell cell = null;
Row row;
XSSFSheet sheet = wb.createSheet(title);
// turn off gridlines
initExcle(sheet);
//init header
log.info("init header");
List<String> relaList = initHeader(sheet, relation);
//init body
log.info("init body");
for (int i = 1; i <= data.size(); i++) {
row = sheet.createRow(i);
Map<String, String> rowData = data.get(i - 1);
for (int j = 0; j <= relaList.size(); j++) {
cell = row.createCell(j);
if (j == 0) {
//序号
cell.setCellValue(i);
cell.setCellStyle(styles.get("cell_normal"));
} else {
String attrStr = relation.get(relaList.get(j - 1));
String valStr = String.valueOf(rowData.get(attrStr));
//set cell style
cell.setCellValue(valStr);
cell.setCellStyle(styles.get(setCellStyle(cell, attrStr)));
}
}
}
wb.write(os);
log.info("完成生成excel");
}
} catch (Exception e) {
log.error("{}", e);
}
}
private void initExcle(XSSFSheet sheet) {
sheet.setDisplayGridlines(true);
sheet.setPrintGridlines(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
// the following three statements are required only for XSSF
sheet.setAutobreaks(true);
printSetup.setFitHeight((short) 1);
printSetup.setFitWidth((short) 1);
}
private List<String> initHeader(XSSFSheet sheet, Map<String, String> relations) {
Row dataRow = sheet.createRow(0);
Set<String> keys = relations.keySet();
Set<Map.Entry<String, String>> entries = relations.entrySet();
Iterator<String> iterator = keys.iterator();
List<String> relaList = new ArrayList<>();
int k = 0;
Cell noCell = dataRow.createCell(k);
noCell.setCellValue("序号");
noCell.setCellStyle(styles.get("header"));
sheet.setColumnWidth(k, 256 * headWidth[k]);
k++;
while (iterator.hasNext()) {
String key = iterator.next();
relaList.add(key);
Cell cell = dataRow.createCell(k);
cell.setCellValue(key);
cell.setCellStyle(styles.get("header"));
sheet.setColumnWidth(k, 256 * headWidth[k]);
k++;
}
return relaList;
}
}
541
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
