原因:
输出的CSV文件中没有BOM
什么是BOM?
在UCS 编码中有一个叫做”ZERO WIDTH NO-BREAK SPACE”的字符,它的编码是FEFF。而FFFE在UCS中是不存在的字符,所以不应该出现在实际传输中。UCS规范建议我们在传输字节流前,先传输字符”ZERO WIDTH NO-BREAK SPACE”。这样如果接收者收到FEFF,就表明这个字节流是Big-Endian的;如果收到FFFE,就表明这个字节流是Little- Endian的。因此字符”ZERO WIDTH NO-BREAK SPACE”又被称作BOM。
UTF-8不需要BOM来表明字节顺序,但可以用BOM来表明编码方式。字符”ZERO WIDTH NO-BREAK SPACE”的UTF-8编码是EF BB BF。所以如果接收者收到以EF BB BF开头的字节流,就知道这是UTF-8编码了。
解决方案:
1、Excel通过“数据”->“来自文本文件”打开csv文件后,设置文件编码为utf-8
2、改变编码为UTF-16LE,添加\uFEFF
参照代码:
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
/**
* 将数据导出到指定格式文件的工具类.
* @author arenzhj
*
*/
public class DataExportFileUtil{
//CSV config
public static final String CSV_FILE = ".csv";
public static final String CSV_TYPE = "text/plain";
public static final String CSV_ENCODING = "UTF-16LE";
public static final String CSV_BOMENCODING = "\uFEFF";
//Excel config
public static final String EXCEL_TYPE = "application/vnd.ms-excel";
public static final String EXCEL_FILE = ".xls";
public static final String EXCEL_ENCODING = "UTF-8";
//Excel Styles
public static final String STYLE_HEADER = "HEADER";
public static final String STYLE_BORDER = "BORDER";
public static final String STYLE_DATECELL = "DATECELL";
public static final String STYLE_NUMBERCELL = "NUMBERCELL";
public static final String ENTER = "\r\n";
/**
* 导出csv文件
* @param fileName
* @param rowMapper
* @param modelMaps
* @param response
* @throws Exception
*/
public static void exportDataCSVFile(String fileName,
LinkedHashMap rowMapper,
List<Map<String, String>> modelMaps,
HttpServletResponse response) {
ServletOutputStream out =null;
BufferedOutputStream buff =null;
try {
out = response.getOutputStream();
buff = new BufferedOutputStream(out);
StringBuffer write = new StringBuffer();
// 写入文件头部
for (Iterator propertyIterator = rowMapper.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator
.next();
write.append("\""
+ propertyEntry.getValue().toString() + "\"");
if (propertyIterator.hasNext()) {
write.append(",");
}
}
write.append(ENTER);
// 写入文件内容
for (Iterator iterator = modelMaps.iterator(); iterator.hasNext();) {
Map<String, String> modelMap =(Map<String, String>)iterator.next();
for (Iterator propertyIterator = rowMapper.entrySet().iterator(); propertyIterator.hasNext();) {
java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();
write.append("\""
+ modelMap.get(propertyEntry.getKey())
+ "\"");
if (propertyIterator.hasNext()) {
write.append(",");
}
}
if (iterator.hasNext()) {
write.append(ENTER);
}
}
//输出Excel文件.
response.setContentType(CSV_TYPE+";charset="+CSV_ENCODING);
//中文文件名支持
String encodedfileName = new String(fileName.getBytes(CSV_ENCODING), CSV_ENCODING);
response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedfileName +CSV_FILE + "\"");
buff.write((CSV_BOMENCODING+write.toString()).getBytes(CSV_ENCODING));
buff.flush();
buff.close();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
buff.close();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 导出Excel(含多个Sheet)
* @param fileName 导出Excel文件名称
* @param sheetNames 导出Excel的Sheet名
* @param rowMappers 各Excel数据标题
* @param modelMaps 各Excel数据集
* @param response
* @throws Exception
*/
public static void exportDataExcelFile(String fileName,
LinkedHashMap sheetNames,
Map<String,LinkedHashMap> rowMappers,
Map<String,List<Map<String, String>>> modelMaps,
HttpServletResponse response)throws Exception{
HSSFWorkbook workbook = new HSSFWorkbook();
//创建所有Cell Style
Map<String, HSSFCellStyle> styles = createStyles(workbook);
int sheetNum=0;
for (Iterator sheetNameIterator = sheetNames.entrySet().iterator(); sheetNameIterator.hasNext();) {
Entry sheetNameEntry = (Entry) sheetNameIterator.next();
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetNameEntry.getValue().toString(),HSSFWorkbook.ENCODING_UTF_16);
LinkedHashMap rowMapper =rowMappers.get(sheetNameEntry.getKey());
HSSFRow row;
int rowIndex = 0;
short colIndex = 0;
row=sheet.createRow(rowIndex);
// 写入文件头部
for (Iterator headerIterator = rowMapper.entrySet().iterator(); headerIterator.hasNext();) {
Entry headerEntry = (Entry) headerIterator.next();
HSSFCell cell = row.createCell(colIndex++);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(headerEntry.getValue().toString());
cell.setCellStyle(styles.get(STYLE_HEADER)); // 设置该cell浮点数的显示格式
colIndex++;
}
rowIndex++;
// 写入内容部分
List<Map<String, String>> models=modelMaps.get(sheetNameEntry.getKey());
if(!models.isEmpty()){
for (Map<String, String> modelMap : models) {
row=sheet.createRow(rowIndex);
colIndex = 0;
for (Iterator headerIterator = rowMapper.entrySet().iterator(); headerIterator.hasNext();) {
Entry headerEntry = (Entry) headerIterator.next();
HSSFCell cell = row.createCell(colIndex++);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(modelMap.get(headerEntry.getKey()));
cell.setCellStyle(styles.get(STYLE_BORDER)); // 设置该cell浮点数的显示格式
colIndex++;
}
rowIndex++;
}
}
sheetNum++;
}
//输出excel文件
responseExcel(response,workbook,fileName);
}
private static Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb) {
Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();
//普通字体
HSSFFont normalFont = wb.createFont();
normalFont.setFontHeightInPoints((short) 10);
//加粗字体
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//蓝色加粗字体
HSSFFont blueBoldFont = wb.createFont();
blueBoldFont.setFontHeightInPoints((short) 10);
blueBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
blueBoldFont.setColor(HSSFColor.BLUE.index);
//行标题格式
HSSFCellStyle headerStyle = wb.createCellStyle();
headerStyle.setFont(boldFont);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
palette.setColorAtIndex((short)9, (byte) (0xff & 200), (byte) (0xff & 200), (byte) (0xff & 200));
headerStyle.setFillForegroundColor((short)9);
headerStyle.setAlignment(headerStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(headerStyle.VERTICAL_CENTER);
setBorder(headerStyle);
styles.put(STYLE_HEADER, headerStyle);
//border
HSSFCellStyle borderCellStyle = wb.createCellStyle();
setBorder(borderCellStyle);
styles.put(STYLE_BORDER, borderCellStyle);
HSSFDataFormat df = wb.createDataFormat();
//日期格式
HSSFCellStyle dateCellStyle = wb.createCellStyle();
dateCellStyle.setFont(normalFont);
dateCellStyle.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm:ss"));
setBorder(dateCellStyle);
styles.put(STYLE_DATECELL, dateCellStyle);
//数字格式
HSSFCellStyle numberCellStyle = wb.createCellStyle();
numberCellStyle.setFont(normalFont);
numberCellStyle.setDataFormat(df.getFormat("#,##0.00"));
setBorder(numberCellStyle);
styles.put(STYLE_NUMBERCELL, numberCellStyle);
return styles;
}
private static void setBorder(HSSFCellStyle style) {
//设置边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
}
public static void responseExcel(HttpServletResponse response,
HSSFWorkbook workbook ,String fileName) throws Exception {
//输出Excel文件.
response.setContentType(EXCEL_TYPE+";charset="+EXCEL_ENCODING);
//中文文件名支持
String encodedfileName = new String(fileName.getBytes(EXCEL_ENCODING), EXCEL_ENCODING);
response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedfileName +EXCEL_FILE + "\"");
workbook.write(response.getOutputStream());
response.getOutputStream().flush();
}
}