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.usermo
详见附件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