java-导出数据到excle表格（动态表头和静态表头）
两种类型（1.动态生成列数和列名2.自定义列名）1.动态表头（把需要的实体类换成自己的就行了）@RequestMapping(value = “/historyExport”)public String historyExport(HttpServletResponse response, String ncode, String from, String to, HttpServletReque
·
两种类型（1.动态生成列数和列名 2.自定义列名）
1.动态表头（把需要的实体类换成自己的就行了）
@RequestMapping(value = “/historyExport”)
public String historyExport(HttpServletResponse response, String ncode, String from, String to, HttpServletRequest request) throws IOException {
if (StringUtils.isBlank(ncode)) {
log.error("设备号为空");
return "redirect:../index";
}
if (StringUtils.isBlank(from)) {
log.error("起始时间为空");
return "redirect:../index";
}
if (StringUtils.isBlank(to)) {
log.error("结束时间为空");
return "redirect:../index";
}
//验证设备是否属于用户。
SysUserBean user = ControllerHelper.getInstance(dao).getLoginUser();
if (user == null) {
log.error("用户未登录");
return "redirect:../doLogin";
}
if (user.getLevel() > 0) {
UserDeviceBean bean = dao.fetch(UserDeviceBean.class, Cnd.where("user_name", "=", user.getUserName()).and("ncode", "=", ncode).and("is_del", "=", "0"));
if (bean == null) {
throw new ServiceException("权限不足");
}
}
Timestamp fromTime = Timestamp.valueOf(from);
Timestamp toTime = Timestamp.valueOf(to);
Timestamp timestamp = new Timestamp(System.currentTimeMillis() - (6 * 30 * 24 * 60 * 60 * 1000l));
DeviceBean deviceBean = dao.fetch(DeviceBean.class, ncode);
if (deviceBean == null) {
throw new ServiceException("设备不存在");
}
if (fromTime.getTime() < timestamp.getTime()) {
fromTime = timestamp;
}
if (deviceBean.getCreatTime() != null && fromTime.getTime() < deviceBean.getCreatTime().getTime()) {
fromTime = deviceBean.getCreatTime();
}
Condition cnd = Cnd.where("Drecord_time", ">", fromTime).and("Drecord_time", "<", toTime).and("Dcode", "=", ncode).desc("Did");
List<NetDevicedata> list = SplitTableHelper.queryForDate(NetDevicedata.class, cnd, fromTime, toTime, 1, 50000);
// Sql sql = Sqls.create(“SELECT a.Nname,a.Ncode,a.Nserson_type,b.dsensor_data Nsensor_data,b.drecord_time Nrecord_time FROM netdevicedata b,network a where b.drecord_time>= @from and b.drecord_time<= @to and a.ncode=b.dcode and a.ncode=@ncode”);
// sql.params().set(“from”, fromtime);
// sql.params().set(“to”, totime);
// sql.params().set(“ncode”, ncode);
// this.sqlCallBack(sql);
// dao.execute(sql);
// List list = sql.getList(DeviceBean.class);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(ncode + “历史数据”);
String fileName = ncode + “历史数据”;//设置要导出的文件的名字
//新增数据行，并且设置单元格数据
int rowNum = 1;
Cookie[] cookies = request.getCookies();
String language = "";
if (cookies != null) {
for (Cookie cookie : cookies) {
String name = cookie.getName();
if (Constant.COOKIE_LANG.equals(name)) {
language = cookie.getValue();
break;
}
}
}
int headerCount = 4;
String[] typeArr = {};
if (!StringUtils.isEmpty(deviceBean.getType())) {
typeArr = deviceBean.getType().split("/");
headerCount += typeArr.length * 2;
}
String[] headers = new String[headerCount];
if (language.equals("en")) {
headers[0] = "Number";
headers[1] = "Device Name";
headers[2] = "Device Number";
headers[3] = "Receive Time";
int j = 0;
try {
for (int i = 4; i < headers.length; i += 2) {
if (j < typeArr.length) {
String enHeader = "";
SensorTypeBean sensorTypeBean = dao.fetch(SensorTypeBean.class, Cnd.where("tname", "=", typeArr[j].trim()));
if (sensorTypeBean != null) {
if (StringUtils.isBlank(sensorTypeBean.getEnName())) {
enHeader = getEnHeaderStr(typeArr[j].trim());
sensorTypeBean.setEnName(enHeader);
dao.update(sensorTypeBean);
} else {
enHeader = sensorTypeBean.getEnName();
}
} else {
enHeader = getEnHeaderStr(typeArr[j].trim());
}
headers[i] = enHeader;
headers[i + 1] = "unit";
j++;
}
}
} catch (Exception e) {
log.warn(e.getMessage());
}
} else {
headers[0] = "序号";
headers[1] = "设备名称";
headers[2] = "设备编号";
headers[3] = "接收时间";
int j = 0;
for (int i = 4; i < headers.length; i += 2) {
if (j < typeArr.length) {
headers[i] = typeArr[j];
headers[i + 1] = "单位";
j++;
}
}
}
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
cell.getCellStyle().setAlignment(HorizontalAlignment.CENTER);
switch (i) {
case 1:
cell.getSheet().setColumnWidth(i, 5000);
break;
case 2:
cell.getSheet().setColumnWidth(i, 5000);
break;
default:
cell.getSheet().setColumnWidth(i, 3000);
}
}
//在表中存放查询到的数据放入对应的列
for (NetDevicedata netDevicedata : list) {
HSSFRow row1 = sheet.createRow(rowNum);
String time = netDevicedata.getDrecordTime().toString();
row1.createCell(0).setCellValue(rowNum);
row1.createCell(1).setCellValue(deviceBean.getName());
row1.createCell(2).setCellValue(netDevicedata.getDeviceNumber());
row1.createCell(3).setCellValue(time.substring(0, time.lastIndexOf(".")));
String[] datas = {};
if (!StringUtils.isEmpty(deviceBean.getType())) {
datas = netDevicedata.getSensorData().split("\\|");
int j = 0;
for (int i = 4; i < headers.length; i += 2) {
if (j < typeArr.length) {
try {//改模越界问题
String[] datasStr = datas[j].trim().split("\\s+");
row1.createCell(i).setCellValue(datasStr[0]);
if (datasStr.length < 2) {
if (language.equals("zh")) {
row1.createCell(i + 1).setCellValue("无");
} else {
row1.createCell(i + 1).setCellValue("none");
}
} else {
row1.createCell(i + 1).setCellValue(datasStr[1]);
}
} catch (Exception e) {
// log.info(e.toString(),e);
}
j++;
}
}
}
rowNum++;
}
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“application/octet-stream”);
response.setHeader(“Content-disposition”, “attachment;filename=” + new String(fileName.getBytes(“gbk”), “iso8859-1”) + “.xls”);
//response.setHeader(“Content-disposition”, “attachment;filename=” + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
return null;
}
private String getEnHeaderStr(String cnHeaderStr) throws Exception {
String enHeader = TranslationUtils.getTranslationStr(cnHeaderStr);
if (StringUtils.isBlank(enHeader)) {
return cnHeaderStr;
}
return enHeader;
}
public static String getAnalysisData(String type, String value) {
if (StringUtils.isEmpty(value)) {
return "";
}
String[] types = {};
if (!StringUtils.isEmpty(type)) {
types = type.split("/");
}
String[] datas = value.split("\\|");
String data = "";
for (int j = 0; j < datas.length; j++) {
if (types.length > j) {
data += types[j].replace(" ", "") + "·" + datas[j].replace(" ", "");
} else {
data += datas[j];
}
if (j < datas.length - 1) {
data += " | ";
}
}
return data;
}
效果图
静态表头，这个就比较简单了，需要几列自己设置
@RequestMapping(value = "/historyExport")
@ResponseBody
public String historyExport(HttpServletResponse response, HttpServletRequest request) throws IOException {
//验证设备是否属于用户。
SysUserBean user = ControllerHelper.getInstance(dao).getLoginUser();
if (user == null) {
log.error("用户未登录");
return "redirect:../doLogin";
}
List<DeviceBean> deviceList=new ArrayList<>();
List<UserDeviceBean> list1 = dao.query(UserDeviceBean.class, Cnd.where("user_name", "=", user.getUserName()).and("is_del", "=", "0"));
for (int i=0;i<list1.size();i++){
DeviceBean deviceBean=dao.fetch(DeviceBean.class,Cnd.where("Ncode","=",list1.get(i).getDeviceNumber()));
if (null!=deviceBean){
deviceList.add(deviceBean);
}
}
// Sql sql = Sqls.create("SELECT a.Nname,a.Ncode,a.Nserson_type,b.dsensor_data Nsensor_data,b.drecord_time Nrecord_time FROM netdevicedata b,network a where b.drecord_time>= @from and b.drecord_time<= @to and a.ncode=b.dcode and a.ncode=@ncode");
// sql.params().set("from", fromtime);
// sql.params().set("to", totime);
// sql.params().set("ncode", ncode);
// this.sqlCallBack(sql);
// dao.execute(sql);
// List<DeviceBean> list = sql.getList(DeviceBean.class);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(user.getUserName() + "用户设备历史数据");
String fileName = user.getUserName() + "设备数据";//设置要导出的文件的名字
//新增数据行，并且设置单元格数据
int rowNum = 1;
Cookie[] cookies = request.getCookies();
String language = "";
if (cookies != null) {
for (Cookie cookie : cookies) {
String name = cookie.getName();
if (Constant.COOKIE_LANG.equals(name)) {
language = cookie.getValue();
break;
}
}
}
int headerCount = 1;
String typeArr = "";
if (null!=(deviceList)) {
for (DeviceBean deviceBean:deviceList){
typeArr = deviceBean.getType();
}
}
String[] headers = new String[6];
if (language.equals("en")) {
headers[0] = "Number";
headers[1] = "Device Name";
headers[2] = "Device Number";
headers[3] = "Receive Time";
headers[4] = "Receive Data";
headers[5] = "unit";
} else {
headers[0] = "序号";
headers[1] = "设备名称";
headers[2] = "设备编号";
headers[3] = "接收时间";
headers[4] = "接收数据";
headers[5] = "单位";
}
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
cell.getCellStyle().setAlignment(HorizontalAlignment.CENTER);
switch (i) {
case 1:
cell.getSheet().setColumnWidth(i, 5000);
break;
case 2:
cell.getSheet().setColumnWidth(i, 5000);
break;
default:
cell.getSheet().setColumnWidth(i, 3000);
}
}
//在表中存放查询到的数据放入对应的列
for (DeviceBean deviceBean1 : deviceList) {
HSSFRow row1 = sheet.createRow(rowNum);
String time = deviceBean1.getTime().toString();
row1.createCell(0).setCellValue(rowNum);
row1.createCell(1).setCellValue(deviceBean1.getName());
row1.createCell(2).setCellValue(deviceBean1.getDeviceNumber());
row1.createCell(3).setCellValue(time.substring(0, time.lastIndexOf(".")));
row1.createCell(4).setCellValue(deviceBean1.getData());
row1.createCell(5).setCellValue(deviceBean1.getType());
String[] datas = {};
rowNum++;
}
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls");
//response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
return null;
}
private String getEnHeaderStr(String cnHeaderStr) throws Exception {
String enHeader = TranslationUtils.getTranslationStr(cnHeaderStr);
if (StringUtils.isBlank(enHeader)) {
return cnHeaderStr;
}
return enHeader;
}
效果图：
技术共进，成长同行——讯飞AI开发者社区
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)