java generated Excel

Java generated by Excel is very simple to use the JExcelAPI here and MySql driver package, package-related jar and source files have been uploaded by myself downloaded the following steps:

1. Be downloaded unzip the file compression package, and then import it into eclipse in.

2. Here is to connect mysql database, the default user name: root, password is: root, port: 3306; here according to their own situation can be modified in the procedure.

Need to create a database called testexcel and the creation of this database in a test form, the command script as follows:

create database testexcel;



create table test (

id int primary key,

name varchar (20),

age int

);

3. Directly run the program, the program will first insert the test data, and then return the result set according to generate Excel sheet, the default in D: \ \ output.xls;



Program source list is as follows:

package cn.xwuxin.Excel;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 
 * @author <a href="mailto:xwuxin@yahoo.cn">X梦无心</a>
 * 
 */
public class WriteExcel {

	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	public static void main(String[] args) {
		WriteExcel we = new WriteExcel();
		
		we.createExcel(we.readData());
		we.closeConn();
	}

	/*
	 * 关闭链接
	 */
	private void closeConn() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private ResultSet readData() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			// 设置连接数据库的url,用户名及密码
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/testexcel", "root", "root");
			stmt = conn.createStatement();

			for (int i = 2; i < 100; i++) {
				stmt.execute("insert test values (" + i + ", 'neil', 20+i)");
			}

			rs = stmt.executeQuery("select * from test ");

			return rs;

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;

	}

	private void createExcel(ResultSet rs) {
		if (rs == null) {
			throw new RuntimeException("结果无记录");
		}
		WritableWorkbook workbook = null;
		try {
			// 创建一个工作薄位于D盘下面
			workbook = Workbook.createWorkbook(new File("D:\\output.xls"));
			// 创建一个工作表
			WritableSheet sheet = workbook.createSheet("first sheet", 0);

			// 设置单元格式
			WritableFont arial20font = new WritableFont(WritableFont.ARIAL, 10);
			arial20font.setItalic(true);
			arial20font.setColour(Colour.BROWN);
			WritableCellFormat arial20format = new WritableCellFormat(
					arial20font);

			arial20format.setBackground(Colour.IVORY);

			// 创建一个单元，位置为A3
			Label lid = new Label(0, 0, "id");
			Label lname = new Label(1, 0, "name");
			Label lage = new Label(2, 0, "age");
			sheet.addCell(lid);
			sheet.addCell(lname);
			sheet.addCell(lage);

			int i = 0;
			while (rs.next()) {

				i++;
				Label idl = new Label(0, i, rs.getString(1), arial20format);
				Label namel = new Label(1, i, rs.getString(2), arial20format);
				Label agel = new Label(2, i, rs.getString(3), arial20format);

				sheet.addCell(idl);
				sheet.addCell(namel);
				sheet.addCell(agel);

			}

			// 将文件写入
			workbook.write();
			// 关闭工作薄
			workbook.close();
			System.out.println("文件写入完毕，请到相关目录查看");
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}