HSSFWorkbook导出Excel

最新推荐文章于 2024-06-11 09:40:13 发布
最新推荐文章于 2024-06-11 09:40:13 发布
阅读量1k 收藏 2
点赞数 1
分类专栏： Java 文章标签： HSSFWorkbook导出Excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Tane_1018/article/details/75390593
版权

HSSFWorkbook导出Excel

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.Region;



	public String dc_Mxbb(){
		OutputStream outputStream=null;
		String ny = getServletRequest().getParameter("ny1"); 	
		List<bbtjDTO> list = bbttService.dc_Bzmx(ny);
	try{
			HSSFWorkbook workbook = new HSSFWorkbook();   // 创建一个Excel文件 		
			
		//cell_Style样式	 标题
			HSSFCellStyle cell_Style = (HSSFCellStyle) workbook .createCellStyle(); // // 普通单元格样式   
			cell_Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 上下居中  
			cell_Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   // 左右居中
				HSSFFont font = workbook.createFont();  //字体
				font.setFontName("宋体"); //字体样式
				font.setFontHeightInPoints((short) 18); // 字体大小
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗  
				cell_Style.setFont(font);
		//cell_Style1样式	用于年月
			HSSFCellStyle cell_Style1 = (HSSFCellStyle) workbook .createCellStyle();
			cell_Style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			cell_Style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				HSSFFont font1 = workbook.createFont();
				font1.setFontHeightInPoints((short) 10);
				font1.setFontName("宋体");
				cell_Style1.setFont(font1);	
		//cell_Style2样式	用于表头
			HSSFCellStyle cell_Style2 = (HSSFCellStyle) workbook .createCellStyle();
				cell_Style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				cell_Style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell_Style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				cell_Style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				cell_Style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
				cell_Style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
			HSSFFont font2 = workbook.createFont();
				font2.setFontHeightInPoints((short) 11);
				font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗  
				font2.setFontName("宋体");
				cell_Style2.setFont(font2);		
		//cell_Style3样式	用于数据样式	
			HSSFCellStyle cell_Style3 = (HSSFCellStyle) workbook .createCellStyle();
				cell_Style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell_Style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				cell_Style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				cell_Style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				cell_Style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
				cell_Style3.setBorderTop(HSSFCellStyle.BORDER_THIN);  
			HSSFFont font3 = workbook.createFont();
				font3.setFontHeightInPoints((short) 11);
				font3.setFontName("宋体");
				cell_Style3.setFont(font3);			
		/**
		 *公务员医疗费统筹补助明细统计表 sheet
		 * */
			HSSFSheet sheet = workbook.createSheet("医疗费统筹补助明细清单");    // 创建一个Excel的Sheet
			sheet.setColumnWidth(0, 1500);    // 设置列宽
		    sheet.setColumnWidth(1, 12000);    
		    sheet.setColumnWidth(2, 10000);    
		    sheet.setColumnWidth(3, 7000);    
		    sheet.setColumnWidth(4, 3000);    
		    sheet.setColumnWidth(5, 3000);    
		    sheet.setColumnWidth(6, 3000);    
		    sheet.setColumnWidth(7, 3000);
		    sheet.setColumnWidth(8, 3000);    
		    sheet.setColumnWidth(9, 3000); 		    
	    HSSFRow row0 = sheet.createRow(0);    创建第一行  
			HSSFCell c0 = row0.createCell(0);    创建第一列  	
			c0.setCellValue(new HSSFRichTextString("明细清单"));   //标题			
				c0.setCellStyle(cell_Style);			
	//第2行年月
			HSSFRow row1 = sheet.createRow(1);   
				HSSFCell c1 = row1.createCell(0);   
					c1.setCellValue(new HSSFRichTextString(ny.substring(0, 4)) +"年"+new HSSFRichTextString(ny.substring(4, 6))+"月");  
					c1.setCellStyle(cell_Style1);
				//	row1.setHeight((short)400);  
	//	第3行 留白，需求变更时，修改方便
		HSSFRow row2 = sheet.createRow(2);   
			HSSFCell c21 = row2.createCell(1);   
				c21.setCellValue(new HSSFRichTextString(""));   
				c21.setCellStyle(cell_Style1);  
		// row2.setHeight((short)400);  //设置行高
		 
	//第四行--表头
	HSSFRow row3 = sheet.createRow(3);  							
			HSSFCell c30= row3.createCell(0);   
				c30.setCellValue(new HSSFRichTextString("序号"));     
				c30.setCellStyle(cell_Style2);
			HSSFCell c31 = row3.createCell(1);   
				c31.setCellValue(new HSSFRichTextString("单位名称"));     
				c31.setCellStyle(cell_Style2); 
			HSSFCell c32 = row3.createCell(2);   
				c32.setCellValue(new HSSFRichTextString("开户银行"));     
				c32.setCellStyle(cell_Style2);
			HSSFCell c33 = row3.createCell(3);   
				c33.setCellValue(new HSSFRichTextString("银行账号"));     
				c33.setCellStyle(cell_Style2);
			HSSFCell c34 = row3.createCell(4);   
				c34.setCellValue(new HSSFRichTextString("总计"));     
				c34.setCellStyle(cell_Style2);
			HSSFCell c35 = row3.createCell(5);   
				c35.setCellValue(new HSSFRichTextString("在职"));     
				c35.setCellStyle(cell_Style2);
				row3.createCell(6).setCellStyle(cell_Style2);
			HSSFCell c37 = row3.createCell(7);   
				c37.setCellValue(new HSSFRichTextString("退休"));     
				c37.setCellStyle(cell_Style2);
			    row3.createCell(8).setCellStyle(cell_Style2);
			HSSFCell c39 = row3.createCell(9);   
				c39.setCellValue(new HSSFRichTextString("家床"));     
				c39.setCellStyle(cell_Style2);	
			 row3.setHeight((short)400);  //设置行高
        //第5行
	HSSFRow row4 = sheet.createRow(4);	
		 HSSFCell c45 = row4.createCell(5);   
			c45.setCellValue(new HSSFRichTextString("住院"));     
			c45.setCellStyle(cell_Style2);
		  HSSFCell c46= row4.createCell(6);   
			c46.setCellValue(new HSSFRichTextString("门诊大病"));     
			c46.setCellStyle(cell_Style2); 
		  HSSFCell c47 = row4.createCell(7);   
			c47.setCellValue(new HSSFRichTextString("住院"));    
			c47.setCellStyle(cell_Style2); 
		  HSSFCell c48 = row4.createCell(8);   
				c48.setCellValue(new HSSFRichTextString("门诊大病"));     
				c48.setCellStyle(cell_Style2);
		row4.createCell(0).setCellStyle(cell_Style2);	
		row4.createCell(1).setCellStyle(cell_Style2);	
		row4.createCell(2).setCellStyle(cell_Style2);	
		row4.createCell(3).setCellStyle(cell_Style2);	
		row4.createCell(4).setCellStyle(cell_Style2);	
		row4.createCell(9).setCellStyle(cell_Style2);	
			row4.setHeight((short)400);  	
    //第6行 		表格数据统一样式  cell_Style3	
		int i=0;
		for(bbtjDTO mx : list){
			HSSFRow row5 = sheet.createRow(5+i); 	
				        //   String xh=String.valueOf(i+1);
				HSSFCell c50 = row5.createCell(0);   
					c50.setCellValue(new HSSFRichTextString(mx.getXh()));    //序号 
					c50.setCellStyle(cell_Style3);	
				HSSFCell c51 = row5.createCell(1);   
					c51.setCellValue(new HSSFRichTextString(mx.getDwmc()) );  //单位名称
					c51.setCellStyle(cell_Style3);			
				HSSFCell c52= row5.createCell(2);   
					c52.setCellValue(new HSSFRichTextString(mx.getKhyh() ));  //开户银行   
					c52.setCellStyle(cell_Style3);
				HSSFCell c53= row5.createCell(3);   
					c53.setCellValue(new HSSFRichTextString(mx.getYhzh()));     //银行账户
					c53.setCellStyle(cell_Style3);				
				HSSFCell c54 = row5.createCell(4);   
					c54.setCellValue(new HSSFRichTextString( String.valueOf( mx.getYbbhdje() ) ));  //合计
					c54.setCellStyle(cell_Style3);
				HSSFCell c55= row5.createCell(5);   
					c55.setCellValue(new HSSFRichTextString( String.valueOf(mx.getZz_zy_bzje()) )); //  在职-住院  
					c55.setCellStyle(cell_Style3);
				HSSFCell c56 = row5.createCell(6);   
					c56.setCellValue(new HSSFRichTextString(String.valueOf(mx.getZz_mz_dbbzje() )));    //在职--门诊大病 
					c56.setCellStyle(cell_Style3);
				HSSFCell c57 = row5.createCell(7);   
					c57.setCellValue(new HSSFRichTextString(String.valueOf( mx.getTx_zy_bzje())));   //退休--住院  
					c57.setCellStyle(cell_Style3);
				HSSFCell c58 = row5.createCell(8);   
					c58.setCellValue(new HSSFRichTextString(String.valueOf(mx.getTx_mz_dbbzje() )));    //退休--门诊大病 
					c58.setCellStyle(cell_Style3);
				HSSFCell c59 = row5.createCell(9);   
					c59.setCellValue(new HSSFRichTextString(String.valueOf(mx.getJc_bzje() )));     //家床
					c59.setCellStyle(cell_Style3);
		//		row5.setHeight((short)500); 
				i++;					
			}	
			//合并单元格: Region region1 = new Region(0, (short) 0, 0, (short) 6);
																	//参数1：行号   参数2：起始列号   参数3：行号     参数4：终止列号 
			Region region0 = new Region(0, (short)0, 0, (short)9);  
			Region region1 = new Region(1, (short)0, 1, (short)9);  
			Region region2 = new Region(2, (short)0, 2, (short)9);  		
			Region region3 = new Region(3, (short)0,4, (short)0);  			
			Region region4 = new Region(3, (short)1, 4, (short)1);  			
			Region region5 = new Region(3, (short)2, 4, (short)2);
			Region region6 = new Region(3, (short)3, 4, (short)3);
			Region region7 = new Region(3, (short)4, 4, (short)4);			
			Region region8 = new Region(3, (short)5, 3, (short)6);		
			Region region9 = new Region(3, (short)7, 3, (short)8);
			Region region10 = new Region(3, (short)9, 4, (short)9);
		
			sheet.addMergedRegion(region0);   
			sheet.addMergedRegion(region1);   
			sheet.addMergedRegion(region2); 
			sheet.addMergedRegion(region3);			
			sheet.addMergedRegion(region4);
			sheet.addMergedRegion(region5); 
			sheet.addMergedRegion(region6);	
			sheet.addMergedRegion(region7);		
			sheet.addMergedRegion(region8);	
			sheet.addMergedRegion(region9);	
			sheet.addMergedRegion(region10);	
			
			outputStream = this.getServletResponse().getOutputStream();
			this.getServletResponse().setHeader("Content-Type","application/vnd.ms-excel;charset=UTF-8");
			//导出Excel文件名
			this.getServletResponse().setHeader("Content-Disposition", "attachment; filename="+ java.net.URLEncoder.encode("明细统计表.xls","UTF-8"));
			workbook.write(outputStream);  			
			
		} catch (Exception e) {  
		    e.printStackTrace();  
		}finally{
			if(outputStream != null){
				System.gc();
				try {
					outputStream.flush();
					//outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}				
			}
		}
		return null;			
	}
		
}


Tane_1018
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
HSSFWorkbook导入Microsoft Office Excel
付士山哥
09-24 273
控制层 @PostMapping("/importExcel") public JSONObject importExcel(@RequestParam("file") MultipartFile file) { LOGGER.info("importExcel - start"); JSONObject result = this.start(); try { this.pa...
使用HSSFWorkbook导出Excel表格
qq_37432174的博客
08-04 997
使用HSSFWorkbook导出Excel表格 一、加依赖或者导入jar包 去https://mvnrepository.com下载Apache POI 这里使用的3.9 二、上代码 /** * * <li>action控制层</li> * @param req * @param resp * @throws Exception */ publi...
java导出Excel文件
atokinesis的博客
09-10 1449
【代码】java导出Excel文件。
HSSFWorkbook 转 file
落日流年
10-08 9798
public static File xssfWorkbookToFile(HSSFWorkbook wb, String name) { File toFile = new File(name); try { OutputStream os = new FileOutputStream(toFile); wb.write(os); os.close(); } catch (Exception e) { e.printStackTrace(); } re.
使用HSSFWorkbook导出excel
最新发布
FenceRain的博客
06-11 232
使用HSSFWorkbook导出excel
SXSSFWorkbook 转 InputStream
掐指一算乀缺钱
11-01 9910
public static InputStream workbookConvertorStream(SXSSFWorkbook workbook) { InputStream in = null; try{ //临时缓冲区 ByteArrayOutputStream out = new ByteArrayOutputStream(); ...
【Java】-【XSSFWorkBook解析.xlsx的excel】
CaraYQ的博客
04-26 2073
在POI中提供了Excel文件的两种实现类，HSSFWrorBook和XSSFWorkBook，前者适用于Excel文件的老版本，后缀名为“.xls”,而后者适用于后缀名为“.xlsx”。
java 后台 Excel 文件生成后转化为字节流
程介元
05-06 3万+
Java 后台 Excel 文件生成后转化为字节流java excel 使用 poi组件， HSSFWorkbook workBook = new HSSFWorkbook(); 对于workBook生成字节流，很容易发现有个workBook .getBytes()，但是，是不可用的，下载以后打不开， 如果下载，正确的写法为 workBook.write(response.getOutputStr
EasyExcel转化为byte[]、inputStream或者OutputStream
weixin_43316101的博客
04-09 9805
Excel转化为流操作
Java处理excel两种不同的方式
loongshawn的博客
07-22 1万+
将生成的excel文件转为输入流，然后上传OSS，不用在本地存储该文件对象。
HSSFWorkBooK用法
jackyxwr的专栏
06-27 4万+
public ActionResult excelPrint() {       HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件       HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet       sheet.createFreezePane(1, 3);// 冻结       // 设置列宽       sheet.setColumnWidth(0, 1000);
poi操作文档转为输入流
BrotherJinJin的博客
08-26 3795
poi操作文档转为输入流
XSSFWorkbook对象的write方法内会将传入的资源流自动关闭 导致下载excel失败
yangmengcsdn的博客
08-25 2047
解决方法: 将XSSFWorkbook转换成ByteArrayOutputStream，用ByteArrayOutputStream对象将流写入zip对象中。
POI--HSSFWorkbook类
weixin_34166472的博客
05-30 292
HSSFWorkbook类 要生成一个Workbook，需要用到HSSFWorkbook类文件。构造器如下： 现在我们使用2种方式来获得一个Workbook对象。 1，创建excel表 HSSFWorkbook workbook = new HSSFWorkbook()。 保存操作用「HSSFWorkbook」类的「write」方法:参数是作为保存...
HSSFWorkbook操作excel读写
hao_hl1314的博客
02-28 1457
//exlel读操作 MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; Iterator iter = multipartRequest.getFileNames(); File fileFile = null; while (iter.hasNext()) { Mu
【Java】Excel解析，文件读取或者上传文件直接读InputStream
寒沨的博客
12-11 4006
package com.jd.ibdreport.service.approta; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.
在做excel导出时如何将workbook直接写在输出流中
weixin_30825199的博客
02-11 5597
参考网址 https://blog.csdn.net/u011109420/article/details/51330677 https://blog.csdn.net/u012116457/article/details/49780677 Java 后台 Excel 文件生成后转化为字节流 java excel 使用 poi组件， HSSFWorkbook workBook = ...
java导出Excel通用方法
热门推荐
10-25 8万+
java导出Excel通用方法，只需要一个list 集合。通用方法改进之处踊跃提出 package oa.common.utils; import java.io.OutputStream; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.struts2.Servlet
HSSFWorkBooK导出excel
09-06
- *2* [使用HSSFWorkbook导出、操作excel](https://blog.csdn.net/caicai1171523597/article/details/86643667)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值