`
miyazaki
  • 浏览: 16939 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

POI读写海量Excel

阅读更多

 

目前处理Excel的开源javaAPI主要有两种,一是JxlJava Excel API,Jxl只支持Excel2003以下的版本。另外一种是ApacheJakarta POI,相比于Jxl,POI对微软办公文档的支持更加强大,但是它使用复杂,上手慢。POI可支持更高的Excel版本2007。对Excel的读取,POI有两种模式,一是用户模式,这种方式同Jxl的使用很类似,使用简单,都是将文件一次性读到内存,文件小的时候,没有什么问题,当文件大的时候,就会出现OutOfMemory的内存溢出问题。第二种是事件驱动模式,拿Excel2007来说,其内容采用XML的格式来存储,所以处理excel就是解析XML,而目前使用事件驱动模式解析XMLAPISAX(Simple API for XML),这种模型在读取XML文档时,并没有将整个文档读入内存,而是按顺序将整个文档解析完,在解析过程中,会主动产生事件交给程序中相应的处理函数来处理当前内容。因此这种方式对系统资源要求不高,可以处理海量数据。笔者曾经做过测试,这种方法处理一千万条,每条五列的数据花费大约11分钟。可见处理海量数据的文件事件驱动是一个很好的方式。而本文中用到的AbstractExcel2003ReaderAbstractExcel2007ReaderExcel的读取都是采用这种POI的事件驱动模式。至于Excel的写操作,对较高版本的Excel2007POI提供了很好的支持,主要流程是第一步构建工作薄和电子表格对象,第二步在一个流中构建文本文件,第三步使用流中产生的数据替换模板中的电子表格。这种方式也可以处理海量数据文件。AbstractExcel2007Writer就是使用这种方式进行写操作。对于写入较低版本的Excel2003POI使用了用户模式来处理,就是将整个文档加载进内存,如果数据量大的话就会出现内存溢出的问题,Excel2003Writer就是使用这种方式。据笔者的测试,如果数据量大于3万条,每条8列的话,就会报OutOfMemory的错误。Excel2003中每个电子表格的记录数必须在65536以下,否则就会发生异常。目前还没有好的解决方案,建议对于海量数据写入操作,尽量使用Excel2007

 

/**

 

 * 抽象Excel2003读取器,通过实现HSSFListener监听器,采用事件驱动模式解析excel2003

 

 * 中的内容,遇到特定事件才会触发,大大减少了内存的使用。

 

 *

 

 */

 

public  class Excel2003Reader implements HSSFListener{

 

        private int minColumns = -1;

 

        private POIFSFileSystem fs;

 

        private int lastRowNumber;

 

        private int lastColumnNumber;

 

 

 

        /** Should we output the formula, or the value it has? */

 

        private boolean outputFormulaValues = true;

 

 

 

        /** For parsing Formulas */

 

        private SheetRecordCollectingListener workbookBuildingListener;

 

        //excel2003工作薄

 

        private HSSFWorkbook stubWorkbook;

 

 

 

        // Records we pick up as we process

 

        private SSTRecord sstRecord;

 

        private FormatTrackingHSSFListener formatListener;

 

 

 

        //表索引

 

        private int sheetIndex = -1;

 

        private BoundSheetRecord[] orderedBSRs;

 

        @SuppressWarnings("unchecked")

 

        private ArrayList boundSheetRecords = new ArrayList();

 

 

 

        // For handling formulas with string results

 

        private int nextRow;

 

        private int nextColumn;

 

        private boolean outputNextStringRecord;

 

        //当前行

 

        private int curRow = 0;

 

        //存储行记录的容器

 

        private List<String> rowlist = new ArrayList<String>();;

 

        @SuppressWarnings( "unused")

 

        private String sheetName;

 

       

 

        private IRowReader rowReader;

 

 

 

       

 

        public void setRowReader(IRowReader rowReader){

 

               this.rowReader = rowReader;

 

        }

 

       

 

        /**

 

         * 遍历excel下所有的sheet

 

         * @throws IOException

 

         */

 

        public void process(String fileName) throws IOException {

 

               this.fs = new POIFSFileSystem(new FileInputStream(fileName));

 

               MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(

 

                               this);

 

               formatListener = new FormatTrackingHSSFListener(listener);

 

               HSSFEventFactory factory = new HSSFEventFactory();

 

               HSSFRequest request = new HSSFRequest();

 

               if (outputFormulaValues) {

 

                       request.addListenerForAllRecords(formatListener);

 

               } else {

 

                       workbookBuildingListener = new SheetRecordCollectingListener(

 

                                       formatListener);

 

                       request.addListenerForAllRecords(workbookBuildingListener);

 

               }

 

               factory.processWorkbookEvents(request, fs);

 

        }

 

       

 

        /**

 

         * HSSFListener 监听方法,处理 Record

 

         */

 

        @SuppressWarnings("unchecked")

 

        public void processRecord(Record record) {

 

               int thisRow = -1;

 

               int thisColumn = -1;

 

               String thisStr = null;

 

               String value = null;

 

               switch (record.getSid()) {

 

                       case BoundSheetRecord.sid:

 

                               boundSheetRecords.add(record);

 

                               break;

 

                       case BOFRecord.sid:

 

                               BOFRecord br = (BOFRecord) record;

 

                               if (br.getType() == BOFRecord.TYPE_WORKSHEET) {

 

                                      // 如果有需要,则建立子工作薄

 

                                      if (workbookBuildingListener != null && stubWorkbook == null) {

 

                                              stubWorkbook = workbookBuildingListener

 

                                                             .getStubHSSFWorkbook();

 

                                      }

 

                                     

 

                                      sheetIndex++;

 

                                      if (orderedBSRs == null) {

 

                                              orderedBSRs = BoundSheetRecord

 

                                                             .orderByBofPosition(boundSheetRecords);

 

                                      }

 

                                      sheetName = orderedBSRs[sheetIndex].getSheetname();

 

                               }

 

                               break;

 

       

 

                       case SSTRecord.sid:

 

                               sstRecord = (SSTRecord) record;

 

                               break;

 

       

 

                       case BlankRecord.sid:

 

                               BlankRecord brec = (BlankRecord) record;

 

                               thisRow = brec.getRow();

 

                               thisColumn = brec.getColumn();

 

                               thisStr = "";

 

                               rowlist.add(thisColumn, thisStr);

 

                               break;

 

                       case BoolErrRecord.sid: //单元格为布尔类型

 

                               BoolErrRecord berec = (BoolErrRecord) record;

 

                               thisRow = berec.getRow();

 

                               thisColumn = berec.getColumn();

 

                               thisStr = berec.getBooleanValue()+"";

 

                               rowlist.add(thisColumn, thisStr);

 

                               break;

 

       

 

                       case FormulaRecord.sid: //单元格为公式类型

 

                               FormulaRecord frec = (FormulaRecord) record;

 

                               thisRow = frec.getRow();

 

                               thisColumn = frec.getColumn();

 

                               if (outputFormulaValues) {

 

                                      if (Double.isNaN(frec.getValue())) {

 

                                              // Formula result is a string

 

                                              // This is stored in the next record

 

                                              outputNextStringRecord = true;

 

                                              nextRow = frec.getRow();

 

                                              nextColumn = frec.getColumn();

 

                                      } else {

 

                                              thisStr = formatListener.formatNumberDateCell(frec);

 

                                      }

 

                               } else {

 

                                      thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,

 

                                                     frec.getParsedExpression()) + '"';

 

                               }

 

                               rowlist.add(thisColumn,thisStr);

 

                               break;

 

                       case StringRecord.sid://单元格中公式的字符串

 

                               if (outputNextStringRecord) {

 

                                      // String for formula

 

                                      StringRecord srec = (StringRecord) record;

 

                                      thisStr = srec.getString();

 

                                      thisRow = nextRow;

 

                                      thisColumn = nextColumn;

 

                                      outputNextStringRecord = false;

 

                               }

 

                               break;

 

                       case LabelRecord.sid:

 

                               LabelRecord lrec = (LabelRecord) record;

 

                               curRow = thisRow = lrec.getRow();

 

                               thisColumn = lrec.getColumn();

 

                               value = lrec.getValue().trim();

 

                               value = value.equals("")?" ":value;

 

                               this.rowlist.add(thisColumn, value);

 

                               break;

 

                       case LabelSSTRecord.sid:  //单元格为字符串类型

 

                               LabelSSTRecord lsrec = (LabelSSTRecord) record;

 

                               curRow = thisRow = lsrec.getRow();

 

                               thisColumn = lsrec.getColumn();

 

                               if (sstRecord == null) {

 

                                      rowlist.add(thisColumn, " ");

 

                               } else {

 

                                      value =  sstRecord

 

                                      .getString(lsrec.getSSTIndex()).toString().trim();

 

                                      value = value.equals("")?" ":value;

 

                                      rowlist.add(thisColumn,value);

 

                               }

 

                               break;

 

                       case NumberRecord.sid:  //单元格为数字类型

 

                               NumberRecord numrec = (NumberRecord) record;

 

                               curRow = thisRow = numrec.getRow();

 

                               thisColumn = numrec.getColumn();

 

                               value = formatListener.formatNumberDateCell(numrec).trim();

 

                               value = value.equals("")?" ":value;

 

                               // 向容器加入列值

 

                               rowlist.add(thisColumn, value);

 

                               break;

 

                       default:

 

                               break;

 

               }

 

 

 

               // 遇到新行的操作

 

               if (thisRow != -1 && thisRow != lastRowNumber) {

 

                       lastColumnNumber = -1;

 

               }

 

 

 

               // 空值的操作

 

               if (record instanceof MissingCellDummyRecord) {

 

                       MissingCellDummyRecord mc = (MissingCellDummyRecord) record;

 

                       curRow = thisRow = mc.getRow();

 

                       thisColumn = mc.getColumn();

 

                       rowlist.add(thisColumn," ");

 

               }

 

 

 

               // 更新行和列的值

 

               if (thisRow > -1)

 

                       lastRowNumber = thisRow;

 

               if (thisColumn > -1)

 

                       lastColumnNumber = thisColumn;

 

 

 

               // 行结束时的操作

 

               if (record instanceof LastCellOfRowDummyRecord) {

 

                       if (minColumns > 0) {

 

                               // 列值重新置空

 

                               if (lastColumnNumber == -1) {

 

                                      lastColumnNumber = 0;

 

                               }

 

                       }

 

                       lastColumnNumber = -1;

 

                               // 每行结束时, 调用getRows() 方法

 

                       rowReader.getRows(sheetIndex,curRow, rowlist);

 

                      

 

                       // 清空容器

 

                       rowlist.clear();

 

               }

 

        }

 

       

 

}

 

/**

 

 * 抽象Excel2007读取器,excel2007的底层数据结构是xml文件,采用SAX的事件驱动的方法解析

 

 * xml,需要继承DefaultHandler,在遇到文件内容时,事件会触发,这种做法可以大大降低

 

 * 内存的耗费,特别使用于大数据量的文件。

 

 *

 

 */

 

public class Excel2007Reader extends DefaultHandler {

 

        //共享字符串表

 

        private SharedStringsTable sst;

 

        //上一次的内容

 

        private String lastContents;

 

        private boolean nextIsString;

 

 

 

        private int sheetIndex = -1;

 

        private List<String> rowlist = new ArrayList<String>();

 

        //当前行

 

        private int curRow = 0;

 

        //当前列

 

        private int curCol = 0;

 

        //日期标志

 

        private boolean dateFlag;

 

        //数字标志

 

        private boolean numberFlag;

 

       

 

        private boolean isTElement;

 

       

 

        private IRowReader rowReader;

 

       

 

        public void setRowReader(IRowReader rowReader){

 

               this.rowReader = rowReader;

 

        }

 

       

 

        /**只遍历一个电子表格,其中sheetId为要遍历的sheet索引,从1开始,1-3

 

         * @param filename

 

         * @param sheetId

 

         * @throws Exception

 

         */

 

        public void processOneSheet(String filename,int sheetId) throws Exception {

 

               OPCPackage pkg = OPCPackage.open(filename);

 

               XSSFReader r = new XSSFReader(pkg);

 

               SharedStringsTable sst = r.getSharedStringsTable();

 

               XMLReader parser = fetchSheetParser(sst);

 

              

 

               // 根据 rId# rSheet# 查找sheet

 

               InputStream sheet2 = r.getSheet("rId"+sheetId);

 

               sheetIndex++;

 

               InputSource sheetSource = new InputSource(sheet2);

 

               parser.parse(sheetSource);

 

               sheet2.close();

 

        }

 

 

 

        /**

 

         * 遍历工作簿中所有的电子表格

 

         * @param filename

 

         * @throws Exception

 

         */

 

        public void process(String filename) throws Exception {

 

               OPCPackage pkg = OPCPackage.open(filename);

 

               XSSFReader r = new XSSFReader(pkg);

 

               SharedStringsTable sst = r.getSharedStringsTable();

 

               XMLReader parser = fetchSheetParser(sst);

 

               Iterator<InputStream> sheets = r.getSheetsData();

 

               while (sheets.hasNext()) {

 

                       curRow = 0;

 

                       sheetIndex++;

 

                       InputStream sheet = sheets.next();

 

                       InputSource sheetSource = new InputSource(sheet);

 

                       parser.parse(sheetSource);

 

                       sheet.close();

 

               }

 

        }

 

 

 

        public XMLReader fetchSheetParser(SharedStringsTable sst)

 

                       throws SAXException {

 

               XMLReader parser = XMLReaderFactory

 

                               .createXMLReader("org.apache.xerces.parsers.SAXParser");

 

               this.sst = sst;

 

               parser.setContentHandler(this);

 

               return parser;

 

        }

 

 

 

        public void startElement(String uri, String localName, String name,

 

                       Attributes attributes) throws SAXException {

 

              

 

               // c => 单元格

 

               if ("c".equals(name)) {

 

                       // 如果下一个元素是 SST 的索引,则将nextIsString标记为true

 

                       String cellType = attributes.getValue("t");

 

                       if ("s".equals(cellType)) {

 

                               nextIsString = true;

 

                       } else {

 

                               nextIsString = false;

 

                       }

 

                       //日期格式

 

                       String cellDateType = attributes.getValue("s");

 

                       if ("1".equals(cellDateType)){

 

                               dateFlag = true;

 

                       } else {

 

                               dateFlag = false;

 

                       }

 

                       String cellNumberType = attributes.getValue("s");

 

                       if("2".equals(cellNumberType)){

 

                               numberFlag = true;

 

                       } else {

 

                               numberFlag = false;

 

                       }

 

                      

 

               }

 

               //当元素为t

 

               if("t".equals(name)){

 

                       isTElement = true;

 

               } else {

 

                       isTElement = false;

 

               }

 

              

 

               // 置空

 

               lastContents = "";

 

        }

 

 

 

        public void endElement(String uri, String localName, String name)

 

                       throws SAXException {

 

              

 

               // 根据SST的索引值的到单元格的真正要存储的字符串

 

               // 这时characters()方法可能会被调用多次

 

               if (nextIsString) {

 

                       try {

 

                               int idx = Integer.parseInt(lastContents);

 

                               lastContents = new XSSFRichTextString(sst.getEntryAt(idx))

 

                                              .toString();

 

                       } catch (Exception e) {

 

 

 

                       }

 

               }

 

               //t元素也包含字符串

 

               if(isTElement){

 

                       String value = lastContents.trim();

 

                       rowlist.add(curCol, value);

 

                       curCol++;

 

                       isTElement = false;

 

                       // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引

 

                       // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符

 

               } else if ("v".equals(name)) {

 

                       String value = lastContents.trim();

 

                       value = value.equals("")?" ":value;

 

                       //日期格式处理

 

                       if(dateFlag){

 

                                Date date = HSSFDateUtil.getJavaDate(Double.valueOf(value));

 

                                SimpleDateFormat dateFormat = new SimpleDateFormat(

 

                 "dd/MM/yyyy");

 

                                value = dateFormat.format(date);

 

                       }

 

                       //数字类型处理

 

                       if(numberFlag){

 

                               BigDecimal bd = new BigDecimal(value);

 

                               value = bd.setScale(3,BigDecimal.ROUND_UP).toString();

 

                       }

 

                       rowlist.add(curCol, value);

 

                       curCol++;

 

               }else {

 

                       //如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法

 

                       if (name.equals("row")) {

 

                               rowReader.getRows(sheetIndex,curRow,rowlist);

 

                               rowlist.clear();

 

                               curRow++;

 

                               curCol = 0;

 

                       }

 

               }

 

              

 

        }

 

 

 

        public void characters(char[] ch, int start, int length)

 

                       throws SAXException {

 

               //得到单元格内容的值

 

               lastContents += new String(ch, start, length);

 

        }

 

}

 




 

public class ExcelReaderUtil {

 

       

 

        //excel2003扩展名

 

        public static final String EXCEL03_EXTENSION = ".xls";

 

        //excel2007扩展名

 

        public static final String EXCEL07_EXTENSION = ".xlsx";

 

       

 

        /**

 

         * 读取Excel文件,可能是03也可能是07版本

 

         * @param excel03

 

         * @param excel07

 

         * @param fileName

 

         * @throws Exception

 

         */

 

        public static void readExcel(IRowReader reader,String fileName) throws Exception{

 

               // 处理excel2003文件

 

               if (fileName.endsWith(EXCEL03_EXTENSION)){

 

                       Excel2003Reader excel03 = new Excel2003Reader();

 

                       excel03.setRowReader(reader);

 

                       excel03.process(fileName);

 

               // 处理excel2007文件

 

               } else if (fileName.endsWith(EXCEL07_EXTENSION)){

 

                       Excel2007Reader excel07 = new Excel2007Reader();

 

                       excel07.setRowReader(reader);

 

                       excel07.process(fileName);

 

               } else {

 

                       throw new  Exception("文件格式错误,fileName的扩展名只能是xlsxlsx");

 

               }

 

        }

 

}

 




 

public interface IRowReader {

 

       

 

        /**业务逻辑实现方法

 

         * @param sheetIndex

 

         * @param curRow

 

         * @param rowlist

 

         */

 

        public  void getRows(int sheetIndex,int curRow, List<String> rowlist);

 

}

 




 

public class RowReader implements IRowReader{

 

 

 

 

 

        /* 业务逻辑实现方法

 

         * @see com.eprosun.util.excel.IRowReader#getRows(int, int, java.util.List)

 

         */

 

        public void getRows(int sheetIndex, int curRow, List<String> rowlist) {

 

               // TODO Auto-generated method stub

 

               System.out.print(curRow+" ");

 

               for (int i = 0; i < rowlist.size(); i++) {

 

                       System.out.print(rowlist.get(i) + " ");

 

               }

 

               System.out.println();

 

        }

 

 

 

}

 



 

public class Main {

 

       

 

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

 

               IRowReader reader = new RowReader();

 

               //ExcelReaderUtil.readExcel(reader, "F://te03.xls");

 

               ExcelReaderUtil.readExcel(reader, "F://test07.xlsx");

 

        }

 

}

 




 

public class Excel2003Writer {

 

 

 

        /**

 

         * @param args

 

         */

 

        public static void main(String[] args) {

 

               try{   

 

                       System.out.println("开始写入excel2003....");

 

                       writeExcel("tes2003.xls");

 

                       System.out.println("写完xcel2003");

 

               } catch (IOException e) {

 

              

 

               }

 

        }

 

       

 

       

 

        /**

 

         * 写入excel并填充内容,一个sheet只能写65536行以下,超出会报异常,写入时建议使用AbstractExcel2007Writer

 

         * @param fileName

 

         * @throws IOException

 

         */

 

        public static void writeExcel(String fileName) throws IOException{

 

                      

 

                       // 创建excel2003对象

 

                       Workbook wb = new HSSFWorkbook();

 

                      

 

                       // 设置文件放置路径和文件名

 

                   FileOutputStream fileOut = new FileOutputStream(fileName);

 

                   // 创建新的表单

 

                    Sheet sheet = wb.createSheet("newsheet");

 

                   // 创建新行

 

                   for(int i=0;i<20000;i++){

 

                           Row row = sheet.createRow(i);

 

                           // 创建单元格

 

                           Cell cell = row.createCell(0);

 

                           // 设置单元格值

 

                           cell.setCellValue(1);

 

                           row.createCell(1).setCellValue(1+i);

 

                           row.createCell(2).setCellValue(true);

 

                           row.createCell(3).setCellValue(0.43d);

 

                           row.createCell(4).setCellValue('d');

 

                           row.createCell(5).setCellValue("");

 

                           row.createCell(6).setCellValue("第七列"+i);

 

                           row.createCell(7).setCellValue("第八列"+i);

 

                   }

 

                   wb.write(fileOut);

 

                   fileOut.close();

 

        }

 

 

 

 

 

}

 

 

 

/**

 

 * 抽象excel2007读入器,先构建.xlsx一张模板,改写模板中的sheet.xml,使用这种方法

 

 * 写入.xlsx文件,不需要太大的内存

 

 *

 

 */

 

public abstract class AbstractExcel2007Writer {

 

       

 

        private SpreadsheetWriter sw;

 

 

 

        /**

 

         * 写入电子表格的主要流程

 

         * @param fileName

 

         * @throws Exception

 

         */

 

        public void process(String fileName) throws Exception{

 

               // 建立工作簿和电子表格对象

 

               XSSFWorkbook wb = new XSSFWorkbook();

 

               XSSFSheet sheet = wb.createSheet("sheet1");

 

               // 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml

 

               String sheetRef = sheet.getPackagePart().getPartName().getName();

 

 

 

               // 保存模板

 

               FileOutputStream os = new FileOutputStream("template.xlsx");

 

               wb.write(os);

 

               os.close();

 

              

 

               // 生成xml文件

 

               File tmp = File.createTempFile("sheet", ".xml");

 

               Writer fw = new FileWriter(tmp);

 

               sw = new SpreadsheetWriter(fw);

 

               generate();

 

               fw.close();

 

              

 

               // 使用产生的数据替换模板

 

               File templateFile = new File("template.xlsx");

 

               FileOutputStream out = new FileOutputStream(fileName);

 

               substitute(templateFile, tmp, sheetRef.substring(1), out);

 

               out.close();

 

               //删除文件之前调用一下垃圾回收器,否则无法删除模板文件

 

               System.gc();

 

               // 删除临时模板文件

 

               if (templateFile.isFile()&&templateFile.exists()){

 

                       templateFile.delete();

 

               }

 

        }

 

 

 

        /**

 

         * 类使用者应该使用此方法进行写操作

 

         * @throws Exception

 

         */

 

        public abstract void generate() throws Exception;

 

 

 

        public void beginSheet() throws IOException {

 

               sw.beginSheet();

 

        }

 

 

 

        public void insertRow(int rowNum) throws IOException {

 

               sw.insertRow(rowNum);

 

        }

 

 

 

        public void createCell(int columnIndex, String value) throws IOException {

 

               sw.createCell(columnIndex, value, -1);

 

        }

 

 

 

        public void createCell(int columnIndex, double value) throws IOException {

 

               sw.createCell(columnIndex, value, -1);

 

        }

 

 

 

        public void endRow() throws IOException {

 

               sw.endRow();

 

        }

 

 

 

        public void endSheet() throws IOException {

 

               sw.endSheet();

 

        }

 

 

 

        /**

 

         *

 

         * @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

 

         */

 

        private 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);

 

               }

 

        }

 

 

 

        /**

 

         * 在写入器中写入电子表格

 

         *

 

         */

 

        public static class SpreadsheetWriter {

 

               private final Writer _out;

 

               private int _rownum;

 

               private static String LINE_SEPARATOR = System.getProperty("line.separator");

 

 

 

               public SpreadsheetWriter(Writer out) {

 

                       _out = out;

 

               }

 

 

 

               public void beginSheet() throws IOException {

 

                       _out.write("<?xml version=\"1.0\" encoding=\"GB2312\"?>"

 

                                                     + "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");

 

                       _out.write("<sheetData>"+LINE_SEPARATOR);

 

               }

 

 

 

               public void endSheet() throws IOException {

 

                       _out.write("</sheetData>");

 

                       _out.write("</worksheet>");

 

               }

 

 

 

               /**

 

                * 插入新行

 

                *

 

                * @param rownum 0开始

 

                */

 

               public void insertRow(int rownum) throws IOException {

 

                       _out.write("<row r=\"" + (rownum + 1) + "\">"+LINE_SEPARATOR);

 

                       this._rownum = rownum;

 

               }

 

 

 

               /**

 

                * 插入行结束标志

 

                */

 

               public void endRow() throws IOException {

 

                       _out.write("</row>"+LINE_SEPARATOR);

 

               }

 

 

 

               /**

 

                * 插入新列

 

                * @param columnIndex

 

                * @param value

 

                * @param styleIndex

 

                * @throws IOException

 

                */

 

               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>"+XMLEncoder.encode(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 class Excel2007WriterImpl extends AbstractExcel2007Writer{

 

 

 

       

 

        /**

 

         * @param args

 

         * @throws Exception

 

         */

 

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

 

               // TODO Auto-generated method stub

 

               System.out.println("............................");

 

               long start = System.currentTimeMillis();

 

               //构建excel2007写入器

 

               AbstractExcel2007Writer excel07Writer = new Excel2007WriterImpl();

 

               //调用处理方法

 

               excel07Writer.process("F://test07.xlsx");

 

               long end = System.currentTimeMillis();

 

               System.out.println("....................."+(end-start)/1000);

 

        }

 

 

 

       

 

        /*

 

         * 可根据需求重写此方法,对于单元格的小数或者日期格式,会出现精度问题或者日期格式转化问题,建议使用字符串插入方法

 

         * @see com.excel.ver2.AbstractExcel2007Writer#generate()

 

         */

 

        @Override

 

        public void generate()throws Exception {

 

        //电子表格开始

 

        beginSheet();

 

        for (int rownum = 0; rownum < 100; rownum++) {

 

               //插入新行

 

            insertRow(rownum);

 

            //建立新单元格,索引值从0开始,表示第一列

 

            createCell(0, "中国<" + rownum + "!");

 

            createCell(1, 34343.123456789);

 

            createCell(2, "23.67%");

 

            createCell(3, "12:12:23");

 

            createCell(4, "2010-10-11 12:12:23");

 

            createCell(5, "true");

 

            createCell(6, "false");

 

         

 

            //结束行

 

            endRow();

 

        }

 

        //电子表格结束

 

        endSheet();

 

        }

 

 

 

}

 




 

public class XMLEncoder {

 

 

 

    private static final String[] xmlCode = new String[256];

 

 

 

    static {

 

        // Special characters

 

        xmlCode['\''] = "'";

 

        xmlCode['\"'] = """; // double quote

 

        xmlCode['&'] = "&"; // ampersand

 

        xmlCode['<'] = "<"; // lower than

 

        xmlCode['>'] = ">"; // greater than

 

    }

 

 

 

    /**

 

     * <p>

 

     * Encode the given text into xml.

 

     * </p>

 

     *

 

     * @param string the text to encode

 

     * @return the encoded string

 

     */

 

    public static String encode(String string) {

 

        if (string == null) return "";

 

        int n = string.length();

 

        char character;

 

        String xmlchar;

 

        StringBuffer buffer = new StringBuffer();

 

        // loop over all the characters of the String.

 

        for (int i = 0; i < n; i++) {

 

            character = string.charAt(i);

 

            // the xmlcode of these characters are added to a StringBuffer one by one

 

            try {

 

                xmlchar = xmlCode[character];

 

                if (xmlchar == null) {

 

                    buffer.append(character);

 

                } else {

 

                    buffer.append(xmlCode[character]);

 

                }

 

            } catch (ArrayIndexOutOfBoundsException aioobe) {

 

                buffer.append(character);

 

            }

 

        }

 

        return buffer.toString();

 

    }

 

 

 

}

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics