用jxl.jar包做成的excel导出在tomcat下跑完成正常,但部署到weblogic上会报错,文件错误,某些数字格式可能丢失
jxl.jar只支持到office 03版本,如果出现上述的错误,可能是你上传的模板 有 问题,最好用金山办公的execl保存成03以下的版本,不要用office来保存成03版本,具体原因,不是很清楚,不过这方法很有效果
下面是用jxl.jar包做成的产品目录导出的代码:
public void productDirectoryDownLoadExecl(HttpServletRequest request,
HttpServletResponse response) throws Exception {
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename="
+ java.net.URLEncoder.encode("产品目录导出.xls", "UTF-8"));// toUtf8String("供应商评估明细模板.xls"));//
// 设定输出文件头
response.setContentType("application/x-msdownload;charset=utf-8");// 定义输出类型
ArrayList productDirs = new ArrayList();
productDirs = (ArrayList) DataBaseUtil
.getHibernateTemplate()
.find("from PlProductDirectoryVOImpl where parentId='0' order by productDirectorySequence");
File inputFile = new File(
request.getSession().getServletContext().getRealPath("/")
+ "/business/template/download/product_library_product_directoryDB.xls"); // 读取模板
Workbook wrb = Workbook.getWorkbook(inputFile);
WritableWorkbook wwb = Workbook.createWorkbook(os, wrb);
WritableSheet ws = wwb.getSheet(0);
WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.BOLD);
WritableCellFormat wc = new WritableCellFormat(wf);
WritableCellFeatures wcf = new WritableCellFeatures();
for (int i = 3; i < 11; i++) {
ws.setColumnView(i, 16);
}
for (int i = 0; i < 3; i++) {
ws.setColumnView(i, 10);
}
wc.setAlignment(Alignment.CENTRE);
wc.setVerticalAlignment(VerticalAlignment.CENTRE);
addCellToSheet(ws, 0, 0, "序号", wc, wcf);
addCellToSheet(ws, 1, 0, "物资编码", wc, wcf);
addCellToSheet(ws, 2, 0, "物资类型", wc, wcf);
addCellToSheet(ws, 3, 0, "产品大类编码", wc, wcf);
addCellToSheet(ws, 4, 0, "产品大类名称", wc, wcf);
addCellToSheet(ws, 5, 0, "产品中类编码", wc, wcf);
addCellToSheet(ws, 6, 0, "产品中类名称", wc, wcf);
addCellToSheet(ws, 7, 0, "产品小类编码", wc, wcf);
addCellToSheet(ws, 8, 0, "产品小类名称", wc, wcf);
addCellToSheet(ws, 9, 0, "产品细类编码", wc, wcf);
addCellToSheet(ws, 10, 0,"产品细类名称", wc, wcf);
PlProductDirectoryVOImpl plDir = null;
int j = 1;
WritableFont wf0 = new WritableFont(WritableFont.createFont("宋体"), 11,
WritableFont.NO_BOLD);
WritableCellFormat wc0 = new WritableCellFormat(wf0);
wc0.setAlignment(Alignment.CENTRE);
wc0.setVerticalAlignment(VerticalAlignment.CENTRE);
for (int i = 0; i < productDirs.size(); i++) {
plDir = productDirs.get(i);
if (plDir.getProductDirectoryLevel() == 1) {
WritableFont wf1 = new WritableFont(
WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
WritableCellFormat wc1 = new WritableCellFormat(wf1);
wc1.setAlignment(Alignment.CENTRE);
wc1.setVerticalAlignment(VerticalAlignment.CENTRE);
addCellToSheet(ws, 2, j, plDir.getProductDirectoryName(), wc1,
wcf);
addCellToSheet(ws, 1, j, plDir.getProductDirectoryNumber(),
wc1, wcf);
addCellToSheet(ws, 0, j, j + "", wc0, wcf);
}
String sql1 = "select a.product_directory_id from pl_product_directory a start with a.product_directory_id='"
+ plDir.getProductDirectoryId()
+ "' connect by prior a.product_directory_id=a.parent_id and a.product_directory_id<>'"
+ plDir.getProductDirectoryId()
+ "' order by product_directory_level ";
List strList = (List)DataBaseUtil.findBySQL(sql1, null, -1, -1);
//得到每个物资类下面所有的目录
List list1 =this.getPlDir(strList);
Map> map = getMap(list1);
//得到第二层的目录
List list2 =map.get(2);
if(list2.size()>0){
//循环二层目录
for (int k = 0; k
PlProductDirectoryVOImpl vo = list2.get(k);
List temp2 = this.getDirList(list1, vo.getProductDirectoryId());
addCellToSheet(ws, 4, j, vo.getProductDirectoryName(), wc0,
wcf);
addCellToSheet(ws, 3, j, vo.getProductDirectoryNumber(),
wc0, wcf);
addCellToSheet(ws, 0, j, j + "", wc0, wcf);
j++;
//得到二层目录 下的第三层
if(temp2!=null&&temp2.size()>0){
for (int l = 0; l
PlProductDirectoryVOImpl vo1 = temp2.get(l);
List temp3 = this.getDirList(list1, vo1.getProductDirectoryId());
addCellToSheet(ws, 6, j, vo1.getProductDirectoryName(), wc0,
wcf);
addCellToSheet(ws, 5, j, vo1.getProductDirectoryNumber(),
wc0, wcf);
addCellToSheet(ws, 0, j, j + "", wc0, wcf);
j++;
if(temp3!=null&&temp3.size()>0){
//得到第四层
for (int m = 0; m < temp3.size(); m++) {
PlProductDirectoryVOImpl vo2 = temp3.get(m);
List temp4 = this.getDirList(list1, vo2.getProductDirectoryId());
addCellToSheet(ws, 8, j, vo2.getProductDirectoryName(), wc0,
wcf);
addCellToSheet(ws, 7, j, vo2.getProductDirectoryNumber(),
wc0, wcf);
addCellToSheet(ws, 0, j, j + "", wc0, wcf);
j++;
if(temp4!=null&&temp4.size()>0){
//得到第五层
for (int n = 0; n < temp4.size(); n++) {
addCellToSheet(ws, 10, j, temp4.get(n).getProductDirectoryName(), wc0,
wcf);
addCellToSheet(ws, 9, j, temp4.get(n).getProductDirectoryNumber(),
wc0, wcf);
addCellToSheet(ws, 0, j, j + "", wc0, wcf);
j++;
}
}
}
}
}
}
}
}
}
wwb.write();
wwb.close();
os.flush();
os.close();
}
private List getPlDir(List strList) throws Exception {
List l = new ArrayList();
for (int i = 0; i < strList.size(); i++) {
l.add(this.findByPrimaryKey(strList.get(i)));
}
return l;
}
// 根据传过来的ID得到该目录下的子目录
private List getDirList(
List list2, String parentId) {
ArrayList l = new ArrayList();
for (int i = 0; i < list2.size(); i++) {
PlProductDirectoryVOImpl vo = list2.get(i);
if (vo.getParentId().equals(parentId)) {
l.add(vo);
}
}
return l;
}
private Map> getMap(
List list) {
Map> map = new HashMap>();
List list2 = new ArrayList();
List list3 = new ArrayList();
List list4 = new ArrayList();
List list5 = new ArrayList();
for (int i = 0; i < list.size(); i++) {
PlProductDirectoryVOImpl vo = list.get(i);
if(vo.getProductDirectoryLevel()==2){
list2.add(vo);
}
if(vo.getProductDirectoryLevel()==3){
list3.add(vo);
}
if(vo.getProductDirectoryLevel()==4){
list4.add(vo);
}
if(vo.getProductDirectoryLevel()==5){
list5.add(vo);
}
}
map.put(2, list2);
map.put(3, list3);
map.put(4, list4);
map.put(5, list5);
return map;
};
public Label addCellToSheet(WritableSheet wsheet, int col, int row,
String content, WritableCellFormat wcFM, WritableCellFeatures wcFT)
throws Exception {
Label newCell = null;
if (wcFM != null) {
newCell = new Label(col, row, content, wcFM);
} else {
newCell = new Label(col, row, content);
}
if (wcFT != null) {
newCell.setCellFeatures(wcFT);
}
wsheet.addCell(newCell);
return newCell;
}