JAVA 数据导入导出(CSV、TXT、EXCL(读取大文件))

文件数据导入功能:

设计思路:

  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();
  1. 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 用户模式:适合大数据量)

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;
			}
		}
	}

}

数据库连接方式(可用多数据源替换)

/**
 * @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"));
	}
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一介布衣+

做好事,当好人

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值