Java运用jxl包实现数据库表导出Excel实例

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() throwsException{

try {

Class.forName(DBDRIVER);

conn =DriverManager.getConnection(A);

} catch (Exception e) {

throw e;

}

}

public Connection getConnect(){

return this.conn;

}

public void close() throwsException{

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(StringuserName) {

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 WritableCellFormatbodyFormat=null;
  //注释单元格格式
  private static WritableCellFormatnoteFormat=null;
   
  private static WritableCellFormatfloatFormat=null;
   
  private static WritableCellFormatintFormat=null;
   
  private static WritableCellFormatstringFormat=null;
 
  private static boolean init = false;
 
 
 
  private static void init() throwsWriteException{
   WritableFontfont1,font2,font3,font4,font5;
  //Arial字体，9号，粗体，单元格黄色，田字边框，居中对齐 
     font1 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD,false); 
   titleFormat = newWritableCellFormat (font1); 
  titleFormat.setBackground(Colour.YELLOW); 
  titleFormat.setBorder(Border.ALL,BorderLineStyle.THIN); 
  titleFormat.setAlignment(Alignment.CENTRE);
  //Arial字体，9号，粗体，单元格黄色，田字边框，左右居中对齐，垂直居中对齐，自动换行 
   font2 = newWritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD,false); 
   noteFormat = newWritableCellFormat (font2); 
  noteFormat.setBackground(Colour.YELLOW); 
  noteFormat.setBorder(Border.ALL,BorderLineStyle.THIN); 
  noteFormat.setAlignment(Alignment.CENTRE); 
  noteFormat.setVerticalAlignment(VerticalAlignment.CENTRE); 
  noteFormat.setWrap(true); 
  //Arial字体，9号，非粗体，单元格淡绿色，田字边框 
   font3 = newWritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,false); 
   bodyFormat = newWritableCellFormat (font3); 
  bodyFormat.setBackground(Colour.LIGHT_GREEN); 
  bodyFormat.setBorder(Border.ALL,BorderLineStyle.THIN); 
  //Arial字体，9号，非粗体，单元格淡绿色，田字边框 
   font4 = newWritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,false); 
   floatFormat = newWritableCellFormat(font4,NumberFormats.FLOAT); 
  floatFormat.setBackground(Colour.LIGHT_GREEN); 
  floatFormat.setBorder(Border.ALL,BorderLineStyle.THIN); 
  //Arial字体，9号，非粗体，单元格淡绿色，田字边框 
   font4 = newWritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD,false); 
   intFormat = newWritableCellFormat(font4,NumberFormats.INTEGER); 
  intFormat.setBackground(Colour.LIGHT_GREEN); 
  intFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
  
  
  init=true;

  }
 
  public static voidcreateLogsExcelFile(List<LogsVo>
  logsList,File destFile) throws WriteException,IOException{
   if(init==false) init();
   int index,row;
   WritableSheetsheet=null;
   WritableWorkbookbook=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(newLabel(index++,0,"ID",titleFormat));
   sheet.addCell(newLabel(index++,0,"用户名",titleFormat));
   sheet.addCell(newLabel(index++,0,"日志描述",titleFormat));
   sheet.addCell(newLabel(index++,0,"日志日期",titleFormat));
   row = 1;
   for(LogsVo logs :logsList){
    if(logs ==null)continue;
    index =0;
   sheet.addCell(newLabel(index++,row,Long.toString(logs.getLogId()),bodyFormat));
   sheet.addCell(newLabel(index++,row,logs.getUserName(),bodyFormat));
   sheet.addCell(newLabel(index++,row,logs.getLogDesc(),bodyFormat));
   sheet.addCell(newLabel(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 = newExcelUtil();
 
 
    publicstatic 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 = newLogsVo();
     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 = newSimpleDateFormat("yyyy.MM.dd");
  String dateStr =format.format(new Date());
  File file = newFile("D:\\jkbb\\"+dateStr +"用户操作信息.xls");
  excelUtil.createLogsExcelFile(logsList,file);
    
    }
}
7.导出Ecel：

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