读写csv xlsx文件的工具类

微信搜索：“二十同学” 公众号，欢迎关注一条不一样的成长之路

Java对csv文件和xlsx文件进行读写的工具类，简单易用，代码如下：

需要引用的依赖

<dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.49</version></dependency><dependency><groupId>net.sourceforge.javacsv</groupId><artifactId>javacsv</artifactId><version>2.0</version></dependency><dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper</artifactId><version>5.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency><!--读取大批数据--><dependency><groupId>xerces</groupId><artifactId>xercesImpl</artifactId><version>2.11.0</version></dependency>

工具类：

package com.example.demo;import com.alibaba.fastjson.JSON;import com.csvreader.CsvReader;import com.csvreader.CsvWriter;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.util.StringUtils;import javax.servlet.http.HttpServletResponse;import java.io.*;import java.nio.charset.Charset;import java.text.DecimalFormat;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.*;import java.util.stream.Collectors;public class FileUtils {private static final Logger logger = LoggerFactory.getLogger(FileUtils.class);//- 版本的excelprivate final static String excelL ="xls";//+ 版本的excelprivate final static String excelU ="xlsx";private final static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");public void writeCSV(String csvFilePath, List<KeyLabel> exportKeys, List<Map<String, String>> dataList) {try {// 创建CSV写对象 例如:CsvWriter(文件路径，分隔符，编码格式);CsvWriter csvWriter = new CsvWriter(csvFilePath, ',', Charset.forName("Utf-8"));// 写内容String[] headers = exportKeys.stream().map(a -> a.getLabel()).collect(Collectors.toList()).toArray(new String[exportKeys.size()]);String[] keys = exportKeys.stream().map(a -> a.getKey()).collect(Collectors.toList()).toArray(new String[exportKeys.size()]);csvWriter.writeRecord(headers);for (Map<String, String> map : dataList) {String[] writeLine = new String[keys.length];for (int j = 0; j < keys.length; j++) {if ("updateTime".equals(keys[j])) {if (map.get(keys[j]) == null || "".equals(map.get(keys[j]))) {writeLine[j] = "";} else {SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);Date date = sdf.parse(String.valueOf(map.get(keys[j])));writeLine[j] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}} else {writeLine[j] = map.get(keys[j]);}}csvWriter.writeRecord(writeLine);}csvWriter.close();} catch (IOException e) {e.printStackTrace();} catch (ParseException e) {e.printStackTrace();}}public List<Map> readCSV(String filePath) {List<Map> resultInfo = new ArrayList<>();try {// 创建CSV读对象CsvReader csvReader = new CsvReader(filePath, ',', Charset.forName("Utf-8"));// 读表头csvReader.readHeaders();// 获取表头String[] headers = csvReader.getHeaders();String[] fields = csvReader.getHeaders();StringBuilder sb = new StringBuilder();sb.append("[");while (csvReader.readRecord()) {sb.append("{");for (int i = 0; i < fields.length; i++) {sb.append("\"").append(fields[i]).append("\":\"").append(csvReader.get(headers[i])).append("\"");if (i < fields.length - 1) {sb.append(",");}}sb.append("},");}if (sb.lastIndexOf(",") > 0) {sb.deleteCharAt(sb.lastIndexOf(",")).append("]");}resultInfo = JSON.parseArray(sb.toString(), Map.class);} catch (IOException e) {e.printStackTrace();}return resultInfo;}/*** 生成excel文件* @param fileFullName 保存文件的地址（eg C:\xsls\abc.xls）* @param titles 文件头* @param values 需要写入的数据 Map<rowNo, <colNo, String>>* @return* @throws IOException*/public void saveExcel(String fileFullName, List<String> titles, Map<Integer, Map<Integer, String>> values) throws IOException {Workbook workbook = generateWorkbook(StringUtils.getFilenameExtension(fileFullName), titles, values);File file = new File(fileFullName);if (!file.exists()){file.createNewFile();}FileOutputStream fileOutputStream = new FileOutputStream(file);workbook.write(fileOutputStream);fileOutputStream.flush();}/*** 指定header名和map格式数据，生成Workbook* @param fileExtension* @param titles* @param values* @return* @throws IOException*/public static Workbook generateWorkbook(String fileExtension, List<String> titles, Map<Integer, Map<Integer, String>> values) throws IOException {Workbook workbook = createWorkBook(fileExtension);// 生成一个sheet表格Sheet sheet = workbook.createSheet();// 设置表格默认列宽度为15个字节sheet.setDefaultColumnWidth((short) 15);// 创建标题行Row row = sheet.createRow(0);for (int i = 0; i < titles.size(); i++) {row.createCell(i).setCellValue(titles.get(i));}//标题行数int headRow = 1;//写入正文for (Integer key : values.keySet()) {row = sheet.createRow(headRow + key);Map<Integer, String> keySet = values.get(key);for (int j = 0; j < keySet.size(); j++) {Cell cell = row.createCell(j);cell.setCellValue(keySet.get(j));}}return workbook;}/*** 读取Excel为Json字符串，客户端反序列化即可得到List* @param inputStream* @param filenameExtension* @param headerMapper 【列序号】和【Json的Key名】组成Key-Value* @return* @throws Exception*/public static String getJson(InputStream inputStream, String filenameExtension, Map<Integer, String> headerMapper) throws Exception {Workbook wb = createWorkBook(filenameExtension ,inputStream);Sheet sheetAt = wb.getSheetAt(0);int lastRowNum = sheetAt.getLastRowNum();StringBuilder sb = new StringBuilder();sb.append("[");for (int i = 0; i <= lastRowNum; i++) {Row row = sheetAt.getRow(i);if (i == 0) {continue;}sb.append("{");int physicalNumberOfCells = row.getPhysicalNumberOfCells();for (int j = 0; j < physicalNumberOfCells; j++) {Object value = getCellValue(row.getCell(j));sb.append("\"").append(headerMapper.get(j)).append("\":\"").append(value).append("\"");if (j < physicalNumberOfCells - 1) {sb.append(",");}}sb.append("}");if (i < lastRowNum) {sb.append(",");}}sb.append("]");return sb.toString();}/*** 根据inputStream创建一个Workbook* 10W一下读取* @param fileType* @param inputStream* @return* @throws Exception*/private static Workbook createWorkBook(String fileType, InputStream inputStream) throws Exception {Workbook wb;if (excelL.equals(fileType)) {//-wb = new HSSFWorkbook(inputStream);} else if (excelU.equals(fileType)) {//+wb = new XSSFWorkbook(inputStream);} else {throw new Exception("解析的文件格式有误！");}return wb;}/*** 创建一个Workbook* @param fileType* @return* @throws Exception*/private static Workbook createWorkBook(String fileType) throws IOException {Workbook wb;if (excelL.equals(fileType)) {//-wb = new HSSFWorkbook();} else if (excelU.equals(fileType)) {//+ 内存留存数据wb = new SXSSFWorkbook(10000);} else {throw new IOException("解析的文件格式有误！");}return wb;}/*** 读取Excel，返回Map* @param inputStream* @param filenameExtension* @param columnNos* @return* @throws Exception*/public Map<Integer,Map<Integer,Object>> getMapFromWorkBook(InputStream inputStream, String filenameExtension, List<Integer> columnNos) throws Exception {//Map<rowNo, Map<columnNo, columnValue>>Map<Integer, Map<Integer, Object>> sheetMap = new HashMap<>();Workbook wb = createWorkBook(filenameExtension ,inputStream);Sheet sheetAt = wb.getSheetAt(0);int lastRowNum = sheetAt.getLastRowNum();for (int i = 0; i <= lastRowNum; i++) {Row row = sheetAt.getRow(i);if(i == 0) {//skip headercontinue;}Map<Integer, Object> columnValMap = new HashMap<>();//按列展开for (Integer index : columnNos) {Object value = getCellValue(row.getCell(index));columnValMap.put(index, value);}sheetMap.put(i,columnValMap);}return sheetMap;}/*** 获取cell值* @param cell* @return*/public static Object getCellValue(Cell cell) {Object value = "";if (cell != null){switch (cell.getCellType()) {case Cell.CELL_TYPE_BLANK:value = "";break;case Cell.CELL_TYPE_BOOLEAN:value = cell.getBooleanCellValue();break;case Cell.CELL_TYPE_ERROR:case Cell.CELL_TYPE_FORMULA:break;case Cell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));break;} else {value = new DecimalFormat("0").format(cell.getNumericCellValue());}break;case Cell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;}}return value;}/*** 文本写入txt* @param file* @param infos*/public void writeTxt(File file,List<String> infos){try {FileOutputStream fos = new FileOutputStream(file);OutputStreamWriter osw=new OutputStreamWriter(fos, "UTF-8");BufferedWriter bw=new BufferedWriter(osw);for (String info:infos){bw.write(info+"\t\n");}bw.close();osw.close();fos.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (UnsupportedEncodingException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}public HttpServletResponse downloadFile(File file, HttpServletResponse response) {if (file.exists() == false) {logger.info("待下载的文件：" + file + "不存在.");} else {try {// 以流的形式下载文件。InputStream fis = new BufferedInputStream(new FileInputStream(file.getPath()));byte[] buffer = new byte[fis.available()];fis.read(buffer);fis.close();// 清空responseresponse.reset();OutputStream toClient = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/octet-stream");// 如果输出的是中文名的文件，在此处就要用URLEncoder.encode方法进行处理response.setHeader("Content-Disposition","attachment;filename=" + new String(file.getName().getBytes("GB2312"), "ISO8859-1"));toClient.write(buffer);toClient.flush();toClient.close();} catch (Exception ex) {ex.printStackTrace();} finally {try {File f = new File(file.getPath());f.delete();} catch (Exception e) {e.printStackTrace();}}}return response;}/*** 对字符串里的中文符号转换为英文括号* @param msg* @return*/public String converBracket(String msg){if (msg != null || !"".equals(msg)){msg = msg.replaceAll("（", "(").replaceAll("）", ")");}return msg;}}

package com.example.demo;public class KeyLabel {//字段private String key;//字段名private String label;public String getKey() {return key;}public void setKey(String key) {this.key = key;}public String getLabel() {return label;}public void setLabel(String label) {this.label = label;}}