前言
最近需要一个需求，有80个xlsx文档，每个约50~80M之间，需要导入到pgsql数据库中。开始尝试用navicat导入，发现效率很低，一个个的手工导入实在太慢。因此想到用copy的方式入库，但是copy的方式只能将csv格式的数据文件导入到pgsql中。然后开始到处找工具将excel文档转换成csv，经过测试发现excel2016可以将xlsx另存为csv，但是此csv不是utf-8格式的，还需要将csv转换成utf-8格式，而且操作过程也是非常的麻烦。后面在网上找了一个exe工具，经过测试20M的文件转换时都卡死。实在没有办法了，只能自己敲代码，实现将excel转换成utf-8格式的代码！工具包在最下面！
准备项目
项目开发工具
Eclipse IDE for Enterprise Java and Web Developers (includes Incubating components)
Version: 2022-06 (4.24.0)
Build id: 20220609-1112
项目结构
代码如下
DealMain 是程序的入口，用来读取数据和输出数据。
package com.main;
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.service.DealMethod;
import com.utils.LogUtil;
import com.utils.PropertiesUtil;
/**
*
* @author 小胖java
*
* 2023年4月4日
*/
public class DealMain {
public static Logger log = Logger.getGlobal();
static {
Properties prop = new Properties();
PropertiesUtil propertiesUtil = new PropertiesUtil();
try {
propertiesUtil.getProperties(prop);
} catch (IOException e) {
e.printStackTrace();
}
// 由于jdk自带的全局log没有写入文件的功能，我这里手动添加了文件handler
LogUtil.addFileHandler(log, Level.INFO, prop.getProperty("deal.log") + File.separator + "deal.log");
}
public static void main(String[] args) throws Exception {
log.info("开始处理文件！");
// 0.获取文件列表
DealMethod dealMethod = new DealMethod();
String in = dealMethod.getPath("in");
File file = new File(in);
// 1.校验输入目录是否为空
if (file.list().length == 0) {
log.warning("目录为空，请添加文件！");
return;
}
// 2.处理文件
File[] files = file.listFiles();
for (File fe : files) {
String suffix = fe.getName().substring(fe.getName().lastIndexOf(".") + 1);
if (fe.isDirectory() || suffix.equals("csv")) {
continue;
}
Map<Integer, String> title = dealMethod.getTitleIndex2(fe);
List<Map<String, String>> maps = dealMethod.dealFile(fe);
// 输出到csv文件
String name = fe.getName();
dealMethod.exportCsv(maps, name, title);
}
// 3.输出为csv文件
log.info("结束处理文件！");
}
}
DealMethod 是核心代码，实现了转换的业务
package com.service;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.logging.Logger;
import com.utils.CsvUtil;
import com.utils.ExcelUtil;
import com.utils.PropertiesUtil;
/**
*
* @author 小胖java
*
* 2023年4月4日
*/
public class DealMethod {
private static Logger log = Logger.getGlobal();
// 0.获取输入与输出目录
public String getPath(String type) throws IOException {
log.info(log.toString());
Properties prop = new Properties();
PropertiesUtil propertiesUtil = new PropertiesUtil();
propertiesUtil.getProperties(prop);
if ("in".equals(type)) {
return prop.getProperty("deal.in");
} else {
return prop.getProperty("deal.out");
}
}
// 1.处理文件
public List<Map<String, String>> dealFile(File fe) throws Exception {
InputStream in = null;
String version;
ExcelUtil excel;
try {
log.info("开始读取文件：" + fe.getName() + "...");
// 判断版本
version = ExcelUtil.isExcel2003(fe.getName()) ? "2003" : "2007";
in = new FileInputStream(fe);
excel = new ExcelUtil(in, version);
log.info("抽取sheet的标题索引" + "...");
// 获取需要抽取列的键值
Map<Integer, String> titleMap = getTitleIndex(excel);
log.info("整理结果集...");
// 获取结果集数据
List<Map<String, String>> dataMap = getDataMap(titleMap, excel);
log.info(fe.getName() + "=>结果集条数：" + dataMap.size() + "条");
return dataMap;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
if (in != null) {
in.close();
}
}
}
/**
* 整理最终结果集
*
* @param titleMap
* @param excelUtil
* @return
* @throws Exception
*/
private List<Map<String, String>> getDataMap(Map<Integer, String> titleMap, ExcelUtil excelUtil) throws Exception {
// 获取sheet页数据
List<Map<Integer, String>> maps = excelUtil.readMap(0);
List<Map<String, String>> nmaps = new ArrayList<>();
Set<Integer> keyTitleSet = titleMap.keySet();
// 遍历提取数据
maps.forEach(i -> {
Set<Integer> keySet = i.keySet();
Map<String, String> nmap = new HashMap<>();
keySet.forEach(k -> {
if (keyTitleSet.contains(k)) {
nmap.put(titleMap.get(k), i.get(k));
}
});
nmaps.add(nmap);
});
return nmaps;
}
public Map<Integer, String> getTitleIndex2(File fe) throws Exception {
// 判断版本
String version = ExcelUtil.isExcel2003(fe.getName()) ? "2003" : "2007";
InputStream in = new FileInputStream(fe);
ExcelUtil excel = new ExcelUtil(in, version);
// 获取需要抽取列的键值
Map<Integer, String> titleMap = getTitleIndex(excel);
return titleMap;
}
public Map<Integer, String> getTitleIndex(ExcelUtil excelUtil) throws Exception {
Map<Integer, String> map = new HashMap<>();
Map<Integer, String> kv = excelUtil.getRowKeyValue(0, 0);
if (kv.isEmpty()) {
log.warning("文件格式有问题，请核查文件格式！");
return null;
}
Set<Integer> keySet = kv.keySet();
keySet.forEach(k -> {
map.put(k, kv.get(k));
});
return map;
}
public boolean exportCsv(List<Map<String, String>> maps, String name, Map<Integer, String> title) throws Exception {
CsvUtil csvUtil = new CsvUtil();
// 生成文件名
String nname = name.substring(0, name.lastIndexOf("."));
String fileName = this.getPath("out") + File.separator + nname + ".csv";
String[] headers = new String[title.size()];
for (Integer k : title.keySet()) {
headers[k] = title.get(k);
}
// 根据标题列处理map
List<LinkedHashMap<Integer, String>> rlist = new ArrayList<>();
maps.forEach(map -> {
LinkedHashMap<Integer, String> rmap = new LinkedHashMap<>();
for (Integer k : title.keySet()) {
String value = map.get(title.get(k));
rmap.put(k, value);
}
rlist.add(rmap);
});
boolean b = csvUtil.toWrite(fileName, headers, rlist);
if (b) {
log.info("文件输出完成！" + name);
}
return true;
}
}
CsvUtil 用于处理csv数据的工具类
package com.utils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.LinkedHashMap;
import java.util.List;
/**
*
* @author 小胖java
*
* 2023年4月4日
*/
public class CsvUtil {
private FileOutputStream file;
private OutputStreamWriter out;
private CSVPrinter csvPrinter;
public boolean toWrite(String f, String[] headers, List<LinkedHashMap<Integer, String>> ml) throws Exception {
try {
file = new FileOutputStream(f);
out = new OutputStreamWriter(file, "UTF-8");
CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(headers).withTrim();
csvPrinter = new CSVPrinter(out, csvFormat);
//写入数据
for (LinkedHashMap<Integer, String> m : ml) {
csvPrinter.printRecord(m.values());
}
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
} finally {
if (!(csvPrinter ==null)) {
csvPrinter.flush();
csvPrinter.close();
}
if (!(out ==null)) {
//out.flush();
out.close();
}
if (!(file ==null)) {
file.close();
}
}
}
}
LogUtil 用于记录日志
package com.utils;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.ConsoleHandler;
import java.util.logging.FileHandler;
import java.util.logging.Formatter;
import java.util.logging.Level;
import java.util.logging.LogRecord;
import java.util.logging.Logger;
/**
*
* @author 小胖java
*
* 2023年4月4日
*/
public class LogUtil {
// 正常的日期格式
public static final String DATE_PATTERN_FULL = "yyyy-MM-dd HH:mm:ss";
// 不带符号的日期格式，用来记录时间戳
public static final String DATE_PATTERN_NOMARK = "yyyyMMdd";
/**
* 为log设置等级
*
* @param log
* @param level
*/
public static void setLogLevel(Logger log, Level level) {
log.setLevel(level);
}
/**
* 为log添加控制台handler
*
* @param log
* 要添加handler的log
* @param level
* 控制台的输出等级
*/
public static void addConsoleHandler(Logger log, Level level) {
// 控制台输出的handler
ConsoleHandler consoleHandler = new ConsoleHandler();
// 设置控制台输出的等级（如果ConsoleHandler的等级高于或者等于log的level，则按照FileHandler的level输出到控制台，如果低于，则按照Log等级输出）
consoleHandler.setLevel(level);
// 添加控制台的handler
log.addHandler(consoleHandler);
}
/**
* 为log添加文件输出Handler
*
* @param log
* 要添加文件输出handler的log
* @param level
* log输出等级
* @param filePath
* 指定文件全路径
*/
public static void addFileHandler(Logger log, Level level, String filePath) {
FileHandler fileHandler = null;
try {
fileHandler = new FileHandler(filePath);
// 设置输出文件的等级（如果FileHandler的等级高于或者等于log的level，则按照FileHandler的level输出到文件，如果低于，则按照Log等级输出）
fileHandler.setLevel(level);
fileHandler.setFormatter(new Formatter() {
@Override
public String format(LogRecord record) {
// 设置文件输出格式
return "[ " + getCurrentDateStr(DATE_PATTERN_FULL) + " - Level:"
+ record.getLevel().getName().substring(0, 1) + " ]-" + "[" + record.getSourceClassName()
+ " -> " + record.getSourceMethodName() + "()] " + record.getMessage() + "\n";
}
});
} catch (SecurityException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
// 添加输出文件handler
log.addHandler(fileHandler);
}
/**
* 获取当前时间
*
* @return
*/
public static String getCurrentDateStr(String pattern) {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
return sdf.format(date);
}
}
PropertiesUtil 用来解析配置文件
package com.utils;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.net.URLDecoder;
import java.util.Properties;
/**
*
* @author 小胖java
*
* 2023年4月4日
*/
public class PropertiesUtil {
//获取配置文件内容
public void getProperties(Properties prop) throws IOException {
//1.获取jar包所在目录
String path=this.getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
path = path.substring(0, path.lastIndexOf("/"));
String decode = URLDecoder.decode(path, "UTF-8");
//2.获取app.properties文件内容
BufferedReader bufferedReader = new BufferedReader(new FileReader(decode + "/app.properties"));
prop.load(bufferedReader);
}
//获取jar包路径
public String getPath(){
return this.getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
}
}
总结
此代码可以实现将批量excel转换成utf-8格式的csv，经过测试80个xlsx文档，1千万条数据耗时大概30分钟。如下为工具包：
链接: 工具包
214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
