servlet查询数据导出到excel

最新推荐文章于 2019-07-20 19:43:39 发布
最新推荐文章于 2019-07-20 19:43:39 发布
阅读量479 收藏
点赞数
分类专栏： servlet
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36925536/article/details/86513584
版权 
package com.zhuoer.qmaintance.servlet;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.zhuoer.qmaintance.beans.MaintenanceRecords;
import com.zhuoer.qmaintance.beans.Parts;
import com.zhuoer.qmaintance.beans.RepairRecordsInfo;
import com.zhuoer.qmaintance.utils.MaintenanceRecordsTools;
import com.zhuoer.qmaintance.utils.RepairRecordsTools;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**根据不同条件条件返回维修记录信息
 * Servlet implementation class QRepairRecordsInfo
 */
@WebServlet("/QRepairRecordsInfoSendExcel")
public class QRepairRecordsInfoSendExcel extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		response.setHeader("Access-Control-Allow-Origin", "*");//解决跨域问题
		
		String startTime=request.getParameter("startdate");
		String endTime=request.getParameter("enddate");
		String deviceNo=request.getParameter("deviceNo");
		String customerNo=request.getParameter("customerNo");
		String spage = request.getParameter("spage");
		String state=request.getParameter("state");
		String finish=request.getParameter("finish");
		int index=1;//行数
		
		//多条件查询
		String sql="select repairrecordsinfo.*,deviceinfo.name as deviceinfoname from deviceinfo,repairrecordsinfo where  deviceinfo.no=repairrecordsinfo.deviceNo";
		if(deviceNo!=null&& !deviceNo.equals(""))
		{
			sql+=" and repairrecordsinfo.deviceNo='"+deviceNo+"'";
		}
		if(customerNo!=null&& !customerNo.equals(""))
		{
			sql+=" and deviceinfo.customerNo='"+customerNo+"'";
		}
		
		if(startTime!=null&& !startTime.equals(""))
		{
			sql+=" and repairDate>='"+startTime+"'";
		}
		if(endTime!=null&& !endTime.equals(""))
		{
			sql+=" and repairDate<='"+endTime+"'";
		}
		if(state!=null && !state.equals(""))
		{
			sql+=" and state = '"+state+"'";
		}
		if(finish!=null && !finish.equals(""))
		{
			sql+=" and finish = '"+finish+"'";
		}
		
		//查询维修记录表中的数据
				ArrayList<Map<String,Object>> records = (ArrayList<Map<String,Object>>) RepairRecordsTools.executeQuary(sql);
				
				
				if(records.size()>0) {
					long subffix = new Date().getTime();
					File xlsFile = new File(request.getServletContext().getRealPath("/")+"file/维修记录表"+subffix+".xls");
					String[] arrs = {"设备编号","设备名称","审核状态","审核意见","报修日期",
							"接报时刻","开始日期","开始时刻","终了日期","终了时刻","接报人","作业区分","实施区分",
							"完成状态","保全修理时间","设备停机时间","保全人数","部位","故障原因",
							"处置方法","维修人","配件名称","配件数量","备注"};
					Label label = null;
					try {
						xlsFile.createNewFile();
						WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);
						WritableSheet sheet = workbook.createSheet("sheet1", 0);
						for(int i=0; i<arrs.length; i++) {
							label = new Label(i,0,arrs[i]);
							sheet.addCell(label);
						}
						for(int j=0;j<records.size(); j++) {
							
							
							String endstate="";
		            		if(records.get(j).get("state").equals("0"))
		            		{
		            			endstate="提交";
		            		}
		            		if(records.get(j).get("state").equals("1"))
		            		{
		            			endstate="审核通过";
		            		}
		            		if(records.get(j).get("state").equals("2"))
		            		{
		            			endstate="审核未通过";
		            		}
		            		if(records.get(j).get("state").equals("3"))
		            		{
		            			endstate="审核通过";
		            		}
		            		if(records.get(j).get("state").equals("4"))
		            		{
		            			endstate="审核未通过";
		            		}
		            		
							
							
							
		            		label = new Label(0,index, records.get(j).get("deviceNo")==null?"无":(records.get(j).get("deviceNo").toString()));
							sheet.addCell(label);
							label = new Label(1,index, records.get(j).get("deviceinfoname")==null?"无":(records.get(j).get("deviceinfoname").toString()));
							sheet.addCell(label);
							label = new Label(2,index,endstate);
							sheet.addCell(label);
							label = new Label(3,index,(records.get(j).get("auditOpinion1"))==null?"无":(records.get(j).get("auditOpinion1").toString()));
							sheet.addCell(label);
//							label = new Label(4,index,(records.get(j).get("auditOpinion2"))==null?"无":(records.get(j).get("auditOpinion2").toString()));
//							sheet.addCell(label);
							try {
								label = new Label(4,index, records.get(j).get("repairDate")==null?"无":(records.get(j).get("repairDate").toString().substring(0, 10)));
							} catch (Exception e) {
								label=new Label(4,index,"");
							}
							sheet.addCell(label);
							label = new Label(5,index,records.get(j).get("acceptance")==null?"无":(records.get(j).get("acceptance").toString()));
							sheet.addCell(label);
							try {
								label = new Label(6,index, records.get(j).get("startTime")==null?"无":(records.get(j).get("startTime").toString().substring(0, 10)));
							} catch (Exception e) {
								label = new Label(6,index,"");
							}
							sheet.addCell(label);
							try {
								label = new Label(7,index, records.get(j).get("startTime")==null?"无":(records.get(j).get("startTime").toString().substring(10)));
							} catch (Exception e) {
								label = new Label(7,index,"");
							}
							sheet.addCell(label);
							try {
								label = new Label(8,index, records.get(j).get("endTime")==null?"无":(records.get(j).get("endTime").toString().substring(0, 10)));
							} catch (Exception e) {
								label = new Label(8,index,"");
							}
							sheet.addCell(label);
							try {
								label = new Label(9,index, records.get(j).get("endTime")==null?"无":(records.get(j).get("endTime").toString().substring(10)));
							} catch (Exception e) {
								label = new Label(9,index,"");
							}
							sheet.addCell(label);
							label = new Label(10,index, records.get(j).get("acceptancePerson")==null?"无":(records.get(j).get("acceptancePerson").toString()));
							sheet.addCell(label);
							label = new Label(11,index,records.get(j).get("task")==null?"无":(records.get(j).get("task").toString()));
							sheet.addCell(label);
							label = new Label(12,index,records.get(j).get("implementationDistinction")==null?"无":(records.get(j).get("implementationDistinction").toString()));
							sheet.addCell(label);
							label = new Label(13,index, records.get(j).get("finish")==null?"无":(records.get(j).get("finish").toString()));
							sheet.addCell(label);
							label = new Label(14,index, records.get(j).get("maintenanceTime")==null?"无":(records.get(j).get("maintenanceTime").toString()));
							sheet.addCell(label);
							label = new Label(15,index, records.get(j).get("downTime")==null?"无":(records.get(j).get("downTime").toString()));
							sheet.addCell(label);
							label = new Label(16,index, records.get(j).get("repairmanNumber")==null?"无":(records.get(j).get("repairmanNumber").toString()));
							sheet.addCell(label);
							label = new Label(17,index, records.get(j).get("part")==null?"无":(records.get(j).get("part").toString()));
							sheet.addCell(label);
							label = new Label(18,index, records.get(j).get("failureCause")==null?"无":(records.get(j).get("failureCause").toString()));
							sheet.addCell(label);
							label = new Label(19,index, records.get(j).get("disposalMethod")==null?"无":(records.get(j).get("disposalMethod").toString()));
							sheet.addCell(label);
							label = new Label(20,index, records.get(j).get("repairman")==null?"无":(records.get(j).get("repairman").toString()));
							sheet.addCell(label);
							label = new Label(23,index, records.get(j).get("mark")==null?"无":(records.get(j).get("mark").toString()));
							sheet.addCell(label);
							if(records.get(j).get("parts")==null ||records.get(j).get("parts").equals(""))
							{
								label = new Label(21,index, "无");
								sheet.addCell(label);
								index++;
							}
							else
							{
								String[] split = records.get(j).get("parts").toString().split(",");//把每个零件和数量切成数组
								for (int i = 0; i < split.length; i++) {
									String[] split2 = split[i].split("-");
									Parts parts = new Parts(split2[0],split2[1]);
									label = new Label(21,index, parts.getName());//配件名
									sheet.addCell(label);
									label = new Label(22,index, parts.getCount());//配件名
									sheet.addCell(label);
									index++;//全局变量
									
								}
							}
							
							
						}
						workbook.write();
						workbook.close();
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					response.getWriter().println("file/维修记录表"+subffix+".xls");
				}
				else {
					response.getWriter().println("empty");
				}
	}

}

 

百味皆苦
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
servlet导入导出excel
04-16
简单的jsp+servlet实现把xlsx(excel，07版之前为xlsx,之后为xls,xls用HSSFWorkBook)数据导入到mysql，并导出
利用Servlet导出Excel
01-04 983
因为Excel可以打开HTML文件，因此可以利用页面的Form表单把页面中的table内容提交给Servlet，然后后台把提交上来的table内容转换成文件流的形式，并以下载的形式转给客户端，由此完成导出Excel功能。 效果：   Java代码 package com.mr;   import java.io.IOException; import java.io.Prin
java Servlet导出数据到Excel文件
weixin_30314813的博客
03-04 466
package com.lbc.excel.servlet; import java.io.IOException; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.ServletOutputStream...
servlet导出Excel
weixin_30542079的博客
04-18 77
package khservlet; import java.io.IOException;import java.io.PrintWriter;import java.sql.*; import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.Http...
使用POI数据导出到Excel
11-05
本篇文章将详细讲解如何利用Apache POI库将数据导出到Excel。 首先，我们需要了解Apache POI的基本概念。POI提供了一组API，如HSSF（处理.xls文件）和XSSF（处理.xlsx文件），它们是专门用来操作Excel的。HSSF对应...
JSP SERVLET 导出数据库内容到EXCEL
08-20
本篇主要探讨如何利用JSP和Servlet将数据库中的数据导出到Excel文件，这对于数据分析、报告生成等场景非常实用。 首先，我们需要了解JSP和Servlet的基本概念。JSP是一种动态网页技术，它允许开发者在HTML页面中嵌入...
将数据导出到Excel
03-27
将数据导出到Excel源代码及方法：response.setContentType("application/vnd.ms-excel");//响应正文的MIME类型，表示Excel response.addHeader("Content-Disposition", "attachment;filename=logininfo.xls"); ...
java实现JSP表格数据导出到EXCEL文件
01-07
在Java Web开发中，将JSP表格数据导出到Excel文件是一种常见的需求，这有助于用户对大量数据进行处理、分析和存储。以下是如何使用Java来实现这一功能的关键知识点： 1. **JSP（JavaServer Pages）**: JSP是Java的...
servlet 实现excel 导出
zs_TNT的专栏
02-10 354
package com.test; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletExcep...
servlet+api导出excel
08-19
servlet+api导出excel，从Oracle中查询数据存入List泛型集合，从List泛型集合中筛选出符合条件的数据，存入泛型集合，导出excel，并且提供下载
将任意sql查询结果导出只excel
08-26
此程序使用java编写，采用swing编写界面，可以将任意sql查询语句的查询结果导入excel中
servlet导出数据为excel文件
休对故人思故国，且将新火试新茶。诗酒趁年华
07-20 310
导包 在项目中导入poi的jar包 在servlet中调用 //到处数据为xls格式 private static void createExcel(List<Duty> list,HttpServletResponse response) { // 创建一个Excel文件 HSSFWorkbook workbook = new HSSF...
servlet Excel 导出
佳乐之窗
11-17 154
  servlet生成Excel格式的数据：首先导入jxl.jar包。 在servlet中处理： public void doPost(HttpServletRequrequest, HttpServletResponse response) throws ServletException, IOException { List theDat...
最近用servlet做了一个简单导出excel程序
fyc2008fyc的专栏
09-23 347
package com.software.cntrrep.EDI; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import javax.servlet.ServletException; import javax.servlet.http.HttpSer...
excel导出servlet
csdn_12345678910的博客
06-18 347
首先导入jar包c3p0-0.9.2.1.jarcommons-fileupload-1.2.2.jarmchange-commons-java-0.2.3.4.jarmysql-connector-java-5.0.5lombok-1.16.20.jarpoi-3.17.jarueditor-mini.jarweb.xml文件：&lt;?xml version="1.0" encoding="U...
用servlet 实现excel 导出到浏览器
最新发布
06-06
要使用 Servlet 实现 Excel 导出到浏览器中，可以按照以下步骤进行： 1. 在 Servlet 中创建一个 POJO 类，用于存储 Excel 数据。 ``` public class ExcelData { private String name; private int age; private...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值