使用poi 从数据库导出生成Excel文件-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_9716/article/details/81815646

本文介绍了一种利用Apache POI库将数据库中的数据导出为Excel文件的方法。该过程涉及下载所需jar包并配置Struts 1.*版本项目，通过示例代码展示了如何创建Excel工作簿、设置单元格值及正确处理中文字符。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近做一个月参与了公司两个小项目，毕业快两年，参与项目还真少！所以呢，本人掌握的知识还是很有限，完成了某些功能，需要放到这里，供日后参考！

[b]如何从数据库中导出数据生成Excel文档[/b]

首先第一步，我参考了POI 这个开源项目，从其官方网站download需要用到的jar包。 http://poi.apache.org/

然后项目采用的是Struts1.* 的版本，ok，那么放一些关键代码上来：

Action中的代码：


	public ActionForward doExportExcel(ActionMapping mapping, ActionForm form, 
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		ExportExcelService eeService = new ExportExcelService();		
		RawDataExportSearchForm rawDataExportSearchForm = (RawDataExportSearchForm) form;		


		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=\" " + "Reporting.xls" + "\"");

		HSSFWorkbook wb = eeService.doExportExcel(request, rawDataExportSearchForm);
		BufferedOutputStream buf = new BufferedOutputStream(response
				.getOutputStream());
		wb.write(buf);
		buf.close();

		return mapping.findForward("doExporting");
	}

说明下： RawDataExportSearchForm 是前台页面用来查询的ActionForm，用于传入参数（parameters）

Service的代码：

		public HSSFWorkbook doExportExcel(HttpServletRequest request, RawDataExportSearchForm form){
HSSFWorkbook workbook = new HSSFWorkbook();
				HSSFSheet worksheet = workbook.createSheet("Sheet1");

				// index from 0,0... cell A1 is cell(0,0)
				HSSFRow rowHead = worksheet.createRow((short) 0);

				HSSFCell cellHeadA = rowHead.createCell((short) 0);
				cellHeadA.setCellValue("第一行列名");

				HSSFCell cellHeadB = rowHead.createCell((short) 1);
				cellHeadB.setCellValue("第一行列名");


				HSSFCell cellHeadC = rowHead.createCell((short) 2);
				cellHeadC.setCellValue("第一行列名");

				HSSFCell cellHeadD = rowHead.createCell((short) 3);
				cellHeadD.setCellValue("第一行列名");

…………

//第一行列名写完后


//从DB中获取Data，组装成HashMap
HashMap<Long, RawData> rawDataMap = getRawDataFromDB(form);					
				Set keys = rawDataMap.keySet();
				RawData rawData = null;	
				int i = 1;
//遍历List中的每一个Object
				for(Iterator iter = keys.iterator(); iter.hasNext(); i++){
					rawData = rawDataMap.get(iter.next());									
					HSSFRow row = worksheet.createRow((short) i);

					HSSFCell cellA = row.createCell((short) 0);
					cellA.setEncoding(HSSFCell.ENCODING_UTF_16);
					cellA.setCellValue(rawData.getField1());

					HSSFCell cellB = row.createCell((short) 1);
					cellB.setEncoding(HSSFCell.ENCODING_UTF_16);
					cellB.setCellValue(rawData.getField2());

					HSSFCell cellC = row.createCell((short) 2);
					cellC.setEncoding(HSSFCell.ENCODING_UTF_16);
					cellC.setCellValue(rawData.getField3());

					HSSFCell cellD = row.createCell((short) 3);
					cellD.setEncoding(HSSFCell.ENCODING_UTF_16);
					cellD.setCellValue(rawData.getField4());

……

//till to end， cell.setEncoding(HSSFCell.ENCODING_UTF_16);是为解决显示中文出乱码问题

}

return workbook;	
}