java实现的导出Excel工具类实例

java实现的导出Excel工具类实例

本文实例讲述了java实现的导出Excel工具类。分享给大家供大家参考，具体如下：

ExcelExportUtil：

package com.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 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 ExcelExportUtil {

/**

* 构造器

*

*/

public ExcelExportUtil() {

}

/**

* 生成具有一定格式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

* 需要水平居中的列,默认居左。以逗号分隔的字符串

*/

public void export(String sheetName, NumberFormat nf, String[][] content,

String[] mergeInfo, OutputStream os, String row, String col) {

if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {

return;

}

// 默认名称

if (VerifyUtil.isNullObject(sheetName)) {

sheetName = "sheet1";

}

Set rows = this.getInfo(row);

Set 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 (VerifyUtil.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();

} finally {

try {

workbook.close();

os.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输出流

*/

public void exportFormatExcel(String[][] content, String sheetName,

OutputStream os) {

if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {

return;

}

// 默认名称

if (VerifyUtil.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();

} 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输出流

*/

public void exportFormatExcel(Map content,

String[] salary_name_array, String sheetName, OutputStream os)

{

if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {

return;

}

// 默认名称

if (VerifyUtil.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();

} 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 (VerifyUtil.isNullObject(sheet) || VerifyUtil.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 getInfo(String indexes) {

Set set = new HashSet();

if (VerifyUtil.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 (VerifyUtil.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 (VerifyUtil.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 (VerifyUtil.isNull1DArray(mergeInfo)) {

return false;

} else {

for (String str : mergeInfo) {

String[] temp = str.split(",");

if (VerifyUtil.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) {

ExcelExportUtil ee = new ExcelExportUtil();

String[][] content = new String[][] { { "", "第一列", null, "第三列" },

{ "第一行", "aa", "2.00", "22" }, { "第二行", "bb", "3.01", "32" },

{ "第三行", "cc", "4.00", "41" } };

try {

OutputStream os = new FileOutputStream("D:/test2.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();

}

}

}

VerifyUtil：

package com.excel;

import java.io.OutputStream;

import java.util.Map;

import jxl.write.NumberFormat;

import jxl.write.WritableSheet;

public class VerifyUtil {

public static boolean isNullObject(String[][] content, OutputStream os) {

// TODO Auto-generated method stub

if(content != null && content.length > 0 && os != null)

{

return false;

}

return true;

}

public static boolean isNull2DArray(String[][] content) {

// TODO Auto-generated method stub

if(content != null && content.length > 0)

{

return false;

}

return true;

}

public static boolean isNullObject(NumberFormat nf) {

// TODO Auto-generated method stub

if(nf != null)

{

return false;

}

return true;

}

public static boolean isNullObject(String sheetName) {

if(sheetName != null && !"".equals(sheetName.trim()))

{

return false;

}

return true;

}

public static boolean isNullObject(Map content,

OutputStream os) {

// TODO Auto-generated method stub

if(content != null && content.size() > 0 && os != null)

{

return false;

}

return true;

}

public static boolean isNull1DArray(String[] mergeInfo) {

// TODO Auto-generated method stub

if(mergeInfo != null && mergeInfo.lehttp://ngth > 0)

{

return false;

}

return true;

}

public static boolean isNullObject(WritableSheet sheet) {

// TODO Auto-generated method stub

if(sheet != null)

{

return false;

}

return true;

}

}

更多关于java相关内容感兴趣的读者可查看本站专题：《Java操作Excel技巧总结》、《Java+mysql数据库程序设计总结》、《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》及《Java操作DOM节点技巧总结》

希望本文所述对大家java程序设计有所帮助。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。