热门标签
热门文章
- 1Python常用的12个GUI框架，张口就来_python桌面应用框架
- 2低代码：美味膳食或垃圾食品？
- 36大产品20项亮点，支付宝小程序云全揭秘_支付宝小程序云产品发布会:6大产品20项亮点全揭秘
- 4【2】VitePress搭建个人博客-快速上手_vitepress搭建博客
- 5泛型（包装类），反射，枚举，lambda表达式_泛型枚举反射
- 6[已解决:多种方法:本地上传码云报错]The authenticity of host ‘gitee.com (212.64.63.215)‘ can‘t be established._unexpected key word verify
- 7题解 | #24点运算#注意边界#题中规律&；数学规律两种解法_华创微系统面试
- 8接口自动化测试：Postman实战教程_postman接口自动化，软件测试面试资料集合
- 9网络安全—PKI公钥基础设施_pki公钥体系
- 102024软件测试就业现状分析，到底是卷还是润？_软件测试行业目前现状
当前位置: article > 正文
JAVA 数据导入导出（CSV、TXT、EXCL（读取大文件））_java读取超大csv excel 文件的实现方式
作者：IT小白 | 2024-06-16 09:08:22
赞
踩
java读取超大csv excel 文件的实现方式
文件数据导入功能：
设计思路：
- 先实现文件上传到服务器，获取到文件名和文件保存的路径
- 获取当前项目运行的路径，读取服务器中的文件
- 解析文件数据
- 数据处理，导入数据库
文件部分上传代码：
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- txt数据文件导入：
RandomAccessFile raf = new RandomAccessFile(path, “rw”);
while ((line_record = raf.readLine()) != null) {
line_record = new String(line_record.getBytes("ISO-8859-1"), "utf8");
- 1
- 2
- 3
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);
}
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
用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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
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;
}
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
数据库连接方式(可用多数据源替换)
/**
* @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"));
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/725998
推荐阅读
相关标签
