java_web 学习记录(十)：jxl excel export(二)

接之前，这篇我们来讲如何从数据库查询数据，并导出excel

一，这里我们使用前篇写好的工具类DbcpUtil，并在其中添加方法：

	/**
	 * 根据返回结果集获取列名集合
	 * @param rsmd
	 * @return
	 */
	public static List<String> getColumnNames (List<Map<String,Object>> list) {
		List<String> names = new ArrayList<String>();
		if (list != null && list.size() > 0) {
			Map<String,Object> map = list.get(0);
			for (String name : map.keySet()) {
				names.add(name);
			}
		}
		
		return names;
	}
	

二，之前忘记提供sql语句，这里补上，方便导出数据比对：

drop table user_info;
CREATE TABLE `user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `gender` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into user_info (gender,name) values ('M','李达康');
insert into user_info (gender,name) values ('M','侯亮平');
insert into user_info (gender,name) values ('M','沙瑞金');
insert into user_info (gender,name) values ('M','高育良');

三，编写逻辑处理类ExcelExportServlet2，主要是数据的变化

package com.example.servlet;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

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

import com.example.util.DbcpUtil;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 从数据库查询并导出数据
 * @author Administrator
 *
 */
public class ExcelExportServlet2 extends HttpServlet {

	private static final long serialVersionUID = 1647435458512117259L;

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		super.doGet(req, resp);
	}

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.setCharacterEncoding("utf-8");
		resp.setCharacterEncoding("utf-8");
		
		//导出文件名
		String fileName = "用户信息表";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
		fileName = fileName+"-"+sdf.format(new Date())+".xls";
		
		//正文数据,查询需要导出的数据
		String sql = "select gender,name,create_data from user_info";
		List<Map<String,Object>> listContent = DbcpUtil.getColumnDatas(sql);
		
		//表头数据
		List<String> columnNames = DbcpUtil.getColumnNames(listContent);
		
		//获取输出流
		OutputStream out = resp.getOutputStream();
		//清空输出流数据，避免文件写出格式错误
		resp.reset();
		//设置输出格式
		resp.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode(fileName, "UTF-8"));
		
		try {
			//创建excel工作簿
			WritableWorkbook workbook = Workbook.createWorkbook(out);
			//创建工作表
			WritableSheet sheet = workbook.createSheet("Sheet1", 0);
			
			//添加格式
			//设置默认值
			sheet.getSettings().setDefaultColumnWidth(30);//默认列宽
			sheet.getSettings().setDefaultRowHeight(600);//默认行高
			sheet.setRowView(0,700);//设置第一行标题栏的行高
			
			//设置打印格式
			sheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);// 设置为横向打印
            sheet.getSettings().setPaperSize(PaperSize.A4);// 设置纸张
            sheet.getSettings().setFitHeight(297);// 打印区高度
            sheet.getSettings().setFitWidth(210);// 打印区宽度  

			// 用于标题
			WritableCellFormat title_form = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD));
			title_form.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			title_form.setAlignment(Alignment.CENTRE); // 文字水平对齐
			title_form.setWrap(false); // 文字是否换行
			
			// 用于表头
			WritableCellFormat head_form = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD));
			head_form.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			head_form.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			head_form.setAlignment(Alignment.CENTRE); // 文字水平对齐
			head_form.setWrap(false); // 文字是否换行
			
			// 用于文本
			WritableCellFormat body_form = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10));
			body_form.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			body_form.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			body_form.setAlignment(Alignment.CENTRE); // 文字水平对齐
			body_form.setWrap(true); // 文字是否换行

			//第一行填入表名,合并单元格
			sheet.mergeCells(0, 0, columnNames.size()-1, 0);
			Label label = new Label(0, 0,fileName,title_form);
			sheet.addCell(label);
			//从第二行开始，插入表头
			for (int i = 0; i < columnNames.size(); i++) {  
			    sheet.addCell(new Label(i, 1,columnNames.get(i),head_form));  
			}
			//从第三行开始，插入正文
			int i = 2;
			for (Map<String,Object> map : listContent) {
				int j = 0;
				for (Entry<String, Object> entry : map.entrySet()) {
					Object value = entry.getValue();
					sheet.addCell(new Label(j, i, value.toString(),body_form));
					j++;
				}
				i++;
			}
			
			// 写入数据并关闭文件
			 workbook.write();
			 workbook.close();
			
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		
	}

}

四，在web.xml中添加映射

<!-- 导出excel2 -->
  <servlet>
  	<servlet-name>ExcelExportServlet2</servlet-name>
  	<servlet-class>com.example.servlet.ExcelExportServlet2</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>ExcelExportServlet2</servlet-name>
  	<url-pattern>/excel_export2</url-pattern>
  </servlet-mapping>

五，启动测试：http://localhost:8088/webDemo/excel_export2

查看导出文件：

==============================================================================================

导出ecxel的功能到这里就演示完了，下篇我们讲讲session和cookies