数据导出Excel表、数据库数据导出

public static HashMap<String, String> column = new HashMap<String, String>();

static {

//根据自己的数据库结构添加说明

column.put("NAME", "名称");

column.put("LEVEL", "等级");

column.put("ADDRESS", "地址");

column.put("COUNTY", "所属区县");

column.put("CONTACTPERSON", "联系人");

column.put("TELEPHONE", "电话");

}

/**

 * 

 * @param table 要导出的表

 * @param name  导出的Excel表名称、表头

 * @throws Exception

 */

public void createExcel(String table, String name) throws Exception{

try {

//这个不用太关心，可以替换为你想要的路径即可

String path = Path.getFullPathRelateClass("../../15Layer",JSON.class);

path += "\\" + name + ".xls";

File file = new File(path);

if(file.exists()) {

file.delete();

}

//Excel表格

WritableWorkbook book = Workbook.createWorkbook(new File(path));

WritableSheet sheet = book.createSheet("Sheet1", 0);

//整体表格样式

WritableCellFormat format = new WritableCellFormat();

format.setAlignment(Alignment.CENTRE);

format.setVerticalAlignment(VerticalAlignment.CENTRE);

format.setWrap(false);

//表格标题样式

WritableCellFormat titleFormat = new WritableCellFormat();

titleFormat.setAlignment(Alignment.CENTRE);

titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

titleFormat.setWrap(false);

WritableFont font = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD);

titleFormat.setFont(font);

//表格表头样式

WritableCellFormat columnFormat = new WritableCellFormat();

columnFormat.setAlignment(Alignment.CENTRE);

columnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

columnFormat.setWrap(false);

WritableFont columnFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

columnFormat.setFont(columnFont);

//设置表格根据内容多少自动扩容

CellView view = new CellView();

view.setAutosize(true);

//获取数据库连接，根据你自己的系统情况获取相应数据库连接

Connection conn = getSession().connection();

PreparedStatement ps = conn.prepareStatement("select * from " + table + "");

ResultSet set = ps.executeQuery();

//获取列属性：列数、列名称

ResultSetMetaData meta = set.getMetaData();

int columnCount = meta.getColumnCount();

for (int i = 1; i <= columnCount; i++) {

Label label = new Label(i - 1, 2, column.get(meta.getColumnName(i)), columnFormat);

sheet.setColumnView(i -1, view);

sheet.addCell(label);

}

sheet.mergeCells(0, 0, columnCount - 1, 1);

Label title = new Label(0, 0, name, titleFormat);

sheet.addCell(title);

int amount = 3;

while(set.next()) {

for (int i = 1; i <= columnCount; i++) {

Label label = new Label(i - 1, amount, set.getString(i), format);

sheet.setColumnView(i -1, view);

sheet.addCell(label);

}

amount++;

}

// 写入数据并关闭文件

book.write();

book.close();

}catch (RuntimeException e) {

throw e;

}

}