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:
这里出现导出的汉字为乱码的错误,试过改变Mysql数据库编码格式,和JAVA代码中装换编码格式都没解决。先留着...研究下
转载于:https://blog.51cto.com/dzwtf/1239337