Java Code Examples for org.apache.poi.xssf.streaming.SXSSFSheet#createRow()
The following examples show how to use
org.apache.poi.xssf.streaming.SXSSFSheet#createRow() .
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 |
|
/**
* 构建模板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 | 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;
}
Example 3
| 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;
}
Example 4
| 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;
}
Example 5
| 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());
}
Example 6
| 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);
}
Example 7
| 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);
}
Example 8
| 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());
}
Example 9
| 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());
}
Example 10
| 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;
}
Example 11
| 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;
}
Example 12
| 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++;
}
}
Example 13
| 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();
}
}
Example 14
| 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);
}
