POI操作Excel工具类 java实现

为什么80%的码农都做不了架构师？>>>   

package com.test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

// 依赖jar(poi-3.8.jar,poi-ooxml-3.8.jar,poi-ooxml-schemas-3.8.jar,xml-apis-1.0.b2.jar,xmlbeans-2.3.0.jar,dom4j-1.6.1.jar)
public class ExcelCase {
	
	final static int memoryDataCount = 7000;
	
	static String GetXlsxPath(String path){
		if(path.endsWith(".xls")){
			path = path.substring(0,path.length()-4)+".xlsx";
		}
		if(path.indexOf(".") <= 0){
			path +=".xlsx";
		}else{
			if(!(path.endsWith(".xls")
					||path.endsWith(".xlsx"))){
				path +=".xlsx";
			}
		}
		return path;
	}

	/**
	 * 
	* @Title: expSheetsResponse 
	* @Description: 导出多个工作表用Response返回(文件名，编码等请在调用前设置)
	* @param @param resp
	* @param @param sheets  参数说明
	* 多个工作薄每个需设置
	* headerZh("显示的表头"),
	* headers("取值时的字段名"),
	* sheetName(每个工作薄的名称),
	* sheetData(每个工作薄的数据List<Map<String, Object>>类型)
	* @return void    返回类型 
	* @throws
	 */
	public static void expSheetsResponse(HttpServletResponse resp,
			List<Map<String, Object>> sheets) {
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(memoryDataCount);
			workbook.setCompressTempFiles(true);
			int sheetCount = sheets.size();
			for (int a = 0; a < sheetCount; a++) {
				Map<String, Object> objMap = sheets.get(a);
				if(objMap == null){
					continue;
				}
				String[] headerZh = (String[])objMap.get("headerZh");
				String[] headers = (String[])objMap.get("headers");
				String sheetName = (String)objMap.get("sheetName");
				@SuppressWarnings("unchecked")
				List<Map<String, Object>> sheetData = (List<Map<String, Object>>)sheets.get(a).get("sheetData");
				Sheet sheet = workbook.createSheet(sheetName);
				expSheet(sheet, resp.getOutputStream(),headerZh,headers,sheetData);
			}
			mergeWorkBookOutputStream(workbook, resp.getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void expSheetResponse(HttpServletResponse resp,
			String[] headerZh, String[] headers,
			List<Map<String, Object>> listRows) {
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(memoryDataCount);
			workbook.setCompressTempFiles(true);
			Sheet sheet = workbook.createSheet();
			expSheet(sheet, resp.getOutputStream(), headerZh, headers,
					listRows);
			mergeWorkBookOutputStream(workbook, resp.getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void expSheetResponse(HttpServletResponse resp,
			String sheetName, String[] headerZh, String[] headers,
			List<Map<String, Object>> listRows) {
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(memoryDataCount);
			workbook.setCompressTempFiles(true);
			Sheet sheet = workbook.createSheet(sheetName);
			expSheet(sheet,resp.getOutputStream(), headerZh,
					headers, listRows);
			mergeWorkBookOutputStream(workbook, resp.getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void expSheetsFile(String path,List<Map<String, Object>> sheets) {
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(memoryDataCount);
			workbook.setCompressTempFiles(true);
			FileOutputStream fout = new FileOutputStream(path);
			int sheetCount = sheets.size();
			for (int a = 0; a < sheetCount; a++) {
				Object obj = sheets.get(a);
				if(obj == null){
					continue;
				}
				String[] headerZh = (String[])sheets.get(a).get("headerZh");
				String[] headers = (String[])sheets.get(a).get("headers");
				String sheetName = (String)sheets.get(a).get("sheetName");
				@SuppressWarnings("unchecked")
				List<Map<String, Object>> sheetData = (List<Map<String, Object>>)sheets.get(a).get("sheetData");
				Sheet sheet = workbook.createSheet(sheetName);
				expSheet(sheet, fout, headerZh, headers, sheetData);
			}
			System.out.println(workbook.getNumberOfSheets());
			mergeWorkBookOutputStream(workbook, fout);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void expSheetFile(String path, String[] headerZh,
			String[] headers, List<Map<String, Object>> listRows) {
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(memoryDataCount);
			workbook.setCompressTempFiles(true);
			FileOutputStream fout = new FileOutputStream(GetXlsxPath(path));
			Sheet sheet = workbook.createSheet();
			expSheet(sheet, fout, headerZh, headers, listRows);
			mergeWorkBookOutputStream(workbook, fout);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void expSheetFile(String path, String sheetName,
			String[] headerZh, String[] headers,
			List<Map<String, Object>> listRows) {
		try {
			Workbook hworkbook = new SXSSFWorkbook(memoryDataCount);
			FileOutputStream fout = new FileOutputStream(path);
			Sheet sheet = hworkbook.createSheet(sheetName);
			expSheet(sheet, fout, headerZh, headers, listRows);
			mergeWorkBookOutputStream(hworkbook, fout);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	static void mergeWorkBookOutputStream(Workbook workbook,
			OutputStream fout) {
		try {
			workbook.write(fout);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	static void expSheet(Sheet sheet, OutputStream fout,String[] headerZh, String[] headers,
			List<Map<String, Object>> listRows) {
		Row rowHeader = sheet.createRow(0);
		int headerSize = headerZh.length;
		for (int a = 0; a < headerSize; a++) {
			Cell cell = rowHeader.createCell(a);
			cell.setCellValue(headerZh[a]);
			sheet.setColumnWidth(a, headerZh[a].getBytes().length * 2 * 256);
		}
		int i = 1;
		for (Map<String, Object> row : listRows) {
			Row rowData = sheet.createRow(i);
			i += 1;
			for (int a = 0; a < headerSize; a++) {
				Cell cell = rowData.createCell(a);
				String value = "";
				if (row.get(headers[a]) != null) {
					value = row.get(headers[a]).toString();
				}
				cell.setCellValue(value);
			}
		}
	}
	
	static void testPath(){
		GetXlsxPath("d:\\a.xls");
		GetXlsxPath("d:\\b.xlsx");
		GetXlsxPath("d:\\c.xl");
		GetXlsxPath("d:\\d");
		System.out.println("结束单工作表导出");
	}

	public static void main(String[] args) {
		//testPath();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		for (int b = 0; b < 800000; b++) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int a = 0; a < 11; a++) {
				map.put("field" + a, "第" + (b + 1) + "行,第" + (a + 1) + "列");
			}
			list.add(map);
		}
		String[] headerZh = { "字段1", "字段2", "字段3", "字段4", "字段5", "字段6", "字段7",
				"字段8", "字段9", "字段10", "字段11" };
		String[] headers = { "field0", "field1", "field2", "field3", "field4",
				"field5", "field6", "field7", "field8", "field9", "field10" };
		expSheetFile("d:\\aa.xls", headerZh, headers, list);
		System.out.println("结束单工作表导出");
//		List<Map<String, Object>> sheets = new ArrayList<Map<String,Object>>();
//		for(int a = 0;a<10;a++){
//			Map<String, Object> sheet = new HashMap<String, Object>();
//			sheet.put("headerZh", headerZh);
//			sheet.put("headers", headers);
//			sheet.put("sheetData", list);
//			sheet.put("sheetName", (a+1)+"工作表");
//			
//			sheets.add(sheet);
//		}
//		expSheetsFile("d:\\b.xls",sheets);
//		System.out.println("结束多工作表导出");
		
	}

}

 

转载于:https://my.oschina.net/body/blog/1588408