当前位置：首页 > news >正文

导出excel

news 来源：原创 2024/5/20 9:06:59

2019独角兽企业重金招聘Python工程师标准>>>

<span style="font-size:18px;">/**
* @param sql 查询语句
* @param contentTitle excel表格标题名称
* @param filepath 生成excel表格文件的路径
* @param filename excel表格的名称
* @return 生成excel表格文件的路径
*/
public String ToExcel(String sql,String contentTitle,String filepath,String filename) {
WritableWorkbook workbook = null;
try{
/**
* 执行sql语句，查询数据库结果
*/
List<Map> list = null;
//获取结果中的字段
if(list!=null && list.isEmpty()){
//获取导出excel表格中所有的列字段名称
Map<String,String> columnMap = list.get(0);
Set<String> keySet = columnMap.keySet();
/*
* 1，创建excel文件
*/
File file = new File(filepath);
if (!file.exists()) {
file.mkdirs();
}
//获取文件的完整路径
filename =file+"/"+filename+ ".xls";
//创建excel文件
File excelfile = new File(filename);
excelfile.createNewFile();
workbook = Workbook.createWorkbook(new FileOutputStream(excelfile));
/*
* 2，写入excel文件标题和工作簿名称
*/
WritableSheet sheet = workbook.createSheet("sheet名称", 0);
Label nL = null;
jxl.write.WritableFont headerFont = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat headerFormat = new jxl.write.WritableCellFormat(headerFont);
jxl.write.WritableFont titleFont = new jxl.write.WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat titleFormat = new jxl.write.WritableCellFormat(titleFont);
/*
* 3，写入标题
*/
nL = new Label(0, 0, contentTitle, headerFormat);
sheet.addCell(nL);
/*
* 4，写入内容
*/
Label label;
int index = 0;
for(String key:keySet){
nL = new Label(index, 1, key, titleFormat);
sheet.addCell(nL);
for (int j = 0; j < list.size(); j++) {
//得到单元格的列所对应的值
String value = (String) list.get(j).get(key);
//把值放入单元格中
Label obj = new Label(index, j + 2, value);
sheet.addCell(obj);
}
index++;
}
workbook.write();
workbook.close();
}
}catch(Exception e){
e.printStackTrace();
}
return filename;
}</span>