关于 HSSFWorkbook 的导出excel的相关工具类(封装/优化)

关于 HSSFWorkbook 的导出excel的相关工具类(具体导出excel图片使用可修改部分源码)

//依赖的jar包
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.alibaba.fastjson.JSONObject;

/**
 * 导出excel
 * @param sheetName		sheet名称
 * @param map			字段名称以及标题名称集合
 * @param list			数据集合(字段名称需与数据集合的key值一致)
 * @return
 */
public static HSSFWorkbook listxToExcel(String sheetName,LinkedHashMap<String, String> map, List<?> list) {
	Object[] fieldsName =  map.keySet().toArray();		//字段集合
	Object[] rowsName =  map.values().toArray();		//标题集合
	// 第一步，创建一个webbook，对应一个Excel文件
	HSSFWorkbook wb = new HSSFWorkbook();
	// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
	HSSFSheet sheet = wb.createSheet(sheetName);
	// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
	HSSFRow row = sheet.createRow((int) 0);
	// 第四步，创建单元格，并设置值表头 设置表头居中
	HSSFCellStyle style = wb.createCellStyle();
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);				//水平居中  
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);	//垂直居中  
    style.setWrapText( true);									//自动换行
    HSSFCell cell=null;
    //写入标题
	for (int i = 0; i < rowsName.length; i++) {
		cell = row.createCell(i);
		cell.setCellValue(rowsName[i].toString());
		cell.setCellStyle(style);
	}
	
	// 第五步，创建单元格，并设置值
	for (int i = 0; i < list.size(); i++) {
		row = sheet.createRow((int) i + 1);
		for (int j = 0; j < fieldsName.length; j++) {
			cell = row.createCell(j);
			Object obj = list.get(i);
			String jsonString = JSONObject.toJSONString(obj);
			JSONObject jo = JSONObject.parseObject(jsonString);
			//jsonString = jo.get("columns").toString();
			jo = JSONObject.parseObject(jsonString);
			if (jo.get(fieldsName[j].toString())==null) {
				cell.setCellValue("");
			}else{
				cell.setCellValue(jo.get(fieldsName[j].toString()).toString());
			}
			cell.setCellStyle(style);
		}
		/*
		// img --start
		ByteArrayOutputStream outStream_item = new ByteArrayOutputStream();
		// 将图片写入流中 
		BufferedImage bufferImg_item;
		try { 
			bufferImg_item = ImageIO.read(getInputStream("https://www.baidu.com/img/bd_logo1.png"));
			//bufferImg_item = ImageIO.read(getInputStream("http://39.108.192.245"+list.get(i).getStr("goodsImg")));
			ImageIO.write(bufferImg_item, "PNG", outStream_item);
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		//设置图片路径
		HSSFClientAnchor anchor_item = new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1+i, (short) 2, 2+i);
		patri_item.createPicture(anchor_item, wb.addPicture(outStream_item.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
		// img -- end
		*/
	}
	return wb;
}

调用示例

/**
 * 模拟测试导出数据
 * @param args
 * @throws IOException
 */
public static void main(String[] args) throws IOException {
    LinkedHashMap<String, String> map= new LinkedHashMap<String, String>();
    map.put("name", "名称");
    map.put("age", "年龄");
    //--------------------这里模拟从数据库查询出的数据
    List<Map<String,Object>> list=new ArrayList<>();
    Map<String,Object> project = new HashMap<>();
    project.put("name", "小李");
    project.put("age", "33");
    list.add(project);
    list.add(project);
    //--------------------这里模拟从数据库查询出的数据
    HSSFWorkbook wb = doExcel("sheetName", map, list);
    String path = "F:/test.xls";
    FileOutputStream out = new FileOutputStream(path);
    wb.write(out);
    out.close();
}

直接输出响应给浏览器客户端下载(map,list略写)

HSSFWorkbook wb =ExcelUtil.listxToExcel("明细",map, list);
	this.getResponse().setCharacterEncoding("UTF-8");
	this.getResponse().addHeader("Content-Disposition","attachment;filename="+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".xls");
	this.getResponse().setContentType("application/x-msdownload");
	try {
		wb.write(this.getResponse().getOutputStream());
		renderNull();//基于jfinal框架
	} catch (IOException e) {
		e.printStackTrace();
	}