java 导出excel表

Java知识分享(spring全家桶、面试突击)

每天进步一点点

---

最近在做订单信息导出时，遇到一些问题在此分享一下：

1、导出表名乱码

new String(fileName.getBytes(), "iso8859-1");

2、导出表03与07之后

if (FILE_EXTENSION_XLS.equals(type)) {
			try (Workbook workbook = new HSSFWorkbook()) {
				doExcelExports(fileName, workbook, maps, maps2, list, list2, response);
			} catch (Exception e) {
				return false;
			}
		}
		
		if (FILE_EXTENSION_XLSX.equals(type)) {
			try (Workbook workbook = new XSSFWorkbook()) {
				doExcelExports(fileName, workbook, maps, maps2, list, list2, response);
			} catch (Exception e) {
				return false;
			}
		}

3、一张表有多个工作簿

			List<String> sheetNames = new ArrayList<String>();
			sheetNames.add("用户信息");
			sheetNames.add("用户拓展信息");
			
			Map<String, List<Map<String, Object>>> listMaps = new HashMap<String, List<Map<String,Object>>>();
			
			List<Map<String, Object>> listMaps1 = new ArrayList<Map<String,Object>>();
			Map<String, Object> map1 = new HashMap<String, Object>();
			map1.put("id", "001");
			map1.put("name", "张三");
			listMaps1.add(map1);
			Map<String, Object> map2 = new HashMap<String, Object>();
			map2.put("id", "002");
			map2.put("name", "李四");
			listMaps1.add(map2);
			listMaps.put("用户信息", listMaps1);
			
			List<Map<String, Object>> listMaps2 = new ArrayList<Map<String,Object>>();
			Map<String, Object> map21 = new HashMap<String, Object>();
			map21.put("age", 20);
			map21.put("wight", 98);
			listMaps2.add(map21);
			Map<String, Object> map22 = new HashMap<String, Object>();
			map22.put("age", 21);
			map22.put("wight", 97);
			listMaps2.add(map21);
			listMaps.put("用户拓展信息", listMaps2);
			
			
			Map<String, List<KeyValue2>> keyValueList = new HashMap<String, List<KeyValue2>>();
			List<KeyValue2> keyValueList1 = new ArrayList<KeyValue2>();
			keyValueList1.add(new KeyValue2("id", "编号"));
			keyValueList1.add(new KeyValue2("name", "姓名"));
			keyValueList.put("用户信息", keyValueList1);
			
			List<KeyValue2> keyValueList2 = new ArrayList<KeyValue2>();
			keyValueList2.add(new KeyValue2("age", "年龄"));
			keyValueList2.add(new KeyValue2("wight", "体重"));
			keyValueList.put("用户拓展信息", keyValueList2);
			
			ExportExcelUtils2.write2007(response, listMaps, keyValueList, ExportOrderInfoConstants.FILE_NAME, sheetNames);

KeyValue2.java

public class KeyValue2 {
	private String key;
	private String value;
	public String getKey() {
		return key;
	}
	
	public KeyValue2(){}
	
	public KeyValue2(String key, String value) {
		super();
		this.key = key;
		this.value = value;
	}
 
	public void setKey(String key) {
		this.key = key;
	}
	public String getValue() {
		return value;
	}
	public void setValue(String value) {
		this.value = value;
	}
}

ExportExcelUtils2.java

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcelUtils2 {
	/**
	 * 设置头信息
	 * 
	 * @param response
	 *            HttpServletResponse
	 * @param fileName
	 *            默认的文件名称
	 */
	public static void setExcelContentType(HttpServletResponse response,
			String fileName) {
		try {
			fileName = new String(fileName.getBytes(), "iso8859-1");
		} catch (UnsupportedEncodingException e) {
			// should no happen
		}
		response.reset();
		response.setContentType("application/msexcel;charset=utf-8");
		response.setHeader("Content-disposition", "attachment;filename= "
				+ fileName);

	}
	
	public static void write2007(HttpServletResponse response,
            Map<String, List<Map<String, Object>>> listMaps, Map<String, List<KeyValue2>> keyValueList,String title, List<String> sheetNames) {
        //设置导出的文件名，按当前时间设置
        setExcelContentType(response, title);
        //创建工作簿对象
        XSSFWorkbook wb = new XSSFWorkbook();
        OutputStream os = null;
        try {
        		//设置输出流对象
            os = response.getOutputStream();
           
            for (String sheetName : sheetNames) {
            	 //根据入参title创建Sheet对象
                XSSFSheet sheet = wb.createSheet(sheetName);// set sheet
                //创建Sheet对象的第一行
                XSSFRow rowtitle = sheet.createRow(0);
                //根据入参keyValueList设置表头
                for (int i = 0; i < keyValueList.size(); i++) { // set header title
                    XSSFCell cell = rowtitle.createCell(i);
                    cell.setCellValue(keyValueList.get(sheetName).get(i).getValue());
                }
                //从第二行开始按照表头创建行对象
                for (int i = 1; i <= listMaps.size(); i++) { // set value
                    Map<String, Object> map = listMaps.get(sheetName).get(i - 1);
                    XSSFRow rowValue = sheet.createRow(i);
                    for (int j = 0; j < keyValueList.size(); j++) {
                        KeyValue2 keyValue = keyValueList.get(sheetName).get(j);
                        XSSFCell cell = rowValue.createCell(j);
                        cell.setCellValue((map.get(keyValue.getKey()) != null) ? map.get(
                            keyValue.getKey()).toString() : "");
                        
                    }
                }
			}
            
            //写入输出流对象，导出
            wb.write(os);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                wb.close();
                os.close();
            } catch (IOException ie) {
                ie.printStackTrace();
            }catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
    }

}