JXL读写excel

 

package com.wangl.util;


import java.io.File;

import java.util.List;

import java.util.Map;


import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;


public class ExcelUtil {


/**

* @param filePath 文件全路径+文件名

* @param columnKey 列的key对应数据的key

* @param columnValue  列名

* @param dataList 数据集

* @param columnWidth 列宽

* @param pageSum 分页每页数量

* @return

*/

public static boolean createExcel(String filePath, String[] columnKey, String[] columnValue,

List> dataList, int columnWidth, int pageSum) {


if (null == dataList || null == filePath || null == columnKey || null == columnValue) {

return false;

}

WritableWorkbook book = null;

try {

// 数据行数

int dataSize = dataList.size();

// 每五万为一个sheet页,总sheet页数

int sheetsize = (dataSize / pageSum) == 0 ? 1 : (dataSize % pageSum > 0 ? (dataSize / pageSum + 1)

: dataSize / pageSum);

book = Workbook.createWorkbook(new File(filePath));

// 生成名为“第一页”的工作表，参数0表示这是第一页

WritableSheet sheet = null;


// 循环sheet页

for (int sheetIndex = 1; sheetIndex sheet = book.createSheet(" 第" + sheetIndex + "页  ", sheetIndex - 1);

// 生成标题

createTitle(sheet, columnValue, columnWidth);

// 行数

int count = getColumnCount(pageSum, dataSize, sheetsize, sheetIndex);

// 生成行数据

creatClumnData(columnKey, dataList, pageSum, sheet, sheetIndex, count);

}

} catch (Exception e) {

e.printStackTrace();

return false;

} finally {

try {

book.write();

book.close();

} catch (Exception e) {

e.printStackTrace();

}


}

return true;

}


/**

* 生成行数据

* @param columnKey 列的key

* @param dataList 数据集合

* @param pageSum 分页数

* @param sheet sheet对象

* @param sheetIndex sheet页序号

* @param count 行数

* @throws Exception

*/

private static void creatClumnData(String[] columnKey, List> dataList, int pageSum,

WritableSheet sheet, int sheetIndex, int count) throws Exception {

// 生数据行

for (int i = 0; i Label column = null;

// 生成数据列

for (int j = 0, size = columnKey.length; j // 设置单元格

WritableCellFormat format1 = new WritableCellFormat();

// 水平居左

format1.setAlignment(jxl.format.Alignment.LEFT);

// 数据序号

int dataIndex = pageSum * (sheetIndex - 1) + i;

// 列 行 数据 单元格样式

column = new Label(j, (i + 1), dataList.get(dataIndex).get(columnKey[j]) == null ? ""

: dataList.get(dataIndex).get(columnKey[j]).toString(), format1);

// 行数据生成

sheet.addCell(column);

}

}

}


/**

* 获取行数

* 

* @param pageSum 总页数

* @param dataSize  数据总数

* @param sheetsize sheet总数

* @param sheetIndex sheet页序号

* @return

*/

private static int getColumnCount(int pageSum, int dataSize, int sheetsize, int sheetIndex) {

int count = 0;

// 行数量

if (sheetsize == 1) {

count = dataSize;

}

else if (sheetsize == sheetIndex && sheetsize != 1) {

count = dataSize - pageSum * (sheetsize - 1);

}

else {

count = pageSum;

}

return count;

}


/**

* 生成sheet页的标题

* 

* @param sheet sheet页对象

* @param columnKey 列的key

* @param columnValue 列名

* @param columnWidth 列宽

* @throws Exception

*/

public static void createTitle(WritableSheet sheet, String[] columnValue, int columnWidth)

throws Exception {

// 生成标题行

Label title = null;

for (int i = 0, count = columnValue.length; i // 字体处理start 字体名称,字号,样式

WritableFont font1 = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);

WritableCellFormat format1 = new WritableCellFormat(font1);

// 水平居中

format1.setAlignment(jxl.format.Alignment.CENTRE);

// 把垂直对齐方式指定为居中

format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

// end

// 设置宽度i:哪列,columnWidth:宽度

sheet.setColumnView(i, columnWidth);


title = new Label(i, 0, columnValue[i], format1);

sheet.addCell(title);

}

}


}

 测试写excel:

 

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.wangl.util.ExcelUtil;

// 生成Excel的类 
public class CreateExcel {
	public static void main(String args[]) {
		Long startTime = System.currentTimeMillis();
		String filePath = "D:\\批量卡信息详单.xls";
		String[] columnKey = { "id", "password" };
		String[] columnValue = { "卡号", "卡密码" };
		int cardSum = 100000;
		List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
		Map<String, Object> param = null;
		for(int i=0;i<cardSum;i++){
			param = new HashMap<String, Object>();
			param.put("id", "20121102-"+(i+1));
			param.put("password", "password-"+(i+1));
			dataList.add(param);
		}
		Long dataEnd = System.currentTimeMillis();
		// TODO
		System.out.println("数据用时"+(dataEnd - startTime) );
		// 生成
		ExcelUtil.createExcel(filePath, columnKey, columnValue, dataList, 20, 50000);
		Long endTime = System.currentTimeMillis();
		
		// 时间
		System.out.println("生成excel用时:"+(endTime-dataEnd));
	}
}

测试读取excel

package com.wangl.test;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws BiffException 
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		String filePath = "D:\\批量卡信息详单.xls";
		Workbook book = Workbook.getWorkbook(new File(filePath));
		Sheet[] sheetArray = book.getSheets();
		for(int i=0,count=sheetArray.length;i<count;i++){
			int rows = sheetArray[i].getRows();
			int columns = sheetArray[i].getColumns();
			System.out.println("第"+(i+1)+"页 ,,,行:"+rows+"列:"+columns);
			for(int rowIndex = 0;rowIndex<rows;rowIndex++){
				for(int columnIndex = 0;columnIndex<columns;columnIndex++){
					Cell cell = sheetArray[i].getCell(columnIndex, rowIndex);
					System.out.print("     "+cell.getContents());
				}
				System.out.println();
			}
		}
	}

}