作者：乔山办公网日期：
返回目录：excel表格制作
我这有段java代码是将Mysql中的数据查询出来后写入到excel中的，利用的也是jxl.jar包，代码如下，可供参考：
package common;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.TimeZone;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class PointUseCount {
public Connection getConnection() {
e69da5e6ba90e799bee5baa6e997aee7ad94333String url = "jdbc:mysql://127.0.0.1:3306/member";
String userName = "root";
String password = "root";
Connection con = null;
try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
con = DriverManager.getConnection(url, userName, password);
} catch (SQLException sw) {
System.out.println("connection error...");
}
if (null == con) {
for (int i = 0; i < 10; i++) {
try {
Thread.sleep(2000);
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
con = DriverManager.getConnection(url, userName, password);
} catch (Exception e) {
}
if (null != con) {
break;
}
}
if (null != con) {
return con;
} else {
return null;
}
} else {
return con;
}
}
public String pointsUseCount(String sql,String month) {
Connection conn = null;
Statement cstmt = null;
ResultSet rs = null;
Integer customerId = null;
Integer tempPoints = null;
Integer confirmPoints = null;
PointUseCount cusCamPoints = new PointUseCount();
conn = cusCamPoints.getConnection();
Integer count = 0;
String nowDate = getJapanDate().substring(0,10);
Integer nowMonth = Integer.valueOf(nowDate.substring(5,7));
Integer day = Integer.valueOf(nowDate.substring(8,10));
if (null != conn) {
try {
// String name = nowDate+"("+ month +"月).xls";
String name = null;
name = nowMonth +"月"+day+"日.xls";
WritableWorkbook book = Workbook.createWorkbook(new File("D:\\user\\local\\pointExcel\\"+name));//本地测试使用
// WritableWorkbook book = Workbook.createWorkbook(new File("/usr/local/pointExcel/"+name));//AWS上开发使用
// 设置第一个输入参数的值为point过期的截止即当前日本时间，第二个值为输出结果
cstmt = conn.createStatement();
rs = cstmt.executeQuery(sql);
// 生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheet=null;//创建sheet
while(rs.next()){
if(count/65536 == 0 && count%65536 == 0){
sheet = book.createSheet((count/65536 + 1) + " " + name,0);
}else if(count/65536 == 1 && count%65536 == 0){
sheet = book.createSheet((count/65536 + 1) + " " + name,1);
count = 0;
}else if (count/65536 == 2 && count%65536 == 0) {
sheet = book.createSheet((count/65536 + 1) + " " + name,2);
count = 0;
}
customerId = Integer.valueOf(rs.getString(1));
tempPoints = rs.getInt(2);
confirmPoints = rs.getInt(3);
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
// Label cusId=new Label(0,count,customerId);
jxl.write.Number cusId = new jxl.write.Number(0,count,customerId);
sheet.addCell(cusId);
//单元格位置是第二列，第一行
jxl.write.Number tempPoint = new jxl.write.Number(1,count,tempPoints);
sheet.addCell(tempPoint);
// 单元格位置是第三列，第一行
jxl.write.Number confirmPoint = new jxl.write.Number(2,count,confirmPoints);
sheet.addCell(confirmPoint);
count ++;
}
book.write();//写入数据
book.close(); //关闭文件
return "successed";
} catch (Exception e) {
System.out.println("error ... "+e.toString());
return "failed";
}
} else {
System.out.println("pointsUseCount connection Mysql error...");
return "failed";
}
}
public static String getJapanDate() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sdf.setTimeZone(TimeZone.getTimeZone("Japan"));
// System.out.println("Japan time successed");
return sdf.format(new Date());
}
public Properties getProperties() {
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("sendmail.properties");
Properties p = new Properties();
try {
p.load(inputStream);
} catch (IOException e) {
System.out.println(e.toString());
}
return p;
}
public static void main(String[] args) {
PointUseCount checkConf = new PointUseCount();
Properties props = checkConf.getProperties();
String sql1 = "sql查询语句";
try {
String startDate = getJapanDate();
Integer nowMonth = Integer.valueOf(startDate.substring(5,7));
System.out.println("startDate: " + startDate);
sql1 = new String(sql1.getBytes("ISO-8859-1"),"utf-8");
String check = checkConf.pointsUseCount(sql1,"1");
System.out.println("Jan PointUseCount: " + check);
String endDate = getJapanDate();
System.out.println("endDate: " + endDate);
System.out.println("---------------------------------------------------------");
} catch (Exception e) {
System.out.println(e.toString());
}
System.exit(0);
}
}
很简单啊，先读出excel表里的数据，一般百都是一行是一条数据，当然也可以按照单元格读取度数据；但是你的数据回一定要有规律；读出数据之后放到你的数据库表生成的类里；最后生成包含多个该答类的list；循环该list，执行入库操作。
Java Excel 是一个开源项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件等，在项目中需要导入名为jxl.jar的包。在这里只是示例它的基本用法，其他高级的功能（图片、公式、格式等）请参考Java Excel的帮助文档。
如有一个用户资料的Excel表，包含ID、用户名、性别、邮件等信息，定义一个用户JavaBean：
package com.monitor1394.excel;
/**
*
* 用户
*
* @author monitor
* Created on 2010-12-22, 9:57:58
*/
public class User {
/** ID */
private int id;
/** 用户名 */
private String name;
/** 性别 1:男 2:女*/
private int sex;
/** 邮件 */
private String email;
public User(){
}
public User(int id,String name,int sex,String email){
this.id=id;
this.name=name;
this.sex=sex;
this.email=email;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
@Override
public String toString(){
return id+":"+name;
}
}
提供的Excel表操作类如下，某些单元格的格式可按自己意愿指定：
package com.monitor1394.excel;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
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.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
*
* Excel表操作
*
* @author monitor
* Created on 2010-12-22, 9:50:28
*/
public class Excel {
/** 标题单元格格式 */
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 boolean init=false;
/** 私有构造方法，防止错误使用Excel类 */
private Excel(){
}
/**
* 初始化各单元格格式
* @throws WriteException 初始化失败
*/
private static void init() throws WriteException{
WritableFont font1,font2,font3,font4;
//Arial字体，9号，粗体，单元格黄色7a686964616fe58685e5aeb9334，田字边框，居中对齐
font1 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
titleFormat = new WritableCellFormat (font1);
titleFormat.setBackground(Colour.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.setBackground(Colour.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.setBackground(Colour.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.setBackground(Colour.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.setBackground(Colour.LIGHT_GREEN);
intFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
init=true;
}
public static void createUserExcelFile(List<User> userList,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, 15);
sheet.setColumnView(3, 40);
//字段变量名
index=0;
sheet.addCell(new Label(index++,0,"id",titleFormat));
sheet.addCell(new Label(index++,0,"name",titleFormat));
sheet.addCell(new Label(index++,0,"sex",titleFormat));
sheet.addCell(new Label(index++,0,"email",titleFormat));
//字段名
index=0;
sheet.addCell(new Label(index++,1,"ID",titleFormat));
sheet.addCell(new Label(index++,1,"用户名",titleFormat));
sheet.addCell(new Label(index++,1,"性别",titleFormat));
sheet.addCell(new Label(index++,1,"邮件",titleFormat));
//字段注释
index=0;
sheet.addCell(new Label(index++,2,null,noteFormat));
sheet.addCell(new Label(index++,2,null,noteFormat));
sheet.addCell(new Label(index++,2,"1:男/n2:女",noteFormat));
sheet.addCell(new Label(index++,2,null,noteFormat));
row=3;
for(User user:userList){
if(user==null) continue;
index=0;
sheet.addCell(new Number(index++,row,user.getId(),bodyFormat));
sheet.addCell(new Label(index++,row,user.getName(),bodyFormat));
sheet.addCell(new Number(index++,row,user.getSex(),bodyFormat));
sheet.addCell(new Label(index++,row,user.getEmail(),bodyFormat));
row++;
}
book.write();
if(book!=null) book.close();
}
public static List<User> readUserExcelFile(File file) throws IOException, BiffException{
if(file==null) return null;
int row,column;
String temp=null;
Workbook book =null;
Sheet sheet=null;
List<User> userList=new ArrayList<User>();
book = Workbook.getWorkbook(file);
sheet = book.getSheet(0);
row=3;
while(row<sheet.getRows()){
column=0;
User user=new User();
//id
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null && !temp.equals("") && temp.matches("//d+")) user.setId(Integer.parseInt(temp));
else break;
//名称
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null && !temp.equals("")) user.setName(temp);
//性别
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null && !temp.equals("") && temp.matches("//d+")) user.setSex(Integer.parseInt(temp));
//邮件
temp=sheet.getCell(column++,row).getContents().trim();
if(temp!=null && !temp.equals("")) user.setEmail(temp);
userList.add(user);
row++;
}
if(book!=null) book.close();
return userList;
}
}
要导入的Excel表格式如下：
导出后的Excel表如下：
自己百下度载内：容http://download.csdn.net/detail/likai22/534250