1 package com.excel;  2   3 import java.io.FileOutputStream;  4 import java.io.IOException;  5 import java.io.OutputStream;  6 import java.util.HashSet;  7 import java.util.Map;  8 import java.util.Set;  9 import java.util.regex.Pattern; 10  11 import jxl.Workbook; 12 import jxl.write.Label; 13 import jxl.write.Number; 14 import jxl.write.NumberFormat; 15 import jxl.write.WritableCellFormat; 16 import jxl.write.WritableSheet; 17 import jxl.write.WritableWorkbook; 18 import jxl.write.WriteException; 19 import jxl.write.biff.RowsExceededException; 20  21 /** 22  * 生成excel表格 23  *  24  * @author 25  *  26  */ 27 public class ExcelExportUtil { 28     /** 29      * 构造器 30      *  31      */ 32     public ExcelExportUtil() { 33  34     } 35  36     /** 37      * 生成具有一定格式excel 38      *  39      * @param sheetName 40      *            sheet名称,默认为sheet1 41      * @param nf 42      *            数字类型的格式 如:jxl.write.NumberFormat nf = new 43      *            jxl.write.NumberFormat("#.##");默认无格式 44      * @param content 45      *            二维数组,要生成excel的数据来源 46      * @param 合并项 47      *            每一项的数据格式为0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二个元素合并 48      * @param os 49      *            excel输出流 50      * @param row 51      *            需要水平居中的行,默认居左。以逗号分隔的字符串 52      * @param col 53      *            需要水平居中的列,默认居左。以逗号分隔的字符串 54      */ 55     public void export(String sheetName, NumberFormat nf, String[][] content, 56             String[] mergeInfo, OutputStream os, String row, String col) { 57          58         if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) { 59             return; 60         } 61         // 默认名称 62         if (VerifyUtil.isNullObject(sheetName)) { 63             sheetName = "sheet1"; 64         } 65         Set
      
        rows = this.getInfo(row); 66         Set
       
         cols = this.getInfo(col); 67         WritableWorkbook workbook = null; 68         try { 69             workbook = Workbook.createWorkbook(os); 70             WritableSheet sheet = workbook.createSheet(sheetName, 0); 71             for (int i = 0; i < content.length; i++) { 72                 for (int j = 0; j < content[i].length; j++) { 73                     if (content[i][j] == null) { 74                         content[i][j] = ""; 75                     } 76                     if (isNumber(content[i][j]) && !rows.contains(i) 77                             && !cols.contains(j)) {
     // 处理数字 78                         Number number = null; 79                         if (VerifyUtil.isNullObject(nf)) {
     // 数字无格式 80                             number = new Number(j, i, 81                                     Double.valueOf(content[i][j])); 82                         } else {
     // 如果有格式,按格式生成 83                             jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat( 84                                     nf); 85                             number = new Number(j, i, 86                                     Double.valueOf(content[i][j]), wcfn); 87                         } 88                         sheet.addCell(number); 89                     } else {
     // 处理非数字 90                         WritableCellFormat format = new WritableCellFormat(); 91                         if (rows.contains(i) || cols.contains(j)) { 92                             format.setAlignment(jxl.format.Alignment.CENTRE); 93                         } else { 94                             format.setAlignment(jxl.format.Alignment.LEFT); 95                         } 96                         format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 97                         Label label = new Label(j, i, content[i][j], format); 98                         sheet.addCell(label); 99                     }100                 }101             }102             this.merge(sheet, mergeInfo);103             workbook.write();104         } catch (Exception e) {105             e.printStackTrace();106             107         } finally {108             try {109                 workbook.close();110                 os.close();111             } catch (WriteException e) {112                 // TODO Auto-generated catch block113                 e.printStackTrace();114             } catch (IOException e) {115                 // TODO Auto-generated catch block116                 e.printStackTrace();117             }118         }119     }120 121     /**122      * 生成固定格式的excel,表格都为文本,水平居左,垂直居中123      * 124      * @param sheetName125      *            sheet名称,默认为sheet1126      * @param content127      *            二维数组,要生成excel的数据来源128      * @param os129      *            excel输出流130      */131     public void exportFormatExcel(String[][] content, String sheetName,132             OutputStream os) {133         if (VerifyUtil.isNullObject(content, os) || VerifyUtil.isNull2DArray(content)) {134             return;135         }136         // 默认名称137         if (VerifyUtil.isNullObject(sheetName)) {138             sheetName = "sheet1";139         }140         WritableWorkbook workbook = null;141         try {142             workbook = Workbook.createWorkbook(os);143             WritableSheet sheet = workbook.createSheet(sheetName, 0);144 145             for (int i = 0; i < content.length; i++) {146                 for (int j = 0; j < content[i].length; j++) {147                     if (content[i][j] == null) {148                         content[i][j] = "";149                     }150                     WritableCellFormat format = new WritableCellFormat();151                     format.setAlignment(jxl.format.Alignment.LEFT);152                     format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);153                     Label label = new Label(j, i, content[i][j], format);154                     sheet.addCell(label);155                 }156             }157 158             workbook.write();159         } catch (Exception e) {160             e.printStackTrace();161             162         } finally {163             try {164                 workbook.close();165             } catch (WriteException e) {166                 // TODO Auto-generated catch block167                 e.printStackTrace();168             } catch (IOException e) {169                 // TODO Auto-generated catch block170                 e.printStackTrace();171             }172         }173     }174 175     /**176      * 生成固定格式的excel,表格都为文本,水平居左,垂直居中177      * 178      * @param sheetName179      *            sheet名称,默认为sheet1180      * @param content181      *            Map,要生成excel的数据来源182      * @param os183      *            excel输出流184      */185     public void exportFormatExcel(Map
        
          content,186             String[] salary_name_array, String sheetName, OutputStream os)187              {188         if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {189             return;190         }191         // 默认名称192         if (VerifyUtil.isNullObject(sheetName)) {193             sheetName = "sheet1";194         }195         WritableWorkbook workbook = null;196         try {197             workbook = Workbook.createWorkbook(os);198             WritableSheet sheet = workbook.createSheet(sheetName, 0);199             int index = 0;200             for (int k = 0; k < salary_name_array.length; k++) {201                 String[][] value = (String[][]) content202                         .get(salary_name_array[k]);203                 if (value != null && value.length > 0) {204                     if (index != 0) {205                         index++;206                     }207                     WritableCellFormat format1 = new WritableCellFormat();208                     format1.setAlignment(jxl.format.Alignment.LEFT);209                     format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);210                     Label label1 = new Label(0, index, salary_name_array[k],211                             format1);212                     sheet.addCell(label1);213                     for (int i = 0; i < value.length; i++) {214                         index++;215                         for (int j = 0; j < value[i].length; j++) {216                             if (value[i][j] == null) {217                                 value[i][j] = "";218                             }219                             WritableCellFormat format = new WritableCellFormat();220                             format.setAlignment(jxl.format.Alignment.LEFT);221                             format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);222 223                             Label label = new Label(j, index, value[i][j],224                                     format);225                             sheet.addCell(label);226                         }227                     }228                 }229             }230             workbook.write();231         } catch (Exception e) {232             e.printStackTrace();233         } finally {234             try {235                 workbook.close();236             } catch (WriteException e) {237                 // TODO Auto-generated catch block238                 e.printStackTrace();239             } catch (IOException e) {240                 // TODO Auto-generated catch block241                 e.printStackTrace();242             }243         }244     }245 246     /**247      * 合并表格248      * @param sheet249      *            工作表250      * @param mergeInfo251      *            要合并的表格的信息252      * @throws RowsExceededException253      * @throws NumberFormatException254      * @throws WriteException255      */256     private void merge(WritableSheet sheet, String[] mergeInfo)257             throws RowsExceededException, NumberFormatException, WriteException {258         if (VerifyUtil.isNullObject(sheet) || VerifyUtil.isNull1DArray(mergeInfo)) {259             return;260         } else if (!this.isMergeInfo(mergeInfo)) {261             return;262         } else {263             for (String str : mergeInfo) {264                 String[] temp = str.split(",");265                 sheet.mergeCells(Integer.parseInt(temp[1]),266                         Integer.parseInt(temp[0]), Integer.parseInt(temp[3]),267                         Integer.parseInt(temp[2]));268             }269         }270     }271 272     /**273      * 处理要居中的行或列的数据274      * 275      * @param indexes276      *            行标或列标277      * @return 行坐标或列坐标组成的集合278      */279     private Set
         
           getInfo(String indexes) {280         Set
          
            set = new HashSet
           
            ();281 if (VerifyUtil.isNullObject(indexes)) {282 return set;283 }284 String[] temp = indexes.split(",", 0);285 for (String str : temp) {286 if (isNumeric(str)) {287 set.add(Integer.parseInt(str));288 }289 }290 return set;291 }292 293 /**294 * 判断字符串是否由纯数字组成295 * 296 * @param str297 * 源字符串298 * @return true是，false否299 */300 private boolean isNumeric(String str) {301 if (VerifyUtil.isNullObject(str)) {302 return false;303 }304 Pattern pattern = Pattern.compile("[0-9]*");305 return pattern.matcher(str).matches();306 }307 308 /**309 * 判断字符串是否是数字310 * 311 * @param str312 * 源字符串313 * @return true是，false否314 */315 private boolean isNumber(String number) {316 // 判断参数317 if (VerifyUtil.isNullObject(number)) {318 return false;319 }320 // 查看是否有小数点321 int index = number.indexOf(".");322 if (index < 0) {323 return isNumeric(number);324 } else {325 // 如果有多个".",则不是数字326 if (number.indexOf(".") != number.lastIndexOf(".")) {327 return false;328 }329 String num1 = number.substring(0, index);330 String num2 = number.substring(index + 1);331 return isNumeric(num1) && isNumeric(num2);332 }333 }334 335 /**336 * 判断合并项内容是否合法337 * 338 * @param mergeInfo339 * 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并340 * @return true合法,false非法341 */342 private boolean isMergeInfo(String[] mergeInfo) {343 if (VerifyUtil.isNull1DArray(mergeInfo)) {344 return false;345 } else {346 for (String str : mergeInfo) {347 String[] temp = str.split(",");348 if (VerifyUtil.isNull1DArray(temp) || temp.length != 4) {349 return false;350 } else {351 for (String s : temp) {352 if (!isNumeric(s)) {353 return false;354 }355 }356 }357 }358 }359 return true;360 }361 362 public static void main(String[] args) {363 ExcelExportUtil ee = new ExcelExportUtil();364 String[][] content = new String[][] { { "", "第一列", null, "第三列" },365 { "第一行", "aa", "2.00", "22" }, { "第二行", "bb", "3.01", "32" },366 { "第三行", "cc", "4.00", "41" } };367 try {368 OutputStream os = new FileOutputStream("D:/test2.xls");369 // ee.export(null,null, content,null, os);370 ee.export(null, null, content,371 new String[] { "0,1,0,2", "1,0,3,0" }, os, "0,1", "0");372 } catch (Exception e) {373 // TODO Auto-generated catch block374 e.printStackTrace();375 }376 }377 }
           
          
         
        
       
      

1 package com.excel; 2  3 import java.io.OutputStream; 4 import java.util.Map; 5  6 import jxl.write.NumberFormat; 7 import jxl.write.WritableSheet; 8  9 public class VerifyUtil {10 11     public static boolean isNullObject(String[][] content, OutputStream os) {12         // TODO Auto-generated method stub13         if(content != null && content.length > 0 && os != null)14         {15             return false;16         }17         return true;18     }19 20     public static boolean isNull2DArray(String[][] content) {21         // TODO Auto-generated method stub22         if(content != null && content.length > 0)23         {24             return false;25         }26         return true;27     }28 29     public static boolean isNullObject(NumberFormat nf) {30         // TODO Auto-generated method stub31         if(nf != null)32         {33             return false;34         }35         return true;36     }37 38     public static boolean isNullObject(String sheetName) {39         if(sheetName != null && !"".equals(sheetName.trim()))40         {41             return false;42         }43         return true;44     }45 46     public static boolean isNullObject(Map
      
        content,47             OutputStream os) {48         // TODO Auto-generated method stub49         if(content != null && content.size() > 0 && os != null)50         {51             return false;52         }53         return true;54     }55 56     public static boolean isNull1DArray(String[] mergeInfo) {57         // TODO Auto-generated method stub58         if(mergeInfo != null && mergeInfo.length > 0)59         {60             return false;61         }62         return true;63     }64 65     public static boolean isNullObject(WritableSheet sheet) {66         // TODO Auto-generated method stub67         if(sheet != null)68         {69             return false;70         }71         return true;72     }73 74 }