压缩包里面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);
}
"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;
}
创建好 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;
}