压缩包里面excel 导出 (跨行跨列)
来源:互联网 发布:手机短信轰炸机源码 编辑:程序博客网 时间:2024/05/22 17:05
压缩包里面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;
}
0 0
- 压缩包里面excel 导出 (跨行跨列)
- php 导出excel压缩包
- 数据导出-excel,excel压缩包
- 将EXCEL导出成ZIP压缩包
- excel导出之大量数据-导出压缩包
- 大于65535的数据GridView导出Excel压缩包
- (实用)导出word里面生成excel
- 导出Zip压缩Excel
- java 导出压缩包
- 导出压缩包
- java导出excel文件(压缩后导出)
- jeesite框架导出zip压缩包(包含一个excel文件和一张缩略图以及多个附件)
- 引用jar包导出excel(普通)
- java导出excel并压缩
- Java 让excel导出大量数据进行分表打成压缩包进行下载
- Java通过Spring MVC导出批量Excel文件压缩包,并弹出下载框
- 文件操作(文件过滤,压缩,导出到excel等)
- 下载excel、压缩包等
- iOS 地图定位
- android开发小技巧
- 分类、标注与回归
- AWT的基本应用
- 通信涉及的几个基础概念+串口通信的基本概念+串口通信的基本原理
- 压缩包里面excel 导出 (跨行跨列)
- Android实现获取短信验证码的功能以及自定义GUI短信验证
- Codeforces Round #346 (Div. 2)--A. Round House
- SecurCRT命令回显设置
- ssh 无密码登陆
- IOS折线统计图
- POJ 1019 数论基础题
- 通过url地址获取数据后将数据利用SpringMVC的@ResponseBody返回中文可能出现乱码地方
- iOS之隐藏/显示tabbar