如何在spring mvc hibernate中使用jxl将数据库内容下载导入excel

2014-05-14 07:54:48 326 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

病理图像处理个人手札（三）WSI & MIL 1.什么是WSI？ WSI全称是Whole Slide Image，也就是全视野数字切片，主要应用在病理学细胞图像领域。WSI 是利用数字扫描仪对传统的病理切片进行扫描，采集具有高分辨率的数字图像，再通过计算机将得到的碎片化图像进行无缝拼接整合，制作可视化数字图像的一项技术。与传统载玻片相比，很好地解决了传统的玻璃切片易损坏、易褪色、易丢片、检索困难的问题。一言以蔽之，WSI是对传统载玻片进行数字化。由于是全视野，因此它将包含多尺度的切片在一个文件上面。因此WSI将使用到金字塔模型满足其支持不同分辨率的阅读详情

后台用的框架，怎么才能用JXL把数据库中的统计日志记录下载保存为EXCEL文件呢。
求高手给个小例子指点下。。

...全文

SSH+JXL 下载 Excel 文档 SSH（Spring、Struts2、Hibernate）是Java领域常见的MVC架构，而JXL是一个流行的Java库，用于读写Excel文件。下面我们将详细探讨这些知识点。1. 立即下载

堕落的唐僧

Web 开发 81,108 成员 341,721 内容

+ 关注 + 加入社区

切换为时间正序

小红蚂蚁 2016-04-22

打赏
举报

楼主这个已经给你百分之八十了,不会改只能愿你自己了

堕落的唐僧 2014-05-19

打赏
举报

加点分，有好心人帮忙吗

堕落的唐僧 2014-05-18

打赏
举报

还是没解决啊

堕落的唐僧 2014-05-17

打赏
举报

哎。。。继续顶

堕落的唐僧 2014-05-16

打赏
举报

引用 14 楼 u010617434 的回复:

给你个我现在项目在用的


@SuppressWarnings("unchecked")
	@RequestMapping(value = "/huodongbm/export")
	public void exportOne(HttpServletRequest request,
			HttpServletResponse response, Model model) throws Exception {
		List<XsxmModel> list = new ArrayList<XsxmModel>();
		Map<String, Object> queryMap = (Map<String, Object>) request.getSession().getAttribute("map");
		list = xsxmService.querylistByQueryMap(queryMap);
		Map<HuodongModel, List<XsxmModel>> map = new HashMap<HuodongModel, List<XsxmModel>>();
		for (XsxmModel xsxm : list) {
			HuodongModel hd = xsxm.getHd();
			if (map.containsKey(hd)) {
				map.get(hd).add(xsxm);
			} else {
				List<XsxmModel> listtemp = new ArrayList<XsxmModel>();
				listtemp.add(xsxm);
				map.put(hd, listtemp);
			}
		}
		exportExcel(request, response, map);
	}

	@SuppressWarnings("unchecked")
	private void exportExcel(HttpServletRequest request,
			HttpServletResponse response, Map map) {
		String[] bt = new String[5];
		bt[0] = "活动";
		bt[1] = "项目";
		bt[2] = "学生";
		bt[3] = "报名人";
		bt[4] = "报名时间";
		// 设置导出excel的相关样式
		String fileName = "活动报名汇总.xls";
		response.setContentType("application/msexcel");// 定义输出类型
		try {
			fileName = new String(fileName.getBytes("gbk"), "ISO8859-1");// 解决中文
			// 文件名问题
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		response.setHeader("content-disposition", "attachment; filename="
				+ fileName);// 设定输出文件头
		try {
			WritableWorkbook wwb;
			OutputStream os = response.getOutputStream();
			wwb = Workbook.createWorkbook(os);
			WritableSheet ws = wwb.createSheet("活动报名汇总", 0);
			ws.getSettings().setVerticalFreeze(1);// 第一行标题冻结。
			ws.getSettings().setVerticalFreeze(2);// 第一行标题冻结。
			// 表头样式
			WritableCellFormat headFormat = new WritableCellFormat(
					new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD,
							false, UnderlineStyle.NO_UNDERLINE, Colour.GREEN));
			WritableCellFormat normalFont = new WritableCellFormat(
					new WritableFont(WritableFont.createFont("宋体"), 10));
			normalFont.setBorder(Border.ALL, BorderLineStyle.THIN);
			WritableCellFormat smallhead = new WritableCellFormat(
					new WritableFont(WritableFont.createFont("宋体"), 14,
							WritableFont.BOLD));
			smallhead.setAlignment(Alignment.CENTRE);
			for (int i = 0; i < bt.length; i++) {
				ws.setColumnView(i, 30);
			}
			ws.addCell(new Label(0, 0, "活动报名汇总", headFormat));// 表头
			// 表格标题
			for (int i = 0; i < bt.length; i++) {
				ws.addCell(new Label(i, 1, bt[i], smallhead));
			}
			int row = 2;
			// 数据区域
			ws.setColumnView(1, 15);
			Map<HuodongModel, List<XsxmModel>> maptemp = (Map<HuodongModel, List<XsxmModel>>) map;
			for (Entry<HuodongModel, List<XsxmModel>> entry : maptemp
					.entrySet()) {
				HuodongModel hd = entry.getKey();
				List<XsxmModel> list = entry.getValue();
				ws.mergeCells(0, row, 0, row + list.size() - 1);
				ws.addCell(new Label(0, row, hd.getHdmc() + "\r\n"
						+ hd.getHdfbsj() + "\r\n" + hd.getHddd(), normalFont));
				for (int i = 0; i < list.size(); i++) {
					ws.setRowView(row + i, 300);
					XsxmModel xsxm = list.get(i);
					XiangmuModel xm = xsxm.getXm();
					ws.addCell(new Label(1, row + i, xm.getXmmc(), normalFont));
					ws
							.addCell(new Label(2, row + i, xsxm.getXsgh() + "/"
									+ xsxm.getXsxm() + "/" + xsxm.getXsxy(),
									normalFont));
					ws.addCell(new Label(3, row + i, xsxm.getBmrgh() + "/"
							+ xsxm.getBmrxm(), normalFont));
					ws
							.addCell(new Label(4, row + i, xsxm.getBmsj(),
									normalFont));
				}
				row += list.size();
			}

			wwb.write();
			wwb.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

看着改下应该会的吧

水平有限。。用不来

Lahne 2014-05-16

打赏
举报

给你个我现在项目在用的


@SuppressWarnings("unchecked")
	@RequestMapping(value = "/huodongbm/export")
	public void exportOne(HttpServletRequest request,
			HttpServletResponse response, Model model) throws Exception {
		List<XsxmModel> list = new ArrayList<XsxmModel>();
		Map<String, Object> queryMap = (Map<String, Object>) request.getSession().getAttribute("map");
		list = xsxmService.querylistByQueryMap(queryMap);
		Map<HuodongModel, List<XsxmModel>> map = new HashMap<HuodongModel, List<XsxmModel>>();
		for (XsxmModel xsxm : list) {
			HuodongModel hd = xsxm.getHd();
			if (map.containsKey(hd)) {
				map.get(hd).add(xsxm);
			} else {
				List<XsxmModel> listtemp = new ArrayList<XsxmModel>();
				listtemp.add(xsxm);
				map.put(hd, listtemp);
			}
		}
		exportExcel(request, response, map);
	}

	@SuppressWarnings("unchecked")
	private void exportExcel(HttpServletRequest request,
			HttpServletResponse response, Map map) {
		String[] bt = new String[5];
		bt[0] = "活动";
		bt[1] = "项目";
		bt[2] = "学生";
		bt[3] = "报名人";
		bt[4] = "报名时间";
		// 设置导出excel的相关样式
		String fileName = "活动报名汇总.xls";
		response.setContentType("application/msexcel");// 定义输出类型
		try {
			fileName = new String(fileName.getBytes("gbk"), "ISO8859-1");// 解决中文
			// 文件名问题
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		response.setHeader("content-disposition", "attachment; filename="
				+ fileName);// 设定输出文件头
		try {
			WritableWorkbook wwb;
			OutputStream os = response.getOutputStream();
			wwb = Workbook.createWorkbook(os);
			WritableSheet ws = wwb.createSheet("活动报名汇总", 0);
			ws.getSettings().setVerticalFreeze(1);// 第一行标题冻结。
			ws.getSettings().setVerticalFreeze(2);// 第一行标题冻结。
			// 表头样式
			WritableCellFormat headFormat = new WritableCellFormat(
					new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD,
							false, UnderlineStyle.NO_UNDERLINE, Colour.GREEN));
			WritableCellFormat normalFont = new WritableCellFormat(
					new WritableFont(WritableFont.createFont("宋体"), 10));
			normalFont.setBorder(Border.ALL, BorderLineStyle.THIN);
			WritableCellFormat smallhead = new WritableCellFormat(
					new WritableFont(WritableFont.createFont("宋体"), 14,
							WritableFont.BOLD));
			smallhead.setAlignment(Alignment.CENTRE);
			for (int i = 0; i < bt.length; i++) {
				ws.setColumnView(i, 30);
			}
			ws.addCell(new Label(0, 0, "活动报名汇总", headFormat));// 表头
			// 表格标题
			for (int i = 0; i < bt.length; i++) {
				ws.addCell(new Label(i, 1, bt[i], smallhead));
			}
			int row = 2;
			// 数据区域
			ws.setColumnView(1, 15);
			Map<HuodongModel, List<XsxmModel>> maptemp = (Map<HuodongModel, List<XsxmModel>>) map;
			for (Entry<HuodongModel, List<XsxmModel>> entry : maptemp
					.entrySet()) {
				HuodongModel hd = entry.getKey();
				List<XsxmModel> list = entry.getValue();
				ws.mergeCells(0, row, 0, row + list.size() - 1);
				ws.addCell(new Label(0, row, hd.getHdmc() + "\r\n"
						+ hd.getHdfbsj() + "\r\n" + hd.getHddd(), normalFont));
				for (int i = 0; i < list.size(); i++) {
					ws.setRowView(row + i, 300);
					XsxmModel xsxm = list.get(i);
					XiangmuModel xm = xsxm.getXm();
					ws.addCell(new Label(1, row + i, xm.getXmmc(), normalFont));
					ws
							.addCell(new Label(2, row + i, xsxm.getXsgh() + "/"
									+ xsxm.getXsxm() + "/" + xsxm.getXsxy(),
									normalFont));
					ws.addCell(new Label(3, row + i, xsxm.getBmrgh() + "/"
							+ xsxm.getBmrxm(), normalFont));
					ws
							.addCell(new Label(4, row + i, xsxm.getBmsj(),
									normalFont));
				}
				row += list.size();
			}

			wwb.write();
			wwb.close();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

看着改下应该会的吧

堕落的唐僧 2014-05-16

打赏
举报

引用 12 楼 yys79 的回复:

上面已经给你回复了方法

WritableWorkbook wb = Workbook.createWorkBook(response.getOutputStream()); response cannot be resolved 要怎么修改，能帮帮我吗，之前代码也是网上找的

loveunittesting 2014-05-16

打赏
举报

上面已经给你回复了方法

堕落的唐僧 2014-05-16

打赏
举报

引用 9 楼 yys79 的回复:

[quote=引用 6 楼 u013794332 的回复:] 我现在能用JAVA指定路径生成EXCEL，怎么样在JSP中用按钮点击下载到任意路径呢。。

jxl早就不更新了，最好还是别用。至于怎么让用户下载，能把你代码贴上来吗？[/quote] 就在JSP里添加一个按钮点击下载，我的JAVA方法运行只能制定路径 import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import jxl.Workbook; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class Export { /** * 导出Excel * @param filePath 导出文件（模板）路径 * @param sql 导出的SQL语句 * @param startLine 起始行 * @param printTitle 是否打印标题 * @throws Exception */ public void doExport(String filePath , String sql , int startLine , boolean printTitle) throws Exception { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433; DatabaseName=test", "admin", "admin"); //获取数据库连接 WritableWorkbook book = null ; WritableSheet sheet = null ; if(new File(filePath).exists()){ Workbook wb = Workbook.getWorkbook(new File(filePath)); book = Workbook.createWorkbook(new File(filePath), wb); // 添加一个工作表 sheet = book.getSheet(0); }else { book = Workbook.createWorkbook(new File(filePath)); // 第一步 sheet = book.createSheet("第一页", 0); // 创建Sheet } PreparedStatement pstm = conn.prepareStatement(sql); ResultSet rs = pstm.executeQuery(); //获取数据集 ResultSetMetaData rsmd = rs.getMetaData(); //获取表头 int colCnt = rsmd.getColumnCount(); //获取数据集的列数 if(printTitle){ /** * 定义单元格样式 */ WritableFont wf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); // 定义格式字体下划线斜体粗体颜色 WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义 wcf.setBackground(jxl.format.Colour.BLACK); // 设置单元格的背景颜色 wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 sheet.setRowView(startLine, 1000); // 设置行的高度 //打印标题头 for(int k = 1; k < colCnt + 1; k++){ String title = rsmd.getColumnName(k); Label labelTitle = new Label( k-1 , startLine , title , wcf); sheet.setColumnView(k-1 , 25); // 设置列的宽度 sheet.addCell(labelTitle); } startLine++ ; } //打印sql语句查出来的数据 while (rs.next()) { for (int j = 1; j < colCnt+1; j++) { String colName = rsmd.getColumnName(j); String colValue = rs.getString(colName); Label label = new Label(j-1, startLine, colValue); sheet.addCell(label); } startLine++; } book.write(); book.close(); pstm.close(); conn.close(); } /** * 导出Excel * @param filePath 导出文件模板路径 * @param sql 导出的SQL语句 * @throws Exception */ public void doExport(String filePath , String sql) throws Exception{ doExport(filePath , sql , 0 , true); } public static void main(String[] args) throws Exception { String filePath = "e:\\1.xls"; StringBuffer sql = new StringBuffer(); sql.append(" select log.name 姓名 , log.age 年龄 from log"); Export impExcel = new Export(); impExcel.doExport(filePath, sql.toString()); //impExcel.doExport(filePath, sql.toString() , 1 ,false); } }

loveunittesting 2014-05-16