代码由网络搜集,第二次测试,导入oracle 50.2W 业务数据,每行7列,从开始导入到前台展示使用了 183s
直接上代码,一共三个部分,请自行改动:
工具类部分:
1 package com.hna.hka.rmc.command.util; 2 3 import org.apache.poi.openxml4j.opc.OPCPackage; 4 import org.apache.poi.ss.usermodel.BuiltinFormats; 5 import org.apache.poi.ss.usermodel.DataFormatter; 6 import org.apache.poi.xssf.eventusermodel.XSSFReader; 7 import org.apache.poi.xssf.model.SharedStringsTable; 8 import org.apache.poi.xssf.model.StylesTable; 9 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 10 import org.apache.poi.xssf.usermodel.XSSFRichTextString; 11 import org.xml.sax.*; 12 import org.xml.sax.helpers.DefaultHandler; 13 import org.xml.sax.helpers.XMLReaderFactory; 14 15 import java.io.BufferedWriter; 16 import java.io.InputStream; 17 import java.text.SimpleDateFormat; 18 import java.util.*; 19 20 public class ExampleEventUserModelUtil { 21 private static String cs; 22 private static StylesTable stylesTable; 23 private static List<String> dataList = new ArrayList<String>(); 24 private static List successList = new ArrayList(); 25 private static List failList = new ArrayList(); 26 private static Map map = new HashMap();; 27 28 // @Value("${weight}") 29 // public static String weight; 30 /** 31 * 处理一个sheet 32 * 33 * @param filename 34 * @throws Exception 35 */ 36 public void processOneSheet(String filename) throws Exception { 37 OPCPackage pkg = OPCPackage.open(filename); 38 XSSFReader r = new XSSFReader(pkg); 39 stylesTable = r.getStylesTable(); 40 SharedStringsTable sst = r.getSharedStringsTable(); 41 XMLReader parser = fetchSheetParser(sst); 42 Iterator<InputStream> sheets = r.getSheetsData(); 43 while (sheets.hasNext()) { 44 InputStream sheet = sheets.next(); 45 InputSource sheetSource = new InputSource(sheet); 46 parser.parse(sheetSource); 47 sheet.close(); 48 } 49 50 } 51 52 /** 53 * 处理所有sheet 54 * 55 * @param filename 56 * @throws Exception 57 */ 58 public void processAllSheets(String filename) throws Exception { 59 60 OPCPackage pkg = OPCPackage.open(filename); 61 XSSFReader r = new XSSFReader(pkg); 62 SharedStringsTable sst = r.getSharedStringsTable(); 63 64 XMLReader parser = fetchSheetParser(sst); 65 66 Iterator<InputStream> sheets = r.getSheetsData(); 67 while (sheets.hasNext()) { 68 System.out.println("Processing new sheet: "); 69 InputStream sheet = sheets.next(); 70 InputSource sheetSource = new InputSource(sheet); 71 parser.parse(sheetSource); 72 sheet.close(); 73 System.out.println(""); 74 } 75 } 76 77 /** 78 * 获取解析器 79 * 80 * @param sst 81 * @return 82 * @throws org.xml.sax.SAXException 83 */ 84 public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { 85 XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); 86 ContentHandler handler = new SheetHandler(sst); 87 parser.setContentHandler(handler); 88 return parser; 89 } 90 91 /** 92 * 自定义解析处理器 See org.xml.sax.helpers.DefaultHandler javadocs 93 */ 94 private static class SheetHandler extends DefaultHandler { 95 96 private SharedStringsTable sst; 97 private String lastContents; 98 private boolean nextIsString; 99 100 private List<String> rowlist = new ArrayList<String>(); 101 private int curRow = 0; 102 private int curCol = 0; 103 104 // 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等 105 private String preRef = null, ref = null; 106 // 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格 107 private String maxRef = null; 108 109 private CellDataType nextDataType = CellDataType.SSTINDEX; 110 private final DataFormatter formatter = new DataFormatter(); 111 private short formatIndex; 112 private String formatString; 113 114 // 用一个enum表示单元格可能的数据类型 115 enum CellDataType { 116 BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL 117 } 118 119 private SheetHandler(SharedStringsTable sst) { 120 this.sst = sst; 121 } 122 123 /** 124 * 解析一个element的开始时触发事件 125 */ 126 public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { 127 128 map.put("flag", "start"); 129 // c => cell 130 if (name.equals("c")) { 131 // 前一个单元格的位置 132 if (preRef == null) { 133 preRef = attributes.getValue("r"); 134 } else { 135 preRef = ref; 136 } 137 // 当前单元格的位置 138 ref = attributes.getValue("r"); 139 140 this.setNextDataType(attributes); 141 142 // Figure out if the value is an index in the SST 143 String cellType = attributes.getValue("t"); 144 // if(cellType != null && cellType.equals("s")) { 145 // nextIsString = true; 146 // } else { 147 // nextIsString = false; 148 // } 149 if (cellType == null) { // 处理空单元格问题 150 nextIsString = true; 151 cs = "x"; 152 } else if (cellType != null && cellType.equals("s")) { 153 cs = "s"; 154 nextIsString = true; 155 } else { 156 nextIsString = false; 157 cs = ""; 158 } 159 160 } 161 // Clear contents cache 162 lastContents = ""; 163 } 164 165 /** 166 * 根据element属性设置数据类型 167 * 168 * @param attributes 169 */ 170 public void setNextDataType(Attributes attributes) { 171 172 nextDataType = CellDataType.NUMBER; 173 formatIndex = -1; 174 formatString = null; 175 String cellType = attributes.getValue("t"); 176 String cellStyleStr = attributes.getValue("s"); 177 if ("b".equals(cellType)) { 178 nextDataType = CellDataType.BOOL; 179 } else if ("e".equals(cellType)) { 180 nextDataType = CellDataType.ERROR; 181 } else if ("inlineStr".equals(cellType)) { 182 nextDataType = CellDataType.INLINESTR; 183 } else if ("s".equals(cellType)) { 184 nextDataType = CellDataType.SSTINDEX; 185 } else if ("str".equals(cellType)) { 186 nextDataType = CellDataType.FORMULA; 187 } 188 if (cellStyleStr != null) { 189 int styleIndex = Integer.parseInt(cellStyleStr); 190 XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); 191 formatIndex = style.getDataFormat(); 192 formatString = style.getDataFormatString(); 193 if ("m/d/yy" == formatString) { 194 nextDataType = CellDataType.DATE; 195 // full format is "yyyy-MM-dd hh:mm:ss.SSS"; 196 formatString = "yyyy-MM-dd"; 197 } 198 if (formatString == null) { 199 nextDataType = CellDataType.NULL; 200 formatString = BuiltinFormats.getBuiltinFormat(formatIndex); 201 } 202 } 203 } 204 205 /** 206 * 解析一个element元素结束时触发事件 207 */ 208 public void endElement(String uri, String localName, String name) throws SAXException { 209 // Process the last contents as required. 210 // Do now, as characters() may be called more than once 211 String flag = (String) map.get("flag"); 212 213 if (nextIsString) { 214 if ("s".equals(cs)) { 215 int idx = Integer.parseInt(lastContents); 216 lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); 217 nextIsString = false; 218 } 219 if ("c".equals(name) && "x".equals(cs)) { 220 if ("start".equals(flag)) { 221 rowlist.add(curCol, ""); 222 curCol++; 223 } 224 225 } 226 } 227 228 map.put("flag", "end"); 229 230 // v => contents of a cell 231 // Output after we've seen the string contents 232 if ("v".equals(name) || "t".equals(name)) { 233 String value = this.getDataValue(lastContents.trim(), ""); 234 // 补全单元格之间的空单元格 235 if (!ref.equals(preRef)) { 236 int len = countNullCell(ref, preRef); 237 for (int i = 0; i < len; i++) { 238 rowlist.add(curCol, ""); 239 curCol++; 240 } 241 } 242 rowlist.add(curCol, value); 243 curCol++; 244 } else { 245 // 如果标签名称为 row，这说明已到行尾，调用 optRows() 方法 246 if (name.equals("row")) { 247 String value = ""; 248 // 默认第一行为表头，以该行单元格数目为最大数目 249 if (curRow == 0) { 250 maxRef = ref; 251 } 252 // 补全一行尾部可能缺失的单元格 253 if (maxRef != null) { 254 int len = countNullCell(maxRef, ref); 255 256 for (int i = 0; i <= len; i++) { 257 // rowlist.add(curCol, ""); 258 // curCol++; 259 } 260 } 261 // 拼接一行的数据 262 for (int i = 0; i < rowlist.size(); i++) { 263 /* 264 * if (rowlist.get(i).contains(",")) { value += """ + 265 * rowlist.get(i) + "","; 266 * 267 * } else { 268 */ 269 if (i == rowlist.size() - 1) { 270 value += rowlist.get(i) + ","; 271 if (rowlist.size() < 7) { 272 value += ","; 273 } 274 SimpleDateFormat sdf = new SimpleDateFormat(" yyyy-MM-dd HH:mm:ss "); 275 String str = sdf.format(new Date()); 276 value += str; 277 } else { 278 value += rowlist.get(i) + ","; 279 } 280 // } 281 } 282 // 加换行符 283 value += " "; 284 // try { 285 // writer.write(value); 286 // } catch (IOException e) { 287 // e.printStackTrace(); 288 // } 289 curRow++; 290 // System.out.println(curRow + rowlist.toString()+"------"); 291 // 一行的末尾重置一些数据,此处rowList为最终返回的list 292 dataList.add(value); 293 rowlist.clear(); 294 curCol = 0; 295 preRef = null; 296 ref = null; 297 } 298 } 299 } 300 301 /** 302 * 根据数据类型获取数据 303 * 304 * @param value 305 * @param thisStr 306 * @return 307 */ 308 public String getDataValue(String value, String thisStr) 309 310 { 311 switch (nextDataType) { 312 // 这几个的顺序不能随便交换，交换了很可能会导致数据错误 313 case BOOL: 314 char first = value.charAt(0); 315 thisStr = first == '0' ? "FALSE" : "TRUE"; 316 break; 317 case ERROR: 318 thisStr = ""ERROR:" + value.toString() + '"'; 319 break; 320 case FORMULA: 321 thisStr = '"' + value.toString() + '"'; 322 break; 323 case INLINESTR: 324 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); 325 thisStr = rtsi.toString(); 326 rtsi = null; 327 break; 328 case SSTINDEX: 329 String sstIndex = value.toString(); 330 thisStr = value.toString(); 331 break; 332 case NUMBER: 333 if (formatString != null) { 334 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString) 335 .trim(); 336 } else { 337 thisStr = value; 338 } 339 thisStr = thisStr.replace("_", "").trim(); 340 break; 341 case DATE: 342 try { 343 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString); 344 } catch (NumberFormatException ex) { 345 thisStr = value.toString(); 346 } 347 thisStr = thisStr.replace(" ", ""); 348 break; 349 default: 350 thisStr = ""; 351 break; 352 } 353 return thisStr; 354 } 355 356 /** 357 * 获取element的文本数据 358 */ 359 public void characters(char[] ch, int start, int length) throws SAXException { 360 lastContents += new String(ch, start, length); 361 } 362 363 /** 364 * 计算两个单元格之间的单元格数目(同一行) 365 * 366 * @param ref 367 * @param preRef 368 * @return 369 */ 370 public int countNullCell(String ref, String preRef) { 371 // excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD 372 String xfd = ref.replaceAll("\d+", ""); 373 String xfd_1 = preRef.replaceAll("\d+", ""); 374 375 xfd = fillChar(xfd, 3, '@', true); 376 xfd_1 = fillChar(xfd_1, 3, '@', true); 377 378 char[] letter = xfd.toCharArray(); 379 char[] letter_1 = xfd_1.toCharArray(); 380 int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]); 381 return res - 1; 382 } 383 384 /** 385 * 字符串的填充 386 * 387 * @param str 388 * @param len 389 * @param let 390 * @param isPre 391 * @return 392 */ 393 String fillChar(String str, int len, char let, boolean isPre) { 394 int len_1 = str.length(); 395 if (len_1 < len) { 396 if (isPre) { 397 for (int i = 0; i < (len - len_1); i++) { 398 str = let + str; 399 } 400 } else { 401 for (int i = 0; i < (len - len_1); i++) { 402 str = str + let; 403 } 404 } 405 } 406 return str; 407 } 408 } 409 410 static BufferedWriter writer = null; 411 412 @SuppressWarnings("unused") 413 public List<String> getListData(String url) { 414 dataList = new ArrayList<String>(); 415 ExampleEventUserModelUtil example = new ExampleEventUserModelUtil(); 416 // String str = "Book1"; 417 String filename = url; 418 /*System.out.println("-- 程序开始 --"); 419 long time_1 = System.currentTimeMillis();*/ 420 try { 421 // writer = new BufferedWriter(new OutputStreamWriter(new 422 // FileOutputStream("C:\users40.xlsx"))); 423 try { 424 example.processOneSheet(filename); 425 } catch (Exception e) { 426 // TODO Auto-generated catch block 427 e.printStackTrace(); 428 } 429 } finally { 430 // writer.close(); 431 } 432 long time_2 = System.currentTimeMillis(); 433 /*System.out.println(dataList.size()); 434 System.out.println("-- 程序结束1 --"); 435 System.out.println("-- 耗时1 --" + (time_2 - time_1) / 1000 + "s"); 436 437 System.out.println("-- 程序结束2 --"); 438 System.out.println("-- 耗时2--" + (time_2 - time_1) / 1000 + "s");*/ 439 return dataList; 440 } 441 442 public static void main(String[] args) { 443 dataList = new ArrayList(); 444 ExampleEventUserModelUtil example = new ExampleEventUserModelUtil(); 445 // String str = "Book1"; 446 String filename = "C:\Users\Administrator\Desktop\kasher.xlsx"; 447 System.out.println("-- 程序开始 --"); 448 //long time_1 = System.currentTimeMillis(); 449 try { 450 // writer = new BufferedWriter(new OutputStreamWriter(new 451 // FileOutputStream("C:\users40.xlsx"))); 452 try { 453 example.processOneSheet(filename); 454 } catch (Exception e) { 455 // TODO Auto-generated catch block 456 e.printStackTrace(); 457 } 458 } finally { 459 // writer.close(); 460 } 461 /*long time_2 = System.currentTimeMillis(); 462 System.out.println(dataList.size()); 463 System.out.println("-- 程序结束1 --"); 464 System.out.println("-- 耗时1 --" + (time_2 - time_1) / 1000 + "s"); 465 466 System.out.println("-- 程序结束2 --"); 467 System.out.println("-- 耗时2--" + (time_2 - time_1) / 1000 + "s");*/ 468 } 469 470 }
业务层调用部分:持久层为Hibernate 我选用的是JDBC请自行选用
1 ExampleEventUserModelUtil excleUtil = new ExampleEventUserModelUtil(); 2 List<String> listData = excleUtil.getListData(文件路径); 3 try { 4 testDao.saveList(listData); 5 } catch (Exception e) { 6 // TODO Auto-generated catch block 7 e.printStackTrace(); 8 } 9 listData.clear();
持久层:
1 Transaction tx = this.getHibernateTemplate().getSessionFactory().openSession().beginTransaction(); 2 PreparedStatement ps =this.getHibernateTemplate().getSessionFactory().openSession().connection().prepareStatement("insert into agent_list_updated (AGENT_ID,AGENT_NAME,POS,CITY,COUNTRY,AREA,AGENT_NA,IMPORT_TIME) values(?,?,?,?,?,?,?,?)"); 3 for (int i = 1; i < listData.size(); i++) { 4 String[] split = listData.get(i).split(","); 5 ps.setString(1, split[0]); 6 ps.setString(2, split[1]); 7 ps.setString(3, split[2]); 8 ps.setString(4, split[3]); 9 ps.setString(5, split[4]); 10 ps.setString(6, split[5]); 11 ps.setString(7, split[6]); 12 ps.setString(8, split[7]); 13 ps.addBatch(); 14 if(i%1000==0){ 15 ps.executeBatch(); 16 } 17 } 18 ps.executeBatch(); 19 org.hibernate.classic.Session session = this.getHibernateTemplate().getSessionFactory().openSession(); 20 tx.commit(); 21 ps.close(); 22 this.getHibernateTemplate().getSessionFactory().openSession().close();