package com.suning.gcpm.admin.util;
import com.suning.framework.lang2.SnfLogger;
import com.suning.framework.lang2.SnfLoggerFactory;
import jxl.CellView;
import jxl.Workbook;
import jxl.write.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class CsvWriter {
/** CSV文件列分隔符 */
private static final String CSV_COLUMN_SEPARATOR = ",";
/** CSV文件列分隔符 */
private static final String CSV_RN = "\r\n";
protected static SnfLogger logger = SnfLoggerFactory.getLogger(CsvWriter.class);
private CsvWriter() {
}
/**
*
* 将检索数据输出的对应的csv列中
* */
public static String formatCsvData(List<Map<String, Object>> data, String displayColNames, String matchColNames) {
StringBuilder buf = new StringBuilder();
String[] displayColNamesArr = null;
String[] matchColNamesMapArr = null;
displayColNamesArr = displayColNames.split(",");
matchColNamesMapArr = matchColNames.split(",");
// 输出列头
for (int i = 0; i < displayColNamesArr.length; i++) {
buf.append(displayColNamesArr[i]).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_RN);
if (null != data) {
// 输出数据
for (int i = 0; i < data.size(); i++) {
for (int j = 0; j < matchColNamesMapArr.length; j++) {
buf.append(data.get(i).get(matchColNamesMapArr[j])).append(CSV_COLUMN_SEPARATOR);
}
buf.append(CSV_RN);
}
}
return buf.toString();
}
public static String getImportErrorInfo(List<Map<String, Object>> failList, int size) {
// 存放导入csv的返回信息
StringBuilder buf = new StringBuilder();
buf.append("成功" + size + "条," + "失败" + failList.size() + "条").append(CSV_RN);
for (Map<String, Object> errorInfo : failList) {
// 行号记录
buf.append("第" + errorInfo.get("errorRow") + "行").append(CSV_COLUMN_SEPARATOR);
// 如果检索到errorRow的值为"false",说明csv文件记录大于5000,清空StringBuffer,更改提示信息
if ("false".equals(errorInfo.get("errorRow"))) {
buf.setLength(0);
buf.append(errorInfo.get("errorMsg")).append(CSV_RN);
break;
}
// 对应行错误信息
buf.append(errorInfo.get("errorMsg")).append(CSV_RN);
}
return buf.toString();
}
public static void exportCsv(String fileName, String content, HttpServletResponse response) throws IOException {
String fn = fileName;
// 读取字符编码
String csvEncoding = "UTF-8";
// 设置响应
response.setCharacterEncoding(csvEncoding);
response.setContentType("text/csv; charset=" + csvEncoding);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fn.getBytes(), csvEncoding));
// 写出响应
OutputStream os = response.getOutputStream();
os.write(content.getBytes("GBK"));
os.flush();
os.close();
}
//excel导出后台管理页面的方法
public static boolean exportCsv(String fileName, List<Map<String, Object>> results, List<String> csvHeaders,
HttpServletResponse response) throws IOException {
String fn = fileName;
// 读取字符编码
String csvEncoding = "UTF-8";
// 设置响应
response.reset();
response.setCharacterEncoding(csvEncoding);
response.setContentType("text/csv; charset=" + csvEncoding);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=30");
response.setHeader("Content-Disposition", "attachment; filename=" + new String(fn.getBytes(), csvEncoding));
// 写出响应
OutputStream os = response.getOutputStream();
WritableWorkbook wwb = null;
WritableSheet ws = null;
try {
wwb = Workbook.createWorkbook(os);
ws = wwb.createSheet("sheet1", 0);
// 创建表头
// WritableFont wfc = new
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE);
WritableCellFormat wcfFC = new WritableCellFormat(NumberFormats.TEXT);
CellView cv = new CellView();
cv.setAutosize(true);
Label headLabel = null;
for (int i = 0; i < csvHeaders.size(); i++) {
ws.setColumnView(0, cv); // 自动列宽
headLabel = new Label(i, 0, (String) csvHeaders.get(i), wcfFC);
ws.addCell(headLabel);
}
Label contentLabel = null;
Set<Map.Entry<String, Object>> s = null;
Iterator<Map.Entry<String, Object>> iter = null;
int j = 0;
for (int i = 1; i <= results.size(); i++) {
s = results.get(i - 1).entrySet();
iter = s.iterator();
j = 0;
while (iter.hasNext()) {
Map.Entry<String, Object> o = (Map.Entry<String, Object>) iter.next();
String str = "";
if(o.getValue()==null){
str = "";
}else if(o instanceof Timestamp){
str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Timestamp) o.getValue());
} else {
str = String.valueOf(o.getValue());
}
contentLabel = new Label(j, i, str);
ws.addCell(contentLabel);
j++;
}
}
} catch (Exception e) {
logger.logException(e);
return false;
} finally {
try {
if (wwb != null) {
wwb.write();
wwb.close();
}
if (os != null) {
os.flush(); //liting
os.close();
}
} catch (WriteException e) {
logger.logException(e);
} catch (IOException e) {
logger.logException(e);
}
}
return true;
}
}