jxl实现导出excel代码:
使用jxl导出的xls文件,格式不兼容office 2013,兼容WPS、office 2007
合并单元格使用mergeCells(a,b,c,d)方法:a.单元格的列号,b.单元格的行号,c.从单元格[a,b]起,向下合并的列数,d.从单元格[a,b]起,向下合并的行数
/**
* 导出待入管理库项目
*
* @param request
* @param response
*/
@RequestMapping(value = "/exportPendingManageLibProjs", method = { RequestMethod.GET, RequestMethod.POST })
public void exportPendingManageLibProjs(HttpServletRequest request, HttpServletResponse response) {
PromptMessage promptMessage = getPromptMessage();
response.reset();// 清空输出流
// String directory = "e:\\ppp\\test";
String directory = DBConfig.getString("export_doc_directory");//
String uuid = UUIDHelper.get32UUID();
String currentDirectory = directory + File.separator + uuid + File.separator;
String currentTime = TimestampUtils.formatDate(new Date(), "yyyyMMddHHmmss");
String zipName = "excelfiles_" + currentTime + ".zip";
File dir = new File(currentDirectory);
dir.mkdirs();
response.setContentType("APPLICATION/OCTET-STREAM");
try {
response.setHeader("Content-Disposition", "attachment; filename=" + new String(zipName.getBytes("GBK"), "ISO-8859-1"));
} catch (UnsupportedEncodingException e2) {
e2.printStackTrace();
}
ZipOutputStream outZ = null;
try {
outZ = new ZipOutputStream(response.getOutputStream());
} catch (IOException e1) {
e1.printStackTrace();
}
String selectedRows = request.getParameter("selectedRows");
JSONArray jsonArray = JSONArray.fromObject(selectedRows);
WritableWorkbook workbook = null;
try {
for (int j = 0; j < jsonArray.size(); j++) {
JSONObject jsonObject = jsonArray.getJSONObject(j);
String projRid = jsonObject.getString("PROJ_RID");
String ticketId = jsonObject.getString("TICKETID");
String bpmnType = jsonObject.getString("BPMNTYPE");
String projName = jsonObject.getString("PROJ_NAME");
projName = projName.replace("/", "");
String fileName = "导出待入库_" + projName + ".xls";
// response.setContentType("application/vnd.ms-excel; charset=gb2312");// 定义输出类型
// response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("GBK"), "ISO-8859-1"));// 设定输出文件头
workbook = Workbook.createWorkbook(new File(currentDirectory + File.separator + fileName));
Map<String, Object> param = new HashMap<String, Object>();
param.put("projRid", projRid);
List<Map<String, Object>> list3org = managerService.getAgenciesByProjRId(projRid);// 获取第三方咨询机构数据
double contractExpenseSum = 0d;// 服务合同金额(费用总和)
String orgNames = "无";// 咨询机构名称(逗号分割)
if (list3org != null && list3org.size() > 0) {
Map<String, Object> map = list3org.get(0);
orgNames = map.get("ORG_NAME").toString();
orgNames = orgNames == null ? "无" : orgNames;
Object contractExpense = map.get("CONTRACT_EXPENSE");
if (contractExpense != null) {
BigDecimal temp = (BigDecimal) contractExpense;
contractExpenseSum += temp.doubleValue();
}
}
Map<String, Object> map = queryService.getPendingManageLibProjs(param);
String industryRequiredNew = map.get("INDUSTRY_REQUIRED_NEW") == null ? "" : map.get("INDUSTRY_REQUIRED_NEW").toString();
String distCode = map.get("DIST_CODE") == null ? "" : map.get("DIST_CODE").toString();
String investCount = map.get("INVEST_COUNT") == null ? "0" : map.get("INVEST_COUNT").toString();
String estimateCoper = map.get("ESTIMATE_COPER") == null ? "" : map.get("ESTIMATE_COPER").toString();
String projType = map.get("PROJ_TYPE") == null ? "" : map.get("PROJ_TYPE").toString();
String operateMode = map.get("OPERATE_MODE") == null ? "" : map.get("OPERATE_MODE").toString();
String returnMode = map.get("RETURN_MODE") == null ? "" : map.get("RETURN_MODE").toString();
String startType = map.get("START_TYPE") == null ? "" : map.get("START_TYPE").toString();
String projState = map.get("PROJ_STATE") == null ? "" : map.get("PROJ_STATE").toString();
String implorg = map.get("IMPLORG") == null ? "无" : map.get("IMPLORG").toString();
String purchaseMode = map.get("PURCHASE_MODE") == null ? "无" : map.get("PURCHASE_MODE").toString();
String implPlan = map.get("IMPL_PLAN") == null ? "无" : map.get("IMPL_PLAN").toString();// 实施方案描述
String riskDistribute = map.get("RISK_DISTRIBUTE") == null ? "无" : map.get("RISK_DISTRIBUTE").toString();// 风险分配框架(描述)
String outputDesc = map.get("OUTPUT_DESC") == null ? "无" : map.get("OUTPUT_DESC").toString();// 项目的公共产品和服务产出说明及产出绩效考核指标体系(描述)
String valueEvaluation = map.get("VALUE_EVALUATION") == null ? "无" : map.get("VALUE_EVALUATION").toString();// 物有所值评价(描述)
String financeBear = map.get("FINANCE_BEAR") == null ? "无" : map.get("FINANCE_BEAR").toString();// 财政承受能力论证(描述)
String industryRequiredNewStr = IndustryCache.getIndustryValue(industryRequiredNew);// 行业
String distCodeValue = ProvinceCache.getDistCodeValue(distCode);// 地区
String projTypeStr = SystemCodeUtil.getCodeValue("xmlx", projType);// 项目类型
String operateModeStr = SystemCodeUtil.getCodeValue("yzfs", operateMode);// 运作方式
String returnModeStr = SystemCodeUtil.getCodeValue("hbjz", returnMode);// 回报机制
String startTypeStr = SystemCodeUtil.getCodeValue("fqrlx", startType);// 发起类型
String projStateStr = SystemCodeUtil.getCodeValue("xmzt", projState);// 项目状态
String purchaseModeStr = SystemCodeUtil.getCodeValue("cgfs", purchaseMode);// 采购方式
// 表头样式
WritableFont font_title = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat format_title = new WritableCellFormat(font_title);
format_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置水平对齐方式
format_title.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 竖直方向居中对齐
format_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
// 左侧标题样式
WritableFont font_title_left = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
WritableCellFormat format_title_left = new WritableCellFormat(font_title_left);
format_title_left.setAlignment(jxl.format.Alignment.LEFT); // 设置水平对齐方式
format_title_left.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 竖直方向居中对齐
format_title_left.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
format_title_left.setWrap(true);// 自动换行
// 右侧内容样式
WritableFont font_title_right = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 颜色
WritableCellFormat format_title_right = new WritableCellFormat(font_title_right);
format_title_right.setAlignment(jxl.format.Alignment.LEFT); // 设置水平对齐方式
format_title_right.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 竖直方向居中对齐
format_title_right.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
format_title_right.setWrap(true);// 自动换行
// 财承表头样式
WritableFont font_title_ct = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 颜色
WritableCellFormat format_title_ct = new WritableCellFormat(font_title_ct);
format_title_ct.setAlignment(jxl.format.Alignment.CENTRE); // 设置水平对齐方式
format_title_ct.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 竖直方向居中对齐
format_title_ct.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
format_title_ct.setWrap(true);// 自动换行
// 财承内容样式
WritableFont font_title_ct1 = new WritableFont(WritableFont.ARIAL, 13, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 颜色
WritableCellFormat format_title_ct1 = new WritableCellFormat(font_title_ct1);
format_title_ct1.setAlignment(jxl.format.Alignment.CENTRE); // 设置水平对齐方式
format_title_ct1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 竖直方向居中对齐
format_title_ct1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
format_title_ct1.setWrap(true);// 自动换行
WritableSheet sheet1 = workbook.createSheet("sheet1", 0);
// 创建单元格,参数:c r content
sheet1.addCell(new jxl.write.Label(0, 0, "待入管理库项目情况表", format_title));
sheet1.addCell(new jxl.write.Label(0, 1, "项目名称", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 1, projName, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 2, "所属行业", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 2, industryRequiredNewStr, format_title_right));
sheet1.addCell(new jxl.write.Label(2, 2, "项目所在地", format_title_left));
sheet1.addCell(new jxl.write.Label(3, 2, distCodeValue, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 3, "项目总投资", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 3, investCount + "万元", format_title_right));
sheet1.addCell(new jxl.write.Label(2, 3, "项目合作期限", format_title_left));
sheet1.addCell(new jxl.write.Label(3, 3, estimateCoper + "年", format_title_right));
sheet1.addCell(new jxl.write.Label(0, 4, "项目类型", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 4, projTypeStr, format_title_right));
sheet1.addCell(new jxl.write.Label(2, 4, "项目运作方式", format_title_left));
sheet1.addCell(new jxl.write.Label(3, 4, operateModeStr, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 5, "回报机制", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 5, returnModeStr, format_title_right));
sheet1.addCell(new jxl.write.Label(2, 5, "发起类型", format_title_left));
sheet1.addCell(new jxl.write.Label(3, 5, startTypeStr, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 6, "项目阶段", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 6, projStateStr, format_title_right));
sheet1.addCell(new jxl.write.Label(2, 6, "实施机构", format_title_left));
sheet1.addCell(new jxl.write.Label(3, 6, implorg, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 7, "采购方式", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 7, purchaseModeStr, format_title_right));
sheet1.addCell(new jxl.write.Label(2, 7, "咨询服务费用", format_title_left));
sheet1.addCell(new jxl.write.Label(3, 7, contractExpenseSum + "万元", format_title_right));
sheet1.addCell(new jxl.write.Label(0, 8, "咨询机构", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 8, orgNames, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 9, "实施方案(描述)", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 9, implPlan, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 10, "风险分配框架(描述)", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 10, riskDistribute, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 11, "项目的公共产品和服务产出说明及产出绩效考核指标体系(描述)", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 11, outputDesc, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 12, "物有所值评价(描述)", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 12, valueEvaluation, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 13, "财政承受能力论证(描述)", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 13, financeBear, format_title_right));
sheet1.addCell(new jxl.write.Label(0, 14, "需从预算中安排的支出责任汇总占一般公共预算支出比例", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 14, "年度", format_title_ct));
sheet1.addCell(new jxl.write.Label(2, 14, "本项目财政承受能力论证测算的财政支出责任小计", format_title_ct));
sheet1.addCell(new jxl.write.Label(3, 14, "支出责任占比", format_title_ct));
// 需从预算中安排的支出责任汇总占一般公共预算支出比例
List<PrepareBudget> listBudget = managerDao.getPrepareBudget(projRid);
int size = listBudget.size();
for (int i = 1; i <= size; i++) {
PrepareBudget budget = listBudget.get(i - 1);
String payYear = budget.getPayYear();
Double investExpense = budget.getInvestExpense();
Double rate = budget.getRate();
sheet1.addCell(new jxl.write.Label(1, 14 + i, payYear, format_title_ct1));
sheet1.addCell(new jxl.write.Label(2, 14 + i, investExpense + "", format_title_ct1));
sheet1.addCell(new jxl.write.Label(3, 14 + i, rate + "%", format_title_ct1));
}
// 审核日志
int logSize = 0;
List<Map<String, Object>> processLogs = this.getBpmnProcessLog(bpmnType, ticketId);
sheet1.addCell(new jxl.write.Label(0, 15 + size, "审核意见汇总", format_title_left));
sheet1.addCell(new jxl.write.Label(1, 15 + size, "审核人", format_title_left));
sheet1.addCell(new jxl.write.Label(2, 15 + size, "审核结果", format_title_ct));
sheet1.addCell(new jxl.write.Label(3, 15 + size, "审核意见", format_title_ct));
if (processLogs != null && processLogs.size() > 0) {
logSize = processLogs.size();
for (int i = 1; i <= logSize; i++) {
Map<String, Object> map2 = processLogs.get(i - 1);
String endTime = map2.get("endTime") == null ? "" : map2.get("endTime").toString();
String people = map2.get("people") == null ? "" : map2.get("people").toString();
String auditResult = map2.get("auditResult") == null ? "" : map2.get("auditResult").toString();
String proposal = map2.get("proposal") == null ? "" : map2.get("proposal").toString();
sheet1.addCell(new jxl.write.Label(1, 15 + size + i, people, format_title_right));
sheet1.addCell(new jxl.write.Label(2, 15 + size + i, auditResult, format_title_ct1));
sheet1.addCell(new jxl.write.Label(3, 15 + size + i, proposal, format_title_right));
}
}
// mergeCells(a,b,c,d) 单元格合并函数 a.单元格的列号,b.单元格的行号,c.从单元格[a,b]起,向下合并的列数,d.从单元格[a,b]起,向下合并的行数
sheet1.mergeCells(0, 0, 3, 0);
sheet1.mergeCells(1, 1, 3, 0);
sheet1.mergeCells(1, 8, 3, 0);
sheet1.mergeCells(1, 9, 3, 0);
sheet1.mergeCells(1, 10, 3, 0);
sheet1.mergeCells(1, 11, 3, 0);
sheet1.mergeCells(1, 12, 3, 0);
sheet1.mergeCells(1, 13, 3, 0);
sheet1.mergeCells(0, 14, 0, 14 + size);
sheet1.mergeCells(0, 15 + size, 0, 15 + size + logSize);
// sheet1.mergeCells(1, 15 + size, 3, 0);
// 设置列宽 横向打印
sheet1.setColumnView(0, 14);
sheet1.setColumnView(1, 19);
sheet1.setColumnView(2, 17);
sheet1.setColumnView(3, 36);
// 设置行高 纵向打印
sheet1.setRowView(9, 5100);
sheet1.setRowView(10, 5100);
sheet1.setRowView(11, 5100);
sheet1.setRowView(12, 5100);
sheet1.setRowView(13, 5100);
// 设置行高 横向打印
// sheet1.setRowView(9, 3300);
// sheet1.setRowView(10, 3300);
// sheet1.setRowView(11, 3300);
// sheet1.setRowView(12, 3300);
// sheet1.setRowView(13, 3300);
// sheet1.setRowView(15 + size, 2000);
workbook.write();
workbook.close();
}
File[] listFiles = dir.listFiles();
for (int i = 0; i < listFiles.length; i++) {
ZipUtils.doCompress(listFiles[i], outZ);
response.flushBuffer();
}
} catch (Exception e) {
e.printStackTrace();
promptMessage.addException(e);
} finally {
try {
outZ.close();
} catch (IOException e) {
e.printStackTrace();
}
File[] listFiles = dir.listFiles();
for (File file : listFiles) {
file.delete();
}
dir.delete();
}
}
ZipUtils类代码:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;
/**
* 压缩文件工具类
*/
public class ZipUtils {
public static void doCompress(String srcFile, String zipFile) throws Exception {
doCompress(new File(srcFile), new File(zipFile));
}
/**
* 文件压缩
* @param srcFile 目录或者单个文件
* @param destFile 压缩后的ZIP文件
*/
public static void doCompress(File srcFile, File destFile) throws Exception {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(destFile));
if(srcFile.isDirectory()){
File[] files = srcFile.listFiles();
for(File file : files){
doCompress(file, out);
}
}else {
doCompress(srcFile, out);
}
}
public static void doCompress(String pathname, ZipOutputStream out) throws IOException{
doCompress(new File(pathname), out);
}
public static void doCompress(File file, ZipOutputStream out) throws IOException{
if( file.exists() ){
byte[] buffer = new byte[1024];
FileInputStream fis = new FileInputStream(file);
out.putNextEntry(new ZipEntry(file.getName()));
int len = 0 ;
// 读取文件的内容,打包到zip文件
while ((len = fis.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
out.setEncoding("gbk");
out.flush();
out.closeEntry();
fis.close();
}
}
}
前台使用Form提交方式:
// 导出待入库项目
exportPendingManageLibProjs : function() {
// 获取被选中的项目
var selectedRows = jkt.table.getSelectedRows();
if (selectedRows.length == 0) {
jkt.dialogError("请选择要导出的项目");
return;
}
var url = "/ppp/projectAudit/exportPendingManageLibProjs.do?TT_TOKEN=" + jkt.getCookie("TT_TOKEN");
$("#selectedRows").val(JSON.stringify(selectedRows));
$("#myExportManageLibForm").attr("action", jkt.realPath(url));
$("#myExportManageLibForm").submit();
}