JXL完成excel报表

Java eXceL api：专门用来导出生成Excel文件的，同时还可以将Excel中的数据导入到程序中读取进来。

导出功能可以在统计的模块中使用。

导入功能可以在初始化数据以及修改商品数量时使用。

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class Test {

	public static void exportXls(String path) throws Exception {
		// 建立要导出的.xls文件对象
		WritableWorkbook wb = Workbook.createWorkbook(new File(path));

		// 在Excel中建立工作表
		WritableSheet sheet = wb.createSheet("新闻数据表", 0);

		// 在工作表中加入数据
		// 设置单元格字体
		WritableFont font = new WritableFont(WritableFont.createFont("黑体"), 20,
				WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
				Colour.RED);

		// 设置单元格格式,例如:对齐方式,背景颜色等
		WritableCellFormat format = new WritableCellFormat(font);
		format.setAlignment(Alignment.CENTRE);

		// 加入最上面的标题信息
		Label temp = new Label(0, 0, "新闻数据列表功能", format);
		sheet.addCell(temp);

		sheet.mergeCells(0, 0, 3, 0);

		// 添加表头信息
		Label idLabel = new Label(0, 1, "新闻编号");
		Label titleLabel = new Label(1, 1, "新闻标题");
		Label pubDateLabel = new Label(2, 1, "发布日期");
		Label photoLabel = new Label(3, 1, "照片");

		sheet.addCell(idLabel);
		sheet.addCell(titleLabel);
		sheet.addCell(pubDateLabel);
		sheet.addCell(photoLabel);

		// 从数据库中将所有新闻查询出来,并加入到Excel.
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@192.168.2.105:1521:ORCL", "sunxun", "123");

		String sql = "SELECT id,title,pub_date,photo FROM news";
		PreparedStatement pst = conn.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		int row = 2;
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		while (rs.next()) {
			idLabel = new Label(0, row, rs.getString(1));
			titleLabel = new Label(1, row, rs.getString(2));
			pubDateLabel = new Label(2, row, sf.format(rs.getDate(3)));
			// photoLabel = new Label(3, row, rs.getString(4));
			WritableImage img = new WritableImage(3, row, 1, 1, new File(
					"D:/a.png"));

			sheet.addCell(idLabel);
			sheet.addCell(titleLabel);
			sheet.addCell(pubDateLabel);
			// sheet.addCell(photoLabel);
			sheet.addImage(img);

			sheet.setRowView(row, 3000);
			row++;
		}

		// 设置行高和列宽
		sheet.setColumnView(2, 15);
		sheet.setColumnView(3, 40);

		wb.write();
		wb.close();

	}

	public static void importXls(String path) throws Exception {
		Workbook wb = Workbook.getWorkbook(new File(path));

		Sheet sheet = wb.getSheet(0);

		for (int row = 2; row < sheet.getRows(); row++) {
			String id = sheet.getCell(0, row).getContents();
			String title = sheet.getCell(1, row).getContents();

			System.out.println(id + " 0------> " + title);

		}

		wb.close();
	}

	public static void main(String[] args) throws Exception {
		// exportXls("D:/test.xls");
		importXls("D:/test.xls");
	}

}

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExportServlet extends HttpServlet {

	public void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("application/download");

		// 设置默认的下载文件名,如果包含中文,就需要进行转码
		String fileName = "新闻报表.xls" ;
		fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName);

		OutputStream os = response.getOutputStream();

		try {
			// 建立要导出的.xls文件对象
			WritableWorkbook wb = Workbook.createWorkbook(os);

			// 在Excel中建立工作表
			WritableSheet sheet = wb.createSheet("新闻数据表", 0);

			// 在工作表中加入数据
			// 设置单元格字体
			WritableFont font = new WritableFont(WritableFont.createFont("黑体"),
					20, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					Colour.RED);

			// 设置单元格格式,例如:对齐方式,背景颜色等
			WritableCellFormat format = new WritableCellFormat(font);
			format.setAlignment(Alignment.CENTRE);

			// 加入最上面的标题信息
			Label temp = new Label(0, 0, "新闻数据列表功能", format);
			sheet.addCell(temp);

			sheet.mergeCells(0, 0, 3, 0);

			// 添加表头信息
			Label idLabel = new Label(0, 1, "新闻编号");
			Label titleLabel = new Label(1, 1, "新闻标题");
			Label pubDateLabel = new Label(2, 1, "发布日期");
			Label photoLabel = new Label(3, 1, "照片");

			sheet.addCell(idLabel);
			sheet.addCell(titleLabel);
			sheet.addCell(pubDateLabel);
			sheet.addCell(photoLabel);

			// 从数据库中将所有新闻查询出来,并加入到Excel.
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@192.168.2.105:1521:ORCL", "sunxun",
					"123");

			String sql = "SELECT id,title,pub_date,photo FROM news";
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			int row = 2;
			SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
			while (rs.next()) {
				idLabel = new Label(0, row, rs.getString(1));
				titleLabel = new Label(1, row, rs.getString(2));
				pubDateLabel = new Label(2, row, sf.format(rs.getDate(3)));
				// photoLabel = new Label(3, row, rs.getString(4));
				WritableImage img = new WritableImage(3, row, 1, 1, new File(
						"D:/a.png"));

				sheet.addCell(idLabel);
				sheet.addCell(titleLabel);
				sheet.addCell(pubDateLabel);
				// sheet.addCell(photoLabel);
				sheet.addImage(img);

				sheet.setRowView(row, 3000);
				row++;
			}

			// 设置行高和列宽
			sheet.setColumnView(2, 15);
			sheet.setColumnView(3, 40);

			wb.write();
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		os.close();

	}
}