大数据写入Excel

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BigGridDemo {

    /**
     * Create a library of cell styles.
     */
     public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
        Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
//        XSSFDataFormat fmt = wb.createDataFormat();

        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
//        style1.setDataFormat(fmt.getFormat("0.0%"));
        styles.put("percent", style1);

        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//        style2.setDataFormat(fmt.getFormat("0.0X"));
        styles.put("coeff", style2);

        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
//        style3.setDataFormat(fmt.getFormat("$#,##0.00"));
        styles.put("currency", style3);

        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
//        style4.setDataFormat(fmt.getFormat("mmm dd"));
        styles.put("date", style4);

        XSSFCellStyle style5 = wb.createCellStyle();
        XSSFFont headerFont = wb.createFont();
        headerFont.setBold(true);
        style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style5.setFont(headerFont);
        styles.put("header", style5);

        return styles;
    }

    public static void generate(OutputStreamWriter out,String[] titles,List<String[]> data) throws Exception {
        SpreadsheetWriter sw = new SpreadsheetWriter(out);
        sw.beginSheet();
        //insert header row
        sw.insertRow(0);
        for(int i=0;i<titles.length;i++){
          sw.createCell(i, titles[i]);
        }
        sw.endRow();
        //write data rows
        for (int rownum = 0; rownum < data.size(); rownum++) {
         String[] rowData=data.get(rownum);
            sw.insertRow(rownum+1);
            for(int i=0;i<rowData.length;i++){
              sw.createCell(i, rowData[i]);
            }
            sw.endRow();
        }
        sw.endSheet();
    }
    /**
     *
     * TODO 大数据写入方法
     * @param sheetName sheet名称
     * @param fileName 文件名称
     * @param titles excel标题
     * @param list excel数据内容
     * @throws Exception
     * void
     */
    public static void init(String sheetName,String fileName,String[] titles,List<String[]> list) throws Exception{
       XSSFWorkbook wb = new XSSFWorkbook();
          XSSFSheet sheet = wb.createSheet(sheetName);
//          Map<String, XSSFCellStyle> styles = BigGridDemo.createStyles(wb);
          //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
          String sheetRef = sheet.getPackagePart().getPartName().getName();
          //save the template
          String tempFile=BigGridDemo.class.getResource("/").getPath()+"template.xlsx";
          FileOutputStream os = new FileOutputStream(tempFile);
          wb.write(os);
          os.close();

          //Step 2. Generate XML file.
          File tmp = File.createTempFile("sheet", ".xml");
//          Writer fw = new FileWriter(tmp);
          OutputStreamWriter osw=new OutputStreamWriter(new FileOutputStream(tmp),"UTF-8");
      
          BigGridDemo.generate(osw,titles,list);
          osw.close();
          //Step 3. Substitute the template entry with the generated data
          FileOutputStream out = new FileOutputStream(fileName);
          BigGridDemo.substitute(new File(tempFile), tmp, sheetRef.substring(1), out);
          out.close();
    }

    /**
     *
     * @param zipfile the template file
     * @param tmpfile the XML file with the sheet data
     * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
     * @param out the stream to write the result to
     */
       public static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
        ZipFile zip = new ZipFile(zipfile);

        ZipOutputStream zos = new ZipOutputStream(out);

        @SuppressWarnings("unchecked")
        Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
        while (en.hasMoreElements()) {
            ZipEntry ze = en.nextElement();
            if(!ze.getName().equals(entry)){
                zos.putNextEntry(new ZipEntry(ze.getName()));
                InputStream is = zip.getInputStream(ze);
                copyStream(is, zos);
                is.close();
            }
        }
        zos.putNextEntry(new ZipEntry(entry));
        InputStream is = new FileInputStream(tmpfile);
        copyStream(is, zos);
        is.close();

        zos.close();
    }

    private static void copyStream(InputStream in, OutputStream out) throws IOException {
        byte[] chunk = new byte[1024];
        int count;
        while ((count = in.read(chunk)) >=0 ) {
          out.write(chunk,0,count);
        }
    }

    /**
     * Writes spreadsheet data in a Writer.
     * (YK: in future it may evolve in a full-featured API for streaming data in Excel)
     */
    public static class SpreadsheetWriter {
        private final OutputStreamWriter _out;
        private int _rownum;

        public SpreadsheetWriter(OutputStreamWriter out){
            _out = out;
        }

        public void beginSheet() throws IOException {
            _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                    "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
            _out.write("<sheetData>\n");
        }

        public void endSheet() throws IOException {
            _out.write("</sheetData>");
            _out.write("</worksheet>");
        }

        /**
         * Insert a new row
         *
         * @param rownum 0-based row number
         */
        public void insertRow(int rownum) throws IOException {
            _out.write("<row r=\""+(rownum+1)+"\">\n");
            this._rownum = rownum;
        }

        /**
         * Insert row end marker
         */
        public void endRow() throws IOException {
            _out.write("</row>\n");
        }

        public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
            if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
            _out.write(">");
            _out.write("<is><t>"+value+"</t></is>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, String value) throws IOException {
            createCell(columnIndex, value, -1);
        }

        public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
            String ref = new CellReference(_rownum, columnIndex).formatAsString();
            _out.write("<c r=\""+ref+"\" t=\"n\"");
            if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
            _out.write(">");
            _out.write("<v>"+value+"</v>");
            _out.write("</c>");
        }

        public void createCell(int columnIndex, double value) throws IOException {
            createCell(columnIndex, value, -1);
        }

        public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
            createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
        }
    }
}
-----------------------------------测试方法---------------------------

 public static void main(String[] args) throws Exception {

           String[] titles={"序号","名称","话务量","地区","时间"};
           List<String[]>  list=new ArrayList<String[]>();
           for(int i=0;i<500000;i++){
            String[] data={(i+1)+"","武侯区","23.11","成都","2012-10-23 12:00:00"};
      list.add(data);
     }
           BigGridDemo.init("大数据sheet", "down.xlsx", titles, list);
    }