UTF-8的CSV文件用Excel打开会出现乱码

arenzhj

浏览: 67322 次
性别:
来自: 上海

最近访客更多访客>>

redsnower

ahuiii

以太_cpp

bigdesert

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Java-POI

UTF-8的CSV文件用Excel打开会出现乱码

原因：

输出的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();
	}
	
}

分享到：