POI之操作Excel表

Office 2003和Office 2007及以上版本Excel的区别:

 

Excel 2003

Excel 2007以上版本

技术背景

OLE2(二进制文件)

OOXML(XML文件)

行数(单sheet)

65536

1048576

列数

255

65536


最后的效果图:


Excel 2003操作使用HSSF

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.9</version>
</dependency>
@RequestMapping("/cargo/outproduct/printNotemplate.action")
public void printNotemplate(String inputDate, HttpServletRequest request, HttpServletResponse response) throws Exception
{
	/*
	 * POI实现excel打印
	 * 1、大标题,合并单元格
	 * 2、标题,修饰
	 * 3、内容,修饰
	 * 
	*/
	Workbook _wb = new HSSFWorkbook();	//创建工作簿
	Sheet _sheet = _wb.createSheet();	//创建工作表
	_sheet.setColumnWidth(0, 1*278);	//设置列宽 256,BUG,精度不够,总是差一点
	_sheet.setColumnWidth(1, 26*278);	
		
	Cell _cell = null;
	int _rowNo = 0;
	//处理大标题
	_sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));	//合并单元格
	Row _row = _sheet.createRow(_rowNo++);
	_row.setHeightInPoints(36);
	_cell = _row.createCell(1);
	_cell.setCellValue(inputDate + "月份出货表");
	//设置大标题的样式
	CellStyle _bigTitleCellStyle = setBigTitleCellStyle(_wb);
	_cell.setCellStyle(_bigTitleCellStyle);
		
	//处理标题
	String[] title = new String[]{"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};	//标题数组
	_row = _sheet.createRow(_rowNo++);
	_row.setHeightInPoints(26);
	for(int i = 0; i < title.length; ++i)
	{
	<span style="white-space:pre">	</span>_cell = _row.createCell(i + 1);
		_cell.setCellValue(title[i]);
		CellStyle _titleCellStyle = setTitleCellStyle(_wb);
		_cell.setCellStyle(_titleCellStyle);
	}
		
	//处理内容
	List<OutProductVO> _dataList = outProductService.find(inputDate);
	inputDate = inputDate.replaceFirst("-0", "-").replaceFirst("-", "年");
	CellStyle _textCellStyle = setTextCellStyle(_wb);
	for(int i = 0; i < _dataList.size(); ++i)
	{
		OutProductVO _outProductVO = _dataList.get(i);
		_row = _sheet.createRow(_rowNo++);
		_row.setHeightInPoints(24);

		int _columnNo = 1;
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getCustomName());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getContractNo());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getProductNo());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getCnumber());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getFactoryName());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getDeliveryPeriod());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getShipTime());
		_cell.setCellStyle(_textCellStyle);
			
		_cell = _row.createCell(_columnNo++);
		_cell.setCellValue(_outProductVO.getTradeTerms());
		_cell.setCellStyle(_textCellStyle);
	}
		
	ByteArrayOutputStream _os = new ByteArrayOutputStream();
	_wb.write(_os);
		
	DownloadUtil downloadUtil = new DownloadUtil();		//直接弹出下载框,用户可以打开,可以保存
	downloadUtil.download(_os, response, inputDate + "月份出货表.xls");
		
	_os.flush();
	_os.close();
}
//大标题样式
private CellStyle setBigTitleCellStyle(Workbook workbook)
{
	CellStyle _cellStyle = workbook.createCellStyle();
	_cellStyle.setAlignment(CellStyle.ALIGN_CENTER); 	//横向居中
	_cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);	//纵向居中
		
	Font _font = workbook.createFont();
	_font.setFontName("宋体");
	_font.setFontHeightInPoints((short)16);	//字体大小
	_font.setBoldweight(Font.BOLDWEIGHT_BOLD);	//字体加粗
		
	_cellStyle.setFont(_font);	//绑定字体
		
	return _cellStyle;
}
	
//小标题样式
private CellStyle setTitleCellStyle(Workbook workbook)
{
	CellStyle _cellStyle = workbook.createCellStyle();
	_cellStyle.setAlignment(CellStyle.ALIGN_CENTER); 	//横向居中
	_cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);	//纵向居中
	_cellStyle.setBorderTop(CellStyle.BORDER_THIN);		//设置四周边线,细线
	_cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
	_cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
	_cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		
	Font _font = workbook.createFont();
	_font.setFontName("黑体");
	_font.setFontHeightInPoints((short)12);
			
	_cellStyle.setFont(_font);
		
	return _cellStyle;
}
	
//文字样式
private CellStyle setTextCellStyle(Workbook workbook)
{
	CellStyle _cellStyle = workbook.createCellStyle();
	_cellStyle.setAlignment(CellStyle.ALIGN_LEFT);				//横向居左
	_cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);		//纵向居中
	//设置四周边线,细线
	_cellStyle.setBorderTop(CellStyle.BORDER_THIN);					
	_cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
	_cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
	_cellStyle.setBorderRight(CellStyle.BORDER_THIN);
	
	Font _font = workbook.createFont();
	_font.setFontName("Times New Roman");
	_font.setFontHeightInPoints((short)10);
			
	_cellStyle.setFont(_font);
		
	return _cellStyle;
}


使用模板开发,模板效果图:



//模板开发
@RequestMapping("/cargo/outproduct/printHSSF.action")
public void printHSSF(String inputDate, HttpServletRequest request, HttpServletResponse response) throws Exception
{
	//linux下jdk1.8 方法获取时,不会拼接自己写的目录 
	String path = request.getSession().getServletContext().getRealPath("/") + "/make/xlsprint/";
	InputStream is = new FileInputStream(new File(path + "tOUTPRODUCT.xls"));
		
	Workbook wb = new HSSFWorkbook(is);		//打开一个模板文件,工作簿
	Sheet sheet = wb.getSheetAt(0);			//获取到第一个工作表
		
	Row nRow = null;
	Cell nCell = null;
		
	//获取模板上的单元格样式
	nRow = sheet.getRow(2);
		
	//客户的样式
	nCell = nRow.getCell(1);
	CellStyle customStyle = nCell.getCellStyle();		
		
	//订单号的样式
	nCell = nRow.getCell(2);
	CellStyle contractNoStyle = nCell.getCellStyle();		
		
	//货号的样式
	nCell = nRow.getCell(3);
	CellStyle productNoStyle = nCell.getCellStyle();		
		
	//数量的样式
	nCell = nRow.getCell(4);
	CellStyle numStyle = nCell.getCellStyle();		
		
	//生产厂家的样式
	nCell = nRow.getCell(5);
	CellStyle factoryStyle = nCell.getCellStyle();		
		
	//日期的样式
	nCell = nRow.getCell(6);
	CellStyle dateStyle = nCell.getCellStyle();		
		
	//贸易条款的样式
	nCell = nRow.getCell(8);
	CellStyle tradeStyle = nCell.getCellStyle();		
				
		
	//处理大标题
	nRow = sheet.getRow(0);			//获取一个行对象
	nCell = nRow.getCell(1);			//获取一个单元格对象
	String _inputDade = inputDate.replaceFirst("-0", "-").replaceFirst("-", "年");
	nCell.setCellValue(_inputDade + "月份出货表");		//yyyy-MM
		
	int rowNo = 2;
		
	//处理内容
	List<OutProductVO> dataList = outProductService.find(inputDate);
	for(int j=0;j<dataList.size();j++)
	{
		int colNo = 1;				//初始化列号
		OutProductVO op = dataList.get(j);
			
		nRow = sheet.createRow(rowNo++);
		nRow.setHeightInPoints(24);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getCustomName());
		nCell.setCellStyle(customStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getContractNo());
		nCell.setCellStyle(contractNoStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getProductNo());
		nCell.setCellStyle(productNoStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getCnumber());
		nCell.setCellStyle(numStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getFactoryName());
		nCell.setCellStyle(factoryStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getDeliveryPeriod());
		nCell.setCellStyle(dateStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getShipTime());
		nCell.setCellStyle(dateStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getTradeTerms());
		nCell.setCellStyle(tradeStyle);
	}
				
<span style="white-space:pre">	</span>ByteArrayOutputStream os = new ByteArrayOutputStream();
	wb.write(os);
		
	DownloadUtil downloadUtil = new DownloadUtil();	<span style="white-space:pre">	</span>//直接弹出下载框,用户可以打开,可以保存
	downloadUtil.download(os, response, _inputDade + "月份出货表.xls");
		
	os.flush();
	os.close();
}

Excel 2007及以上版本使用XSSF

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
</dependency>

//模板开发XSSF
@RequestMapping("/cargo/outproduct/print.action")
public void print(String inputDate, HttpServletRequest request, HttpServletResponse response) throws Exception
{
	//linux下jdk1.8 方法获取时,不会拼接自己写的目录 
	String path = request.getSession().getServletContext().getRealPath("/") + "/make/xlsprint/";
	InputStream is = new FileInputStream(new File(path + "tOUTPRODUCT.xlsx"));
		
	Workbook wb = new XSSFWorkbook(is);		//打开一个模板文件,工作簿 2007以上版本
	Sheet sheet = wb.getSheetAt(0);			//获取到第一个工作表
		
	Row nRow = null;
	Cell nCell = null;
		
	//获取模板上的单元格样式
	nRow = sheet.getRow(2);
		
	//客户的样式
	nCell = nRow.getCell(1);
	CellStyle customStyle = nCell.getCellStyle();		
		
	//订单号的样式
	nCell = nRow.getCell(2);
	CellStyle contractNoStyle = nCell.getCellStyle();		
		
	//货号的样式
	nCell = nRow.getCell(3);
	CellStyle productNoStyle = nCell.getCellStyle();		
		
	//数量的样式
	nCell = nRow.getCell(4);
	CellStyle numStyle = nCell.getCellStyle();		
		
	//生产厂家的样式
	nCell = nRow.getCell(5);
	CellStyle factoryStyle = nCell.getCellStyle();		
		
	//日期的样式
	nCell = nRow.getCell(6);
	CellStyle dateStyle = nCell.getCellStyle();		
		
	//贸易条款的样式
	nCell = nRow.getCell(8);
	CellStyle tradeStyle = nCell.getCellStyle();		
		
		
	//处理大标题
	nRow = sheet.getRow(0);			//获取一个行对象
	nCell = nRow.getCell(1);			//获取一个单元格对象
	String _inputDate = inputDate.replaceFirst("-0", "-").replaceFirst("-", "年");
	nCell.setCellValue(_inputDate + "月份出货表");		//yyyy-MM
		
	int rowNo = 2;
	//处理内容
	List<OutProductVO> dataList = outProductService.find(inputDate);
	for(int j=0;j<dataList.size();j++)
	{
		int colNo = 1;				//初始化列号
		OutProductVO op = dataList.get(j);
			
		nRow = sheet.createRow(rowNo++);
		nRow.setHeightInPoints(24);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getCustomName());
		nCell.setCellStyle(customStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getContractNo());
		nCell.setCellStyle(contractNoStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getProductNo());
		nCell.setCellStyle(productNoStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getCnumber());
		nCell.setCellStyle(numStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getFactoryName());
		nCell.setCellStyle(factoryStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getDeliveryPeriod());
		nCell.setCellStyle(dateStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getShipTime());
		nCell.setCellStyle(dateStyle);
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(op.getTradeTerms());
		nCell.setCellStyle(tradeStyle);
	}		
		
	ByteArrayOutputStream os = new ByteArrayOutputStream();
	wb.write(os);
		
	DownloadUtil downloadUtil = new DownloadUtil();		//直接弹出下载框,用户可以打开,可以保存
	downloadUtil.download(os, response, _inputDate + "月份出货表.xlsx");
		
	os.flush();
	os.close();
}

百万数据导出,只能导出到Excel 2007及以上版本,使用SXSSF

基本原理:在打印过程中,已经加工完的对象,临时存到一个临时文件中,它采用xml临时文件。最终处理完时,以文件写文件的方式将临时文件的内容写入到最终的xlsx文件中。临时文件及其位置:



<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
</dependency>

public void jdbcex(boolean isClose) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException, IOException, InterruptedException
{
	String xlsFile = "c:/poiSXXFSDBBigData.xlsx";	//输出文件
	Workbook wb = new SXSSFWorkbook(100);		//创建excel文件,内存只有100条记录【关键语句】,每加工完100条记录写入临时文件
	Sheet sheet = wb.createSheet("我的第一个工作簿");	//建立新的sheet对象
	
<span style="white-space:pre">	</span>Row nRow = null;
	Cell nCell   = null;

	//使用jdbc链接数据库
	Class.forName("com.mysql.jdbc.Driver").newInstance();  
		
	String url = "jdbc:mysql://localhost:3306/jkmore100?characterEncoding=UTF-8";
	String user = "root";
	String password = "root";
		
	Connection conn = DriverManager.getConnection(url, user,password);   
	Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);   

	String sql = "select name,age,des from customer limit 1000000";   	//100万测试数据
	ResultSet rs = stmt.executeQuery(sql);  	//bug 要分次读取,否则记录过多
		
		
	long  startTime = System.currentTimeMillis();	//开始时间
	System.out.println("strat execute time: " + startTime);
	
	int rowNo = 0;
	int colNo = 0;
	while(rs.next())
<span style="white-space:pre">	</span>{
		colNo = 0;
		nRow = sheet.createRow(rowNo++);

		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(rs.getString(colNo));
			
		nCell = nRow.createCell(colNo++);
		nCell.setCellValue(rs.getString(colNo));
			
		if(rowNo%100==0)
<span style="white-space:pre">		</span>{
		<span style="white-space:pre">	</span>System.out.println("row no: " + rowNo);
		}
			
		Thread.sleep(1);	//休息一下,防止对CPU占用
	}
		
	long finishedTime = System.currentTimeMillis();	//处理完成时间
	System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");
		
		
	FileOutputStream fOut = new FileOutputStream(xlsFile);
	wb.write(fOut);
	fOut.flush();
	fOut.close();
		
	long stopTime = System.currentTimeMillis();		//写文件时间
	System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");
		
	if(isClose)
<span style="white-space:pre">	</span>{
		this.close(rs, stmt, conn);
	}
}
	
//close resource
private void close(ResultSet rs, Statement stmt, Connection conn ) throws SQLException
{
	rs.close();   
	stmt.close();   
	conn.close(); 
}




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值