**
实现在线生成并下载excel
**
pom文件
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>package com.zcx.framework.dps.logquery.controller;
import com.zcx.framework.dps.logquery.service.LogQueryService;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.*;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;
/**
* @author dyh
* @create 2018-07-14 下午8:20
* @desc excle表格功能编写
**/
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Resource
private LogQueryService logQueryService;
/**
* 下载文件
*
* @return
*/
@RequestMapping({"/download"})
public List<Map<String, Object>> download() throws IOException {
//这个是从接口查询的数据
List<Map<String, Object>> logQuery = logQueryService.findLogQuery();
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = requestAttributes.getResponse();
HttpServletRequest request = requestAttributes.getRequest();
// 文件名
String filename = "日志查询记录.xls";
try {
// 写到服务器上
String path = request.getSession().getServletContext().getRealPath("") + "/" + filename;
// 写到服务器上（这种测试过，在本地可以，放到linux服务器就不行）
//String path = this.getClass().getClassLoader().getResource("").getPath()+"/"+filename;
File name = new File(path);
// 创建写工作簿对象
WritableWorkbook workbook = Workbook.createWorkbook(name);
// 工作表
WritableSheet sheet = workbook.createSheet("sheet", 0);
// 设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat = new WritableCellFormat(font);
// 设置背景颜色;
cellFormat.setBackground(Colour.WHITE);
// 设置边框;
cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
// 设置文字居中对齐方式;
cellFormat.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
// 分别给1,5,6列设置不同的宽度;
sheet.setColumnView(0, 15);
sheet.setColumnView(4, 60);
sheet.setColumnView(5, 35);
// 给sheet电子版中所有的列设置默认的列的宽度;
sheet.getSettings().setDefaultColumnWidth(20);
// 给sheet电子版中所有的行设置默认的高度，高度的单位是1/20个像素点,但设置这个貌似就不能自动换行了
// sheet.getSettings().setDefaultRowHeight(30 * 20);
// 设置自动换行;
cellFormat.setWrap(true);
// 单元格
Label label0 = new Label(0, 0, "id", cellFormat);
Label label1 = new Label(1, 0, "OPERATOR", cellFormat);
Label label2 = new Label(2, 0, "OPTTIME", cellFormat);
Label label3 = new Label(3, 0, "pname", cellFormat);
Label label4 = new Label(4, 0, "QUERYINFO", cellFormat);
sheet.addCell(label0);
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
sheet.addCell(label4);
// 给第二行设置背景、字体颜色、对齐方式等等;
WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
// 设置文字居中对齐方式;
cellFormat2.setAlignment(Alignment.CENTRE);
// 设置垂直居中;
cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat2.setBackground(Colour.WHITE);
cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
cellFormat2.setWrap(true);
// 记录行数
int n = 1;
// 查找所有地址
// List<Address> addressList = service.findAll();
if (logQuery != null && logQuery.size() > 0) {
// 遍历
//这里是遍历上面接口查询出来的数据
//我这里查询出来的是object,下面的lable中的字段需要的是string类型的,所以需要转型
for (Map<String, Object> map : logQuery) {
Object id = map.get("你需要查询的字段");
Object obj = map.get("你需要查询的字段");
String operator = obj.toString();
Object obj1 = map.get("你需要查询的字段");
String opttime = obj1.toString();
Object obj2 = map.get("你需要查询的字段");
String pname = obj2.toString();
Object obj3 = map.get("你需要查询的字段");
String queryinfo = obj3.toString();
Label lt0 = new Label(0, n, id + "", cellFormat2);
Label lt1 = new Label(1, n, operator, cellFormat2);
Label lt2 = new Label(2, n, opttime, cellFormat2);
Label lt3 = new Label(3, n, pname, cellFormat2);
Label lt4 = new Label(4, n, queryinfo, cellFormat2);
sheet.addCell(lt0);
sheet.addCell(lt1);
sheet.addCell(lt2);
sheet.addCell(lt3);
sheet.addCell(lt4);
n++;
}
}
//开始执行写入操作
workbook.write();
//关闭流
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
// 第六步，下载excel
OutputStream out = null;
try {
// 1.弹出下载框，并处理中文
/** 如果是从jsp页面传过来的话，就要进行中文处理，在这里action里面产生的直接可以用
* String filename = request.getParameter("filename");
*/
if (request.getMethod().equalsIgnoreCase("GET")) {
filename = new String(filename.getBytes("iso8859-1"), "utf-8");
}
response.addHeader("content-disposition", "attachment;filename="
+ java.net.URLEncoder.encode(filename, "utf-8"));
// 2.下载
out = response.getOutputStream();
String path3 = request.getSession().getServletContext().getRealPath("") + "/" + filename;
// inputStream：读文件，前提是这个文件必须存在，要不就会报错
InputStream is = new FileInputStream(path3);
byte[] b = new byte[4096];
int size = is.read(b);
while (size > 0) {
out.write(b, 0, size);
size = is.read(b);
}
out.close();
is.close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}实现生成excel保存到本地
package com.zcx.framework.dps.logquery.controller;
import com.zcx.framework.core.util.DecodeUtil;
import com.zcx.framework.dps.logquery.service.LogQueryService;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Controller
public class LogQueryController {
@Resource
private LogQueryService service;
@RequestMapping("GetLogQuery")
public List<Map<String, Object>> logquery (HttpServletResponse response) throws IOException {
List<Map<String, Object>> logQuery = service.findLogQuery();
//开始写入excel,创建模型文件头
String[] titleA = {"id","operator","opttime","pname","queryinfo"};
//创建Excel文件
//生成本地excel
File fileA = new File("D:\\TestFile.xls");
if(fileA.exists()){
//如果文件存在就删除
fileA.delete();
}
fileA.createNewFile();
//创建工作簿
//这里括号里面放的是本地路径, 如果网络下载的话,就将fileA替换为其他的
// ByteArrayOutputStream bos = new ByteArrayOutputStream();
WritableWorkbook workbookA = Workbook.createWorkbook(fileA);
//创建sheet
WritableSheet sheetA = workbookA.createSheet("sheet1", 0);
try {
Label labelA = null;
//设置列名
for (int i = 0; i < titleA.length; i++) {
labelA = new Label(i, 0, titleA[i]);
sheetA.addCell(labelA);
}
int j = 1;
//获取数据源
for (Map<String, Object> map : logQuery) {
Object id = map.get("id");
Object operator = map.get("OPERATOR");
Object opttime = map.get("OPTTIME");
Object pname = map.get("pname");
Object queryinfo = map.get("QUERYINFO");
Label label = new Label(0, j, "" + id);
sheetA.addCell(label);
labelA = new Label(1,j,""+operator);
sheetA.addCell(labelA);
labelA = new Label(2,j,""+opttime);
sheetA.addCell(labelA);
labelA = new Label(3,j,""+pname);
sheetA.addCell(labelA);
labelA = new Label(4,j,""+queryinfo);
sheetA.addCell(labelA);
j=j+1;
}
workbookA.write(); //写入数据
workbookA.close(); //关闭连接
} catch (Exception e) {
}
return logQuery;
}
}Java实现excel只在网络进行下载
package com.zcx.framework.ifms.LogQuery.controller;
import com.zcx.framework.ifms.LogQuery.service.LogQueryService;
import jxl.Workbook;
import jxl.write.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;
/**
* @author dyh
* @create 2018-07-14 下午8:20
* @desc excle表格功能编写
**/
@Controller
public class LogQueryController {
@Resource
private LogQueryService logQueryService;
@RequestMapping(value = "/ifms/LogQuery/logPage")
public String logPage() throws IOException {
return "/ifms/LogQuery/Download";
}
/**
* 下载文件
*
* @return
*/
@RequestMapping(value = "/ifms/LogQuery/DownloadFile")
public void download(HttpServletResponse response,String querydateX , String querydateY) throws IOException {
List<Map<String, Object>> logQuery = logQueryService.findLogQuery(querydateX , querydateY);
// 文件名
String filename = "日志查询记录.xls";
try {
response.setHeader("content-disposition", "attachment;filename="
+ java.net.URLEncoder.encode(filename, "utf-8"));
OutputStream os = response.getOutputStream();
// 创建写工作簿对象
WritableWorkbook workbook = Workbook.createWorkbook(os);
// 工作表
WritableSheet sheet = workbook.createSheet("sheet", 0);
// 单元格
Label label0 = new Label(0, 0, "日志记录ID");
Label label1 = new Label(1, 0, "账号");
Label label2 = new Label(2, 0, "姓名");
Label label3 = new Label(3, 0, "查询日期");
Label label4 = new Label(4, 0, "查询名称");
Label label5 = new Label(5, 0, "查询信息");
sheet.addCell(label0);
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
sheet.addCell(label4);
sheet.addCell(label5);
// 记录行数
int n = 1;
// 查找所有地址
// List<Address> addressList = service.findAll();
if (logQuery != null && logQuery.size() > 0) {
// 遍历
for (Map<String, Object> map : logQuery) {
Object id = map.get("id");
Object obj = map.get("OPERATOR");
String operator = obj.toString();
Object obj1 = map.get("NAME");
String name = obj1.toString();
Object obj2 = map.get("OPTTIME");
String opttime = obj2.toString();
Object obj3 = map.get("pname");
String pname = obj3.toString();
Object obj4 = map.get("QUERYINFO");
String queryinfo = obj4.toString();
Label lt0 = new Label(0, n, id + "");
Label lt1 = new Label(1, n, operator);
Label lt2 = new Label(2, n, name);
Label lt3 = new Label(3, n, opttime);
Label lt4 = new Label(4, n, pname);
Label lt5 = new Label(5, n, queryinfo);
sheet.addCell(lt0);
sheet.addCell(lt1);
sheet.addCell(lt2);
sheet.addCell(lt3);
sheet.addCell(lt4);
sheet.addCell(lt5);
n++;
}
}
//开始执行写入操作
workbook.write();
//关闭流
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
