压缩包里面excel 导出 （跨行跨列）

压缩包里面excel 导出 （跨行跨列）


创建好 excel模板  直接读取内容即可的方法

1、
List content = new ArrayList();
InputStream istream = null;//创建输入流
String templateFile = null;
String parentPath = request.getSession().getServletContext().getRealPath("/") + "/WEB-INF/templates/";


//获取根据sql查询出来的结果
List result = this.xxdjShengzsfcdjService.getListBySql(sql.toString());

//循环结果添加到content内容里面
Object[] obj = new Object[36];
for (int i = 0; i < result.size(); i++) {
obj = (Object[]) result.get(i);
String[] str = new String[36];
str[0] = obj[0] == null ? "" : obj[0].toString();
str[1] = obj[1] == null ? "" : obj[1].toString();
content.add(str);
}

//从session中获取当前登录对象

XtglYongh self = (XtglYongh) request.getSession().getAttribute(
"userInfo");


2、根据查询的种类不同 获得的excel 模板也不同
if(xxcxShifcxxcxtjForm.getFenwclss().equals("0")){
templateFile = "szc_template_all.xls";
}else  if(xxcxShifcxxcxtjForm.getFenwclss().equals("1")){
templateFile = "szc_template_fwcl.xls";


3、将模板内容写入到 输入流
istream = new FileInputStream(parentPath+templateFile);


//创建excel 文件  self.getYonghm()是excel的头部
CreateExcelFile cef = new CreateExcelFile(content,name,  self.getYonghm(),istream,response.getOutputStream() ,types0);

fileName = cef.getZipExcelFileName() + ".zip";


//添加文件名称
response.addHeader("Content-Disposition","attachment;filename=\"" + fileName + "\"");


//self.getYonghm()：当前用户  ；name：表头
cef.getZipExcelFile2(self.getYonghm(),name);



javabean : 
CreateExcelFile:


private List list;


private String reportTitle;


private OutputStream outputStream;

private InputStream inputStream;


private HttpServletRequest request;


private HttpServletResponse response;


private String userName;

private int max_row=59997;

private String[] types;

//通过构造 创建对象
public CreateExcelFile(List content, String name,String yonghm,
InputStream istream,OutputStream ostream, String[] types2) {
this.list=content;
this.reportTitle = name;
this.userName=yonghm;
this.inputStream=istream;
this.outputStream = ostream;
this.types=types2;
}


//得到zip压缩包文件的名字
public String getZipExcelFileName() {
return ChineseToUnicode(reportTitle + this.getCurrentDate("yyyyMMdd"));
}
//转编码格式
private static String ChineseToUnicode(String s) {
try {
String newstring = null;
newstring = java.net.URLEncoder.encode(s,"UTF-8"); 
return newstring;
} catch (UnsupportedEncodingException e) {
return s;
}
}




//获得zip压缩包里面excel的文件
public String getZipExcelFile2(String userName,String name) {
String zipFileName = null;
if (list == null || list.size() < 1)
return zipFileName;
if (reportTitle == null || "".equals(reportTitle))
reportTitle = "Sheet1";
HSSFWorkbook wb = null;
if (USE_TEMPLATE) {
try {
wb = this.getHSSFWorkbookByTemplate(userName,name);
} catch (IOException e1) {
return null;
}
} else {
wb = this.getHSSFWorkbook();
}
zipFileName = this.getZipExcel(wb);
String xlsFileName = zipFileName.substring(0, zipFileName.indexOf("."))
+ ".xls";


java.io.FileInputStream fis = null;
OutputStream os = null;
if (this.outputStream != null)
os = this.outputStream;
else
try {
os = this.response.getOutputStream();
} catch (IOException e1) {
return null;
}


try {
fis = new java.io.FileInputStream(zipFileName);
byte[] b = new byte[2048];
int i = 0;
while ((i = fis.read(b)) > 0) {
os.write(b, 0, i);
}
} catch (Exception e) {
// TODO Auto-generated catch block
// log.error("Zip输出流错误：" + this.getClass().getName());
return null;
} finally {
try {
if (fis != null)
fis.close();
if (os != null) {
os.flush();
os.close();
os=null;
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


new File(zipFileName).delete();
new File(xlsFileName).delete();
return zipFileName;
}






//userName是当前登录对象名字 、name 是表头
private HSSFWorkbook getHSSFWorkbookByTemplate(String userName,String name) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook(inputStream);
HSSFRow row;
HSSFSheet sheet = wb.getSheetAt(0);
int rowStart = 4;
int count = list.size();
sheet.getRow(0).getCell(0).setCellValue(name);
sheet.getRow(1).getCell(1).setCellValue(DateUtils.format(new Date(),"yyyy-MM-dd"));
sheet.getRow(1).getCell(3).setCellValue(userName);
sheet.getRow(1).getCell(5).setCellValue(count);
for (int i = 0; i < count; i++) {
Object[] obj = (Object[]) list.get(i);
if (list.get(i)!=null){
row = sheet.createRow(rowStart++);// 建立新row
HSSFCellStyle cs = wb.createCellStyle();   
HSSFFont font = wb.createFont(); 
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);   
font.setFontName("仿宋_GB2312");   
font.setFontHeightInPoints((short) 10); 
cs.setFont(font);   
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中   
cs.setWrapText(true);//自动换行        
cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框    
cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框    
cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 

for (int j = 0; j < obj.length; j++) {
HSSFCell cell = row.createCell(j);// 建立新cell
if (obj[j] != null&&!"".equals(obj[j]))
if(types!=null&&types.length>0){
if("string".equals(types[j])){
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(obj[j].toString());
}
else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(Float.parseFloat(obj[j].toString()));
}
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(obj[j].toString());
}
cell.setCellStyle(cs);
cell = null;
}
}
}
return wb;
}