1.Java对Excel表格的操作一般依赖jxl包比较好。

下载地址：http://ishare.iask.sina.com.cn/f/14559561.html?from=dl

2.实例简述：运用jxl包导出Mysql数据表到Excel表格中。

3.Mysql连接类：MysqlConnect.java

import java.sql.*;

public class MysqlConnect

{

private static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;

private static final String DBURL = "jdbc:mysql://localhost:3306/mysql" ;

private static final String DBUSER = "root" ;

private static final String DBPASSWORD = "root" ;

private static final String A ="jdbc:mysql://localhost:3306/mysql?user=root&password=root&useUnicode=true&characterEncoding=utf8";

private Connection conn = null ;

public MysqlConnect() throws Exception{

try {

Class.forName(DBDRIVER);

conn = DriverManager.getConnection(A);

} catch (Exception e) {

throw e;

}

}

public Connection getConnect(){

return this.conn;

}

public void close() throws Exception{

if(this.conn != null){

try { this.conn.close();

} catch (Exception e) {

throw e;

}

}

}

}

4.Mysql数据表VO：LogsVo.java

public class LogsVo {

private Long logId;

private String userName;

private String logDesc;

private String time;

public Long getLogId() {

return logId;

}

public void setLogId(Long logId) {

this.logId = logId;

}

public String getUserName() {

return userName;

}

public void setUserName(String userName) {

this.userName = userName;

}

public String getLogDesc() {

return logDesc;

}

public void setLogDesc(String logDesc) {

this.logDesc = logDesc;

}

public String getTime() {

return time;

}

public void setTime(String time) {

this.time = time;

}

}

5.Excel处理类:ExcelUtil.java

import java.io.File;
import java.io.IOException;
import java.util.List;

import jxl.Cell;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public class ExcelUtil {
//标题单元格格式
private static WritableCellFormat titleFormat = null;
//主题内容单元格格式
private static WritableCellFormat bodyFormat=null;
//注释单元格格式
private static WritableCellFormat noteFormat=null;

private static WritableCellFormat floatFormat=null;

private static WritableCellFormat intFormat=null;

private static WritableCellFormat stringFormat=null;

private static boolean init = false;



private static void init() throws WriteException{
WritableFont font1,font2,font3,font4,font5;
//Arial字体，9号，粗体，单元格×××，田字边框，居中对齐
font1 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
titleFormat = new WritableCellFormat (font1);
titleFormat.setColour.YELLOW);
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
titleFormat.setAlignment(Alignment.CENTRE);
//Arial字体，9号，粗体，单元格×××，田字边框，左右居中对齐，垂直居中对齐，自动换行
font2 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
noteFormat = new WritableCellFormat (font2);
noteFormat.setColour.YELLOW);
noteFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
noteFormat.setAlignment(Alignment.CENTRE);
noteFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
noteFormat.setWrap(true);
//Arial字体，9号，非粗体，单元格淡绿色，田字边框
font3 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);
bodyFormat = new WritableCellFormat (font3);
bodyFormat.setColour.LIGHT_GREEN);
bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//Arial字体，9号，非粗体，单元格淡绿色，田字边框
font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);
floatFormat = new WritableCellFormat (font4,NumberFormats.FLOAT);
floatFormat.setColour.LIGHT_GREEN);
floatFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
//Arial字体，9号，非粗体，单元格淡绿色，田字边框
font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);
intFormat = new WritableCellFormat (font4,NumberFormats.INTEGER);
intFormat.setColour.LIGHT_GREEN);
intFormat.setBorder(Border.ALL, BorderLineStyle.THIN);


init=true;


}

public static void createLogsExcelFile(List<LogsVo>
logsList,File destFile) throws WriteException, IOException{
if(init==false) init();
int index,row;
WritableSheet sheet=null;
WritableWorkbook book=null;
book = Workbook.createWorkbook(destFile);
sheet = book.createSheet("用户操作表", 0);
sheet.setColumnView(0, 15);
sheet.setColumnView(1, 15);
sheet.setColumnView(2, 25);
sheet.setColumnView(3, 30);
//字段名
index=0;
sheet.addCell(new Label(index++,0,"ID",titleFormat));
sheet.addCell(new Label(index++,0,"用户名",titleFormat));
sheet.addCell(new Label(index++,0,"日志描述",titleFormat));
sheet.addCell(new Label(index++,0,"日志日期",titleFormat));
row = 1;
for(LogsVo logs : logsList){
if(logs == null)continue;
index = 0;
sheet.addCell(new Label(index++,row,Long.toString(logs.getLogId()),bodyFormat));
sheet.addCell(new Label(index++,row,logs.getUserName(),bodyFormat));
sheet.addCell(new Label(index++,row,logs.getLogDesc(),bodyFormat));
sheet.addCell(new Label(index++,row,logs.getTime(),bodyFormat));
row++;
}
book.write();
if(book!=null)book.close();


}

}
6.实现调用主方法：ExcelTestMain.java

import java.io.File;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelTestMain {
private static Connection conn = null;
private static PreparedStatement pstmt = null ;
private static List logsList = null;
static ExcelUtil excelUtil = new ExcelUtil();


public static void main(String args[]) throws Exception{
MysqlConnect mycon = new MysqlConnect();
conn = mycon.getConnect();
String sql = "select * from logs;";
pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery() ;

logsList = new ArrayList();
while(rs.next()){
LogsVo logsVo = new LogsVo();
logsVo.setLogId(rs.getLong(1));
logsVo.setUserName(rs.getString(2));
logsVo.setLogDesc(rs.getString(3));
logsVo.setTime(rs.getTimestamp(4).toString());
logsList.add(logsVo);
}
SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
String dateStr = format.format(new Date());
File file = new File("D:\\jkbb\\"+dateStr +"用户操作信息.xls");
excelUtil.createLogsExcelFile(logsList, file);

}
}
7.导出Ecel：

Java运用jxl包实现数据库表导出Excel实例
这里出现导出的汉字为乱码的错误，试过改变Mysql数据库编码格式，和JAVA代码中装换编码格式都没解决。先留着...研究下