java对execl的操作

HSSFWorkbook 对应操作的execl的文件格式为xls,需要注

意的是在导出execl文件时，一个sheet最多导出65535 ,并且会消耗内存。

XSSFWorkbook 对应操作的execl的文件格式为xlsx，最多可以 导出 104 万行，不过当导出大量数据时，会消耗大量内存，并占用大量cpu。

SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

至于多少行保存在内存里，其他写入硬盘，是由DEFAULT_WINDOW_SIZE决定的。代码也可以在创建SXSSFWorkbook实例时，传入一个int参数，来设定。需注意的是，int rowAccessWindowSize如果传入100，并不是指100行保存在内存里，而是说100的屏幕尺寸下可见的行数。所以现在直接使用SXSSFWorkbook 即可。

SXSSFWorkbook 导入列子：

@RequestMapping("/exportExecl")
	@ResponseBody
	public void exportExecl(@RequestParam Integer deviceId, String beginTime, String endTime,
			@RequestParam(defaultValue = "0") Integer pageNum) {
		ServletOutputStream outputStream = null;
		List<DeviceMonitorDatas> list = null;
		Row row = null;
		SXSSFWorkbook workbook = null;
		Sheet sheet = null;

		try {
			if (!semaphoreExecl.tryAcquire()) {
				response.setCharacterEncoding("utf-8");
				PrintWriter writer = response.getWriter();
				writer.print("当前操作人数太多，请稍后再试！");
				writer.flush();
				writer.close();
				
			} else {
				String times = Dateutil.getStrAllTime(new Date());
				long count = iDeviceDataService.deviceListDataCount(deviceId, Dateutil.getDayMin(beginTime),
						Dateutil.getDayMax(endTime));
				response.setContentType("application/vnd.ms-excel");
				response.setHeader("Content-disposition", "attachment;filename=" + times + "_数据.xlsx");
				workbook = new SXSSFWorkbook(10000);
				sheet = workbook.createSheet();

				DeviceMonitorDatas bean = null;
				setExeclTitle(sheet);

				for (int i = 0; i < count; i = i + 10000) {
					list = iDeviceDataService.exportDeviceListData(i, 100000, deviceId, Dateutil.getDayMin(beginTime),
							Dateutil.getDayMax(endTime));
					if (list != null && !list.isEmpty()) {
						for (int j = 0; j < list.size(); j++) {
							bean = list.get(j);
							row = sheet.createRow(i * 10000 + j + 1);
							setExeclTitle(row, bean);
						}
					}

				}
				outputStream = response.getOutputStream();
				workbook.write(outputStream);
				outputStream.flush();
				outputStream.close();

			}
		} catch (Exception e) {
             e.printStackTrace();
		} finally {
			list = null;
			row = null;
			sheet = null;
			workbook = null;
			semaphoreExecl.release();

		}

	}

其他的一些常用方法：

HSSFCellStyle  titleStyle = sheet.getWorkbook().createCellStyle(); //创建一个单元格样式
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直    
titleStyle.setAlignment(HorizontalAlignment.CENTER);// 水平    
HSSFFont titleFont = sheet.getWorkbook().createFont();
titleFont.setFontName("楷体");//字体名称
titleFont.setFontHeightInPoints((short)18); //字体大小
titleFont.setBold(true);//粗体
titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//设置字体颜色
titleStyle.setFont(titleFont);//设置字体样式

HSSFCell cell = row.createCell(0);//创建单元格
cell.setCellValue("辩题"); 
cell.setCellStyle(titleStyle);//设置单元格的样式

 

 CellRangeAddress cellRangeAddress = new CellRangeAddress(0,3 , 0, 2); //合并单元格  参数意义 第一行 ，最后一行，第一列，最后一列
  sheet.addMergedRegion(cellRangeAddress); //添加到sheet里。

 

对于读取execl ，如果文件太大，用官方推荐的把xlsx转为xsv代码：

package com.glodio.util;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
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.extractor.XSSFEventBasedExcelExtractor;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

/**
 * A rudimentary XLSX -> CSV processor modeled on the
 * POI sample program XLS2CSVmra from the package
 * org.apache.poi.hssf.eventusermodel.examples.
 * As with the HSSF version, this tries to spot missing
 *  rows and cells, and output empty entries for them.
 * <p>
 * Data sheets are read using a SAX parser to keep the
 * memory footprint relatively small, so this should be
 * able to read enormous workbooks.  The styles table and
 * the shared-string table must be kept in memory.  The
 * standard POI styles table class is used, but a custom
 * (read-only) class is used for the shared string table
 * because the standard POI SharedStringsTable grows very
 * quickly with the number of unique strings.
 * <p>
 * For a more advanced implementation of SAX event parsing
 * of XLSX files, see {@link XSSFEventBasedExcelExtractor}
 * and {@link XSSFSheetXMLHandler}. Note that for many cases,
 * it may be possible to simply use those with a custom 
 * {@link SheetContentsHandler} and no SAX code needed of
 * your own!
 */
public class XLSX2CSV {
    /**
     * Uses the XSSF Event SAX helpers to do most of the work
     *  of parsing the Sheet XML, and outputs the contents
     *  as a (basic) CSV.
     */
    private class SheetToCSV implements SheetContentsHandler {
        private boolean firstCellOfRow;
        private int currentRow = -1;
        private int currentCol = -1;
        //这个方法 我们是否要过滤掉前几列数据 ，如果minColumns 为2 那么0,1,2的数据不会读取
        private void outputMissingRows(int number) {
            for (int i=0; i<number; i++) {
                for (int j=0; j<minColumns; j++) {
                    output.append(',');
                }
                output.append('\n');
            }
        }

        @Override
        public void startRow(int rowNum) {
            // If there were gaps, output the missing rows
            //outputMissingRows(rowNum-currentRow-1);
            // Prepare for this row
            firstCellOfRow = true;
            currentRow = rowNum;
            currentCol = -1;
        }

        @Override
        public void endRow(int rowNum) {
            // Ensure the minimum number of columns  每一行执行完后 执行该方法 我们可以做数验证 把不符合数据的进行抛弃掉

            for (int i=currentCol; i<minColumns; i++) {
                output.append(',');
            }
            output.append('\n');
        }

        @Override
        public void cell(String cellReference, String formattedValue,
                XSSFComment comment) {
           //处理每个单元格的数据，如果单元格的数据为空 那么这个方法不会执行
            if (firstCellOfRow) {
                firstCellOfRow = false;
            } else {
                output.append(',');
            }

            // gracefully handle missing CellRef here in a similar way as XSSFCell does
            if(cellReference == null) {
                cellReference = new CellAddress(currentRow, currentCol).formatAsString();
            }

            // Did we miss any cells? //如果前面的单元格数据有为空的，那么这个会执行
            int thisCol = (new CellReference(cellReference)).getCol();
            int missedCols = thisCol - currentCol - 1;
            for (int i=0; i<missedCols; i++) {
                output.append(',');
            }
            currentCol = thisCol;
            
            // Number or string?
            try {
                //noinspection ResultOfMethodCallIgnored
                Double.parseDouble(formattedValue);
                output.append(formattedValue);
            } catch (NumberFormatException e) {
                output.append('"');
                output.append(formattedValue);
                output.append('"');
            }
        }

		@Override
		public void headerFooter(String arg0, boolean arg1, String arg2) {
			// TODO Auto-generated method stub
			
		}
    }


    ///

    private  OPCPackage xlsxPackage;

    /**
     * Number of columns to read starting with leftmost
     */
    private final int minColumns;

    /**
     * Destination for data
     */
    private final PrintStream output;

    /**
     * Creates a new XLSX -> CSV examples
     *
     * @param pkg        The XLSX package to process
     * @param output     The PrintStream to output the CSV to
     * @param minColumns The minimum number of columns to output, or -1 for no minimum
     */
    public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns) {
        this.xlsxPackage = pkg;
        this.output = output;
        this.minColumns = minColumns;
    }

    /**
     * Parses and shows the content of one sheet
     * using the specified styles and shared-strings tables.
     *
     * @param styles The table of styles that may be referenced by cells in the sheet
     * @param strings The table of strings that may be referenced by cells in the sheet
     * @param sheetInputStream The stream to read the sheet-data from.

     * @exception java.io.IOException An IO exception from the parser,
     *            possibly from a byte stream or character stream
     *            supplied by the application.
     * @throws SAXException if parsing the XML data fails.
     */
    public void processSheet(
            StylesTable styles,
            ReadOnlySharedStringsTable strings,
            SheetContentsHandler sheetHandler, 
            InputStream sheetInputStream) throws IOException, SAXException {
        DataFormatter formatter = new DataFormatter();
        InputSource sheetSource = new InputSource(sheetInputStream);
        try {
            XMLReader sheetParser = SAXHelper.newXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(
                  styles, null, strings, sheetHandler, formatter, false);
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
         } catch(ParserConfigurationException e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
         }
    }

    /**
     * Initiates the processing of the XLS workbook file to CSV.
     *
     * @throws IOException If reading the data from the package fails.
     * @throws SAXException if parsing the XML data fails.
     */
    public void process() throws IOException, OpenXML4JException, SAXException {
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int index = 0;
        while (iter.hasNext()) {
            try (InputStream stream = iter.next()) {
                String sheetName = iter.getSheetName();
                this.output.println();
                this.output.println(sheetName + " [index=" + index + "]:");
                processSheet(styles, strings, new SheetToCSV(), stream);
            }
            ++index;
        }
        iter = null;
        styles = null;
        xssfReader = null;
        strings = null;
       
    }

    
    
    public static void readExecl(InputStream inputStream ) throws Exception {
    
        int minColumns = -1;
        

        // The package open is instantaneous, as it should be.
        try (OPCPackage p = OPCPackage.open(inputStream)) {
            XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns);
            xlsx2csv.process();
        }
        
    }
    
    public static void readExecl(String fileName) throws Exception {
        
    	 File xlsxFile = new File(fileName);
         if (!xlsxFile.exists()) {
             System.err.println("Not found or not a file: " + xlsxFile.getPath());
             return;
         }

         int minColumns = -1;
      
         XLSX2CSV xlsx2csv = null;
         // The package open is instantaneous, as it should be.
         try (OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ)) {
              xlsx2csv = new XLSX2CSV(p, System.out, minColumns);
             xlsx2csv.process();
           
         }
         xlsx2csv.xlsxPackage = null;
         xlsx2csv = null;
    }
    
    public static void main(String[] args) throws Exception {
    
        File xlsxFile = new File("E://2.xlsx");
        if (!xlsxFile.exists()) {
            System.err.println("Not found or not a file: " + xlsxFile.getPath());
            return;
        }

        int minColumns = -1;
        if (args.length >= 2)
            minColumns = Integer.parseInt(args[1]);

        // The package open is instantaneous, as it should be.
        //try()这样会自动调用对象的close的方法
        try (OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ)) {
            XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns);
            xlsx2csv.process();
            
        }
    }
}

 我们只需要 重新写 SheetToCSV implements SheetContentsHandler 就可以使用了，OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ) 最好读取文件 ，如果改成流执行的时候 ，刚开始是会消耗大量内存，后面就正常了。