Java excelは、参照用に完全なコードの例をエクスポートします.
主にコードを削除するのがおっくうで、すべて提出して、具体的なあるモジュールの他の文章の中で細分化しますコード例 サイクル方法
public void exportEvents(EventExportQuery query, RedisUser user, HttpServletResponse response, String token) {
List<EvtToDoListVO> toDoEventLists = null;
ServletOutputStream out = null;
FileInputStream ips = null;
jxl.write.WritableWorkbook wwb = null;
try {
//
int queryTimes = 0;
EvtToDoQuery evtToDoQuery = new EvtToDoQuery();
//ids ， ids
if (!StringUtil.isEmpty(query.getIds())){
List<Long> ids = StringUtil.getIdList(query.getIds());
toDoEventLists = eventReadMapper.findEventToDoByIds(ids);
}else {
EvtInfoVO info = null;
BeanUtils.copyProperties(query, evtToDoQuery);
//
if(user.isAdmin()){
//
evtToDoQuery.setIsAdmin(1);
}else {
if (!StringUtil.isInvalid(user.getRoleIds())) {
evtToDoQuery.setRoleIdList(StringUtil.stringToLongList(user.getRoleIds()));
}
if (!StringUtil.isInvalid(user.getDeptIds())) {
evtToDoQuery.setDeptIdList(StringUtil.stringToLongList(user.getDeptIds()));
}else if (user.getDeptId() != null){
List<Long> idList = new ArrayList<>();
idList.add(user.getDeptId());
evtToDoQuery.setDeptIdList(idList);
}
}
// 2000
PageHelper.startPage(1, 2000);
Page<EvtToDoListVO> toDoLists = eventReadMapper.findEvtToDoList(evtToDoQuery);
if (null != toDoLists.getResult() && toDoLists.getResult().size() > 0){
if (toDoLists.getTotal() > 2000) {
double total = toDoLists.getTotal();
double temp = total / 2000;
queryTimes = new Double(Math.ceil(temp)).intValue();
}
toDoEventLists = toDoLists.getResult();
}
}
//
String sourcePath = System.getProperty("user.dir");
String savePath = sourcePath + "/" + TimeUtil.formatDateyyyyMMdd(TimeUtil.now());
File path = new File(savePath);
if(!path.exists()){
//
path.mkdir();//
}
String fileName = new Date().getTime() + "" + Math.random() + ".xls";
File file = new File(savePath, fileName);
wwb = Workbook.createWorkbook(file);
jxl.write.Label lb = null;
jxl.write.WritableSheet ws = wwb.createSheet(" 1 ", 0);//
jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false);//
jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
wcfF.setAlignment(jxl.format.Alignment.CENTRE);//
ws.mergeCells(0, 0, 7, 0);// 1-11
lb = new jxl.write.Label(0, 0, " ", wcfF);
wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD, false);//
wcfF = new jxl.write.WritableCellFormat(wf);
wcfF.setAlignment(jxl.format.Alignment.CENTRE);//
ws.addCell(lb);
String[] title = {
" "," "," "," ", " ", " "," ", " "," "," ",
" "," "," "," "," "," "," "," "," "," "};
for (int i = 0; i < title.length; i++) {
//
ws.setColumnView(i, 20);//
lb = new jxl.write.Label(i, 1, title[i], wcfF);
ws.addCell(lb);
}
int count = 2;
if (toDoEventLists != null && toDoEventLists.size() > 0) {
for (EvtToDoListVO evt : toDoEventLists) {
List<EvtRuTaskVO> taskList = evtAcceptFlowService.findRuTaskInfoByEvtId(evt.getId());
if (taskList != null && taskList.size() > 0) {
for (EvtRuTaskVO ruTaskVO : taskList) {
if (evt.getWorkTache().equals(ruTaskVO.getCurTacheCode())) {
evt.setCurTacheName(ruTaskVO.getCurTacheName());
evt.setOverTimePoint(ruTaskVO.getOverTimePoint());
}
}
}
ws.setRowView(count,900,false);//
//
if (evt.getOverTimePoint() == null) {
lb = new jxl.write.Label(0, count, " ");
} else {
lb = new jxl.write.Label(0, count, EventTimeUtils.getRemainingTime(evt.getReceiveTime(), evt.getOverTimePoint()));
}
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(1, count, evt.getEvtCodeStr());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(2, count, evt.getReceiveTime().toString());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
//
String evtSource = null;
ResultInfo<String> sourceStr = dictService.findByCodeAndValue(BusinessConstants.EVENT_SOURCE_DICT_CODE,
evt.getEvtResource(), token);
if (sourceStr.getCode().equals(ResultCode.SUCCESS.code())) {
evtSource = sourceStr.getData();
}
lb = new jxl.write.Label(3, count, evtSource);
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(4, count, evt.getEvtClassName());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(5, count, evt.getAreaNameStr());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(6, count, evt.getEvtLocation());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(7, count, evt.getContent());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
//
List<EvtAnnexe> beforeList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicBeforeType);
if (!CollectionUtils.isEmpty(beforeList)){
int cellNum = 8;
if (beforeList.size() > 4){
beforeList.subList(0,3);
}
for (EvtAnnexe item:beforeList) {
String imgPath = filePath +item.getFilePath();
File imgFile =getFile(imgPath);
WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
ws.addImage(image);
cellNum ++;
}
}
List<EvtAnnexe> afterList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicAfterType);
if (!CollectionUtils.isEmpty(afterList)){
int cellNum = 12;
if (afterList.size() > 4){
afterList.subList(0,3);
}
for (EvtAnnexe item:afterList) {
String imgPath = filePath +item.getFilePath();
File imgFile =getFile(imgPath);
WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
ws.addImage(image);
cellNum ++;
}
}
List<EvtAnnexe> heChaList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.ONE_FLOW_TYPE);
if (!CollectionUtils.isEmpty(heChaList)){
int cellNum = 16;
if(heChaList.size() > 4){
heChaList.subList(0,3);
}
for (EvtAnnexe item:heChaList) {
String imgPath = filePath +item.getFilePath();
File imgFile =getFile(imgPath);
WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
ws.addImage(image);
cellNum ++;
}
}
count++;
}
}
// Exel
wwb.write();
wwb.close();
if (queryTimes > 1){
AssembleData(file, evtToDoQuery, count, queryTimes, 2, token);
}
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+ new String(" .xls".getBytes("gbk"), "ISO-8859-1"));
out = response.getOutputStream();
ips = new FileInputStream(file);
//
int len = 0;
// 10MB
byte[] buffer = new byte[1024 * 1024 * 10];
while ((len = ips.read(buffer)) != -1){
out.write(buffer, 0, len);
}
out.flush();
}catch (Exception e) {
e.printStackTrace();
}finally {
//
try {
if (ips != null) {
ips.close();
}
if (out != null) {
out.close();
}
}catch(Exception e) {
e.printStackTrace();
}
}
}
private void AssembleData(File file, EvtToDoQuery query, int count,
int queryTimes, int times, String token){
Workbook wb = null;
jxl.write.WritableWorkbook wwb = null;
WritableFont wf = null;
WritableCellFormat wcfF = null;
try {
wb = Workbook.getWorkbook(file);
wwb = Workbook.createWorkbook(file, wb);
jxl.write.Label lb = null;
jxl.write.WritableSheet ws = null;
// 2000 ， 5 ， sheet
int sheet = (times - 1) / 5;
if ((times - 1) % 5 == 0) {
ws = wwb.createSheet(" " + (sheet + 1) + " ", sheet);//
wf = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false);//
wcfF = new jxl.write.WritableCellFormat(wf);
wcfF.setAlignment(jxl.format.Alignment.CENTRE);//
ws.mergeCells(0, 0, 7, 0);// 1-11
lb = new jxl.write.Label(0, 0, " ", wcfF);
wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);//
wcfF = new jxl.write.WritableCellFormat(wf);
wcfF.setAlignment(jxl.format.Alignment.CENTRE);//
ws.addCell(lb);
String[] title = {
" "," "," "," ", " ", " "," ", " "," "," ",
" "," "," "," "," "," "," "," "," "," "};
for (int i = 0; i < title.length; i++) {
//
ws.setColumnView(i, 20);//
lb = new jxl.write.Label(i, 1, title[i], wcfF);
ws.addCell(lb);
}
count = 2;
}else {
ws = wwb.getSheet(sheet);
}
wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
wcfF = new WritableCellFormat(wf);
wcfF.setAlignment(Alignment.CENTRE);
PageHelper.startPage(times, 2000);
Page<EvtToDoListVO> toDoLists = eventReadMapper.findEvtToDoList(query);
List<EvtToDoListVO> toDoEventLists = toDoLists.getResult();
if (toDoEventLists != null && toDoEventLists.size() > 0) {
for (EvtToDoListVO evt : toDoEventLists) {
ws.setRowView(count,900,false);//
//
if (evt.getOverTimePoint() == null) {
lb = new jxl.write.Label(0, count, " ");
} else {
lb = new jxl.write.Label(0, count, EventTimeUtils.getRemainingTime(evt.getReceiveTime(), evt.getOverTimePoint()));
}
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(1, count, evt.getEvtCodeStr());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(2, count, evt.getReceiveTime().toString());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
//
String evtSource = null;
ResultInfo<String> sourceStr = dictService.findByCodeAndValue(BusinessConstants.EVENT_SOURCE_DICT_CODE,
evt.getEvtResource(), token);
if (sourceStr.getCode().equals(ResultCode.SUCCESS.code())) {
evtSource = sourceStr.getData();
}
lb = new jxl.write.Label(3, count, evtSource);
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(4, count, evt.getEvtClassName());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(5, count, evt.getAreaNameStr());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(6, count, evt.getEvtLocation());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
lb = new jxl.write.Label(7, count, evt.getContent());
lb.setCellFormat(setLabCell());
ws.addCell(lb);
List<EvtAnnexe> beforeList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicBeforeType);
if (!CollectionUtils.isEmpty(beforeList)){
int cellNum = 8;
if (beforeList.size() > 4){
beforeList.subList(0,3);
}
for (EvtAnnexe item:beforeList) {
String imgPath = filePath +item.getFilePath();
File imgFile =getFile(imgPath);
WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
ws.addImage(image);
cellNum ++;
}
}
List<EvtAnnexe> afterList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.eventPicAfterType);
if (!CollectionUtils.isEmpty(afterList)){
int cellNum = 12;
if (afterList.size() > 4){
afterList.subList(0,3);
}
for (EvtAnnexe item:afterList) {
String imgPath = filePath +item.getFilePath();
File imgFile =getFile(imgPath);
WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
ws.addImage(image);
cellNum ++;
}
}
List<EvtAnnexe> heChaList = evtAnnexeService.findEvtImgByBsType(evt.getId(),BusinessConstants.ONE_FLOW_TYPE);
if (!CollectionUtils.isEmpty(heChaList)){
int cellNum = 16;
if (heChaList.size() > 4){
heChaList.subList(0,3);
}
for (EvtAnnexe item:heChaList) {
String imgPath = filePath +item.getFilePath();
File imgFile =getFile(imgPath);
WritableImage image = new WritableImage(cellNum, count, 1, 1, imgFile);
ws.addImage(image);
cellNum ++;
}
}
count++;
}
}
// Exel
wwb.write();
// Excel
wwb.close();
++times;
if (times <= queryTimes) {
AssembleData(file, query, count, queryTimes, times, token);
}
}catch (Exception e) {
e.printStackTrace();
}
}