现在很多系统都有导出Excel的功能,但是Excel导出涉及到很多的问题,例如兼容性和效率性能方面的,本人提倡导出CSV文件,CSV是一个文本文件,但是可以被办公软件(WPS,Office)识别的表格文件,好了直接上正题!
方法一:
public File createCSVFile(List exportData, LinkedHashMap map, String outPutPath, String fileName) {
File csvFile = null;
BufferedWriter csvFileOutputStream = null;
try {
File file = new File(outPutPath);
if (!file.exists()) {
file.mkdirs();
}
// 定义文件名格式并创建
csvFile = new File(outPutPath + fileName + ".csv");
file.createNewFile();
// UTF-8使正确读取分隔符","
// 如果生产文件乱码,windows下用gbk,linux用UTF-8
csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"),1024);
// 写入前段字节流,防止乱码
csvFileOutputStream.write(getBOM());
// 写入文件头部
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
csvFileOutputStream
.write((String) propertyEntry.getValue() != null ? (String) propertyEntry.getValue() : "");
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
csvFileOutputStream.newLine();
// 写入文件内容
for (Iterator iterator = exportData.iterator(); iterator.hasNext();) {
Object row = (Object) iterator.next();
for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
String str = row != null ? ((String) ((Map) row).get(propertyEntry.getKey())) : "";
if (StringUtils.isEmpty(str)) {
str = "";
} else {
str = str.replaceAll("\"", "\"\"");
if (str.indexOf(",") >= 0) {
str = "\"" + str + "\"";
}
}
csvFileOutputStream.write(str);
if (propertyIterator.hasNext()) {
csvFileOutputStream.write(",");
}
}
if (iterator.hasNext()) {
csvFileOutputStream.newLine();
}
}
csvFileOutputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
csvFileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return csvFile;
}
如何使用?请看:
private void excel(List<Map<String, Object>> list) {
List exportData = new ArrayList<Map>();
LinkedHashMap map=new LinkedHashMap<>();
Map row=null;
map.put("1", "客户姓名");
map.put("2", "手机号");
map.put("3", "性别");
map.put("4", "出生年月日");
map.put("5", "交易号");
map.put("6", "交易渠道");
map.put("7", "历史申购总额");
map.put("8", "所剩现值");
map.put("9", "最后登录时间");
for (int i = 0; i < list.size(); i++) {
row = new LinkedHashMap<String, String>();
Map<String, Object> obj = list.get(i);
row.put("1", getString(obj.get("name")));
row.put("2", getString(obj.get("mobile")));
row.put("3", getString(obj.get("sex")));
row.put("4", getString(obj.get("birthday")));
row.put("5", getString("\t"+obj.get("tradeacco")));//此处避免出现科学计数法
row.put("6", getString(obj.get("channelno")));
row.put("7", getString(obj.get("buy")));
row.put("8", getString(obj.get("money")));
row.put("9", getString(obj.get("time")));
exportData.add(row);
}
String path = "E:/";
String fileName = "客户信息表" + System.currentTimeMillis();
//文件名=生产的文件名称+时间戳
File file = createCSVFile(exportData, map, path, fileName);
String fileName2 = file.getName();
System.out.println("文件名称:" + fileName2);
}
private static String getString(Object obj) {
if (obj == null) {
return null;
} else {
return obj.toString();
}
}
public static String getBOM() {
byte b[] = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
return new String(b);
}
这个没有用到org.apache.commons.csv包,是手动拼接的,请看用apache插件的例子
方法二:
/***
* 通过Apache工具类将list集合生成CSV文件
* @param list
* @throws Exception
*/
public void listWriteToCSVLocal(List<Map<String, Object>> list) throws Exception{
String path = "E:/";
String fileName = "1.csv";
FileOutputStream fos = new FileOutputStream(path+fileName);
OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");
CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader( "客户姓名", "手机号", "性别", "出生年月日", "交易号", "交易渠道", "历史申购总额", "所剩现值", "最后登录时间" );
CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
for (int i = 0; i < list.size(); i++) {
Map<String, Object> obj = list.get(i);
csvPrinter.printRecord
( getString(obj.get("name"))
,getString(obj.get("mobile"))
,getString(obj.get("sex"))
,getString(obj.get("birthday"))
,getString("\t"+obj.get("tradeacco"))
,getString(obj.get("channelno"))
,getString(obj.get("buy"))
,getString(obj.get("money"))
,getString(obj.get("time"))
);
}
csvPrinter.flush();
csvPrinter.close();
}
如何读取CSV呢?请看:
/**
* 读取csv
* @param path
* @throws Exception
*/
public void ReadCSV(String path) throws Exception {
File file=new File(path);
if(!file.exists()){
file.createNewFile();
}
InputStream is = new FileInputStream(path);
InputStreamReader isr = new InputStreamReader(is, "GBK");
Reader reader = new BufferedReader(isr);
CSVParser parser = CSVFormat.EXCEL.withHeader( "name", "mobile", "sex", "birthday", "tradeacco", "channelno", "buy", "money", "time" ).parse(reader);
List<CSVRecord> list = parser.getRecords();
BigDecimal buyToatol=new BigDecimal(0.00);
for (int i=1;i<list.size();i++) {//注意:第一行是表格标题,所以从第二行开始读取
Map map=list.get(i).toMap();//转换成Map集合后可以用key读取表格的内容
Object buy=map.get("buy");//这里读取金额,读取其它的内容要用不同的key来获取
if(buy !=null && !"".equals(buy.toString().trim())){
System.out.println("buy="+buy);
buyToatol=buyToatol.add(new BigDecimal(buy.toString().trim()));
}
}
parser.close();
System.out.println("总申购金额:"+buyToatol);
parser.close();
}
我们将json文件转换成list,然后再导出CSV:
/***
* 将json文件转换成List集合,然后将list集合转换成CSV文件存储在本地
*/
private void jsonToCSVLocal(){
List<Map<String, Object>> list=new ArrayList<>();
File file=new File("e://5.json");
String jsonString;
try {
jsonString = FileUtils.readFileToString(file);
com.alibaba.fastjson.JSONArray jsonArray = com.alibaba.fastjson.JSONObject.parseArray(jsonString);
for(int i=0;i<jsonArray.size();i++){
com.alibaba.fastjson.JSONObject jo = (com.alibaba.fastjson.JSONObject) jsonArray.get(i);
System.out.println(jo);
list.add(jo);
}
listWriteToCSVLocal(list);
} catch (Exception e) {
e.printStackTrace();
}
}