• java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁...

    java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁的的工具类需要2个参数改成只需要一个参数就可以完成调用,当然你可以根据你自己的情况使用。
    下面是一个工具类,复制到自己的项目中直接调用即可:

    1、工具类

    package com.xxx.xxx.xxx;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    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.poi.xssf.usermodel.XSSFComment;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.stereotype.Component;
    import org.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    import org.xml.sax.helpers.XMLReaderFactory;
    /**
     * 解析大数据量Excel工具类
     * @author RobinTime
     *
     */
    @Component
    public class ExcelParser {
        private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);
        /**
         * 表格默认处理器
         */
        private ISheetContentHandler contentHandler = new DefaultSheetHandler();
        /**
         * 读取数据
         */
        private List<String[]> datas = new ArrayList<String[]>();
    
        /**
         * 转换表格,默认为转换第一个表格
         * @param stream
         * @return
         * @throws InvalidFormatException
         * @throws IOException
         * @throws ParseException
         */
        public ExcelParser parse(InputStream stream)
                throws InvalidFormatException, IOException, ParseException {
            return parse(stream, 1);
        }
    
    
        /**
         * 
         * @param stream
         * @param sheetId:为要遍历的sheet索引,从1开始
         * @return
         * @throws InvalidFormatException
         * @throws IOException
         * @throws ParseException
         */
        public synchronized ExcelParser parse(InputStream stream, int sheetId)
                throws InvalidFormatException, IOException, ParseException {
            // 每次转换前都清空数据
            datas.clear();
            // 打开表格文件输入流
            OPCPackage pkg = OPCPackage.open(stream);
            try {
                // 创建表阅读器
                XSSFReader reader;
                try {
                    reader = new XSSFReader(pkg);
                } catch (OpenXML4JException e) {
                    logger.error("读取表格出错");
                    throw new ParseException(e.fillInStackTrace());
                }
    
                // 转换指定单元表
                InputStream shellStream = reader.getSheet("rId" + sheetId);
                try {
                    InputSource sheetSource = new InputSource(shellStream);
                    StylesTable styles = reader.getStylesTable();
                    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
                    getContentHandler().init(datas);// 设置读取出的数据
                    // 获取转换器
                    XMLReader parser = getSheetParser(styles, strings);
                    parser.parse(sheetSource);
                } catch (SAXException e) {
                    logger.error("读取表格出错");
                    throw new ParseException(e.fillInStackTrace());
                } finally {
                    shellStream.close();
                }
            } finally {
                pkg.close();
    
            }
            return this;
    
        }
    
        /**
         * 获取表格读取数据,获取数据前,需要先转换数据<br>
         * 此方法不会获取第一行数据
         * 
         * @return 表格读取数据
         */
        public List<String[]> getDatas() {
            return getDatas(true);
    
        }
    
        /**
         * 获取表格读取数据,获取数据前,需要先转换数据
         * 
         * @param dropFirstRow
         *            删除第一行表头记录
         * @return 表格读取数据
         */
        public List<String[]> getDatas(boolean dropFirstRow) {
            if (dropFirstRow && datas.size() > 0) {
                datas.remove(0);// 删除表头
            }
            return datas;
    
        }
    
        /**
         * 获取读取表格的转换器
         * 
         * @return 读取表格的转换器
         * @throws SAXException
         *             SAX错误
         */
        protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {
            XMLReader parser = XMLReaderFactory.createXMLReader();
            parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));
            return parser;
        }
    
        public ISheetContentHandler getContentHandler() {
            return contentHandler;
        }
    
        public void setContentHandler(ISheetContentHandler contentHandler) {
            this.contentHandler = contentHandler;
        }
    
        /**
         * 表格转换错误
         */
        public class ParseException extends Exception {
            private static final long serialVersionUID = -2451526411018517607L;
    
            public ParseException(Throwable t) {
                super("表格转换错误", t);
            }
    
        }
    
        public interface ISheetContentHandler extends SheetContentsHandler {
    
            /**
             * 设置转换后的数据集,用于存放转换结果
             * 
             * @param datas
             *            转换结果
             */
            void init(List<String[]> datas);
        }
    
        /**
         * 默认表格解析handder
         */
        class DefaultSheetHandler implements ISheetContentHandler {
            /**
             * 读取数据
             */
            private List<String[]> datas;
            private int columsLength;
            // 读取行信息
            private String[] readRow;
            private ArrayList<String> fristRow = new ArrayList<String>();
    
            @Override
            public void init(List<String[]> datas) {
                this.datas = datas;
    //          this.columsLength = columsLength;
            }
    
            @Override
            public void startRow(int rowNum) {
                if (rowNum != 0) {
                    readRow = new String[columsLength];
                }
            }
    
            @Override
            public void endRow(int rowNum) {
            //将Excel第一行表头的列数当做数组的长度,要保证后续的行的列数不能超过这个长度,这是个约定。
                if (rowNum == 0) {
                    columsLength = fristRow.size();
                    readRow = fristRow.toArray(new String[fristRow.size()]);
                }else {
                    readRow = fristRow.toArray(new String[columsLength]);
                }
                datas.add(readRow.clone());
                readRow = null;
                fristRow.clear();
            }
    
            @Override
            public void cell(String cellReference, String formattedValue, XSSFComment comment) {
                int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置
                try {
                    fristRow.set(index, formattedValue);
                } catch (IndexOutOfBoundsException e) {
                    int size = fristRow.size();
                    for (int i = index - size+1;i>0;i--){
                        fristRow.add(null);
                    }
                    fristRow.set(index,formattedValue);
                }
            }
    
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {
            }
    
            /**
             * 转换表格引用为列编号
             * 
             * @param cellReference
             *            列引用
             * @return 表格列位置,从0开始算
             */
            public int getCellIndex(String cellReference) {
                String ref = cellReference.replaceAll("\\d+", "");
                int num = 0;
                int result = 0;
                for (int i = 0; i < ref.length(); i++) {
                    char ch = cellReference.charAt(ref.length() - i - 1);
                    num = (int) (ch - 'A' + 1);
                    num *= Math.pow(26, i);
                    result += num;
                }
                return result - 1;
            }
        }
    }

    2、调用

    File tempFile = new File(this.getClass().getClassLoader().getResource("").getPath() + "tempFile\\" + (new Date()).getTime() + ".xlsx");
    //传入一个路径产生流再将流传入工具类,返回解析对象,Excel的所有数据就被解析到List<String[]> 里面,遍历list任由你处置。
    FileInputStream inputStream = new FileInputStream(tempFile);
    ExcelParser parse = excelParser.parse(inputStream);
    List<String[]> datas = parse.getDatas();
    展开全文
  • POI 导入导出功能,引用jar包是关键,maven依赖支持1.37版. 介绍: 首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(页/表)...

    POI 导入导出功能,引用jar包是关键,maven依赖支持3.17版.

    介绍:

    首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(页/表)(HSSFSheet)组成,一个sheet是由多个row(行)(HSSFRow)组成,一个row是由多个cell(单元格)(HSSFCell)组成。
    1、用HSSFWorkbook打开或者创建“Excel文件对象
    2、用HSSFWorkbook对象返回或者创建Sheet对象
    3、用Sheet对象返回行对象,用行对象得到Cell对象
    4、对Cell对象读写

    maven 依赖:

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
    

    mvc 依赖:

    poi-3.7-20101029.jar
    poi-3.9.jar
    poi-ooxml-3.9.jar
    poi-ooxml-schemas-3.9.jar
    

    ExcelUtil.java (导入导出工能封装)

    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    import org.apache.poi.common.usermodel.HyperlinkType;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFHyperlink;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.math.BigDecimal;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * Created by cdw on 2018/04/19.
     *
     * Apache POI操作Excel对象 HSSF:操作Excel 2007之前版本(.xls)格式,生成的EXCEL不经过压缩直接导出
     * XSSF:操作Excel 2007及之后版本(.xlsx)格式,内存占用高于HSSF SXSSF:从POI3.8
     * beta3开始支持,基于XSSF,低内存占用,专门处理大数据量(建议)。
     *
     * 注意: 值得注意的是SXSSFWorkbook只能写(导出)不能读(导入)
     *
     * 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
     * 这里引用的是阿里的json包,也可以自行转换成net.sf.json.JSONArray net.sf.json.JSONObject
     */
    public class ExcelUtil {
    
    	private final static String Excel_2003 = ".xls"; // 2003 版本的excel
    	private final static String Excel_2007 = ".xlsx"; // 2007 版本的excel
    
    	public static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss"; // 默认日期格式(类型为Date即可转换)
    	public static final int DEFAULT_COLUMN_WIDTH = 17; // 默认列宽
    
    	/**
    	 * 导入Excel
    	 *
    	 * @param file
    	 *            输入文件流
    	 */
    	public static List<List<Object>> importExcel(@RequestParam(value = "file", required = false) MultipartFile file)
                throws Exception {
    		String fileName = file.getOriginalFilename();
    		String xls = fileName.substring(fileName.lastIndexOf('.'));
    		if (Excel_2003.equals(xls) || Excel_2007.equals(xls)) {
    			return ExcelUtil.getImportExcel(file);
    		} else {
    			// 导入格式不正确
    			System.out.println("导入格式不正确:导入失败!");
    		}
    		return null;
    	}
    
    	/**
    	 * 导出Excel
    	 *
    	 * @param titleList
    	 *            表格头信息集合
    	 * @param dataArray
    	 *            数据数组
    	 * @param os
    	 *            文件输出流
    	 */
    	public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os)
    			throws Exception {
    		ExcelUtil.getExportExcel(titleList, dataArray, os);
    	}
    
    	/**
    	 * 导入Excel
    	 *
    	 * @param file
    	 *            导入文件流对象
    	 */
    	private static List<List<Object>> getImportExcel(MultipartFile file) throws Exception {
    		ImportExcelUtil util = new ImportExcelUtil();
    		String fileName = file.getOriginalFilename();
    		InputStream inputStream = file.getInputStream();
    		// 将导入的Excel数据转换成list集合
    		List<List<Object>> excelLists = util.getBankListByExcel(inputStream, fileName);
    		// 获取工作模板行数据对象
    //        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));
            // 或
    //        Workbook workbook = util.getWorkbook(inputStream, fileName);
    //
    //		for (int i = 0; i < excelLists.size(); i++) { // 循环行
    //			List<Object> list = excelLists.get(i); // 获取行级列集合
    //			for (int j = 0; j < list.size(); j++) {
    //				System.out.println("获取" + i + "行级" + j + "列的值:" + list.get(j));
    //			}
    //		}
    //
    //		for (List<Object> excelList : excelLists) { // 循环行
    //			for (Object obj : excelList) { // 循环列
    //				// 获取行级列的值
    //			}
    //		}
    //
    //        for (Sheet rows : workbook) { // 循环行
    //            for (Row row : rows) { // 循环列
    //                // 获取行级列的值
    //            }
    //        }
    		return excelLists;
    	}
    
    	/**
    	 * 导出Excel
    	 *
    	 * @param titleList
    	 *            表格头信息集合
    	 * @param dataArray
    	 *            数据数组
    	 * @param os
    	 *            文件输出流
    	 */
    	private static void getExportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os)
    			throws Exception {
    		String datePattern = DEFAULT_DATE_PATTERN;
    		int minBytes = DEFAULT_COLUMN_WIDTH;
    
    		/**
    		 * 声明一个工作薄
    		 */
    		SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
    		workbook.setCompressTempFiles(true);
    
    		// 表头1样式
    		CellStyle title1Style = workbook.createCellStyle();
    		title1Style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
    		title1Style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
    		Font titleFont = workbook.createFont();// 字体
    		titleFont.setFontHeightInPoints((short) 20);
    		titleFont.setBold(true);
    		titleFont.setFontHeight((short) 700);
    		title1Style.setFont(titleFont);
    
    		// 表头2样式
    		CellStyle title2Style = workbook.createCellStyle();
    		title2Style.setAlignment(HorizontalAlignment.CENTER);
    		title2Style.setVerticalAlignment(VerticalAlignment.CENTER);
    		title2Style.setBorderTop(BorderStyle.THIN);// 上边框
    		title2Style.setBorderRight(BorderStyle.THIN);// 右
    		title2Style.setBorderBottom(BorderStyle.THIN);// 下
    		title2Style.setBorderLeft(BorderStyle.THIN);// 左
    		Font title2Font = workbook.createFont();
    		title2Font.setUnderline((byte) 1);
    		title2Font.setColor(HSSFColor.BLUE.index);
    		title2Style.setFont(title2Font);
    
    		// head样式
    		CellStyle headerStyle = workbook.createCellStyle();
    		headerStyle.setAlignment(HorizontalAlignment.CENTER);
    		headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    		headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置颜色
    		headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 前景色纯色填充
    		headerStyle.setBorderTop(BorderStyle.THIN);
    		headerStyle.setBorderRight(BorderStyle.THIN);
    		headerStyle.setBorderBottom(BorderStyle.THIN);
    		headerStyle.setBorderLeft(BorderStyle.THIN);
    		Font headerFont = workbook.createFont();
    		headerFont.setFontHeightInPoints((short) 12);
    		headerFont.setBold(true); // 是否加粗
    		headerFont.setFontHeight((short) 500); // 字体大小
    		headerStyle.setFont(headerFont);
    
    		// 单元格样式
    		CellStyle cellStyle = workbook.createCellStyle();
    		cellStyle.setAlignment(HorizontalAlignment.CENTER);
    		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    		cellStyle.setBorderTop(BorderStyle.THIN);
    		cellStyle.setBorderRight(BorderStyle.THIN);
    		cellStyle.setBorderBottom(BorderStyle.THIN);
    		cellStyle.setBorderLeft(BorderStyle.THIN);
    		Font cellFont = workbook.createFont();
    		cellFont.setBold(false); // 是否加粗
    		cellFont.setFontHeight((short) 300); // 字体大小
    		cellStyle.setFont(cellFont);
    
    		String title1 = (String) titleList.get(0).get("title1");
    		String title2 = (String) titleList.get(0).get("title2");
    		LinkedHashMap<String, String> headMap = titleList.get(1);
    
    		/**
    		 * 生成一个(带名称)表格
    		 */
    		SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
    		sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行
    
    		/**
    		 * 生成head相关信息+设置每列宽度
    		 */
    		int[] colWidthArr = new int[headMap.size()];// 列宽数组
    		String[] headKeyArr = new String[headMap.size()];// headKey数组
    		String[] headValArr = new String[headMap.size()];// headVal数组
    		int i = 0;
    		for (Map.Entry<String, String> entry : headMap.entrySet()) {
    			headKeyArr[i] = entry.getKey();
    			headValArr[i] = entry.getValue();
    
    			int bytes = headKeyArr[i].getBytes().length;
    			colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
    			sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
    			i++;
    		}
    
    		/**
    		 * 遍历数据集合,产生Excel行数据,除去 title + head 数据起始行为0,赋值为3(即第四行起)
    		 */
    		int rowIndex = 0;
    		for (Object obj : dataArray) {
    			// 生成title+head信息
    			if (rowIndex == 0) {
    				SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
    				title1Row.createCell(0).setCellValue(title1);
    				title1Row.getCell(0).setCellStyle(title1Style);
    				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));// 合并单元格
    
    				SXSSFRow title2Row = (SXSSFRow) sheet.createRow(1);// title2行
    				title2Row.createCell(0).setCellValue(title2);
    
    				CreationHelper createHelper = workbook.getCreationHelper();
    				XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
    				hyperLink.setAddress(title2);
    				title2Row.getCell(0).setHyperlink(hyperLink);// 添加超链接
    
    				title2Row.getCell(0).setCellStyle(title2Style);
    				sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headMap.size() - 1));// 合并单元格
    
    				SXSSFRow headerRow = (SXSSFRow) sheet.createRow(2);// head行
    				for (int j = 0; j < headValArr.length; j++) {
    					headerRow.createCell(j).setCellValue(headValArr[j]);
    					headerRow.getCell(j).setCellStyle(headerStyle);
    				}
    				rowIndex = 3;
    			}
    
    			JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
    			// 生成数据
    			SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
    			for (int k = 0; k < headKeyArr.length; k++) {
    				SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
    				Object o = jo.get(headKeyArr[k]);
    				String cellValue = "";
    
    				if (o == null) {
    					cellValue = "";
    				} else if (o instanceof Date) {
    					cellValue = new SimpleDateFormat(datePattern).format(o);
    				} else if (o instanceof Float || o instanceof Double) {
    					cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
    				} else {
    					cellValue = o.toString();
    				}
    
    				cell.setCellValue(cellValue);
    				cell.setCellStyle(cellStyle);
    			}
    			rowIndex++;
    		}
    
    //      // 另外一种导出方式
    //		HSSFWorkbook workbook = new HSSFWorkbook();
    //		HSSFSheet sheet = workbook.createSheet("测试表");
    //		HSSFRow titleRow = sheet.createRow(0);
    //		sheet.setColumnWidth(titleRow.createCell(0).getColumnIndex(), 256 * 20);
    //		titleRow.createCell(0).setCellValue("名称");
    //		sheet.setColumnWidth(titleRow.createCell(1).getColumnIndex(), 256 * 20);
    //		titleRow.createCell(1).setCellValue("状态(0-已生成,1-待生成)");
    //		// 设置应用类型,以及编码
    //		response.setContentType("application/msexcel;charset=utf-8");
    //		response.setHeader("Content-Disposition", "filename=" + new String("测试表.xls".getBytes("gb2312"), "iso8859-1"));
    //		workbook.write(output);
    //		output.flush();
    //		output.close();
    
    		try {
    			workbook.write(os);
    			os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
    			os.close();// 关闭流
    			workbook.dispose();// 释放workbook所占用的所有windows资源
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    
    }
    

    ImportExcelUtil.java (Excel数据转换类)

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    /**
     * Created by cdw on 2018/04/19.
     *
     * 转换类
     *
     * 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
     */
    public class ImportExcelUtil {
    	
    	private final static String Excel_2003 = ".xls"; //2003 版本的excel
    	private final static String Excel_2007 = ".xlsx"; //2007 版本的excel
    
    	/**
    	 * @param in
    	 * @param fileName
    	 *
    	 * @return
    	 */
    	public List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
    		List<List<Object>> list = null;
    
    		//创建Excel工作簿
    		Workbook work = this.getWorkbook(in, fileName);
    		if (work == null) {
    			throw new Exception("创建Excel工作簿为空!");
    		}
    		Sheet sheet = null;
    		Row row = null;
    		Cell cell = null;
    		list = new ArrayList<List<Object>>();
    		//遍历Excel中的所有sheet
    		for (int i = 0; i < work.getNumberOfSheets(); i++) {
    			sheet = work.getSheetAt(i);
    			if (sheet == null) {
    				continue;
    			}
    			//遍历当前sheet中的所有行
    			//int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式,这种方式取值不准确
    			int totalRow = sheet.getPhysicalNumberOfRows();
    			for (int j = sheet.getFirstRowNum(); j < totalRow; j++) {
    				row = sheet.getRow(j);
    				if (!isRowEmpty(row)) {
    					//if(row != null && !"".equals(row)) {
    					//获取第一个单元格的数据是否存在
    					Cell fristCell = row.getCell(0);
    					if (fristCell != null) {
    						//遍历所有的列
    						List<Object> li = new ArrayList<Object>();
    						//int totalColum = row.getLastCellNum();
    						for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
    							cell = row.getCell(y);
    							String callCal = this.getCellValue(cell) + "";
    							li.add(callCal);
    						}
    						list.add(li);
    					}
    
    				} else if (isRowEmpty(row)) {
    					continue;
    				}
    
    			}
    		}
    		in.close();
    		return list;
    	}
    
    	/**
    	 * 判断行是否为空
    	 *
    	 * @param row
    	 *
    	 * @return
    	 */
    	public static boolean isRowEmpty(Row row) {
    		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
    			Cell cell = row.getCell(c);
    			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
    				return false;
    		}
    		return true;
    	}
    
    	/**
    	 * 描述:根据文件后缀,自动适应上传文件的版本
    	 *
    	 * @param inStr,fileName
    	 *
    	 * @return
    	 *
    	 * @throws Exception
    	 */
    	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
    		Workbook work = null;
    		String fileType = fileName.substring(fileName.lastIndexOf("."));
    		if (Excel_2003.equals(fileType)) {
    			work = new HSSFWorkbook(inStr);//2003 版本的excel
    		} else if (Excel_2007.equals(fileType)) {
    			work = new XSSFWorkbook(inStr);//2007 版本的excel
    		} else {
    			throw new Exception("解析文件格式有误!");
    		}
    		return work;
    	}
    
    	/**
    	 * 描述:对表格中数值进行格式化
    	 *
    	 * @param cell
    	 *
    	 * @return
    	 */
    	public Object getCellValue(Cell cell) {
    		/*Object value = null;
    		DecimalFormat df1 = new DecimalFormat("0.00");//格式化number,string字符
    		SimpleDateFormat sdf = new  SimpleDateFormat("yyy-MM-dd");//日期格式化
    		DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
    		if(cell !=null && !"".equals(cell)) {
    			switch (cell.getCellType()) {
    			case Cell.CELL_TYPE_STRING:
    				value = cell.getRichStringCellValue().getString();
    				break;
    			case Cell.CELL_TYPE_NUMERIC:
    				if("General".equals(cell.getCellStyle().getDataFormatString())) {
    					value = df1.format(cell.getNumericCellValue());
    				}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
    					value = sdf.format(cell.getDateCellValue());
    				}else if(HSSFDateUtil.isCellDateFormatted(cell)){
    					Date date = cell.getDateCellValue();
    					value = sdf.format(date);				
    				}
    				else {
    					value = df2.format(cell.getNumericCellValue());
    				}
    				break;
    			case Cell.CELL_TYPE_BOOLEAN:
    				value = cell.getBooleanCellValue();
    				break;
    			case Cell.CELL_TYPE_BLANK:
    				value = "";
    				break;
    			default:
    				break;
    			}
    		}		
    		return value;*/
    		String result = new String();  
            switch (cell.getCellType()) {  
            case HSSFCell.CELL_TYPE_FORMULA:  //Excel公式
                try {  
                	result = String.valueOf(cell.getNumericCellValue());  
                } catch (IllegalStateException e) {  
                	result = String.valueOf(cell.getRichStringCellValue());
                }  
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm")) {  
                        sdf = new SimpleDateFormat("HH:mm");  
                    } else {// 日期  
                        sdf = new SimpleDateFormat("yyyy-MM-dd");  
                    }  
                    Date date = cell.getDateCellValue();  
                    result = sdf.format(date);  
                } else if (cell.getCellStyle().getDataFormat() == 58) {  
                    // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)  
                    SimpleDateFormat sdf = new SimpleDateFormat("M月d日");  
                    double value = cell.getNumericCellValue();  
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);  
                    result = sdf.format(date);  
                } else {  
                    double value = cell.getNumericCellValue();  
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();  
                    // 单元格设置成常规  
                    if (temp.equals("General")) {  
                        format.applyPattern("#.##");  
                    }  
                    result = format.format(value);  
                }  
                break;  
            case HSSFCell.CELL_TYPE_STRING:// String类型  
                result = cell.getRichStringCellValue().toString();  
                break;  
            case HSSFCell.CELL_TYPE_BLANK:  
                result = "";  
            default:  
                result = "";  
                break;  
            }  
            return result;  
    	}
    	
    	public String getFormat(String str) {
    		if(str.equals("null")) {
    			str="";
    			return str;
    		}else{
    			return str;
    		}	
    	}
    	public Integer getFormats(Integer str) {
    		if(str==null) {
    			str=0;
    			return str;
    		}else{
    			return str;
    		}	
    	}
    
    	/**
    	 * 获取字符串中的数字订单号、数字金额等,如从"USD 374.69"中获取到374.69、从“交易单号:123456789”获取到123456789
    	 *
    	 * @return
    	 */
    	public static String getFormatNumber(String str){
    		str = str.trim();
    		 Pattern p = Pattern.compile("[0-9]");
    		 int indexNum = 0;
    		 int lenght = str.length();
    		 String num = "";
    		 for(int i=0;i<lenght;i++){
    			num += str.charAt(i);
    		 	Matcher m = p.matcher(num);
    		 	if(m.find()){
    		 		indexNum = i;
    		 		break;
    		 	}
    		  }
    		 String formatNumber = str.substring(indexNum,lenght);
    		 return formatNumber;
    	}
    }
    

    导出功能引用方法:

    // 导出
    @ResponseBody
    @RequestMapping(value = "/excelOut", method = RequestMethod.GET)
    public String excelOut(HttpServletRequest request, HttpServletResponse response) {
        try {
            // 获得输出流
            OutputStream output = response.getOutputStream();
    
            ArrayList<LinkedHashMap> titleList = new ArrayList<LinkedHashMap>();
            LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
            // title1设置标题,key固定
            titleMap.put("title1", "测试导出表");
            titleMap.put("title2", "测试导出表链接");
    
            LinkedHashMap<String, String> headMap = new LinkedHashMap<String, String>();
            headMap.put("ranking", "序号");
            headMap.put("posterName", "名称");
            headMap.put("status", "状态");
            titleList.add(titleMap);
            titleList.add(headMap);
    
            // 数据集合,下面的字段名必须和上面的map对象key或者数据实体类参数保持一致
            List<JSONObject> objects = new ArrayList<JSONObject>();
            for (int i = 0; i < 20; i++) {
                JSONObject result = new JSONObject();
                result.put("ranking", "value" + i);
                result.put("posterName", "value" + i);
                result.put("status", "value" + i);
                objects.add(result);
            }
            JSONArray objectsList = JSONArray.parseArray(objects.toString());
            // 设置应用类型,以及编码
            response.setContentType("application/msexcel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "filename=" + new String("测试导出表.xlsx/测试导出表.xls".getBytes("gb2312"), "iso8859-1"));
            ExcelUtil.exportExcel(titleList, objectsList, output);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }
    

    导入功能引用方法:

    // 导入
    @ResponseBody
    @RequestMapping(value = "/excelIn", method = RequestMethod.POST)
    public String excelIn(@RequestParam(value = "file", required = false) MultipartFile file,
                          HttpServletResponse response) {
        try {
            List<List<Object>> lists = ExcelUtil.importExcel(file);
            System.out.println("导入结果:" + lists.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }
    

    导入功能前台引用文件:

    文件引用
    jquery.min.js
    jquery.form.js
    

    导入功能前台处理方法:

    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <!DOCTYPE html>
    <head>
        <title>导入导出-导入页面</title>
        <script src="${pageContext.request.contextPath}/js/jquery.min.js?v=2.1.4"></script>
        <script src="${pageContext.request.contextPath}/js/jquery.form.js?v=2.1.4"></script>
        <script src="${pageContext.request.contextPath}/js/bootstrap.min.js?v=3.3.6"></script>
    </head>
    <body>
    <!--导入-->
    <form action="/user/excelIn" enctype="multipart/form-data" method="post" id="fileForm" name="fileForm">
        <input type="file" id="file" name="file" accept="application/vnd.ms-excel" onchange="chooseFile(this.value)">
        <input type="button" value="选择文件" class="filebtn" id="filebtn"/>
        <input type="text" id="filetxt" class="filetxt" readonly>
        <input type="submit" value="上传Excel" id="importbtn" class="importbtn"/>
    </form>
    <script>
        function exportExcel() {
            // 导出接口地址
            window.location.href = "http://127.0.0.1:8081/user/excelIn";
        }
    
        //触发file change事件
        function chooseFile(path) {
            if (path) {
                $('#filetxt').val(path);
            } else {
                $('#filetxt').attr('readonly', true);
            }
        }
    
        $(function () {
            $("#fileForm").ajaxForm({
                beforeSubmit: function () {
                    // 表单数据提交之前的操作
                },
                success: function (obj) {
                    console.log("导入结果:" + obj);
                },
                error: function (msg) {
                    console.log("导入错误:" + msg);
                }
            });
        });
    </script>
    </body>
    </html>
    

    示例:

    导入结果:[[测试导出表], [测试导出表链接], [序号, 名称, 状态], [value0, value0, value0], [value1, value1, value1], [value2, value2, value2], [value3, value3, value3], [value4, value4, value4], [value5, value5, value5], [value6, value6, value6], [value7, value7, value7], [value8, value8, value8], [value9, value9, value9], [value10, value10, value10], [value11, value11, value11], [value12, value12, value12], [value13, value13, value13], [value14, value14, value14], [value15, value15, value15], [value16, value16, value16], [value17, value17, value17], [value18, value18, value18], [value19, value19, value19]]

    导出结果:
    导出结果展示

    以上就是完整的导入导出功能,支持大数据量快速导出,导入.

    转载请注明出处!

    展开全文
  • Java POI处理大量数据导入导出xls和xlsx导入数据(大量)导出数据(大量)总结 xls和xlsx xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包, 如图: xls是以二进制的方式...

    xls和xlsx

    1. xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包,
      如图:
      在这里插入图片描述
    2. xls是以二进制的方式存储,这种格式不易被其他软件读取使用;而xlsx采用了基于XML的ooxml开放文档标准,ooxml使用XML和ZIP技术结合进行文件存储,XML是一个基于文本的格式,而且ZIP容器支持内容的压缩,所以其一大优势是可以大大减小文件的尺寸;
    3. 使用POI来读写Excel文件有两种方式,一种用户模式(UserModel),读取时消耗大量内存,造成OOM问题;一种事件模式(SAX模式),仅仅关注文件内部数据,内存消耗很低;
    4. 导出文件同样如此,使用Workbook普通导出,数据量小的时候可以正常使用,但时间等待仍然很长,这时推荐使用POI提供的SXXFWorkbook处理,其使用时间窗口原理(具体可以查询)限制访问,刷出内存,降低内存消耗,提升效率。
      另外还需要注意,根据你使用的功能,仍然可能消耗大量内存,例如合并区域,超链接,注释……,这些内容只存储在内存中。
      在这里插入图片描述

    导入数据(大量)

    大量数据导入在网络上搜寻到的相关代码大部分通过集成POI原生的DefaultHandler重写其startElement, endElement, characters方法进行相关的解析,而POI已经将相关逻辑封装在XSSFSheetXMLHandler,只要实现暴露的接口SheetContentsHandler即可。
    使用SheetContentsHandler的例子可以参考官方的XLSX2CVS
    本例实现该接口:

    package cn.skio.venus.api;
    
    import org.apache.poi.openxml4j.opc.OPCPackage;
    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.poi.xssf.usermodel.XSSFComment;
    import org.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    
    import javax.xml.parsers.ParserConfigurationException;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.LinkedList;
    import java.util.List;
    
    /**
     * @autor jasmine
     */
    public class ExcelEventParser {
        private String fileName;
        private SimpleSheetContentsHandler handler;
        // 测试使用对比使用SAX和UserModel模式选择(实际使用不需要)
        private Integer saxInterupt;
    	private void setHandler(SimpleSheetContentsHandler handler) {
    		this.handler = handler;
    	}
    
    	// 放置读取数据
        protected List<List<String>> table = new ArrayList<>();
    
        public ExcelEventParser(String filename, Integer saxInterupt){
            this.fileName = filename;
            this.saxInterupt = saxInterupt;
        }
    
        public List<List<String>> parse() {
            OPCPackage opcPackage = null;
            InputStream inputStream = null;
    
            try {
                FileInputStream fileStream = new FileInputStream(fileName);
                opcPackage = OPCPackage.open(fileStream);
                XSSFReader xssfReader = new XSSFReader(opcPackage);
    
                StylesTable styles = xssfReader.getStylesTable();
                ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opcPackage);
                inputStream = xssfReader.getSheetsData().next();
    
                processSheet(styles, strings, inputStream);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (inputStream != null) {
                    try {
                        inputStream.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (opcPackage != null) {
                    try {
                        opcPackage.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
            return table;
        }
    
    	// 确定XMLReader解析器,使用SAX模式解析xml文件
        private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws SAXException, ParserConfigurationException, IOException {
            XMLReader sheetParser = SAXHelper.newXMLReader();
    
            if (handler == null) {
                setHandler(new SimpleSheetContentsHandler());
            }
            sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));
    
            try {
                sheetParser.parse(new InputSource(sheetInputStream));
            } catch (RuntimeException e) {
                System.out.println("---> 遇到空行读取文件结束!");
            }
        }
    
    	// 实现SheetContentsHandler
        public class SimpleSheetContentsHandler implements SheetContentsHandler{
            protected List<String> row;
            @Override
            public void startRow(int rowNum) {
                row = new LinkedList<>();
            }
    
            @Override
            public void endRow(int rowNum) {
            	// 判断是否使用异常作为文件读取结束(有些Excel文件格式特殊,导致很多空行,浪费内存)
                if (saxInterupt == 1) {
                    if (row.isEmpty()) {
                        throw new RuntimeException("Excel文件读取完毕");
                    }
                }
    			// 添加数据到list集合
                table.add(row);
            }
    
            /**
             * 所有单元格数据转换为string类型,需要自己做数据类型处理
             * @param cellReference 单元格索引
             * @param formattedValue 单元格内容(全部被POI格式化为字符串)
             * @param comment
             */
            @Override
            public void cell(String cellReference, String formattedValue, XSSFComment comment) {
                row.add(formattedValue);
            }
    
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {
            }
        }
    }
    
    

    经测试结果,发现使用SAX模式(抛弃了样式等,只关注数据)仅仅消耗很少内存,效率高;而普通Workbook读取数据(测试文件为5.2MB的有大量空行文件)内存消耗 > 1GB(此时线上系统OOM概率非常大);
    在这里插入图片描述

    导出数据(大量)

    导出数据的话瓶颈主要在于数据写入Excel文件,代码(同样的74273条数据导出)如下:

    	// 使用SXSSFwrokbook,大量数据处理快速
    	@GetMapping("/outExcel")
        public void outPutExcel(HttpServletResponse response) throws Exception {
            // 每次写100行数据,就刷新数据出缓存
            SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
            Sheet sh = wb.createSheet();
            List<Tmp> tmps = tmpDao.findAll();
            log.info("---> 数据量:{}", tmps.size());
    
            for(int rowNum = 0; rowNum < tmps.size(); rowNum++){
                Row row = sh.createRow(rowNum);
                Tmp tmp = tmps.get(rowNum);
                Cell cell1 = row.createCell(0);
                cell1.setCellValue(tmp.getSource());
    
                Cell cell2 = row.createCell(1);
                cell2.setCellValue(tmp.getName());
                Cell cell3 = row.createCell(2);
                cell3.setCellValue(tmp.getPhone());
                Cell cell4 = row.createCell(3);
                cell4.setCellValue(tmp.getCity());
            }
    
            String fileName = "sxssf.xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            wb.write(response.getOutputStream());
            wb.close();
        }
    
    	// XSSFWorkbook, 效率低下
    	@GetMapping("/outExcel2")
        public void outPutExcel2(HttpServletResponse response) throws Exception {
            XSSFWorkbook wb = new XSSFWorkbook();
            Sheet sh = wb.createSheet();
            List<Tmp> tmps = tmpDao.findAll();
            log.info("---> 数据量:{}", tmps.size());
    
            for(int rowNum = 0; rowNum < tmps.size(); rowNum++){
                Row row = sh.createRow(rowNum);
                Tmp tmp = tmps.get(rowNum);
                Cell cell1 = row.createCell(0);
                cell1.setCellValue(tmp.getSource());
    
                Cell cell2 = row.createCell(1);
                cell2.setCellValue(tmp.getName());
                Cell cell3 = row.createCell(2);
                cell3.setCellValue(tmp.getPhone());
                Cell cell4 = row.createCell(3);
                cell4.setCellValue(tmp.getCity());
            }
    
            String fileName = "sxssf.xlsx";
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "GBK"));
            wb.write(response.getOutputStream());
            wb.close();
        }
    

    效率对比:

    对象 耗时
    SXSSFWorkbook 在这里插入图片描述
    XSSFWorkbook 在这里插入图片描述

    CPU和内存消耗对比:
    在这里插入图片描述

    总结

    1. 大文件读取使用SAX
    2. 大文件写入使用SXSSFWorkbook

    参考链接:
    [1]: https://blog.csdn.net/Holmofy/article/details/82532311
    [2]: https://blog.csdn.net/daiyutage/article/details/53010491
    [3]: https://www.cnblogs.com/yfrs/p/5689347.html
    [4]: easyexcel

    展开全文
  • poi导入100万大数据

    2017-07-06 10:46:39
    poi导入100万大数据,前提你的jvm调过有 调大堆的大小。
    package com.jk.controller.user;
    
    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.formula.functions.T;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.model.SharedStringsTable;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    import org.apache.tomcat.jni.User;
    import org.xml.sax.Attributes;
    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;
    
    import com.jk.model.user.UserBean;
    import com.jk.service.user.UserService;
    
    public class ReadExcel 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;
    	public List<UserBean> dataList = new ArrayList<UserBean>();
    	private UserService userService;
    	public ReadExcel(UserService userService) {
    		super();
    		this.userService = userService;
    	}
        /**  
         * 读取所有工作簿的入口方法  
         * @param path  
         * @throws Exception 
         */  
        public void process(InputStream inputStream) throws Exception {   
            OPCPackage pkg = OPCPackage.open(inputStream);
            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();   
            }   
        }   
        /**  
         * 该方法自动被调用,每读一行调用一次,在方法中写自己的业务逻辑即可 
         * @param sheetIndex 工作簿序号 
         * @param curRow 处理到第几行 
         * @param rowList 当前数据行的数据集合 
         */  
        public void optRow(int sheetIndex, int curRow, List<String> rowList) {   
        	if (curRow >=1) {
        		if (dataList.size() >= 500 || rowlist.size() == 0) {
        			userService.addUserBatch(dataList);
        			dataList.clear();
        		}
        		if (rowlist.size() > 0) {
        			UserBean userBean = new UserBean();
            		userBean.setName(rowList.get(0));
            		userBean.setSex(rowList.get(1).trim().equals("男") ? 1:0);
            		userBean.setRegisTime(rowList.get(2));
            		userBean.setLoginNumer(rowList.get(3));
            		userBean.setPassword(rowList.get(4));
            		userBean.setHeadImg(rowList.get(5));
            		String status = rowList.get(6);
            		if (status.trim().equals("正常")) {
            			userBean.setStatus(1);
            		}else if(status.trim().equals("冻结")){
            			userBean.setStatus(2);
            		}else if(status.trim().equals("账户异常")){
            			userBean.setStatus(3);
            		}else if(status.trim().equals("锁定")){
            			userBean.setStatus(4);
            		}
            		userBean.setRemark(rowList.get(7));
            		dataList.add(userBean);
    			}
    		}
        }   
           
        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 (name.equals("c")) {   
                // 如果下一个元素是 SST 的索引,则将nextIsString标记为true  
                String cellType = attributes.getValue("t");   
                if (cellType != null && cellType.equals("s")) {   
                    nextIsString = true;   
                } else {   
                    nextIsString = false;   
                }   
            }   
            // 置空   
            lastContents = "";   
        }   
           
        public void endElement(String uri, String localName, String name)   
                throws SAXException { 
        	if (localName.equals("worksheet")) {
        		rowlist.clear();
        		optRow(sheetIndex, 1, rowlist);
        		curRow = 0;
    		}
            // 根据SST的索引值的到单元格的真正要存储的字符串  
            // 这时characters()方法可能会被调用多次  
            if (nextIsString) {   
                try {   
                    int idx = Integer.parseInt(lastContents);   
                    lastContents = new XSSFRichTextString(sst.getEntryAt(idx))   
                            .toString();   
                } catch (Exception e) {   
      
                }   
            }   
            // v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引  
            // 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符  
            if (name.equals("v")) {   
                String value = lastContents.trim();   
                value = value.equals("") ? " " : value;   
                rowlist.add(curCol, value);   
                curCol++;   
            } else {   
                // 如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法  
                if (name.equals("row")) {   
                    optRow(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);   
        }
    }
    

    展开全文
  • This one comes up quite a lot, but often the reason isn't what you might initially think. So, the first thing to check is - what's the source of the problem? Your file? Your code? Your environment?...

    This one comes up quite a lot, but often the reason isn't what you might initially think. So, the first thing to check is - what's the source of the problem? Your file? Your code? Your environment? Or Apache POI?

    (If you're here, you probably think it's Apache POI. However, it often isn't! A moderate laptop, with a decent but not excessive heap size, from a standing start, can normally read or write a file with 100 columns and 100,000 rows in under a couple of seconds, including the time to start the JVM).

    Apache POI ships with a few programs and a few example programs, which can be used to do some basic performance checks. For testing file generation, the class to use is in the examples package, SSPerformanceTest. Run SSPerformanceTest with arguments of the writing type (HSSF, XSSF or SXSSF), the number rows, the number of columns, and if the file should be saved. If you can't run that with 50,000 rows and 50 columns in HSSF and SXSSF in under 3 seconds, and XSSF in under 10 seconds (and ideally all 3 in less than that!), then the problem is with your environment.

    Next, use the example program ToCSV to try reading the a file in with HSSF or XSSF. Related is XLSX2CSV, which uses SAX parsing for .xlsx. Run this against both your problem file, and a simple one generated by SSPerformanceTest of the same size. If this is slow, then there could be an Apache POI problem with how the file is being processed (POI makes some assumptions that might not always be right on all files). If these tests are fast, then any performance problems are in your code!

    展开全文
  • POI 导入大数据

    2017-04-28 21:10:06
    package com.jeeframe.cms.updata.service.impl; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager;...import java.sql.PreparedStatem
  • 使用POI能够导出大数据保证内存不溢出的一个重要原因是SXSSFWorkbook生成的EXCEL为2007版本,修改EXCEL2007文件后缀为ZIP打开可以看到,每一个Sheet都是一个xml文件,单元格格式和单元格坐标均用标签表示。...
  • package org.hzjun.hlt.excel; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List;...import javax.xml.parsers.Pars
  • poi的读取的三种模式 模式 说明 读写性 SXSSF 内存中保留一定行数数据,超过行数,将索引最低的数据刷入硬盘 只写 eventmodel 基于事件驱动,SAX的方式解析excel,cup和内存消耗低 只读 ...
  • poi导出大数据 1000万

    2017-11-20 19:13:38
    @RequestMapping("exportUserToExcel.do")   public void exportUserToExcel(UserBean userBean,HttpServletRequest request,HttpServletResponse response) throws Exception{   SXSSFWorkbook wb =...
  • excel 模板导入工具类简介:工具类支持 2003/2007 excel主要根据 excel 模板上的某一行指定 Java bean 的属性列名,再使用 java 反射,进行实体对象的构造使用者可以根据业务需要对该工具类自行扩展模板样式:代码...
  • if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {//poi导入,转换成String类型 DecimalFormat df = new DecimalFormat("0");//格式化实例化数字 ri.setYehujinjilinkmanphone(df.format(cell....
  • java中导出excel第一步 创建一个个workbook对象创建一个工作空间设置表格样式第二部 设置标题 设置表列sheet.createRow(rownum++); 创建一行第三部 设置表列sheet.createRow(rownum++);创建一行第四部 创建行对象...
  • 昨天,产品提了一个紧急需求,让把十一月份已发货的商品数据导出来,写好SQL发给DBA执行之后,得到了三十多个100W数据的Excel文件。有一个属性是以JSON格式存在表中一个字段里面的,需要加工Excel文件将其单独取出来...
  • 考虑到Excel导入在项目中应用较多,随后其他项目中都会用到,为了减少重复工作,不必因为excel的样式、数据等变化而重新编码,重复劳动,因此必须抽取通用的东西出来,封装共同点,暴露个性点,使用时只需要关注相关...
  • java导入大数据文件

    2019-06-14 15:34:32
    2019独角兽企业重金招聘Python工程师标准>>> ...
  • POI处理大数据excel

    2020-03-19 17:01:18
    xls和xlsx 1:xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包, 如图: 2:xls是以二进制的方式存储,这种格式不易被其他软件读取使用;而xlsx采用了基于XML的ooxml开放...
1 2 3 4 5 ... 20
收藏数 747
精华内容 298