Java Code Examples for org.apache.poi.xssf.usermodel.XSSFCell#setCellValue()

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFCell#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: EpidemicReport.java    From MyBox with Apache License 2.0 21 votes vote down vote up
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 vote down vote up
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 vote down vote up
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 vote down vote up
@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: GeographyCode.java    From MyBox with Apache License 2.0 5 votes vote down vote up
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 6
Source File: ExcelFileUtils.java    From SWET with MIT License 5 votes vote down vote up
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 7
Source File: ExcelNodeSerializer.java    From dhis2-core with BSD 3-Clause "New" or "Revised" License 5 votes vote down vote up
@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 8
Source File: XlsxResource.java    From nextreports-server with Apache License 2.0 5 votes vote down vote up
@Override
protected void createDetailCell(int column, Object element) {
	XSSFCell cell = detailRow.createCell(column);
	cell.setCellType(XSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(wb.getCreationHelper().createRichTextString(element.toString()));
}
 
Example 9
Source File: AccessTemplateServiceImpl.java    From axelor-open-suite with GNU Affero General Public License v3.0 5 votes vote down vote up
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 10
Source File: UserController.java    From parker with MIT License 5 votes vote down vote up
/**
 * 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 11
Source File: TestExternalRelvarXLSX4.java    From Rel with Apache License 2.0 5 votes vote down vote up
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 12
Source File: TestExternalRelvarXLSX4.java    From Rel with Apache License 2.0 5 votes vote down vote up
@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 13
Source File: TestExternalRelvarXLSX1.java    From Rel with Apache License 2.0 5 votes vote down vote up
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 14
Source File: TestExternalRelvarXLSX1.java    From Rel with Apache License 2.0 5 votes vote down vote up
@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() + "\" autokey;" +
			"END;\n" +
			"true";
	testEquals("true", src);
}
 
Example 15
Source File: TestExternalRelvarXLSX2.java    From Rel with Apache License 2.0 5 votes vote down vote up
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 16
Source File: TestExternalRelvarXLSX2.java    From Rel with Apache License 2.0 5 votes vote down vote up
@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 17
Source File: TestExternalRelvarXLSX3.java    From Rel with Apache License 2.0 5 votes vote down vote up
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 18
Source File: TestExternalRelvarXLSX3.java    From Rel with Apache License 2.0 5 votes vote down vote up
@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 19
Source File: EpidemicReport.java    From MyBox with Apache License 2.0 5 votes vote down vote up
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 vote down vote up
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;
    }
}