java导出Excel工具类
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<Integer> rows = this.getInfo(row); 66 Set<Integer> 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 block 113 e.printStackTrace(); 114 } catch (IOException e) { 115 // TODO Auto-generated catch block 116 e.printStackTrace(); 117 } 118 } 119 } 120 121 /** 122 * 生成固定格式的excel,表格都为文本,水平居左,垂直居中 123 * 124 * @param sheetName 125 * sheet名称,默认为sheet1 126 * @param content 127 * 二维数组,要生成excel的数据来源 128 * @param os 129 * 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 block 167 e.printStackTrace(); 168 } catch (IOException e) { 169 // TODO Auto-generated catch block 170 e.printStackTrace(); 171 } 172 } 173 } 174 175 /** 176 * 生成固定格式的excel,表格都为文本,水平居左,垂直居中 177 * 178 * @param sheetName 179 * sheet名称,默认为sheet1 180 * @param content 181 * Map,要生成excel的数据来源 182 * @param os 183 * excel输出流 184 */ 185 public void exportFormatExcel(Map<String, String[][]> 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[][]) content 202 .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 block 238 e.printStackTrace(); 239 } catch (IOException e) { 240 // TODO Auto-generated catch block 241 e.printStackTrace(); 242 } 243 } 244 } 245 246 /** 247 * 合并表格 248 * @param sheet 249 * 工作表 250 * @param mergeInfo 251 * 要合并的表格的信息 252 * @throws RowsExceededException 253 * @throws NumberFormatException 254 * @throws WriteException 255 */ 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 indexes 276 * 行标或列标 277 * @return 行坐标或列坐标组成的集合 278 */ 279 private Set<Integer> getInfo(String indexes) { 280 Set<Integer> set = new HashSet<Integer>(); 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 str 297 * 源字符串 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 str 312 * 源字符串 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 mergeInfo 339 * 合并项 每一项的数据格式为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 block 374 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 stub 13 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 stub 22 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 stub 31 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<String, String[][]> content, 47 OutputStream os) { 48 // TODO Auto-generated method stub 49 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 stub 58 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 stub 67 if(sheet != null) 68 { 69 return false; 70 } 71 return true; 72 } 73 74 }
本文旨在学习如何用Java导出Excel，具体细节还需要进一步完善。
