通过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 < columnName.length; i++) { @b@ Label excelTitle = new Label(i, 0, columnName[i], titleFormat); @b@ wsheet.addCell(excelTitle); @b@ } @b@ for (int i = 0; i < columnValue.size(); i++) {@b@ String [] strs = (String [])columnValue.get(i);@b@ for (int j = 0; j < strs.length; j++) {@b@ Label content = new Label(j, i+1, strs[j]); @b@ wsheet.addCell(content); @b@ }@b@ }@b@ wbook.write(); //写入文件 @b@ wbook.close(); @b@ os.close(); @b@ } catch (Exception e) { @b@ throw new Exception("导出文件出错:"+e.getMessage()); @b@ } @b@ }@b@ /**@b@ * @param pathExcelName Excel文件路径@b@ * @param sheetNameArr sheet 名称数组@b@ * */@b@ public synchronized static void complexExport_s1_begin(String pathExcelName,String[] sheetNameArr) throws Exception{@b@ @b@ excelPath=new File(pathExcelName);@b@ int i=0;@b@ if(excelPath.exists()){@b@ excelPath.delete(); @b@ }@b@ oss = new FileOutputStream(pathExcelName);@b@ wbook = Workbook.createWorkbook(oss);@b@ wsheet=new WritableSheet[sheetNameArr.length];@b@ for(String sheetname:sheetNameArr){@b@ wsheet[i]=wbook.createSheet(sheetname,i);@b@ i++;}@b@ }@b@ @b@ /**@b@ * @param hNo 横行编号@b@ * @param zNo 纵向编号@b@ * @param WritableFont @b@ * 如:new WritableFont(@b@ * <ul>@b@ * <li>WritableFont.ARIAL, - 字体</li>@b@ * <li> 8, - 大小</li>@b@ * <li> WritableFont.BOLD, - 粗体</li>@b@ * <li> false, </li>@b@ * <li> jxl.format.UnderlineStyle.NO_UNDERLINE, - 有无下划线</li>@b@ * <li> jxl.format.Colour.BLACK) - 颜色</li>@b@ * </ul>@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<String[]> sslist=new ArrayList<String[]>();@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@ }