您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
本文实例讲述了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<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 (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<String, String[][]> 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<Integer> getInfo(String indexes) { Set<Integer> set = new HashSet<Integer>(); 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<String, String[][]> 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.length > 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程序设计有所帮助。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。