package scrollable.excel.reader;

import java.io.IOException; import java.io.InputStream; import java.util.Locale; import java.util.concurrent.ArrayBlockingQueue; import java.util.concurrent.BlockingQueue;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.POIXMLProperties; import org.apache.poi.POIXMLTextExtractor; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.util.SAXHelper; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler; import org.apache.poi.xssf.model.StylesTable; import org.apache.xmlbeans.XmlException; import org.xml.sax.ContentHandler; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader;

public class ScrollDownXSSFEventBasedExcelExtractor{

 private OPCPackage container;  //private POIXMLProperties properties;  private int sheetId;

 private transient BlockingQueue<SimpleRow> rowsQueue = new ArrayBlockingQueue<SimpleRow>(1);  private transient boolean hasNextRow = true;  private transient SimpleRow currentRow;

 public ScrollDownXSSFEventBasedExcelExtractor(String fileName, int sheetId) throws XmlException, OpenXML4JException, IOException {   this.container = OPCPackage.open(fileName);   //this.properties = new POIXMLProperties(container);   this.sheetId = sheetId;   }

 public void processSheet(){   new XMLParser().start();  }    public SimpleRow nextRow(){   SimpleRow row = null;   if(hasNextRow){    while(true)    {     try {      row = rowsQueue.take();      break;     } catch (InterruptedException e) {      // TODO Auto-generated catch block      e.printStackTrace();     }    }   }   return row;  }      public boolean hasNextRow(){   return hasNextRow;  }  

protected class ScrollableSheetContentsHandler implements SheetContentsHandler  {   @Override   public void startRow(int rowNum) {    currentRow = new SimpleRow(rowNum);   }

  @Override   public void endRow() {    if(currentRow != null)    {     while(true){      //try until current row is in rowsQueue      try {       rowsQueue.put(currentRow);       break;      } catch (InterruptedException e) {             }     }    }   }

  @Override   public void cell(String cellRef, String formattedValue) {    SimpleCell cell = new SimpleCell(cellRef, formattedValue);    currentRow.addCell(cell);   }

  @Override   public void headerFooter(String text, boolean isHeader, String tagName) {    // We don‘t include headers in the output yet, so ignore   }  }    protected class ScrollableXSSFSheetXMLHandler extends XSSFSheetXMLHandler {

  public ScrollableXSSFSheetXMLHandler(StylesTable styles,     ReadOnlySharedStringsTable strings,     SheetContentsHandler sheetContentsHandler) {    super(styles, strings, sheetContentsHandler, false);   }

  @Override   public void endElement(String uri, String localName, String name) throws SAXException {    super.endElement(uri, localName, name);    //end sheet    if("sheetData".equals(name))    {     hasNextRow = false;    }   }

 }    protected class XMLParser extends Thread{

  @Override   public void run() {    try {    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(container);          XSSFReader xssfReader = new XSSFReader(container);          StylesTable styles = xssfReader.getStylesTable();          InputStream  sheetInputStream = xssfReader.getSheet("rId"+sheetId);    InputSource sheetSource = new InputSource(sheetInputStream);             XMLReader sheetParser = SAXHelper.newXMLReader();     SheetContentsHandler sheetContentsExtractor = new ScrollableSheetContentsHandler();     ContentHandler handler = new ScrollableXSSFSheetXMLHandler(styles, strings, sheetContentsExtractor);     sheetParser.setContentHandler(handler);     sheetParser.parse(sheetSource);    } catch (ParserConfigurationException | IOException | SAXException | OpenXML4JException e) {     throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());    }finally{     if (container != null) {      try {       container.close();      } catch (IOException e) {       e.printStackTrace();      }     }    }   }     }

}

package scrollable.excel.reader;

public class SimpleCell {  private String refId;  private String textValue;    public SimpleCell(){}  public SimpleCell(String refId, String textValue){   this.refId = refId;   this.textValue = http://www.mamicode.com/textValue; } public String getRefId() { return refId; } public void setRefId(String refId) { this.refId = refId; } public String getTextValue() { return textValue; } public void setTextValue(String textValue) { this.textValue = textValue; } }

package scrollable.excel.reader;

import java.util.Collection; import java.util.LinkedHashMap; import java.util.Map;

public class SimpleRow {  private Map<String, SimpleCell> cells = new LinkedHashMap<String,SimpleCell>();  private int rowIndex;    public SimpleRow(){}  public SimpleRow(int rowNum){   this.rowIndex = rowNum;  }    public int getRowIndex() {   return rowIndex;  }  public void setRowIndex(int rowIndex) {   this.rowIndex = rowIndex;  }    public void addCell(SimpleCell cell){   this.cells.put(cell.getRefId(), cell);  }    public SimpleCell getCellByRefId(String cellRef)  {   return this.cells.get(cellRef);  }    public Collection<SimpleCell> getCellsInRow(){   return this.cells.values();  }    public String toString(){   StringBuilder sb = new StringBuilder();   sb.append("row ").append(rowIndex);   Collection<SimpleCell> cells = this.getCellsInRow();   for(SimpleCell cell : cells)   {    sb.append("\n").append("\t").append(cell.getRefId()).append("\t").append(cell.getTextValue());   }   return sb.toString();  } }

package block.excel.writer;

import java.io.FileOutputStream; import java.io.IOException; import java.util.Collection; import java.util.List;

import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import scrollable.excel.reader.SimpleCell; import scrollable.excel.reader.SimpleRow;

public class SXSSFExcelWritor {  public static final int DEFAULT_CACHED_ROWS = 100;  private int cachedRowSize = DEFAULT_CACHED_ROWS;

 private SXSSFWorkbook workbook;  private String fileName;  private Sheet sheet;  private FileOutputStream out;

 public SXSSFExcelWritor() {  }

 public SXSSFExcelWritor(String fileName) {   this.fileName = fileName;  }

 public SXSSFExcelWritor(String fileName, int cachedRowSize) {   this(fileName);   this.cachedRowSize = cachedRowSize;  }    public void init() throws IOException{   workbook= new SXSSFWorkbook(cachedRowSize);   sheet = workbook.createSheet();   out = new FileOutputStream(fileName);  }    public void close(){   if(out != null)   {    try {     workbook.dispose();     out.close();    } catch (IOException e) {     e.printStackTrace();    }   }  }

 public void write(List<SimpleRow> rows) throws IOException{   for(SimpleRow row : rows)   {    Row currentRow = sheet.createRow(row.getRowIndex());    Collection<SimpleCell> cells = row.getCellsInRow();    for(SimpleCell cell: cells)    {     CellReference cellRef = new CellReference(cell.getRefId());     int colIndex = cellRef.getCol();          Cell currentCell = currentRow.createCell(colIndex);     currentCell.setCellValue(cell.getTextValue());    }   }   workbook.write(out);   workbook.dispose();  }

 public static void main(String[] args) throws Throwable {   SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory,              // exceeding rows will be              // flushed to disk   Sheet sh = wb.createSheet();   for (int rownum = 0; rownum < 100000; rownum++) {    Row row = sh.createRow(rownum);    for (int cellnum = 0; cellnum < 10; cellnum++) {     Cell cell = row.createCell(cellnum);     String address = new CellReference(cell).formatAsString();     cell.setCellValue(address);    }

  }

  // Rows with rownum < 900 are flushed and not accessible   for (int rownum = 0; rownum < 900; rownum++) {    // Assert.assertNull(sh.getRow(rownum));   }

  // ther last 100 rows are still in memory   for (int rownum = 900; rownum < 1000; rownum++) {    // Assert.assertNotNull(sh.getRow(rownum));   }

  FileOutputStream out = new FileOutputStream("C:\\Users\\YZM\\Desktop\\test2.xlsx");   wb.write(out);   out.close();

  // dispose of temporary files backing this workbook on disk   wb.dispose();  }

}

package ui;

import java.awt.BorderLayout; import java.awt.Color; import java.awt.Dimension; import java.awt.FlowLayout; import java.awt.event.WindowAdapter; import java.awt.event.WindowEvent; import java.awt.event.WindowListener;

import javax.swing.Box; import javax.swing.BoxLayout; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTextArea; import javax.swing.JTextField; import javax.swing.WindowConstants; import javax.swing.border.EmptyBorder;

public class MainWindow extends JFrame{

 private static final long serialVersionUID = 1L;  private static final int DEFAULT_WIDTH = 800;  private static final int DEFAULT_HEIGHT = 600;

 public MainWindow(String name) {   this.setTitle(name);   this.setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT);  }

 public JPanel createPIExtractorContentPanel() {   JPanel piExtractorPanel = new JPanel(new BorderLayout());      JPanel headerPanel = new JPanel();   headerPanel.setLayout(new BoxLayout(headerPanel, BoxLayout.Y_AXIS));   headerPanel.setBorder(new EmptyBorder(10, 10, 10, 10));      JPanel labelPanel = new JPanel(new FlowLayout(FlowLayout.LEFT));   JLabel fileChoserLabel = new JLabel("Please chose a source excel file(support .xlsx file only):");   labelPanel.add(fileChoserLabel);   headerPanel.add(labelPanel);         JPanel fileChoserPanel = new JPanel();   fileChoserPanel.setLayout(new BoxLayout(fileChoserPanel, BoxLayout.X_AXIS));      JTextField fileNameInput = new JTextField();   fileNameInput.setEnabled(false);   fileChoserPanel.add(fileNameInput);      fileChoserPanel.add(Box.createHorizontalStrut(10));      JButton fileChoserButton = new JButton("Chose File");   fileChoserPanel.add(fileChoserButton);      headerPanel.add(fileChoserPanel);

  piExtractorPanel.add(headerPanel, BorderLayout.NORTH);      //center   JPanel logPanel = new JPanel();   //JScrollPane scrollPanel=new JScrollPane();   JTextArea textArea = new JTextArea(25,80);   textArea.setLineWrap(true);   textArea.setWrapStyleWord(true);      logPanel.add(new JScrollPane(textArea));      piExtractorPanel.add(logPanel, BorderLayout.CENTER);

  return piExtractorPanel;

 }

 public static void main(String[] args) {   MainWindow mw = new MainWindow("DA Tool");   mw.add(mw.createPIExtractorContentPanel());   mw.setVisible(true);   mw.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);  }     }

extractor