如何将数据导出为EXCEL格式

如何将数据导出为EXCEL格式

作者:fwgforce

[本文章原创,如需转载,请注明作者和链接]

    第一步:我们先设置一下自己的EXCEL将要导出的样式,这可以是一个公有的方法:

public void designTableStyle(HSSFSheet sheet, HSSFWorkbook workbook,String[] headers) {
		//设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 20);
		// 生成一个样式
		// 设置这些样式
		HSSFCellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);

		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

		style.setBorderRight(HSSFCellStyle.BORDER_THIN);

		style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 生成一个字体

		HSSFFont font = workbook.createFont();

		font.setColor(HSSFColor.VIOLET.index);

		font.setFontHeightInPoints((short) 12);

		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		// 把字体应用到当前的样式

		style.setFont(font);

		// 生成并设置另一个样式

		HSSFCellStyle style2 = workbook.createCellStyle();

		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);

		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);

		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);

		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);

		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		// 生成另一个字体

		HSSFFont font2 = workbook.createFont();

		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

		// 把字体应用到当前的样式

		style2.setFont(font2);
		HSSFRow row = sheet.createRow(0);

		for (short i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);

		}
	}

 

 下面是我的一个ACTION类里面执行导出的方法:返回到前台的是一个流,然后弹出提示保存.注意紫色的地方

 

@Scope("prototype")
@Service("dataExportAction")
public class DataExportAction {
	private InputStream excelStream;
	private String shtName = "";
	public String buildingExport() {
		//要导出的数据
		List<Building> list = buildingService.getBuildAll();
		String[] headers = { "楼宇名称", "楼宇地址", "楼宇开发商", "楼宇占地面积(㎡)", "建筑面积(㎡)",
				"楼层数量", "楼层面积", "房间总数", "房内净高", "竣工时间", "经营单位（1）", "经营单位（2）",
				"经营单位（3）", "入住时间", "楼宇类型 二级", "物业公司", "物业等级", "建筑总高", "车位个数",
				"空调设置", "电梯数", "所属街道", "周边设施", "交通状况", "招商方向", "可招商面积(㎡)",
				"待招商面积(㎡)", "合作方式", "入住主要行业", "入驻企业数", "租金", "物业费", "联系人",
				"联系电话", "闲置面积", "备注", "楼宇产权", "楼宇分类", "楼宇等级", "楼宇排序号" };
		try {	
			shtName = getFileName("楼宇信息表");//必须给它配一个get(),set(),方法,方便调用
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			// 声明一个工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet("楼宇信息表");

			// 生成一个样式
			designTableStyle(sheet, workbook, headers);
			//开始往EXCEL里写数据,循环你要的数据,每行每行的往里面插入
			for (int i = 0; i < list.size(); i++) {
				HSSFRow row2 = sheet.createRow(i + 1);
				Object object = list.get(i);
				if (headers.length == 1) {
					if (null != object) {
						for (int j = 0; j < headers.length; j++) {
							if (null != object) {
								//处理日期格式
								if (object instanceof Date) {
									Date date = (Date) object;
									SimpleDateFormat sdf = new SimpleDateFormat(
											"yyyy-MM-dd");
									object = sdf.format(date);

								}
								HSSFCell createCell = row2.createCell(j);
								createCell.setCellValue(object.toString());
							}

						}
					}
				} else if (headers.length > 1) {
					if (null != object) {
						Object[] objs = (Object[]) (object);
						for (int j = 0; j < headers.length; j++) {
							if (null != objs[j]) {
								//处理日期格式
								if (objs[j] instanceof Date) {
									Date date = (Date) objs[j];
									SimpleDateFormat sdf = new SimpleDateFormat(
											"yyyy-MM-dd");
									objs[j] = sdf.format(date);
								}
								HSSFCell createCell = row2.createCell(j);
								createCell.setCellValue(objs[j].toString());
							}

						}
					}
				}

			}
			workbook.write(out);
			byte[] byteArray = out.toByteArray();
			excelStream = new ByteArrayInputStream(byteArray);
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return Action.SUCCESS;
	}
	public void setShtName(String shtName) {
		this.shtName = shtName;
	}

	public InputStream getExcelStream() {
		return excelStream;
	}

	public void setExcelStream(InputStream excelStream) {
		this.excelStream = excelStream;
	}
}

 

 然后配置文件如下:

	<!-- 楼宇信息表数据导出 -->
		  <action name="buildingExport" class="dataExportAction"	method="buildingExport" > 
			<!-- 失败跳转的页面,根据需要自定义的 -->        
			<result name="error">/data/export_data.jsp</result>
			<!-- 成功跳转的页面,根据需要自定义的 -->
			<result name="success" type="stream">/data/export_data.jsp
			<param name="contentType">application/vnd.ms-excel;charset=UTF-8</param>   
             			<param name="inputName">excelStream</param>
            			<param name="contentDisposition">attachment;filename="${shtName}"</param>
             			</result>
		</action>

  上传配置说明:

一，关于struts.xml参数

 

<struts>
  <package  name="technicalInformationAction"   extends="Action">
   
    <default-action-ref name="download"/>
  
     <action name="downLoad" class="com.DownLoadAction">
               
                <!--这里的返回值并不是字符串，而是一个流类型-->  
                <result name="success" type="stream">
                
                <param name="contentType">application/octet-stream;charset=ISO8859-1</param>
                
                <param name="inputName">inputStream</param>
                     
                 <!--这里需要将文件名进行转码，转码后将值赋予downloadFileName，对应文中的getDownloadFileName()-->        
                <param name="contentDisposition">attachment;filename="${downloadFileName}"</param> 
               
                <param name="bufferSize">4096</param>
                
                </result> 
     </action> 
  
  </package>
</struts>

1、contentDisposition：文件下载的处理方式：包括内联(inline)和附件(attachment)，附件方式会弹出保存文件的对话框，否则浏览器会尝试直接显示文件。一般默认值为attachment;filename="${fileName}"，其中，filename表示使用经过转码的文件名作为下载文件名，""号中间既可以直接加文件名，又可以将调用该Action中的getFileName方法。{}内，一定要去掉get并把第一个大写字母变小写。

困扰1：乱码问题。

       如果要下载的文件包含中文字符，会导致下载下来的文件名称变成乱码，或者干脆出现“InternetExplore无法打开该站点”错误。

2、contentType:下载文件的文件类型，和互联网MIME标准钟的规定类型一致，application/octet-stream 表示无限制。其他类型见附言。

3、inputName:下载文件的来源流，通常就是“流对象名”，对应着action类中某个InputStream类型的属性名，例如：取值为inputStream的属性需要编写getInputStream()方法。一旦xml文件读到inputStream，它就会自动去找Action中的getInputStream方法。

4、bufferSize表示下载缓冲区的大小。

二，action类的编写

import java.io.InputStream;
 
import org.apache.struts2.ServletActionContext;
 
public class DownloadAction {
       
        private String fileName;
       
        public void setFileName(String fileName) {
                this.fileName = fileName;
        }
        public InputStream getInputStream() {
                return ServletActionContext.getServletContext().getResourceAsStream("/" + fileName);
        }
       
        public String execute(){
                return "success";
        }
 
}
需要注意的就是使用getResourceAsStream方法时，文件路径必须是以“/”开头，且是相对路径。这个路径是相对于项目根目录（WEB-INFO下）的。

可以用return new FileInputStream(fileName)的方法来得到绝对路径的文件。

 

三，测试

在WEB-INF下随意丢一个test.txt，部署好后进入浏览器，输入tomcat地址/项目路径/download.action?fileName=test.txt即可下载到该文件。

详细请看http://blog.163.com/lizhenming_2008/blog/static/765583332009220115857856/

 

 

 

附:contentType类型.
'ez' => 'application/andrew-inset',
'hqx' => 'application/mac-binhex40',
'cpt' => 'application/mac-compactpro',
'doc' => 'application/msword',
'bin' => 'application/octet-stream',
'dms' => 'application/octet-stream',
'lha' => 'application/octet-stream',
'lzh' => 'application/octet-stream',
'exe' => 'application/octet-stream',
'class' => 'application/octet-stream',
'so' => 'application/octet-stream',
'dll' => 'application/octet-stream',
'oda' => 'application/oda',
'pdf' => 'application/pdf',
'ai' => 'application/postscript',
'eps' => 'application/postscript',
'ps' => 'application/postscript',
'smi' => 'application/smil',
'smil' => 'application/smil',
'mif' => 'application/vnd.mif',
'xls' => 'application/vnd.ms-excel',
'ppt' => 'application/vnd.ms-powerpoint',
'wbxml' => 'application/vnd.wap.wbxml',
'wmlc' => 'application/vnd.wap.wmlc',
'wmlsc' => 'application/vnd.wap.wmlscriptc',
'bcpio' => 'application/x-bcpio',
'vcd' => 'application/x-cdlink',
'pgn' => 'application/x-chess-pgn',
'cpio' => 'application/x-cpio',
'csh' => 'application/x-csh',
'dcr' => 'application/x-director',
'dir' => 'application/x-director',
'dxr' => 'application/x-director',
'dvi' => 'application/x-dvi',
'spl' => 'application/x-futuresplash',
'gtar' => 'application/x-gtar',
'hdf' => 'application/x-hdf',
'js' => 'application/x-javas

cript',
'skp' => 'application/x-koan',
'skd' => 'application/x-koan',
'skt' => 'application/x-koan',
'skm' => 'application/x-koan',
'latex' => 'application/x-latex',
'nc' => 'application/x-netcdf',
'cdf' => 'application/x-netcdf',
'sh' => 'application/x-sh',
'shar' => 'application/x-shar',
'swf' => 'application/x-shockwave-flash',
'sit' => 'application/x-stuffit',
'sv4cpio' => 'application/x-sv4cpio',
'sv4crc' => 'application/x-sv4crc',
'tar' => 'application/x-tar',
'tcl' => 'application/x-tcl',
'tex' => 'application/x-tex',
'texinfo' => 'application/x-texinfo',
'texi' => 'application/x-texinfo',
't' => 'application/x-troff',
'tr' => 'application/x-troff',
'roff' => 'application/x-troff',
'man' => 'application/x-troff-man',
'me' => 'application/x-troff-me',
'ms' => 'application/x-troff-ms',
'ustar' => 'application/x-ustar',
'src' => 'application/x-wais-source',
'xhtml' => 'application/xhtml+xml',
'xht' => 'application/xhtml+xml',
'zip' => 'application/zip',
'au' => 'audio/basic',
'snd' => 'audio/basic',
'mid' => 'audio/midi',
'midi' => 'audio/midi',
'kar' => 'audio/midi',
'mpga' => 'audio/mpeg',
'mp2' => 'audio/mpeg',
'mp3' => 'audio/mpeg',
'aif' => 'audio/x-aiff',
'aiff' => 'audio/x-aiff',
'aifc' => 'audio/x-aiff',
'm3u' => 'audio/x-mpegurl',
'ram' => 'audio/x-pn-realaudio',
'rm' => 'audio/x-pn-realaudio',
'rpm' => 'audio/x-pn-realaudio-plugin',
'ra' => 'audio/x-realaudio',
'wav' => 'audio/x-wav',
'pdb' => 'chemical/x-pdb',
'xyz' => 'chemical/x-xyz',
'bmp' => 'image/bmp',
'gif' => 'image/gif',
'ief' => 'image/ief',
'jpeg' => 'image/jpeg',
'jpg' => 'image/jpeg',
'jpe' => 'image/jpeg',
'png' => 'image/png',
'tiff' => 'image/tiff',
'tif' => 'image/tiff',
'djvu' => 'image/vnd.djvu',
'djv' => 'image/vnd.djvu',
'wbmp' => 'image/vnd.wap.wbmp',
'ras' => 'image/x-cmu-raster',
'pnm' => 'image/x-portable-anymap',
'pbm' => 'image/x-portable-bitmap',
'pgm' => 'image/x-portable-graymap',
'ppm' => 'image/x-portable-pixmap',
'rgb' => 'image/x-rgb',
'xbm' => 'image/x-xbitmap',
'xpm' => 'image/x-xpixmap',
'xwd' => 'image/x-xwindowdump',
'igs' => 'model/iges',
'iges' => 'model/iges',
'msh' => 'model/mesh',
'mesh' => 'model/mesh',
'silo' => 'model/mesh',
'wrl' => 'model/vrml',
'vrml' => 'model/vrml',
'css' => 'text/css',
'html' => 'text/html',
'htm' => 'text/html',
'asc' => 'text/plain',
'txt' => 'text/plain',
'rtx' => 'text/richtext',
'rtf' => 'text/rtf',
'sgml' => 'text/sgml',
'sgm' => 'text/sgml',
'tsv' => 'text/tab-separated-values',
'wml' => 'text/vnd.wap.wml',
'wmls' => 'text/vnd.wap.wmlscript',
'etx' => 'text/x-setext',
'xsl' => 'text/xml',
'xml' => 'text/xml',
'mpeg' => 'video/mpeg',
'mpg' => 'video/mpeg',
'mpe' => 'video/mpeg',
'qt' => 'video/quicktime',
'mov' => 'video/quicktime',
'mxu' => 'video/vnd.mpegurl',
'avi' => 'video/x-msvideo',
'movie' => 'video/x-sgi-movie',
'ice' => 'x-conference/x-cooltalk'

 

本文配备部分导出说明和JAR包下载!有什么不明白的可以在线联系或加QQ:745270812一起探讨下,鄙人新手.