Java Code Examples for org.apache.poi.xssf.usermodel.XSSFRow#createCell()

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFRow#createCell() . 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: EpidemicReport.java From MyBox with Apache License 2.0

21 votes

public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet, List<String> extraFields) {
    try {
        List<String> columns = externalNames(extraFields);
        sheet.setDefaultColumnWidth(20);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columns.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        return columns;
    } catch (Exception e) {
        return null;
    }
}

Example 2

Source File: ObjectDataExportServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0

8 votes

private MetaFile writeExcel(Map<String, List<String[]>> data) throws IOException {

    XSSFWorkbook workBook = new XSSFWorkbook();

    for (String model : data.keySet()) {
      XSSFSheet sheet = workBook.createSheet(model);
      int count = 0;
      for (String[] record : data.get(model)) {
        XSSFRow row = sheet.createRow(count);
        int cellCount = 0;
        for (String val : record) {
          XSSFCell cell = row.createCell(cellCount);
          cell.setCellValue(val);
          cellCount++;
        }
        count++;
      }
    }

    File excelFile = MetaFiles.createTempFile("Data", ".xls").toFile();
    FileOutputStream out = new FileOutputStream(excelFile);
    workBook.write(out);
    out.close();

    return metaFiles.upload(excelFile);
  }

Example 3

Source File: GeographyCode.java From MyBox with Apache License 2.0

6 votes

public static List<String> writeExcelHeader(XSSFWorkbook wb, XSSFSheet sheet) {
    try {
        List<String> columns = externalNames();
        sheet.setDefaultColumnWidth(20);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0;
                i < columns.size();
                i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        return columns;
    } catch (Exception e) {
        return null;
    }
}

Example 4

Source File: XlsxResource.java From nextreports-server with Apache License 2.0

6 votes

@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new XSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       XSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			XSSFCell cell = headerRow.createCell(col);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(wb.getCreationHelper().createRichTextString(s));
			col++;
		}
	}		
}

Example 5

Source File: TestExternalRelvarXLSX3.java From Rel with Apache License 2.0

5 votes

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_count;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}

Example 6

Source File: UserController.java From parker with MIT License

5 votes

/**
 * poi导出，没用easypoi
 * 不要用swagger测试导出功能，下载的文件异常
 * 直接在浏览器访问http://localhost:8080/swagger-ui.html
 * 火狐浏览器可能出现文件名乱码，谷歌浏览器正常
 * @param response
 * @throws Exception
 */
@ApiOperation("导出excel")
@GetMapping("excel/export")
public void exportExcel(HttpServletResponse response) throws Exception{

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();

    try {
        //表头
        XSSFRow r0 = sheet.createRow(0);
        XSSFCell c00 = r0.createCell(0);
        c00.setCellValue("列1");
        XSSFCell c01 = r0.createCell(1);
        c01.setCellValue("列2");

        //数据
        XSSFRow r1 = sheet.createRow(1);
        XSSFCell c10 = r1.createCell(0);
        c10.setCellValue("数据1");
        XSSFCell c11 = r1.createCell(1);
        c11.setCellValue("数据2");

        response.reset();

        // 告诉浏览器用什么软件可以打开此文件
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 下载文件的默认名称
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试.xlsx", "utf-8"));

        //将文件输出
        workbook.write(response.getOutputStream());
    } catch (Exception e) {
        log.error("导出excel错误{}", e.getMessage());
        e.printStackTrace();
    } finally {
        workbook.close();
    }
}

Example 7

Source File: SpreadsheetTab.java From taro with MIT License

5 votes

private XSSFCell getOrCreatePoiCell(int rowNum, int col) {
    XSSFRow row = getOrCreatePoiRow(rowNum);
    XSSFCell cell = row.getCell(col);
    if (cell == null) {
        cell = row.createCell(col);
    }
    return cell;
}

Example 8

Source File: TestExternalRelvarXLSX2.java From Rel with Apache License 2.0

5 votes

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,4,5,6);
		insert(4,sheet,row,cell,1,2,3);
		insert(5,sheet,row,cell,7,8,9);
		insert(6,sheet,row,cell,7,8,9);
		insert(7,sheet,row,cell,4,5,6);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\" dup_remove;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}

Example 9

Source File: TestExternalRelvarXLSX2.java From Rel with Apache License 2.0

5 votes

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}

Example 10

Source File: TestExternalRelvarXLSX1.java From Rel with Apache License 2.0

5 votes

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}

Example 11

Source File: TestExternalRelvarXLSX4.java From Rel with Apache License 2.0

5 votes

@Before
public void testXLS1() throws IOException {
       try (XSSFWorkbook workbook = new XSSFWorkbook()) {
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = null;
        XSSFCell cell = null;
        row = sheet.createRow(0);
        cell = row.createCell(0);
		cell.setCellValue("A");
		cell = row.createCell(1);
		cell.setCellValue("B");
		cell = row.createCell(2);
		cell.setCellValue("C");
		
		insert(1,sheet,row,cell,1,2,3);
		insert(2,sheet,row,cell,4,5,6);
		insert(3,sheet,row,cell,7,8,9);
        
		try (FileOutputStream out = new FileOutputStream(file)) {
		    workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
       }

	String src = 
			"BEGIN;\n" +
					"var myvar external xls \"" + file.getAbsolutePath() + "\";" +
			"END;\n" +
			"true";
	testEquals("true", src);
}

Example 12

Source File: TestExternalRelvarXLSX4.java From Rel with Apache License 2.0

5 votes

private static void insert(int rowNum, XSSFSheet sheet, XSSFRow row, XSSFCell cell, int arg0, int arg1, int arg2) {
	row = sheet.createRow(rowNum);
       cell = row.createCell(0);
	cell.setCellValue(arg0);
	cell = row.createCell(1);
	cell.setCellValue(arg1);
	cell = row.createCell(2);
	cell.setCellValue(arg2);
}

Example 13

Source File: AccessTemplateServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0

5 votes

private void writeRow(XSSFSheet sheet, String[] values) {

    XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());

    for (int i = 0; i < values.length; i++) {
      XSSFCell cell = row.createCell(i);
      cell.setCellValue(values[i]);
    }
  }

Example 14

Source File: ExcelNodeSerializer.java From dhis2-core with BSD 3-Clause "New" or "Revised" License

5 votes

@Override
protected void startSerialize( RootNode rootNode, OutputStream outputStream ) throws Exception
{
    workbook = new XSSFWorkbook();
    sheet = workbook.createSheet( "Sheet1" );

    XSSFFont boldFont = workbook.createFont();
    boldFont.setBold( true );

    XSSFCellStyle boldCellStyle = workbook.createCellStyle();
    boldCellStyle.setFont( boldFont );

    // build schema
    for ( Node child : rootNode.getChildren() )
    {
        if ( child.isCollection() )
        {
            if ( !child.getChildren().isEmpty() )
            {
                Node node = child.getChildren().get( 0 );

                XSSFRow row = sheet.createRow( 0 );

                int cellIdx = 0;

                for ( Node property : node.getChildren() )
                {
                    if ( property.isSimple() )
                    {
                        XSSFCell cell = row.createCell( cellIdx++ );
                        cell.setCellValue( property.getName() );
                        cell.setCellStyle( boldCellStyle );
                    }
                }
            }
        }
    }
}

Example 15

Source File: Util.java From Knowage-Server with GNU Affero General Public License v3.0

5 votes

/**
 * @param source
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *
 */
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {

	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	short dh = source.getDefaultRowHeight();
	if (srcRow.getHeight() != dh) {
		destRow.setHeight(srcRow.getHeight());
	}
	int j = srcRow.getFirstCellNum();
	if (j < 0) {
		j = 0;
	}
	for (; j <= srcRow.getLastCellNum(); j++) {
		HSSFCell oldCell = srcRow.getCell(j);
		XSSFCell newCell = destRow.getCell(j);
		if (oldCell != null) {
			if (newCell == null) {
				newCell = destRow.createCell(j);
			}
			copyCell(oldCell, newCell, styleMap);
			CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {

				CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
						mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());

				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}

Example 16

Source File: ExcelFileUtils.java From SWET with MIT License

5 votes

public static void writeXLSXFile() throws Exception {

		XSSFWorkbook xssfwb = new XSSFWorkbook();
		XSSFSheet sheet = xssfwb.createSheet(sheetName);
		for (int row = 0; row < tableData.size(); row++) {
			XSSFRow xddfrow = sheet.createRow(row);
			rowData = tableData.get(row);
			for (int col = 0; col < rowData.size(); col++) {
				XSSFCell cell = xddfrow.createCell(col);
				cell.setCellValue(rowData.get(col));
				logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
			}
		}

		try (OutputStream fileOutputStream = new FileOutputStream(
				excelFileName)) {

			xssfwb.write(fileOutputStream);
			xssfwb.close();
			fileOutputStream.flush();
			fileOutputStream.close();

		} catch (IOException e) {
			String message = String.format("Exception saving XLSX file %s\n",
					excelFileName) + e.getMessage();
			logger.info(message);
			// NOTE: throw exceptions with user friendly messages to be rendered
			// by the master app
			throw new Exception(message);

		}
	}

Example 17

Source File: SampleController.java From tutorial with MIT License

5 votes

/**
 * 导出Excel的例子
 * 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
 * @return
 * @throws Exception
 */
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
    logger.trace("exportExcel");
    HttpHeaders responseHeaders = new HttpHeaders();
    responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
    responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);

    //中文文件名需要用iso8859-1编码
    InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
    XSSFSheet sheet = workbook.getSheetAt(0);

    List<SampleItem> list = getDataList();

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));

    for (int i=0; i<list.size(); i++) {
        SampleItem si = list.get(i);

        XSSFRow row = sheet.createRow(i + 1);

        Cell cell1 = row.createCell(0);
        cell1.setCellValue(si.getDate());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(si.getName());

        Cell cell3 = row.createCell(2);
        cell3.setCellValue(si.getScore());
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}

Example 18

Source File: GeographyCode.java From MyBox with Apache License 2.0

5 votes

public static void writeExcel(XSSFSheet sheet, int i, GeographyCode code) {
    try {
        List<String> row = values(code);
        XSSFRow sheetRow = sheet.createRow(i + 1);
        for (int j = 0;
                j < row.size();
                j++) {
            XSSFCell cell = sheetRow.createCell(j);
            cell.setCellValue(row.get(j));
        }
    } catch (Exception e) {
    }
}

Example 19

Source File: EpidemicReport.java From MyBox with Apache License 2.0

5 votes

public static void writeExcel(XSSFSheet sheet, int i, EpidemicReport report, List<String> extraFields) {
    try {
        List<String> row = values(report, extraFields);
        XSSFRow sheetRow = sheet.createRow(i + 1);
        for (int j = 0; j < row.size(); j++) {
            XSSFCell cell = sheetRow.createCell(j);
            cell.setCellValue(row.get(j));
        }
    } catch (Exception e) {
    }
}

Example 20

Source File: DataExportController.java From MyBox with Apache License 2.0

4 votes

protected boolean writeExcel(File file, String sql) {
    try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);
             FileWriter writer = new FileWriter(file, Charset.forName("utf-8"))) {
        String filename = file.getAbsolutePath();
        conn.setReadOnly(true);
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        List<String> columns = columnNames();
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle horizontalCenter = wb.createCellStyle();
        horizontalCenter.setAlignment(HorizontalAlignment.CENTER);
        for (int i = 0; i < columns.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(columns.get(i));
            cell.setCellStyle(horizontalCenter);
        }
        int count = 0;
        try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
            while (results.next()) {
                if (cancelled) {
                    updateLogs(message("Cancelled") + " " + filename);
                    return false;
                }
                writeExcel(conn, sheet, results, count);
                count++;
                if (verboseCheck.isSelected() && (count % 50 == 0)) {
                    updateLogs(message("Exported") + " " + count + ": " + filename);
                }
            }
        }
        for (int i = 0; i < columns.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        try ( OutputStream fileOut = new FileOutputStream(file)) {
            wb.write(fileOut);
        }
        return true;
    } catch (Exception e) {
        updateLogs(e.toString());
        return false;
    }
}