Java Code Examples for org.apache.poi.xssf.streaming.SXSSFCell#setCellValue()

The following examples show how to use org.apache.poi.xssf.streaming.SXSSFCell#setCellValue() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: ExcelWriter.java    From excel-boot with Artistic License 2.0 6 votes vote down vote up
/**
 * 构建模板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;
}
 
Example 2
Source File: ExcelWriter.java    From excel-boot with Artistic License 2.0 6 votes vote down vote up
/**
 * 构造 除第一行以外的其他行的列值
 *
 * @param cell
 * @param entity
 * @param property
 */
private void buildCellValue(SXSSFCell cell, Object entity, ExcelPropertyEntity property) throws Exception {
    Field field = property.getFieldEntity();
    Object cellValue = field.get(entity);
    if (StringUtil.isBlank(cellValue) || "0".equals(cellValue.toString()) || "0.0".equals(cellValue.toString()) || "0.00".equals(cellValue.toString())) {
        nullCellCount++;
    }
    if (cellValue == null) {
        cell.setCellValue("");
    } else if (cellValue instanceof BigDecimal) {
        if (-1 == property.getScale()) {
            cell.setCellValue(cellValue.toString());
        } else {
            cell.setCellValue((((BigDecimal) cellValue).setScale(property.getScale(), property.getRoundingMode())).toString());
        }
    } else if (cellValue instanceof Date) {
        cell.setCellValue(format(property.getDateFormat(), (Date) cellValue));
    } else {
        cell.setCellValue(cellValue.toString());
    }
}
 
Example 3
Source File: ExcelWriter.java    From excel-boot with Artistic License 2.0 5 votes vote down vote up
/**
 * 初始化第一行的属性
 *
 * @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;
}
 
Example 4
Source File: ExcelUtil.java    From agile-service-old with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 5
Source File: PoiWriteTest.java    From easyexcel with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 6
Source File: PoiWriteTest.java    From easyexcel with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 7
Source File: ReportExcelUtil.java    From roncoo-education with MIT License 4 votes vote down vote up
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();
	}
}