java后端导出excel记录

java后端导出excel及自适应列宽

调用方法

// An highlighted block
ExportBigExcel ex = new ExportBigExcel(title, rowname, list);
 File file = new File(filepathdemand);
        if (!file.exists()) {
            file.mkdir();// 创建该文件夹目录
        }
        String name ="维修周期信息" + ".xlsx";
         OutputStream   os = new BufferedOutputStream( new FileOutputStream(file.getAbsolutePath() + "/" + name));
         ex.export1(os);

ExportBigExcel的方法

   // 显示的导出表的标题
    private String title;
    // 导出表的列名
    private String[] rowName;
    List<Object[]> dataList;
     private  Row nRow = null;        //行对象
    private Cell nCell = null;      //列对象
     // 构造函数，传入要导出的数据
  //导出时，传入List
    public ExportBigExcel(String title, String[] rowName, List<Object[]> dataList) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
    }
    //写入excel到服务器上
    // 与传入list配套使用，大量数据处理快速
    public void export2(OutputStream out) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        if(title==null || title.equals("")){
            title="sheet_";
        }
        Sheet sheet = workbook.createSheet(title);

        //存储最大列宽
        Map<Integer,Integer> maxWidth = new HashMap<Integer,Integer>();

        // 定义所需列数
        int columnNum = rowName.length;
        Row rowRowName = sheet.createRow(0);

        // 将列头设置到sheet的单元格中
        for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n);
            cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING);
            XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text);
            maxWidth.put(n,cellRowName.getStringCellValue().getBytes().length  * 256 + 200);
        }
        int rowNo = 0;      //总行号
        int pageRowNo = 1;  //页行号

        for(Object[] dataObject:dataList) {
            //打印1000000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
            if(rowNo%1000000==0){
                System.out.println("Current Sheet:" + rowNo/1000000);
                sheet = workbook.createSheet("sheet"+(rowNo/1000000));//建立新的sheet对象
                sheet = workbook.getSheetAt(rowNo/1000000);        //动态指定当前的工作表
                pageRowNo = 1;      //每当新建了工作表就将当前工作表的行号重置为0
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 打印每行，   rsmd.getColumnCount() --- 列属性的个数
            for(int j=0;j<columnNum;j++){
                nCell = nRow.createCell(j,Cell.CELL_TYPE_STRING);
                nCell.setCellValue(dataObject[j]!=null?dataObject[j].toString():"");
                int length=0;
                if (nCell.getStringCellValue()!=null)
                    length = nCell.getStringCellValue().getBytes().length  * 256 + 200;
                //这里把宽度最大限制到15000
                if (length>15000){
                    length = 15000;
                }
                maxWidth.put(j,Math.max(length,maxWidth.get(j)));
            }

            if(rowNo%10000==0){
                System.out.println("row no: " + rowNo);
            }
        }

        for (int i = 0; i < columnNum; i++) {
//                sheet.autoSizeColumn(i);
          //  sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
            sheet.setColumnWidth(i,maxWidth.get(i));
        }
        sheet.setDefaultRowHeight((short) (2 * 256));

        try {
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }