导出excel 文件

1.导出无表头excel 文件单个工作表(sheet)，【fileName是标题名，titleList是列名，list就是列的内容了】

[java] view plain copy
  1. /** 
  2.      * 导出无表头excel 文件单个工作表(sheet) 
  3.      *  
  4.      * @param fileName 
  5.      * @param titleList 
  6.      * @param list 
  7.      * @param response 
  8.      */  
  9.     public static void exportNoHeadExcel(String fileName, String[] titleList,  
  10.             List list, HttpServletResponse response) {  
  11.         SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");  
  12.         String todayStr = df.format(new Date());  
  13.         OutputStream os = null;  
  14.         try {  
  15.             os = response.getOutputStream();  
  16.             String localFileName = fileName;  
  17.             fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题  
  18.             fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题  
  19.             response.setContentType("application/vnd.ms-excel;");  
  20.             response.setHeader("Content-disposition", "attachment; filename=\""  
  21.                     + fileName + "_" + todayStr + ".xls\"");  
  22.             // 开始写入excel  
  23.             // 字段字体  
  24.             jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(  
  25.                     WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);  
  26.             jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(  
  27.                     wfc1);  
  28.             wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
  29.             wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
  30.             // 结果字体  
  31.             jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
  32.             wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);  
  33.             wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
  34.             WritableWorkbook wbook = Workbook.createWorkbook(os);  
  35.             // 写sheet名称  
  36.             WritableSheet wsheet = wbook.createSheet(localFileName, 0);  
  37.             for (int m = 0; m < titleList.length; m++) {  
  38.                 wsheet.setColumnView(m, 30);  
  39.             }  
  40.             // 加入字段名  
  41.             for (int n = 0; n < titleList.length; n++) {  
  42.                 wsheet.addCell(new jxl.write.Label(n, 0, titleList[n], wcfFC1));  
  43.             }  
  44.             // 写入流中  
  45.             int row = 0;  
  46.             for (int r = 0; r < list.size(); r++) {  
  47.                 Object[] obj = (Object[]) list.get(r);  
  48.                 for (int x = 0; x < titleList.length; x++) {  
  49.                     wsheet.addCell(new jxl.write.Label(x, row + 1,  
  50.                             obj[x] == null ? " " : obj[x].toString(), wcfFC1));  
  51.                 }  
  52.                 row++;  
  53.                 if (row % 60000 == 0) {  
  54.                     row = 0;  
  55.                     // 写sheet名称  
  56.                     wsheet = wbook.createSheet(localFileName, 0);  
  57.                     for (int m = 0; m < titleList.length; m++) {  
  58.                         wsheet.setColumnView(m, 30);  
  59.                     }  
  60.                     // 加入字段名  
  61.                     for (int n = 0; n < titleList.length; n++) {  
  62.                         wsheet.addCell(new jxl.write.Label(n, 0, titleList[n],  
  63.                                 wcfFC1));  
  64.                     }  
  65.                 }  
  66.             }  
  67.             wbook.write();  
  68.             wbook.close();  
  69.             os.flush();  
  70.         } catch (Exception e) {  
  71.             e.printStackTrace();  
  72.         } finally {  
  73.             if (os == null) {  
  74.                 Log.info("os is null");  
  75.             } else {  
  76.                 try {  
  77.                     os.close();  
  78.                     os = null;  
  79.                 } catch (IOException e) {  
  80.                     e.printStackTrace();  
  81.                 }  
  82.             }  
  83.         }  
  84.     }  
  85.       


2.首先导出无表头excel 文件带多个工作表(sheet)，【sheetList存放的就是Map包含fileName是标题名，titleList是列名，list就是列的内容】

[java] view plain copy
  1. /** 
  2.      * 导出无表头excel文件 
  3.      *  
  4.      * @param fileName 
  5.      * @param titleList 
  6.      * @param list 
  7.      * @param response 
  8.      */  
  9.     public static void exportNoHeadExcel(List<Map<String,Object>> sheetList, HttpServletResponse response) {  
  10.         SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");  
  11.         String todayStr = df.format(new Date());  
  12.         OutputStream os = null;  
  13.         try {  
  14.             os = response.getOutputStream();  
  15.             WritableWorkbook wbook = Workbook.createWorkbook(os);  
  16.             for(int i=0;i<sheetList.size();i++){  
  17.                 Map<String,Object> map=sheetList.get(i);  
  18.                 String fileName=(String) map.get("fileName");  
  19.                 String[] titleList=(String[]) map.get("titleList");  
  20.                 List list=(List) map.get("list");  
  21.                 String localFileName = fileName;  
  22.                 fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题  
  23.                 fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题  
  24.                 response.setContentType("application/vnd.ms-excel;");  
  25.                 response.setHeader("Content-disposition", "attachment; filename=\""  
  26.                         + fileName + "_" + todayStr + ".xls\"");  
  27.                 // 开始写入excel  
  28.                 // 字段字体  
  29.                 jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(  
  30.                         WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);  
  31.                 jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(  
  32.                         wfc1);  
  33.                 wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
  34.                 wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
  35.                 // 结果字体  
  36.                 jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
  37.                 wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);  
  38.                 wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
  39.                 // 写sheet名称  
  40.                 WritableSheet wsheet = wbook.createSheet(localFileName,i);  
  41.                 for (int m = 0; m < titleList.length; m++) {  
  42.                     wsheet.setColumnView(m, 30);  
  43.                 }  
  44.                 // 加入字段名  
  45.                 for (int n = 0; n < titleList.length; n++) {  
  46.                     wsheet.addCell(new jxl.write.Label(n, 0, titleList[n], wcfFC1));  
  47.                 }  
  48.                 // 写入流中  
  49.                 int row = 0;  
  50.                 for (int r = 0; r < list.size(); r++) {  
  51.                     Object[] obj = (Object[]) list.get(r);  
  52.                     for (int x = 0; x < titleList.length; x++) {  
  53.                         wsheet.addCell(new jxl.write.Label(x, row + 1,  
  54.                                 obj[x] == null ? " " : obj[x].toString(), wcfFC1));  
  55.                     }  
  56.                     row++;  
  57.                     if (row % 60000 == 0) {  
  58.                         row = 0;  
  59.                         // 写sheet名称  
  60.                         wsheet = wbook.createSheet(localFileName, 0);  
  61.                         for (int m = 0; m < titleList.length; m++) {  
  62.                             wsheet.setColumnView(m, 30);  
  63.                         }  
  64.                         // 加入字段名  
  65.                         for (int n = 0; n < titleList.length; n++) {  
  66.                             wsheet.addCell(new jxl.write.Label(n, 0, titleList[n],  
  67.                                     wcfFC1));  
  68.                         }  
  69.                     }  
  70.                 }  
  71.             }     
  72.             wbook.write();  
  73.             wbook.close();  
  74.             os.flush();  
  75.         } catch (Exception e) {  
  76.             e.printStackTrace();  
  77.         } finally {  
  78.             if (os == null) {  
  79.                 Log.info("os is null");  
  80.             } else {  
  81.                 try {  
  82.                     os.close();  
  83.                     os = null;  
  84.                 } catch (IOException e) {  
  85.                     e.printStackTrace();  
  86.                 }  
  87.             }  
  88.         }  
  89.     }  


3.导出excel文件带标题【fileName是标题名，titleList是列名，list就是列的内容了】

[java] view plain copy
    1. /** 
    2.      * 导出excel 文件  带标题 
    3.      *  
    4.      * @param fileName  
    5.      * @param titleList 
    6.      * @param list 
    7.      * @param response 
    8.      */  
    9.     public static void exportWithHeadExcel(String fileName, String[] titleList,  
    10.             List list, HttpServletResponse response) {  
    11.         Date now = new Date();  
    12.         SimpleDateFormat dateformat = new java.text.SimpleDateFormat(  
    13.                 "yyyy年MM月dd日HH时mm分");  
    14.         SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");  
    15.         String todayStr = df.format(new Date());  
    16.         String today = dateformat.format(now);  
    17.         OutputStream os = null;  
    18.         try {  
    19.             os = response.getOutputStream();  
    20.             String localFileName = fileName;  
    21.             fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题  
    22.             fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题  
    23.             response.setContentType("application/vnd.ms-excel;");  
    24.             response.setHeader("Content-disposition", "attachment; filename=\""  
    25.                     + fileName + "_" + todayStr + ".xls\"");  
    26.             // 开始写入excel  
    27.             // 加标题  
    28.             // 标题字体  
    29.             jxl.write.WritableFont wfc = new jxl.write.WritableFont(  
    30.                     WritableFont.COURIER, 18, WritableFont.NO_BOLD, false);  
    31.             jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(  
    32.                     wfc);  
    33.             wcfFC.setAlignment(jxl.format.Alignment.CENTRE);  
    34.             wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
    35.             // 字段字体  
    36.             jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(  
    37.                     WritableFont.COURIER, 10, WritableFont.NO_BOLD, false);  
    38.             jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(  
    39.                     wfc1);  
    40.             wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);  
    41.             wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
    42.             // 结果字体  
    43.             jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();  
    44.             wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);  
    45.             wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);  
    46.             WritableWorkbook wbook = Workbook.createWorkbook(os);  
    47.             // 写sheet名称  
    48.             WritableSheet wsheet = wbook.createSheet(localFileName, 0);  
    49.             int i = 3;  
    50.             for (int m = 0; m < titleList.length; m++) {  
    51.                 wsheet.setColumnView(m, 30);  
    52.             }  
    53.             // 加入字段名  
    54.             for (int n = 0; n < titleList.length; n++) {  
    55.                 wsheet.addCell(new jxl.write.Label(n, 3, titleList[n], wcfFC1));  
    56.             }  
    57.             // 加入标题  
    58.             wsheet.mergeCells(0, 0, i - 1, 0);  
    59.             wsheet.addCell(new Label(0, 0, localFileName, wcfFC));  
    60.             // 加入打印时间  
    61.             wsheet.addCell(new Label(i - 2, 1, "打印日期:"));  
    62.             wsheet.addCell(new Label(i - 1, 1, today));  
    63.             // 写入流中  
    64.             int row = 0;  
    65.             for (int r = 0; r < list.size(); r++) {  
    66.                 Object[] obj = (Object[]) list.get(r);  
    67.                 for (int x = 0; x < titleList.length; x++) {  
    68.                     wsheet.addCell(new jxl.write.Label(x, row + 4,  
    69.                             obj[x] == null ? " " : obj[x].toString(), wcfFC1));  
    70.                 }  
    71.                 row++;  
    72.                 if (row % 60000 == 0) {  
    73.                     row = 0;  
    74.                     // 写sheet名称  
    75.                     wsheet = wbook.createSheet(localFileName, 0);  
    76.                     i = 3;  
    77.                     for (int m = 0; m < titleList.length; m++) {  
    78.                         wsheet.setColumnView(m, 30);  
    79.                     }  
    80.                     // 加入字段名  
    81.                     for (int n = 0; n < titleList.length; n++) {  
    82.                         wsheet.addCell(new jxl.write.Label(n, 3, titleList[n],  
    83.                                 wcfFC1));  
    84.                     }  
    85.                     // 加入标题  
    86.                     wsheet.mergeCells(0, 0, i - 1, 0);  
    87.                     wsheet.addCell(new Label(0, 0, localFileName, wcfFC));  
    88.                     // 加入打印时间  
    89.                     wsheet.addCell(new Label(i - 2, 1, "打印日期:"));  
    90.                     wsheet.addCell(new Label(i - 1, 1, today));  
    91.                 }  
    92.             }  
    93.             wbook.write();  
    94.             wbook.close();  
    95.             os.flush();  
    96.         } catch (Exception e) {  
    97.             e.printStackTrace();  
    98.         } finally {  
    99.             if (os == null) {  
    100.                 Log.info("os is null");  
    101.             } else {  
    102.                 try {  
    103.                     os.close();  
    104.                     os = null;  
    105.                 } catch (IOException e) {  
    106.                     e.printStackTrace();  
    107.                 }  
    108.             }  
    109.         }  
    110.     } 
posted @ 2017-12-07 01:25  海底一小鱼  阅读(210)  评论(0)  编辑  收藏  举报