利用poi3.9做的excel導出工具

一、先看看所生成的檔案效果圖

利用poi3.9做的excel導出工具

二、準備

本文需要六個jar包：

dom4j-1.6.1.jar

ojdbc14.jar

poi-3.9-20121203.jar

poi-ooxml-3.9-20121203.jar

poi-ooxml-schemas-3.9-20121203.jar

xmlbeans-2.3.0.jar

除了ojdbc14.jar是用來通路資料庫的，其它的都是導出excel所需要的poi相關jar包。

注：本文是以poi3.9版本寫的，利用了SXSSFWorkbook這個Workbook，這個可以分批寫入，防止記憶體溢出。這個類隻有在3.8及3.8以上版本才有。

三、寫個連接配接資料庫的工具

寫得很簡單，用的是oracle資料庫

代碼如下：

package com.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
	private static String driver="oracle.jdbc.driver.OracleDriver";
	private static String url="jdbc:oracle:thin:@127.0.0.1:1521:xe";
	private static String user="test";
	private static String password="test";
	public static Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,user,password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void main(String[] args) {
		System.out.println(DBUtil.getConnection());//測試連接配接
	}
}

四、寫導出工具類

代碼如下：

package com.utils;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class POIExport {
	/**
	 * 根據所傳入的參數生成一個Workbook
	 * @param sql 查詢資料的sql
	 * @param columns sql中列名字元串，以英文逗号分隔，不區分大小寫
	 * @param headers 表頭字元串數組，如果是多表頭，在需要合并的地方寫"null"，如：test1,test2,null,null,test3。
	 * 這表示test2将占三列。如果test2下面沒有null，則占一行三列，有n個null，則占n行3列
	 * @param splitStr 分割表頭字元串的分割符
	 * @return
	 */
	public static Workbook export(String sql,String columns,String[] headers, String splitStr){
		SXSSFWorkbook wb = new SXSSFWorkbook(1000);//建立excel文檔,記憶體中保留 1000 條資料，以免記憶體溢出
		Font font = wb.createFont();//字型
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗
		CellStyle cellStylehead = wb.createCellStyle();//表頭樣式
		cellStylehead.setFont(font);//設定字型樣式
		cellStylehead.setAlignment(CellStyle.ALIGN_CENTER);//水準對齊
		cellStylehead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直對齊
		cellStylehead.setWrapText(true);//自動換行
		//設定邊框
		cellStylehead.setBorderTop(CellStyle.BORDER_THIN);
		cellStylehead.setBorderRight(CellStyle.BORDER_THIN);
		cellStylehead.setBorderBottom(CellStyle.BORDER_THIN);
		cellStylehead.setBorderLeft(CellStyle.BORDER_THIN);
		//表體樣式
		CellStyle cellStyleBody = wb.createCellStyle();//表體單元格樣式
		cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);//水準對齊
		cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直對齊
		cellStyleBody.setWrapText(true);//自動換行
		//設定邊框
		cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
		cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
		cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
		Sheet sheet = wb.createSheet("sheet1");//建立一個sheet
		sheet.setDefaultColumnWidth(15);//設定預設列寬
		//寫表頭
		createHeader(wb,sheet,cellStylehead,headers,splitStr);
		//寫表體
		int beginRowNumber = headers.length;//表體開始行
		String[] cols = columns.split(",");//切分sql列名
		int cellSize = cols.length;//列數
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		conn = DBUtil.getConnection();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			int count = 0;//記錄行号
			while(rs.next()){
				Row row = sheet.createRow(count+beginRowNumber);
				row.setHeightInPoints(14);//設定行高
				for(int j=0;j<cellSize;j++){
					Cell cell = row.createCell(j);
					Object obj = rs.getObject(cols[j]);
					String cv = obj==null?"":obj.toString();//取得對應列中的值
					cell.setCellValue(cv);//設定單元格的值
					cell.setCellStyle(cellStyleBody);//設定樣式
				}
				count++;
			}
			System.out.println("共寫入資料："+count+"條");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null){
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(stmt!=null){
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return wb;
	}

	/**
	 * 建立excel表頭
	 * @param wb excel的workbook
	 * @param sheet excel的sheet
	 * @param cellStylehead excel的樣式
	 * @param headers	表頭字元串數組
	 * @param splitStr	表頭字元串切割符
	 */
	private static void createHeader(SXSSFWorkbook wb, Sheet sheet,
			CellStyle cellStylehead, String[] headers, String splitStr) {
		//周遊建立單元格
		for(int i=0;i<headers.length;i++){
			Row row = sheet.createRow(i);
			String[] header = headers[i].split(splitStr);
			for(int r=0;r<header.length;r++){
				Cell cell = row.createCell(r);
				cell.setCellValue(header[r]);
				cell.setCellStyle(cellStylehead);
			}
		}
		//周遊合并單元格，如果是單表頭則跳過
		if(headers.length>1){
			int[][][] mergeDatas = parseHeader(headers,splitStr);
			for(int i=0;i<mergeDatas.length;i++){
				int[][] mergeData = mergeDatas[i];
				for(int j=0;j<mergeData.length;j++){
					int[] merges = mergeData[j];
					int mergesR = merges[1]-merges[0];
					int mergesC = merges[3]-merges[2];
					if(mergesR!=0||mergesC!=0){
						//合并單元格
						sheet.addMergedRegion(new CellRangeAddress(merges[0],merges[1],merges[2],merges[3]));
						//合并單元格後重新設定單元格的樣式
						setMergedStyle(merges[0],merges[1],merges[2],merges[3],sheet,cellStylehead);
					}
				}
			}
		}
	}
	/**
	 * 設定合并單元格的樣式
	 * @param i	first row(0-based)
	 * @param j	last row(0-based)
	 * @param k	first column(0-based)
	 * @param l	last column(0-based)
	 * @param sheet	excel的sheet
	 * @param cellStylehead	excel的樣式
	 */
	private static void setMergedStyle(int i, int j, int k, int l, Sheet sheet, CellStyle cellStylehead) {
		for(int mm=i;mm<=j;mm++){
			Row row = sheet.getRow(mm);
			if(row==null){
				row = sheet.createRow(mm);
			}
			for(int nn=k;nn<=l;nn++){
				Cell cell = row.getCell(nn);
				if(cell==null){
					cell = row.createCell(nn);
				}
				cell.setCellStyle(cellStylehead);
			}
		}
	}
	/**
	 * 解析複雜表頭，表頭為多行且有合并的情況。表頭要符合約定格式。
	 * 格式例子：
	 * String h21 = "test,test,test,null,null,null,null,null,test,null,null,null,null,null,test";
	 * String h22 = "null,null,test,null,null,test,null,null,test,null,test,null,test,null,null";
	 * String h23 = "null,null,test,test,test,test,test,test,test,test,test,test,test,test,null";
	 * 這裡表頭有三行，且有合并情況。一行為一個字元串，字元串以英文逗号隔開，在需要合并的地方填寫"null"字元串
	 * @param headers 要解析的表頭字元串數組
	 * @param splitStr 切割表頭字元串的分割符
	 * @return 傳回一個三維數組，第三重資料中儲存着合并資料
	 */
	private static int[][][] parseHeader(String[] headers, String splitStr){
		//依據表頭建立一個二維數組
		String[][] doubleAry = new String[headers.length][];
		//依據表頭建立一個三維數組，用來儲存合并單元格所需要的資料，單元格所需要的資料有四個，依次是開始合并的行，結束合并的行，開始合并的列，結束合并的列
		//sheet.addMergedRegion(new CellRangeAddress(merges[0],merges[1],merges[2],merges[3]));
		int[][][] rcs = new int[headers.length][][];
		//周遊指派
		for(int i=0;i<headers.length;i++){
			String[] header = headers[i].split(splitStr);
			doubleAry[i] = header;
			rcs[i] = new int[header.length][4];
		}
		//周遊二維數組
		for(int i=0;i<doubleAry.length;i++){
			String[] sub = doubleAry[i];
			for(int j=0;j<sub.length;j++){
				int sum = 0;//計算行辨別
				//計算i,j需要合并的行數
				if(!sub[j].equals("null")){//如果單元格不為null
					for(int m=i;m<doubleAry.length;m++){
						String rs = doubleAry[m][j];
						if(rs.equals("null")){//如果單元格為null
							doubleAry[m][j] = "null2";//把null重指派為null2，防止在計算列時交錯了
							sum++;//個數加1
						}
					}
				}
				rcs[i][j][0] = i;//賦合并的開始行，目前行
				rcs[i][j][1] = i+sum;//賦合并的結束行，目前行加上它下面為null的行數
				int sum2 = 0;//計算列辨別
				//計算i,j需要合并的列數
				if(!sub[j].equals("null")){//如果單元格不為null
					for(int m=j+1;m<doubleAry[i].length;m++){
						String rs = doubleAry[i][m];
						if(rs.equals("null")){//如果單元格為null
							sum2++;
						}else{
							break;//一定要break，不然會算錯
						}
					}
				}
				rcs[i][j][2] = j;//賦合并的開始列，目前列
				rcs[i][j][3] = j+sum2;//賦合并的結束列，目前列加上它右邊為null的列數
			}
		}
		return rcs;
	}
	public static void main(String[] args) throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("select to_char(sysdate,'yyyymmdd') c1,       \n");
		sb.append("'類别'||round(dbms_random.value(0,4)) c2,    \n");
		sb.append("round(dbms_random.value(0,10000),2) c3,      \n");
		sb.append("round(dbms_random.value(0,100),2)||'%' c4,   \n");
		sb.append("round(dbms_random.value(0,10000),2) c5,      \n");
		sb.append("round(dbms_random.value(0,10000),2) c6,      \n");
		sb.append("round(dbms_random.value(0,10000),2) c7,      \n");
		sb.append("round(dbms_random.value(0,10000),2) c8       \n");
		sb.append(" from dual connect by level < 100             \n");//注：改這裡的數字，可得到不同行數的資料
		String sql = sb.toString();
		System.out.println("sql:\n"+sql);
		String columns = "c1,c2,c3,c4,c5,c6,c7,c8";
		System.out.println("columns:\n"+columns);
		String[] headers = new String[2];
		headers[0] = "日期,類别,3G,null,4G,null,null,null";
		headers[1] = "null,null,月流量,同比,月流量,上行流量,下行流量,贈送流量";
		System.out.println("表頭：");
		for(int i=0;i<headers.length;i++){
			String[] header = headers[i].split(",");
			for(int j=0;j<header.length;j++){
				System.out.print(header[j]+"\t");
			}
			System.out.println();
		}
		System.out.println("生成檔案開始。。。");
		long t1 = System.currentTimeMillis();
		Workbook wb = export(sql,columns,headers,",");
		long t11 = System.currentTimeMillis();
		System.out.println("生成Workbook共花費："+(t11-t1)+"毫秒");
		//寫入檔案
		FileOutputStream out = new FileOutputStream("f:/test/exp.xlsx");
		wb.write(out);
		out.close();
		long t2 = System.currentTimeMillis();
		System.out.println("把Workbook寫入檔案共花費："+(t2-t11)+"毫秒");
		System.out.println("生成檔案結束，共花費："+(t2-t1)+"毫秒");
		//測試10萬行*8列，所花時間13620毫秒
		//測試100萬行*8列，所花時間121443毫秒
	}
}

五、如有什麼bug請批評指正。

我把源碼打包了，如果不想浪費2分，可以直接複制以上代碼。如果不想複制，可以點這裡下載下傳：

http://download.csdn.net/detail/yunsyz/8345949

附檔案目錄圖：

利用poi3.9做的excel導出工具

利用poi3.9做的excel導出工具

繼續閱讀

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

mybatis_入門程式Mybatis入門

線性表之順序表的實作

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

C++判斷素數、求最大公約數代碼判斷一個數是否為素數求兩個數的最大公約數

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method

在python中建立excel并寫入