poi大数据将excel2007导入数据库
1 package com.jeeframe.cms.updata.service.impl; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 import java.sql.Connection; 6 import java.sql.DriverManager; 7 import java.sql.PreparedStatement; 8 import java.sql.SQLException; 9 import java.sql.Types; 10 import java.util.List; 11 12 import javax.xml.parsers.ParserConfigurationException; 13 import javax.xml.parsers.SAXParser; 14 import javax.xml.parsers.SAXParserFactory; 15 16 import org.apache.poi.openxml4j.exceptions.OpenXML4JException; 17 import org.apache.poi.openxml4j.opc.OPCPackage; 18 import org.apache.poi.openxml4j.opc.PackageAccess; 19 import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; 20 import org.apache.poi.xssf.eventusermodel.XSSFReader; 21 import org.apache.poi.xssf.model.StylesTable; 22 import org.xml.sax.InputSource; 23 import org.xml.sax.SAXException; 24 import org.xml.sax.XMLReader; 25 26 public class ExcelReader { 27 28 /** 29 * 解析并显示一个表的内容和使用指定的样式 30 * 31 * @param styles 32 * @param strings 33 * @param sheetInputStream 34 */ 35 public static List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, 36 InputStream sheetInputStream, int minColumns) 37 throws IOException, ParserConfigurationException, SAXException { 38 39 InputSource sheetSource = new InputSource(sheetInputStream); 40 SAXParserFactory saxFactory = SAXParserFactory.newInstance(); 41 SAXParser saxParser = saxFactory.newSAXParser(); 42 XMLReader sheetParser = saxParser.getXMLReader(); 43 ExcelReaderHandler handler = new ExcelReaderHandler(styles, strings, minColumns, System.out); 44 45 sheetParser.setContentHandler(handler); 46 sheetParser.parse(sheetSource); 47 return handler.getRows(); 48 } 49 50 /** 51 * 解析第一个sheet 52 * 53 * @param path 54 * @param minColumns 55 * @return List<String[]> 56 * @throws IOException 57 * @throws OpenXML4JException 58 * @throws ParserConfigurationException 59 * @throws SAXException 60 */ 61 public static List<String[]> processOneSheet(String path, int minColumns) 62 throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { 63 OPCPackage p = OPCPackage.open(path, PackageAccess.READ); 64 ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p); 65 XSSFReader xssfReader = new XSSFReader(p); 66 StylesTable styles = xssfReader.getStylesTable(); 67 InputStream stream = xssfReader.getSheet("rId1"); 68 List<String[]> list = processSheet(styles, strings, stream, minColumns); 69 stream.close(); 70 return list; 71 } 72 73 public static void main(String[] args) throws Exception { 74 /* 75 * long begin = System.currentTimeMillis() ; List<String[]> list = 76 * ExcelReader.processOneSheet("d:\\201401-ds.xlsx" , 18); 77 * //List<String[]> list = ExcelReader.processOneSheet("d:\\out.xlsx" , 78 * 10); for (String cell : list.get(1)) { System.out.print(cell + " "); 79 * System.out.println(cell == null); } long end = 80 * System.currentTimeMillis() ; System.out.println("用时:" + (end - begin) 81 * /1000 + "秒"); 82 */ 83 long begin = System.currentTimeMillis(); 84 System.out.println(begin); 85 List<String[]> list = ExcelReader.processOneSheet("d:\\test.xlsx", 18); 86 long end = System.currentTimeMillis(); 87 System.out.println("读取用时:" + (end - begin) / 1000 + "秒,总量:" + list.size()); 88 Connection conn = getNew_Conn(); 89 conn.setAutoCommit(false); 90 PreparedStatement pstmt = conn.prepareStatement("insert into temp_table values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); 91 for (int i = 0; i < list.size(); i++) { 92 String[] row = list.get(i); 93 for (int index = 1; index <= 18; index++) { 94 if (row[index - 1] == null) { 95 pstmt.setNull(index, Types.NULL); 96 } else { 97 pstmt.setObject(index, row[index - 1]); 98 } 99 } 100 101 pstmt.addBatch(); 102 if (i > 0 && i % 10000 == 0) { 103 pstmt.executeBatch(); 104 System.out.println("提交:" + i); 105 } 106 } 107 pstmt.executeBatch(); 108 conn.commit(); 109 pstmt.close(); 110 conn.close(); 111 end = System.currentTimeMillis(); 112 System.out.println("插入用时:" + (end - begin) / 1000 + "秒"); 113 } 114 115 private static Connection getNew_Conn() { 116 Connection conn = null; 117 try { 118 Class.forName("com.mysql.jdbc.Driver"); 119 conn = DriverManager.getConnection( 120 "jdbc:mysql://localhost:3306/jeeframe_cms?useUnicode=true&characterEncoding=UTF-8", "root", 121 "1392010"); 122 } catch (ClassNotFoundException e) { 123 // TODO Auto-generated catch block 124 e.printStackTrace(); 125 } catch (SQLException e) { 126 // TODO Auto-generated catch block 127 e.printStackTrace(); 128 } 129 130 return conn; 131 } 132 133 }
1 package com.jeeframe.cms.updata.service.impl; 2 3 import java.io.PrintStream; 4 import java.text.SimpleDateFormat; 5 import java.util.ArrayList; 6 import java.util.Date; 7 import java.util.List; 8 9 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 10 import org.apache.poi.ss.usermodel.BuiltinFormats; 11 import org.apache.poi.ss.usermodel.DataFormatter; 12 import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; 13 import org.apache.poi.xssf.model.StylesTable; 14 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 15 import org.apache.poi.xssf.usermodel.XSSFRichTextString; 16 import org.xml.sax.Attributes; 17 import org.xml.sax.SAXException; 18 import org.xml.sax.helpers.DefaultHandler; 19 20 public class ExcelReaderHandler extends DefaultHandler { 21 22 /** 23 * The type of the data value is indicated by an attribute on the cell. The 24 * value is usually in a "v" element within the cell. 25 */ 26 enum xssfDataType { 27 BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, 28 } 29 30 /** 31 * Table with styles 32 */ 33 private StylesTable stylesTable; 34 35 /** 36 * Table with unique strings 37 */ 38 private ReadOnlySharedStringsTable sharedStringsTable; 39 40 /** 41 * Destination for data 42 */ 43 private final PrintStream output; 44 45 /** 46 * Number of columns to read starting with leftmost 47 */ 48 private final int minColumnCount; 49 50 // Set when V start element is seen 51 private boolean vIsOpen; 52 53 // Set when cell start element is seen; 54 // used when cell close element is seen. 55 private xssfDataType nextDataType; 56 57 // Used to format numeric cell values. 58 private short formatIndex; 59 private String formatString; 60 private final DataFormatter formatter; 61 62 private int thisColumn = -1; 63 // The last column printed to the output stream 64 private int lastColumnNumber = -1; 65 66 // Gathers characters as they are seen. 67 private StringBuffer value; 68 private String[] record; 69 private List<String[]> rows = new ArrayList<String[]>(); 70 private boolean isCellNull = false; 71 72 /** 73 * Accepts objects needed while parsing. 74 * 75 * @param styles 76 * Table of styles 77 * @param strings 78 * Table of shared strings 79 * @param cols 80 * Minimum number of columns to show 81 * @param target 82 * Sink for output 83 */ 84 public ExcelReaderHandler(StylesTable styles, ReadOnlySharedStringsTable strings, int cols, PrintStream target) { 85 this.stylesTable = styles; 86 this.sharedStringsTable = strings; 87 this.minColumnCount = cols; 88 this.output = target; 89 this.value = new StringBuffer(); 90 this.nextDataType = xssfDataType.NUMBER; 91 this.formatter = new DataFormatter(); 92 record = new String[this.minColumnCount]; 93 rows.clear();// 每次读取都清空行集合 94 } 95 96 /* 97 * (non-Javadoc) 98 * 99 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, 100 * java.lang.String, java.lang.String, org.xml.sax.Attributes) 101 */ 102 public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { 103 104 if ("inlineStr".equals(name) || "v".equals(name)) { 105 vIsOpen = true; 106 // Clear contents cache 107 value.setLength(0); 108 } 109 // c => cell 110 else if ("c".equals(name)) { 111 // Get the cell reference 112 String r = attributes.getValue("r"); 113 int firstDigit = -1; 114 for (int c = 0; c < r.length(); ++c) { 115 if (Character.isDigit(r.charAt(c))) { 116 firstDigit = c; 117 break; 118 } 119 } 120 thisColumn = nameToColumn(r.substring(0, firstDigit)); 121 122 // Set up defaults. 123 this.nextDataType = xssfDataType.NUMBER; 124 this.formatIndex = -1; 125 this.formatString = null; 126 String cellType = attributes.getValue("t"); 127 String cellStyleStr = attributes.getValue("s"); 128 if ("b".equals(cellType)) 129 nextDataType = xssfDataType.BOOL; 130 else if ("e".equals(cellType)) 131 nextDataType = xssfDataType.ERROR; 132 else if ("inlineStr".equals(cellType)) 133 nextDataType = xssfDataType.INLINESTR; 134 else if ("s".equals(cellType)) 135 nextDataType = xssfDataType.SSTINDEX; 136 else if ("str".equals(cellType)) 137 nextDataType = xssfDataType.FORMULA; 138 else if (cellStyleStr != null) { 139 // It's a number, but almost certainly one 140 // with a special style or format 141 int styleIndex = Integer.parseInt(cellStyleStr); 142 XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); 143 this.formatIndex = style.getDataFormat(); 144 this.formatString = style.getDataFormatString(); 145 if (this.formatString == null) 146 this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex); 147 } 148 } 149 } 150 151 /* 152 * (non-Javadoc) 153 * 154 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, 155 * java.lang.String, java.lang.String) 156 */ 157 public void endElement(String uri, String localName, String name) throws SAXException { 158 159 String thisStr = null; 160 161 // v => contents of a cell 162 if ("v".equals(name)) { 163 // Process the value contents as required. 164 // Do now, as characters() may be called more than once 165 switch (nextDataType) { 166 167 case BOOL: 168 char first = value.charAt(0); 169 thisStr = first == '0' ? "FALSE" : "TRUE"; 170 break; 171 172 case ERROR: 173 thisStr = "\"ERROR:" + value.toString() + '"'; 174 break; 175 176 case FORMULA: 177 // A formula could result in a string value, 178 // so always add double-quote characters. 179 // thisStr = '"' + value.toString() + '"'; 180 thisStr = value.toString(); 181 break; 182 183 case INLINESTR: 184 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); 185 // thisStr = '"' + rtsi.toString() + '"'; 186 thisStr = rtsi.toString(); 187 break; 188 189 case SSTINDEX: 190 String sstIndex = value.toString(); 191 try { 192 int idx = Integer.parseInt(sstIndex); 193 XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)); 194 // thisStr = '"' + rtss.toString() + '"'; 195 thisStr = rtss.toString(); 196 } catch (NumberFormatException ex) { 197 output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString()); 198 } 199 break; 200 201 case NUMBER: 202 String n = value.toString(); 203 // 判断是否是日期格式 204 if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) { 205 Double d = Double.parseDouble(n); 206 Date date = HSSFDateUtil.getJavaDate(d); 207 thisStr = formateDateToString(date); 208 } else if (this.formatString != null) 209 thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, 210 this.formatString); 211 else 212 thisStr = n; 213 break; 214 215 default: 216 thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; 217 break; 218 } 219 220 // Output after we've seen the string contents 221 // Emit commas for any fields that were missing on this row 222 if (lastColumnNumber == -1) { 223 lastColumnNumber = 0; 224 } 225 // 判断单元格的值是否为空 226 if (thisStr == null || "".equals(isCellNull)) { 227 isCellNull = true;// 设置单元格是否为空值 228 } 229 record[thisColumn] = thisStr; 230 // Update column 231 if (thisColumn > -1) 232 lastColumnNumber = thisColumn; 233 234 } else if ("row".equals(name)) { 235 236 // Print out any missing commas if needed 237 if (this.minColumnCount > 0) { 238 // Columns are 0 based 239 if (lastColumnNumber == -1) { 240 lastColumnNumber = 0; 241 } 242 if (isCellNull == false && record[0] != null && record[1] != null)// 判断是否空行 243 { 244 rows.add(record.clone()); 245 isCellNull = false; 246 for (int i = 0; i < record.length; i++) { 247 record[i] = null; 248 } 249 } 250 } 251 lastColumnNumber = -1; 252 } 253 254 } 255 256 public List<String[]> getRows() { 257 return rows; 258 } 259 260 public void setRows(List<String[]> rows) { 261 this.rows = rows; 262 } 263 264 /** 265 * Captures characters only if a suitable element is open. Originally was 266 * just "v"; extended for inlineStr also. 267 */ 268 public void characters(char[] ch, int start, int length) throws SAXException { 269 if (vIsOpen) 270 value.append(ch, start, length); 271 } 272 273 /** 274 * Converts an Excel column name like "C" to a zero-based index. 275 * 276 * @param name 277 * @return Index corresponding to the specified name 278 */ 279 private int nameToColumn(String name) { 280 int column = -1; 281 for (int i = 0; i < name.length(); ++i) { 282 int c = name.charAt(i); 283 column = (column + 1) * 26 + c - 'A'; 284 } 285 return column; 286 } 287 288 private String formateDateToString(Date date) { 289 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期 290 return sdf.format(date); 291 } 292 }