通过jxl方式生成api生成excel报表文档,一种方式生成单表记录或简单报表格式,另一种生成复杂的嵌套和自定义的数据报表。具体代码工具类如下(jxl.jar下载,在线doc参见其他页)import java.io.BufferedInputStream;@b@import java.io.BufferedOutputStream;@b@import java.io.File;@b@import java.io.FileInputStream;@b@import java.io.FileOutputStream;@b@import java.io.InputStream;@b@import java.io.OutputStream;@b@import java.net.URLEncoder;@b@import java.util.List;@b@import javax.servlet.http.HttpServletResponse;@b@import com.woopa.common.util.InitConfUtils;@b@@b@import jxl.Workbook;@b@import jxl.write.Label;@b@import jxl.write.WritableCellFormat;@b@import jxl.write.WritableFont;@b@import jxl.write.WritableSheet;@b@import jxl.write.WritableWorkbook;@b@ @b@public class ExportExcelUtil {@b@ @b@ private static File excelPath;//excel路径@b@ private static String[] sheetNameArr;//sheet名称@b@ private static WritableWorkbook wbook;//工作簿@b@ private static WritableCellFormat cellFormat;//单元格样式@b@ private static WritableSheet[] wsheet;//sheet@b@ private static OutputStream oss;//数据输入流 @b@ @b@ @b@ /**@b@ * 完整的自定义导出接口@b@ * @param pathExcelName 导出路径&文件名称@b@ * @param columnValue 记录集数据(记录)@b@ * @param columnName 列明title(属性后字段名称)@b@ * @param sheetName 工作表名称@b@ * @throws Exception@b@ */@b@ public synchronized static void onceExport(String pathExcelName,List columnValue,String [] columnName,String sheetName) throws Exception { @b@ try { @b@ File file = new File(pathExcelName);@b@ OutputStream os = new FileOutputStream(file); @b@ WritableWorkbook wbook = Workbook.createWorkbook(os); //建立excel文件 @b@ WritableSheet wsheet = wbook.createSheet(sheetName, 0); //工作表名称 @b@ //设置Excel字体 @b@ WritableFont wfont = new WritableFont(WritableFont.ARIAL, 8, @b@ WritableFont.BOLD, false, @b@ jxl.format.UnderlineStyle.NO_UNDERLINE, @b@ jxl.format.Colour.BLACK); @b@ WritableCellFormat titleFormat = new WritableCellFormat(wfont); @b@ //设置Excel表头 @b@ for (int i = 0; i @b@ *
WritableFont.ARIAL, - 字体@b@ * 8, - 大小@b@ * WritableFont.BOLD, - 粗体@b@ * false, @b@ * jxl.format.UnderlineStyle.NO_UNDERLINE, - 有无下划线@b@ * jxl.format.Colour.BLACK) - 颜色@b@ * @b@ * */@b@ @b@ public synchronized static void complexExport_s2_mid(int sheetNo,int hNo,int zNo,String content,WritableFont wfont) throws Exception{@b@ if(wfont==null)@b@ wfont = new WritableFont(WritableFont.ARIAL,8, @b@ WritableFont.NO_BOLD, false, @b@ jxl.format.UnderlineStyle.NO_UNDERLINE, @b@ jxl.format.Colour.BLACK);@b@ cellFormat= new WritableCellFormat(wfont); @b@ Label cell = new Label(zNo, hNo, content, cellFormat); @b@ wsheet[sheetNo].addCell(cell); @b@ }@b@ @b@ /**@b@ * 对象销毁@b@ * @b@ * */@b@ public synchronized static void complexExport_s3_end() throws Exception{@b@ wbook.write();@b@ excelPath=null;@b@ sheetNameArr=null;@b@ cellFormat=null;@b@ wsheet=null;@b@ wbook.close();@b@ oss.close();@b@ }@b@ @b@ public static void export(HttpServletResponse response,String path){@b@BufferedInputStream bis = null;@b@BufferedOutputStream bos = null;@b@OutputStream os = null;@b@InputStream is = null;@b@//String strRootFilePath = ServletActionContext.getServletContext().getRealPath("/");@b@int index = path.lastIndexOf("/");@b@String filePath = path.substring(index+1);@b@File file = new File(path);@b@try {@b@is = new FileInputStream(file);@b@bis = new BufferedInputStream(is);@b@os = response.getOutputStream();@b@bos = new BufferedOutputStream(os);@b@response.setContentType("application/vnd.ms-excel");@b@response.setHeader("Content-disposition", "inline;filename="@b@+ URLEncoder.encode(filePath, "utf-8"));@b@int count = 0;@b@byte[] by = new byte[8192];@b@while ((count = bis.read(by, 0, 8192)) != -1) {@b@bos.write(by, 0, count);@b@}@b@bos.flush();@b@if (bos != null)@b@bos.close();@b@if (os != null)@b@os.close();@b@if (bis != null)@b@bis.close();@b@if (is != null)@b@is.close();@b@} catch (Exception e) {@b@e.printStackTrace();@b@} finally {@b@try {@b@if (bos != null)@b@bos.close();@b@if (os != null)@b@os.close();@b@if (bis != null)@b@bis.close();@b@if (is != null)@b@is.close();@b@} catch (Exception e) {@b@e.printStackTrace();@b@}@b@}@b@}@b@ @b@ @b@}用例一public static void main(String[] args) throws Exception{@b@ List sslist=new ArrayList();@b@ String[] titleS = { "ID", "TITLE", "DATE"};@b@ sslist.add(new String[]{"12","xwood","8.9"});@b@ ExportExcelUtil.onceExport("c:/NJ/bak/test123.xls", sslist, titleS, "测试Sheet");@b@}
用例二public static void main(String[] args) throws Exception{@b@ @b@ ExportExcelUtil.complexExport_s1_begin("c:/NJ/bak/test456.xls",new String[]{"测试Sheet2"});@b@ @b@ ExportExcelUtil.complexExport_s2_mid(0, 0, 1, "ID2", null);@b@ ExportExcelUtil.complexExport_s2_mid(0, 0, 2, "TITLE2", null);@b@ ExportExcelUtil.complexExport_s2_mid(0, 0, 3, "DATA2", null);@b@ @b@ ExportExcelUtil.complexExport_s2_mid(0, 1, 1, "13", null);@b@ ExportExcelUtil.complexExport_s2_mid(0, 1, 2, "xwood", null);@b@ ExportExcelUtil.complexExport_s2_mid(0, 1, 3, "8/9", null);@b@ @b@ ExportExcelUtil.complexExport_s3_end();@b@ }