接之前,这篇我们来讲如何从数据库查询数据,并导出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