org.apache.poi.xssf.streaming.SXSSFSheet#createRow

Source File: ExcelWriter.java From excel-boot with Artistic License 2.0

6 votes

/**
 * 构建模板Excel
 *
 * @param <R>
 * @param <T>
 * @return
 */
public SXSSFWorkbook generateTemplateWorkbook() {
    SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);

    List<ExcelPropertyEntity> propertyList = excelEntity.getPropertyList();
    SXSSFSheet sheet = generateHeader(workbook, propertyList, excelEntity.getFileName());

    SXSSFRow row = sheet.createRow(1);
    for (int j = 0; j < propertyList.size(); j++) {
        SXSSFCell cell = row.createCell(j);
        cell.setCellValue(propertyList.get(j).getTemplateCellValue());
        calculateColumWidth(cell, j);
    }
    sizeColumWidth(sheet, propertyList.size());
    return workbook;
}

Source File: ExcelWriter.java From excel-boot with Artistic License 2.0

5 votes

/**
 * 初始化第一行的属性
 *
 * @param workbook
 * @param propertyList
 * @param sheetName
 * @return
 */
private SXSSFSheet generateHeader(SXSSFWorkbook workbook, List<ExcelPropertyEntity> propertyList, String sheetName) {
    SXSSFSheet sheet = workbook.createSheet(sheetName);
    SXSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeight((short) 600);
    CellStyle headCellStyle = getHeaderCellStyle(workbook);
    for (int i = 0; i < propertyList.size(); i++) {
        SXSSFCell cell = headerRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellValue(propertyList.get(i).getColumnName());
        calculateColumWidth(cell, i);
    }
    return sheet;
}

Source File: CommonsUtils.java From czy-nexus-commons-utils with Apache License 2.0

5 votes

/**
 * 自定义：大标题
 *
 * @param jRow
 * @param k
 * @param wb
 * @param labelName
 * @param sxssfRow
 * @param sxssfSheet
 * @param listRow
 * @return
 */
private static int setLabelName(Integer jRow, Integer k, SXSSFWorkbook wb, String[] labelName, SXSSFRow sxssfRow, SXSSFSheet sxssfSheet, List<String[]> listRow) {

    if (labelName != null) {
        //  自定义：大标题和样式。参数说明：new String[]{"表格数据一", "表格数据二", "表格数据三"}
        sxssfRow = sxssfSheet.createRow(0);
        Cell cell = createCell(sxssfRow, 0, labelName[k]);
        setMergedRegion(sxssfSheet, 0, 0, 0, listRow.get(0).length - 1);
        setLabelStyles(wb, cell, sxssfRow);
        jRow = 1;
    }
    return jRow;
}

Source File: ExcelUtil.java From agile-service-old with Apache License 2.0

5 votes

public static <T> SXSSFWorkbook generateExcel(List<T> list, Class<T> clazz, String[] fieldsName, String[] fields, String sheetName) {
    //1、创建工作簿
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    if (list != null && !list.isEmpty()) {
        //1.3、列标题样式
        CellStyle style2 = createCellStyle(workbook, (short) 13, CellStyle.ALIGN_LEFT, true);
        //1.4、强制换行
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setWrapText(true);
        //2、创建工作表
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        //设置默认列宽
        sheet.setDefaultColumnWidth(13);
        SXSSFRow row2 = sheet.createRow(0);
        row2.setHeight((short) 260);
        for (int j = 0; j < list.size(); j++) {
            SXSSFRow row = sheet.createRow(j + 1);
            row.setHeight((short) 260);
            for (int i = 0; i < fieldsName.length; i++) {
                //3.3设置列标题
                SXSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(style2);
                cell2.setCellValue(fieldsName[i]);
                //4、操作单元格；将数据写入excel
                handleWriteCell(row, i, j, list, cellStyle, fields, clazz);
            }
        }
    }
    return workbook;
}

Source File: PoiFormatTest.java From easyexcel with Apache License 2.0

5 votes

@Test
public void lastRowNum() throws IOException {
    String file = "D:\\test\\原文件.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}

Source File: PoiWriteTest.java From easyexcel with Apache License 2.0

5 votes

@Test
public void write0() throws IOException {
    FileOutputStream fileOutputStream =
        new FileOutputStream("D://test//tt132" + System.currentTimeMillis() + ".xlsx");
    SXSSFWorkbook sxxsFWorkbook = new SXSSFWorkbook();
    SXSSFSheet sheet = sxxsFWorkbook.createSheet("t1");
    SXSSFRow row = sheet.createRow(0);
    SXSSFCell cell1 = row.createCell(0);
    cell1.setCellValue(999999999999999L);
    SXSSFCell cell2 = row.createCell(1);
    cell2.setCellValue(1000000000000001L);
    SXSSFCell cell32 = row.createCell(2);
    cell32.setCellValue(300.35f);
    sxxsFWorkbook.write(fileOutputStream);
}

Source File: PoiWriteTest.java From easyexcel with Apache License 2.0

5 votes

@Test
public void write() throws IOException {
    FileOutputStream fileOutputStream =
        new FileOutputStream("D://test//tt132" + System.currentTimeMillis() + ".xlsx");
    SXSSFWorkbook sxxsFWorkbook = new SXSSFWorkbook();
    SXSSFSheet sheet = sxxsFWorkbook.createSheet("t1");
    SXSSFRow row = sheet.createRow(0);
    SXSSFCell cell1 = row.createCell(0);
    cell1.setCellValue(Long.toString(999999999999999L));
    SXSSFCell cell2 = row.createCell(1);
    cell2.setCellValue(Long.toString(1000000000000001L));
    sxxsFWorkbook.write(fileOutputStream);
}

Source File: PoiTest.java From easyexcel with Apache License 2.0

5 votes

@Test
public void lastRowNum() throws IOException {
    String file = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}

Source File: PoiTest.java From easyexcel with Apache License 2.0

5 votes

@Test
public void cp() throws IOException, InvalidFormatException {
    String file = "d://test/tt.xlsx";
    SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
    SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    SXSSFRow row = xssfSheet.getRow(0);
    LOGGER.info("第一行数据:{}", row);
    xssfSheet.createRow(20);
    LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
}

Source File: ExcelWriter.java From excel-boot with Artistic License 2.0

4 votes

/**
 * @param param
 * @param exportFunction
 * @param <P>
 * @param <T>
 * @return
 * @throws InvocationTargetException
 * @throws NoSuchMethodException
 * @throws ParseException
 * @throws IllegalAccessException
 */
public <P, T> SXSSFWorkbook generateWorkbook(P param, ExportFunction<P, T> exportFunction) throws Exception {
    SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
    int sheetNo = 1;
    int rowNum = 1;
    List<ExcelPropertyEntity> propertyList = excelEntity.getPropertyList();
    //初始化第一行
    SXSSFSheet sheet = generateHeader(workbook, propertyList, excelEntity.getFileName());

    //生成其他行
    int firstPageNo = 1;
    while (true) {
        List<T> data = exportFunction.pageQuery(param, firstPageNo, pageSize);
        if (data == null || data.isEmpty()) {
            if (rowNum != 1) {
                sizeColumWidth(sheet, propertyList.size());
            }
            log.warn("查询结果为空,结束查询!");
            break;
        }
        int dataSize = data.size();
        for (int i = 1; i <= dataSize; i++, rowNum++) {
            T queryResult = data.get(i - 1);
            Object convertResult = exportFunction.convert(queryResult);
            if (rowNum > MAX_RECORD_COUNT_PEER_SHEET) {
                sizeColumWidth(sheet, propertyList.size());
                sheet = generateHeader(workbook, propertyList, excelEntity.getFileName() + "_" + sheetNo);
                sheetNo++;
                rowNum = 1;
                columnWidthMap.clear();
            }
            SXSSFRow row = sheet.createRow(rowNum);
            for (int j = 0; j < propertyList.size(); j++) {
                SXSSFCell cell = row.createCell(j);
                buildCellValue(cell, convertResult, propertyList.get(j));
                calculateColumWidth(cell, j);
            }
            if (nullCellCount == propertyList.size()) {
                log.warn("忽略一行空数据!");
                sheet.removeRow(row);
                rowNum--;
            }
            nullCellCount = 0;

        }
        if (data.size() < pageSize) {
            sizeColumWidth(sheet, propertyList.size());
            log.warn("查询结果数量小于pageSize,结束查询!");
            break;
        }
        firstPageNo++;
    }
    return workbook;
}

Source File: ExcelWriter.java From excel-boot with Artistic License 2.0

4 votes

/**
 * 构建多Sheet Excel
 *
 * @param param
 * @param exportFunction
 * @param <R>
 * @param <T>
 * @return
 * @throws InvocationTargetException
 * @throws NoSuchMethodException
 * @throws ParseException
 * @throws IllegalAccessException
 */
public <R, T> SXSSFWorkbook generateMultiSheetWorkbook(R param, ExportFunction<R, T> exportFunction) throws Exception {
    int pageNo = 1;
    int sheetNo = 1;
    int rowNum = 1;
    SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
    List<ExcelPropertyEntity> propertyList = excelEntity.getPropertyList();
    SXSSFSheet sheet = generateHeader(workbook, propertyList, excelEntity.getFileName());

    while (true) {
        List<T> data = exportFunction.pageQuery(param, pageNo, pageSize);
        if (data == null || data.isEmpty()) {
            if (rowNum != 1) {
                sizeColumWidth(sheet, propertyList.size());
            }
            log.warn("查询结果为空,结束查询!");
            break;
        }
        for (int i = 1; i <= data.size(); i++, rowNum++) {
            T queryResult = data.get(i - 1);
            Object convertResult = exportFunction.convert(queryResult);
            if (rowNum > recordCountPerSheet) {
                sizeColumWidth(sheet, propertyList.size());
                sheet = generateHeader(workbook, propertyList, excelEntity.getFileName() + "_" + sheetNo);
                sheetNo++;
                rowNum = 1;
                columnWidthMap.clear();
            }
            SXSSFRow bodyRow = sheet.createRow(rowNum);
            for (int j = 0; j < propertyList.size(); j++) {
                SXSSFCell cell = bodyRow.createCell(j);
                buildCellValue(cell, convertResult, propertyList.get(j));
                calculateColumWidth(cell, j);
            }
            if (nullCellCount == propertyList.size()) {
                log.warn("忽略一行空数据!");
                sheet.removeRow(bodyRow);
                rowNum--;
            }
            nullCellCount = 0;
        }
        if (data.size() < pageSize) {
            sizeColumWidth(sheet, propertyList.size());
            log.warn("查询结果数量小于pageSize,结束查询!");
            break;
        }
        pageNo++;
    }
    return workbook;
}

Source File: CommonsUtils.java From czy-nexus-commons-utils with Apache License 2.0

4 votes

/**
 * 设置数据：无样式（行、列、单元格样式）
 *
 * @param wb
 * @param sxssfRow
 * @param dataLists
 * @param regionMap
 * @param columnMap
 * @param paneMap
 * @param sheetName
 * @param labelName
 * @param dropDownMap
 * @throws Exception
 */
public static void setDataListNoStyle(SXSSFWorkbook wb, SXSSFRow sxssfRow, List<List<String[]>> dataLists, HashMap regionMap,
                                      HashMap columnMap, HashMap paneMap, String[] sheetName, String[] labelName, HashMap dropDownMap,Integer defaultColumnWidth,Integer fontSize) throws Exception {
    if (dataLists == null) {
        log.debug("=== ===  === :Andyczy ExcelUtils Exception Message：Export data(type:List<List<String[]>>) cannot be empty!");
    }
    if (sheetName == null) {
        log.debug("=== ===  === :Andyczy ExcelUtils Exception Message：Export sheet(type:String[]) name cannot be empty!");
    }
    int k = 0;
    for (List<String[]> listRow : dataLists) {
        SXSSFSheet sxssfSheet = wb.createSheet();
        sxssfSheet.setDefaultColumnWidth(defaultColumnWidth);
        wb.setSheetName(k, sheetName[k]);
        CellStyle cellStyle = wb.createCellStyle();
        XSSFFont font = (XSSFFont) wb.createFont();

        int jRow = 0;
        //  自定义：大标题（看该方法说明）。
        jRow = setLabelName(jRow, k, wb, labelName, sxssfRow, sxssfSheet, listRow);

        //  自定义：每个表格固定表头（看该方法说明）。
        Integer pane = 1;
        if (paneMap != null && paneMap.get(k + 1) != null) {
            pane = (Integer) paneMap.get(k + 1) + (labelName != null ? 1 : 0);
            createFreezePane(sxssfSheet, pane);
        }
        //  自定义：每个单元格自定义合并单元格：对每个单元格自定义合并单元格（看该方法说明）。
        if (regionMap != null) {
            setMergedRegion(sxssfSheet, (ArrayList<Integer[]>) regionMap.get(k + 1));
        }
        //  自定义：每个单元格自定义下拉列表：对每个单元格自定义下拉列表（看该方法说明）。
        if (dropDownMap != null) {
            setDataValidation(sxssfSheet, (List<String[]>) dropDownMap.get(k + 1), listRow.size());
        }
        //  自定义：每个表格自定义列宽：对每个单元格自定义列宽（看该方法说明）。
        if (columnMap != null) {
            setColumnWidth(sxssfSheet, (HashMap) columnMap.get(k + 1));
        }
        //  默认样式。
        setStyle(cellStyle, font,fontSize);

        //  写入小标题与数据。
        Integer SIZE = listRow.size() < MAX_ROWSUM ? listRow.size() : MAX_ROWSUM;
        for (int i = 0; i < SIZE; i++) {
            sxssfRow = sxssfSheet.createRow(jRow);
            for (int j = 0; j < listRow.get(i).length; j++) {
                Cell cell = createCell(sxssfRow, j, listRow.get(i)[j]);
                cell.setCellStyle(cellStyle);
            }
            jRow++;
        }
        k++;
    }
}

Source File: ReportExcelUtil.java From roncoo-education with MIT License

4 votes

public static void exportExcelForLecturerProfit(HttpServletResponse response, Page<LecturerProfitVO> result) throws IOException {
	// 创建一个workbook 对应一个excel文件
	final SXSSFWorkbook workBook = new SXSSFWorkbook();
	SXSSFSheet sheet = workBook.createSheet("讲师分润报表");

	// 列名和列宽
	String[] names = { "讲师名称", "银行卡号", "银行名称", "银行开户名", "讲师分润（元）", "平台分润（元）", "时间" };// 表头
	Integer[] widths = { 25, 15, 15, 25, 25, 25, 25 };// 列宽

	// 创建第一行
	SXSSFRow row = sheet.createRow(0);

	// 设置第一行样式
	CellStyle headStyle = workBook.createCellStyle();
	headStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);// 水平居中
	headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中

	// 设置第一行字体
	Font headFont = workBook.createFont();
	headFont.setBold(true);
	headStyle.setFont(headFont);

	// 设置第一行单元格内容、单元格样式
	for (int i = 0; i < names.length; i++) {
		SXSSFCell cell = row.createCell(i);
		cell.setCellValue(names[i]);
		cell.setCellStyle(headStyle);
		sheet.setColumnWidth(i, widths[i] * 256);
	}

	// 从第二行开始遍历出分润记录表的数据，再写入单元格
	SXSSFRow row1 = sheet.createRow(1);
	int r = 1;
	for (LecturerProfitVO bean : result.getList()) {
		row1 = sheet.createRow(r++);
		row1.createCell(0).setCellValue(bean.getLecturerVO().getLecturerName());
		row1.createCell(1).setCellValue(bean.getBankCardNo());
		row1.createCell(2).setCellValue(bean.getBankName());
		row1.createCell(3).setCellValue(bean.getBankUserName());
		row1.createCell(4).setCellValue(bean.getLecturerProfit().doubleValue());
		row1.createCell(5).setCellValue(bean.getPlatformProfit().doubleValue());
		row1.createCell(6).setCellValue(new SimpleDateFormat("yyyy/MM/dd").format(bean.getGmtCreate()));
	}
	try {
		workBook.write(response.getOutputStream());
		response.getOutputStream().flush();
	} catch (IOException e) {
		e.printStackTrace();
	} finally {
		if (response.getOutputStream() != null)
			response.getOutputStream().close();
		if (workBook != null)
			workBook.close();
	}
}

Source File: POIUtils.java From FEBS-Security with Apache License 2.0

4 votes

static SXSSFRow newSXSSFRow(SXSSFSheet sheet, int index) {
    return sheet.createRow(index);
}

Java Code Examples for org.apache.poi.xssf.streaming.SXSSFSheet#createRow()