生成excel表格

导出Excel表格的类
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import com.shxy.exception.FileOperationException;
import com.shxy.util.Verify;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 生成excel表格
* @author
*
*/
public class ExcelExport {
/**
* 构造器
*
*/
public ExcelExport() {

}
/**
* 生成具有一定格式excel
* @param sheetName sheet名称,默认为sheet1
* @param nf 数字类型的格式 如:jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");默认无格式
* @param content 二维数组,要生成excel的数据来源
* @param 合并项 每一项的数据格式为0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
* @param os excel输出流
* @param row 需要水平居中的行,默认居左。以逗号分隔的字符串
* @param col 需要水平居中的列,默认居左。以逗号分隔的字符串
* @throws FileOperationException
*/
public void export(String sheetName, NumberFormat nf, String[][] content, String[] mergeInfo, OutputStream os, String row, String col)
throws FileOperationException {
if (Verify.isNullObject(content, os)
|| Verify.isNull2DArray(content)) {
return;
}
//默认名称
if(Verify.isNullObject(sheetName)){
sheetName="sheet1";
}
Set<Integer>rows=this.getInfo(row);
Set<Integer>cols=this.getInfo(col);
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if(content[i][j]==null){
content[i][j]="";
}
if (isNumber(content[i][j])&&!rows.contains(i)&&!cols.contains(j)) {//处理数字
Number number=null;
if(Verify.isNullObject(nf)){//数字无格式
number = new Number(j, i, Double
.valueOf(content[i][j]));
}else{//如果有格式,按格式生成
jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(nf);
number = new Number(j, i, Double
.valueOf(content[i][j]),wcfn);
}
sheet.addCell(number);
} else {//处理非数字
WritableCellFormat format=new WritableCellFormat();
if(rows.contains(i)||cols.contains(j)){
format.setAlignment(jxl.format.Alignment.CENTRE);
}else{
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j],format);
sheet.addCell(label);
}
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
throw new FileOperationException(e);
}finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
* @param sheetName sheet名称,默认为sheet1
* @param content 二维数组,要生成excel的数据来源
* @param os excel输出流
* @throws FileOperationException
*/
public void exportFormatExcel(String[][] content, String sheetName, OutputStream os)
throws FileOperationException {
if (Verify.isNullObject(content, os)
|| Verify.isNull2DArray(content)) {
return;
}
// 默认名称
if(Verify.isNullObject(sheetName)){
sheetName="sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);

for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if(content[i][j]==null){
content[i][j]="";
}
WritableCellFormat format=new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j],format);
sheet.addCell(label);
}
}


workbook.write();
} catch (Exception e) {
e.printStackTrace();
throw new FileOperationException(e);
}finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
* @param sheetName sheet名称,默认为sheet1
* @param content Map,要生成excel的数据来源
* @param os excel输出流
* @throws FileOperationException
*/
public void exportFormatExcel(Map<String, String[][]> content,String[] salary_name_array, String sheetName, OutputStream os)
throws FileOperationException {
if (Verify.isNullObject(content, os)
|| content.size()==0) {
return;
}
// 默认名称
if(Verify.isNullObject(sheetName)){
sheetName="sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
int index = 0;
for (int k = 0; k < salary_name_array.length; k++) {
String[][] value = (String[][])content.get(salary_name_array[k]);
if(value!=null&&value.length>0){
if(index!=0){
index++;
}
WritableCellFormat format1=new WritableCellFormat();
format1.setAlignment(jxl.format.Alignment.LEFT);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label1 = new Label(0, index, salary_name_array[k] ,format1);
sheet.addCell(label1);
for (int i = 0; i < value.length; i++) {
index ++ ;
for (int j = 0; j < value[i].length; j++) {
if(value[i][j]==null){
value[i][j]="";
}
WritableCellFormat format=new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

Label label = new Label(j, index, value[i][j],format);
sheet.addCell(label);
}
}
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
throw new FileOperationException(e);
}finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


/**
* 合并表格
* @param sheet 工作表
* @param mergeInfo 要合并的表格的信息
* @throws RowsExceededException
* @throws NumberFormatException
* @throws WriteException
*/
private void merge(WritableSheet sheet,String[]mergeInfo) throws RowsExceededException, NumberFormatException, WriteException{
if(Verify.isNullObject(sheet)||Verify.isNull1DArray(mergeInfo)){
return;
}else if(!this.isMergeInfo(mergeInfo)){
return;
}else{
for(String str:mergeInfo){
String[]temp=str.split(",");
sheet.mergeCells(Integer.parseInt(temp[1]), Integer.parseInt(temp[0]), Integer.parseInt(temp[3]), Integer.parseInt(temp[2]));
}
}
}
/**
* 处理要居中的行或列的数据
* @param indexes 行标或列标
* @return 行坐标或列坐标组成的集合
*/
private Set<Integer> getInfo(String indexes){
Set<Integer>set=new HashSet<Integer>();
if(Verify.isNullObject(indexes)){
return set;
}
String[]temp=indexes.split(",",0);
for(String str:temp){
if(isNumeric(str)){
set.add(Integer.parseInt(str));
}
}
return set;
}
/**
* 判断字符串是否由纯数字组成
* @param str 源字符串
* @return true是，false否
*/
private boolean isNumeric(String str) {
if (Verify.isNullObject(str)){
return false;
}
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();
}
/**
* 判断字符串是否是数字
* @param str 源字符串
* @return true是，false否
*/
private boolean isNumber(String number) {
//判断参数
if (Verify.isNullObject(number)){
return false;
}
//查看是否有小数点
int index = number.indexOf(".");
if (index < 0) {
return isNumeric(number);
} else {
//如果有多个".",则不是数字
if(number.indexOf(".")!=number.lastIndexOf(".")){
return false;
}
String num1 = number.substring(0, index);
String num2 = number.substring(index + 1);
return isNumeric(num1) && isNumeric(num2);
}
}
/**
* 判断合并项内容是否合法
* @param mergeInfo 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
* @return true合法,false非法
*/
private boolean isMergeInfo(String[]mergeInfo){
if(Verify.isNull1DArray(mergeInfo)){
return false;
}else{
for(String str:mergeInfo){
String[]temp=str.split(",");
if(Verify.isNull1DArray(temp)||temp.length!=4){
return false;
}else{
for(String s:temp){
if(!isNumeric(s)){
return false;
}
}
}
}
}
return true;
}
public static void main(String[] args) {
ExcelExport ee=new ExcelExport();
String[][]content=new String[][]{{"测试","第一列",null,"第三列"},{"第一行","aa","2.00","22"},{"第二行","bb","3.01","3"},{"第三行","cc","4.00","4"}};
try {
OutputStream os=new FileOutputStream("D://test//test.xls");
// ee.export(null,null, content,null, os);
ee.export(null,null, content,new String[]{"0,1,0,2","1,0,3,0"}, os,"0,1","0");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}