java－excel导出

java excel导出分为两种2003年的格式和2007年的格式。

2003年的xls一个sheet限制65536。

2007年的xlsx限制为1048576。

jxl导入2003

gradle jar包compile group: 'net.sourceforge.jexcelapi', name: 'jxl', version: '2.6.12'

public void writeExcelJXL() {
         String path = "/macc/excel/";
         String[] titles = {"用户活跃时间","频段","网点名称","下行速率(kbps)","MAC","上线时间","丢包率(‰)","信号强度","SN","时延(ms)","上下行速率(kbps)","上行速率(kbps)","用户ip","下行流量(byte)","上行流量(byte)","上下行流量(byte)"};
         List<String> titleList = Arrays.asList(titles);
         File file = new File(path);
         if(!file.exists())
             file.mkdirs();
         
          // 以下开始输出到EXCEL
          try {    
           /** **********创建工作簿************ */
           OutputStream os = new FileOutputStream(path + "test.xls");
           WritableWorkbook workbook = Workbook.createWorkbook(os);
         
           /** **********创建工作表************ */
         
           WritableSheet sheet = workbook.createSheet("Sheet1", 0);
         
           /** **********设置纵横打印（默认为纵打）、打印纸***************** */
           jxl.SheetSettings sheetset = sheet.getSettings();
           sheetset.setProtected(false);
         
         
           /** ************设置单元格字体************** */
           WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
         
           /** ************以下设置三种单元格样式，灵活备用************ */
           // 用于标题居中
           WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
           wcf_center.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
           wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
           wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
           wcf_center.setWrap(false); // 文字是否换行
         
           /** ***************以下是EXCEL第一行列标题********************* */
           for (int i = 0; i < titleList.size(); i++) {
            sheet.addCell(new Label(i, 0,titleList.get(i),wcf_center));
           }   
           /** ***************以下是EXCEL正文数据********************* */
           int i=1;
           List<CurrentUser> listContent = mongo.findAll(CurrentUser.class);
           for(CurrentUser obj:listContent){
               for (int j = 0; j < titleList.size(); j++) {
                   Object va = obj.getMac();
                   if(va != null)
                       sheet.addCell(new Label(j, i,va.toString(),wcf_center));
               }
               i++;
           }
           /** **********将以上缓存中的内容写到EXCEL文件中******** */
           workbook.write();
           /** *********关闭文件************* */
           workbook.close();   
           System.out.println("finished");
          } catch (Exception e) {
              e.printStackTrace();
          }
     }

poi导入2007

compile group: 'org.apache.poi', name: 'poi', version: '3.14'

compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.14'

private void create2007Excel() {
        String path = "/macc/excel/";
         String[] titles = {"用户活跃时间","频段","网点名称","下行速率(kbps)","MAC","上线时间","丢包率(‰)","信号强度","SN","时延(ms)","上下行速率(kbps)","上行速率(kbps)","用户ip","下行流量(byte)","上行流量(byte)","上下行流量(byte)"};
         List<String> titleList = Arrays.asList(titles);
         File file = new File(path);
         if(!file.exists())
             file.mkdirs();
         XSSFWorkbook workbook = new XSSFWorkbook();
         try (OutputStream os = new FileOutputStream(path + "test.xlsx")) {
             /** **********创建工作簿************ */
           XSSFSheet sheet = workbook.createSheet();
           /** ***************以下是EXCEL第一行列标题********************* */
           XSSFRow titleRow = sheet.createRow(0);
           for (int i = 0; i < titleList.size(); i++) {
               XSSFCell cell = titleRow.createCell(i);
               cell.setCellValue(titleList.get(i));
           }   
           /** ***************以下是EXCEL正文数据********************* */
           int i=1;
           List<CurrentUser> listContent = mongo.findAll(CurrentUser.class);
           int rowNum = 1;
           for(CurrentUser obj:listContent){
               XSSFRow row = sheet.createRow(rowNum);
               rowNum ++;
               for (int j = 0; j < titleList.size(); j++) {
                   Object va = obj.getMac();
                   if(va != null)
                       row.createCell(j).setCellValue(va.toString());
               }  
               i++;
           }
           workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        System.out.println("创建成功 office 2007 excel");  
    }