如何将POI大数据高效导入Excel进行长尾词分析？

本文共计1338个文字，预计阅读时间需要6分钟。

javaPoi大数据导入导出，详情见附件 ExcelHelper.java

poi大数据导入导出

详见附件 ExcelHelper.java

import java.io.FileOutputStream; import java.io.OutputStream; import java.util.Calendar; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; /** * 每月导出数据 * @author admin * */ public class ExcelHelper { public void sysnMouthData (){ System.out.println("开始导出数据"); /*从数据库中查数据 */ Calendar cal = Calendar.getInstance(); int month = cal.get(Calendar.MONTH); int year = cal.get(Calendar.YEAR); String date = null;// 上一月份 if (month < 10) { date = "" + year + 0 + month + "%"; } else { date = "" + year + month + "%"; } // 创建一个Excel文件 SXSSFWorkbook workbook = new SXSSFWorkbook();//SXSSFWorkbook是poi3.8之后采用的专门处理大数据到execl中 Sheet sheet1 = workbook.createSheet("sheet1"); // 添加表头行 Row hssfRow1 = sheet1.createRow(0); // 设置单元格格式居中 cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 添加表头内容 Cell headCell1 = hssfRow1.createCell(0); headCell1.setCellValue("表头1"); headCell1.setCellStyle(cellStyle); headCell1 = hssfRow1.createCell(1); headCell1.setCellValue("表头2"); headCell1.setCellStyle(cellStyle); headCell1 = hssfRow1.createCell(2); headCell1.setCellValue("表头3"); headCell1.setCellStyle(cellStyle); headCell1 = hssfRow1.createCell(3); headCell1.setCellValue("表头4"); headCell1.setCellStyle(cellStyle); List<> daiqs = null; /** * 数据量过大，但又不能分sheet保存时 分批处理 */ Map map = new HashMap(); map.put("date", date); int total = userDao.countall(date);//从数据库中查询总数据 int page = 1; int minnum = 0; int maxnum = 1; int pagesize = 100000; if (total > pagesize) { page = total / pagesize + 1; } for (int i = 1; i <= page; i++) { minnum = (i - 1) * pagesize; if (i == page) { maxnum = total; } else { maxnum = i * pagesize; } daiqs = userDao.findByTime( date, maxnum, minnum);//从数据库中查询出数据 // 将内存中的数据刷到硬盘中去，这样就不会造成内存溢出 sheet1.getRow(minnum); // 添加数据内容 for (int j = 0; j < daiqs.size(); j++) { hssfRow1 = sheet1.createRow(j + 1 + (i - 1) * pagesize); DaIllegalQuery daiq = daiqs.get(j); //System.out.println(j + 1 + (i - 1) * pagesize); // 创建单元格，并设置值 Cell cell = hssfRow1.createCell(0); cell.setCellValue(daiq.getUserPhone()); // 手机号 cell.setCellStyle(cellStyle); cell = hssfRow1.createCell(1); cell.setCellValue(daiq.getQueryDate()); // 操作时间 cell.setCellStyle(cellStyle); cell = hssfRow1.createCell(2); cell.setCellValue("sdaf"+j); cell.setCellStyle(cellStyle); cell = hssfRow1.createCell(3); if (daiq.getOpercteType().trim().equals("1")) { cell.setCellValue("WEB_WAP"); } else if (daiq.getOpercteType().trim().equals("2")) { cell.setCellValue("WXCS_APP"); } else if (daiq.getOpercteType().trim().equals("3")) { cell.setCellValue("ANDROID_APP"); } else if (daiq.getOpercteType().trim().equals("4")) { cell.setCellValue("IOS_APP"); } else if (daiq.getOpercteType().trim().equals("5")) { cell.setCellValue("12580"); } cell.setCellStyle(cellStyle); cell = hssfRow1.createCell(4); cell.setCellValue(j); cell.setCellStyle(cellStyle); } if (i == page) { // 这里需要将最后剩下的数据也要刷到硬盘中去 sheet1.getRow(total - minnum * page); } } // 保存Excel文件 try { OutputStream outputStream = new FileOutputStream("test" + year + month + ".xlsx"); workbook.write(outputStream); outputStream.close(); } catch (Exception e) { e.printStackTrace(); } } } poi解析execl文件XML的方式

详见附件 ExampleEventUserModelUtil.java

import java.io.BufferedWriter; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; import org.xml.sax.ContentHandler; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory; public class ExampleEventUserModelUtil { private static StylesTable stylesTable; /** * 处理一个sheet * @param filename * @throws Exception */ public void processOneSheet(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader( pkg ); stylesTable = r.getStylesTable(); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); Iterator sheets = r.getSheetsData(); while (sheets.hasNext()) { InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); } } /** * 处理所有sheet * @param filename * @throws Exception */ public void processAllSheets(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader( pkg ); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); Iterator sheets = r.getSheetsData(); while(sheets.hasNext()) { System.out.println("Processing new sheet:\n"); InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); System.out.println(""); } } /** * 获取解析器 * @param sst * @return * @throws SAXException */ public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader( "org.apache.xerces.parsers.SAXParser" ); ContentHandler handler = new SheetHandler(sst); parser.setContentHandler(handler); return parser; } /** * 自定义解析处理器 * See org.xml.sax.helpers.DefaultHandler javadocs */ private static class SheetHandler extends DefaultHandler { private SharedStringsTable sst; private String lastContents; private boolean nextIsString; private List rowlist = new ArrayList (); private int curRow = 0; private int curCol = 0; //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等 private String preRef = null, ref = null; //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格 private String maxRef = null; private CellDataType nextDataType = CellDataType.SSTINDEX; private final DataFormatter formatter = new DataFormatter(); private short formatIndex; private String formatString; //用一个enum表示单元格可能的数据类型 enum CellDataType{ BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL } private SheetHandler(SharedStringsTable sst) { this.sst = sst; } /** * 解析一个element的开始时触发事件 */ public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { // c => cell if(name.equals("c")) { //前一个单元格的位置 if(preRef == null){ preRef = attributes.getValue("r"); }else{ preRef = ref; } //当前单元格的位置 ref = attributes.getValue("r"); this.setNextDataType(attributes); // Figure out if the value is an index in the SST String cellType = attributes.getValue("t"); if(cellType != null && cellType.equals("s")) { nextIsString = true; } else { nextIsString = false; } } // Clear contents cache lastContents = ""; } /** * 根据element属性设置数据类型 * @param attributes */ public void setNextDataType(Attributes attributes){ nextDataType = CellDataType.NUMBER; formatIndex = -1; formatString = null; String cellType = attributes.getValue("t"); String cellStyleStr = attributes.getValue("s"); if ("b".equals(cellType)){ nextDataType = CellDataType.BOOL; }else if ("e".equals(cellType)){ nextDataType = CellDataType.ERROR; }else if ("inlineStr".equals(cellType)){ nextDataType = CellDataType.INLINESTR; }else if ("s".equals(cellType)){ nextDataType = CellDataType.SSTINDEX; }else if ("str".equals(cellType)){ nextDataType = CellDataType.FORMULA; } if (cellStyleStr != null){ int styleIndex = Integer.parseInt(cellStyleStr); XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); formatIndex = style.getDataFormat(); formatString = style.getDataFormatString(); if ("m/d/yy" == formatString){ nextDataType = CellDataType.DATE; //full format is "yyyy-MM-dd hh:mm:ss.SSS"; formatString = "yyyy-MM-dd"; } if (formatString == null){ nextDataType = CellDataType.NULL; formatString = BuiltinFormats.getBuiltinFormat(formatIndex); } } } /** * 解析一个element元素结束时触发事件 */ public void endElement(String uri, String localName, String name) throws SAXException { // Process the last contents as required. // Do now, as characters() may be called more than once if(nextIsString) { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); nextIsString = false; } // v => contents of a cell // Output after we've seen the string contents if ("v".equals(name)|| "t".equals(name)) { String value = this.getDataValue(lastContents.trim(), ""); //补全单元格之间的空单元格 if(!ref.equals(preRef)){ int len = countNullCell(ref, preRef); for(int i=0;i