思路：后台获取要导出的信息（一般是获取id等，然后去数据库联合查询出结果），然后利用poi，设计excel格式，然后调出下载页面进行下载。
代码展示的是一个具体的事例，借鉴的话，根据自己的情况进行改进。
需要导入的包：
import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress;
前台代码：
<a class="mini-button" iconCls="icon-download" onclick="ExportExcel()">导出Excel</a>
<script type="text/javascript">
mini.parse();
var obj = mini.getParams();
var task_grid = mini.get("task_grid");
var btnYes = document.getElementById("yes");
var btnNo = document.getElementById("no");
task_grid.load({"id":obj.id});
function ExportExcel(){
window.location.href="/random/export?id="+obj.id;
}
</script>
java后台代码：
public void export() {
int task_id = getParaToInt("id");
Map<String, String> titleData = new LinkedHashMap<String, String>();//标题，后面用到 只有linkedMap有序
titleData.put("kz_staff_id", "组长");
titleData.put("fkz_staff_id", "组员");
titleData.put("ky1_staff_id", "组员");
titleData.put("ky2_staff_id", "组员");
titleData.put("bul_name", "项目/工程");
String name = rtService.findbytask_id(task_id).get("task_name");
File file = new File(getTitle(name));
file = saveFile(titleData,file,task_id,name);
this.renderFile(file);
}
private static final String FILEPATH = PathKit.getWebRootPath() + File.separator + "upload" + File.separator ;
private static String getTitle(String name) {
Date date = new Date();
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
String title=FILEPATH+dateFormat.format(date) + ".xls";
return title;
}
private File saveFile(Map<String, String> headData, File file,int task_id,String name) {
// 创建工作薄
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
// sheet:一张表的简称
// row:表里的行
// 创建工作薄中的工作表
HSSFSheet hssfSheet = hssfWorkbook.createSheet();
hssfSheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));
hssfSheet.setColumnWidth(0, 3766);
hssfSheet.setColumnWidth(1, 3766);
hssfSheet.setColumnWidth(2, 3766);
hssfSheet.setColumnWidth(3, 3766);
hssfSheet.setColumnWidth(4, 6000);
//创建单元格格式
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
HSSFCellStyle cellStyle1 = hssfWorkbook.createCellStyle();
//水平居中
HorizontalAlignment align = HorizontalAlignment.CENTER;
cellStyle.setAlignment(align);
cellStyle1.setAlignment(align);
//垂直居中
VerticalAlignment vertical = VerticalAlignment.CENTER;
cellStyle.setVerticalAlignment(vertical);
cellStyle1.setVerticalAlignment(vertical);
//设置字体
HSSFFont font = hssfWorkbook.createFont();
HSSFFont font1 = hssfWorkbook.createFont();
font.setFontHeightInPoints((short)15);
font1.setFontHeightInPoints((short)10);
font.setBold(true);
font1.setBold(true);
cellStyle.setFont(font);
cellStyle1.setFont(font1);
//创建headerrow
HSSFRow headerRow = hssfSheet.createRow(0);
hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
HSSFCell headercell = headerRow.createCell(0);
headercell.setCellValue(name);
//将格式应用到具体单元格中
headercell.setCellStyle(cellStyle);
//创建复杂表头
HSSFRow initRow = hssfSheet.createRow(1);
hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));
HSSFCell initcell = initRow.createCell(0);
initcell.setCellValue("巡查小组");
initcell.setCellStyle(cellStyle1);
HSSFCell cell1 = initRow.createCell(4);
cell1.setCellValue("项目/工程");
cell1.setCellStyle(cellStyle1);
// 创建行
HSSFRow row = hssfSheet.createRow(2);
// 创建单元格，设置表头 创建列
HSSFCell cell = null;
// 初始化索引
int rowIndex = 2;
int cellIndex = 0;
// 创建标题行
row = hssfSheet.createRow(rowIndex);
rowIndex++;
// 遍历标题
for (String h : headData.keySet()) {
//创建列
cell = row.createCell(cellIndex);
cell.setCellStyle(cellStyle1);
//索引递增
cellIndex++;
//逐列插入标题
cell.setCellValue(headData.get(h));
}
// 得到所有记录 行：列
int random_task = rtService.findTypeById(task_id).getInt("random_type");
List<Record> list = rtService.exportList(task_id,random_task);
Record record = null;
if (list != null) {
// 获取所有的记录 有多少条记录就创建多少行
for (int i = 0; i < list.size(); i++) {
row = hssfSheet.createRow(rowIndex);
// 得到所有的行 一个record就代表 一行
record = list.get(i);
//下一行索引
rowIndex++;
//刷新新行索引
cellIndex = 0;
// 在有所有的记录基础之上，便利传入进来的表头,再创建N行
for (String h : headData.keySet()) {
cell = row.createCell(cellIndex);
cellIndex++;
//按照每条记录匹配数据
cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
}
}
}
try {
FileOutputStream fileOutputStreane = new FileOutputStream(file);
hssfWorkbook.write(fileOutputStreane);
fileOutputStreane.flush();
fileOutputStreane.close();
hssfWorkbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return file;
}
读取数据库：
//export
public List<Record> exportList(int task_id,int random_task){
List<Record> list = new ArrayList<Record>();
if(random_task == 1){
//单位工程
String sql="select t.*,b.bul_name,sk.name as kz_staff_id,sf.name as fkz_staff_id,s1.name as ky1_staff_id,s2.name as ky2_staff_id from tbl_random_task t inner join tbl_random_relation r on t.global_no = r.global_no inner join tbl_staff sk on r.kz_staff_id = sk.id inner join tbl_staff sf on r.fkz_staff_id = sf.id inner join tbl_staff s1 on r.ky1_staff_id = s1.id inner join tbl_staff s2 on r.ky2_staff_id = s2.id inner join tbl_building b on r.unit_id = b.id where t.status =1 and t.id = '"+task_id+"' ";
list = Db.find(sql);
}else{
//建设项目
String sql="select t.*,p.pro_name as bul_name,sk.name as kz_staff_id,sf.name as fkz_staff_id,s1.name as ky1_staff_id,s2.name as ky2_staff_id from tbl_random_task t inner join tbl_random_relation r on t.global_no = r.global_no inner join tbl_staff sk on r.kz_staff_id = sk.id inner join tbl_staff sf on r.fkz_staff_id = sf.id inner join tbl_staff s1 on r.ky1_staff_id = s1.id inner join tbl_staff s2 on r.ky2_staff_id = s2.id inner join tbl_project p on r.unit_id = p.id where t.status =1 and t.id = '"+task_id+"' ";
list = Db.find(sql);
}
return list;
}