JAVA WEB 实现文件数据导入(CSV、TXT、excel(读取大文件))

设计思路:
1、先实现文件上传到服务器,获取到文件名和文件保存的路径
2、获取当前项目运行的路径,读取服务器中的文件
3、解析文件数据
4、数据处理,导入数据库

文件部分上传代码:

	if (!file.isEmpty()) {
		FileTb upLoadFile = null;
		String originalName = file.getOriginalFilename();
		int dotPosition = originalName.indexOf(".");
		String fileType = null;
		String filename;
		if (dotPosition != -1) {
			fileType = originalName.substring(
					originalName.indexOf(".") + 1, originalName.length());
			filename = originalName;
		} else {
			filename = DateUtils.currentDate();
		}
		File savedFile = new File(path, filename);
		try {
			FileUtils.copyInputStreamToFile(file.getInputStream(),
					savedFile);
		} catch (IOException e) {
			e.printStackTrace();
  1. excel文件上传(包括一些特定的处理方式POI常用模式:适合小数据量):

     	 Workbook wookbook = getSheets(path);
     //获取sheet的总数
     for (int i = 0; i < wookbook.getNumberOfSheets(); i++) {
     	Date startTime = new Date();
     	//得到一个工作表
     	Sheet sheet = wookbook.getSheetAt(i);
     	
     	//1.创建一个存储导入数据的列表:importList:List<LinkedList>
     	
     	List<LinkedList> importList = new ArrayList<>();
     	
     	
     	//2.将excel中为表头保存为key-value的集合:excelFieldMap
     	Map<Object, Integer> excelFieldMap = new HashMap<>();
     	Row headRow = sheet.getRow(0);
     	//3.表属性列表
     	LinkedList<String> propsTabList = new LinkedList<>();
     	propsTabList.add(MYJZConfig.TABLENAMEPROMARYKEY);
     	if (headRow != null) {
     		for (int j = 0; j < headRow.getPhysicalNumberOfCells(); j++) {
     			
     			//4.获取该表下所有的属性字段名且移除id:propsList :List<String>
     			List<String> propsList = commenUtilMapper.findAllPropsName('"' + tableName + '"');
     			propsList.remove(MYJZConfig.TABLENAMEPROMARYKEY);
     			
     			//5.根据表属性字段名去查询表属性字段名和excel字段名对应表,获取1. excel中的字段名 :excelfield
     			
     			for (String prop : propsList) {
     				if (headRow.getCell(j).getStringCellValue().equalsIgnoreCase(excelTableFieldMapper.findExcelFieldByTabNameAndTabFiledName(tableName, prop))) {
     					
     					//6.将excelFieldMap中的value值用prop替换
     					excelFieldMap.put(prop, j);
     					
     					propsTabList.add(prop);
     				}
     			}
     		}
     		//TODO 操作数据-----------------------------------
     		//6.获得数据的总行数
     		int totalRowNum = sheet.getLastRowNum();
     		int successCount = 0;
     		//7.获得所有数据:通过下标index获取
     		
     		for (int rowNum = 1; rowNum < totalRowNum; rowNum++) {
     			
     			Row row = sheet.getRow(rowNum);
     	
     		}
    

    /**

    • 获取sheet表
    • @param filePath :完整file的URL
    • @return
      */
	private Workbook getSheets(String filePath) {
		
		FileInputStream fis = null;
		Workbook wookbook = null;
		try {
			//获取一个绝对地址的流
			fis = new FileInputStream(filePath);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (filePath.endsWith(".xls")) {
			/*2003版本的excel,用.xls结尾:得到工作簿*/
			try {
				wookbook = new HSSFWorkbook(fis);//
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else if (filePath.endsWith(".xlsx")) {
			try {
				/*2007版本的excel,用.xlsx结尾 :工作薄*/
				wookbook = new XSSFWorkbook(fis);
			} catch (IOException e) {
				//  Auto-generated catch block
				e.printStackTrace();
			}
		} else {
			throw new CustomException("文件不是excel类型");
		}
		
		return wookbook;
	}
  1. csv 文件导入:

     /* 读取文件内容*/
     try {
     	
     	// 创建CSV读对象 例如:CsvReader(文件路径,分隔符,编码格式);
     	CsvReader reader = new CsvReader(savePath, ',', Charset.forName("UTF-8"));
     	
     	// 跳过表头 如果需要表头的话,这句可以忽略
     	reader.readHeaders();
     	
     	/**
     	 * 获取关系表中的字段名
     	 */
     	List<String> tableFieldsList = commenUtilMapper.findAllPropsName('"' + tableName + '"');
     	
     	// 逐行读入除表头的数据
     	while (reader.readRecord()) {
     		
     		String excelfieldValue = reader.get(excelfield);
    
     	}
    
     	reader.close();
    
  2. txt数据文件导入:

     RandomAccessFile raf = new RandomAccessFile(path, “rw”);
       while ((line_record = raf.readLine()) != null) {
     line_record = new String(line_record.getBytes("ISO-8859-1"), "utf8");
    

excel 2007:快速读取数据(POI 用户模式:适合大数据量)

package com.swad.asura.common;

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.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.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

public class ExcelAnalyseUtils {
	public static void main(String[] args) {
		Date da = new Date();
		List<Map<String, String>> datas = getFileData(
				"D:\\apache-tomcat-8.5.20\\webapps\\files\\upload\\201805161306480986.xlsx", "四川1");
		System.out.println(new Date().getTime() - da.getTime());
	}

	public static List<Map<String, String>> getFileData(String filePath, String sheeName) {
		List<Map<String, String>> results = new ArrayList<>();
		ExcelParser parser;
		OPCPackage pkg = null;
		try {
			pkg = OPCPackage.open(filePath, PackageAccess.READ);
			parser = new ExcelParser(pkg, null, -1, results);
			parser.process(sheeName);
		} catch (OpenXML4JException | SAXException | IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (null != pkg) {
				try {
					pkg.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		return results;
	}
}

class ExcelParser {
	/**
	 * 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;
		private Map<String, String> record = new HashMap();
		private int index = 0;
		private List<String> title = new ArrayList<>();

		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) {
			firstCellOfRow = true;
			currentRow = rowNum;
			currentCol = -1;
			index = 0;
		}

		@Override
		public void endRow(int rowNum) {
			if (rowNum > 0) {
				Map<String, String> result = new HashMap();
				result.putAll(record);
				record.clear();
				results.add(result);
			}

		}

		@Override
		public void cell(String cellReference, String formattedValue, XSSFComment comment) {
			if (currentRow == 0) {
				title.add(formattedValue);
			} else {
				record.put(title.get(index), formattedValue);
			}

			index++;
		}

		@Override
		public void headerFooter(String text, boolean isHeader, String tagName) {
			
		}
	}

	

	private final OPCPackage xlsxPackage;

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

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

	private final List<Map<String, String>> results;

	/**
	 * 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 ExcelParser(OPCPackage pkg, PrintStream output, int minColumns, List<Map<String, String>> results) {
		this.xlsxPackage = pkg;
		this.output = output;
		this.minColumns = minColumns;
		this.results = results;
	}

	/**
	 * 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(String sheet) 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();
		while (iter.hasNext()) {
			try (InputStream stream = iter.next()) {
				String sheetName = iter.getSheetName();
				if (!sheetName.equals(sheet)) {
					continue;
				}
				processSheet(styles, strings, new SheetToCSV(), stream);
			}
		}
	}

}

用StreamingReader读取

  public static Map<String, List<List<String>>> readExcelByStreamingReader(String filePath, int num) {
        Workbook wk = null;
        Map<String, List<List<String>>> metaInfo = null;
        try {
            wk = StreamingReader.builder().rowCacheSize(100) // 缓存到内存中的行数,默认是10
                    .bufferSize(4096) // 读取资源时,缓存到内存的字节大小,默认是1024
                    .open(new FileInputStream(filePath)); // 打开资源,必须,可以是InputStream或者是File,注意:只能打开XLSX格式的文件

//            wk = StreamingReader.builder()
//                    .sstCacheSize(100)
//                    .open(new FileInputStream(filePath)); // 打开资源,必须,可以是InputStream或者是File,注意:只能打开XLSX格式的文件

            metaInfo = new LinkedHashMap<>();
            for (int i = 0; i < wk.getNumberOfSheets(); i++) {
                Sheet sheet = wk.getSheetAt(i);
                List<List<String>> sample = new LinkedList<>();
                for (Row row : sheet) {
                    // 遍历所有的列
                    List<String> rowData = new LinkedList<>();
                    for (Cell cell : row) {
                        rowData.add(cell.getStringCellValue() + "");
                    }
                    if (num>=0 && sample.size() < num) {
                        sample.add(rowData);
                        break;
                    }
                }
                metaInfo.put(sheet.getSheetName(), sample);
            }
        } catch (FileNotFoundException e) {
            throw new AsuraRuntimeException(e);
        } finally {
            try {
                if (null != wk) {
                    wk.close();
                }
            } catch (IOException e) {
                wk = null;
            }
        }
        return metaInfo;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;

/**
 * @author liaowei
 * @create 2018-05-03 16:46
 * @desc 将数据load进mysql
 * sql.csv:可不用
 **/

public class LoadDataToMysqlUtils {

	/**
	 * 获取字段列表及字段流
	 *
	 * @param datas
	 * @return String testSql = "LOAD DATA LOCAL INFILE '12.csv' IGNORE INTO TABLE
	 *         test.test (a,b,c,d,e,f)";
	 */
	public static LoadDataToMysqlFieldsVo getDataInputStream(HttpServletRequest request,
			List<Map<String, String>> datas, Map<String, String> MysqlToExcelProp, String userName, String caseId) {
		LoadDataToMysqlFieldsVo loadDataToMysqlFieldsVo = new LoadDataToMysqlFieldsVo();
		String sql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE  " + 表名 + " (";
		StringBuilder builder = new StringBuilder();
		String midSql = "";
		request.getSession().setAttribute("TotalNumberOfData", datas.size());
		for (int i = 0; i < datas.size(); i++) {
			for (Map.Entry<String, String> mapData : MysqlToExcelProp.entrySet()) {
				if (i == 0) {
					if (StringUtils.isEmpty(midSql)) {
						midSql = mapData.getValue();
					} else {
						midSql += "," + mapData.getValue();
					}
				}
				if ("member_register_date".equalsIgnoreCase(mapData.getValue())) {
					if (StringUtils.isNotEmpty(datas.get(i).get(mapData.getKey()))) {
						List<String> convetTime = DateUtils.ArrayDateForm(datas.get(i).get(mapData.getKey()));
						String date = convetTime.get(0) + "-" + convetTime.get(1) + "-" + convetTime.get(2) + " "
								+ convetTime.get(3) + ":" + convetTime.get(4) + ":" + convetTime.get(5);
						builder.append(date);
					} else {
						builder.append("");
					}
				} else {
					if (StringUtils.isNotEmpty(datas.get(i).get(mapData.getKey()))) {
						builder.append(datas.get(i).get(mapData.getKey()));
					} else {
						builder.append("");
					}
				}
				builder.append("\t");
			}

			builder.append(caseId);
			builder.append("\t");
			builder.append(AsuraTimeUtils.currentDate_Time());
			builder.append("\t");
			builder.append(userName);
			builder.append("\t");
			builder.append(AsuraTimeUtils.currentDate_Time());
			builder.append("\t");
			builder.append(userName);
			builder.append("\t");
			builder.append("\n");
			request.getSession().setAttribute("LoadedData", (long) (i + 1));
		}
		sql += midSql + ",case_id,create_date,create_account,last_modify_time,last_modify_account)";
		byte[] bytes = builder.toString().getBytes();
		InputStream is = new ByteArrayInputStream(bytes);
		loadDataToMysqlFieldsVo.setSql(sql);
		loadDataToMysqlFieldsVo.setDataStream(is);
		return loadDataToMysqlFieldsVo;
	}

	/**
	 * 保存到数据库中
	 *
	 * @param loadDataToMysqlFieldsVo
	 */
	public static void loadDataIntoMysql(LoadDataToMysqlFieldsVo loadDataToMysqlFieldsVo) {
		Connection conn = null;
		try {

			System.out.println(loadDataToMysqlFieldsVo.getSql());

			conn = JdbcConnUtils.getConn();
			PreparedStatement statement = conn.prepareStatement(loadDataToMysqlFieldsVo.getSql());
			if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {
				com.mysql.jdbc.PreparedStatement mysqlStatement = statement
						.unwrap(com.mysql.jdbc.PreparedStatement.class);
				mysqlStatement.setLocalInfileInputStream(loadDataToMysqlFieldsVo.getDataStream());
				mysqlStatement.executeUpdate();
			}
		} catch (Exception e) {
			throw new RuntimeException("操作数据库错误!" + e);
		} finally {
			try {
				if (null != conn) {
					conn.close();
				} else {

				}

			} catch (SQLException e) {
				conn = null;
			}
		}
	}

}


数据库连接方式

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.springframework.util.ResourceUtils;

/**
 * @author liaowei
 * @create 2017-11-06 10:36
 * @desc 原始连接数据库方式
 */
public class JdbcConnUtils {

	private static Connection conn = null;

	private static Properties props = null;

	static {
		loadProps();
	}

	synchronized static private void loadProps() {
		props = new Properties();
		InputStream in = null;
		try {
			// 第一种 通过类加载器进行获取properties文件流
			in = ResourceUtils.class.getClassLoader().getResourceAsStream("project_config.properties");
	
			props.load(in);
		} catch (FileNotFoundException e) {
			throw new RuntimeException("未找到需要读取的文件!");
		} catch (IOException e) {
			throw new RuntimeException("IO异常!");
		} finally {
			try {
				if (null != in) {
					in.close();
				}
			} catch (IOException e) {
				throw new RuntimeException("数据流关闭失败!");
			}
		}
	}

	public static String getProperty(String key) {
		if (null == props || props.size() <= 0) {
			loadProps();
		}
		return props.getProperty(key);
	}

	public static Connection getConn() {
		try {
			Class.forName(getProperty("asura.db.driver"));
			conn = DriverManager.getConnection(props.getProperty("asura.db.url"),
					props.getProperty("asura.db.username"), props.getProperty("asura.db.pwd"));
		} catch (ClassNotFoundException cnfe) {
			throw new RuntimeException("加载驱动失败");
		} catch (SQLException e) {
			throw new RuntimeException("获取连接失败");
		}

		return conn;
	}

	public void closeConn() {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) {
		System.out.println(JdbcConnUtils.getProperty("asura.db.pwd"));
	}
}
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 终极编程指南 设计师:CSDN官方博客 返回首页